Saving all output to "!!{outputDirectory}!!/smb_mapjoin9.q.raw". Enter "record" with no arguments to stop it.
>>>  !run !!{qFileDirectory}!!/smb_mapjoin9.q
>>>  create table hive_test_smb_bucket1 (key int, value string) partitioned by (ds string) clustered by (key) sorted by (key) into 2 buckets;
No rows affected 
>>>  create table hive_test_smb_bucket2 (key int, value string) partitioned by (ds string) clustered by (key) sorted by (key) into 2 buckets;
No rows affected 
>>>  
>>>  set hive.optimize.bucketmapjoin = true;
No rows affected 
>>>  set hive.optimize.bucketmapjoin.sortedmerge = true;
No rows affected 
>>>  set hive.input.format = org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;
No rows affected 
>>>  
>>>  -- empty partitions (HIVE-3205)
>>>  explain extended 
SELECT /* + MAPJOIN(b) */ b.key as k1, b.value, b.ds, a.key as k2 
FROM hive_test_smb_bucket1 a JOIN 
hive_test_smb_bucket2 b 
ON a.key = b.key WHERE a.ds = '2010-10-15' and b.ds='2010-10-15' and  b.key IS NOT NULL;
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_JOIN (TOK_TABREF (TOK_TABNAME hive_test_smb_bucket1) a) (TOK_TABREF (TOK_TABNAME hive_test_smb_bucket2) b) (= (. (TOK_TABLE_OR_COL a) key) (. (TOK_TABLE_OR_COL b) key)))) (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 b) key) k1) (TOK_SELEXPR (. (TOK_TABLE_OR_COL b) value)) (TOK_SELEXPR (. (TOK_TABLE_OR_COL b) ds)) (TOK_SELEXPR (. (TOK_TABLE_OR_COL a) key) k2)) (TOK_WHERE (and (and (= (. (TOK_TABLE_OR_COL a) ds) '2010-10-15') (= (. (TOK_TABLE_OR_COL b) ds) '2010-10-15')) (TOK_FUNCTION TOK_ISNOTNULL (. (TOK_TABLE_OR_COL b) key))))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        a '
'          TableScan'
'            alias: a'
'            GatherStats: false'
'            Filter Operator'
'              isSamplingPred: false'
'              predicate:'
'                  expr: ((ds = '2010-10-15') and key is not null)'
'                  type: boolean'
'              Sorted Merge Bucket Map Join Operator'
'                condition map:'
'                     Inner Join 0 to 1'
'                condition expressions:'
'                  0 {key} {ds}'
'                  1 {key} {value} {ds}'
'                handleSkewJoin: false'
'                keys:'
'                  0 [Column[key]]'
'                  1 [Column[key]]'
'                outputColumnNames: _col0, _col2, _col5, _col6, _col7'
'                Position of Big Table: 0'
'                Select Operator'
'                  expressions:'
'                        expr: _col0'
'                        type: int'
'                        expr: _col2'
'                        type: string'
'                        expr: _col5'
'                        type: int'
'                        expr: _col6'
'                        type: string'
'                        expr: _col7'
'                        type: string'
'                  outputColumnNames: _col0, _col2, _col5, _col6, _col7'
'                  Select Operator'
'                    expressions:'
'                          expr: _col5'
'                          type: int'
'                          expr: _col6'
'                          type: string'
'                          expr: _col7'
'                          type: string'
'                          expr: _col0'
'                          type: int'
'                    outputColumnNames: _col0, _col1, _col2, _col3'
'                    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,_col2,_col3'
'                            columns.types int:string:string:int'
'                            escape.delim \'
'                            serialization.format 1'
'                      TotalFiles: 1'
'                      GatherStats: false'
'                      MultiFileSpray: false'
'      Needs Tagging: false'
''
'  Stage: Stage-0'
'    Fetch Operator'
'      limit: -1'
''
''
80 rows selected 
>>>  
>>>  SELECT /* + MAPJOIN(b) */ b.key as k1, b.value, b.ds, a.key as k2 
FROM hive_test_smb_bucket1 a JOIN 
hive_test_smb_bucket2 b 
ON a.key = b.key WHERE a.ds = '2010-10-15' and b.ds='2010-10-15' and  b.key IS NOT NULL;
'k1','value','ds','k2'
No rows selected 
>>>  
>>>  explain extended 
SELECT /* + MAPJOIN(a) */ b.key as k1, b.value, b.ds, a.key as k2 
FROM hive_test_smb_bucket1 a JOIN 
hive_test_smb_bucket2 b 
ON a.key = b.key WHERE a.ds = '2010-10-15' and b.ds='2010-10-15' and  b.key IS NOT NULL;
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_JOIN (TOK_TABREF (TOK_TABNAME hive_test_smb_bucket1) a) (TOK_TABREF (TOK_TABNAME hive_test_smb_bucket2) b) (= (. (TOK_TABLE_OR_COL a) key) (. (TOK_TABLE_OR_COL b) key)))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_HINTLIST (TOK_HINT TOK_MAPJOIN (TOK_HINTARGLIST a))) (TOK_SELEXPR (. (TOK_TABLE_OR_COL b) key) k1) (TOK_SELEXPR (. (TOK_TABLE_OR_COL b) value)) (TOK_SELEXPR (. (TOK_TABLE_OR_COL b) ds)) (TOK_SELEXPR (. (TOK_TABLE_OR_COL a) key) k2)) (TOK_WHERE (and (and (= (. (TOK_TABLE_OR_COL a) ds) '2010-10-15') (= (. (TOK_TABLE_OR_COL b) ds) '2010-10-15')) (TOK_FUNCTION TOK_ISNOTNULL (. (TOK_TABLE_OR_COL b) key))))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        b '
'          TableScan'
'            alias: b'
'            GatherStats: false'
'            Filter Operator'
'              isSamplingPred: false'
'              predicate:'
'                  expr: ((ds = '2010-10-15') and key is not null)'
'                  type: boolean'
'              Sorted Merge Bucket Map Join Operator'
'                condition map:'
'                     Inner Join 0 to 1'
'                condition expressions:'
'                  0 {key} {ds}'
'                  1 {key} {value} {ds}'
'                handleSkewJoin: false'
'                keys:'
'                  0 [Column[key]]'
'                  1 [Column[key]]'
'                outputColumnNames: _col0, _col2, _col5, _col6, _col7'
'                Position of Big Table: 1'
'                Select Operator'
'                  expressions:'
'                        expr: _col0'
'                        type: int'
'                        expr: _col2'
'                        type: string'
'                        expr: _col5'
'                        type: int'
'                        expr: _col6'
'                        type: string'
'                        expr: _col7'
'                        type: string'
'                  outputColumnNames: _col0, _col2, _col5, _col6, _col7'
'                  Select Operator'
'                    expressions:'
'                          expr: _col5'
'                          type: int'
'                          expr: _col6'
'                          type: string'
'                          expr: _col7'
'                          type: string'
'                          expr: _col0'
'                          type: int'
'                    outputColumnNames: _col0, _col1, _col2, _col3'
'                    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,_col2,_col3'
'                            columns.types int:string:string:int'
'                            escape.delim \'
'                            serialization.format 1'
'                      TotalFiles: 1'
'                      GatherStats: false'
'                      MultiFileSpray: false'
'      Needs Tagging: false'
''
'  Stage: Stage-0'
'    Fetch Operator'
'      limit: -1'
''
''
80 rows selected 
>>>  
>>>  SELECT /* + MAPJOIN(a) */ b.key as k1, b.value, b.ds, a.key as k2 
FROM hive_test_smb_bucket1 a JOIN 
hive_test_smb_bucket2 b 
ON a.key = b.key WHERE a.ds = '2010-10-15' and b.ds='2010-10-15' and  b.key IS NOT NULL;
'k1','value','ds','k2'
No rows selected 
>>>  
>>>  set hive.enforce.bucketing = true;
No rows affected 
>>>  set hive.enforce.sorting = true;
No rows affected 
>>>  set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
No rows affected 
>>>  set hive.exec.reducers.max = 1;
No rows affected 
>>>  
>>>  insert overwrite table hive_test_smb_bucket1 partition (ds='2010-10-15') select key, value from src;
'_col0','_col1'
No rows selected 
>>>  insert overwrite table hive_test_smb_bucket2 partition (ds='2010-10-15') select key, value from src;
'_col0','_col1'
No rows selected 
>>>  
>>>  set hive.input.format = org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;
No rows affected 
>>>  
>>>  explain 
create table smb_mapjoin9_results as 
SELECT /* + MAPJOIN(b) */ b.key as k1, b.value, b.ds, a.key as k2 
FROM hive_test_smb_bucket1 a JOIN 
hive_test_smb_bucket2 b 
ON a.key = b.key WHERE a.ds = '2010-10-15' and b.ds='2010-10-15' and  b.key IS NOT NULL;
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_CREATETABLE (TOK_TABNAME smb_mapjoin9_results) TOK_LIKETABLE (TOK_QUERY (TOK_FROM (TOK_JOIN (TOK_TABREF (TOK_TABNAME hive_test_smb_bucket1) a) (TOK_TABREF (TOK_TABNAME hive_test_smb_bucket2) b) (= (. (TOK_TABLE_OR_COL a) key) (. (TOK_TABLE_OR_COL b) key)))) (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 b) key) k1) (TOK_SELEXPR (. (TOK_TABLE_OR_COL b) value)) (TOK_SELEXPR (. (TOK_TABLE_OR_COL b) ds)) (TOK_SELEXPR (. (TOK_TABLE_OR_COL a) key) k2)) (TOK_WHERE (and (and (= (. (TOK_TABLE_OR_COL a) ds) '2010-10-15') (= (. (TOK_TABLE_OR_COL b) ds) '2010-10-15')) (TOK_FUNCTION TOK_ISNOTNULL (. (TOK_TABLE_OR_COL b) key)))))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-0 depends on stages: Stage-1'
'  Stage-4 depends on stages: Stage-0'
'  Stage-2 depends on stages: Stage-4'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        a '
'          TableScan'
'            alias: a'
'            Filter Operator'
'              predicate:'
'                  expr: key is not null'
'                  type: boolean'
'              Sorted Merge Bucket Map Join Operator'
'                condition map:'
'                     Inner Join 0 to 1'
'                condition expressions:'
'                  0 {key} {ds}'
'                  1 {key} {value} {ds}'
'                handleSkewJoin: false'
'                keys:'
'                  0 [Column[key]]'
'                  1 [Column[key]]'
'                outputColumnNames: _col0, _col2, _col5, _col6, _col7'
'                Position of Big Table: 0'
'                Select Operator'
'                  expressions:'
'                        expr: _col0'
'                        type: int'
'                        expr: _col2'
'                        type: string'
'                        expr: _col5'
'                        type: int'
'                        expr: _col6'
'                        type: string'
'                        expr: _col7'
'                        type: string'
'                  outputColumnNames: _col0, _col2, _col5, _col6, _col7'
'                  Select Operator'
'                    expressions:'
'                          expr: _col5'
'                          type: int'
'                          expr: _col6'
'                          type: string'
'                          expr: _col7'
'                          type: string'
'                          expr: _col0'
'                          type: int'
'                    outputColumnNames: _col0, _col1, _col2, _col3'
'                    File Output Operator'
'                      compressed: false'
'                      GlobalTableId: 1'
'                      table:'
'                          input format: org.apache.hadoop.mapred.TextInputFormat'
'                          output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
'                          name: smb_mapjoin9.smb_mapjoin9_results'
''
'  Stage: Stage-0'
'    Move Operator'
'      files:'
'          hdfs directory: true'
'          destination: !!{hive.metastore.warehouse.dir}!!/smb_mapjoin9.db/smb_mapjoin9_results'
''
'  Stage: Stage-4'
'      Create Table Operator:'
'        Create Table'
'          columns: k1 int, value string, ds string, k2 int'
'          if not exists: false'
'          input format: org.apache.hadoop.mapred.TextInputFormat'
'          # buckets: -1'
'          output format: org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'
'          name: smb_mapjoin9_results'
'          isExternal: false'
''
'  Stage: Stage-2'
'    Stats-Aggr Operator'
''
''
85 rows selected 
>>>  
>>>  
>>>  create table smb_mapjoin9_results as 
SELECT /* + MAPJOIN(b) */ b.key as k1, b.value, b.ds, a.key as k2 
FROM hive_test_smb_bucket1 a JOIN 
hive_test_smb_bucket2 b 
ON a.key = b.key WHERE a.ds = '2010-10-15' and b.ds='2010-10-15' and  b.key IS NOT NULL;
'k1','value','ds','k2'
No rows selected 
>>>  
>>>  drop table smb_mapjoin9_results;
No rows affected 
>>>  drop table hive_test_smb_bucket1;
No rows affected 
>>>  drop table hive_test_smb_bucket2;
No rows affected 
>>>  !record