Hive中使用 != 或 <>符号进行条件过滤时踩坑记录

处理数据的过程中,经常反复使用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类型在进行比较的时候会查不出来结果。

数字和数字类型:可以用 != 比较;

带引号的数字和数字类型:也可以用!= 比较;

带引号的数字和带引号数字类型:还可以用 != 比较;

字符串和数字类型:不可以用 != 比较;

字符串和数字类型:不可以用 <> 比较;

总而言之,在使用!= 或 <>比较的时候两者的字段类型尽量保持一致。

©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 216,039评论 6 498
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 92,223评论 3 392
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 161,916评论 0 351
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 58,009评论 1 291
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 67,030评论 6 388
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 51,011评论 1 295
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 39,934评论 3 416
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 38,754评论 0 271
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 45,202评论 1 309
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,433评论 2 331
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,590评论 1 346
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 35,321评论 5 342
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 40,917评论 3 325
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,568评论 0 21
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,738评论 1 268
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 47,583评论 2 368
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 44,482评论 2 352

推荐阅读更多精彩内容