Hive_HIVE优化指南_场景五_排序问题

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

优化大纲：https://blog.csdn.net/u010003835/article/details/105334641

 

 

 

 

测试表以及测试数据

+----------------------------------------------------+
|                   createtab_stmt                   |
+----------------------------------------------------+
| CREATE TABLE `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 SERDE                                   |
|   'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'  |
| WITH SERDEPROPERTIES (                             |
|   'field.delim'=',',                               |
|   'serialization.format'=',')                      |
| STORED AS INPUTFORMAT                              |
|   'org.apache.hadoop.mapred.TextInputFormat'       |
| OUTPUTFORMAT                                       |
|   'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' |
| LOCATION                                           |
|   'hdfs://cdh-manager:8020/user/hive/warehouse/data_warehouse_test.db/datacube_salary_org' |
| TBLPROPERTIES (                                    |
|   'transient_lastDdlTime'='1586310488')            |
+----------------------------------------------------+

+-----------------------------------+-------------------------------+------------------------------+--------------------------------+-----------------------------+----------------------------------+----------------------------------+-------------------------+
| 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                |
+-----------------------------------+-------------------------------+------------------------------+--------------------------------+-----------------------------+----------------------------------+----------------------------------+-------------------------+

 

 

 

场景五.排序问题

1) 合理使用 ORDER BY 与 SORT BY , 在两者之间做取舍

2) 通过使用 LIMIT 限制排序的输出

 

 

1) 合理使用 ORDER BY 与 SORT BY , 在两者之间做取舍

 

   应该尽量避免使用  ORDER BY ，ORDER BY 会在全局进行排序。会单独增加一个作业，在全局进行排序。并且在排序中，数据会被分放在一个Reducer 中。

   SORT BY 只是在每一个 reducer 中，进行局部的排序。

 

这里不熟悉SORT BY 的同学，可以看下我的这篇文章。

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

 

可以看到这SORT BY 与  ORDER BY 是不一样的。

我们利用以上数据执行下排序，看看两者有什么区别：

 

 

SORT BY

我们先设置下 Reduce 的数量：

因为数量量的问题，我们的Reducer 默认是1。设置 Reducer 数量，主要是帮助我们调整数据的分发问题。

set mapred.reduce.tasks=5;

 

SORT BY SQL

SELECT user_id, user_name, salary  
FROM datacube_salary_org 
SORT BY user_id;

SORT BY 的 EXPLAIN 结果：

INFO  : Starting task [Stage-2:EXPLAIN] in serial mode
INFO  : Completed executing command(queryId=hive_20200414042647_8a05e5e1-5eff-4fe7-b3e1-10e84b19c6c0); Time taken: 0.003 seconds
INFO  : OK
+----------------------------------------------------+
|                      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: datacube_salary_org             |
|             Statistics: Num rows: 7 Data size: 340 Basic stats: COMPLETE Column stats: NONE |
|             Select Operator                        |
|               expressions: user_id (type: bigint), user_name (type: string), salary (type: decimal(10,2)) |
|               outputColumnNames: _col0, _col1, _col2 |
|               Statistics: Num rows: 7 Data size: 340 Basic stats: COMPLETE Column stats: NONE |
|               Reduce Output Operator               |
|                 key expressions: _col0 (type: bigint) |
|                 sort order: +                      |
|                 Statistics: Num rows: 7 Data size: 340 Basic stats: COMPLETE Column stats: NONE |
|                 value expressions: _col1 (type: string), _col2 (type: decimal(10,2)) |
|       Reduce Operator Tree:                        |
|         Select Operator                            |
|           expressions: KEY.reducesinkkey0 (type: bigint), VALUE._col0 (type: string), VALUE._col1 (type: decimal(10,2)) |
|           outputColumnNames: _col0, _col1, _col2   |
|           Statistics: Num rows: 7 Data size: 340 Basic stats: COMPLETE Column stats: NONE |
|           File Output Operator                     |
|             compressed: false                      |
|             Statistics: Num rows: 7 Data size: 340 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                                   |
|                                                    |
+----------------------------------------------------+
39 rows selected (0.153 seconds)

执行结果：

INFO  : Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 5
INFO  : 2020-04-14 04:28:56,154 Stage-1 map = 0%,  reduce = 0%
INFO  : 2020-04-14 04:29:03,348 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 2.02 sec
INFO  : 2020-04-14 04:29:08,477 Stage-1 map = 100%,  reduce = 20%, Cumulative CPU 3.77 sec
INFO  : 2020-04-14 04:29:12,587 Stage-1 map = 100%,  reduce = 40%, Cumulative CPU 5.53 sec
INFO  : 2020-04-14 04:29:16,693 Stage-1 map = 100%,  reduce = 60%, Cumulative CPU 7.04 sec
INFO  : 2020-04-14 04:29:20,804 Stage-1 map = 100%,  reduce = 80%, Cumulative CPU 8.76 sec
INFO  : 2020-04-14 04:29:25,005 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 10.99 sec
INFO  : MapReduce Total cumulative CPU time: 10 seconds 990 msec
INFO  : Ended Job = job_1586835454940_0014
INFO  : MapReduce Jobs Launched: 
INFO  : Stage-Stage-1: Map: 1  Reduce: 5   Cumulative CPU: 10.99 sec   HDFS Read: 28509 HDFS Write: 1002 SUCCESS
INFO  : Total MapReduce CPU Time Spent: 10 seconds 990 msec
INFO  : Completed executing command(queryId=hive_20200414042847_8447505f-581e-4204-804c-f63d97dcd553); Time taken: 39.228 seconds
INFO  : OK
+----------+------------+-----------+
| user_id  | user_name  |  salary   |
+----------+------------+-----------+
| 6        | zxxc       | 13000.00  |
| 7        | xsz        | 8600.00   |
| 5        | kip        | 24500.00  |
| 6        | zxxc       | 13000.00  |
| 7        | xsz        | 8600.00   |
| 1        | szh        | 28000.00  |
| 1        | szh        | 28000.00  |
| 5        | kip        | 24500.00  |
| 5        | kip        | 24500.00  |
| 2        | zyq        | 26000.00  |
| 2        | zyq        | 26000.00  |
| 2        | zyq        | 26000.00  |
| 3        | gkm        | 20000.00  |
| 4        | pip        | 13400.00  |
| 4        | pip        | 13400.00  |
| 1        | szh        | 28000.00  |
| 3        | gkm        | 20000.00  |
| 3        | gkm        | 20000.00  |
| 4        | pip        | 13400.00  |
| 6        | zxxc       | 13000.00  |
| 7        | xsz        | 8600.00   |
+----------+------------+-----------+
21 rows selected (39.402 seconds)

可以看到是5个升序的子结果，也印证了数据被分发到了5个reducer 

| 6        | zxxc       | 13000.00  |
| 7        | xsz        | 8600.00   |

| 5        | kip        | 24500.00  |
| 6        | zxxc       | 13000.00  |
| 7        | xsz        | 8600.00   |

| 1        | szh        | 28000.00  |
| 1        | szh        | 28000.00  |
| 5        | kip        | 24500.00  |
| 5        | kip        | 24500.00  |

| 2        | zyq        | 26000.00  |
| 2        | zyq        | 26000.00  |
| 2        | zyq        | 26000.00  |
| 3        | gkm        | 20000.00  |
| 4        | pip        | 13400.00  |
| 4        | pip        | 13400.00  |

| 1        | szh        | 28000.00  |
| 3        | gkm        | 20000.00  |
| 3        | gkm        | 20000.00  |
| 4        | pip        | 13400.00  |
| 6        | zxxc       | 13000.00  |
| 7        | xsz        | 8600.00   |

 

ORDER BY

 

我们先设置下 Reduce 的数量：

因为数量量的问题，我们的Reducer 默认是1。设置 Reducer 数量，主要是帮助我们调整数据的分发问题。

set mapred.reduce.tasks=5;

ORDER BY SQL

SELECT user_id, user_name, salary  
FROM datacube_salary_org 
ORDER BY user_id;

EXPLAIN 结果

INFO  : Starting task [Stage-2:EXPLAIN] in serial mode
INFO  : Completed executing command(queryId=hive_20200414044711_439184d4-6d74-46d6-8b79-1853b21090f2); Time taken: 0.006 seconds
INFO  : OK
+----------------------------------------------------+
|                      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: datacube_salary_org             |
|             Statistics: Num rows: 7 Data size: 340 Basic stats: COMPLETE Column stats: NONE |
|             Select Operator                        |
|               expressions: user_id (type: bigint), user_name (type: string), salary (type: decimal(10,2)) |
|               outputColumnNames: _col0, _col1, _col2 |
|               Statistics: Num rows: 7 Data size: 340 Basic stats: COMPLETE Column stats: NONE |
|               Reduce Output Operator               |
|                 key expressions: _col0 (type: bigint) |
|                 sort order: +                      |
|                 Statistics: Num rows: 7 Data size: 340 Basic stats: COMPLETE Column stats: NONE |
|                 value expressions: _col1 (type: string), _col2 (type: decimal(10,2)) |
|       Reduce Operator Tree:                        |
|         Select Operator                            |
|           expressions: KEY.reducesinkkey0 (type: bigint), VALUE._col0 (type: string), VALUE._col1 (type: decimal(10,2)) |
|           outputColumnNames: _col0, _col1, _col2   |
|           Statistics: Num rows: 7 Data size: 340 Basic stats: COMPLETE Column stats: NONE |
|           File Output Operator                     |
|             compressed: false                      |
|             Statistics: Num rows: 7 Data size: 340 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                                   |
|                                                    |
+----------------------------------------------------+
39 rows selected (0.143 seconds)

运行输出

INFO  : Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
INFO  : 2020-04-14 04:56:06,003 Stage-1 map = 0%,  reduce = 0%
INFO  : 2020-04-14 04:56:13,192 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 2.31 sec
INFO  : 2020-04-14 04:56:18,323 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 3.99 sec
INFO  : MapReduce Total cumulative CPU time: 3 seconds 990 msec
INFO  : Ended Job = job_1586835454940_0015
INFO  : MapReduce Jobs Launched: 
INFO  : Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 3.99 sec   HDFS Read: 11305 HDFS Write: 654 SUCCESS
INFO  : Total MapReduce CPU Time Spent: 3 seconds 990 msec
INFO  : Completed executing command(queryId=hive_20200414045557_ba114e3d-fe27-4268-89a6-ecb424340503); Time taken: 21.548 seconds
INFO  : OK
+----------+------------+-----------+
| user_id  | user_name  |  salary   |
+----------+------------+-----------+
| 1        | szh        | 28000.00  |
| 1        | szh        | 28000.00  |
| 1        | szh        | 28000.00  |
| 2        | zyq        | 26000.00  |
| 2        | zyq        | 26000.00  |
| 2        | zyq        | 26000.00  |
| 3        | gkm        | 20000.00  |
| 3        | gkm        | 20000.00  |
| 3        | gkm        | 20000.00  |
| 4        | pip        | 13400.00  |
| 4        | pip        | 13400.00  |
| 4        | pip        | 13400.00  |
| 5        | kip        | 24500.00  |
| 5        | kip        | 24500.00  |
| 5        | kip        | 24500.00  |
| 6        | zxxc       | 13000.00  |
| 6        | zxxc       | 13000.00  |
| 6        | zxxc       | 13000.00  |
| 7        | xsz        | 8600.00   |
| 7        | xsz        | 8600.00   |
| 7        | xsz        | 8600.00   |
+----------+------------+-----------+
21 rows selected (21.682 seconds)

 

可以看出来， 两者的 EXPLAIN 结果没有差异

   主要的差异体现在 Redcuer wo数量上，ORDER BY 会严格控制 Reducer 为1 ，因为需要进行全局排序！！！，去调整 Reducer 数量是没有意义的。

 

 

2) 通过使用 LIMIT 限制排序的输出

  我们都知道 ODRER BY + LIMIT N 可以取到前N 个数据。

但是 SORT BY + LIMIT N 也可以取到前N 个数据，并且更高效 ！！！（大数据情况下）

我们按照 user_id 取前5条数据

ORDER BY 写法

set mapred.reduce.tasks=5;

SELECT user_id, user_name, salary  
FROM datacube_salary_org 
ORDER BY user_id
LIMIT 5;

执行结果：

INFO  : Starting Job = job_1586835454940_0018, Tracking URL = http://cdh-manager:8088/proxy/application_1586835454940_0018/
INFO  : Kill Command = /opt/cloudera/parcels/CDH-6.0.1-1.cdh6.0.1.p0.590678/lib/hadoop/bin/hadoop job  -kill job_1586835454940_0018
INFO  : Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
INFO  : 2020-04-14 05:21:17,010 Stage-1 map = 0%,  reduce = 0%
INFO  : 2020-04-14 05:21:24,194 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 1.48 sec
INFO  : 2020-04-14 05:21:30,379 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 3.22 sec
INFO  : MapReduce Total cumulative CPU time: 3 seconds 220 msec
INFO  : Ended Job = job_1586835454940_0018
INFO  : MapReduce Jobs Launched: 
INFO  : Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 3.22 sec   HDFS Read: 11463 HDFS Write: 222 SUCCESS
INFO  : Total MapReduce CPU Time Spent: 3 seconds 220 msec
INFO  : Completed executing command(queryId=hive_20200414052108_f4b9e94b-ade0-4756-9175-1139a5975b7f); Time taken: 22.635 seconds
INFO  : OK
+----------+------------+-----------+
| user_id  | user_name  |  salary   |
+----------+------------+-----------+
| 1        | szh        | 28000.00  |
| 1        | szh        | 28000.00  |
| 1        | szh        | 28000.00  |
| 2        | zyq        | 26000.00  |
| 2        | zyq        | 26000.00  |
+----------+------------+-----------+
5 rows selected (22.751 seconds)

可以看到只有一个 Reduce

 

SORT BY 写法

set mapred.reduce.tasks=5;

SELECT user_id, user_name, salary  
FROM datacube_salary_org 
SORT BY user_id
LIMIT 5;

执行结果：

INFO  : Starting Job = job_1586835454940_0020, Tracking URL = http://cdh-manager:8088/proxy/application_1586835454940_0020/
INFO  : Kill Command = /opt/cloudera/parcels/CDH-6.0.1-1.cdh6.0.1.p0.590678/lib/hadoop/bin/hadoop job  -kill job_1586835454940_0020
INFO  : Hadoop job information for Stage-2: number of mappers: 1; number of reducers: 1
INFO  : 2020-04-14 05:23:52,360 Stage-2 map = 0%,  reduce = 0%
INFO  : 2020-04-14 05:23:58,508 Stage-2 map = 100%,  reduce = 0%, Cumulative CPU 2.28 sec
INFO  : 2020-04-14 05:24:04,679 Stage-2 map = 100%,  reduce = 100%, Cumulative CPU 4.09 sec
INFO  : MapReduce Total cumulative CPU time: 4 seconds 90 msec
INFO  : Ended Job = job_1586835454940_0020
INFO  : MapReduce Jobs Launched: 
INFO  : Stage-Stage-1: Map: 1  Reduce: 5   Cumulative CPU: 10.08 sec   HDFS Read: 24849 HDFS Write: 610 SUCCESS
INFO  : Stage-Stage-2: Map: 1  Reduce: 1   Cumulative CPU: 4.09 sec   HDFS Read: 7846 HDFS Write: 222 SUCCESS
INFO  : Total MapReduce CPU Time Spent: 14 seconds 170 msec
INFO  : Completed executing command(queryId=hive_20200414052303_4228dcc5-a45f-4fe1-93e8-20be71cf84d3); Time taken: 62.358 seconds
INFO  : OK
+----------+------------+-----------+
| user_id  | user_name  |  salary   |
+----------+------------+-----------+
| 1        | szh        | 28000.00  |
| 1        | szh        | 28000.00  |
| 1        | szh        | 28000.00  |
| 2        | zyq        | 26000.00  |
| 2        | zyq        | 26000.00  |
+----------+------------+-----------+

可以看到第一个作业也就是 SORT BY 是 5个 Reduce,  而第二个作业 LIMIT 是 1个Reduce

 

 

我们看下执行计划

ORDER BY + LIMIT 

INFO  : Starting task [Stage-2:EXPLAIN] in serial mode
INFO  : Completed executing command(queryId=hive_20200414052614_9ded2fc7-ad41-4f6d-9c3d-8a46f529bc7d); Time taken: 0.005 seconds
INFO  : OK
+----------------------------------------------------+
|                      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: datacube_salary_org             |
|             Statistics: Num rows: 7 Data size: 340 Basic stats: COMPLETE Column stats: NONE |
|             Select Operator                        |
|               expressions: user_id (type: bigint), user_name (type: string), salary (type: decimal(10,2)) |
|               outputColumnNames: _col0, _col1, _col2 |
|               Statistics: Num rows: 7 Data size: 340 Basic stats: COMPLETE Column stats: NONE |
|               Reduce Output Operator               |
|                 key expressions: _col0 (type: bigint) |
|                 sort order: +                      |
|                 Statistics: Num rows: 7 Data size: 340 Basic stats: COMPLETE Column stats: NONE |
|                 TopN Hash Memory Usage: 0.1        |
|                 value expressions: _col1 (type: string), _col2 (type: decimal(10,2)) |
|       Reduce Operator Tree:                        |
|         Select Operator                            |
|           expressions: KEY.reducesinkkey0 (type: bigint), VALUE._col0 (type: string), VALUE._col1 (type: decimal(10,2)) |
|           outputColumnNames: _col0, _col1, _col2   |
|           Statistics: Num rows: 7 Data size: 340 Basic stats: COMPLETE Column stats: NONE |
|           Limit                                    |
|             Number of rows: 5                      |
|             Statistics: Num rows: 5 Data size: 240 Basic stats: COMPLETE Column stats: NONE |
|             File Output Operator                   |
|               compressed: false                    |
|               Statistics: Num rows: 5 Data size: 240 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: 5                                     |
|       Processor Tree:                              |
|         ListSink                                   |
|                                                    |
+----------------------------------------------------+
43 rows selected (0.142 seconds)

 

SORT BY + LIMIT

INFO  : Starting task [Stage-3:EXPLAIN] in serial mode
INFO  : Completed executing command(queryId=hive_20200414052914_89ed6d4a-e498-4372-bdc1-6aa8ceb43ddd); Time taken: 0.004 seconds
INFO  : OK
+----------------------------------------------------+
|                      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: datacube_salary_org             |
|             Statistics: Num rows: 7 Data size: 340 Basic stats: COMPLETE Column stats: NONE |
|             Select Operator                        |
|               expressions: user_id (type: bigint), user_name (type: string), salary (type: decimal(10,2)) |
|               outputColumnNames: _col0, _col1, _col2 |
|               Statistics: Num rows: 7 Data size: 340 Basic stats: COMPLETE Column stats: NONE |
|               Reduce Output Operator               |
|                 key expressions: _col0 (type: bigint) |
|                 sort order: +                      |
|                 Statistics: Num rows: 7 Data size: 340 Basic stats: COMPLETE Column stats: NONE |
|                 TopN Hash Memory Usage: 0.1        |
|                 value expressions: _col1 (type: string), _col2 (type: decimal(10,2)) |
|       Reduce Operator Tree:                        |
|         Select Operator                            |
|           expressions: KEY.reducesinkkey0 (type: bigint), VALUE._col0 (type: string), VALUE._col1 (type: decimal(10,2)) |
|           outputColumnNames: _col0, _col1, _col2   |
|           Statistics: Num rows: 7 Data size: 340 Basic stats: COMPLETE Column stats: NONE |
|           Limit                                    |
|             Number of rows: 5                      |
|             Statistics: Num rows: 5 Data size: 240 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) |
|               sort order: +                        |
|               Statistics: Num rows: 5 Data size: 240 Basic stats: COMPLETE Column stats: NONE |
|               TopN Hash Memory Usage: 0.1          |
|               value expressions: _col1 (type: string), _col2 (type: decimal(10,2)) |
|       Reduce Operator Tree:                        |
|         Select Operator                            |
|           expressions: KEY.reducesinkkey0 (type: bigint), VALUE._col0 (type: string), VALUE._col1 (type: decimal(10,2)) |
|           outputColumnNames: _col0, _col1, _col2   |
|           Statistics: Num rows: 5 Data size: 240 Basic stats: COMPLETE Column stats: NONE |
|           Limit                                    |
|             Number of rows: 5                      |
|             Statistics: Num rows: 5 Data size: 240 Basic stats: COMPLETE Column stats: NONE |
|             File Output Operator                   |
|               compressed: false                    |
|               Statistics: Num rows: 5 Data size: 240 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: 5                                     |
|       Processor Tree:                              |
|         ListSink                                   |
|                                                    |
+----------------------------------------------------+
69 rows selected (0.143 seconds)

STAGE1 -> STAGE2 -> STAGE0 

可以看到第一个阶段 STAGE1 是每个Reduce 取前5。 第二个阶段，所有的Reduce 取了前5

 

 

总结

    通过 使用SORT BY +  LMIT N , 我们只需要每个 Reduce 控制前N 条记录即可。然后我们在总的 M个 Reduce 中再去选取其中的 前N 个数据就可以找到前 N 排名的数据了。

Tips

1) 使用 SORT BY + LIMIT N 的方式会比 ORDER BY 多一个JOB。所以，在使用 SORT BY + LIMIT 的方式，我们也要注意数据量的规模

2) 据说每个Reduce 取前N 条记录，使用的是冒泡排序。

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

高达一号
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Hive用户指南(Hive_user_guide)_中文版
10-07
### Hive用户指南中文版知识点概览 #### 一、Hive结构 **1.1 Hive架构** Hive作为建立在Hadoop之上的数据仓库基础架构，其主要目标是简化大数据的处理过程。Hive的架构主要包括以下几个核心组件： - **用户接口*...
Hive_HQL_Hive优化_复杂SQL_观看不同视频个数的前5名
迎难而上
06-04 1080
今天，遇到了一个问题，求 : 某一天HIVE分区表的数据，观看视频个数不同的前5名user_id。 问题描述 ： 用户浏览日志（date, user_id, video_id）, 统计 2020.03.29 观看不同视频个数的前5名 user_id。（大数据集） 先创建表，并构建数据 CREATE TABLE IF NOT EXSITS user_video_log( user_id BIGINT, video_id BIGINT )PARTITIONED BY( pt STRIN..
Hive 优化实践 -- 执行计划
Nice的博客
05-08 936
目录数据准备执行计划问题分析SQL优化最后小结 数据准备 -- 创建数据库 create database tuning; use tuning; -- 创建表 create table if not exists tuning.student_txt( s_no string comment '学号', s_name string comment '姓名', s_birth string comment '出生日期', s_age int comment '年龄', s_sex string comme
Hive自定义函数及优化（整理版）
weixin_30607029的博客
07-04 428
1. 概述 1.1 hive的特征： 可以通过SQL轻松访问数据的工具，从而实现数据仓库任务，如提取/转换/加载（ETL），报告和数据分析； 它可以使已经存储的数据结构化； 可以直接访问存储在Apache HDFS或其他数据存储系统（如Apache HBase）中的文件； Hive除了支持MapReduce计算引擎，还支持Spark和Tez这两种分布式计算引擎； 它提供类似sql的...
Hive中的四种排序
象在舞的技术专栏
09-01 6289
一、Order by order by会对输入的数据做全局排序，因此只有一个reducer，多个reducer无法保证全局有序。只有一个reducer的弊端在于当输入的数据量较大时，需要较长的计算时间。order by跟数据库中的order by功能一致，按照某一项或几项排序输出。它与数据库中 order by 的区别在于在hive.mapred.mode = strict 模式下
Hive和MySQL分组排序取前n条记录
u011250186的博客
09-10 2430
实例需求 有一张职员信息表test（id，姓名，部门，入职时间），要求：查询出各部门最早入职的职员信息，表数据如下图： Hive实现 语法：row_number() over (partition BY 字段A order by 字段B desc) partition by：类似hive的建表，分区的意思； order by ：排序，默认是升序，加desc降序； 这里按字段A分区，对字段B进行降序排序 引用上面语法（按部门分组，按时间正序排序）查询row_num， 查询语句： SE...
Hive 编程指南
05-30
《Hive编程指南》是一本深入探讨Hive技术的教程，专门为那些想要利用Hadoop进行大规模数据处理的读者设计。Hive是建立在Hadoop生态系统之上的一种数据仓库工具，它允许用户通过SQL-like语言（HQL）对分布式存储的...
设计开发 Hive 编程指南 完整版
04-08
6. **Hive 分桶和排序**：分桶和排序是优化查询性能的重要手段。分桶是根据列值的哈希函数将数据分成若干个桶，而排序则是按照指定列对数据进行升序或降序排列，这两种方式可以提高 JOIN 和 GROUP BY 的效率。 7. *...
基于Hadoop的数据仓库Hive学习指南.doc
04-06
【标题】：“基于Hadoop的数据仓库Hive学习指南” 【描述】：该文档是一份针对Hive的学习资料，旨在引导读者理解如何在Hadoop平台上利用Hive进行数据仓库操作和编程实践。它涵盖了Hive的基本概念、安装步骤、实验...
Hive 编程指南（书签版）
06-04
Hive编程指南（书签版）是一份宝贵的资源，对于学习和理解Hive的工作原理、SQL语法以及性能优化策略非常有帮助。通过深入阅读和实践，数据分析师和开发人员能够更好地利用Hive进行大数据处理，提升工作效率。书签版...
hive的一些报错及解决方法
12-18
hive的一些报错及解决方法：连接报错、执行脚本卡住、内存溢出报错、表死锁问题
Hive_Hive 排序及优化 ORDER BY, SORT BY, DISTRIBUTE BY, CLUSTER BY
热门推荐
迎难而上
07-06 2万+
Hive 提供了多重排序语句，我们这里对这几种排序方法做一解析，并进行测试 编排如下: 试验场景 Hive 中的 ORDER BY 与其不足 Hive 中的 SORT BY，DISTRIBUTE BY Hive 中的CLUSTER BY 与其不足 完成相同功能的Job 执行时长对比 实验场景： 我们创建了一个按天分区的分区...
hive表级权限配置以及运行调试
zfszhangyuan的博客
01-11 8086
环境：我们已经安装了Hadoop集群，hive，hue 以及命令行工具Beeline hue： 主要是对hive数据仓库的一个可视化操作客户端，我们可以用hue对hive库添加管理员用户账号。 Beeline： HiveServer2提供了一个新的命令行工具Beeline，它是基于SQLLine CLI的JDBC客户端。关于SQLLine的的知识，可以参考这个网站：http://sqll
Hive 中的四种排序
旧时明月
08-05 9814
1、order   by   //可以指定desc 降序 asc 升序 order by会对输入做全局排序，因此只有一个Reducer(多个Reducer无法保证全局有序)，然而只有一个Reducer，会导致当输入规模较大时，消耗较长的计算时间。 create table  temperature(        year int, temper float ) r
hive的查询注意事项以及优化总结 .
weixin_33836874的博客
06-20 735
Hive是将符合SQL语法的字符串解析生成可以在Hadoop上执行的MapReduce的工具。使用Hive尽量按照分布式计算的一些特点来设计sql，和传统关系型数据库有区别， 所以需要去掉原有关系型数据库下开发的一些固有思维。 基本原则： 1：尽量尽早地过滤数据，减少每个阶段的数据量,对于分区表要加分区，同时只选择需要使用到的字段 select ... from A join B ...
hive中使用ORDER BY报错
clever_wr的博客
03-20 3427
在hive中出现；例如以下的错误： Error while compiling statement: FAILED: SemanticException 19:9 In strict mode, if ORDER BY is specified, LIMIT must also be specified. Error encountered near token 'id' 主要是通过在使用O...
Hive/HiveQL常用优化方法全面总结
qq_36864672的博客
09-27 833
目录 列裁剪和分区裁剪 谓词下推 sort by代替order by group by代替distinct group by配置调整 map端预聚合 倾斜均衡配置项 join基础优化 build table（小表）前置 多表join时key相同 利用map join特性 分桶表map join 倾斜均衡配置项 优化SQL处理join数据倾斜 空值或无意义值 单独处理倾斜key 不同数据类型 build table过大 MapReduce优化
文末赠书|一篇文章让你了解Hive调优
abcdefg90876的博客
03-24 370
老工在职场多年，从事过海量（PB级）数据的关系型数据库数据处理工作，后由于数据平台升级的要求，将数据迁移到Hadoop集群，做了多年的数据研发和数据产品...
Hive编程指南：从入门到精通
Hive编程指南 本书《Programming Hive》是学习Hive编程的绝佳选择，旨在带领读者由入门逐渐深入学习Hive技术。下面是本书的知识点总结： 1. Hive简介 Hive是一个基于Hadoop的数据仓库工具，使用SQL语句来查询和...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值