Hive关联时丢失数据问题和常用的Hive SQL参数设置

问题描述：
描述1：表A存在主键为1的数据行，表B也存在主键为1的数据行，表A left join 表B，取主键为1的数据行时，表B数据缺失。
描述2: 基于描述1，count(表B字段)数据时，计数行数不准确(主要表现为缺数据)

针对结果的发生，本文从以下方面分析原因及提供解决方案：

• 右表没有匹配的数据
• 关联键数据类型不匹配
• 受count列null值影响
• Hive版本问题，在某些版本中，左连可能导致右表为null
• 数据倾斜

并在文末附属了Hive SQL常用参数设置的说明。

下面进行逐一分析

1. 右表没有匹配的数据

SQL基础，略。

2. 关联键数据类型不匹配

首先要确定一个问题，Hive会不会自动匹配两表类型？下面开始验证

2.1 首先在test_db中创建表A，语句如下：

CREATE TABLE test_db.table_A
(
    id        int comment '编号',
    id_string string comment '编号字符串',
    name      string comment '名称'
)
    ROW FORMAT DELIMITED
        FIELDS TERMINATED BY ','
        LINES TERMINATED BY '\n'
        NULL DEFINED AS ''
    STORED AS TEXTFILE
    TBLPROPERTIES ("serialization.null.format" = '');

覆盖写入新数据如下：

insert overwrite table test_db.table_A
values (1, '1', '张三'),
       (2, '2', '李四'),
       (3, '3', '王五');
select id, id_string, name from test_db.table_A;

2.2 首先在test_db中创建表B，语句如下：

CREATE TABLE test_db.table_B
(
    id        int comment '编号',
    id_string string comment '编号字符串',
    money     decimal(10, 2) comment '金钱'
)
    ROW FORMAT DELIMITED
        FIELDS TERMINATED BY ','
        LINES TERMINATED BY '\n'
        NULL DEFINED AS ''
    STORED AS TEXTFILE
    TBLPROPERTIES ("serialization.null.format" = '');

覆盖写入新数据如下：

insert overwrite table test_db.table_B
values (1, '1', 10.1),
       (2, '2', 20.1),
       (2, '2', 20.2),
       (3, '3', 30.1),
       (3, '3', 30.2),
       (3, '3', null);
select id, id_string, money from test_db.table_B;

2.3 接下来使用int类型的table_a.id关联string类型的table_b.id_string

select a.id, b.id_string, a.name, b.money
from test_db.table_a a
left join test_db.table_b b on a.id = b.id_string

得到结果如下：

可见，Hive不会因类型而关联异常。一般来讲，同一个含义的列在不同表中应保持列名和类型相同。

3. 受count列null值影响

在Hive Sql中，count()方法的使用及相关区别如下：

select count(*) from test_db.table_b;
-- 输出结果：6
select count(1) from test_db.table_b;
-- 输出结果：6
select count(money) from test_db.table_b;
-- 输出结果：5
select count(distinct money) from test_db.table_b;
-- 输出结果：5
select count(distinct coalesce(money, 1)) from test_db.table_b;
-- 输出结果：6

可见，count(column)将自动排除null值数据行

4. Hive版本问题，在某些版本中，左连可能导致右表为null

这里直接照搬文档如下：

在Hive 0.13.0和Hive 0.14.0版本中，这两个版本的Hive引擎使用了新的Cost-Based Optimizer（CBO）作为查询优化器。
CBO主要是通过对数据分布和数据统计信息进行分析，来选择最优的查询计划。
但是，在左连接的情况下，CBO对于一些特殊情况下的数据分布和统计信息计算出的结果可能存在误差，从而导致左连接时未能正确关联上数据。

从Hive 0.15.0版本开始，Hive引擎将默认使用新的Rule-Based Optimizer（RBO），而不再使用CBO，因此该问题已经得到了解决。
同时，在Hive 0.13.0和0.14.0版本中，可以通过设置hive.cbo.enable参数为false，禁用CBO，从而规避这个问题。

5. 数据倾斜

关联中的数据倾斜通常是由于大小表关联，可能某个Reducer处理数据量与其他差异过大导致
我们可以通过查询前临时设置来解决这个问题，下面介绍几个常用设置：

• set hive.merge.smallfiles.avgsize=[数值];: 这个指令参数用于指定需要合并的小文件的平均大小，单位是字节。
  默认值是 16 MB。当输入数据被分割成多个小文件时，如果这些小文件总大小超过了这个阈值，Hive会自动合并这些小文件。
  这样可以减少Mapper的数量，提高作业的并发度，从而更好地处理数据倾斜的情况。
• set hive.exec.reducers.bytes.per.reducer=[数值];: 这个参数用于指定每个 Reducer 处理的数据量大小，单位是字节。
  这个数值过大、过小都不合适，需要预估查询从而设置更合适的范围。
  并且，如果发生了本文中提到的关联丢失数据，我建议仅使用这条参数即可解决，根据查询大小，尝试增加或减少此数值，尽可能平衡每个reducer的处理即可！
• set mapred.max.split.size=[数值]：这个参数用于指定 Map 任务处理的输入数据的最大分片大小，单位是字节，默认值是 1GB。
  默认值是 64MB。如果该参数设置得太小，可能会导致数据分片过多，从而增加了任务调度的开销，降低了并发度，也可能造成数据倾斜。
  如果设置得太大，又会导致单个 Map 任务处理的数据量过大，从而增加了单个任务的执行时间。因此，需要根据实际情况进行调整，使 Map 任务处理的数据量适中，以达到最佳的性能。

总结

几个方面分析完成以后，可以发现基本是数据倾斜导致的关联数据缺失，我建议参考上方所说的在执行特殊查询前加上set hive.exec.reducers.bytes.per.reducer=[数值];
尝试解决。需注意的是，请查看原本该值设定是多少，然后根据你的查询去决定数值为多少。

附文：常用的Hive SQL参数设置及解释

1. set hive.exec.parallel=true;: 默认值为false，开启Hive执行任务的并行化，这样Hive可以在多个处理器上同时执行不同的任务，提高作业的执行速度。
2. set mapreduce.job.queuename=hadoop;: 默认值为"default"，指定MapReduce作业提交到的队列名称，当Hadoop集群中有多个队列时，可以通过此参数指定作业提交的队列。
3. set hive.exec.dynamic.partition=true;: 默认值为false，开启Hive动态分区功能，这样可以在插入数据时动态创建分区，而不是需要提前手动创建好分区。
4. set hive.exec.dynamic.partition.mode=nostrict;: 默认值为"strict"，设置动态分区模式为非严格模式，即在插入数据时，只需要指定要插入的分区字段即可，不需要指定所有的分区字段。
5. set hive.exec.max.dynamic.partitions=10000;: 默认值为1000，设置动态分区最大数量为10000个，即在动态分区时，最多只能创建10000个分区。
6. SET hive.exec.max.dynamic.partitions.pernode=10000;: 默认值为100，设置每个节点上动态分区最大数量为10000个，即每个节点最多只能创建10000个分区。
7. set mapred.map.output.compression.codec=org.apache.hadoop.io.compress.GzipCodec;: 默认值为null，设置Map任务输出时采用Gzip压缩算法进行压缩，减小数据的磁盘占用。
8. set hive.exec.compress.intermediate=true;: 默认值为false，开启Hive中间结果的压缩，可以减小数据在磁盘上的占用。
9. set mapred.output.compression.type=BLOCK;: 默认值为NONE，可选的值为NONE、BLOCK、RECORD。设置输出数据的压缩格式为块压缩（Block Compression），这种压缩方式可以提高压缩率。
10. set hive.exec.compress.output=true;: 默认值为false，开启输出结果的压缩，减小数据在磁盘上的占用。
11. set hive.merge.smallfiles.avgsize=120000000;: 默认值为256MB，当小文件个数超过一定阈值时，Hive会自动将它们合并成一个大文件。这里设置合并后的文件大小为120MB。
12. set hive.exec.reducers.bytes.per.reducer=10000000;: 默认值为1GB，设置每个Reducer处理的数据量大小，这里设置为10MB。
13. set mapred.max.split.size=100000000;: 默认值为64MB，设置每个输入Split的最大大小为100MB，这样可以控制每个Map任务处理的数据量，避免数据倾斜。
14. set hive.merge.mapfiles=true;: 默认值为false，开启小文件合并，将多个小文件合并成一个大文件。
15. set hive.merge.mapredfiles=true;: 默认值为false，开启Map任务输出结果的小文件合并功能，可以将多个Map任务输出的小文件合并成一个大文件。