Saving all output to "!!{outputDirectory}!!/load_dyn_part14.q.raw". Enter "record" with no arguments to stop it. >>> !run !!{qFileDirectory}!!/load_dyn_part14.q >>> >>> create table if not exists nzhang_part14 (key string) partitioned by (value string); No rows affected >>> >>> describe extended nzhang_part14; 'col_name','data_type','comment' 'key','string','' 'value','string','' '','','' 'Detailed Table Information','Table(tableName:nzhang_part14, dbName:load_dyn_part14, owner:!!{user.name}!!, createTime:!!UNIXTIME!!, lastAccessTime:0, retention:0, sd:StorageDescriptor(cols:[FieldSchema(name:key, type:string, comment:null), FieldSchema(name:value, type:string, comment:null)], location:!!{hive.metastore.warehouse.dir}!!/load_dyn_part14.db/nzhang_part14, inputFormat:org.apache.hadoop.mapred.TextInputFormat, outputFormat:org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat, compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null, serializationLib:org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, parameters:{serialization.format=1}), bucketCols:[], sortCols:[], parameters:{}, skewedInfo:SkewedInfo(skewedColNames:[], skewedColValues:[], skewedColValueLocationMaps:{})), partitionKeys:[FieldSchema(name:value, type:string, comment:null)], parameters:{transient_lastDdlTime=!!UNIXTIME!!}, viewOriginalText:null, viewExpandedText:null, tableType:MANAGED_TABLE)','' 4 rows selected >>> >>> set hive.exec.dynamic.partition=true; No rows affected >>> set hive.exec.dynamic.partition.mode=nonstrict; No rows affected >>> >>> explain insert overwrite table nzhang_part14 partition(value) select key, value from ( select 'k1' as key, cast(null as string) as value from src limit 2 union all select 'k2' as key, '' as value from src limit 2 union all select 'k3' as key, ' ' as value from src limit 2 ) T; 'Explain' 'ABSTRACT SYNTAX TREE:' ' (TOK_QUERY (TOK_FROM (TOK_SUBQUERY (TOK_UNION (TOK_UNION (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME src))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR 'k1' key) (TOK_SELEXPR (TOK_FUNCTION TOK_STRING TOK_NULL) value)) (TOK_LIMIT 2))) (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME src))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR 'k2' key) (TOK_SELEXPR '' value)) (TOK_LIMIT 2)))) (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME src))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR 'k3' key) (TOK_SELEXPR ' ' value)) (TOK_LIMIT 2)))) T)) (TOK_INSERT (TOK_DESTINATION (TOK_TAB (TOK_TABNAME nzhang_part14) (TOK_PARTSPEC (TOK_PARTVAL value)))) (TOK_SELECT (TOK_SELEXPR (TOK_TABLE_OR_COL key)) (TOK_SELEXPR (TOK_TABLE_OR_COL value)))))' '' 'STAGE DEPENDENCIES:' ' Stage-1 is a root stage' ' Stage-2 depends on stages: Stage-1, Stage-9, Stage-10' ' Stage-8 depends on stages: Stage-2 , consists of Stage-5, Stage-4, Stage-6' ' Stage-5' ' Stage-0 depends on stages: Stage-5, Stage-4, Stage-7' ' Stage-3 depends on stages: Stage-0' ' Stage-4' ' Stage-6' ' Stage-7 depends on stages: Stage-6' ' Stage-9 is a root stage' ' Stage-10 is a root stage' '' 'STAGE PLANS:' ' Stage: Stage-1' ' Map Reduce' ' Alias -> Map Operator Tree:' ' null-subquery1-subquery2:t-subquery1-subquery2:src ' ' TableScan' ' alias: src' ' Select Operator' ' expressions:' ' expr: 'k2'' ' type: string' ' expr: ''' ' type: string' ' outputColumnNames: _col0, _col1' ' Limit' ' Reduce Output Operator' ' sort order: ' ' tag: -1' ' value expressions:' ' expr: _col0' ' type: string' ' expr: _col1' ' type: string' ' Reduce Operator Tree:' ' Extract' ' Limit' ' 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: load_dyn_part14.nzhang_part14' ' 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: load_dyn_part14.nzhang_part14' ' 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: load_dyn_part14.nzhang_part14' '' ' Stage: Stage-8' ' Conditional Operator' '' ' Stage: Stage-5' ' Move Operator' ' files:' ' hdfs directory: true' ' destination: pfile:!!{hive.exec.scratchdir}!!' '' ' Stage: Stage-0' ' Move Operator' ' tables:' ' partition:' ' value ' ' 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: load_dyn_part14.nzhang_part14' '' ' Stage: Stage-3' ' Stats-Aggr Operator' '' ' Stage: Stage-4' ' Map Reduce' ' Alias -> Map Operator Tree:' ' pfile:!!{hive.exec.scratchdir}!! ' ' File Output Operator' ' compressed: false' ' GlobalTableId: 0' ' 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: load_dyn_part14.nzhang_part14' '' ' Stage: Stage-6' ' Map Reduce' ' Alias -> Map Operator Tree:' ' pfile:!!{hive.exec.scratchdir}!! ' ' File Output Operator' ' compressed: false' ' GlobalTableId: 0' ' 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: load_dyn_part14.nzhang_part14' '' ' Stage: Stage-7' ' Move Operator' ' files:' ' hdfs directory: true' ' destination: pfile:!!{hive.exec.scratchdir}!!' '' ' Stage: Stage-9' ' Map Reduce' ' Alias -> Map Operator Tree:' ' null-subquery2:t-subquery2:src ' ' TableScan' ' alias: src' ' Select Operator' ' expressions:' ' expr: 'k3'' ' type: string' ' expr: ' '' ' type: string' ' outputColumnNames: _col0, _col1' ' Limit' ' Reduce Output Operator' ' sort order: ' ' tag: -1' ' value expressions:' ' expr: _col0' ' type: string' ' expr: _col1' ' type: string' ' Reduce Operator Tree:' ' Extract' ' Limit' ' 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-10' ' Map Reduce' ' Alias -> Map Operator Tree:' ' null-subquery1-subquery1:t-subquery1-subquery1:src ' ' TableScan' ' alias: src' ' Select Operator' ' expressions:' ' expr: 'k1'' ' type: string' ' expr: UDFToString(null)' ' type: string' ' outputColumnNames: _col0, _col1' ' Limit' ' Reduce Output Operator' ' sort order: ' ' tag: -1' ' value expressions:' ' expr: _col0' ' type: string' ' expr: _col1' ' type: string' ' Reduce Operator Tree:' ' Extract' ' Limit' ' File Output Operator' ' compressed: false' ' GlobalTableId: 0' ' table:' ' input format: org.apache.hadoop.mapred.SequenceFileInputFormat' ' output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat' '' '' 228 rows selected >>> >>> insert overwrite table nzhang_part14 partition(value) select key, value from ( select 'k1' as key, cast(null as string) as value from src limit 2 union all select 'k2' as key, '' as value from src limit 2 union all select 'k3' as key, ' ' as value from src limit 2 ) T; 'key','value' No rows selected >>> >>> >>> show partitions nzhang_part14; 'partition' 'value= ' 'value=__HIVE_DEFAULT_PARTITION__' 2 rows selected >>> >>> select * from nzhang_part14 where value <> 'a' order by key, value; 'key','value' 'k1','__HIVE_DEFAULT_PARTITION__' 'k1','__HIVE_DEFAULT_PARTITION__' 'k2','__HIVE_DEFAULT_PARTITION__' 'k2','__HIVE_DEFAULT_PARTITION__' 'k3',' ' 'k3',' ' 6 rows selected >>> >>> >>> !record