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

1.诡异消失的 null 值
hivesql 中支持常用的算术运算(加减乘除) 、比较运算(>, <, =)、逻辑运算符(in, not in) 以上运算符计算时要特别注意 null 值,在建设数据仓库时,使用HiveHQL处理数据,尤其是进行数据汇总时,如果我们在使用这些运算符的时候,发现数据丢失问题,根源在于使用一些运算符的时候没注意null值的处理,比如在使用<>时处理包含NULL的数据时,会过滤掉这些记录。
注意事项:
- 在使用<>或者!=时,会把为null值的那条记录也过滤掉
- 每行中的列字段相加或相减的时候,如果含有 null 值,则结果为 null
例如:用户表,有四条记录:

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

可以看出来,id为3的这行数据,在查询过程中丢失了。因为这行数据,年龄没有采集到为空,在使用<>时,会把为null值的也过滤掉。
2)我们将小花的薪资家加1000,查询语句:
查询结果为null,因为小花的薪资没采集到,加上null+1000还会变成空,所以我们在对金额处理的时候一定要事先处理比如:
2.聚合函数 null 值的“秘密”
在使用聚合函数时,如果聚合函数在遇见null值的时候,也有注意点:
聚合操作时要注意 :
count(*) 是包含 null 值,统计所有行数的;
count(id) 是不包含id为 null 的值的;
min 求最小值是不包含 null,除非所有值都是 null;
avg 求平均值也是不包含 null(和sum()/count(*)区分)。以上需要特别注意,null 值最容易导致算出错误的结果
3.字符串处理的 “真假空值”
NULL与空字符串('')在Hive中不同:NULL需用IS NULL判断,空字符串用= ''判断。
双保险写法:
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数据的时候会问,我的文件怎么消失了?这就涉及到具体的文件存储位置了:
注意事项:
- 从磁盘上加载文件使用 load data local 表示从本地文件系统加载,文件会拷贝到hdfs上,源文件不会消失。
- 从hdfs上加载文件使用 load data 表示从hdfs文件系统加载,文件会直接移动到hive表相关目录下,注意不是拷贝过去,源文件会消失。
- 如果表是分区表,load 时不指定分区会一定会报错
- 如果加载相同文件名的文件,会被自动重命名
6.left semi join的“限制”
left semi join语句的作用和 in/exists 作用是一样的,具体语法:
注意事项:
- left semi join 的限制是:join 子句中右边的表只能在 on 子句中设置过滤条件,在 where 子句、select 子句或其他地方过滤都不行。
- left semi join 中 on 后面的过滤条件只能是等于号,不能是其他的。
- left semi join 是只传递表的 join key 给 map 阶段,因此left semi join 中最后 select 的结果只许出现左表。
- 因为 left semi join 是 in(keySet) 的关系,遇到右表重复记录,左表会跳过
7.drop 和 truncate的“生死之别”
注意事项:
如果 hdfs 开启了回收站,drop 删除的表数据是可以从回收站恢复的,表结构恢复不了,需要自己重新创建;truncate 清空的表是不进回收站的,所以无法恢复truncate清空的表。
所以 truncate 一定慎用,一旦清空除物理恢复外将无力回天,删数前,想清楚了。
8.分区字段使用的“陷阱”
Hive 的分区信息存储在元数据库(如 MySQL)中,分区裁剪有时候会有失效的情况,比如使用 udf 函数对其进行处理时,可能存在分区裁剪失效的情况,比如:记录的是分区字段的原始值(例如 data_dt='20231001')。当查询条件为 SUBSTR(dt, 1, 6) = '202310' 时,Hive 无法通过元数据直接判断哪些分区的 dt 字段经过截取后等于 '202310',因此只能扫描所有分区目录,再逐条计算过滤条件。
454



被折叠的 条评论
为什么被折叠?



