处理数据的过程中,经常反复使用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 values(1,22,'小李','男','销售')(
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,'小明','男','销售');
SQL语句:
select id,age,name,sex,job from not_eq_temp where age <> 22
查询结果:
| 3| 26|'小丽'|'女'|'文员'|
| 5| 25|'小王'|'男'| ''|
| 6| 24|'小明'|'男'|'销售'|
如何解决使用<>过滤 空值的问题?
方案一:补值
(1)通过使用 nvl (age, 0)
(2) if(age is null, floor(rand()*100+200), age) AS age
注意:因为这里age是整数,我们使用floor(rand()*100+200) 来对age进行补值操作。这样做的好处是,使用rand()随机函数,有效避免数据倾斜情况的出现。
加200的目的,是为了跟正常年龄进行区别。在后续数据使用中,当我们看到200岁(目前来说没人能活200岁)以上的目标时,就能第一时间知道,这是我们补的值,原始业务数据并没有采集到年龄。
这只是一种情况,大家可以灵活使用。字段类型是字符串或其他类型时,补充对应类型的值就行。千万注意不要补同样的值,最好是随机数。
(4)coalesce(age,1)
(3)字段值为null若为脏数据,可剔除。
方案二:
若仍保留null值,判断条件可以为:
select id,age,name,sex,job from not_eq_temp where (age <> 22 or age is null )
另外:使用不等值!= 或<>需要注意的
在使用不等值:<>比较或过滤数据时,需要注意以下多种情况。
先来看看<>语法格式:
语法: A <> B
针对所有基本类型,如果表达式A为NULL,或者表达式B为NULL,返回NULL;如果表达式A与表达式B不相等,则为TRUE;否则为FALSE。
注意:在关系型数据库中,通常SQL的写法中不等于也可以这样写 != 。但在hive中,当一个string类型和int类型在进行比较的时候会查不出来结果。
数字和数字类型:可以用 != 比较;
带引号的数字和数字类型:也可以用!= 比较;
带引号的数字和带引号数字类型:还可以用 != 比较;
字符串和数字类型:不可以用 != 比较;
字符串和数字类型:不可以用 <> 比较;
总而言之,在使用!= 或 <>比较的时候两者的字段类型尽量保持一致。