Saving all output to "!!{outputDirectory}!!/input13.q.raw". Enter "record" with no arguments to stop it. >>> !run !!{qFileDirectory}!!/input13.q >>> CREATE TABLE dest1(key INT, value STRING) STORED AS TEXTFILE; No rows affected >>> CREATE TABLE dest2(key INT, value STRING) STORED AS TEXTFILE; No rows affected >>> CREATE TABLE dest3(key INT) PARTITIONED BY(ds STRING, hr STRING) STORED AS TEXTFILE; No rows affected >>> >>> EXPLAIN FROM src INSERT OVERWRITE TABLE dest1 SELECT src.* WHERE src.key < 100 INSERT OVERWRITE TABLE dest2 SELECT src.key, src.value WHERE src.key >= 100 and src.key < 200 INSERT OVERWRITE TABLE dest3 PARTITION(ds='2008-04-08', hr='12') SELECT src.key WHERE src.key >= 200 and src.key < 300 INSERT OVERWRITE DIRECTORY '../build/ql/test/data/warehouse/dest4.out' SELECT src.value WHERE src.key >= 300; 'Explain' 'ABSTRACT SYNTAX TREE:' ' (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME src))) (TOK_INSERT (TOK_DESTINATION (TOK_TAB (TOK_TABNAME dest1))) (TOK_SELECT (TOK_SELEXPR (TOK_ALLCOLREF (TOK_TABNAME src)))) (TOK_WHERE (< (. (TOK_TABLE_OR_COL src) key) 100))) (TOK_INSERT (TOK_DESTINATION (TOK_TAB (TOK_TABNAME dest2))) (TOK_SELECT (TOK_SELEXPR (. (TOK_TABLE_OR_COL src) key)) (TOK_SELEXPR (. (TOK_TABLE_OR_COL src) value))) (TOK_WHERE (and (>= (. (TOK_TABLE_OR_COL src) key) 100) (< (. (TOK_TABLE_OR_COL src) key) 200)))) (TOK_INSERT (TOK_DESTINATION (TOK_TAB (TOK_TABNAME dest3) (TOK_PARTSPEC (TOK_PARTVAL ds '2008-04-08') (TOK_PARTVAL hr '12')))) (TOK_SELECT (TOK_SELEXPR (. (TOK_TABLE_OR_COL src) key))) (TOK_WHERE (and (>= (. (TOK_TABLE_OR_COL src) key) 200) (< (. (TOK_TABLE_OR_COL src) key) 300)))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR '../build/ql/test/data/warehouse/dest4.out')) (TOK_SELECT (TOK_SELEXPR (. (TOK_TABLE_OR_COL src) value))) (TOK_WHERE (>= (. (TOK_TABLE_OR_COL src) key) 300))))' '' 'STAGE DEPENDENCIES:' ' Stage-4 is a root stage' ' Stage-10 depends on stages: Stage-4 , consists of Stage-7, Stage-6, Stage-8' ' Stage-7' ' Stage-0 depends on stages: Stage-7, Stage-6, Stage-9' ' Stage-5 depends on stages: Stage-0' ' Stage-6' ' Stage-8' ' Stage-9 depends on stages: Stage-8' ' Stage-16 depends on stages: Stage-4 , consists of Stage-13, Stage-12, Stage-14' ' Stage-13' ' Stage-1 depends on stages: Stage-13, Stage-12, Stage-15' ' Stage-11 depends on stages: Stage-1' ' Stage-12' ' Stage-14' ' Stage-15 depends on stages: Stage-14' ' Stage-22 depends on stages: Stage-4 , consists of Stage-19, Stage-18, Stage-20' ' Stage-19' ' Stage-2 depends on stages: Stage-19, Stage-18, Stage-21' ' Stage-17 depends on stages: Stage-2' ' Stage-18' ' Stage-20' ' Stage-21 depends on stages: Stage-20' ' Stage-27 depends on stages: Stage-4 , consists of Stage-24, Stage-23, Stage-25' ' Stage-24' ' Stage-3 depends on stages: Stage-24, Stage-23, Stage-26' ' Stage-23' ' Stage-25' ' Stage-26 depends on stages: Stage-25' '' 'STAGE PLANS:' ' Stage: Stage-4' ' Map Reduce' ' Alias -> Map Operator Tree:' ' src ' ' TableScan' ' alias: src' ' Filter Operator' ' predicate:' ' expr: (key < 100.0)' ' type: boolean' ' Select Operator' ' expressions:' ' expr: key' ' type: string' ' expr: value' ' type: string' ' outputColumnNames: _col0, _col1' ' Select Operator' ' expressions:' ' expr: UDFToInteger(_col0)' ' type: int' ' 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: input13.dest1' ' Filter Operator' ' predicate:' ' expr: ((key >= 100.0) and (key < 200.0))' ' type: boolean' ' Select Operator' ' expressions:' ' expr: key' ' type: string' ' expr: value' ' type: string' ' outputColumnNames: _col0, _col1' ' Select Operator' ' expressions:' ' expr: UDFToInteger(_col0)' ' type: int' ' expr: _col1' ' type: string' ' outputColumnNames: _col0, _col1' ' File Output Operator' ' compressed: false' ' GlobalTableId: 2' ' 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: input13.dest2' ' Filter Operator' ' predicate:' ' expr: ((key >= 200.0) and (key < 300.0))' ' type: boolean' ' Select Operator' ' expressions:' ' expr: key' ' type: string' ' outputColumnNames: _col0' ' Select Operator' ' expressions:' ' expr: UDFToInteger(_col0)' ' type: int' ' outputColumnNames: _col0' ' File Output Operator' ' compressed: false' ' GlobalTableId: 3' ' 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: input13.dest3' ' Filter Operator' ' predicate:' ' expr: (key >= 300.0)' ' type: boolean' ' Select Operator' ' expressions:' ' expr: value' ' type: string' ' outputColumnNames: _col0' ' File Output Operator' ' compressed: false' ' GlobalTableId: 4' ' table:' ' input format: org.apache.hadoop.mapred.TextInputFormat' ' output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' '' ' Stage: Stage-10' ' Conditional Operator' '' ' Stage: Stage-7' ' Move Operator' ' files:' ' hdfs directory: true' ' destination: pfile:!!{hive.exec.scratchdir}!!' '' ' 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: input13.dest1' '' ' Stage: Stage-5' ' Stats-Aggr Operator' '' ' 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: input13.dest1' '' ' Stage: Stage-8' ' 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: input13.dest1' '' ' Stage: Stage-9' ' Move Operator' ' files:' ' hdfs directory: true' ' destination: pfile:!!{hive.exec.scratchdir}!!' '' ' Stage: Stage-16' ' Conditional Operator' '' ' Stage: Stage-13' ' Move Operator' ' files:' ' hdfs directory: true' ' destination: pfile:!!{hive.exec.scratchdir}!!' '' ' Stage: Stage-1' ' 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: input13.dest2' '' ' Stage: Stage-11' ' Stats-Aggr Operator' '' ' Stage: Stage-12' ' 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: input13.dest2' '' ' Stage: Stage-14' ' 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: input13.dest2' '' ' Stage: Stage-15' ' Move Operator' ' files:' ' hdfs directory: true' ' destination: pfile:!!{hive.exec.scratchdir}!!' '' ' Stage: Stage-22' ' Conditional Operator' '' ' Stage: Stage-19' ' Move Operator' ' files:' ' hdfs directory: true' ' destination: pfile:!!{hive.exec.scratchdir}!!' '' ' Stage: Stage-2' ' Move Operator' ' tables:' ' partition:' ' ds 2008-04-08' ' hr 12' ' 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: input13.dest3' '' ' Stage: Stage-17' ' Stats-Aggr Operator' '' ' Stage: Stage-18' ' 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: input13.dest3' '' ' Stage: Stage-20' ' 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: input13.dest3' '' ' Stage: Stage-21' ' Move Operator' ' files:' ' hdfs directory: true' ' destination: pfile:!!{hive.exec.scratchdir}!!' '' ' Stage: Stage-27' ' Conditional Operator' '' ' Stage: Stage-24' ' Move Operator' ' files:' ' hdfs directory: true' ' destination: file:!!{hive.exec.scratchdir}!!' '' ' Stage: Stage-3' ' Move Operator' ' files:' ' hdfs directory: true' ' destination: ../build/ql/test/data/warehouse/dest4.out' '' ' Stage: Stage-23' ' Map Reduce' ' Alias -> Map Operator Tree:' ' file:!!{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' '' ' Stage: Stage-25' ' Map Reduce' ' Alias -> Map Operator Tree:' ' file:!!{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' '' ' Stage: Stage-26' ' Move Operator' ' files:' ' hdfs directory: true' ' destination: file:!!{hive.exec.scratchdir}!!' '' '' 339 rows selected >>> >>> FROM src INSERT OVERWRITE TABLE dest1 SELECT src.* WHERE src.key < 100 INSERT OVERWRITE TABLE dest2 SELECT src.key, src.value WHERE src.key >= 100 and src.key < 200 INSERT OVERWRITE TABLE dest3 PARTITION(ds='2008-04-08', hr='12') SELECT src.key WHERE src.key >= 200 and src.key < 300 INSERT OVERWRITE DIRECTORY '../build/ql/test/data/warehouse/dest4.out' SELECT src.value WHERE src.key >= 300; 'value' No rows selected >>> >>> SELECT dest1.* FROM dest1; 'key','value' '86','val_86' '27','val_27' '98','val_98' '66','val_66' '37','val_37' '15','val_15' '82','val_82' '17','val_17' '0','val_0' '57','val_57' '20','val_20' '92','val_92' '47','val_47' '72','val_72' '4','val_4' '35','val_35' '54','val_54' '51','val_51' '65','val_65' '83','val_83' '12','val_12' '67','val_67' '84','val_84' '58','val_58' '8','val_8' '24','val_24' '42','val_42' '0','val_0' '96','val_96' '26','val_26' '51','val_51' '43','val_43' '95','val_95' '98','val_98' '85','val_85' '77','val_77' '0','val_0' '87','val_87' '15','val_15' '72','val_72' '90','val_90' '19','val_19' '10','val_10' '5','val_5' '58','val_58' '35','val_35' '95','val_95' '11','val_11' '34','val_34' '42','val_42' '78','val_78' '76','val_76' '41','val_41' '30','val_30' '64','val_64' '76','val_76' '74','val_74' '69','val_69' '33','val_33' '70','val_70' '5','val_5' '2','val_2' '35','val_35' '80','val_80' '44','val_44' '53','val_53' '90','val_90' '12','val_12' '5','val_5' '70','val_70' '24','val_24' '70','val_70' '83','val_83' '26','val_26' '67','val_67' '18','val_18' '9','val_9' '18','val_18' '97','val_97' '84','val_84' '28','val_28' '37','val_37' '90','val_90' '97','val_97' 84 rows selected >>> SELECT dest2.* FROM dest2; 'key','value' '165','val_165' '193','val_193' '150','val_150' '128','val_128' '146','val_146' '152','val_152' '145','val_145' '166','val_166' '153','val_153' '193','val_193' '174','val_174' '199','val_199' '174','val_174' '162','val_162' '167','val_167' '195','val_195' '113','val_113' '155','val_155' '128','val_128' '149','val_149' '129','val_129' '170','val_170' '157','val_157' '111','val_111' '169','val_169' '125','val_125' '192','val_192' '187','val_187' '176','val_176' '138','val_138' '103','val_103' '176','val_176' '137','val_137' '180','val_180' '181','val_181' '138','val_138' '179','val_179' '172','val_172' '129','val_129' '158','val_158' '119','val_119' '197','val_197' '100','val_100' '199','val_199' '191','val_191' '165','val_165' '120','val_120' '131','val_131' '156','val_156' '196','val_196' '197','val_197' '187','val_187' '137','val_137' '169','val_169' '179','val_179' '118','val_118' '134','val_134' '138','val_138' '118','val_118' '177','val_177' '168','val_168' '143','val_143' '160','val_160' '195','val_195' '119','val_119' '149','val_149' '138','val_138' '103','val_103' '113','val_113' '167','val_167' '116','val_116' '191','val_191' '128','val_128' '193','val_193' '104','val_104' '175','val_175' '105','val_105' '190','val_190' '114','val_114' '164','val_164' '125','val_125' '164','val_164' '187','val_187' '104','val_104' '163','val_163' '119','val_119' '199','val_199' '120','val_120' '169','val_169' '178','val_178' '136','val_136' '172','val_172' '133','val_133' '175','val_175' '189','val_189' '134','val_134' '100','val_100' '146','val_146' '186','val_186' '167','val_167' '183','val_183' '152','val_152' '194','val_194' '126','val_126' '169','val_169' 105 rows selected >>> SELECT dest3.* FROM dest3; 'key','ds','hr' '238','2008-04-08','12' '255','2008-04-08','12' '278','2008-04-08','12' '265','2008-04-08','12' '273','2008-04-08','12' '224','2008-04-08','12' '213','2008-04-08','12' '281','2008-04-08','12' '277','2008-04-08','12' '209','2008-04-08','12' '252','2008-04-08','12' '292','2008-04-08','12' '219','2008-04-08','12' '287','2008-04-08','12' '237','2008-04-08','12' '207','2008-04-08','12' '208','2008-04-08','12' '247','2008-04-08','12' '266','2008-04-08','12' '203','2008-04-08','12' '205','2008-04-08','12' '221','2008-04-08','12' '280','2008-04-08','12' '277','2008-04-08','12' '208','2008-04-08','12' '286','2008-04-08','12' '239','2008-04-08','12' '213','2008-04-08','12' '216','2008-04-08','12' '278','2008-04-08','12' '289','2008-04-08','12' '221','2008-04-08','12' '275','2008-04-08','12' '241','2008-04-08','12' '284','2008-04-08','12' '230','2008-04-08','12' '260','2008-04-08','12' '272','2008-04-08','12' '217','2008-04-08','12' '230','2008-04-08','12' '208','2008-04-08','12' '298','2008-04-08','12' '230','2008-04-08','12' '205','2008-04-08','12' '288','2008-04-08','12' '282','2008-04-08','12' '282','2008-04-08','12' '238','2008-04-08','12' '277','2008-04-08','12' '273','2008-04-08','12' '224','2008-04-08','12' '242','2008-04-08','12' '272','2008-04-08','12' '242','2008-04-08','12' '226','2008-04-08','12' '229','2008-04-08','12' '233','2008-04-08','12' '223','2008-04-08','12' '218','2008-04-08','12' '228','2008-04-08','12' '209','2008-04-08','12' '230','2008-04-08','12' '296','2008-04-08','12' '216','2008-04-08','12' '274','2008-04-08','12' '219','2008-04-08','12' '239','2008-04-08','12' '223','2008-04-08','12' '256','2008-04-08','12' '263','2008-04-08','12' '288','2008-04-08','12' '244','2008-04-08','12' '202','2008-04-08','12' '229','2008-04-08','12' '280','2008-04-08','12' '283','2008-04-08','12' '235','2008-04-08','12' '257','2008-04-08','12' '258','2008-04-08','12' '203','2008-04-08','12' '262','2008-04-08','12' '201','2008-04-08','12' '217','2008-04-08','12' '298','2008-04-08','12' '291','2008-04-08','12' '255','2008-04-08','12' '200','2008-04-08','12' '237','2008-04-08','12' '248','2008-04-08','12' '277','2008-04-08','12' '230','2008-04-08','12' '207','2008-04-08','12' '249','2008-04-08','12' '265','2008-04-08','12' '214','2008-04-08','12' '233','2008-04-08','12' '256','2008-04-08','12' '298','2008-04-08','12' '285','2008-04-08','12' '273','2008-04-08','12' '281','2008-04-08','12' '222','2008-04-08','12' '200','2008-04-08','12' 103 rows selected >>> dfs -cat ../build/ql/test/data/warehouse/dest4.out/*; No rows affected >>> !record