最近在建设数据仓库，处理数据的过程中，经常反复使用hive的HQL语句，尽管HQL和SQL语言有很多相同之处，但也并不是说HQL就能通用SQL的语法。在使用过程中要尤为注意。事情经过是这样的，我在把业务系统数据同步到数仓(数据存储在Hive)中时，在数据汇总层(DWS)，对数据进行汇总处理时，发现有数据丢失的问题，经过排查，发现是在使用 <> 引发的坑。
Hive 中 != 或 <> 致命陷阱
业务场景：把业务数据抽到ODS层(原始数据层)、在DWS层(数据汇总层)，对多张多表中的数据进行汇总操作，目的是为了补全各表的多种维度指标(维表)。
实际操作：因为是在Hive直接使用HQL语句对多表进行Join的关联查询操作，把处理完成的数据写入到提前建好的表中。跑完SQL以后，对结果数据进行验证，发现少了数百万数据，问题极其严重(在实际开发过程中，一定要对结果进行多方面的校验)，开始排查问题。
排查问题：首先是对逻辑进行排查。发现逻辑并无错误，之后分解HQL，把每个SQL过滤条件单独拿出来进行验证，发现问题。 在使用 <> 产生了坑。
问题思考：在数仓建设过程中，因为工作疏忽，忘记了对ODS原始数据层的数据进行处理。因为在把ODS原始数据层的数据同步到到DWS数据汇总层时，并没有经过DWD数据明细层的处理，导致问题出现。
注意：在数仓建设过程，因为业务数据、或日志数据、或其他来源的数据。因为数据往往是很脏乱差的，我们需要对数据进行清洗操作，也就是ETL过程。但是数据仓库有个指标很重要，就是要把原始数据原封不动的同步到ODS层，在DWD层对数据进行简单处理。比如补全数据的操作，对NULL或空值进行补值操作。
对!= 或 <>实操验证
首先，先建一张表，插入数据：
create table if not exists not_eq_temp (
id int comment 'id',
age int comment '年龄'，
name string comment '姓名',
sex string comment '性别',
job string comment '工作')；
insert into table not_eq_temp values(1,22,'小李','男','销售')；
insert into table not_eq_temp values(2,,'小张','男','')；
insert into table not_eq_temp values(3,26,'小丽','女','文员')；
insert into table not_eq_temp values(4,22,'小花','女','行政')；
insert into table not_eq_temp values(5,25,'小王','男','')；
insert into table not_eq_temp values(6,24,'小明','男','销售')；
然后，查询语句：
select id,age,name,sex,job from not_eq_temp where age <> 22
查询结果：
| 3| 26|'小丽'|'女'|'文员'|| 5| 25|'小王'|'男'| ''|| 6| 24|'小明'|'男'|'销售'|
可以看出来，id为4的这行数据，在查询过程中丢失了。因为这行数据，年龄没有采集到，为空，在使用<>时，会把为null值的也过滤掉，这显然不是我们想要的结果。
如何解决使用<>过滤 空值的问题?
方案一
这就需要用到我们前面说的补值操作。在DWD层对缺少或空值的记录进行补值处理。
具体方式：
select id, if(age is null,floor(rand()*100+200),age) AS age, name, sex, jobfrom not_eq_temp
注意：因为这里age是整数，我们使用floor(rand()*100+200) 来对age进行补值操作。这样做的好处是，使用rand()随机函数，有效避免数据倾斜情况的出现。
加200的目的，是为了跟正常年龄进行区别。在后续数据使用中，当我们看到200岁(目前来说没人能活200岁)以上的目标时，就能第一时间知道，这是我们补的值，原始业务数据并没有采集到年龄。
这只是一种情况，大家可以灵活使用。字段类型是字符串或其他类型时，补充对应类型的值就行。千万注意不要补同样的值，最好是随机数。
方案二
如果我们没有进行DWD层的操作，也就是没有补值操作。我们在查询数据的时候，可以使用条件判断避免出现null值被过滤的情况。
具体方式：
select id ,age ,name ,sex ,job from not_eq_temp where coalesce(age,1) <> 22
coalesce的用法，相当于if(expr is null,expr1,expr2)。
当然还有其他很多方式，我们可以在工作中，自己尝试。但是还是建议使用第一种方式，在DWD层对脏数据进行处理，因为这是建设数据仓库过程中很严格的规范要求。数据仓库中，一般dwd层就是用来对ods层数据进行简单处理的，如果不发挥这层的作用，那就有点不合时宜了。
使用不等值!= 或<>需要注意
在使用不等值:<>比较或过滤数据时，需要注意以下多种情况。
先来看看<>语法格式：
语法: A <> B
针对所有基本类型，如果表达式A为NULL，或者表达式B为NULL，返回NULL；如果表达式A与表达式B不相等，则为TRUE；否则为FALSE。
注意：在关系型数据库中，通常SQL的写法中不等于也可以这样写 != 。但在hive中，当一个string类型和int类型在进行比较的时候会查不出来结果。
数字和数字类型：可以用 != 比较；
带引号的数字和数字类型：也可以用!= 比较；
带引号的数字和带引号数字类型：还可以用 != 比较；
字符串和数字类型：不可以用 != 比较；
字符串和数字类型：不可以用 <> 比较；
总而言之，在使用!= 或 <>比较的时候两者的字段类型尽量保持一致。
往期回顾
网友评论