hive-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From kevinwilf...@apache.org
Subject svn commit: r1399732 - in /hive/trunk/ql/src/test: queries/clientpositive/smb_mapjoin_12.q results/clientpositive/smb_mapjoin_12.q.out
Date Thu, 18 Oct 2012 16:41:27 GMT
Author: kevinwilfong
Date: Thu Oct 18 16:41:27 2012
New Revision: 1399732

URL: http://svn.apache.org/viewvc?rev=1399732&view=rev
Log:
HIVE-3551. Add more tests where output of sort merge join is sorted. (njain via kevinwilfong)

Added:
    hive/trunk/ql/src/test/queries/clientpositive/smb_mapjoin_12.q
    hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_12.q.out

Added: hive/trunk/ql/src/test/queries/clientpositive/smb_mapjoin_12.q
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/queries/clientpositive/smb_mapjoin_12.q?rev=1399732&view=auto
==============================================================================
--- hive/trunk/ql/src/test/queries/clientpositive/smb_mapjoin_12.q (added)
+++ hive/trunk/ql/src/test/queries/clientpositive/smb_mapjoin_12.q Thu Oct 18 16:41:27 2012
@@ -0,0 +1,49 @@
+set hive.optimize.bucketmapjoin = true;
+set hive.optimize.bucketmapjoin.sortedmerge = true;
+set hive.input.format = org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;
+set hive.enforce.bucketing=true;
+set hive.enforce.sorting=true;
+set hive.exec.reducers.max = 1;
+set hive.merge.mapfiles=false;
+set hive.merge.mapredfiles=false; 
+
+-- This test verifies that the output of a sort merge join on 1 big partition with multiple
small partitions is bucketed and sorted
+
+-- Create two bucketed and sorted tables
+CREATE TABLE test_table1 (key INT, value STRING) PARTITIONED BY (ds STRING) CLUSTERED BY
(key) SORTED BY (key) INTO 16 BUCKETS;
+CREATE TABLE test_table2 (key INT, value STRING) PARTITIONED BY (ds STRING) CLUSTERED BY
(key) SORTED BY (key) INTO 16 BUCKETS;
+
+FROM src
+INSERT OVERWRITE TABLE test_table1 PARTITION (ds = '1') SELECT *
+INSERT OVERWRITE TABLE test_table2 PARTITION (ds = '1') SELECT *
+INSERT OVERWRITE TABLE test_table2 PARTITION (ds = '2') SELECT *
+INSERT OVERWRITE TABLE test_table2 PARTITION (ds = '3') SELECT *;
+
+set hive.enforce.bucketing=false;
+set hive.enforce.sorting=false;
+
+-- Create a bucketed table
+CREATE TABLE test_table3 (key INT, value STRING) PARTITIONED BY (ds STRING) CLUSTERED BY
(key) SORTED BY (key) INTO 16 BUCKETS;
+
+-- Insert data into the bucketed table by joining the two bucketed and sorted tables, bucketing
is not enforced
+EXPLAIN EXTENDED
+INSERT OVERWRITE TABLE test_table3 PARTITION (ds = '1') SELECT /*+ MAPJOIN(b) */ a.key, b.value
FROM test_table1 a JOIN test_table2 b ON a.key = b.key AND a.ds = '1' AND b.ds >= '1';
+
+INSERT OVERWRITE TABLE test_table3 PARTITION (ds = '1') SELECT /*+ MAPJOIN(b) */ a.key, b.value
FROM test_table1 a JOIN test_table2 b ON a.key = b.key AND a.ds = '1' AND b.ds >= '1';
+
+-- Join data from a sampled bucket to verify the data is bucketed
+SELECT COUNT(*) FROM test_table3 TABLESAMPLE(BUCKET 2 OUT OF 16) a JOIN test_table1 TABLESAMPLE(BUCKET
2 OUT OF 16) b ON a.key = b.key AND a.ds = '1' AND b.ds='1';
+
+set hive.optimize.bucketmapjoin = true;
+set hive.optimize.bucketmapjoin.sortedmerge = true;
+set hive.input.format = org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;
+
+-- Join data from the sampled buckets of 2 tables to verify the data is bucketed and sorted
+explain extended
+INSERT OVERWRITE TABLE test_table3 PARTITION (ds = '2') 
+SELECT /*+mapjoin(b)*/ a.key, concat(a.value, b.value) FROM test_table3 a JOIN test_table1
b ON a.key = b.key AND a.ds = '1' AND b.ds='1';
+
+INSERT OVERWRITE TABLE test_table3 PARTITION (ds = '2') 
+SELECT /*+mapjoin(b)*/ a.key, concat(a.value, b.value) FROM test_table3 a JOIN test_table1
b ON a.key = b.key AND a.ds = '1' AND b.ds='1';
+
+SELECT count(*) from test_table3 tablesample (bucket 2 out of 16) a where ds = '2';
\ No newline at end of file

Added: hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_12.q.out
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_12.q.out?rev=1399732&view=auto
==============================================================================
--- hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_12.q.out (added)
+++ hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_12.q.out Thu Oct 18 16:41:27
2012
@@ -0,0 +1,519 @@
+PREHOOK: query: -- This test verifies that the output of a sort merge join on 1 big partition
with multiple small partitions is bucketed and sorted
+
+-- Create two bucketed and sorted tables
+CREATE TABLE test_table1 (key INT, value STRING) PARTITIONED BY (ds STRING) CLUSTERED BY
(key) SORTED BY (key) INTO 16 BUCKETS
+PREHOOK: type: CREATETABLE
+POSTHOOK: query: -- This test verifies that the output of a sort merge join on 1 big partition
with multiple small partitions is bucketed and sorted
+
+-- Create two bucketed and sorted tables
+CREATE TABLE test_table1 (key INT, value STRING) PARTITIONED BY (ds STRING) CLUSTERED BY
(key) SORTED BY (key) INTO 16 BUCKETS
+POSTHOOK: type: CREATETABLE
+POSTHOOK: Output: default@test_table1
+PREHOOK: query: CREATE TABLE test_table2 (key INT, value STRING) PARTITIONED BY (ds STRING)
CLUSTERED BY (key) SORTED BY (key) INTO 16 BUCKETS
+PREHOOK: type: CREATETABLE
+POSTHOOK: query: CREATE TABLE test_table2 (key INT, value STRING) PARTITIONED BY (ds STRING)
CLUSTERED BY (key) SORTED BY (key) INTO 16 BUCKETS
+POSTHOOK: type: CREATETABLE
+POSTHOOK: Output: default@test_table2
+PREHOOK: query: FROM src
+INSERT OVERWRITE TABLE test_table1 PARTITION (ds = '1') SELECT *
+INSERT OVERWRITE TABLE test_table2 PARTITION (ds = '1') SELECT *
+INSERT OVERWRITE TABLE test_table2 PARTITION (ds = '2') SELECT *
+INSERT OVERWRITE TABLE test_table2 PARTITION (ds = '3') SELECT *
+PREHOOK: type: QUERY
+PREHOOK: Input: default@src
+PREHOOK: Output: default@test_table1@ds=1
+PREHOOK: Output: default@test_table2@ds=1
+PREHOOK: Output: default@test_table2@ds=2
+PREHOOK: Output: default@test_table2@ds=3
+POSTHOOK: query: FROM src
+INSERT OVERWRITE TABLE test_table1 PARTITION (ds = '1') SELECT *
+INSERT OVERWRITE TABLE test_table2 PARTITION (ds = '1') SELECT *
+INSERT OVERWRITE TABLE test_table2 PARTITION (ds = '2') SELECT *
+INSERT OVERWRITE TABLE test_table2 PARTITION (ds = '3') SELECT *
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@src
+POSTHOOK: Output: default@test_table1@ds=1
+POSTHOOK: Output: default@test_table2@ds=1
+POSTHOOK: Output: default@test_table2@ds=2
+POSTHOOK: Output: default@test_table2@ds=3
+POSTHOOK: Lineage: test_table1 PARTITION(ds=1).key EXPRESSION [(src)src.FieldSchema(name:key,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table1 PARTITION(ds=1).value SIMPLE [(src)src.FieldSchema(name:value,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=1).key EXPRESSION [(src)src.FieldSchema(name:key,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=1).value SIMPLE [(src)src.FieldSchema(name:value,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=2).key EXPRESSION [(src)src.FieldSchema(name:key,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=2).value SIMPLE [(src)src.FieldSchema(name:value,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=3).key EXPRESSION [(src)src.FieldSchema(name:key,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=3).value SIMPLE [(src)src.FieldSchema(name:value,
type:string, comment:default), ]
+PREHOOK: query: -- Create a bucketed table
+CREATE TABLE test_table3 (key INT, value STRING) PARTITIONED BY (ds STRING) CLUSTERED BY
(key) SORTED BY (key) INTO 16 BUCKETS
+PREHOOK: type: CREATETABLE
+POSTHOOK: query: -- Create a bucketed table
+CREATE TABLE test_table3 (key INT, value STRING) PARTITIONED BY (ds STRING) CLUSTERED BY
(key) SORTED BY (key) INTO 16 BUCKETS
+POSTHOOK: type: CREATETABLE
+POSTHOOK: Output: default@test_table3
+POSTHOOK: Lineage: test_table1 PARTITION(ds=1).key EXPRESSION [(src)src.FieldSchema(name:key,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table1 PARTITION(ds=1).value SIMPLE [(src)src.FieldSchema(name:value,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=1).key EXPRESSION [(src)src.FieldSchema(name:key,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=1).value SIMPLE [(src)src.FieldSchema(name:value,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=2).key EXPRESSION [(src)src.FieldSchema(name:key,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=2).value SIMPLE [(src)src.FieldSchema(name:value,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=3).key EXPRESSION [(src)src.FieldSchema(name:key,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=3).value SIMPLE [(src)src.FieldSchema(name:value,
type:string, comment:default), ]
+PREHOOK: query: -- Insert data into the bucketed table by joining the two bucketed and sorted
tables, bucketing is not enforced
+EXPLAIN EXTENDED
+INSERT OVERWRITE TABLE test_table3 PARTITION (ds = '1') SELECT /*+ MAPJOIN(b) */ a.key, b.value
FROM test_table1 a JOIN test_table2 b ON a.key = b.key AND a.ds = '1' AND b.ds >= '1'
+PREHOOK: type: QUERY
+POSTHOOK: query: -- Insert data into the bucketed table by joining the two bucketed and sorted
tables, bucketing is not enforced
+EXPLAIN EXTENDED
+INSERT OVERWRITE TABLE test_table3 PARTITION (ds = '1') SELECT /*+ MAPJOIN(b) */ a.key, b.value
FROM test_table1 a JOIN test_table2 b ON a.key = b.key AND a.ds = '1' AND b.ds >= '1'
+POSTHOOK: type: QUERY
+POSTHOOK: Lineage: test_table1 PARTITION(ds=1).key EXPRESSION [(src)src.FieldSchema(name:key,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table1 PARTITION(ds=1).value SIMPLE [(src)src.FieldSchema(name:value,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=1).key EXPRESSION [(src)src.FieldSchema(name:key,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=1).value SIMPLE [(src)src.FieldSchema(name:value,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=2).key EXPRESSION [(src)src.FieldSchema(name:key,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=2).value SIMPLE [(src)src.FieldSchema(name:value,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=3).key EXPRESSION [(src)src.FieldSchema(name:key,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=3).value SIMPLE [(src)src.FieldSchema(name:value,
type:string, comment:default), ]
+ABSTRACT SYNTAX TREE:
+  (TOK_QUERY (TOK_FROM (TOK_JOIN (TOK_TABREF (TOK_TABNAME test_table1) a) (TOK_TABREF (TOK_TABNAME
test_table2) b) (AND (AND (= (. (TOK_TABLE_OR_COL a) key) (. (TOK_TABLE_OR_COL b) key)) (=
(. (TOK_TABLE_OR_COL a) ds) '1')) (>= (. (TOK_TABLE_OR_COL b) ds) '1')))) (TOK_INSERT (TOK_DESTINATION
(TOK_TAB (TOK_TABNAME test_table3) (TOK_PARTSPEC (TOK_PARTVAL ds '1')))) (TOK_SELECT (TOK_HINTLIST
(TOK_HINT TOK_MAPJOIN (TOK_HINTARGLIST b))) (TOK_SELEXPR (. (TOK_TABLE_OR_COL a) key)) (TOK_SELEXPR
(. (TOK_TABLE_OR_COL b) value)))))
+
+STAGE DEPENDENCIES:
+  Stage-1 is a root stage
+  Stage-0 depends on stages: Stage-1
+  Stage-2 depends on stages: Stage-0
+
+STAGE PLANS:
+  Stage: Stage-1
+    Map Reduce
+      Alias -> Map Operator Tree:
+        a 
+          TableScan
+            alias: a
+            GatherStats: false
+            Sorted Merge Bucket Map Join Operator
+              condition map:
+                   Inner Join 0 to 1
+              condition expressions:
+                0 {key}
+                1 {value}
+              handleSkewJoin: false
+              keys:
+                0 [Column[key]]
+                1 [Column[key]]
+              outputColumnNames: _col0, _col6
+              Position of Big Table: 0
+              Select Operator
+                expressions:
+                      expr: _col0
+                      type: int
+                      expr: _col6
+                      type: string
+                outputColumnNames: _col0, _col6
+                Select Operator
+                  expressions:
+                        expr: _col0
+                        type: int
+                        expr: _col6
+                        type: string
+                  outputColumnNames: _col0, _col1
+                  File Output Operator
+                    compressed: false
+                    GlobalTableId: 1
+#### A masked pattern was here ####
+                    NumFilesPerFileSink: 1
+                    Static Partition Specification: ds=1/
+#### A masked pattern was here ####
+                    table:
+                        input format: org.apache.hadoop.mapred.TextInputFormat
+                        output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
+                        properties:
+                          SORTBUCKETCOLSPREFIX TRUE
+                          bucket_count 16
+                          bucket_field_name key
+                          columns key,value
+                          columns.types int:string
+#### A masked pattern was here ####
+                          name default.test_table3
+                          partition_columns ds
+                          serialization.ddl struct test_table3 { i32 key, string value}
+                          serialization.format 1
+                          serialization.lib org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
+#### A masked pattern was here ####
+                        serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
+                        name: default.test_table3
+                    TotalFiles: 1
+                    GatherStats: true
+                    MultiFileSpray: false
+      Needs Tagging: false
+      Path -> Alias:
+#### A masked pattern was here ####
+      Path -> Partition:
+#### A masked pattern was here ####
+          Partition
+            base file name: ds=1
+            input format: org.apache.hadoop.mapred.TextInputFormat
+            output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
+            partition values:
+              ds 1
+            properties:
+              SORTBUCKETCOLSPREFIX TRUE
+              bucket_count 16
+              bucket_field_name key
+              columns key,value
+              columns.types int:string
+#### A masked pattern was here ####
+              name default.test_table1
+              numFiles 16
+              numPartitions 1
+              numRows 500
+              partition_columns ds
+              rawDataSize 5312
+              serialization.ddl struct test_table1 { i32 key, string value}
+              serialization.format 1
+              serialization.lib org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
+              totalSize 5812
+#### A masked pattern was here ####
+            serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
+          
+              input format: org.apache.hadoop.mapred.TextInputFormat
+              output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
+              properties:
+                SORTBUCKETCOLSPREFIX TRUE
+                bucket_count 16
+                bucket_field_name key
+                columns key,value
+                columns.types int:string
+#### A masked pattern was here ####
+                name default.test_table1
+                numFiles 16
+                numPartitions 1
+                numRows 500
+                partition_columns ds
+                rawDataSize 5312
+                serialization.ddl struct test_table1 { i32 key, string value}
+                serialization.format 1
+                serialization.lib org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
+                totalSize 5812
+#### A masked pattern was here ####
+              serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
+              name: default.test_table1
+            name: default.test_table1
+      Truncated Path -> Alias:
+        /test_table1/ds=1 [a]
+
+  Stage: Stage-0
+    Move Operator
+      tables:
+          partition:
+            ds 1
+          replace: true
+#### A masked pattern was here ####
+          table:
+              input format: org.apache.hadoop.mapred.TextInputFormat
+              output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
+              properties:
+                SORTBUCKETCOLSPREFIX TRUE
+                bucket_count 16
+                bucket_field_name key
+                columns key,value
+                columns.types int:string
+#### A masked pattern was here ####
+                name default.test_table3
+                partition_columns ds
+                serialization.ddl struct test_table3 { i32 key, string value}
+                serialization.format 1
+                serialization.lib org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
+#### A masked pattern was here ####
+              serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
+              name: default.test_table3
+#### A masked pattern was here ####
+
+  Stage: Stage-2
+    Stats-Aggr Operator
+#### A masked pattern was here ####
+
+
+PREHOOK: query: INSERT OVERWRITE TABLE test_table3 PARTITION (ds = '1') SELECT /*+ MAPJOIN(b)
*/ a.key, b.value FROM test_table1 a JOIN test_table2 b ON a.key = b.key AND a.ds = '1' AND
b.ds >= '1'
+PREHOOK: type: QUERY
+PREHOOK: Input: default@test_table1@ds=1
+PREHOOK: Input: default@test_table2@ds=1
+PREHOOK: Input: default@test_table2@ds=2
+PREHOOK: Input: default@test_table2@ds=3
+PREHOOK: Output: default@test_table3@ds=1
+POSTHOOK: query: INSERT OVERWRITE TABLE test_table3 PARTITION (ds = '1') SELECT /*+ MAPJOIN(b)
*/ a.key, b.value FROM test_table1 a JOIN test_table2 b ON a.key = b.key AND a.ds = '1' AND
b.ds >= '1'
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@test_table1@ds=1
+POSTHOOK: Input: default@test_table2@ds=1
+POSTHOOK: Input: default@test_table2@ds=2
+POSTHOOK: Input: default@test_table2@ds=3
+POSTHOOK: Output: default@test_table3@ds=1
+POSTHOOK: Lineage: test_table1 PARTITION(ds=1).key EXPRESSION [(src)src.FieldSchema(name:key,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table1 PARTITION(ds=1).value SIMPLE [(src)src.FieldSchema(name:value,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=1).key EXPRESSION [(src)src.FieldSchema(name:key,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=1).value SIMPLE [(src)src.FieldSchema(name:value,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=2).key EXPRESSION [(src)src.FieldSchema(name:key,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=2).value SIMPLE [(src)src.FieldSchema(name:value,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=3).key EXPRESSION [(src)src.FieldSchema(name:key,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=3).value SIMPLE [(src)src.FieldSchema(name:value,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table3 PARTITION(ds=1).key SIMPLE [(test_table1)a.FieldSchema(name:key,
type:int, comment:null), ]
+POSTHOOK: Lineage: test_table3 PARTITION(ds=1).value SIMPLE [(test_table2)b.FieldSchema(name:value,
type:string, comment:null), ]
+PREHOOK: query: -- Join data from a sampled bucket to verify the data is bucketed
+SELECT COUNT(*) FROM test_table3 TABLESAMPLE(BUCKET 2 OUT OF 16) a JOIN test_table1 TABLESAMPLE(BUCKET
2 OUT OF 16) b ON a.key = b.key AND a.ds = '1' AND b.ds='1'
+PREHOOK: type: QUERY
+PREHOOK: Input: default@test_table1@ds=1
+PREHOOK: Input: default@test_table3@ds=1
+#### A masked pattern was here ####
+POSTHOOK: query: -- Join data from a sampled bucket to verify the data is bucketed
+SELECT COUNT(*) FROM test_table3 TABLESAMPLE(BUCKET 2 OUT OF 16) a JOIN test_table1 TABLESAMPLE(BUCKET
2 OUT OF 16) b ON a.key = b.key AND a.ds = '1' AND b.ds='1'
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@test_table1@ds=1
+POSTHOOK: Input: default@test_table3@ds=1
+#### A masked pattern was here ####
+POSTHOOK: Lineage: test_table1 PARTITION(ds=1).key EXPRESSION [(src)src.FieldSchema(name:key,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table1 PARTITION(ds=1).value SIMPLE [(src)src.FieldSchema(name:value,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=1).key EXPRESSION [(src)src.FieldSchema(name:key,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=1).value SIMPLE [(src)src.FieldSchema(name:value,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=2).key EXPRESSION [(src)src.FieldSchema(name:key,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=2).value SIMPLE [(src)src.FieldSchema(name:value,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=3).key EXPRESSION [(src)src.FieldSchema(name:key,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=3).value SIMPLE [(src)src.FieldSchema(name:value,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table3 PARTITION(ds=1).key SIMPLE [(test_table1)a.FieldSchema(name:key,
type:int, comment:null), ]
+POSTHOOK: Lineage: test_table3 PARTITION(ds=1).value SIMPLE [(test_table2)b.FieldSchema(name:value,
type:string, comment:null), ]
+879
+PREHOOK: query: -- Join data from the sampled buckets of 2 tables to verify the data is bucketed
and sorted
+explain extended
+INSERT OVERWRITE TABLE test_table3 PARTITION (ds = '2') 
+SELECT /*+mapjoin(b)*/ a.key, concat(a.value, b.value) FROM test_table3 a JOIN test_table1
b ON a.key = b.key AND a.ds = '1' AND b.ds='1'
+PREHOOK: type: QUERY
+POSTHOOK: query: -- Join data from the sampled buckets of 2 tables to verify the data is
bucketed and sorted
+explain extended
+INSERT OVERWRITE TABLE test_table3 PARTITION (ds = '2') 
+SELECT /*+mapjoin(b)*/ a.key, concat(a.value, b.value) FROM test_table3 a JOIN test_table1
b ON a.key = b.key AND a.ds = '1' AND b.ds='1'
+POSTHOOK: type: QUERY
+POSTHOOK: Lineage: test_table1 PARTITION(ds=1).key EXPRESSION [(src)src.FieldSchema(name:key,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table1 PARTITION(ds=1).value SIMPLE [(src)src.FieldSchema(name:value,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=1).key EXPRESSION [(src)src.FieldSchema(name:key,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=1).value SIMPLE [(src)src.FieldSchema(name:value,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=2).key EXPRESSION [(src)src.FieldSchema(name:key,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=2).value SIMPLE [(src)src.FieldSchema(name:value,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=3).key EXPRESSION [(src)src.FieldSchema(name:key,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=3).value SIMPLE [(src)src.FieldSchema(name:value,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table3 PARTITION(ds=1).key SIMPLE [(test_table1)a.FieldSchema(name:key,
type:int, comment:null), ]
+POSTHOOK: Lineage: test_table3 PARTITION(ds=1).value SIMPLE [(test_table2)b.FieldSchema(name:value,
type:string, comment:null), ]
+ABSTRACT SYNTAX TREE:
+  (TOK_QUERY (TOK_FROM (TOK_JOIN (TOK_TABREF (TOK_TABNAME test_table3) a) (TOK_TABREF (TOK_TABNAME
test_table1) b) (AND (AND (= (. (TOK_TABLE_OR_COL a) key) (. (TOK_TABLE_OR_COL b) key)) (=
(. (TOK_TABLE_OR_COL a) ds) '1')) (= (. (TOK_TABLE_OR_COL b) ds) '1')))) (TOK_INSERT (TOK_DESTINATION
(TOK_TAB (TOK_TABNAME test_table3) (TOK_PARTSPEC (TOK_PARTVAL ds '2')))) (TOK_SELECT (TOK_HINTLIST
(TOK_HINT TOK_MAPJOIN (TOK_HINTARGLIST b))) (TOK_SELEXPR (. (TOK_TABLE_OR_COL a) key)) (TOK_SELEXPR
(TOK_FUNCTION concat (. (TOK_TABLE_OR_COL a) value) (. (TOK_TABLE_OR_COL b) value))))))
+
+STAGE DEPENDENCIES:
+  Stage-1 is a root stage
+  Stage-0 depends on stages: Stage-1
+  Stage-2 depends on stages: Stage-0
+
+STAGE PLANS:
+  Stage: Stage-1
+    Map Reduce
+      Alias -> Map Operator Tree:
+        a 
+          TableScan
+            alias: a
+            GatherStats: false
+            Sorted Merge Bucket Map Join Operator
+              condition map:
+                   Inner Join 0 to 1
+              condition expressions:
+                0 {key} {value}
+                1 {value}
+              handleSkewJoin: false
+              keys:
+                0 [Column[key]]
+                1 [Column[key]]
+              outputColumnNames: _col0, _col1, _col6
+              Position of Big Table: 0
+              Select Operator
+                expressions:
+                      expr: _col0
+                      type: int
+                      expr: _col1
+                      type: string
+                      expr: _col6
+                      type: string
+                outputColumnNames: _col0, _col1, _col6
+                Select Operator
+                  expressions:
+                        expr: _col0
+                        type: int
+                        expr: concat(_col1, _col6)
+                        type: string
+                  outputColumnNames: _col0, _col1
+                  File Output Operator
+                    compressed: false
+                    GlobalTableId: 1
+#### A masked pattern was here ####
+                    NumFilesPerFileSink: 1
+                    Static Partition Specification: ds=2/
+#### A masked pattern was here ####
+                    table:
+                        input format: org.apache.hadoop.mapred.TextInputFormat
+                        output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
+                        properties:
+                          SORTBUCKETCOLSPREFIX TRUE
+                          bucket_count 16
+                          bucket_field_name key
+                          columns key,value
+                          columns.types int:string
+#### A masked pattern was here ####
+                          name default.test_table3
+                          numFiles 16
+                          numPartitions 1
+                          numRows 3084
+                          partition_columns ds
+                          rawDataSize 32904
+                          serialization.ddl struct test_table3 { i32 key, string value}
+                          serialization.format 1
+                          serialization.lib org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
+                          totalSize 35988
+#### A masked pattern was here ####
+                        serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
+                        name: default.test_table3
+                    TotalFiles: 1
+                    GatherStats: true
+                    MultiFileSpray: false
+      Needs Tagging: false
+      Path -> Alias:
+#### A masked pattern was here ####
+      Path -> Partition:
+#### A masked pattern was here ####
+          Partition
+            base file name: ds=1
+            input format: org.apache.hadoop.mapred.TextInputFormat
+            output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
+            partition values:
+              ds 1
+            properties:
+              SORTBUCKETCOLSPREFIX TRUE
+              bucket_count 16
+              bucket_field_name key
+              columns key,value
+              columns.types int:string
+#### A masked pattern was here ####
+              name default.test_table3
+              numFiles 16
+              numPartitions 1
+              numRows 3084
+              partition_columns ds
+              rawDataSize 32904
+              serialization.ddl struct test_table3 { i32 key, string value}
+              serialization.format 1
+              serialization.lib org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
+              totalSize 35988
+#### A masked pattern was here ####
+            serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
+          
+              input format: org.apache.hadoop.mapred.TextInputFormat
+              output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
+              properties:
+                SORTBUCKETCOLSPREFIX TRUE
+                bucket_count 16
+                bucket_field_name key
+                columns key,value
+                columns.types int:string
+#### A masked pattern was here ####
+                name default.test_table3
+                numFiles 16
+                numPartitions 1
+                numRows 3084
+                partition_columns ds
+                rawDataSize 32904
+                serialization.ddl struct test_table3 { i32 key, string value}
+                serialization.format 1
+                serialization.lib org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
+                totalSize 35988
+#### A masked pattern was here ####
+              serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
+              name: default.test_table3
+            name: default.test_table3
+      Truncated Path -> Alias:
+        /test_table3/ds=1 [a]
+
+  Stage: Stage-0
+    Move Operator
+      tables:
+          partition:
+            ds 2
+          replace: true
+#### A masked pattern was here ####
+          table:
+              input format: org.apache.hadoop.mapred.TextInputFormat
+              output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
+              properties:
+                SORTBUCKETCOLSPREFIX TRUE
+                bucket_count 16
+                bucket_field_name key
+                columns key,value
+                columns.types int:string
+#### A masked pattern was here ####
+                name default.test_table3
+                numFiles 16
+                numPartitions 1
+                numRows 3084
+                partition_columns ds
+                rawDataSize 32904
+                serialization.ddl struct test_table3 { i32 key, string value}
+                serialization.format 1
+                serialization.lib org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
+                totalSize 35988
+#### A masked pattern was here ####
+              serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
+              name: default.test_table3
+#### A masked pattern was here ####
+
+  Stage: Stage-2
+    Stats-Aggr Operator
+#### A masked pattern was here ####
+
+
+PREHOOK: query: INSERT OVERWRITE TABLE test_table3 PARTITION (ds = '2') 
+SELECT /*+mapjoin(b)*/ a.key, concat(a.value, b.value) FROM test_table3 a JOIN test_table1
b ON a.key = b.key AND a.ds = '1' AND b.ds='1'
+PREHOOK: type: QUERY
+PREHOOK: Input: default@test_table1@ds=1
+PREHOOK: Input: default@test_table3@ds=1
+PREHOOK: Output: default@test_table3@ds=2
+POSTHOOK: query: INSERT OVERWRITE TABLE test_table3 PARTITION (ds = '2') 
+SELECT /*+mapjoin(b)*/ a.key, concat(a.value, b.value) FROM test_table3 a JOIN test_table1
b ON a.key = b.key AND a.ds = '1' AND b.ds='1'
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@test_table1@ds=1
+POSTHOOK: Input: default@test_table3@ds=1
+POSTHOOK: Output: default@test_table3@ds=2
+POSTHOOK: Lineage: test_table1 PARTITION(ds=1).key EXPRESSION [(src)src.FieldSchema(name:key,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table1 PARTITION(ds=1).value SIMPLE [(src)src.FieldSchema(name:value,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=1).key EXPRESSION [(src)src.FieldSchema(name:key,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=1).value SIMPLE [(src)src.FieldSchema(name:value,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=2).key EXPRESSION [(src)src.FieldSchema(name:key,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=2).value SIMPLE [(src)src.FieldSchema(name:value,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=3).key EXPRESSION [(src)src.FieldSchema(name:key,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=3).value SIMPLE [(src)src.FieldSchema(name:value,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table3 PARTITION(ds=1).key SIMPLE [(test_table1)a.FieldSchema(name:key,
type:int, comment:null), ]
+POSTHOOK: Lineage: test_table3 PARTITION(ds=1).value SIMPLE [(test_table2)b.FieldSchema(name:value,
type:string, comment:null), ]
+POSTHOOK: Lineage: test_table3 PARTITION(ds=2).key SIMPLE [(test_table3)a.FieldSchema(name:key,
type:int, comment:null), ]
+POSTHOOK: Lineage: test_table3 PARTITION(ds=2).value EXPRESSION [(test_table3)a.FieldSchema(name:value,
type:string, comment:null), (test_table1)b.FieldSchema(name:value, type:string, comment:null),
]
+PREHOOK: query: SELECT count(*) from test_table3 tablesample (bucket 2 out of 16) a where
ds = '2'
+PREHOOK: type: QUERY
+PREHOOK: Input: default@test_table3@ds=2
+#### A masked pattern was here ####
+POSTHOOK: query: SELECT count(*) from test_table3 tablesample (bucket 2 out of 16) a where
ds = '2'
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@test_table3@ds=2
+#### A masked pattern was here ####
+POSTHOOK: Lineage: test_table1 PARTITION(ds=1).key EXPRESSION [(src)src.FieldSchema(name:key,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table1 PARTITION(ds=1).value SIMPLE [(src)src.FieldSchema(name:value,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=1).key EXPRESSION [(src)src.FieldSchema(name:key,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=1).value SIMPLE [(src)src.FieldSchema(name:value,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=2).key EXPRESSION [(src)src.FieldSchema(name:key,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=2).value SIMPLE [(src)src.FieldSchema(name:value,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=3).key EXPRESSION [(src)src.FieldSchema(name:key,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table2 PARTITION(ds=3).value SIMPLE [(src)src.FieldSchema(name:value,
type:string, comment:default), ]
+POSTHOOK: Lineage: test_table3 PARTITION(ds=1).key SIMPLE [(test_table1)a.FieldSchema(name:key,
type:int, comment:null), ]
+POSTHOOK: Lineage: test_table3 PARTITION(ds=1).value SIMPLE [(test_table2)b.FieldSchema(name:value,
type:string, comment:null), ]
+POSTHOOK: Lineage: test_table3 PARTITION(ds=2).key SIMPLE [(test_table3)a.FieldSchema(name:key,
type:int, comment:null), ]
+POSTHOOK: Lineage: test_table3 PARTITION(ds=2).value EXPRESSION [(test_table3)a.FieldSchema(name:value,
type:string, comment:null), (test_table1)b.FieldSchema(name:value, type:string, comment:null),
]
+879



Mime
View raw message