title: Hive进阶
cover: /cover_imgs/hive.png
tags:
- Hive
categories:
- Hive
第 1 章 Explain 查看执行计划（重点）
1.1 基础语法
EXPLAIN [EXTENDED | DEPENDENCY | AUTHORIZATION] query-sql
1.2 使用
explain select * from bigtable; #查看语句的执行计划
explain extended select * from bigtable; #查看该语句的详细执行计划
explain select click_url, count(*) ct from bigtable group by click_url;
explain extended select click_url, count(*) ct from bigtable group by click_url;
第 2 章 Hive建表优化
2.1 分区表
分区表实际上就是对应一个 HDFS 文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。
Hive 中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。
在查询时通过 WHERE 子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多，所以我们需要把常常用在 WHERE 语句中的字段指定为表的分区字段。
2.1 .1 创建分区表语法
create table tablename(
xxx type, xxx type, xxx type
)
partitioned by (xxx type)
row format delimited fields terminated by '\t';
注意：分区字段不能是表中已经存在的数据，可以将分区字段看作表的伪列。
注意：分区表加载数据时，必须指定分区
2.1.2 分区表相关语法
load data local inpath 'DataPath' into table tablename partition(xxx='xxx');
alter table tablename add partition(xxx='xxx'); # 增加分区
alter table dept_partition add partition(xxx='xxx') partition(xx='xxx'); # 增加多个分区
alter table dept_partition drop partition (xx='xx'); # 删除分区
alter table dept_partition drop partition (xx='xx'), partition(xx='xx'); # 删除多个分区
show partitions tablename; # 查看分区表有多少分区
desc formatted tablename; # 查看表结构
思考: 如果一天的日志数据量也很大，如何再将数据拆分?
2.1.3 二级分区
（1）创建二级分区表
create table tablename(
xxx type, xxx type, xxx type )
partitioned by (day string, hour string)
row format delimited fields terminated by '\t';
（2） 加载数据
load data local inpath 'DataPath'
into table tablename
partition(day='xx', hour='xx');
（3） 查询分区数据
select * from tablename where day='xxx' and hour='xxx';
2.1.4 动态分区
关系型数据库中，insert数据到分区表中时，会自动根据分区字段将数据插入到对应的分区中（即动态分区）。但要进行配置。
1）开启动态分区参数设置
set hive.exec.dynamic.partition=true; #默认为true
set hive.exec.dynamic.partition.mode=nonstrict; # 设置为非严格模式（动态分区的模式，默认strict，表示必须指定至少一个分区为静态分区，nonstrict 模式表示允许所有的分区字段都可以使用动态分区。）
set hive.exec.max.dynamic.partitions=1000; # 在所有执行 MR 的节点上，最大一共可以创建多少个动态分区。默认 1000
set hive.exec.max.dynamic.partitions.pernode=100 # 该参数需要根据实际的数据来设定。比如：源数据中包含了一年的数据，即 day 字段有365 个值，那么该参数就需要设置成大于 365，如果使用默认值 100，则会报错。
set hive.exec.max.created.files=100000 #整个 MR Job 中,最大可以创建多少个 HDFS 文件。默认100000
set hive.error.on.empty.partition=false #当有空分区生成时,是否抛出异常。一般不需要设置。默认 false
2 ) 案例实操
(1) 创建目标分区表
create table tablename(id int, name string)
partitioned by (loc int)
row format delimited fields terminated by '\t';
(2) 设置动态分区
insert into table tablename
partition(loc)
select deptno, dname, loc from dept;
(3) 查看目标分区表的分区情况
show partitions dept_partition;
2.2 分桶表
分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区。对于一张表或者分区，Hive 可以进一步组织成桶，也就是更为细粒度的数据范围 划分。
分桶是将数据集分解成更容易管理的若干部分的另一个技术。分区针对的是数据的存储路径，分桶针对的是数据文件。
分桶表会将指定列的值进行哈希散列，并对 bucket（桶数量）取余，然后存储到对应的 bucket（桶）中。
2.2.1 创建分桶表
create table stu_buck(id int, name string)
clustered by(id)
into 4 buckets
row format delimited fields terminated by '\t';
分桶规则： 根据结果可知：Hive 的分桶采用对分桶字段的值进行哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中
2.2.2 抽样查询
对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结 果。Hive 可以通过对表进行抽样来满足这个需求。
语法: TABLESAMPLE(BUCKET x OUT OF y)
select * from stu_buck tablesample(bucket 1 out of 4 on id);
注意：x 的值必须小于等于 y 的值
2.3 合适的文件格式
2.3.1 行式存储和列式存储
Hive 支持的存储数据的格式主要有：
行式存储：TEXTFILE 、SEQUENCEFILE
特点：当查找某一行时，只需要找到其中一个值，其他的都聚集在旁边。所以行存储查询速度较快。
列式存储：ORC、PARQUET
特点：当只查询几个字段时，能大大减少读取数据量，每个字段的数据类型是一样的。可以设置更好的压缩算法。
左边为逻辑表，右上为行式存储，右下为列式存储。
2.3.2 TextFile格式
默认格式、数据不做压缩、磁盘开销大、数据解析开销大、结合Gzip, Bzip2使用，但使用Gzip, 数据不进行切分，故不发进行数据并行操作。
2.3.3 Orc格式
Orc (Optimized Row Columnar)是 Hive 0.11 版里引入的新的存储格式。
2.3.4 Parquet格式
Parquet文件是二进制存储的，不能直接读取。存储的文件包括数据和元数据。Parquet格式文件是自解析的
2.4 合适的压缩格式
| 压缩格式 | hadoop自带? | 算法 | 文件拓展名 | 是否可切片 | 换压缩格式,原程序是否需要修改 |
|---|---|---|---|---|---|
| DEFLATE | 是，直接使用 | DEFLATE | .deflate | 否 | 和文本处理一样，不需要修改 |
| Gzip | 是，直接使用 | DEFLATE | .gz | 否 | 和文本处理一样，不需要修改 |
| bzip2 | 是，直接使用 | bzip2 | .bz2 | 是 | 和文本处理一样，不需要修改 |
| LZO | 否，需要安装 | LZO | .lzo | 是 | 需要建索引，还需要指定输入格式 |
| Snappy | 否，需要安装 | Snappy | .snappy | 否 | 和文本处理一样，不需要修改 |
为了支持多种压缩/解压缩算法，Hadoop 引入了编码/解码器，如下表所示。
| 压缩格式 | 对应的编码/解码器 |
|---|---|
| DEFLATE | org.apache.hadoop.io.compress.DefaultCodec |
| Gzip | org.apache.hadoop.io.compress.GzipCodec |
| bzip2 | org.apache.hadoop.io.compress.BZip2Codec |
| LZO | com.hadoop.compression.lzo.LzopCodec |
| Snappy | org.apache.hadoop.io.compress.SnappyCodec |
第 3 章 HQL语法优化
3.1 列裁剪与分区裁剪
列裁剪就是在查询时只读取需要的列，分区裁剪就是只读取需要的分区。当列很多或者数据量很大时，如果 select * 或者不指定分区，全列扫描和全表扫描效率都很低。
3.2 Group by
默认情况，Map阶段的同一key数据分发给一个Reduce,当Key过大时，就会造成数据倾斜。
并不是所有的聚合操作都要在reduce端完成，很多聚合操作都可以先在 Map 端进行部分聚合，最后在 Reduce 端得出最终结果。
开启 Map端聚合参数设置
set hive.map.aggr = true; #是否在 Map 端进行聚合，默认为 True
set hive.groupby.mapaggr.checkinterval = 100000; #在 Map 端进行聚合操作的条目数目
set hive.groupby.skewindata = true; #有数据倾斜的时候进行负载均衡（默认是 false）
当选项设定为 true，生成的查询计划会有两个 MR Job。
第一个MR中，Map的结果随机发给Reduce, Reduce部分聚合后，并输出结果。相同的 Group By Key 有可能被分发到不同的 Reduce 中，从而达到负载均衡的目的；
第二个MR中，对预处理的数据结果按照 Group By Key 分布到 Reduce 中（保证相同的key分布到同一个Reduce）。---虽然 能解决数据倾斜，但是不能让运行速度的更快。
3.3 多重模式
一堆 SQL，并且这一堆 SQL 的模式还一样。都是从同一个表进行扫描，做不同的逻辑
insert into t_ptn partition(city=A). select id,name,sex, age from student where city= A;
insert into t_ptn partition(city=B). select id,name,sex, age from student where city= B;
insert int0 t_ptn partition(city=c). select id,name,sex, age from student where city= c;
修改为：
from student
insert into t_ptn partition(city=A) select id,name,sex, age where city= A
insert into t_ptn partition(city=B) select id,name,sex, age where city= B
如果一个 HQL 底层要执行 10 个 Job，那么能优化成 8 个一般来说，肯定能有所提高， 多重插入就是一个非常实用的技能。一次读取，多次插入，有些场景是从一张表读取数据后， 要多次利用。
3.4 left semi join语句
-- in / exists 实现
select a.id, a.name from a where a.id in (select b.id from b);
select a.id, a.name from a where exists (select id from b where a.id = b.id);
-- join 来改写
select a.id, a.name from a join b on a.id = b.id;
-- left semi join 实现
select a.id, a.name from a left semi join b on a.id = b.id;
1、left semi join 的限制是， JOIN 子句中右边的表只能在 ON 子句中设置过滤条件，在 WHERE 子句、SELECT 子句或其他地方过滤都不行。
2、left semi join 是只传递表的 join key 给 map 阶段，因此left semi join 中最后 select 的结果只许出现左表。
3、因为 left semi join 是 in(keySet) 的关系，遇到右表重复记录，左表会跳过，而 join 则会一直遍历。这就导致右表有重复值得情况下 left semi join 只产生一条，join 会产生多条，也会导致 left semi join 的性能更高。
比如以下A表和B表进行 join 或 left semi join，然后 select 出所有字段，结果区别如下：
注意：蓝色叉的那一列实际是不存在left semi join中的，因为最后 select 的结果只许出现左表。
3.5 CBO 优化
要使用基于成本的优化（也称为 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;
3.6 谓词下推
将 SQL 语句中的 where 谓词逻辑都尽可能提前执行，减少下游处理的数据量。
打开谓词下推优化属性
set hive.optimize.ppd = true; #谓词下推，默认是 true
3.7 MapJoin
MapJoin 是将 Join 双方比较小的表直接分发到各个 Map 进程的内存中，在 Map 进程中进行 Join 操 作，这样就不用进行 Reduce 步骤，从而提高了速度。
如果不指定 MapJoin 或者不符合 MapJoin 的条件，那么 Hive 解析器会将 Join 操作转换成 Common Join。
在 Reduce 阶段完成 Join，容易发生数据倾斜。可以用 MapJoin 把小表全部加载到内存在 Map 端进行 Join，避免 Reducer 处理。
1）开启 MapJoin 参数设置
set hive.auto.convert.join=true; #默认为 true
set hive.mapjoin.smalltable.filesize=25000000; #大表小表的阈值设置（默认 25M 以下认为是小表）
2）MapJoin 工作机制
MapJoin 是将 Join 双方比较小的表直接分发到各个 Map 进程的内存中，在 Map 进 程中进行 Join 操作，这样就不用进行 Reduce 步骤，从而提高了速度。
注意：小表(左连接)作为主表，所有数据都要写出去，因此此时会走 reduce，mapjoin 失
3.8大表、大表 SMB Join（重点）
创建分桶表
SMB Join ：Sort Merge Bucket Join
分桶的方式采取的是：对列的值进行hash除以桶的数量取余 ；
create table bigtable_buck1(
id bigint,
...)
clustered by(id)
sorted by(id)
into 6 buckets
row format delimited fields terminated by '\t';
load data local inpath '...' into table bigtable_buck1;
设置参数
set hive.optimize.bucketmapjoin = true;
set hive.optimize.bucketmapjoin.sortedmerge = true;
set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;
测试 发现分桶表之间join可以节省不少时间
3.9 笛卡尔积
Join 的时候不加 on 条件，或者无效的 on 条件，因为找不到 Join key，Hive 只能使用 1 个 Reducer 来完成笛卡尔积。当 Hive 设定为严格模式（hive.mapred.mode=strict，nonstrict） 时，不允许在 HQL 语句中出现笛卡尔积。
第 4 章 数据倾斜 (重点)
绝大部分任务都很快完成，只有一个或者少数几个任务执行的很慢甚至最终执行失败， 这样的现象为数据倾斜现象。
一定要和数据过量导致的现象区分开，数据过量的表现为所有任务都执行的很慢，这个 时候只有提高执行资源才可以优化 HQL 的执行效率。
综合来看，导致数据倾斜的原因在于按照 Key 分组以后，少量的任务负责绝大部分数据 的计算，也就是说产生数据倾斜的 HQL 中一定存在分组操作，那么从 HQL 的角度，我们可以将数据倾斜分为单表携带了 GroupBy 字段的查询和两表（或者多表）Join 的查询。
4.1 单表数据倾斜优化
4.1.1 参数解决数据倾斜
set hive.map.aggr = true; 是否在 Map 端进行聚合，默认为 True
set hive.groupby.mapaggr.checkinterval = 100000; #在 Map 端进行聚合操作的条目数目
set hive.groupby.skewindata = true; #有数据倾斜的时候进行负载均衡（默认是 false）
当选项设定为 true，生成的查询计划会有两个 MR Job。
4.1.2 增加reduce数解决数据倾斜
4.2 Join 数据倾斜优化
在编写 Join 查询语句时，如果确定是由于 join 出现的数据倾斜，那么请做如下设置：
4.2.1 参数
# join 的键对应的记录条数超过这个值则会进行分拆，值根据具体数据量设置
set hive.skewjoin.key=100000;
# 如果是 join 过程出现倾斜应该设置为 true
set hive.optimize.skewjoin=false;
如果开启了，在 Join 过程中 Hive 会将计数超过阈值 hive.skewjoin.key（默认 100000）的 倾斜 key 对应的行临时写进文件中，然后再启动另一个 job 做 map join 生成结果。通过 hive.skewjoin.mapjoin.map.tasks 参数还可以控制第二个 job 的 mapper 数量，默认 10000。
set hive.skewjoin.mapjoin.map.tasks=10000;
4.2.2 MapJoin
详情见3.7节
第 5 章 Hive Job 优化
5.1 Hive Map 优化
5.1.1 复杂文件增加 Map 数
当 input 的文件都很大，任务逻辑复杂，map 执行非常慢的时候，可以考虑增加 Map 数，来使得每个 map 处理的数据量减少，从而提高任务的执行效率。
计算切片大小的逻辑为computeSliteSize(Math.max(minSize,Math.min(maxSize,blocksize)))=blocksize=128M
mapreduce.input.fileinputformat.split.minsize=1 默认值为1
mapreduce.input.fileinputformat.split.maxsize= Long.MAXValue 默认值Long.MAXValue
因此，默认情况下，切片大小=blocksize。
maxsize（切片最大值）：参数如果调得比blocksize小，则会让切片变小，而且就等于配置的这个参数的值。
minsize（切片最小值）：参数调的比blockSize大，则可以让切片变得比blocksize还大。
操作
select count(*) from emp;
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
set mapreduce.input.fileinputformat.split.maxsize=100;#设置最大切片值为 100 个字节
select count(*) from emp;
Hadoop job information for Stage-1: number of mappers: 6; number of reducers: 1
5.1.2 小文件进行合并
1）在 map 执行前合并小文件，减少 map 数：CombineHiveInputFormat 具有对小文件进行合 并的功能（系统默认的格式）。HiveInputFormat 没有对小文件合并功能
set hive.input.format= org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
5.1.3 Map 端聚合
5.2 Hive Reduce 优化
5.2.1 合理设置 Reduce 数
reduce 个数并不是越多越好
（1）过多的启动和初始化 reduce 也会消耗时间和资源；
（2）另外，有多少个 reduce，就会有多少个输出文件，如果生成了很多个小文件，那 么如果这些小文件作为下一个任务的输入，则也会出现小文件过多的问题；
在设置 reduce 个数的时候也需要考虑这两个原则：处理大数据量利用合适的 reduce 数； 使单个 reduce 任务处理数据量大小要合适；
5.3 Hive 任务整体优化
5.3.1 Fetch 抓取
Fetch 抓取是指，Hive 中对某些情况的查询可以不必使用 MapReduce 计算。
在 hive-default.xml.template 文件中 hive.fetch.task.conversion 默认是 more，老版本 hive 默认是 minimal，该属性修改为 more 以后，在全局查找、字段查找、limit 查找等都不走mapreduce。
5.3.2 本地模式
大多数的 Hadoop Job 是需要 Hadoop 提供的完整的可扩展性来处理大数据集的。不过， 有时 Hive 的输入数据量是非常小的。在这种情况下，为查询触发执行任务消耗的时间可能 会比实际 job 的执行时间要多的多。对于大多数这种情况，Hive 可以通过本地模式在单台机 器上处理所有的任务。对于小数据集，执行时间可以明显被缩短。
用户可以通过设置 hive.exec.mode.local.auto 的值为 true，来让 Hive 在适当的时候自动 启动这个优化。
set hive.exec.mode.local.auto=true; //开启本地 mr
set hive.exec.mode.local.auto.inputbytes.max=50000000; //设置local mr的最大输入数据量，当输入数据量小于这个值时采用local mr 的方式，默认为 134217728，即128M
set hive.exec.mode.local.auto.input.files.max=10; //设置local mr的最大输入文件个数，当输入文件个数小于这个值时采用 local mr 的方式，默认为 4
5.3.3 并行执行
5.3.4 严格模式
Hive 可以通过设置防止一些危险操作：
1）分区表不使用分区过滤
将 hive.strict.checks.no.partition.filter 设置为 true 时，对于分区表，除非 where 语句中含 有分区字段过滤条件来限制范围，否则不允许执行。
2）使用 order by 没有 limit 过滤
将 hive.strict.checks.orderby.no.limit 设置为 true 时，对于使用了 order by 语句的查询，要 求必须使用 limit 语句。
3）笛卡尔积
将 hive.strict.checks.cartesian.product 设置为 true 时，会限制笛卡尔积的查询。
5.3.5 JVM 重用
小文件过多的时候使用。
标签：set,进阶,分区,hive,Hive,id,select From： https://www.cnblogs.com/songxr/p/16939952.html