Saving all output to "!!{outputDirectory}!!/ppd2.q.raw". Enter "record" with no arguments to stop it.
>>>  !run !!{qFileDirectory}!!/ppd2.q
>>>  set hive.optimize.ppd=true;
No rows affected 
>>>  set hive.ppd.remove.duplicatefilters=true;
No rows affected 
>>>  
>>>  explain 
select b.key,b.cc 
from ( 
select a.* 
from ( 
select key, count(value) as cc 
from srcpart a 
where a.ds = '2008-04-08' and a.hr = '11' 
group by key 
)a 
distribute by a.key 
sort by a.key,a.cc desc) b 
where b.cc>1;
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_SUBQUERY (TOK_QUERY (TOK_FROM (TOK_SUBQUERY (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME srcpart) a)) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_TABLE_OR_COL key)) (TOK_SELEXPR (TOK_FUNCTION count (TOK_TABLE_OR_COL value)) cc)) (TOK_WHERE (and (= (. (TOK_TABLE_OR_COL a) ds) '2008-04-08') (= (. (TOK_TABLE_OR_COL a) hr) '11'))) (TOK_GROUPBY (TOK_TABLE_OR_COL key)))) a)) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_ALLCOLREF (TOK_TABNAME a)))) (TOK_DISTRIBUTEBY (. (TOK_TABLE_OR_COL a) key)) (TOK_SORTBY (TOK_TABSORTCOLNAMEASC (. (TOK_TABLE_OR_COL a) key)) (TOK_TABSORTCOLNAMEDESC (. (TOK_TABLE_OR_COL a) cc))))) b)) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (. (TOK_TABLE_OR_COL b) key)) (TOK_SELEXPR (. (TOK_TABLE_OR_COL b) cc))) (TOK_WHERE (> (. (TOK_TABLE_OR_COL b) cc) 1))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-2 depends on stages: Stage-1'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        b:a:a '
'          TableScan'
'            alias: a'
'            Select Operator'
'              expressions:'
'                    expr: key'
'                    type: string'
'                    expr: value'
'                    type: string'
'              outputColumnNames: key, value'
'              Group By Operator'
'                aggregations:'
'                      expr: count(value)'
'                bucketGroup: false'
'                keys:'
'                      expr: key'
'                      type: string'
'                mode: hash'
'                outputColumnNames: _col0, _col1'
'                Reduce Output Operator'
'                  key expressions:'
'                        expr: _col0'
'                        type: string'
'                  sort order: +'
'                  Map-reduce partition columns:'
'                        expr: _col0'
'                        type: string'
'                  tag: -1'
'                  value expressions:'
'                        expr: _col1'
'                        type: bigint'
'      Reduce Operator Tree:'
'        Group By Operator'
'          aggregations:'
'                expr: count(VALUE._col0)'
'          bucketGroup: false'
'          keys:'
'                expr: KEY._col0'
'                type: string'
'          mode: mergepartial'
'          outputColumnNames: _col0, _col1'
'          Select Operator'
'            expressions:'
'                  expr: _col0'
'                  type: string'
'                  expr: _col1'
'                  type: bigint'
'            outputColumnNames: _col0, _col1'
'            Select Operator'
'              expressions:'
'                    expr: _col0'
'                    type: string'
'                    expr: _col1'
'                    type: bigint'
'              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}!! '
'            Reduce Output Operator'
'              key expressions:'
'                    expr: _col0'
'                    type: string'
'                    expr: _col1'
'                    type: bigint'
'              sort order: +-'
'              Map-reduce partition columns:'
'                    expr: _col0'
'                    type: string'
'              tag: -1'
'              value expressions:'
'                    expr: _col0'
'                    type: string'
'                    expr: _col1'
'                    type: bigint'
'      Reduce Operator Tree:'
'        Extract'
'          Filter Operator'
'            predicate:'
'                expr: (_col1 > 1)'
'                type: boolean'
'            Select Operator'
'              expressions:'
'                    expr: _col0'
'                    type: string'
'                    expr: _col1'
'                    type: bigint'
'              outputColumnNames: _col0, _col1'
'              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-0'
'    Fetch Operator'
'      limit: -1'
''
''
119 rows selected 
>>>  
>>>  select b.key,b.cc 
from ( 
select a.* 
from ( 
select key, count(value) as cc 
from srcpart a 
where a.ds = '2008-04-08' and a.hr = '11' 
group by key 
)a 
distribute by a.key 
sort by a.key,a.cc desc) b 
where b.cc>1;
'key','cc'
'0','3'
'100','2'
'103','2'
'104','2'
'113','2'
'118','2'
'119','3'
'12','2'
'120','2'
'125','2'
'128','3'
'129','2'
'134','2'
'137','2'
'138','4'
'146','2'
'149','2'
'15','2'
'152','2'
'164','2'
'165','2'
'167','3'
'169','4'
'172','2'
'174','2'
'175','2'
'176','2'
'179','2'
'18','2'
'187','3'
'191','2'
'193','3'
'195','2'
'197','2'
'199','3'
'200','2'
'203','2'
'205','2'
'207','2'
'208','3'
'209','2'
'213','2'
'216','2'
'217','2'
'219','2'
'221','2'
'223','2'
'224','2'
'229','2'
'230','5'
'233','2'
'237','2'
'238','2'
'239','2'
'24','2'
'242','2'
'255','2'
'256','2'
'26','2'
'265','2'
'272','2'
'273','3'
'277','4'
'278','2'
'280','2'
'281','2'
'282','2'
'288','2'
'298','3'
'307','2'
'309','2'
'311','3'
'316','3'
'317','2'
'318','3'
'321','2'
'322','2'
'325','2'
'327','3'
'331','2'
'333','2'
'342','2'
'344','2'
'348','5'
'35','3'
'353','2'
'367','2'
'369','3'
'37','2'
'382','2'
'384','3'
'395','2'
'396','3'
'397','2'
'399','2'
'401','5'
'403','3'
'404','2'
'406','4'
'409','3'
'413','2'
'414','2'
'417','3'
'42','2'
'424','2'
'429','2'
'430','3'
'431','3'
'438','3'
'439','2'
'454','3'
'458','2'
'459','2'
'462','2'
'463','2'
'466','3'
'468','4'
'469','5'
'478','2'
'480','3'
'489','4'
'492','2'
'498','3'
'5','3'
'51','2'
'58','2'
'67','2'
'70','3'
'72','2'
'76','2'
'83','2'
'84','2'
'90','3'
'95','2'
'97','2'
'98','2'
136 rows selected 
>>>  
>>>  EXPLAIN 
SELECT user_id 
FROM ( 
SELECT 
CAST(key AS INT) AS user_id 
,CASE WHEN (value LIKE 'aaa%' OR value LIKE 'vvv%') 
THEN 1 
ELSE 0 END AS tag_student 
FROM srcpart 
) sub 
WHERE sub.tag_student > 0;
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_SUBQUERY (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME srcpart))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_FUNCTION TOK_INT (TOK_TABLE_OR_COL key)) user_id) (TOK_SELEXPR (TOK_FUNCTION WHEN (OR (LIKE (TOK_TABLE_OR_COL value) 'aaa%') (LIKE (TOK_TABLE_OR_COL value) 'vvv%')) 1 0) tag_student)))) sub)) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_TABLE_OR_COL user_id))) (TOK_WHERE (> (. (TOK_TABLE_OR_COL sub) tag_student) 0))))'
''
'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:'
'        sub:srcpart '
'          TableScan'
'            alias: srcpart'
'            Select Operator'
'              expressions:'
'                    expr: UDFToInteger(key)'
'                    type: int'
'                    expr: CASE WHEN (((value like 'aaa%') or (value like 'vvv%'))) THEN (1) ELSE (0) END'
'                    type: int'
'              outputColumnNames: _col0, _col1'
'              Filter Operator'
'                predicate:'
'                    expr: (_col1 > 0)'
'                    type: boolean'
'                Select Operator'
'                  expressions:'
'                        expr: _col0'
'                        type: int'
'                  outputColumnNames: _col0'
'                  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-0'
'    Fetch Operator'
'      limit: -1'
''
''
42 rows selected 
>>>  
>>>  EXPLAIN 
SELECT x.key, x.value as v1, y.key  FROM SRC x JOIN SRC y ON (x.key = y.key)  where x.key = 20 CLUSTER BY v1;
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_JOIN (TOK_TABREF (TOK_TABNAME SRC) x) (TOK_TABREF (TOK_TABNAME SRC) y) (= (. (TOK_TABLE_OR_COL x) key) (. (TOK_TABLE_OR_COL y) key)))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (. (TOK_TABLE_OR_COL x) key)) (TOK_SELEXPR (. (TOK_TABLE_OR_COL x) value) v1) (TOK_SELEXPR (. (TOK_TABLE_OR_COL y) key))) (TOK_WHERE (= (. (TOK_TABLE_OR_COL x) key) 20)) (TOK_CLUSTERBY (TOK_TABLE_OR_COL v1))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-2 depends on stages: Stage-1'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        x '
'          TableScan'
'            alias: x'
'            Filter Operator'
'              predicate:'
'                  expr: (key = 20.0)'
'                  type: boolean'
'              Reduce Output Operator'
'                key expressions:'
'                      expr: key'
'                      type: string'
'                sort order: +'
'                Map-reduce partition columns:'
'                      expr: key'
'                      type: string'
'                tag: 0'
'                value expressions:'
'                      expr: key'
'                      type: string'
'                      expr: value'
'                      type: string'
'        y '
'          TableScan'
'            alias: y'
'            Filter Operator'
'              predicate:'
'                  expr: (key = 20.0)'
'                  type: boolean'
'              Reduce Output Operator'
'                key expressions:'
'                      expr: key'
'                      type: string'
'                sort order: +'
'                Map-reduce partition columns:'
'                      expr: key'
'                      type: string'
'                tag: 1'
'                value expressions:'
'                      expr: key'
'                      type: string'
'      Reduce Operator Tree:'
'        Join Operator'
'          condition map:'
'               Inner Join 0 to 1'
'          condition expressions:'
'            0 {VALUE._col0} {VALUE._col1}'
'            1 {VALUE._col0}'
'          handleSkewJoin: false'
'          outputColumnNames: _col0, _col1, _col4'
'          Select Operator'
'            expressions:'
'                  expr: _col0'
'                  type: string'
'                  expr: _col1'
'                  type: string'
'                  expr: _col4'
'                  type: string'
'            outputColumnNames: _col0, _col1, _col2'
'            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}!! '
'            Reduce Output Operator'
'              key expressions:'
'                    expr: _col1'
'                    type: string'
'              sort order: +'
'              Map-reduce partition columns:'
'                    expr: _col1'
'                    type: string'
'              tag: -1'
'              value expressions:'
'                    expr: _col0'
'                    type: string'
'                    expr: _col1'
'                    type: string'
'                    expr: _col2'
'                    type: string'
'      Reduce Operator Tree:'
'        Extract'
'          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-0'
'    Fetch Operator'
'      limit: -1'
''
''
111 rows selected 
>>>  
>>>  set hive.optimize.ppd=true;
No rows affected 
>>>  set hive.ppd.remove.duplicatefilters=false;
No rows affected 
>>>  
>>>  explain 
select b.key,b.cc 
from ( 
select a.* 
from ( 
select key, count(value) as cc 
from srcpart a 
where a.ds = '2008-04-08' and a.hr = '11' 
group by key 
)a 
distribute by a.key 
sort by a.key,a.cc desc) b 
where b.cc>1;
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_SUBQUERY (TOK_QUERY (TOK_FROM (TOK_SUBQUERY (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME srcpart) a)) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_TABLE_OR_COL key)) (TOK_SELEXPR (TOK_FUNCTION count (TOK_TABLE_OR_COL value)) cc)) (TOK_WHERE (and (= (. (TOK_TABLE_OR_COL a) ds) '2008-04-08') (= (. (TOK_TABLE_OR_COL a) hr) '11'))) (TOK_GROUPBY (TOK_TABLE_OR_COL key)))) a)) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_ALLCOLREF (TOK_TABNAME a)))) (TOK_DISTRIBUTEBY (. (TOK_TABLE_OR_COL a) key)) (TOK_SORTBY (TOK_TABSORTCOLNAMEASC (. (TOK_TABLE_OR_COL a) key)) (TOK_TABSORTCOLNAMEDESC (. (TOK_TABLE_OR_COL a) cc))))) b)) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (. (TOK_TABLE_OR_COL b) key)) (TOK_SELEXPR (. (TOK_TABLE_OR_COL b) cc))) (TOK_WHERE (> (. (TOK_TABLE_OR_COL b) cc) 1))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-2 depends on stages: Stage-1'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        b:a:a '
'          TableScan'
'            alias: a'
'            Filter Operator'
'              predicate:'
'                  expr: ((ds = '2008-04-08') and (hr = '11'))'
'                  type: boolean'
'              Select Operator'
'                expressions:'
'                      expr: key'
'                      type: string'
'                      expr: value'
'                      type: string'
'                outputColumnNames: key, value'
'                Group By Operator'
'                  aggregations:'
'                        expr: count(value)'
'                  bucketGroup: false'
'                  keys:'
'                        expr: key'
'                        type: string'
'                  mode: hash'
'                  outputColumnNames: _col0, _col1'
'                  Reduce Output Operator'
'                    key expressions:'
'                          expr: _col0'
'                          type: string'
'                    sort order: +'
'                    Map-reduce partition columns:'
'                          expr: _col0'
'                          type: string'
'                    tag: -1'
'                    value expressions:'
'                          expr: _col1'
'                          type: bigint'
'      Reduce Operator Tree:'
'        Group By Operator'
'          aggregations:'
'                expr: count(VALUE._col0)'
'          bucketGroup: false'
'          keys:'
'                expr: KEY._col0'
'                type: string'
'          mode: mergepartial'
'          outputColumnNames: _col0, _col1'
'          Select Operator'
'            expressions:'
'                  expr: _col0'
'                  type: string'
'                  expr: _col1'
'                  type: bigint'
'            outputColumnNames: _col0, _col1'
'            Select Operator'
'              expressions:'
'                    expr: _col0'
'                    type: string'
'                    expr: _col1'
'                    type: bigint'
'              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}!! '
'            Reduce Output Operator'
'              key expressions:'
'                    expr: _col0'
'                    type: string'
'                    expr: _col1'
'                    type: bigint'
'              sort order: +-'
'              Map-reduce partition columns:'
'                    expr: _col0'
'                    type: string'
'              tag: -1'
'              value expressions:'
'                    expr: _col0'
'                    type: string'
'                    expr: _col1'
'                    type: bigint'
'      Reduce Operator Tree:'
'        Extract'
'          Filter Operator'
'            predicate:'
'                expr: (_col1 > 1)'
'                type: boolean'
'            Select Operator'
'              expressions:'
'                    expr: _col0'
'                    type: string'
'                    expr: _col1'
'                    type: bigint'
'              outputColumnNames: _col0, _col1'
'              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-0'
'    Fetch Operator'
'      limit: -1'
''
''
123 rows selected 
>>>  
>>>  select b.key,b.cc 
from ( 
select a.* 
from ( 
select key, count(value) as cc 
from srcpart a 
where a.ds = '2008-04-08' and a.hr = '11' 
group by key 
)a 
distribute by a.key 
sort by a.key,a.cc desc) b 
where b.cc>1;
'key','cc'
'0','3'
'100','2'
'103','2'
'104','2'
'113','2'
'118','2'
'119','3'
'12','2'
'120','2'
'125','2'
'128','3'
'129','2'
'134','2'
'137','2'
'138','4'
'146','2'
'149','2'
'15','2'
'152','2'
'164','2'
'165','2'
'167','3'
'169','4'
'172','2'
'174','2'
'175','2'
'176','2'
'179','2'
'18','2'
'187','3'
'191','2'
'193','3'
'195','2'
'197','2'
'199','3'
'200','2'
'203','2'
'205','2'
'207','2'
'208','3'
'209','2'
'213','2'
'216','2'
'217','2'
'219','2'
'221','2'
'223','2'
'224','2'
'229','2'
'230','5'
'233','2'
'237','2'
'238','2'
'239','2'
'24','2'
'242','2'
'255','2'
'256','2'
'26','2'
'265','2'
'272','2'
'273','3'
'277','4'
'278','2'
'280','2'
'281','2'
'282','2'
'288','2'
'298','3'
'307','2'
'309','2'
'311','3'
'316','3'
'317','2'
'318','3'
'321','2'
'322','2'
'325','2'
'327','3'
'331','2'
'333','2'
'342','2'
'344','2'
'348','5'
'35','3'
'353','2'
'367','2'
'369','3'
'37','2'
'382','2'
'384','3'
'395','2'
'396','3'
'397','2'
'399','2'
'401','5'
'403','3'
'404','2'
'406','4'
'409','3'
'413','2'
'414','2'
'417','3'
'42','2'
'424','2'
'429','2'
'430','3'
'431','3'
'438','3'
'439','2'
'454','3'
'458','2'
'459','2'
'462','2'
'463','2'
'466','3'
'468','4'
'469','5'
'478','2'
'480','3'
'489','4'
'492','2'
'498','3'
'5','3'
'51','2'
'58','2'
'67','2'
'70','3'
'72','2'
'76','2'
'83','2'
'84','2'
'90','3'
'95','2'
'97','2'
'98','2'
136 rows selected 
>>>  !record