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

简介: 针对结果的发生,本文从以下方面分析原因及提供解决方案:- 右表没有匹配的数据- 关联键数据类型不匹配- 受count列null值影响- Hive版本问题,在某些版本中,左连可能导致右表为null- 数据倾斜并在文末附属了`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;

table_a数据.pngtable_a数据.png

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;

table_b数据.pngtable_b数据.png

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

得到结果如下:
ab表关联.pngab表关联.png

可见,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任务输出的小文件合并成一个大文件。
相关文章
|
1月前
|
SQL 人工智能 JSON
Flink 2.1 SQL:解锁实时数据与AI集成,实现可扩展流处理
简介:本文整理自阿里云高级技术专家李麟在Flink Forward Asia 2025新加坡站的分享,介绍了Flink 2.1 SQL在实时数据处理与AI融合方面的关键进展,包括AI函数集成、Join优化及未来发展方向,助力构建高效实时AI管道。
484 43
|
1月前
|
SQL 人工智能 JSON
Flink 2.1 SQL:解锁实时数据与AI集成,实现可扩展流处理
本文整理自阿里云的高级技术专家、Apache Flink PMC 成员李麟老师在 Flink Forward Asia 2025 新加坡[1]站 —— 实时 AI 专场中的分享。将带来关于 Flink 2.1 版本中 SQL 在实时数据处理和 AI 方面进展的话题。
163 0
Flink 2.1 SQL:解锁实时数据与AI集成,实现可扩展流处理
|
2月前
|
SQL
SQL如何只让特定列中只显示一行数据
SQL如何只让特定列中只显示一行数据
|
6月前
|
SQL 自然语言处理 数据库
【Azure Developer】分享两段Python代码处理表格(CSV格式)数据 : 根据每列的内容生成SQL语句
本文介绍了使用Python Pandas处理数据收集任务中格式不统一的问题。针对两种情况:服务名对应多人拥有状态(1/0表示),以及服务名与人名重复列的情况,分别采用双层for循环和字典数据结构实现数据转换,最终生成Name对应的Services列表(逗号分隔)。此方法高效解决大量数据的人工处理难题,减少错误并提升效率。文中附带代码示例及执行结果截图,便于理解和实践。
162 4
|
2月前
|
SQL
SQL中如何删除指定查询出来的数据
SQL中如何删除指定查询出来的数据
|
2月前
|
SQL 关系型数据库 MySQL
SQL如何对不同表的数据进行更新
本文介绍了如何将表A的Col1数据更新到表B的Col1中,分别提供了Microsoft SQL和MySQL的实现方法,并探讨了多表合并后更新的优化方式,如使用MERGE语句提升效率。适用于数据库数据同步与批量更新场景。
|
3月前
|
SQL DataWorks 数据管理
SQL血缘分析实战!数据人必会的3大救命场景
1. 开源工具:Apache Atlas(元数据管理)、Spline(血缘追踪) 2. 企业级方案:阿里DataWorks血缘分析、腾讯云CDW血缘引擎 3. 自研技巧:在ETL脚本中植入版本水印,用注释记录业务逻辑变更 📌 重点总结:
|
4月前
|
SQL 数据挖掘 关系型数据库
【SQL 周周练】一千条数据需要做一天,怎么用 SQL 处理电表数据(如何动态构造自然月)
题目来自于某位发帖人在某 Excel 论坛的求助,他需要将电表缴费数据按照缴费区间拆开后再按月份汇总。当时用手工处理数据,自称一千条数据就需要处理一天。我将这个问题转化为 SQL 题目。
171 12
|
4月前
|
SQL 数据采集 资源调度
【SQL 周周练】爬取短视频发现数据缺失,如何用 SQL 填充
爬虫爬取抖音和快手的短视频数据时,如果遇到数据缺失的情况,如何使用 SQL 语句完成数据的补全。
119 5
|
6月前
|
SQL 分布式计算 资源调度
Dataphin功能Tips系列(48)-如何根据Hive SQL/Spark SQL的任务优先级指定YARN资源队列
如何根据Hive SQL/Spark SQL的任务优先级指定YARN资源队列
205 4