广电用户收视行为数据查询优化¶
- 使用视图统计不同节目的用户观看人数
- 优化配置 Fetch 抓取，提升简单查询效率
- 合理设置 map 和 reduce 任务数
- 使用子查询统计节目类型为直播的频道 Top10
任务背景¶
通过前面章节的学习，读者对在 Hive CLI 中通过执行 HQL 语句对 Hive 表进行查询的方法有了一定的了解，也熟悉了各种 Hive 函数的使用场景，掌握了调用函数编写 HQL 语句实现查询的方法。
然而，为了进一步提高查询性能和效率，读者该如何合理地、高效地选择函数和编写 HQL 语句，依旧是值得探究的主题。
在数据量很小的场景下可以不考虑对 HQL 语句进行优化，因为数据量很小，所以优化效果可能不显著。然而，在大数据场景下，语句优化就尤为关键，在面对以百万或以亿计的数据量时，即使细微的优化，都会在执行效率上有质的飞越。依“旧例”办事固然很少出错，但执行速度会极慢，在高要求的背景下，大家需要做到“守正创新”，实现飞跃发展。
广电案例中的用户收视行为数据表 media_index 含有 475 万条数据，相应的查询属于大数据场景下的查询，因此查询语句的优化是十分有必要的。
本章将对广电用户收视行为数据表进行查询优化。首先介绍视图的创建及其操作方法，其次介绍 Hive 的设置优化，如配置 Fetch 抓取、设置 map 和 reduce 任务数等，再介绍 Hive 的语句优化，如使用子查询优化查询语句。通过介绍 Hive 查询的优化方法，并结合广电用户收视行为数据表，以实现各任务为目标，帮助读者掌握 Hive 的优化查询方法。
本章实验环境
项目 配置 Hadoop 3.1.3，伪分布式单节点，4 核 CPU / 3 GB 内存 Hive 3.1.2，元数据存储于 MySQL 数据表 ZJSM.media_index，781.3 MB，4 754 442 条记录，单文件存储仓库目录 /user/hive/local/warehouse/zjsm.db/media_index本章所有执行时间、任务数均由上述环境实测得出。单机 4 核环境下，部分"教科书式"的优化项反而会变慢，下文对每一项都给出了实测数据与原因分析，请读者以实测结论为准，切勿生搬硬套。
一. 使用视图统计不同节目的用户观看人数¶
- 掌握 Hive 视图的创建、查看与删除方法
- 理解视图作为查询优化抽象层的作用
任务描述¶
随着互联网的发展，电视与互联网相结合，已经实现了节目点播或回放，人们可以随时观看自己喜欢的节目。
为了解不同节目的用户观看人数，探索人们是观看直播节目居多，还是因时间冲突，只能以点播或回放的方式观看节目。本任务介绍使用 Hive 视图降低查询复杂度，优化统计不同节目的用户观看人数查询语句。
视图的概念¶
视图是基于数据库的基本表创建的一种伪表，因此数据库只存储视图的定义，不存储数据项，数据项仍然存在基本表中。视图可作为抽象层，将数据发布给下游用户。
- 视图只能用于查询，但不能进行数据的插入和修改，提高了数据的安全性。
- 在创建视图时，视图的结构就已经固定，因此对基本表的后续更改（如添加列等操作）将不会反映在视图中。
- 视图允许从多个表中抽取字段组成可查询的伪表。使用视图可以降低查询的复杂度，达到优化查询的目的。
在 Hive 中可以使用 CREATE VIEW 关键字创建视图。
创建视图的语法结构
CREATE VIEW [IF NOT EXISTS] [db_name.]view_name
[(column_name [COMMENT column_comment], ...) ]
[COMMENT view_comment]
[TBLPROPERTIES (property_name = property_value, ...)]
AS SELECT ...;
| 参数 | 说明 |
|---|---|
IF NOT EXISTS |
当创建视图时，如果已经存在同名的视图，那么将引发错误。使用 IF NOT EXISTS 判断是否存在同名的视图 |
COMMENT |
注释，不仅可以为选择的字段添加注释，而且也可以为视图添加注释 |
TBLPROPERTIES |
用户可在创建视图时添加自定义或预定义的数据属性，并设置数据属性的赋值 |
AS SELECT |
用户可选择所基于的基本表内容创建视图，定义视图的结构和数据。如果在创建视图时未定义列名，那么视图列的名称将自动由定义的 SELECT 表达式派生（如果 SELECT 包含无别名的标量表达式，如 x+y，那么视图列名将以 _C0、_C1 的形式生成） |
广电用户收视行为数据表 media_index 中，记录了不同节目的收视情况。现从中选取用户编号、直播频道名称、观看行为开始时间和观看行为结束时间，形成一个可供查询的视图 media_index_time_view。
-- 创建 media_index_time_view 视图
CREATE VIEW IF NOT EXISTS media_index_time_view (
phone_no,
station_name,
origin_time,
end_time)
AS SELECT phone_no, station_name, origin_time, end_time FROM media_index;
查看与删除视图¶
视图是一个没有关联存储的纯逻辑对象。当查询引用视图时，会评估视图的定义以生成一组行数据供进一步地查询。
视图是一个概念性描述，实际上作为查询优化的一部分，Hive 将视图的定义与查询的定义结合起来，如在查询视图内容时，Hive 根据视图的定义选择引用表中对应的字段内容进行查询。
在 ZJSM 数据库中使用 SHOW TABLES; 语句，可查看当前数据库中的表和视图，实测输出如下。
| 表名 / 视图名 | 类型 |
|---|---|
media_index |
表 |
media_index_time_view |
视图 |
mediamatch_userevent |
表 |
mediamatch_usermsg |
表 |
mmconsume_billevents |
表 |
order_index |
表 |
除了使用 SHOW TABLES; 语句可查看当前数据库中的视图外，Hive 2.2.0 及其后的版本开始支持使用 SHOW VIEWS; 语句专门查看当前数据库中的视图。
从上表中可看到，数据库中已经存在了 media_index_time_view 视图，可使用 DESC media_index_time_view; 语句查看视图结构，实测输出如下。
| # | col_name | data_type | comment |
|---|---|---|---|
| 1 | phone_no | string | |
| 2 | station_name | string | |
| 3 | origin_time | string | |
| 4 | end_time | string |
视图内容查询方法与表内容查询方法一致，查询 media_index_time_view 视图的前 10 行数据，实测结果如下。
| phone_no | station_name | origin_time | end_time |
|---|---|---|---|
| 2559492 | 东方卫视-高清 | 2022-07-11 22:55:00 | 2022-07-11 23:08:01 |
| 3489591 | 中央10台-高清 | 2022-07-11 21:25:00 | 2022-07-11 21:41:37 |
| 4050240 | 江西卫视-高清 | 2022-07-11 21:51:47 | 2022-07-11 21:52:49 |
| 4195365 | 河南卫视-高清 | 2022-07-11 11:24:58 | 2022-07-11 11:25:46 |
| 2009814 | 中央4台-高清 | 2022-07-11 20:34:12 | 2022-07-11 20:35:03 |
| 2436506 | 中央音乐 | 2022-07-10 21:59:42 | 2022-07-10 22:02:45 |
| 2493673 | 江苏卫视-高清 | 2022-07-09 12:58:08 | 2022-07-09 12:59:05 |
| 2981036 | 中央10台-高清 | 2022-07-11 12:55:23 | 2022-07-11 12:56:39 |
| 3168858 | 广州电视-高清 | 2022-07-09 10:30:00 | 2022-07-09 11:16:07 |
| 3469254 | 广东体育-高清 | 2022-07-09 15:52:16 | 2022-07-09 15:53:52 |
该查询耗时 0.368 秒，未启动 MapReduce 作业——因为 LIMIT 查询命中了 Fetch 抓取（详见本章第二节）。
视图是基于基本表创建的伪表，并没有将真实数据存储在 Hive 中，若删除视图关联的基本表，则查询视图内容时将会报错。因此，生产环境中不应随意删除视图所依赖的基本表，否则下游依赖该视图的查询会全部失败。
删除视图可使用 DROP VIEW view_name; 语句。对视图使用 DROP TABLE 语句是非法的，这不会删除视图。
任务实现思路¶
- 广电用户收视行为数据表
media_index的字段众多，直接在查询中列举字段会降低语句可读性。 - 使用视图筛选出相关字段（
phone_no、res_type），可以降低查询的复杂度，是 Hive 查询优化的常用手段。 - 统计"不同节目的用户观看人数"本质是按
res_type分组，对phone_no去重计数，即COUNT(DISTINCT phone_no)。
任务实现¶
根据 media_index 表中的 phone_no（用户编号）和 res_type（节目类型）字段创建视图 media_index_type_view。
-- 创建 media_index_type_view 视图
CREATE VIEW IF NOT EXISTS media_index_type_view (
phone_no,
res_type)
AS SELECT phone_no, res_type FROM media_index;
使用 media_index_type_view 视图统计不同节目的用户观看人数。
-- 使用视图统计不同节目的用户观看人数
SELECT res_type, COUNT(DISTINCT phone_no) AS user_Number
FROM media_index_type_view
GROUP BY res_type;
实测结果如下（耗时 34.507 秒，1 个 MapReduce 作业，4 个 map 任务、4 个 reduce 任务）。
| res_type | user_Number | 含义 |
|---|---|---|
| 0 | 6333 | 电视直播节目 |
| 1 | 2448 | 点播或回放节目 |
由上表可得，观看电视直播节目的用户有 6333 人，观看点播或回放节目的用户有 2448 人。因此可得观看电视直播节目的用户人数约是观看点播或回放节目的用户人数的 2.6 倍。
关于
res_type取值：本表中res_type为0表示直播节目，为1表示点播或回放节目。判断直播节目应写作res_type = 0（或res_type != '1'），切勿写成res_type = 1。
二. 优化统计直播频道数¶
- 掌握 Fetch 抓取的原理与配置方法
- 理解 map 和 reduce 任务数对执行效率的影响
- 掌握并行执行的配置方法
任务描述¶
随着有线数字电视的发展与普及，用户能够观看更多的直播频道。
用户除了可以观看基本的免费直播节目外，还可以通过付费的方式观看更多的付费节目。现今电视节目丰富多彩，直播频道相对较多。
本任务介绍优化 Hive 配置并统计广电直播频道数，提高查询效率。
配置 Fetch 抓取¶
Fetch 抓取是指在 Hive 中对某些数据的查询可以不必使用 MapReduce 计算，而是读取存储目录下的文件，再输出查询结果到控制台，如全局查询、字段查询和使用 LIMIT 语句查询。在特殊的场景下配置 Fetch 抓取，可以提高查询的效率。
Hive 安装目录的 conf 目录下存在一个 hive-default.xml.template 配置文件，该文件中存在一个 hive.fetch.task.conversion 参数，对该参数可以设置 3 种值。
| 参数值 | 含义解析 |
|---|---|
none |
表示所有查询都会运行 MapReduce |
minimal |
表示在查询的开始阶段、选择某些分区的字段和使用 LIMIT 语句查询时不会执行 MapReduce |
more |
表示在全局查询、字段查询和使用 LIMIT 查询时都不会执行 MapReduce |
将 hive.fetch.task.conversion 参数的值设置为 more，即可实现 Fetch 抓取。Hive 3.1.2 中的该参数默认值即为 more。
例如，在 Hive 3.1.2 的 CLI 中简单地查询广电用户收视行为数据表的前 5 行数据，查询时将不会执行 MapReduce。
实测结果：耗时 2.317 秒，未启动 MapReduce 作业，直接读取文件后输出结果。
若将 hive.fetch.task.conversion 的值设置为 none，再次查询广电用户收视行为数据表的前 5 行数据，则查询时将执行 MapReduce。
实测结果：耗时 19.411 秒，启动 1 个 MapReduce 作业（4 个 map 任务、0 个 reduce 任务）。
对比两次执行可得：开启 Fetch 抓取耗时 2.317 秒，关闭后耗时 19.411 秒，耗时放大约 8.4 倍。因此配置 Fetch 抓取对执行简单查询的效率有显著的提升。
注意：Fetch 抓取只对"无需聚合、无需全表扫描"的简单查询生效。若查询包含
GROUP BY、COUNT(DISTINCT)等聚合操作，仍会走完整的 MapReduce 流程。
合理设置 map 和 reduce 任务数¶
当使用 Hive 进行聚合查询时，将不通过 Fetch 抓取读取存储目录文件，而是使用 MapReduce 作业执行聚合查询的过程。
在通常情况下，MapReduce 作业通过读取数据文件将产生一个或多个 map 任务，产生的 map 任务数主要取决于读取到的文件数和集群设置的文件块大小。而在默认的情况下，reduce 任务数则是通过 Hive 的内置算法决定的。
1. 调整 map 任务数¶
并不是 map 任务数越多，其执行效率就越高。
假设一个任务存在多个小文件，每一个小文件都会启动一个 map 任务，当一个 map 任务启动和初始化的时间远远大于逻辑处理的时间，会造成很大的资源浪费，并且可执行的 map 任务数是有限的。因此，可以设置在 map 任务执行前合并小文件以达到减少 map 任务数的目的。
当然，map 任务数也不是越少越好。假设处理的文件较大，任务逻辑复杂，map 任务执行较慢的时候，可考虑增加 map 任务数，降低每个 map 任务处理的数据量，从而提高任务的执行效率。增加 map 任务数可以通过减小 Hadoop 块大小实现。
广电用户收视行为数据表大小约为 781.3 MB（单文件存储），使用 SET mapreduce.input.fileinputformat.split.maxsize; 语句查看最大切分数据块。
实测结果：mapreduce.input.fileinputformat.split.maxsize=256000000，即本环境中默认最大切分数据块的大小为 256 MB。
使用 count() 聚合函数统计广电用户收视行为数据表的记录数，实测耗时 32.041 秒，产生 4 个 map 任务。由结果可得，处理广电用户收视行为数据表将会产生 4 个 map 任务，map 任务数较少，因此每个 map 任务所需处理的数据量较大。
将最大切分数据块的大小设置为 128 MB，再统计广电用户收视行为数据表的记录数。
-- 128MB 等于 134217728 字节
SET mapreduce.input.fileinputformat.split.maxsize=134217728;
SELECT count(*) FROM media_index;
实测结果：耗时 48.051 秒，产生 7 个 map 任务（与预测结果一致：781.3 MB ÷ 128 MB ≈ 7 个分片）。
实测结论（重要）：将分片大小由 256 MB 调整为 128 MB 后，map 任务数由 4 个增加到 7 个，但执行时间从 32.041 秒上升为 48.051 秒，反而变慢了约 50%。
原因是本实验环境为单节点伪分布式，仅 4 核 CPU。7 个 map 任务在 4 个可用 slot 上排队执行，任务调度与进程启动开销超过了并行化带来的收益，且单机磁盘 I/O 竞争加剧。
由此可见，"增加 map 任务数必然提速"的结论只在多节点集群、且数据倾斜不严重的前提下成立。在单机或节点数较少的教学环境中，盲目调小分片大小反而会拖慢作业。读者应在自己环境中实测后再决定分片大小，切勿照搬。
2. 调整 reduce 任务数¶
Hive 3.1.2 中每个 reduce 任务处理的数据块大小默认是 256 MB，每个 MapReduce 任务的最大可执行 reduce 任务数为 1009 个。
-- reduce 任务处理的数据量
SET hive.exec.reducers.bytes.per.reducer;
-- 最大可执行 reduce 任务数
SET hive.exec.reducers.max;
实测结果：
| 参数 | 实测值 |
|---|---|
hive.exec.reducers.bytes.per.reducer |
256000000（256 MB） |
hive.exec.reducers.max |
1009 |
若不设置 reduce 任务数，则 Hive 将使用内置算法确定 reduce 任务数，这将对执行效率有很大的影响。
在处理的数据量较大时，若 reduce 任务数较少，则会导致 reduce 任务执行过慢，甚至会出现内存过载的错误。若 reduce 任务数较多，则会产生大量的小文件，造成文件合并代价太高，NameNode 的内存占用也会增大。
因此，合理地设置 reduce 任务数尤为关键。合理的 reduce 任务数等于读取的文件大小除以每个 reduce 任务能够处理的数据量大小。如广电用户收视行为数据表的大小约为 781.3 MB，合理的 reduce 任务数为 780 MB ÷ 256 MB ≈ 3～4 个。
与 map 任务数同理，在 4 核单机环境下 reduce 任务数也不宜超过 4，否则同样会因资源争抢而变慢。
配置并行执行¶
在 Hive 中执行 HQL 语句查询时，会将查询转化成一个或多个阶段来完成查询任务，包括 MapReduce 阶段、全局查询阶段、合并阶段或使用 LIMIT 语句查询阶段等。
在 Hive 的默认环境下，Hive 执行任务时将按照划分好的阶段逐步执行，换而言之，Hive 一次只会执行一个阶段。
其实在 Hive 作业的众多阶段中，并非所有的阶段都是完全相互依赖的，因此存在某些阶段是允许并行执行的。通过配置并行执行，可以使得整个 Hive 作业的执行时间缩短。
Hive 的并行执行默认状态是 false（即关闭），需要设置为 true（开启）。
Hive 中执行同一条 HQL 语句所支持的并行度默认值为 8，换而言之，Hive 可以同时执行 8 个互不相关的阶段。读者可以根据所执行的 HQL 语句的复杂程度调整最大并行度。
值得注意的是，在 Hive 集群当中，如果 Hive 作业中并行执行阶段增多，那么集群资源利用率也会增大。并行执行会占用大量的集群资源以加速 HQL 语句的执行，因此一定要清楚集群资源的总量与当前利用率，否则并行执行将失败。
在单机伪分布式环境中，MapReduce 作业的各阶段本身已经跑满 4 核，并行执行带来的收益有限，且可能与 Job 自身争抢资源。本章第三节的实测中，开启并行执行后 Top10 查询仍耗时 101.112 秒。
任务实现思路¶
- 创建
media_index_station_view视图，从广电用户收视行为数据表中筛选出直播频道字段。 - 将 Hive 中的最大切分数据块的大小设置为 128 MB。
- 开启任务并行执行。
- 使用
COUNT(DISTINCT)去重统计直播频道数。
任务实现¶
-- 创建视图
CREATE VIEW IF NOT EXISTS media_index_station_view (
station_name)
AS SELECT station_name FROM media_index;
-- 将最大切分数据块的大小设置为 128MB
SET mapreduce.input.fileinputformat.split.maxsize=134217728;
-- 开启任务并行执行
SET hive.exec.parallel=true;
-- 去重统计直播频道数
SELECT COUNT(DISTINCT station_name) FROM media_index_station_view;
实测结果：耗时 39.288 秒，生成 1 个 MapReduce 作业，读取 media_index_station_view 视图数据时生成 7 个 map 任务。因为使用 COUNT(DISTINCT) 语句去重，所以只会生成 1 个 reduce 任务。
统计结果为 147，即广电公司共拥有 147 个直播频道。
COUNT(DISTINCT)只启动 1 个 reduce 任务，该任务需要把全部 147 个频道名在内存中聚合，数据量大时容易成为瓶颈甚至 OOM。本章第三节改用GROUP BY+COUNT的两阶段方案来解决这一问题。
三. 使用子查询统计节目类型为直播的频道 Top10¶
- 掌握 Hive 中子查询的使用方法
- 掌握
GROUP BY语句的优化配置 - 掌握
LIMIT语句的优化配置与严格模式
任务描述¶
现今广电直播频道数量众多，电视节目日益全球化，任务 6.2 统计出广电公司拥有 147 个直播频道。
广电公司需要对每个直播频道进行投资，因此了解用户感兴趣的节目，进行直播频道热度统计，对广电公司进行节目投资十分重要。
本任务介绍使用子查询统计出节目类型为直播的频道 Top10。
使用子查询优化查询语句¶
Hive 作为分布式的数据仓库，在执行分布式计算和分布式存储时，都会消耗大量的磁盘和网络 I/O（Input/Output，输入输出）资源，因此如何减少 I/O 资源消耗是一个优化的焦点。
Hive 的查询依赖于 MapReduce 计算框架，而每一个 MapReduce 作业的启动需要消耗大量的 I/O 资源，原因是 MapReduce 存在 Shuffle 操作，中间结果将产生大量的磁盘落地。HQL 语句的查询任务会转化为 MapReduce 程序来执行，若存在多个作业，则作业与作业之间的中间结果会先溢写到磁盘上。因此优化 HQL 语句减少中间结果数据的产生，也能够达到减少 I/O 资源消耗的效果。
子查询指在一个查询语句中嵌套使用一个或多个查询语句，子查询放在 FROM 关键字之后，且因为 FROM 子句中的每个表都必须具有名称，所以需要为子查询指定表名称。
- 子查询
SELECT列表中的列（字段）名称也必须唯一。 - 从 Hive 0.13 开始允许使用
AS关键字进行子查询表命名，同时也开始支持在WHERE之后使用关键字IN或NOT IN实现子查询。
在 Hive 中，尽量先多使用子查询和使用 WHERE 语句降低表数据的复杂度，再使用 JOIN 连接查询。如果先进行表连接，那么查询将先进行全表扫描，最后才使用 WHERE 语句筛选，执行效率将会降低。如果先使用子查询，那么可利用 WHERE 语句过滤不相关字段，不但能增加 map 任务数，还能减小数据量。
使用子查询，查询用户数小于 500 的用户等级名称，实测查询到 EA 级的用户数小于 500。
-- 查询用户数小于 500 的用户等级名称
SELECT a.owner_name
FROM (SELECT owner_name, COUNT(phone_no) AS count FROM media_index GROUP BY owner_name) a
WHERE a.count < 500;
实测耗时 36.003 秒，1 个 MapReduce 作业（4 个 map 任务、4 个 reduce 任务），输出为 EA级。
对照全部用户等级的分组统计结果，可以看出 EA级 的用户数（367）确实是唯一低于 500 的等级：
| owner_name | 用户数 |
|---|---|
| EA级 | 367 |
| HB级 | 1261 |
| HA级 | 1372 |
| EE级 | 76566 |
| HE级 | 370778 |
| HC级 | 4304098 |
优化配置 GROUP BY 语句¶
在使用 GROUP BY 语句进行数据分组时，在默认情况下，Map 阶段相同的 key 数据将发送给同一个 reduce 任务处理。当某一个 key 数据过大时，将产生数据倾斜，导致某个 reduce 任务需要处理的数据量过大，使得该 reduce 任务执行缓慢，甚至造成任务积压。
其实并非所有的聚合操作都需要在 Reduce 阶段完成，许多聚合操作在不影响最终结果的情况下可以在 Map 阶段进行预聚合（如求和以及求最值），最后在 Reduce 阶段进行结果输出即可。
在 Hive 中开启 Map 阶段预聚合的参数设置如下。
设置允许在 Map 端进行聚合，聚合设置默认为 true（开启）。
设置允许在 Map 端进行聚合操作的数据量时应对所需处理的数据量有一定的了解，若设置的可聚合数量过小，则会影响执行效率。
设置允许在发生数据倾斜时进行负载均衡，负载均衡默认为 false（关闭），需要将其设置为 true（开启）。
当开启负载均衡时，将生成两个 MapReduce 作业。
- 第一个 MapReduce 作业的 Map 端的输出结果将被随机分布到 Reduce 端，对每个 reduce 任务进行部分数据聚合操作，并输出结果，目的是将相同的 key 分发到不同的 reduce 任务中，以达到负载均衡。
- 第二个 MapReduce 作业根据第一个 MapReduce 作业处理好的结果按 key 分组聚合并发送到相同的 reduce 任务中。
以分组统计广电用户收视行为数据表中的用户等级名称为例，对比在优化配置 GROUP BY 语句前后的任务执行时间。
使用 Hive 默认配置分组统计用户等级名称，将产生一个 MapReduce 作业，实测耗时 35.352 秒（4 个 map 任务、4 个 reduce 任务）。
使用语句优化配置 GROUP BY 语句后，再次分组统计用户等级名称，将产生两个 MapReduce 作业：第一个作业的作用是实现部分数据预聚合与 reduce 任务负载均衡，第二个作业的作用是实现全部数据分组聚合输出。两个作业的总执行时间为 61.802 秒。
实测结论（重要）：开启负载均衡后，作业由 1 个 MR 变成 2 个 MR（Stage-1 为 4 map / 4 reduce，Stage-2 为 1 map / 1 reduce），总耗时从 35.352 秒上升为 61.802 秒，反而变慢了约 75%。
原因有二：其一，本表数据不存在明显数据倾斜（最大分组 HC 级占 4 304 098 条，其余分组均为几十万量级，分布相当均匀），负载均衡无从发力，却白白付出了多一个 Stage 的 Shuffle 与磁盘落地开销；其二，单节点 4 核环境下两个 Stage 串行执行，无法并行提速。
负载均衡是针对数据倾斜的"对症下药"，不是通用提速手段。 只有当某个 key 的数据量远超其他 key、导致个别 reduce 长时间不结束时，开启它才有意义。
使用 GROUP BY 代替 COUNT(DISTINCT) 去重统计¶
在数据量较小的场景下，使用 COUNT(DISTINCT) 去重统计与使用 GROUP BY 去重统计在执行效率上区别不大。
在数据量较大的场景下，COUNT(DISTINCT) 存在明显风险：该语法只会启动一个 reduce 任务，该 reduce 任务需要维护去重集合，数据量大时容易内存过载，导致整个 MapReduce 作业难以完成。可考虑先使用 GROUP BY 分组后再使用 COUNT 函数统计的方式实现去重。
使用 COUNT(DISTINCT) 去重统计用户数，将启用一个 reduce 任务执行，实测耗时 28.842 秒，结果为 6338。
使用 GROUP BY 分组后再使用 COUNT 函数统计的方式实现去重统计用户数，将会启动两个 MapReduce 作业，也就是对应的先分组后统计。
-- 使用 GROUP BY 分组后再使用 COUNT 函数统计的方式实现去重统计用户数
SELECT COUNT(phone_no) FROM (SELECT phone_no FROM media_index GROUP BY phone_no) a;
实测耗时 61.149 秒（Stage-1 为 4 map / 4 reduce，Stage-2 为 1 map / 1 reduce），结果同样为 6338，与 COUNT(DISTINCT) 完全一致。
两种方案的对比：
| 方案 | reduce 任务数 | MR 作业数 | 实测耗时 | 内存风险 |
|---|---|---|---|---|
COUNT(DISTINCT) |
1 | 1 | 28.842 秒 | 高，单点内存压力 |
GROUP BY + COUNT |
4 | 2 | 61.149 秒 | 低，分散到多个 reduce |
虽然采用 GROUP BY 分组后再使用 COUNT 函数统计的方式会启动两个 MapReduce 作业，在执行时间上稍慢（61.149 秒 vs 28.842 秒），但是相比于只用一个 reduce 任务，采用 4 个 reduce 任务执行更能保证数据的安全和作业的稳定执行。
本表 6338 个用户相对于 475 万条记录而言基数很小，单 reduce 尚能胜任；但在千万级用户场景下，
COUNT(DISTINCT)的单点内存风险会迅速放大，届时应毫不犹豫地改用GROUP BY方案。
优化配置 LIMIT 语句¶
LIMIT 语句用于限制查询返回的行数。如果不优化 LIMIT 语句，将会在全表查询后返回限制的行数。
在 Hive 中可开启 LIMIT 语句优化参数，优化后将对数据抽样返回。开启 LIMIT 语句优化参数，需要开启对数据进行采样的功能，且可以设置最小采样容量和可抽样的最大文件数。
-- 开启对数据进行采样的功能
SET hive.limit.optimize.enable=true;
-- 设置最小采样容量，默认为 100000
SET hive.limit.row.max.size=100000;
-- 可抽样的最大文件数，默认为 10
SET hive.limit.optimize.limit.file=10;
设置 LIMIT 抽样返回之后存在一个缺点，即有用的数据可能永远不会被抽到。
Hive 还可以通过设置严格模式，防止一些危险操作。例如，设置使用 ORDER BY 语句进行查询时必须使用 LIMIT 语句。
因为 ORDER BY 语句为了执行排序会将所有的结果数据分发到同一个 Reduce 任务中做处理，所以强制要求用户增加 LIMIT 语句可以防止 Reducer 阶段执行时间过长，该设置默认 false（关闭）。
严格模式开启后，不带 LIMIT 的 ORDER BY 查询会直接报错，实测信息如下。
SET hive.strict.checks.orderby.no.limit = true;
SELECT station_name FROM media_index ORDER BY station_name;
FAILED: SemanticException 2:46 Order by-s without limit are disabled for safety reasons.
If you know what you are doing, please set hive.strict.checks.orderby.no.limit to false and make sure
that hive.mapred.mode is not set to 'strict' to proceed. Note that you may get errors or incorrect
results if you make a mistake while making use of some of the unsafe features.
Error encountered near token 'station_name'
任务实现思路¶
- 因为需要使用子查询统计节目类型为直播的频道 Top10，且需要去重用户数，所以需要使用
GROUP BY语句，因此需要优化配置GROUP BY语句。 - 创建
media_index_Top10_view视图，从广电用户收视行为数据表中筛选出用户编号、直播频道名称和节目类型字段。 - 将最大切分数据块的大小设置为 128 MB。
- 因为广电用户收视行为数据表大小约为 781.3 MB，Hive 中每个 reduce 任务处理的数据量大小默认是 256 MB，所以设置 reduce 任务数为 4。
- 优化配置
GROUP BY语句，设置允许在 Map 端进行聚合，且设置允许在 Map 端进行聚合操作的数据量为 10000000。 - 设置执行
GROUP BY语句时，允许在发生数据倾斜时进行负载均衡。 - 开启任务并行执行。
- 设置使用
ORDER BY语句进行查询时必须使用LIMIT语句。 - 使用子查询统计节目类型为直播的频道 Top10，过滤掉点播或回放的节目类型数据。
任务实现¶
-- 创建视图
CREATE VIEW IF NOT EXISTS media_index_Top10_view (
phone_no,
station_name,
res_type)
AS SELECT phone_no, station_name, res_type FROM media_index;
-- 将最大切分数据块的大小设置为 128MB
SET mapreduce.input.fileinputformat.split.maxsize=134217728;
-- 设置 reduce 任务数为 4
SET mapreduce.job.reduces=4;
-- 设置允许在 Map 端进行聚合
SET hive.map.aggr = true;
-- 设置允许在 Map 端进行聚合操作的数据量
SET hive.groupby.mapaggr.checkinterval = 10000000;
-- 设置允许在发生数据倾斜时进行负载均衡
SET hive.groupby.skewindata = true;
-- 开启任务并行执行
SET hive.exec.parallel=true;
-- 设置使用 ORDER BY 语句进行查询时必须使用 LIMIT 语句
SET hive.strict.checks.orderby.no.limit = true;
-- 统计节目类型为直播的频道 Top10
SELECT a.station_name, COUNT(a.phone_no) AS users
FROM (SELECT * FROM media_index_Top10_view WHERE res_type != '1') a
GROUP BY a.station_name
ORDER BY users DESC
LIMIT 10;
实测结果：耗时 101.112 秒，启动 3 个 MapReduce 作业（Stage-1 为 7 map / 4 reduce，Stage-2 为 1 map / 4 reduce，Stage-3 为 1 map / 1 reduce），Top10 结果如下。
| 排名 | station_name | users |
|---|---|---|
| 1 | 中央1台-高清 | 304502 |
| 2 | 中央5台-高清 | 213712 |
| 3 | 翡翠台 | 178731 |
| 4 | 广州电视-高清 | 172332 |
| 5 | 凤凰中文 | 144223 |
| 6 | 浙江卫视-高清 | 142657 |
| 7 | 东方卫视-高清 | 135534 |
| 8 | 湖南卫视-高清 | 135272 |
| 9 | 中央6台-高清 | 133928 |
| 10 | 北京卫视-高清 | 133836 |
可以看到，中央 1 台、中央 5 台、翡翠台、广州电视等频道的收视量显著领先，这些频道是广电公司的核心资产，也是后续节目投资应优先保障的对象。
WHERE res_type != '1'过滤掉了点播或回放（res_type=1）的数据，保留的正是直播节目（res_type=0），与"统计节目类型为直播的频道"的语义一致。
四. 本章实测数据汇总¶
本章所有查询均在同一环境、同一份数据上实测得出，汇总如下，供读者对照参考。
| 查询 / 配置 | MR 作业数 | map 任务数 | reduce 任务数 | 实测耗时 |
|---|---|---|---|---|
LIMIT 5（Fetch 抓取开） |
0（不走 MR） | — | — | 2.317 秒 |
LIMIT 5（Fetch 抓取关） |
1 | 4 | 0 | 19.411 秒 |
SELECT * FROM 视图 LIMIT 10 |
0（不走 MR） | — | — | 0.368 秒 |
COUNT(*)（分片 256 MB） |
1 | 4 | 1 | 32.041 秒 |
COUNT(*)（分片 128 MB） |
1 | 7 | 1 | 48.051 秒 |
| 视图统计各节目用户数 | 1 | 4 | 4 | 34.507 秒 |
COUNT(DISTINCT station_name) |
1 | 7 | 1 | 39.288 秒 |
GROUP BY owner_name（默认配置） |
1 | 4 | 4 | 35.352 秒 |
GROUP BY owner_name（开启负载均衡） |
2 | 4 → 1 | 4 → 1 | 61.802 秒 |
COUNT(DISTINCT phone_no) |
1 | 4 | 1 | 28.842 秒 |
GROUP BY + COUNT 去重 |
2 | 4 → 1 | 4 → 1 | 61.149 秒 |
| 子查询查用户数 < 500 的等级 | 1 | 4 | 4 | 36.003 秒 |
| 直播频道 Top10（全部优化项） | 3 | 7 → 1 → 1 | 4 → 4 → 1 | 101.112 秒 |
优化项适用性判断¶
| 优化项 | 是否提速 | 适用条件 |
|---|---|---|
| Fetch 抓取 | 显著提速（8.4 倍） | 简单查询（无聚合、LIMIT）几乎无风险 |
| 调小分片以增加 map 数 | 本环境变慢 | 需多节点集群且节点数充足；单机慎用 |
| 设置 reduce 任务数 | 视数据量而定 | reduce 数不宜超过可用核数 |
| 并行执行 | 收益有限 | 集群资源有余量时开启，否则反而争抢资源 |
| Map 端预聚合 | 一般有效 | 聚合类查询的常规项，开销小 |
负载均衡 skewindata |
本环境变慢 | 仅在确有数据倾斜时启用，否则多一个 Stage 的开销 |
GROUP BY 替代 COUNT(DISTINCT) |
本环境变慢，但更稳 | 基数大时优先，牺牲时间换稳定性 |
LIMIT 抽样优化 |
有效但有随机性 | 结果可容忍不完整时使用 |
ORDER BY 严格模式 |
非提速项 | 安全防护，禁止无 LIMIT 全表排序 |
给读者的实践建议：优化不是"参数开得越多越好"。本章实测中，开启负载均衡、减小分片大小等教科书推荐项，在 4 核单机环境下均出现了耗时上升。正确的做法是：先用
EXPLAIN和作业计数器定位瓶颈，再针对瓶颈单项调整，每次改动后用实测数据验证效果。
小结¶
- 本章先介绍了 Hive 视图的创建、查看与删除方法。视图是存储于元数据中的伪表，只保存定义不存储数据，通过降低查询复杂度实现查询优化；视图依赖基本表存活，随意删除基本表会导致依赖视图的查询全部失败。
- 其次介绍了如何配置 Fetch 抓取、设置 map 和 reduce 任务数以及配置并行执行。Fetch 抓取对简单查询提速显著（实测 8.4 倍），而 map/reduce 任务数的调整必须结合集群实际资源规模来确定。
- 然后介绍了使用子查询的方法。子查询配合
WHERE过滤可减少参与 Shuffle 的数据量，是降低 I/O 消耗的重要手段。 - 最后介绍了优化配置
GROUP BY语句和LIMIT语句，并给出了GROUP BY替代COUNT(DISTINCT)的稳定性对比方案。 - 本章通过优化 Hive 配置与 HQL 语句，实现广电用户收视行为数据查询优化，帮助读者掌握各种 Hive 优化方法。
- 更重要的收获是方法论：本章所有优化项均给出了本环境的实测数据，其中多项"标准优化"在单机环境下反而变慢。优化应以瓶颈定位为前提、以实测验证为闭环，切忌照搬参数。