Hive_HIVE优化指南_场景八_数据裁剪问题

最新推荐文章于 2024-05-15 09:30:00 发布
最新推荐文章于 2024-05-15 09:30:00 发布
阅读量760 收藏 1
点赞数
分类专栏： Hive
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u010003835/article/details/105495151
版权

 

Hive优化，大纲地址：

https://blog.csdn.net/u010003835/article/details/105334641

 

关闭优化参数 

由于数据量比较小，所以为了避免 map join , 所以关闭自动 map join.

set hive.auto.convert.join=false;

另外由于 HIVE 的谓词下推是 默认自动开启的，需要将其关闭

set hive.optimize.ppd=false;

 

测试数据 

use data_warehouse_test;
 
 
CREATE TABLE IF NOT EXISTS datacube_salary_org (
 company_name STRING COMMENT '公司名称'
 ,dep_name STRING COMMENT '部门名称'
 ,user_id BIGINT COMMENT '用户id'
 ,user_name STRING COMMENT '用户姓名'
 ,salary DECIMAL(10,2) COMMENT '薪水'
 ,create_time DATE COMMENT '创建时间'
 ,update_time DATE COMMENT '修改时间'
) 
PARTITIONED BY(
 pt STRING COMMENT '数据分区'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE
;
 
 
CREATE TABLE IF NOT EXISTS datacube_salary_basic_aggr(
 company_name STRING COMMENT '公司名称'
 ,dep_name STRING COMMENT '部门名称'
 ,user_id BIGINT COMMENT '用户id'
 ,salary DECIMAL(10,2) COMMENT '薪水'
)
STORED AS ORC
;
 
 
CREATE TABLE IF NOT EXISTS datacube_salary_dep_aggr(
 company_name STRING COMMENT '公司名称'
 ,dep_name STRING COMMENT '部门名称'
 ,total_salary DECIMAL(10,2) COMMENT '薪水'
)
STORED AS ORC
;
 
 
CREATE TABLE IF NOT EXISTS datacube_salary_company_aggr(
 company_name STRING COMMENT '公司名称'
 ,total_salary DECIMAL(10,2) COMMENT '薪水'
)
STORED AS ORC
;
 
 
CREATE TABLE IF NOT EXISTS datacube_salary_total_aggr(
 total_salary DECIMAL(10,2) COMMENT '薪水'
)
STORED AS ORC
;

 

看一下用到的表的数据：

datacube_salary_org 表的数据

+-----------------------------------+-------------------------------+------------------------------+--------------------------------+-----------------------------+----------------------------------+----------------------------------+-------------------------+
| datacube_salary_org.company_name  | datacube_salary_org.dep_name  | datacube_salary_org.user_id  | datacube_salary_org.user_name  | datacube_salary_org.salary  | datacube_salary_org.create_time  | datacube_salary_org.update_time  | datacube_salary_org.pt  |
+-----------------------------------+-------------------------------+------------------------------+--------------------------------+-----------------------------+----------------------------------+----------------------------------+-------------------------+
| s.zh                              | engineer                      | 1                            | szh                            | 28000.00                    | 2020-04-07                       | 2020-04-07                       | 20200405                |
| s.zh                              | engineer                      | 2                            | zyq                            | 26000.00                    | 2020-04-03                       | 2020-04-03                       | 20200405                |
| s.zh                              | tester                        | 3                            | gkm                            | 20000.00                    | 2020-04-07                       | 2020-04-07                       | 20200405                |
| x.qx                              | finance                       | 4                            | pip                            | 13400.00                    | 2020-04-07                       | 2020-04-07                       | 20200405                |
| x.qx                              | finance                       | 5                            | kip                            | 24500.00                    | 2020-04-07                       | 2020-04-07                       | 20200405                |
| x.qx                              | finance                       | 6                            | zxxc                           | 13000.00                    | 2020-04-07                       | 2020-04-07                       | 20200405                |
| x.qx                              | kiccp                         | 7                            | xsz                            | 8600.00                     | 2020-04-07                       | 2020-04-07                       | 20200405                |
| s.zh                              | engineer                      | 1                            | szh                            | 28000.00                    | 2020-04-07                       | 2020-04-07                       | 20200406                |
| s.zh                              | engineer                      | 2                            | zyq                            | 26000.00                    | 2020-04-03                       | 2020-04-03                       | 20200406                |
| s.zh                              | tester                        | 3                            | gkm                            | 20000.00                    | 2020-04-07                       | 2020-04-07                       | 20200406                |
| x.qx                              | finance                       | 4                            | pip                            | 13400.00                    | 2020-04-07                       | 2020-04-07                       | 20200406                |
| x.qx                              | finance                       | 5                            | kip                            | 24500.00                    | 2020-04-07                       | 2020-04-07                       | 20200406                |
| x.qx                              | finance                       | 6                            | zxxc                           | 13000.00                    | 2020-04-07                       | 2020-04-07                       | 20200406                |
| x.qx                              | kiccp                         | 7                            | xsz                            | 8600.00                     | 2020-04-07                       | 2020-04-07                       | 20200406                |
| s.zh                              | enginer                       | 1                            | szh                            | 28000.00                    | 2020-04-07                       | 2020-04-07                       | 20200407                |
| s.zh                              | enginer                       | 2                            | zyq                            | 26000.00                    | 2020-04-03                       | 2020-04-03                       | 20200407                |
| s.zh                              | tester                        | 3                            | gkm                            | 20000.00                    | 2020-04-07                       | 2020-04-07                       | 20200407                |
| x.qx                              | finance                       | 4                            | pip                            | 13400.00                    | 2020-04-07                       | 2020-04-07                       | 20200407                |
| x.qx                              | finance                       | 5                            | kip                            | 24500.00                    | 2020-04-07                       | 2020-04-07                       | 20200407                |
| x.qx                              | finance                       | 6                            | zxxc                           | 13000.00                    | 2020-04-07                       | 2020-04-07                       | 20200407                |
| x.qx                              | kiccp                         | 7                            | xsz                            | 8600.00                     | 2020-04-07                       | 2020-04-07                       | 20200407                |
+-----------------------------------+-------------------------------+------------------------------+--------------------------------+-----------------------------+----------------------------------+----------------------------------+-------------------------+
21 rows selected (0.153 seconds)

datacube_salary_dep_aggr 表的数据

+----------------------------------------+------------------------------------+----------------------------------------+
| datacube_salary_dep_aggr.company_name  | datacube_salary_dep_aggr.dep_name  | datacube_salary_dep_aggr.total_salary  |
+----------------------------------------+------------------------------------+----------------------------------------+
| s.zh                                   | enginer                            | 54000.00                               |
| s.zh                                   | tester                             | 20000.00                               |
| x.qx                                   | finance                            | 50900.00                               |
| x.qx                                   | kiccp                              | 8600.00                                |
+----------------------------------------+------------------------------------+----------------------------------------+
4 rows selected (0.262 seconds)

 

 

 

 

 

场景八_数据裁剪问题

 

1) 记录数裁剪

   i.通过分区，分桶 表的优势 去构建 

  ii.通过筛选条件，去除无效的记录，使得无效数据在 map 阶段 剔除

2) 列裁剪

 i.剔除无效, 非计算范围内的列数据

 ii.使用列式存储

 

 

场景八_数据裁剪问题

 

1) 记录数裁剪

   i.通过分区，分桶 表的优势 去构建 

分桶表的构建过程与使用：https://blog.csdn.net/qq_26803795/article/details/105205702

分区表与分桶表本身就是一个一个优化策略。

分区表  更适用可以合理方式进行数据组织的列，比如 每天的log 日志，由于每天的数据都差不多，可以按分区进行组织！！

分桶表 适用于无法形成合理分区的场景，并且可以比较方便的实现抽取，如用户id列。

当然两者可以相互结合，并无冲突

 

分区表 ： 通过筛选指定的分区，就可以很容易做到数据筛选，因为分区在 hdfs 会以文件夹的形式存在 ！！（分区列 ，可以大幅度提升筛选效率）

分桶表 ：由于分桶列是按照  对列值哈希，然后除以桶的个数求余，决定将该条记录存放到哪个桶中。所以在后续的筛选也有很大的优势 （不用扫描全表）

 

 

  ii.通过筛选条件，去除无效的记录，使得无效数据在 map 阶段 剔除

由于数据量比较小，所以为了避免 map join , 所以关闭自动 map join.

set hive.auto.convert.join=false;

另外由于 HIVE 的谓词下推是 默认自动开启的，需要将其关闭

set hive.optimize.ppd=false;

 

 

 

这个也比较好理解：

例如 下面3个语句执行的结果都是一致的。

SELECT 
 a.user_id
 ,b.user_id
FROM datacube_salary_org  AS a
JOIN datacube_salary_org  AS b
 ON a.user_id = b.user_id
 AND a.salary >= 10000.0
 AND b.salary >= 10000.0
ORDER BY a.user_id, b.user_id
;

SELECT
 tmp_a.user_id
 ,tmp_b.user_id
FROM
(
SELECT 
 *
FROM datacube_salary_org  AS a
WHERE a.salary >= 10000.0
) tmp_a
JOIN 
(
SELECT 
 *
FROM datacube_salary_org  AS b
WHERE b.salary >= 10000.0
) tmp_b
ON tmp_a.user_id = tmp_b.user_id
ORDER BY tmp_a.user_id, tmp_b.user_id
;
SELECT 
 a.user_id
 ,b.user_id
FROM datacube_salary_org  AS a
JOIN datacube_salary_org  AS b
 ON a.user_id = b.user_id
WHERE 
 a.salary >= 10000.0
 AND b.salary >= 10000.0
ORDER BY a.user_id, b.user_id
;

 

运行结果

+------------+------------+
| a.user_id  | b.user_id  |
+------------+------------+
| 1          | 1          |
| 1          | 1          |
| 1          | 1          |
| 1          | 1          |
| 1          | 1          |
| 1          | 1          |
| 1          | 1          |
| 1          | 1          |
| 1          | 1          |
| 2          | 2          |
| 2          | 2          |
| 2          | 2          |
| 2          | 2          |
| 2          | 2          |
| 2          | 2          |
| 2          | 2          |
| 2          | 2          |
| 2          | 2          |
| 3          | 3          |
| 3          | 3          |
| 3          | 3          |
| 3          | 3          |
| 3          | 3          |
| 3          | 3          |
| 3          | 3          |
| 3          | 3          |
| 3          | 3          |
| 4          | 4          |
| 4          | 4          |
| 4          | 4          |
| 4          | 4          |
| 4          | 4          |
| 4          | 4          |
| 4          | 4          |
| 4          | 4          |
| 4          | 4          |
| 5          | 5          |
| 5          | 5          |
| 5          | 5          |
| 5          | 5          |
| 5          | 5          |
| 5          | 5          |
| 5          | 5          |
| 5          | 5          |
| 5          | 5          |
| 6          | 6          |
| 6          | 6          |
| 6          | 6          |
| 6          | 6          |
| 6          | 6          |
| 6          | 6          |
| 6          | 6          |
| 6          | 6          |
| 6          | 6          |
+------------+------------+
54 rows selected (51.263 seconds)

 

我们比较下3个语句的执行计划 ， 利用 EXPLAIN

EXPLAIN 
SELECT 
 a.user_id
 ,b.user_id
FROM datacube_salary_org  AS a
JOIN datacube_salary_org  AS b
 ON a.user_id = b.user_id
 AND a.salary >= 10000.0
 AND b.salary >= 10000.0
ORDER BY a.user_id, b.user_id
;

+----------------------------------------------------+
|                      Explain                       |
+----------------------------------------------------+
| STAGE DEPENDENCIES:                                |
|   Stage-1 is a root stage                          |
|   Stage-2 depends on stages: Stage-1               |
|   Stage-0 depends on stages: Stage-2               |
|                                                    |
| STAGE PLANS:                                       |
|   Stage: Stage-1                                   |
|     Map Reduce                                     |
|       Map Operator Tree:                           |
|           TableScan                                |
|             alias: a                               |
|             filterExpr: ((salary >= 10000.0) and user_id is not null) (type: boolean) |
|             Statistics: Num rows: 7 Data size: 340 Basic stats: COMPLETE Column stats: NONE |
|             Filter Operator                        |
|               predicate: ((salary >= 10000.0) and user_id is not null) (type: boolean) |
|               Statistics: Num rows: 2 Data size: 97 Basic stats: COMPLETE Column stats: NONE |
|               Reduce Output Operator               |
|                 key expressions: user_id (type: bigint) |
|                 sort order: +                      |
|                 Map-reduce partition columns: user_id (type: bigint) |
|                 Statistics: Num rows: 2 Data size: 97 Basic stats: COMPLETE Column stats: NONE |
|           TableScan                                |
|             alias: b                               |
|             filterExpr: ((salary >= 10000.0) and user_id is not null) (type: boolean) |
|             Statistics: Num rows: 7 Data size: 340 Basic stats: COMPLETE Column stats: NONE |
|             Filter Operator                        |
|               predicate: ((salary >= 10000.0) and user_id is not null) (type: boolean) |
|               Statistics: Num rows: 2 Data size: 97 Basic stats: COMPLETE Column stats: NONE |
|               Reduce Output Operator               |
|                 key expressions: user_id (type: bigint) |
|                 sort order: +                      |
|                 Map-reduce partition columns: user_id (type: bigint) |
|                 Statistics: Num rows: 2 Data size: 97 Basic stats: COMPLETE Column stats: NONE |
|       Reduce Operator Tree:                        |
|         Join Operator                              |
|           condition map:                           |
|                Inner Join 0 to 1                   |
|           keys:                                    |
|             0 user_id (type: bigint)               |
|             1 user_id (type: bigint)               |
|           outputColumnNames: _col2, _col13         |
|           Statistics: Num rows: 2 Data size: 106 Basic stats: COMPLETE Column stats: NONE |
|           Select Operator                          |
|             expressions: _col2 (type: bigint), _col13 (type: bigint) |
|             outputColumnNames: _col0, _col1        |
|             Statistics: Num rows: 2 Data size: 106 Basic stats: COMPLETE Column stats: NONE |
|             File Output Operator                   |
|               compressed: false                    |
|               table:                               |
|                   input format: org.apache.hadoop.mapred.SequenceFileInputFormat |
|                   output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat |
|                   serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe |
|                                                    |
|   Stage: Stage-2                                   |
|     Map Reduce                                     |
|       Map Operator Tree:                           |
|           TableScan                                |
|             Reduce Output Operator                 |
|               key expressions: _col0 (type: bigint), _col1 (type: bigint) |
|               sort order: ++                       |
|               Statistics: Num rows: 2 Data size: 106 Basic stats: COMPLETE Column stats: NONE |
|       Reduce Operator Tree:                        |
|         Select Operator                            |
|           expressions: KEY.reducesinkkey0 (type: bigint), KEY.reducesinkkey1 (type: bigint) |
|           outputColumnNames: _col0, _col1          |
|           Statistics: Num rows: 2 Data size: 106 Basic stats: COMPLETE Column stats: NONE |
|           File Output Operator                     |
|             compressed: false                      |
|             Statistics: Num rows: 2 Data size: 106 Basic stats: COMPLETE Column stats: NONE |
|             table:                                 |
|                 input format: org.apache.hadoop.mapred.SequenceFileInputFormat |
|                 output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat |
|                 serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe |
|                                                    |
|   Stage: Stage-0                                   |
|     Fetch Operator                                 |
|       limit: -1                                    |
|       Processor Tree:                              |
|         ListSink                                   |
|                                                    |
+----------------------------------------------------+
80 rows selected (0.165 seconds)

 

==============================

 

第二种方式

EXPLAIN
SELECT
 tmp_a.user_id
 ,tmp_b.user_id
FROM
(
SELECT 
 *
FROM datacube_salary_org  AS a
WHERE a.salary >= 10000.0
) tmp_a
JOIN 
(
SELECT 
 *
FROM datacube_salary_org  AS b
WHERE b.salary >= 10000.0
) tmp_b
ON tmp_a.user_id = tmp_b.user_id
ORDER BY tmp_a.user_id, tmp_b.user_id
;
+----------------------------------------------------+
|                      Explain                       |
+----------------------------------------------------+
| STAGE DEPENDENCIES:                                |
|   Stage-1 is a root stage                          |
|   Stage-2 depends on stages: Stage-1               |
|   Stage-0 depends on stages: Stage-2               |
|                                                    |
| STAGE PLANS:                                       |
|   Stage: Stage-1                                   |
|     Map Reduce                                     |
|       Map Operator Tree:                           |
|           TableScan                                |
|             alias: a                               |
|             Statistics: Num rows: 7 Data size: 340 Basic stats: COMPLETE Column stats: NONE |
|             Filter Operator                        |
|               predicate: (salary >= 10000.0) (type: boolean) |
|               Statistics: Num rows: 2 Data size: 97 Basic stats: COMPLETE Column stats: NONE |
|               Select Operator                      |
|                 expressions: user_id (type: bigint) |
|                 outputColumnNames: _col2           |
|                 Statistics: Num rows: 2 Data size: 97 Basic stats: COMPLETE Column stats: NONE |
|                 Filter Operator                    |
|                   predicate: _col2 is not null (type: boolean) |
|                   Statistics: Num rows: 2 Data size: 97 Basic stats: COMPLETE Column stats: NONE |
|                   Reduce Output Operator           |
|                     key expressions: _col2 (type: bigint) |
|                     sort order: +                  |
|                     Map-reduce partition columns: _col2 (type: bigint) |
|                     Statistics: Num rows: 2 Data size: 97 Basic stats: COMPLETE Column stats: NONE |
|           TableScan                                |
|             alias: b                               |
|             Statistics: Num rows: 7 Data size: 340 Basic stats: COMPLETE Column stats: NONE |
|             Filter Operator                        |
|               predicate: (salary >= 10000.0) (type: boolean) |
|               Statistics: Num rows: 2 Data size: 97 Basic stats: COMPLETE Column stats: NONE |
|               Select Operator                      |
|                 expressions: user_id (type: bigint) |
|                 outputColumnNames: _col2           |
|                 Statistics: Num rows: 2 Data size: 97 Basic stats: COMPLETE Column stats: NONE |
|                 Filter Operator                    |
|                   predicate: _col2 is not null (type: boolean) |
|                   Statistics: Num rows: 2 Data size: 97 Basic stats: COMPLETE Column stats: NONE |
|                   Reduce Output Operator           |
|                     key expressions: _col2 (type: bigint) |
|                     sort order: +                  |
|                     Map-reduce partition columns: _col2 (type: bigint) |
|                     Statistics: Num rows: 2 Data size: 97 Basic stats: COMPLETE Column stats: NONE |
|       Reduce Operator Tree:                        |
|         Join Operator                              |
|           condition map:                           |
|                Inner Join 0 to 1                   |
|           keys:                                    |
|             0 _col2 (type: bigint)                 |
|             1 _col2 (type: bigint)                 |
|           outputColumnNames: _col2, _col10         |
|           Statistics: Num rows: 2 Data size: 106 Basic stats: COMPLETE Column stats: NONE |
|           Select Operator                          |
|             expressions: _col2 (type: bigint), _col10 (type: bigint) |
|             outputColumnNames: _col0, _col1        |
|             Statistics: Num rows: 2 Data size: 106 Basic stats: COMPLETE Column stats: NONE |
|             File Output Operator                   |
|               compressed: false                    |
|               table:                               |
|                   input format: org.apache.hadoop.mapred.SequenceFileInputFormat |
|                   output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat |
|                   serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe |
|                                                    |
|   Stage: Stage-2                                   |
|     Map Reduce                                     |
|       Map Operator Tree:                           |
|           TableScan                                |
|             Reduce Output Operator                 |
|               key expressions: _col0 (type: bigint), _col1 (type: bigint) |
|               sort order: ++                       |
|               Statistics: Num rows: 2 Data size: 106 Basic stats: COMPLETE Column stats: NONE |
|       Reduce Operator Tree:                        |
|         Select Operator                            |
|           expressions: KEY.reducesinkkey0 (type: bigint), KEY.reducesinkkey1 (type: bigint) |
|           outputColumnNames: _col0, _col1          |
|           Statistics: Num rows: 2 Data size: 106 Basic stats: COMPLETE Column stats: NONE |
|           File Output Operator                     |
|             compressed: false                      |
|             Statistics: Num rows: 2 Data size: 106 Basic stats: COMPLETE Column stats: NONE |
|             table:                                 |
|                 input format: org.apache.hadoop.mapred.SequenceFileInputFormat |
|                 output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat |
|                 serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe |
|                                                    |
|   Stage: Stage-0                                   |
|     Fetch Operator                                 |
|       limit: -1                                    |
|       Processor Tree:                              |
|         ListSink                                   |
|                                                    |
+----------------------------------------------------+
92 rows selected (0.166 seconds)

 

==============================

 

第三种方式

EXPLAIN 
SELECT 
 a.user_id
 ,b.user_id
FROM datacube_salary_org  AS a
JOIN datacube_salary_org  AS b
 ON a.user_id = b.user_id
WHERE 
 a.salary >= 10000.0
 AND b.salary >= 10000.0
ORDER BY a.user_id, b.user_id
;
+----------------------------------------------------+
|                      Explain                       |
+----------------------------------------------------+
| STAGE DEPENDENCIES:                                |
|   Stage-1 is a root stage                          |
|   Stage-2 depends on stages: Stage-1               |
|   Stage-0 depends on stages: Stage-2               |
|                                                    |
| STAGE PLANS:                                       |
|   Stage: Stage-1                                   |
|     Map Reduce                                     |
|       Map Operator Tree:                           |
|           TableScan                                |
|             alias: a                               |
|             Statistics: Num rows: 7 Data size: 340 Basic stats: COMPLETE Column stats: NONE |
|             Filter Operator                        |
|               predicate: user_id is not null (type: boolean) |
|               Statistics: Num rows: 7 Data size: 340 Basic stats: COMPLETE Column stats: NONE |
|               Reduce Output Operator               |
|                 key expressions: user_id (type: bigint) |
|                 sort order: +                      |
|                 Map-reduce partition columns: user_id (type: bigint) |
|                 Statistics: Num rows: 7 Data size: 340 Basic stats: COMPLETE Column stats: NONE |
|                 value expressions: salary (type: decimal(10,2)) |
|           TableScan                                |
|             alias: b                               |
|             Statistics: Num rows: 7 Data size: 340 Basic stats: COMPLETE Column stats: NONE |
|             Filter Operator                        |
|               predicate: user_id is not null (type: boolean) |
|               Statistics: Num rows: 7 Data size: 340 Basic stats: COMPLETE Column stats: NONE |
|               Reduce Output Operator               |
|                 key expressions: user_id (type: bigint) |
|                 sort order: +                      |
|                 Map-reduce partition columns: user_id (type: bigint) |
|                 Statistics: Num rows: 7 Data size: 340 Basic stats: COMPLETE Column stats: NONE |
|                 value expressions: salary (type: decimal(10,2)) |
|       Reduce Operator Tree:                        |
|         Join Operator                              |
|           condition map:                           |
|                Inner Join 0 to 1                   |
|           keys:                                    |
|             0 user_id (type: bigint)               |
|             1 user_id (type: bigint)               |
|           outputColumnNames: _col2, _col4, _col13, _col15 |
|           Statistics: Num rows: 7 Data size: 374 Basic stats: COMPLETE Column stats: NONE |
|           Filter Operator                          |
|             predicate: ((_col4 >= 10000.0) and (_col15 >= 10000.0)) (type: boolean) |
|             Statistics: Num rows: 1 Data size: 53 Basic stats: COMPLETE Column stats: NONE |
|             Select Operator                        |
|               expressions: _col2 (type: bigint), _col13 (type: bigint) |
|               outputColumnNames: _col0, _col1      |
|               Statistics: Num rows: 1 Data size: 53 Basic stats: COMPLETE Column stats: NONE |
|               File Output Operator                 |
|                 compressed: false                  |
|                 table:                             |
|                     input format: org.apache.hadoop.mapred.SequenceFileInputFormat |
|                     output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat |
|                     serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe |
|                                                    |
|   Stage: Stage-2                                   |
|     Map Reduce                                     |
|       Map Operator Tree:                           |
|           TableScan                                |
|             Reduce Output Operator                 |
|               key expressions: _col0 (type: bigint), _col1 (type: bigint) |
|               sort order: ++                       |
|               Statistics: Num rows: 1 Data size: 53 Basic stats: COMPLETE Column stats: NONE |
|       Reduce Operator Tree:                        |
|         Select Operator                            |
|           expressions: KEY.reducesinkkey0 (type: bigint), KEY.reducesinkkey1 (type: bigint) |
|           outputColumnNames: _col0, _col1          |
|           Statistics: Num rows: 1 Data size: 53 Basic stats: COMPLETE Column stats: NONE |
|           File Output Operator                     |
|             compressed: false                      |
|             Statistics: Num rows: 1 Data size: 53 Basic stats: COMPLETE Column stats: NONE |
|             table:                                 |
|                 input format: org.apache.hadoop.mapred.SequenceFileInputFormat |
|                 output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat |
|                 serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe |
|                                                    |
|   Stage: Stage-0                                   |
|     Fetch Operator                                 |
|       limit: -1                                    |
|       Processor Tree:                              |
|         ListSink                                   |
|                                                    |
+----------------------------------------------------+
83 rows selected (0.193 seconds)

 

可以看到 第一种 ， 第二种方式的执行计划 基本一致。 第三个执行计划，可以看到 把筛选条件 放到了 JOIN 之后， 在大数据量下，必定慢 ：

|       Reduce Operator Tree:                        |
|         Join Operator                              |
|           condition map:                           |
|                Inner Join 0 to 1                   |
|           keys:                                    |
|             0 user_id (type: bigint)               |
|             1 user_id (type: bigint)               |
|           outputColumnNames: _col2, _col4, _col13, _col15 |
|           Statistics: Num rows: 7 Data size: 374 Basic stats: COMPLETE Column stats: NONE |
|           Filter Operator                          |
|             predicate: ((_col4 >= 10000.0) and (_col15 >= 10000.0)) (type: boolean) |
|             Statistics: Num rows: 1 Data size: 53 Basic stats: COMPLETE Column stats: NONE |
|             Select Operator                        |
|               expressions: _col2 (type: bigint), _col13 (type: bigint) |
|               outputColumnNames: _col0, _col1      |
|               Statistics: Num rows: 1 Data size: 53 Basic stats: COMPLETE Column stats: NONE |
|               File Output Operator                 |
|                 compressed: false                  |
|                 table:                             |
|                     input format: org.apache.hadoop.mapred.SequenceFileInputFormat |
|                     output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat |
|                     serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe |

 

 

 

另外 我们需要注意 第二种方式 

SELECT
 tmp_a.user_id
 ,tmp_b.user_id
FROM
(
SELECT 
 *
FROM datacube_salary_org  AS a
WHERE a.salary >= 10000.0
) tmp_a
JOIN 
(
SELECT 
 *
FROM datacube_salary_org  AS b
WHERE b.salary >= 10000.0
) tmp_b
ON tmp_a.user_id = tmp_b.user_id
ORDER BY tmp_a.user_id, tmp_b.user_id
;

需要注意 :  虽然我们子查询里面用的是 * 。 但是实际上，执行器会帮我优化，只选取 user_id !!!

 

 

 

2) 列裁剪

 i.剔除无效, 非计算范围内的列数据

这个比较好理解，在 SELECT 之中对 需要的列做筛选。而不是使用 SELECT * 

下面，我们看下这两种方式有什么区别：

 

只选取两列

EXPLAIN
SELECT 
 a.user_id
 ,a.company_name
FROM datacube_salary_org  AS a
WHERE 
 a.pt = '20200406'
;
+----------------------------------------------------+
|                      Explain                       |
+----------------------------------------------------+
| STAGE DEPENDENCIES:                                |
|   Stage-1 is a root stage                          |
|   Stage-0 depends on stages: Stage-1               |
|                                                    |
| STAGE PLANS:                                       |
|   Stage: Stage-1                                   |
|     Map Reduce                                     |
|       Map Operator Tree:                           |
|           TableScan                                |
|             alias: a                               |
|             Statistics: Num rows: 7 Data size: 984 Basic stats: COMPLETE Column stats: PARTIAL |
|             Filter Operator                        |
|               predicate: (pt = '20200406') (type: boolean) |
|               Statistics: Num rows: 7 Data size: 644 Basic stats: COMPLETE Column stats: PARTIAL |
|               Select Operator                      |
|                 expressions: user_id (type: bigint), company_name (type: string) |
|                 outputColumnNames: _col0, _col1    |
|                 Statistics: Num rows: 7 Data size: 0 Basic stats: PARTIAL Column stats: PARTIAL |
|                 File Output Operator               |
|                   compressed: false                |
|                   Statistics: Num rows: 7 Data size: 0 Basic stats: PARTIAL Column stats: PARTIAL |
|                   table:                           |
|                       input format: org.apache.hadoop.mapred.SequenceFileInputFormat |
|                       output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat |
|                       serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe |
|                                                    |
|   Stage: Stage-0                                   |
|     Fetch Operator                                 |
|       limit: -1                                    |
|       Processor Tree:                              |
|         ListSink                                   |
|                                                    |
+----------------------------------------------------+
32 rows selected (0.133 seconds)

 

========================================================

 

选取所有列

EXPLAIN
SELECT 
 a.*
FROM datacube_salary_org  AS a
WHERE 
 a.pt = '20200406'
;
+----------------------------------------------------+
|                      Explain                       |
+----------------------------------------------------+
| STAGE DEPENDENCIES:                                |
|   Stage-1 is a root stage                          |
|   Stage-0 depends on stages: Stage-1               |
|                                                    |
| STAGE PLANS:                                       |
|   Stage: Stage-1                                   |
|     Map Reduce                                     |
|       Map Operator Tree:                           |
|           TableScan                                |
|             alias: a                               |
|             Statistics: Num rows: 7 Data size: 984 Basic stats: COMPLETE Column stats: PARTIAL |
|             Filter Operator                        |
|               predicate: (pt = '20200406') (type: boolean) |
|               Statistics: Num rows: 7 Data size: 644 Basic stats: COMPLETE Column stats: PARTIAL |
|               Select Operator                      |
|                 expressions: company_name (type: string), dep_name (type: string), user_id (type: bigint), user_name (type: string), salary (type: decimal(10,2)), create_time (type: date), update_time (type: date), '20200406' (type: string) |
|                 outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7 |
|                 Statistics: Num rows: 7 Data size: 644 Basic stats: COMPLETE Column stats: PARTIAL |
|                 File Output Operator               |
|                   compressed: false                |
|                   Statistics: Num rows: 7 Data size: 644 Basic stats: COMPLETE Column stats: PARTIAL |
|                   table:                           |
|                       input format: org.apache.hadoop.mapred.SequenceFileInputFormat |
|                       output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat |
|                       serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe |
|                                                    |
|   Stage: Stage-0                                   |
|     Fetch Operator                                 |
|       limit: -1                                    |
|       Processor Tree:                              |
|         ListSink                                   |
|                                                    |
+----------------------------------------------------+
32 rows selected (0.117 seconds)

 

 

 

 ii.使用列式存储

    使用列式存储的 作为 表的存储方式，也可以帮助我们更迅速的实现对列 的快速筛选。

目前 Hive 支持的列式存储方式 ：  PARQUET,  RCFILE(不推荐),  ORCFILE

 

参考文章：

https://blog.csdn.net/u010003835/article/details/88236132

 

 

 

高达一号
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Hive>分区剪裁、列剪裁
qq_44509920的博客
12-02 2340
使用分区剪裁、列剪裁 在SELECT中，只拿需要的列，如果有，尽量使用分区过滤，少用SELECT *。 在分区剪裁中，当使用外关联时，如果将副表的过滤条件写在Where后面，那么就会先全表关联，之后再过滤，比如： 环境准备： 创建表1 create table ori(id bigint, time bigint, uid string, keyword string, url_rank int,...
Hive性能优化（全面）
大数据hive教程详解
01-13 733
1.介绍 首先，我们来看看Hadoop的计算框架特性，在此特性下会衍生哪些问题？ 数据量大不是问题，数据倾斜是个问题。 jobs数比较多的作业运行效率相对比较低，比如即使有几百行的表，如果多次关联多次汇总，产生十几个jobs，耗时很长。原因是map reduce作业初始化的时间是比较长的。 sum,count,max,min等UDAF，不怕数据倾斜问题,hadoop在map端的汇总合并优化...
Hive优化---or
Today_2018的博客
02-24 1160
优化前 select count(b.user_initial_ccid) from dwd.dwd_ccid_register_a a left join audience_data_service.sys_user_all b on ((b.user_id=a.union_id_md5 and b.user_type_id=15) or (b.user_id=a.mobile_md5 and b.user_type_id=16) or (b.user_id=a.email_md5 and b.use
hive_hql开发规范（SQL优化）
最新发布
wowulita123的博客
05-15 1104
hive_hql开发规范（sql优化）
01-hive优化总结经验
qq_35281775的博客
10-31 349
hive优化总结 . Hive是将符合SQL语法的字符串解析生成可以在Hadoop上执行的MapReduce的工具。使用Hive尽量按照分布式计算的一些特点来设计sql，和传统关系型数据库有区别， 所以需要去掉原有关系型数据库下开发的一些固有思维。 基本原则： 1：尽量尽早地过滤数据，减少每个阶段的数据量,对于分区表要加分区，同时只选择需要使用到的字段 select ... from A...
Hive 调优 分区裁剪、列裁剪
大数据流浪法师的学习笔记与分享
11-28 2221
使用分区剪裁、列剪裁 在SELECT中，只拿需要的列，如果有，尽量使用分区过滤，少用SELECT *。 在分区剪裁中，当使用外关联时，如果将副表的过滤条件写在Where后面，那么就会先全表关联，之后再过滤，比如： 环境准备： create table ori(id bigint, time bigint, uid string, keyword string, url_rank int, clic...
hive高级分析函数与优化.rar_hive_hive 函数优化_hive 分析 大数据_hive高级优化_oldwlc
09-23
压缩可以减小存储空间，数据倾斜优化是解决数据分布不均问题，Join优化涉及MapJOIN、BroadcastJOIN等，SubQuery优化则涉及到子查询的重写和执行顺序调整。 5. **OldWLC（ Warehouse Load Coordinator）**： OldWLC...
hive_site.zip
10-08
hive_site.xml，供参考。 hive_site.xml，供参考 hive_site.xml，供参考 hive_site.xml，供参考 hive_site.xml，供参考 hive_site.xml，供参考
flink-connector-hive_2.11-1.13.1.jar
03-14
flink-connector-hive_2.11-1.13.1
hive_jdbc_2.6.2.1002.zip
08-22
Hive JDBC 2.6.2.1002 是一个关键组件，它在大数据处理领域扮演着重要的角色。Hive 是由 Apache 软件基金会开发的一个数据仓库工具，它允许用户使用类 SQL 查询语言（HQL）来处理存储在分布式文件系统中的大规模数据...
spark-hive_2.11-2.1.3-SNAPSHOT.jar
04-01
spark-hive_2.11-2.1.3-SNAPSHOT.jar
hive hive.optimize.ppd=false导致map数量很大
空中的鱼
01-03 596
hive官方https://cwiki.apache.org/confluence/display/Hive/LanguageManual+LateralView 说 Prior to Hive 0.6.0, lateral view did not support the predicate push-down optimization. In Hive 0.5.0 and earlier,...
四 、Hive逻辑优化
jiayuanv_127的博客
03-20 1488
Hive生成逻辑算之后，会对逻辑算子进行优化。优化的目的是为了减少不必要的数据处理、消除冗余运算，以到达提高执行效率的目的，这个过程称为逻辑优化。 4.1 逻辑优化类型 Hive逻辑优化包括以下几点： 1) 谓词下推 谓词下推指的是将外层HiveQL的过滤表达式提前到内层HiveQL，以达到提前过滤，减少数据的处理量。如语句:selecta.* , b.* from a join b on
hive综合
baiyunl的专栏
03-16 211
Hive 是什么 在接触一个新的事物首先要回到的问题是：这是什么？ 这里引用 Hive wiki 上的介绍： Hive is a data warehouse infrastructure built on top of Hadoop. It provides tools to enable easy data ETL, a mechanism to put structures on ...
hive优化
MRzhenglea的博客
04-17 1673
命令 explain (sql语句)查看详细的MR explain extended (sql语句) 查看参数的MR 动态分区 就是说给分区表导入数据的时候，想给数据动态分区插入进去 1首先开去动态分区功能 set hive.exec.dynamic.partition=true; 2 设置为非严格模式（动态分区的模式，默认strict,表示必须指定至少一个分区为静态分区，nonstrict表示允许所有的分区字段都可以使用动态分区） set hive.exec.dynamic.partition.mode=
Hive优化语句
lz_N_one的博客
07-26 393
Hive的优化方法
Hive SQL 及 hive参数 优化
m0_47668312的博客
12-29 663
Hive的优化 主要分为：配置优化、SQL语句优化、任务优化等方案。其中在开发过程中主要涉及到的可能是SQL优化这块。 优化的核心思想是： 减少数据量（例如分区、列剪裁） 避免数据倾斜（例如加参数、Key打散） 避免全表扫描（例如on添加加上分区等） 减少job数（例如相同的on条件的join放在一起作为一个任务） 1. 使用分区剪裁、列剪裁 在分区剪裁中，当使用外关联时，如果将副表的过滤条件写在Where后面，那么就会先全表关联，之后再过滤。 select a.* from a left join
动态分区导致__HIVE_DEFAULT_PARTITION__
01-20
当启用动态分区时，如果指定的分区键存在空值，Hive会自动创建一个名为__HIVE_DEFAULT_PARTITION__的默认分区来存储这部分数据。 这个默认分区的目的是为了处理空值情况，以保证数据的完整性和一致性。当动态分区的...

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值