Saving all output to "!!{outputDirectory}!!/stats1.q.raw". Enter "record" with no arguments to stop it. >>> !run !!{qFileDirectory}!!/stats1.q >>> set datanucleus.cache.collections=false; No rows affected >>> set hive.stats.autogather=true; No rows affected >>> set hive.merge.mapfiles=false; No rows affected >>> set hive.merge.mapredfiles=false; No rows affected >>> set hive.map.aggr=true; No rows affected >>> >>> create table tmptable(key string, value string); No rows affected >>> >>> EXPLAIN INSERT OVERWRITE TABLE tmptable SELECT unionsrc.key, unionsrc.value FROM (SELECT 'tst1' AS key, cast(count(1) AS string) AS value FROM src s1 UNION ALL SELECT s2.key AS key, s2.value AS value FROM src1 s2) unionsrc; 'Explain' 'ABSTRACT SYNTAX TREE:' ' (TOK_QUERY (TOK_FROM (TOK_SUBQUERY (TOK_UNION (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME src) s1)) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR 'tst1' key) (TOK_SELEXPR (TOK_FUNCTION TOK_STRING (TOK_FUNCTION count 1)) value)))) (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME src1) s2)) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (. (TOK_TABLE_OR_COL s2) key) key) (TOK_SELEXPR (. (TOK_TABLE_OR_COL s2) value) value))))) unionsrc)) (TOK_INSERT (TOK_DESTINATION (TOK_TAB (TOK_TABNAME tmptable))) (TOK_SELECT (TOK_SELEXPR (. (TOK_TABLE_OR_COL unionsrc) key)) (TOK_SELEXPR (. (TOK_TABLE_OR_COL unionsrc) value)))))' '' 'STAGE DEPENDENCIES:' ' Stage-1 is a root stage' ' Stage-2 depends on stages: Stage-1' ' Stage-0 depends on stages: Stage-2' ' Stage-3 depends on stages: Stage-0' '' 'STAGE PLANS:' ' Stage: Stage-1' ' Map Reduce' ' Alias -> Map Operator Tree:' ' null-subquery1:unionsrc-subquery1:s1 ' ' TableScan' ' alias: s1' ' Select Operator' ' Group By Operator' ' aggregations:' ' expr: count(1)' ' bucketGroup: false' ' mode: hash' ' outputColumnNames: _col0' ' Reduce Output Operator' ' sort order: ' ' tag: -1' ' value expressions:' ' expr: _col0' ' type: bigint' ' Reduce Operator Tree:' ' Group By Operator' ' aggregations:' ' expr: count(VALUE._col0)' ' bucketGroup: false' ' mode: mergepartial' ' outputColumnNames: _col0' ' Select Operator' ' expressions:' ' expr: 'tst1'' ' type: string' ' expr: UDFToString(_col0)' ' type: string' ' outputColumnNames: _col0, _col1' ' File Output Operator' ' compressed: false' ' GlobalTableId: 0' ' table:' ' input format: org.apache.hadoop.mapred.SequenceFileInputFormat' ' output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat' '' ' Stage: Stage-2' ' Map Reduce' ' Alias -> Map Operator Tree:' ' file:!!{hive.exec.scratchdir}!! ' ' TableScan' ' Union' ' Select Operator' ' expressions:' ' expr: _col0' ' type: string' ' expr: _col1' ' type: string' ' outputColumnNames: _col0, _col1' ' File Output Operator' ' compressed: false' ' GlobalTableId: 1' ' table:' ' input format: org.apache.hadoop.mapred.TextInputFormat' ' output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' ' serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' ' name: stats1.tmptable' ' null-subquery2:unionsrc-subquery2:s2 ' ' TableScan' ' alias: s2' ' Select Operator' ' expressions:' ' expr: key' ' type: string' ' expr: value' ' type: string' ' outputColumnNames: _col0, _col1' ' Union' ' Select Operator' ' expressions:' ' expr: _col0' ' type: string' ' expr: _col1' ' type: string' ' outputColumnNames: _col0, _col1' ' File Output Operator' ' compressed: false' ' GlobalTableId: 1' ' table:' ' input format: org.apache.hadoop.mapred.TextInputFormat' ' output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' ' serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' ' name: stats1.tmptable' '' ' Stage: Stage-0' ' Move Operator' ' tables:' ' replace: true' ' table:' ' input format: org.apache.hadoop.mapred.TextInputFormat' ' output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' ' serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' ' name: stats1.tmptable' '' ' Stage: Stage-3' ' Stats-Aggr Operator' '' '' 112 rows selected >>> >>> INSERT OVERWRITE TABLE tmptable SELECT unionsrc.key, unionsrc.value FROM (SELECT 'tst1' AS key, cast(count(1) AS string) AS value FROM src s1 UNION ALL SELECT s2.key AS key, s2.value AS value FROM src1 s2) unionsrc; 'key','value' No rows selected >>> >>> SELECT * FROM tmptable x SORT BY x.key, x.value; 'key','value' '','' '','' '','' '','' '','val_165' '','val_193' '','val_265' '','val_27' '','val_409' '','val_484' '128','' '146','val_146' '150','val_150' '213','val_213' '224','' '238','val_238' '255','val_255' '273','val_273' '278','val_278' '311','val_311' '369','' '401','val_401' '406','val_406' '66','val_66' '98','val_98' 'tst1','500' 26 rows selected >>> >>> DESCRIBE FORMATTED tmptable; 'col_name','data_type','comment' '# col_name ','data_type ','comment ' '','','' 'key ','string ','None ' 'value ','string ','None ' '','','' '# Detailed Table Information','','' 'Database: ','stats1 ','' 'Owner: ','!!{user.name}!! ','' 'CreateTime: ','!!TIMESTAMP!!','' 'LastAccessTime: ','UNKNOWN ','' 'Protect Mode: ','None ','' 'Retention: ','0 ','' 'Location: ','!!{hive.metastore.warehouse.dir}!!/stats1.db/tmptable','' 'Table Type: ','MANAGED_TABLE ','' 'Table Parameters:','','' '','numFiles ','2 ' '','numPartitions ','0 ' '','numRows ','26 ' '','rawDataSize ','199 ' '','totalSize ','225 ' '','transient_lastDdlTime','!!UNIXTIME!! ' '','','' '# Storage Information','','' 'SerDe Library: ','org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe','' 'InputFormat: ','org.apache.hadoop.mapred.TextInputFormat','' 'OutputFormat: ','org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat','' 'Compressed: ','No ','' 'Num Buckets: ','-1 ','' 'Bucket Columns: ','[] ','' 'Sort Columns: ','[] ','' 'Storage Desc Params:','','' '','serialization.format','1 ' 32 rows selected >>> >>> -- Load a file into a existing table >>> -- Some stats (numFiles, totalSize) should be updated correctly >>> -- Some other stats (numRows, rawDataSize) should be cleared >>> load data local inpath '../data/files/srcbucket20.txt' INTO TABLE tmptable; No rows affected >>> DESCRIBE FORMATTED tmptable; 'col_name','data_type','comment' '# col_name ','data_type ','comment ' '','','' 'key ','string ','None ' 'value ','string ','None ' '','','' '# Detailed Table Information','','' 'Database: ','stats1 ','' 'Owner: ','!!{user.name}!! ','' 'CreateTime: ','!!TIMESTAMP!!','' 'LastAccessTime: ','UNKNOWN ','' 'Protect Mode: ','None ','' 'Retention: ','0 ','' 'Location: ','!!{hive.metastore.warehouse.dir}!!/stats1.db/tmptable','' 'Table Type: ','MANAGED_TABLE ','' 'Table Parameters:','','' '','numFiles ','3 ' '','numPartitions ','0 ' '','numRows ','0 ' '','rawDataSize ','0 ' '','totalSize ','1583 ' '','transient_lastDdlTime','!!UNIXTIME!! ' '','','' '# Storage Information','','' 'SerDe Library: ','org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe','' 'InputFormat: ','org.apache.hadoop.mapred.TextInputFormat','' 'OutputFormat: ','org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat','' 'Compressed: ','No ','' 'Num Buckets: ','-1 ','' 'Bucket Columns: ','[] ','' 'Sort Columns: ','[] ','' 'Storage Desc Params:','','' '','serialization.format','1 ' 32 rows selected >>> !record