在大数据分析领域,Hive SQL扮演着至关重要的角色。它为我们提供了便捷的数据查询和处理方式,Hive SQL的语法看似简单,但稍不留神就会掉进“陷阱”。你是否经历过这样的抓狂时刻:跑了一小时的脚本突然报错,发现是字段类型不匹配?查询结果莫名其妙少了一半数据?分析展示报表的数据怎么突然变成空了?今天,就让我们一起来梳理一下这些常见的“坑”,帮助大家在使用Hive SQL时更加得心应手,帮你精准避坑!

小心踩坑!Hive SQL中这些易错点你必须知道_sql

1.诡异消失的 null 值

hivesql 中支持常用的算术运算(加减乘除) 、比较运算(>, <, =)、逻辑运算符(in, not in)  以上运算符计算时要特别注意 null 值,在建设数据仓库时,使用HiveHQL处理数据,尤其是进行数据汇总时,如果我们在使用这些运算符的时候,发现数据丢失问题,根源在于使用一些运算符的时候没注意null值的处理,比如在使用<>时处理包含NULL的数据时,会过滤掉这些记录。

注意事项:

  1. 在使用<>或者!=时,会把为null值的那条记录也过滤掉
  2. 每行中的列字段相加或相减的时候,如果含有 null 值,则结果为 null  

例如:用户表,有四条记录:

小心踩坑!Hive SQL中这些易错点你必须知道_加载_02

create table ds_stu7.user_info_all (
  user_id string,
  name string,
  age int,
  job decimal(10, 2)
) stored as orc;insert overwrite table ds_stu7.user_info_all
select
  1,
  '小李',
  20,
  5005.65
union all
select
  2,
  '小张',
  22,
  7005.65
union all
select
  3,
  '小王',
  null,
  7005.65
union all
select
  4,
  '小花',
  45,
  null;
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.
  • 29.

1)我们查询年龄不为20的所有用户,查询语句:

select * from ds_stu7.user_info_all where age<>20
  • 1.

查询结果:

小心踩坑!Hive SQL中这些易错点你必须知道_hadoop_03

可以看出来,id为3的这行数据,在查询过程中丢失了。因为这行数据,年龄没有采集到为空,在使用<>时,会把为null值的也过滤掉。

2)我们将小花的薪资家加1000,查询语句:

select   job+1000 from ds_stu7.user_info_all where name='小花';
  • 1.

查询结果为null,因为小花的薪资没采集到,加上null+1000还会变成空,所以我们在对金额处理的时候一定要事先处理比如:

select   coalesce(job,0)+1000 from ds_stu7.user_info_all where name='小花';
  • 1.

2.聚合函数 null 值的“秘密”

在使用聚合函数时,如果聚合函数在遇见null值的时候,也有注意点:

聚合操作时要注意 :

count(*) 是包含 null 值,统计所有行数的; 
count(id) 是不包含id为 null 的值的; 
min 求最小值是不包含 null,除非所有值都是 null; 
avg 求平均值也是不包含 null(和sum()/count(*)区分)。以上需要特别注意,null 值最容易导致算出错误的结果

3.字符串处理的 “真假空值”

NULL与空字符串('')在Hive中不同:NULL需用IS NULL判断,空字符串用= ''判断。

双保险写法:

WHERE col IS NOT NULL AND col != ''
  • 1.

4.decimal的精度“黑洞”

hive 除了支持 int,double,string等常用类型,也支持 decimal 类型,用于在数据库中存储精确的数值,常用在表示金额的字段上

注意事项:

如:声明DECIMAL(9,8)时,代表最多有1位整数字,8位小数。小数点前最多只能有1位,如果整数部分超过9位,则这个字段就会变成null,如果整数部分不超过1位。  如果小数部分不足8位,则后面用0补齐8位,如果小数部分超过88位,则超出部分四舍五入。
也可直接写 decimal,后面不指定位数,默认是 decimal(10,0) 整数10位,没有小数。

5.load数据的“隐藏条款”

很多同学在load数据的时候会问,我的文件怎么消失了?这就涉及到具体的文件存储位置了:

从hdfs上加载文件
load data inpath "/home/hewwen8888/data/ch5_emp.txt"  overwrite into table ds_hive.ch5_emp;
从磁盘上加载文件
load data local inpath "/home/hewwen8888/data/ch5_emp.txt"  overwrite into table ds_hive.ch5_emp;
  • 1.
  • 2.
  • 3.
  • 4.

注意事项:

  1. 从磁盘上加载文件使用 load data local 表示从本地文件系统加载,文件会拷贝到hdfs上,源文件不会消失。
  2. 从hdfs上加载文件使用 load data 表示从hdfs文件系统加载,文件会直接移动到hive表相关目录下,注意不是拷贝过去,源文件会消失。
  3. 如果表是分区表,load 时不指定分区会一定会报错  
  4. 如果加载相同文件名的文件,会被自动重命名

6.left semi join的“限制” 

left semi join语句的作用和 in/exists 作用是一样的,具体语法:

SELECT A.* FROM A where id in (select id from B)
SELECT A.* FROM A left semi join B ON A.id=B.id
  • 1.
  • 2.

注意事项:

  1. left semi join 的限制是:join 子句中右边的表只能在 on 子句中设置过滤条件,在 where 子句、select 子句或其他地方过滤都不行。
  2. left semi join 中 on 后面的过滤条件只能是等于号,不能是其他的。
  3. left semi join 是只传递表的 join key 给 map 阶段,因此left semi join 中最后 select 的结果只许出现左表。
  4. 因为 left semi join 是 in(keySet) 的关系,遇到右表重复记录,左表会跳过

7.drop 和 truncate的“生死之别”

删除表操作
drop table emp1;
 
清空表操作
truncate table emp1;
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.

注意事项:

如果 hdfs 开启了回收站,drop 删除的表数据是可以从回收站恢复的,表结构恢复不了,需要自己重新创建;truncate 清空的表是不进回收站的,所以无法恢复truncate清空的表。  
所以 truncate 一定慎用,一旦清空除物理恢复外将无力回天,删数前,想清楚了。

8.分区字段使用的“陷阱”

Hive 的分区信息存储在元数据库(如 MySQL)中,分区裁剪有时候会有失效的情况,比如使用 udf 函数对其进行处理时,可能存在分区裁剪失效的情况,比如:记录的是分区字段的原始值(例如 data_dt='20231001')。当查询条件为 SUBSTR(dt, 1, 6) = '202310' 时,Hive 无法通过元数据直接判断哪些分区的 dt 字段经过截取后等于 '202310',因此只能扫描所有分区目录,再逐条计算过滤条件。

select * from ds_hive.ch9_t_mis_order_d where substr(data_dt,1,4)='2025';
  • 1.
改成:
select * from ds_hive.ch9_t_mis_order_d where data_dt>='2025101' and data_dt<='20251231';
  • 1.
  • 2.