hive建表设计层面

1.使用分区表优化

分区表 是在某一个或者几个维度上对数据进行分类存储，一个分区对应一个目录。如果筛选条件里有分
区字段，那么 Hive 只需要遍历对应分区目录下的文件即可，不需要遍历全局数据，使得处理的数据量
大大减少，从而提高查询效率。

也就是说：当一个 Hive 表的查询大多数情况下，会根据某一个字段进行筛选时，那么非常适合创建为
分区表，该字段即为分区字段。

2.使用分桶表优化

跟分区的概念很相似，都是把数据分成多个不同的类别，区别就是规则不一样！

1、分区：按照字段值来进行：一个分区，就只是包含这个这一个值的所有记录
不是当前分区的数据一定不在当前分区
当前分区也只会包含当前这个分区值的数据

2、分桶：默认规则：Hash散列
一个分桶中会有多个不同的值
如果一个分桶中，包含了某个值，这个值的所有记录，必然都在这个分桶

Hive Bucket，分桶，是指将数据以指定列的值为 key 进行 hash，hash 到指定数目的桶中，这样做的
目的和分区表类似，使得筛选时不用全局遍历所有的数据，只需要遍历所在桶就可以了。这样也可以支
持高效采样。

3.选择合适的文件存储格式

Apache Hive支持 Apache Hadoop 中使用的几种熟悉的文件格式，比如 TextFile、SequenceFile、RCFile、Avro、ORC、ParquetFile等。

存储格式一般需要根据业务进行选择，在我们的实操中，绝大多数表都采用TextFile与Parquet两种存储
格式之一。 TextFile是最简单的存储格式，它是纯文本记录，也是Hive的默认格式。虽然它的磁盘开销
比较大，查询效率也低，但它更多地是作为跳板来使用。RCFile、ORC、Parquet等格式的表都不能由
文件直接导入数据，必须由TextFile来做中转。 Parquet和ORC都是Apache旗下的开源列式存储格式。
列式存储比起传统的行式存储更适合批量OLAP查询，并且也支持更好的压缩和编码。

创建表时，特别是宽表，尽量使用 ORC、ParquetFile 这些列式存储格式，因为列式存储的表，每一
列的数据在物理上是存储在一起的，Hive查询时会只遍历需要列数据，大大减少处理的数据量。

第一种：TextFile

1、存储方式：行存储。默认格式，如果建表时不指定默认为此格式。，
2、每一行都是一条记录，每行都以换行符"\n"结尾。数据不做压缩时，磁盘会开销比较大，数据解析开销也 比较大。
3、可结合Gzip、Bzip2等压缩方式一起使用（系统会自动检查，查询时会自动解压）,推荐选用可切分的压 缩算法。

第二种：Sequence File

1、一种Hadoop API提供的二进制文件，使用方便、可分割、个压缩的特点。
2、支持三种压缩选择：NONE、RECORD、BLOCK。RECORD压缩率低，一般建议使用BLOCK压缩。

第三种：RC File

1、存储方式：数据按行分块，每块按照列存储 。
A、首先，将数据按行分块，保证同一个record在一个块上，避免读一个记录需要读取多个block。
B、其次，块数据列式存储，有利于数据压缩和快速的列存取。
2、相对来说，RCFile对于提升任务执行性能提升不大，但是能节省一些存储空间。可以使用升级版的ORC格 式。

第四种：ORC File

1、存储方式：数据按行分块，每块按照列存储
2、Hive提供的新格式，属于RCFile的升级版，性能有大幅度提升，而且数据可以压缩存储，压缩快，快速 列存取。 3、ORC
File会基于列创建索引，当查询的时候会很快。

第五种：Parquet File

1、存储方式：列式存储。
2、Parquet对于大型查询的类型是高效的。对于扫描特定表格中的特定列查询，Parquet特别有用。
Parquet一般使用Snappy、Gzip压缩。默认Snappy。
3、Parquet支持Impala 查询引擎。
4、表的文件存储格式尽量采用Parquet或ORC，不仅降低存储量，还优化了查询，压缩，表关联等性能。

4.选择合适的压缩格式

Hive 语句最终是转化为 MapReduce 程序来执行的，而 MapReduce 的性能瓶颈在与 网络IO 和 磁盘
IO，要解决性能瓶颈，最主要的是 减少数据量，对数据进行压缩是个好方式。压缩虽然是减少了数据
量，但是压缩过程要消耗 CPU，但是在 Hadoop 中，往往性能瓶颈不在于 CPU，CPU 压力并不大，所
以压缩充分利用了比较空闲的 CPU。

map阶段输出数据压缩 ，在这个阶段，优先选择一个低CPU开销的算法。

set hive.exec.compress.intermediate=true
set mapred.map.output.compression.codec= org.apache.hadoop.io.compress.SnappyCodec
set mapred.map.output.compression.codec=com.hadoop.compression.lzo.LzoCodec;
1
2
3

对最终输出结果压缩

set hive.exec.compress.output=true
set mapred.output.compression.codec=org.apache.hadoop.io.compress.SnappyCodec
1
2

##当然，也可以在hive建表时指定表的文件格式和压缩编码
结论，一般选择orcfile/parquet + snappy 方式

sql语法和运行参数层面

1.列裁剪

列裁剪就是在查询时只读取需要的列，分区裁剪就是只读取需要的分区。当列很多或者数据量很大时，
如果 select * 或者不指定分区，全列扫描和全表扫描效率都很低。
Hive 在读数据的时候，可以只读取查询中所需要用到的列，而忽略其他的列。这样做可以节省读取开
销：中间表存储开销和数据整合开销。

set hive.optimize.cp = true; ## 列裁剪，取数只取查询中需要用到的列，默认是true
1

2.谓词下推

将 SQL 语句中的 where 谓词逻辑都尽可能提前执行，减少下游处理的数据量。对应逻辑优化器是
PredicatePushDown。

set hive.optimize.ppd=true;
1

3.合并小文件

在执行 MapReduce 程序的时候，一般情况是一个文件的一个数据分块需要一个 mapTask 来处理。但
是如果数据源是大量的小文件，这样就会启动大量的 mapTask 任务，这样会浪费大量资源。可以将输
入的小文件进行合并，从而减少 mapTask 任务数量。

4.合理控制map task和reduce task的数量

这个优化措施，但凡能用就用！ 大表 join 小表 小表满足需求： 小表数据小于控制条件时
MapJoin 是将 join 双方比较小的表直接分发到各个 map 进程的内存中，在 map 进程中进行 join 操
作，这样就不用进行 reduce 步骤，从而提高了速度。只有 join 操作才能启用 MapJoin。

5.join 数据倾斜优化

在编写 Join 查询语句时，如果确定是由于 join 出现的数据倾斜，那么请做如下设置：

join的键对应的记录条数超过这个值则会进行分拆，值根据具体数据量设置

set hive.skewjoin.key=100000;
1

如果是join过程出现倾斜应该设置为true

 set hive.optimize.skewjoin=false;
1

如果开启了，在 Join 过程中 Hive 会将计数超过阈值 hive.skewjoin.key（默认100000）的倾斜 key 对
应的行临时写进文件中，然后再启动另一个 job 做 map join 生成结果。
通过 hive.skewjoin.mapjoin.map.tasks 参数还可以控制第二个 job 的 mapper 数量，默认
10000。

6.CBO优化

join的时候表的顺序的关系：前面的表都会被加载到内存中。后面的表进行磁盘扫描。

select a.*, b.*, c.* from a join b on a.id = b.id join c on a.id = c.id;
1

Hive 自 0.14.0 开始，加入了一项 “Cost based Optimizer” 来对 HQL 执行计划进行优化，这个功能通
过 “hive.cbo.enable” 来开启。在 Hive 1.1.0 之后，这个 feature 是默认开启的，它可以 自动优化 HQL
中多个 Join 的顺序，并选择合适的 Join 算法。

CBO，成本优化器，代价最小的执行计划就是最好的执行计划。传统的数据库，成本优化器做出最优化
的执行计划是依据统计信息来计算的。Hive 的成本优化器也一样。
Hive 在提供最终执行前，优化每个查询的执行逻辑和物理执行计划。这些优化工作是交给底层来完成
的。根据查询成本执行进一步的优化，从而产生潜在的不同决策：如何排序连接，执行哪种类型的连
接，并行度等等。
要使用基于成本的优化（也称为CBO），请在查询开始设置以下参数：

set hive.cbo.enable=true;
set hive.compute.query.using.stats=true;
set hive.stats.fetch.column.stats=true;
set hive.stats.fetch.partition.stats=true;
1
2
3
4

hive架构层面

1.启用本地抓取

Hive 的某些 SQL 语句需要转换成 MapReduce 的操作，某些 SQL 语句就不需要转换成 MapReduce 操
作，但是同学们需要注意，理论上来说，所有的 SQL 语句都需要转换成 MapReduce 操作，只不过
Hive 在转换 SQL 语句的过程中会做部分优化，使某些简单的操作不再需要转换成 MapReduce，例
如：

1、只是 select * 的时候
2、where 条件针对分区字段进行筛选过滤时
3、带有 limit 分支语句时

Hive 从 HDFS 中读取数据，有两种方式：启用MapReduce读取 和 直接抓取。
直接抓取数据比 MapReduce 方式读取数据要快的多，但是只有少数操作可以使用直接抓取方式。
可以通过 hive.fetch.task.conversion 参数来配置在什么情况下采用直接抓取方式：

minimal：只有 select * 、在分区字段上 where 过滤、有 limit 这三种场景下才启用直接抓取方式。 more：在
select、where 筛选、limit 时，都启用直接抓取方式。

2.本地执行优化

Hive 在集群上查询时，默认是在集群上多台机器上运行，需要多个机器进行协调运行，这种方式很好
的解决了大数据量的查询问题。但是在 Hive 查询处理的数据量比较小的时候，其实没有必要启动分布
式模式去执行，因为以分布式方式执行设计到跨网络传输、多节点协调等，并且消耗资源。对于小数据
集，可以通过本地模式，在单台机器上处理所有任务，执行时间明显被缩短。

启动本地模式涉及到三个参数：

--打开hive自动判断是否启动本地模式的开关
set hive.exec.mode.local.auto=true;
-- map任务数最大值，不启用本地模式的task最大个数
set hive.exec.mode.local.auto.input.files.max=4;
-- map输入文件最大大小，不启动本地模式的最大输入文件大小
set hive.exec.mode.local.auto.inputbytes.max=134217728;
1
2
3
4
5
6

3.JVM重用

Hive 语句最终会转换为一系列的 MapReduce 任务，每一个MapReduce 任务是由一系列的 MapTask
和 ReduceTask 组成的，默认情况下，MapReduce 中一个 MapTask 或者 ReduceTask 就会启动一个
JVM 进程，一个 Task 执行完毕后，JVM 进程就会退出。这样如果任务花费时间很短，又要多次启动
JVM 的情况下，JVM 的启动时间会变成一个比较大的消耗，这时，可以通过重用 JVM 来解决。

set mapred.job.reuse.jvm.num.tasks=5;
1

JVM也是有缺点的，开启JVM重用会一直占用使用到的 task 的插槽，以便进行重用，直到任务完成后才
会释放。如果某个 不平衡的job 中有几个 reduce task 执行的时间要比其他的 reduce task 消耗的时间
要多得多的话，那么保留的插槽就会一直空闲却无法被其他的 job 使用，直到所有的 task 都结束了才
会释放。
根据经验，一般来说可以使用一个 cpu core 启动一个 JVM，假如服务器有 16 个 cpu core ，但是这个
节点，可能会启动 32 个mapTask，完全可以考虑：启动一个JVM，执行两个Task。

4.并行执行

有的查询语句，Hive 会将其转化为一个或多个阶段，包括：MapReduce 阶段、抽样阶段、合并阶段、
limit 阶段等。默认情况下，一次只执行一个阶段。但是，如果某些阶段不是互相依赖，是可以并行执行
的。多阶段并行是比较耗系统资源的。
一个 Hive SQL 语句可能会转为多个 MapReduce Job，每一个 job 就是一个 stage，这些 Job 顺序执
行，这个在 cli 的运行日志中也可以看到。但是有时候这些任务之间并不是是相互依赖的，如果集群资
源允许的话，可以让多个并不相互依赖 stage 并发执行，这样就节约了时间，提高了执行速度，但是如
果集群资源匮乏时，启用并行化反倒是会导致各个 Job 相互抢占资源而导致整体执行性能的下降。启用
并行化：

-- 可以开启并发执行。
set hive.exec.parallel=true;
--同一个sql允许最大并行度，默认为8。
set hive.exec.parallel.thread.number=16;
1
2
3
4

sql优化

1 对于这个调优，有待再次验证目前我验证了4千万 join 8千万发现不管是left join还是inner join，执行计划差不多，job数，maptask，reducetask数都一样，执行时长也差不多可能都已经自动优化了(谓词下推)

select
count(1)
from test1 t1
inner join test2 t2
on t1.uuid = t2.uuid and t2.date_id = ‘2020-10-23’
where t1.date_id = ‘2020-10-23’；

select
count(1)
from (select * from test1 where date_id = ‘2020-10-23’) t1
inner join test2 t2
on t1.uuid = t2.uuid and t2.date_id = ‘2020-10-23’
1
2
3
4
5
6
7
8
9
10
11
12

;

2 .union优化

尽量不要使用union （union 去掉重复的记录）而是使用 union all 然后再用group by 去重

3 .count distinct优化

数据量小的时候无所谓，数据量大的情况下，由于COUNT DISTINCT操作需要用一个Reduce Task来完成，这一个Reduce需要处理的数据量太大，就会导致整个Job很难完成，一般COUNT DISTINCT使用先GROUP BY再COUNT的方式替换：
不要使用count (distinct cloumn) ,使用子查询

select count(1) from (select id from tablename group by id) tmp;
1

虽然会多用一个Job来完成，但在数据量大的情况下，这个绝对是值得的。

4.用in 来代替join

如果需要根据一个表的字段来约束另为一个表，尽量用in来代替join . in 要比join 快

select
a.Id
,name
from tb1 a
join tb2 b
on(a.id = b.id);
1
2
3
4
5
6

select
Id
,name
from tb1
where id in(select id from tb2);
1
2
3
4
5

5. Left semi join优化in/exists

(a表和b表通过user_id关联)
a表数据

select * from wedw_dw.t_user;
1

b表数据

 select * from wedw_dw.t_order;
1

l

--left semi join
Select
*
from
wedw_dw.t_user t1
left semi join wedw_dw.t_order t2
on t1.user_id = t2.user_id;
1
2
3
4
5
6
7

如图所示：只能展示a表的字段，因为left semi join 只传递表的 join key 给 map 阶段