Saving all output to "!!{outputDirectory}!!/bucketmapjoin7.q.raw". Enter "record" with no arguments to stop it. >>> !run !!{qFileDirectory}!!/bucketmapjoin7.q >>> set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; No rows affected >>> >>> CREATE TABLE srcbucket_mapjoin_part_1 (key INT, value STRING) PARTITIONED BY (ds STRING, hr STRING) CLUSTERED BY (key) INTO 2 BUCKETS STORED AS TEXTFILE; No rows affected >>> LOAD DATA LOCAL INPATH '../data/files/srcbucket20.txt' INTO TABLE srcbucket_mapjoin_part_1 PARTITION (ds='2008-04-08', hr='0'); No rows affected >>> LOAD DATA LOCAL INPATH '../data/files/srcbucket21.txt' INTO TABLE srcbucket_mapjoin_part_1 PARTITION (ds='2008-04-08', hr='0'); No rows affected >>> >>> CREATE TABLE srcbucket_mapjoin_part_2 (key INT, value STRING) PARTITIONED BY (ds STRING, hr STRING) CLUSTERED BY (key) INTO 2 BUCKETS STORED AS TEXTFILE; No rows affected >>> LOAD DATA LOCAL INPATH '../data/files/srcbucket20.txt' INTO TABLE srcbucket_mapjoin_part_2 PARTITION (ds='2008-04-08', hr='0'); No rows affected >>> LOAD DATA LOCAL INPATH '../data/files/srcbucket21.txt' INTO TABLE srcbucket_mapjoin_part_2 PARTITION (ds='2008-04-08', hr='0'); No rows affected >>> >>> set hive.optimize.bucketmapjoin=true; No rows affected >>> >>> -- Tests that bucket map join works with a table with more than one level of partitioning >>> >>> EXPLAIN EXTENDED SELECT /*+ MAPJOIN(b) */ a.key, b.value FROM srcbucket_mapjoin_part_1 a JOIN srcbucket_mapjoin_part_2 b ON a.key = b.key AND a.ds = '2008-04-08' AND b.ds = '2008-04-08' LIMIT 1; 'Explain' 'ABSTRACT SYNTAX TREE:' ' (TOK_QUERY (TOK_FROM (TOK_JOIN (TOK_TABREF (TOK_TABNAME srcbucket_mapjoin_part_1) a) (TOK_TABREF (TOK_TABNAME srcbucket_mapjoin_part_2) b) (AND (AND (= (. (TOK_TABLE_OR_COL a) key) (. (TOK_TABLE_OR_COL b) key)) (= (. (TOK_TABLE_OR_COL a) ds) '2008-04-08')) (= (. (TOK_TABLE_OR_COL b) ds) '2008-04-08')))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (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))) (TOK_LIMIT 1)))' '' 'STAGE DEPENDENCIES:' ' Stage-3 is a root stage' ' Stage-1 depends on stages: Stage-3' ' Stage-0 is a root stage' '' 'STAGE PLANS:' ' Stage: Stage-3' ' Map Reduce Local Work' ' Alias -> Map Local Tables:' ' b ' ' Fetch Operator' ' limit: -1' ' Alias -> Map Local Operator Tree:' ' b ' ' TableScan' ' alias: b' ' GatherStats: false' ' HashTable Sink Operator' ' condition expressions:' ' 0 {key}' ' 1 {value}' ' handleSkewJoin: false' ' keys:' ' 0 [Column[key]]' ' 1 [Column[key]]' ' Position of Big Table: 0' ' Bucket Mapjoin Context:' ' Alias Bucket Base File Name Mapping:' ' b {ds=2008-04-08/hr=0/srcbucket20.txt=[ds=2008-04-08/hr=0/srcbucket20.txt], ds=2008-04-08/hr=0/srcbucket21.txt=[ds=2008-04-08/hr=0/srcbucket21.txt]}' ' Alias Bucket File Name Mapping:' ' b {!!{hive.metastore.warehouse.dir}!!/bucketmapjoin7.db/srcbucket_mapjoin_part_1/ds=2008-04-08/hr=0/srcbucket20.txt=[!!{hive.metastore.warehouse.dir}!!/bucketmapjoin7.db/srcbucket_mapjoin_part_2/ds=2008-04-08/hr=0/srcbucket20.txt], !!{hive.metastore.warehouse.dir}!!/bucketmapjoin7.db/srcbucket_mapjoin_part_1/ds=2008-04-08/hr=0/srcbucket21.txt=[!!{hive.metastore.warehouse.dir}!!/bucketmapjoin7.db/srcbucket_mapjoin_part_2/ds=2008-04-08/hr=0/srcbucket21.txt]}' ' Alias Bucket Output File Name Mapping:' ' !!{hive.metastore.warehouse.dir}!!/bucketmapjoin7.db/srcbucket_mapjoin_part_1/ds=2008-04-08/hr=0/srcbucket20.txt 0' ' !!{hive.metastore.warehouse.dir}!!/bucketmapjoin7.db/srcbucket_mapjoin_part_1/ds=2008-04-08/hr=0/srcbucket21.txt 1' '' ' Stage: Stage-1' ' Map Reduce' ' Alias -> Map Operator Tree:' ' a ' ' TableScan' ' alias: a' ' GatherStats: false' ' 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, _col7' ' Position of Big Table: 0' ' Select Operator' ' expressions:' ' expr: _col0' ' type: int' ' expr: _col7' ' type: string' ' outputColumnNames: _col0, _col7' ' Select Operator' ' expressions:' ' expr: _col0' ' type: int' ' expr: _col7' ' type: string' ' outputColumnNames: _col0, _col1' ' Limit' ' File Output Operator' ' compressed: false' ' GlobalTableId: 0' ' directory: file:!!{hive.exec.scratchdir}!!' ' NumFilesPerFileSink: 1' ' Stats Publishing Key Prefix: file:!!{hive.exec.scratchdir}!!' ' table:' ' input format: org.apache.hadoop.mapred.TextInputFormat' ' output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' ' properties:' ' columns _col0,_col1' ' columns.types int:string' ' escape.delim \' ' serialization.format 1' ' TotalFiles: 1' ' GatherStats: false' ' MultiFileSpray: false' ' Local Work:' ' Map Reduce Local Work' ' Needs Tagging: false' ' Path -> Alias:' ' !!{hive.metastore.warehouse.dir}!!/bucketmapjoin7.db/srcbucket_mapjoin_part_1/ds=2008-04-08/hr=0 [a]' ' Path -> Partition:' ' !!{hive.metastore.warehouse.dir}!!/bucketmapjoin7.db/srcbucket_mapjoin_part_1/ds=2008-04-08/hr=0 ' ' Partition' ' base file name: hr=0' ' input format: org.apache.hadoop.mapred.TextInputFormat' ' output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' ' partition values:' ' ds 2008-04-08' ' hr 0' ' properties:' ' bucket_count 2' ' bucket_field_name key' ' columns key,value' ' columns.types int:string' ' file.inputformat org.apache.hadoop.mapred.TextInputFormat' ' file.outputformat org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' ' location !!{hive.metastore.warehouse.dir}!!/bucketmapjoin7.db/srcbucket_mapjoin_part_1/ds=2008-04-08/hr=0' ' name bucketmapjoin7.srcbucket_mapjoin_part_1' ' numFiles 2' ' numPartitions 1' ' numRows 0' ' partition_columns ds/hr' ' rawDataSize 0' ' serialization.ddl struct srcbucket_mapjoin_part_1 { i32 key, string value}' ' serialization.format 1' ' serialization.lib org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' ' totalSize 2750' ' transient_lastDdlTime !!UNIXTIME!!' ' 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:' ' bucket_count 2' ' bucket_field_name key' ' columns key,value' ' columns.types int:string' ' file.inputformat org.apache.hadoop.mapred.TextInputFormat' ' file.outputformat org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' ' location !!{hive.metastore.warehouse.dir}!!/bucketmapjoin7.db/srcbucket_mapjoin_part_1' ' name bucketmapjoin7.srcbucket_mapjoin_part_1' ' numFiles 2' ' numPartitions 1' ' numRows 0' ' partition_columns ds/hr' ' rawDataSize 0' ' serialization.ddl struct srcbucket_mapjoin_part_1 { i32 key, string value}' ' serialization.format 1' ' serialization.lib org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' ' totalSize 2750' ' transient_lastDdlTime !!UNIXTIME!!' ' serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' ' name: bucketmapjoin7.srcbucket_mapjoin_part_1' ' name: bucketmapjoin7.srcbucket_mapjoin_part_1' '' ' Stage: Stage-0' ' Fetch Operator' ' limit: 1' '' '' 154 rows selected >>> >>> SELECT /*+ MAPJOIN(b) */ a.key, b.value FROM srcbucket_mapjoin_part_1 a JOIN srcbucket_mapjoin_part_2 b ON a.key = b.key AND a.ds = '2008-04-08' AND b.ds = '2008-04-08' LIMIT 1; 'key','value' '165','val_165' 1 row selected >>> !record