Hive PredicatePushDown (谓词下推)

最新推荐文章于 2024-04-21 16:26:47 发布
最新推荐文章于 2024-04-21 16:26:47 发布
阅读量869 收藏 2
点赞数
分类专栏： 大数据 hive
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_35744460/article/details/90638652
版权
源码介绍
/**
 * Implements predicate pushdown. Predicate pushdown is a term borrowed from
 * relational databases even though for Hive it is predicate pushup. The basic
 * idea is to process expressions as early in the plan as possible. The default
 * plan generation adds filters where they are seen but in some instances some
 * of the filter expressions can be pushed nearer to the operator that sees this
 * particular data for the first time. e.g. select a.*, b.* from a join b on
 * (a.col1 = b.col1) where a.col1 > 20 and b.col2 > 40
 *
 * For the above query, the predicates (a.col1 > 20) and (b.col2 > 40), without
 * predicate pushdown, would be evaluated after the join processing has been
 * done. Suppose the two predicates filter out most of the rows from a and b,
 * the join is unnecessarily processing these rows. With predicate pushdown,
 * these two predicates will be processed before the join.
 *
 * Predicate pushdown is enabled by setting hive.optimize.ppd to true. It is
 * disable by default.
 *
 * The high-level algorithm is describe here - An operator is processed after
 * all its children have been processed - An operator processes its own
 * predicates and then merges (conjunction) with the processed predicates of its
 * children. In case of multiple children, there are combined using disjunction
 * (OR). - A predicate expression is processed for an operator using the
 * following steps - If the expr is a constant then it is a candidate for
 * predicate pushdown - If the expr is a col reference then it is a candidate
 * and its alias is noted - If the expr is an index and both the array and index
 * expr are treated as children - If the all child expr are candidates for
 * pushdown and all of the expression reference only one alias from the
 * operator's RowResolver then the current expression is also a candidate One
 * key thing to note is that some operators (Select, ReduceSink, GroupBy, Join
 * etc) change the columns as data flows through them. In such cases the column
 * references are replaced by the corresponding expression in the input data.
 */
开启命令
默认开启
hive> set hive.optimize.ppd=true;
hive> set hive.optimize.ppd=false;
准备表以及数据
create table user_info(
user_id int,
dept_id int,
username string
) 
row format delimited fields terminated by ',';

create table dept_info(
dept_id int,
deptname string
) 
row format delimited fields terminated by ',';

INSERT INTO `dept_info` VALUES ('1', '技术部');
INSERT INTO `dept_info` VALUES ('2', '业务部');
INSERT INTO `dept_info` VALUES ('3', '运营部');
INSERT INTO `dept_info` VALUES ('4', '决策层');

INSERT INTO `user_info` VALUES ('1', '1', '张三');
INSERT INTO `user_info` VALUES ('2', '1', '李四');
INSERT INTO `user_info` VALUES ('3', '2', '王五');
INSERT INTO `user_info` VALUES ('4', '3', '赵六');
join 测试
  • set hive.optimize.ppd=false;
hive> explain select * from user_info u join dept_info d on u.dept_id = d.dept_id where u.dept_id=1;
OK
STAGE DEPENDENCIES:
  Stage-4 is a root stage
  Stage-3 depends on stages: Stage-4
  Stage-0 depends on stages: Stage-3

STAGE PLANS:
  Stage: Stage-4
          TableScan
            alias: u
            Statistics: Num rows: 1 Data size: 44 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: dept_id is not null (type: boolean)
           
  Stage: Stage-3
          TableScan
            alias: d
            Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: dept_id is not null (type: boolean)
              Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
              Map Join Operator
                condition map:
              
                Filter Operator
                  predicate: (_col1 = 1) (type: boolean)
                
      Local Work:
        Map Reduce Local Work

  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

Time taken: 0.403 seconds, Fetched: 65 row(s)

hive> explain select * from user_info u join dept_info d on u.dept_id = d.dept_id and u.dept_id=1;
OK
STAGE DEPENDENCIES:
  Stage-4 is a root stage
  Stage-3 depends on stages: Stage-4
  Stage-0 depends on stages: Stage-3

STAGE PLANS:
  Stage: Stage-4
    Map Reduce Local Work
      Alias -> Map Local Tables:
        u 
          Fetch Operator
            limit: -1
      Alias -> Map Local Operator Tree:
        u 
          TableScan
            alias: u
            Statistics: Num rows: 1 Data size: 44 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (dept_id = 1) (type: boolean)
              Statistics: Num rows: 1 Data size: 44 Basic stats: COMPLETE Column stats: NONE
              HashTable Sink Operator
                keys:
                  0 1 (type: int)
                  1 dept_id (type: int)

  Stage: Stage-3
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: d
            Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: dept_id is not null (type: boolean)
              Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
              Map Join Operator
                condition map:
                     Inner Join 0 to 1
                keys:
                  0 1 (type: int)
                  1 dept_id (type: int)
                outputColumnNames: _col0, _col2, _col6, _col7
                Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
                Select Operator
                  expressions: _col0 (type: int), 1 (type: int), _col2 (type: string), _col6 (type: int), _col7 (type: string)
                  outputColumnNames: _col0, _col1, _col2, _col3, _col4
                  Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
                  File Output Operator
                    compressed: false
                    Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
                    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
      Local Work:
        Map Reduce Local Work

  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

Time taken: 0.472 seconds, Fetched: 62 row(s)

hive> explain select * from user_info u join dept_info d on u.dept_id = d.dept_id and d.dept_id=1;
OK
STAGE DEPENDENCIES:
  Stage-4 is a root stage
  Stage-3 depends on stages: Stage-4
  Stage-0 depends on stages: Stage-3

STAGE PLANS:
  Stage: Stage-4
    Map Reduce Local Work
      Alias -> Map Local Tables:
        u 
          Fetch Operator
            limit: -1
      Alias -> Map Local Operator Tree:
        u 
          TableScan
            alias: u
            Statistics: Num rows: 1 Data size: 44 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: dept_id is not null (type: boolean)
              Statistics: Num rows: 1 Data size: 44 Basic stats: COMPLETE Column stats: NONE
              HashTable Sink Operator
                keys:
                  0 dept_id (type: int)
                  1 1 (type: int)

  Stage: Stage-3
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: d
            Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (dept_id = 1) (type: boolean)
              Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
              Map Join Operator
                condition map:
                     Inner Join 0 to 1
                keys:
                  0 dept_id (type: int)
                  1 1 (type: int)
                outputColumnNames: _col0, _col1, _col2, _col7
                Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
                Select Operator
                  expressions: _col0 (type: int), _col1 (type: int), _col2 (type: string), 1 (type: int), _col7 (type: string)
                  outputColumnNames: _col0, _col1, _col2, _col3, _col4
                  Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
                  File Output Operator
                    compressed: false
                    Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
                    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
      Local Work:
        Map Reduce Local Work

  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

Time taken: 1.215 seconds, Fetched: 62 row(s)

hive> explain select * from user_info u join dept_info d on u.dept_id = d.dept_id where d.dept_id=1;
OK
STAGE DEPENDENCIES:
  Stage-4 is a root stage
  Stage-3 depends on stages: Stage-4
  Stage-0 depends on stages: Stage-3

STAGE PLANS:
  Stage: Stage-4
    Map Reduce Local Work
      Alias -> Map Local Tables:
        u 
          Fetch Operator
            limit: -1
      Alias -> Map Local Operator Tree:
        u 
          TableScan
            alias: u
            Statistics: Num rows: 1 Data size: 44 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: dept_id is not null (type: boolean)
              Statistics: Num rows: 1 Data size: 44 Basic stats: COMPLETE Column stats: NONE
              HashTable Sink Operator
                keys:
                  0 dept_id (type: int)
                  1 dept_id (type: int)

  Stage: Stage-3
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: d
            Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: dept_id is not null (type: boolean)
              Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
              Map Join Operator
                condition map:
                     Inner Join 0 to 1
                keys:
                  0 dept_id (type: int)
                  1 dept_id (type: int)
                outputColumnNames: _col0, _col1, _col2, _col6, _col7
                Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
                Filter Operator
                  predicate: (_col6 = 1) (type: boolean)
                  Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
                  Select Operator
                    expressions: _col0 (type: int), _col1 (type: int), _col2 (type: string), 1 (type: int), _col7 (type: string)
                    outputColumnNames: _col0, _col1, _col2, _col3, _col4
                    Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
                    File Output Operator
                      compressed: false
                      Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
                      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
      Local Work:
        Map Reduce Local Work

  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

Time taken: 0.431 seconds, Fetched: 65 row(s)
  • set hive.optimize.ppd=false;
hive> explain select * from user_info u join dept_info d on u.dept_id = d.dept_id where u.dept_id=1;
OK
STAGE DEPENDENCIES:
  Stage-4 is a root stage
  Stage-3 depends on stages: Stage-4
  Stage-0 depends on stages: Stage-3

STAGE PLANS:
  Stage: Stage-4
    Map Reduce Local Work
      Alias -> Map Local Tables:
        u 
          Fetch Operator
            limit: -1
      Alias -> Map Local Operator Tree:
        u 
          TableScan
            alias: u
            Statistics: Num rows: 1 Data size: 44 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (dept_id = 1) (type: boolean)
              Statistics: Num rows: 1 Data size: 44 Basic stats: COMPLETE Column stats: NONE
              HashTable Sink Operator
                keys:
                  0 1 (type: int)
                  1 1 (type: int)

  Stage: Stage-3
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: d
            Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (dept_id = 1) (type: boolean)
              Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
              Map Join Operator
                condition map:
                     Inner Join 0 to 1
                keys:
                  0 1 (type: int)
                  1 1 (type: int)
                outputColumnNames: _col0, _col2, _col7
                Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
                Select Operator
                  expressions: _col0 (type: int), 1 (type: int), _col2 (type: string), 1 (type: int), _col7 (type: string)
                  outputColumnNames: _col0, _col1, _col2, _col3, _col4
                  Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
                  File Output Operator
                    compressed: false
                    Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
                    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
      Local Work:
        Map Reduce Local Work

  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

Time taken: 0.428 seconds, Fetched: 62 row(s)

hive> explain select * from user_info u join dept_info d on u.dept_id = d.dept_id and u.dept_id=1;
OK
STAGE DEPENDENCIES:
  Stage-4 is a root stage
  Stage-3 depends on stages: Stage-4
  Stage-0 depends on stages: Stage-3

STAGE PLANS:
  Stage: Stage-4
    Map Reduce Local Work
      Alias -> Map Local Tables:
        u 
          Fetch Operator
            limit: -1
      Alias -> Map Local Operator Tree:
        u 
          TableScan
            alias: u
            Statistics: Num rows: 1 Data size: 44 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (dept_id = 1) (type: boolean)
              Statistics: Num rows: 1 Data size: 44 Basic stats: COMPLETE Column stats: NONE
              HashTable Sink Operator
                keys:
                  0 1 (type: int)
                  1 1 (type: int)

  Stage: Stage-3
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: d
            Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (dept_id = 1) (type: boolean)
              Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
              Map Join Operator
                condition map:
                     Inner Join 0 to 1
                keys:
                  0 1 (type: int)
                  1 1 (type: int)
                outputColumnNames: _col0, _col2, _col7
                Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
                Select Operator
                  expressions: _col0 (type: int), 1 (type: int), _col2 (type: string), 1 (type: int), _col7 (type: string)
                  outputColumnNames: _col0, _col1, _col2, _col3, _col4
                  Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
                  File Output Operator
                    compressed: false
                    Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
                    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
      Local Work:
        Map Reduce Local Work

  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

Time taken: 0.423 seconds, Fetched: 62 row(s)
hive> explain select * from user_info u join dept_info d on u.dept_id = d.dept_id and d.dept_id=1;
OK
STAGE DEPENDENCIES:
  Stage-4 is a root stage
  Stage-3 depends on stages: Stage-4
  Stage-0 depends on stages: Stage-3

STAGE PLANS:
  Stage: Stage-4
    Map Reduce Local Work
      Alias -> Map Local Tables:
        u 
          Fetch Operator
            limit: -1
      Alias -> Map Local Operator Tree:
        u 
          TableScan
            alias: u
            Statistics: Num rows: 1 Data size: 44 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (dept_id = 1) (type: boolean)
              Statistics: Num rows: 1 Data size: 44 Basic stats: COMPLETE Column stats: NONE
              HashTable Sink Operator
                keys:
                  0 1 (type: int)
                  1 1 (type: int)

  Stage: Stage-3
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: d
            Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (dept_id = 1) (type: boolean)
              Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
              Map Join Operator
                condition map:
                     Inner Join 0 to 1
                keys:
                  0 1 (type: int)
                  1 1 (type: int)
                outputColumnNames: _col0, _col2, _col7
                Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
                Select Operator
                  expressions: _col0 (type: int), 1 (type: int), _col2 (type: string), 1 (type: int), _col7 (type: string)
                  outputColumnNames: _col0, _col1, _col2, _col3, _col4
                  Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
                  File Output Operator
                    compressed: false
                    Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
                    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
      Local Work:
        Map Reduce Local Work

  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

Time taken: 0.411 seconds, Fetched: 62 row(s)

hive> explain select * from user_info u join dept_info d on u.dept_id = d.dept_id where d.dept_id=1;
OK
STAGE DEPENDENCIES:
  Stage-4 is a root stage
  Stage-3 depends on stages: Stage-4
  Stage-0 depends on stages: Stage-3

STAGE PLANS:
  Stage: Stage-4
    Map Reduce Local Work
      Alias -> Map Local Tables:
        u 
          Fetch Operator
            limit: -1
      Alias -> Map Local Operator Tree:
        u 
          TableScan
            alias: u
            Statistics: Num rows: 1 Data size: 44 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (dept_id = 1) (type: boolean)
              Statistics: Num rows: 1 Data size: 44 Basic stats: COMPLETE Column stats: NONE
              HashTable Sink Operator
                keys:
                  0 1 (type: int)
                  1 1 (type: int)

  Stage: Stage-3
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: d
            Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (dept_id = 1) (type: boolean)
              Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
              Map Join Operator
                condition map:
                     Inner Join 0 to 1
                keys:
                  0 1 (type: int)
                  1 1 (type: int)
                outputColumnNames: _col0, _col2, _col7
                Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
                Select Operator
                  expressions: _col0 (type: int), 1 (type: int), _col2 (type: string), 1 (type: int), _col7 (type: string)
                  outputColumnNames: _col0, _col1, _col2, _col3, _col4
                  Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
                  File Output Operator
                    compressed: false
                    Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
                    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
      Local Work:
        Map Reduce Local Work

  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

Time taken: 0.477 seconds, Fetched: 62 row(s)
left outer join 测试
  • set hive.optimize.ppd=true;
hive> explain select * from user_info u left outer join dept_info d on u.dept_id = d.dept_id where d.dept_id=1;
OK
STAGE DEPENDENCIES:
  Stage-4 is a root stage
  Stage-3 depends on stages: Stage-4
  Stage-0 depends on stages: Stage-3

STAGE PLANS:
  Stage: Stage-4
    Map Reduce Local Work
      Alias -> Map Local Tables:
        d 
          Fetch Operator
            limit: -1
      Alias -> Map Local Operator Tree:
        d 
          TableScan
            alias: d
            Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
            HashTable Sink Operator
              keys:
                0 dept_id (type: int)
                1 dept_id (type: int)

  Stage: Stage-3
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: u
            Statistics: Num rows: 1 Data size: 44 Basic stats: COMPLETE Column stats: NONE
            Map Join Operator
              condition map:
                   Left Outer Join0 to 1
              keys:
                0 dept_id (type: int)
                1 dept_id (type: int)
              outputColumnNames: _col0, _col1, _col2, _col6, _col7
              Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
              Filter Operator
                predicate: (_col6 = 1) (type: boolean)
                Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
                Select Operator
                  expressions: _col0 (type: int), _col1 (type: int), _col2 (type: string), 1 (type: int), _col7 (type: string)
                  outputColumnNames: _col0, _col1, _col2, _col3, _col4
                  Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
                  File Output Operator
                    compressed: false
                    Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
                    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
      Local Work:
        Map Reduce Local Work

  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

Time taken: 0.507 seconds, Fetched: 59 row(s)
hive> explain select * from user_info u left outer join dept_info d on u.dept_id = d.dept_id and d.dept_id=1;
OK
STAGE DEPENDENCIES:
  Stage-4 is a root stage
  Stage-3 depends on stages: Stage-4
  Stage-0 depends on stages: Stage-3

STAGE PLANS:
  Stage: Stage-4
    Map Reduce Local Work
      Alias -> Map Local Tables:
        d 
          Fetch Operator
            limit: -1
      Alias -> Map Local Operator Tree:
        d 
          TableScan
            alias: d
            Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (dept_id = 1) (type: boolean)
              Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
              HashTable Sink Operator
                keys:
                  0 dept_id (type: int)
                  1 dept_id (type: int)

  Stage: Stage-3
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: u
            Statistics: Num rows: 1 Data size: 44 Basic stats: COMPLETE Column stats: NONE
            Map Join Operator
              condition map:
                   Left Outer Join0 to 1
              keys:
                0 dept_id (type: int)
                1 dept_id (type: int)
              outputColumnNames: _col0, _col1, _col2, _col6, _col7
              Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: _col0 (type: int), _col1 (type: int), _col2 (type: string), _col6 (type: int), _col7 (type: string)
                outputColumnNames: _col0, _col1, _col2, _col3, _col4
                Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
                File Output Operator
                  compressed: false
                  Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
                  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
      Local Work:
        Map Reduce Local Work

  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

Time taken: 0.873 seconds, Fetched: 59 row(s)
hive> explain select * from user_info u left outer join dept_info d on u.dept_id = d.dept_id where u.dept_id=1;
OK
STAGE DEPENDENCIES:
  Stage-4 is a root stage
  Stage-3 depends on stages: Stage-4
  Stage-0 depends on stages: Stage-3

STAGE PLANS:
  Stage: Stage-4
    Map Reduce Local Work
      Alias -> Map Local Tables:
        d 
          Fetch Operator
            limit: -1
      Alias -> Map Local Operator Tree:
        d 
          TableScan
            alias: d
            Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (dept_id = 1) (type: boolean)
              Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
              HashTable Sink Operator
                keys:
                  0 dept_id (type: int)
                  1 dept_id (type: int)

  Stage: Stage-3
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: u
            Statistics: Num rows: 1 Data size: 44 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (dept_id = 1) (type: boolean)
              Statistics: Num rows: 1 Data size: 44 Basic stats: COMPLETE Column stats: NONE
              Map Join Operator
                condition map:
                     Left Outer Join0 to 1
                keys:
                  0 dept_id (type: int)
                  1 dept_id (type: int)
                outputColumnNames: _col0, _col2, _col6, _col7
                Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
                Select Operator
                  expressions: _col0 (type: int), 1 (type: int), _col2 (type: string), _col6 (type: int), _col7 (type: string)
                  outputColumnNames: _col0, _col1, _col2, _col3, _col4
                  Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
                  File Output Operator
                    compressed: false
                    Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
                    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
      Local Work:
        Map Reduce Local Work

  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

Time taken: 0.41 seconds, Fetched: 62 row(s)
  • set hive.optimize.ppd=false;
hive> explain select * from user_info u left outer join dept_info d on u.dept_id = d.dept_id where u.dept_id=1;
OK
STAGE DEPENDENCIES:
  Stage-4 is a root stage
  Stage-3 depends on stages: Stage-4
  Stage-0 depends on stages: Stage-3

STAGE PLANS:
  Stage: Stage-4
    Map Reduce Local Work
      Alias -> Map Local Tables:
        d 
          Fetch Operator
            limit: -1
      Alias -> Map Local Operator Tree:
        d 
          TableScan
            alias: d
            Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
            HashTable Sink Operator
              keys:
                0 dept_id (type: int)
                1 dept_id (type: int)

  Stage: Stage-3
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: u
            Statistics: Num rows: 1 Data size: 44 Basic stats: COMPLETE Column stats: NONE
            Map Join Operator
              condition map:
                   Left Outer Join0 to 1
              keys:
                0 dept_id (type: int)
                1 dept_id (type: int)
              outputColumnNames: _col0, _col1, _col2, _col6, _col7
              Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
              Filter Operator
                predicate: (_col1 = 1) (type: boolean)
                Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
                Select Operator
                  expressions: _col0 (type: int), 1 (type: int), _col2 (type: string), _col6 (type: int), _col7 (type: string)
                  outputColumnNames: _col0, _col1, _col2, _col3, _col4
                  Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
                  File Output Operator
                    compressed: false
                    Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
                    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
      Local Work:
        Map Reduce Local Work

  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

Time taken: 0.416 seconds, Fetched: 59 row(s)
hive> explain select * from user_info u left outer join dept_info d on u.dept_id = d.dept_id where d.dept_id=1;
OK
STAGE DEPENDENCIES:
  Stage-4 is a root stage
  Stage-3 depends on stages: Stage-4
  Stage-0 depends on stages: Stage-3

STAGE PLANS:
  Stage: Stage-4
    Map Reduce Local Work
      Alias -> Map Local Tables:
        d 
          Fetch Operator
            limit: -1
      Alias -> Map Local Operator Tree:
        d 
          TableScan
            alias: d
            Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
            HashTable Sink Operator
              keys:
                0 dept_id (type: int)
                1 dept_id (type: int)

  Stage: Stage-3
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: u
            Statistics: Num rows: 1 Data size: 44 Basic stats: COMPLETE Column stats: NONE
            Map Join Operator
              condition map:
                   Left Outer Join0 to 1
              keys:
                0 dept_id (type: int)
                1 dept_id (type: int)
              outputColumnNames: _col0, _col1, _col2, _col6, _col7
              Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
              Filter Operator
                predicate: (_col6 = 1) (type: boolean)
                Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
                Select Operator
                  expressions: _col0 (type: int), _col1 (type: int), _col2 (type: string), 1 (type: int), _col7 (type: string)
                  outputColumnNames: _col0, _col1, _col2, _col3, _col4
                  Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
                  File Output Operator
                    compressed: false
                    Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
                    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
      Local Work:
        Map Reduce Local Work

  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

Time taken: 0.4 seconds, Fetched: 59 row(s)
hive> explain select * from user_info u left outer join dept_info d on u.dept_id = d.dept_id and d.dept_id=1;
OK
STAGE DEPENDENCIES:
  Stage-4 is a root stage
  Stage-3 depends on stages: Stage-4
  Stage-0 depends on stages: Stage-3

STAGE PLANS:
  Stage: Stage-4
    Map Reduce Local Work
      Alias -> Map Local Tables:
        d 
          Fetch Operator
            limit: -1
      Alias -> Map Local Operator Tree:
        d 
          TableScan
            alias: d
            Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (dept_id = 1) (type: boolean)
              Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
              HashTable Sink Operator
                keys:
                  0 dept_id (type: int)
                  1 dept_id (type: int)

  Stage: Stage-3
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: u
            Statistics: Num rows: 1 Data size: 44 Basic stats: COMPLETE Column stats: NONE
            Map Join Operator
              condition map:
                   Left Outer Join0 to 1
              keys:
                0 dept_id (type: int)
                1 dept_id (type: int)
              outputColumnNames: _col0, _col1, _col2, _col6, _col7
              Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: _col0 (type: int), _col1 (type: int), _col2 (type: string), _col6 (type: int), _col7 (type: string)
                outputColumnNames: _col0, _col1, _col2, _col3, _col4
                Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
                File Output Operator
                  compressed: false
                  Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
                  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
      Local Work:
        Map Reduce Local Work

  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

Time taken: 0.459 seconds, Fetched: 59 row(s)

hive> explain select * from user_info u left outer join dept_info d on u.dept_id = d.dept_id and u.dept_id=1;
OK
STAGE DEPENDENCIES:
  Stage-4 is a root stage
  Stage-3 depends on stages: Stage-4
  Stage-0 depends on stages: Stage-3

STAGE PLANS:
  Stage: Stage-4
    Map Reduce Local Work
      Alias -> Map Local Tables:
        d 
          Fetch Operator
            limit: -1
      Alias -> Map Local Operator Tree:
        d 
          TableScan
            alias: d
            Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
            HashTable Sink Operator
              filter predicates:
                0 {(dept_id = 1)}
                1 
              keys:
                0 dept_id (type: int)
                1 dept_id (type: int)

  Stage: Stage-3
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: u
            Statistics: Num rows: 1 Data size: 44 Basic stats: COMPLETE Column stats: NONE
            Map Join Operator
              condition map:
                   Left Outer Join0 to 1
              filter predicates:
                0 {(dept_id = 1)}
                1 
              keys:
                0 dept_id (type: int)
                1 dept_id (type: int)
              outputColumnNames: _col0, _col1, _col2, _col6, _col7
              Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: _col0 (type: int), _col1 (type: int), _col2 (type: string), _col6 (type: int), _col7 (type: string)
                outputColumnNames: _col0, _col1, _col2, _col3, _col4
                Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
                File Output Operator
                  compressed: false
                  Statistics: Num rows: 1 Data size: 48 Basic stats: COMPLETE Column stats: NONE
                  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
      Local Work:
        Map Reduce Local Work

  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

Time taken: 0.425 seconds, Fetched: 62 row(s)
潇洒-人生
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql谓词下推_Filter(过滤)|Project(映射)|Pushdowns(谓词下推)
weixin_32099979的博客
02-19 685
1. Filter(过滤) 和 Project(映射)在传统的 OLAP 系统中，在进行 Join 的时候使用过滤和映射会极大的提高性能。同样的，在 Hadoop 中使用 Filter 和 Projection 同样可以提高效率，由于减少了一个管道需要处理的数据量。在Hadoop中减少处理的数据量是至关重要的，尤其当需要通过网络和本地磁盘进行处理的时候。我们都知道，MapReduce 的shuff...
Ubuntu下安装hive数据库
04-15
适用于搭建伪分布的学生用户使用
Hive中的Predicate Pushdown Rules（谓词下推规则)
strongyoung的专栏
07-22 1万+
谓词下推概念 谓词下推 Predicate Pushdown（PPD）：简而言之，就是在不影响结果的情况下，尽量将过滤条件提前执行。谓词下推后，过滤条件在map端执行，减少了map端的输出，降低了数据在集群上传输的量，节约了集群的资源，也提升了任务的性能。 PPD 配置 PPD控制参数：hive.optimize.ppd Default Value: true Added ...
HIVE：谓词下推
最新发布
ALX3li的博客
04-21 567
谓词下推 Predicate Pushdown（PPD），简而言之，就是在不影响结果的情况下，尽量将过滤条件提前执行。谓词下推后，过滤条件在map端执行，减少了map端的输出，降低了数据在集群上传输的量，节约了集群的资源，也提升了任务的性能。因为unix_timestamp是不确定函数，在编译的时候无法得知，所以，整个表达式不会被pushed，即ds='2019-10-09’也不会被提前过滤。比如以下选择，left join中左表s1为保留行表，所以on条件（join过滤条件）不能下推到s1中。
Hive谓词下推
Learning
04-29 1261
谓词下推是一种自动优化，如果养成好的查询习惯你其实并不需要hive自动去帮你谓词下推。 谓词下推 Predicate Pushdown（PPD）：简而言之，就是在不影响结果的情况下，尽量将过滤条件提前执行。
SQL优化实战：外层查询条件放到内层查询中（predicate push down）
weixin_30312557的博客
05-09 515
客户反应考勤sql查询太慢，需要优化。 这个查询是客户用来考核他们员工的考勤的，所以会经常去查，另外，原始语句比较复杂，这个sql一共92行，涉及到6个表的多次关联，客户在查询1个月的数据，基本上要10分钟。 接下来做了如下尝试： 1、原始数据本来从实时表取，修改为从结存表取，减少表的关联，但是修改后速度没有变快。 2、这种情况下很难优化，时间有紧，不可能重新写，因为这样会引...
SQL Server 的弱鸡 Predicate Pushdown
wujiandao的专栏
02-15 262
点击蓝色“有关SQL”关注我哟加个“星标”，天天与10000人一起快乐成长图 | Lenis昨天分享了 Oracle 的 Predicate Pushdown.惊！不可以貌取 SQL今天继...
windows下hive的可执行文件
10-04
【标题】：“Windows环境下Hive的可执行文件” 在Windows操作系统中使用Apache Hive，你需要一个兼容的可执行文件集，以便能够与Hadoop生态系统交互。Hive是一个基于Hadoop的数据仓库工具，它允许用户通过SQL-like...
Linux环境下Hive的安装部署
06-15
Linux 环境下 Hive 的安装部署 Linux 环境下 Hive 的安装部署是大数据处理中的重要步骤。Hive 是基于 Hadoop 的数据仓库工具，能够提供数据仓库的功能，帮助用户快速地对数据进行分析和处理。本文档将指导用户如何...
hadoop下hive的安装
03-29
这里详细介绍了hive的安装，以及和mysql的整合，以及一些其他的一些简单操作
Linux下MySQL-Hive.rar
12-26
标题中的“Linux下MySQL-Hive.rar”是一个压缩包，包含了在Linux环境中安装和配置MySQL与Hive的相关资料。为了深入理解这些技术并进行实际操作，我们需要了解以下几个关键知识点： 1. **Linux基础**：首先，你需要...
Sargable 与 谓语下推 (predicate pushdown) 简介
weixin_30521161的博客
03-24 250
关键词：SQL优化 , sargable , pushdown filter , predicate pushdown Sargable Sargable = Search ARGument ABLE ，即SQL中可利用数据库自身索引优势对查询条件进行执行性能优化。换句话说，即可以利用存储层的索引优势来优化的查询条件。wikipedia: https://en.wikipedia.org...
谓词下推
热门推荐
无关风月
05-09 1万+
优化关系 SQL 查询的一项基本技术是，将外层查询块的 WHERE 子句中的谓词移入所包含的较低层查询块（例如视图），从而能够提早进行数据过滤以及有可能更好地利用索引。这在分区数据库环境中甚至更为重要，其原因在于，提早进行过滤有可能减少必须在数据库分区之间传递的数据量。此优化技术在 SQL 中被称为谓词下推(Predicate pushdown) 。 基本策略是，始终将过滤表达式尽可能移至靠近数
SQL 谓词下推
littlecarton的博客
03-13 461
谓词下推
SQL优化之谓词下推
five小点心的博客
06-19 924
1、谓词下推：在存储层即过滤了大量大表无效数据，减少扫描无效数据；所谓下推，即谓词过滤在map端执行，所谓不下推，即谓词过滤在reduce端执行2、inner join时，谓词放任意位置都会下推3、left join时，左表的谓词应该写在where 后4、right join时，左表的谓词应该写在join后通过上面的分析，谓词下推是生效的，但是在我们对最后结果的输出是因为执行顺序不同导入的结果不一致。
Filter(过滤)|Project(映射)|Pushdowns(谓词下推)
微信搜：import_bigdata，大数据领域硬核原创作者
04-09 1038
1. Filter（过滤） 和 Project（映射）在传统的 OLAP 系统中，在进行 Join 的时候使用过滤和映射会极大的提高性能。同样的，在 Hadoop 中使用 Filte...
hive谓词下推的失效与生效
大数据训练营
05-22 7326
谓词下推的生效：[sql] view plain copyselect * from czw_test2 a  join (select * from czw_test1 where pt >='20121101' and pt <='20121102') b  on (a.search_id = b.search_id)    select count(1)   from czw_t...
大数据SQL中的Join【谓词下推】讲解
大数据技术派
11-10 1428
听到谓词下推这个词，是不是觉得很高大上，找点资料看了半天才能搞懂概念和思想，借这个机会好好学习一下吧。引用范欣欣大佬的博客中写道，以前经常满大街听到谓词下推，然而对谓词下推却总感觉懵懵懂懂...
hive sql 谓词下推
08-25
谓词下推是指在Hive中，尽量将过滤条件提前执行，使得最后参与join的表的数据量更小，从而减少数据传输IO，节约资源，提升性能。无论在Hive中是否开启了CBO（Cost-Based Optimizer），无论谓词写在ON后面还是WHERE...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值