永州网,内容丰富有趣,生活中的好帮手!
永州网 > 知识 > 正文

数据仓库,Hive中使用 != 或 <>; 符号进行条件过滤时的坑

时间:2021-07-06 15:47:45

相关推荐

最近在建设数据仓库,处理数据的过程中,经常反复使用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或空值进行补值操作。

对!= 或 <>实操验证

首先,先建一张表,插入数据:

createtableifnotexistsnot_eq_tempvalues(1,22,"小李","男","销售")(

idintcomment"id",

ageintcomment"年龄",

namestringcomment"姓名",

sexstringcomment"性别",

jobstringcomment"工作"

);

insertintotablenot_eq_tempvalues(1,22,"小李","男","销售");

insertintotablenot_eq_tempvalues(2,,"小张","男","");

数据仓库,Hive中使用 != 或 &amp;lt;&amp;gt;; 符号进行条件过滤时的坑

insertintotablenot_eq_tempvalues(3,26,"小丽","女","文员");

insertintotablenot_eq_tempvalues(4,22,"小花","女","行政");

insertintotablenot_eq_tempvalues(5,25,"小王","男","");

insertintotablenot_eq_tempvalues(6,24,"小明","男","销售");

然后,查询语句:

selectid,age,name,sex,jobfromnot_eq_tempwhereage<>22

查询结果:

|3|26|"小丽"|"女"|"文员"|

|5|25|"小王"|"男"|""|

|6|24|"小明"|"男"|"销售"|可以看出来,id为4的这行数据,在查询过程中丢失了。因为这行数据,年龄没有采集到,为空,在使用<>时,会把为null值的也过滤掉,这显然不是我们想要的结果。

如何解决使用<>过滤 空值的问题?

方案一

这就需要用到我们前面说的补值操作。在DWD层对缺少或空值的记录进行补值处理。

具体方式:

select

id,

if(ageisnull,floor(rand()*100+200),age)ASage,

name,

sex,

job

from

not_eq_temp

注意:因为这里age是整数,我们使用floor(rand()*100+200) 来对age进行补值操作。这样做的好处是,使用rand()随机函数,有效避免数据倾斜情况的出现。

加200的目的,是为了跟正常年龄进行区别。在后续数据使用中,当我们看到200岁(目前来说没人能活200岁)以上的目标时,就能第一时间知道,这是我们补的值,原始业务数据并没有采集到年龄。

这只是一种情况,大家可以灵活使用。字段类型是字符串或其他类型时,补充对应类型的值就行。千万注意不要补同样的值,最好是随机数。

方案二

如果我们没有进行DWD层的操作,也就是没有补值操作。我们在查询数据的时候,可以使用条件判断避免出现null值被过滤的情况。

具体方式:

selectid,age,name,sex,jobfromnot_eq_tempwherecoalesce(age,1)<>22coalesce的用法,相当于if(expr is null,expr1,expr2)。

当然还有其他很多方式,我们可以在工作中,自己尝试。但是还是建议使用第一种方式,在DWD层对脏数据进行处理,因为这是建设数据仓库过程中很严格的规范要求。数据仓库中,一般dwd层就是用来对ods层数据进行简单处理的,如果不发挥这层的作用,那就有点不合时宜了。

使用不等值!= 或<>需要注意

在使用不等值:<>比较或过滤数据时,需要注意以下多种情况。

先来看看<>语法格式:

语法: A <> B

针对所有基本类型,如果表达式A为NULL,或者表达式B为NULL,返回NULL;如果表达式A与表达式B不相等,则为TRUE;否则为FALSE。

注意:在关系型数据库中,通常SQL的写法中不等于也可以这样写 != 。但在hive中,当一个string类型和int类型在进行比较的时候会查不出来结果。

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

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

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

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

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

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

收集不易,本文《数据仓库,Hive中使用 != 或 &amp;lt;&amp;gt;; 符号进行条件过滤时的坑》知识如果对你有帮助,请点赞收藏并留下你的评论。

本内容不代表本网观点和政治立场,如有侵犯你的权益请联系我们处理。
网友评论
网友评论仅供其表达个人看法,并不表明网站立场。
相关阅读
利用正则表达式去除HTML标签的方法

利用正则表达式去除HTML标签的方法

..."\"''tbnr]|[^7])*?7|w+)|.{0})|s)*?(/s*)?\",@\"([])[s]+\",@\"&(quot|#34);\",@\"&(amp|#38);\",@\"&(lt|#60);\",@\"&(gt|#62);\",@\"&(nbsp|#160);\",@\"&(iexcl|#161);\",@\"&(cent|#162);\",@\"&(pound|#163);\",@\"&(copy|#169);\",@\"&#(d+);\",@\"--\",@\"!...

2024-02-04

种植猕猴桃小树多年 却无法生长的原因及3种解决方法

种植猕猴桃小树多年 却无法生长的原因及3种解决方法

...疼一般小老树具有的特点>>>每一年的生长量很少<<<跟着朋友一起栽植的小树,别人管理得好的,大部分挂果卖果实不愁的。反而是种了几年,果树一直年生长量很少,每年只长一点点,尽管果树年纪不小了,还是很...

2024-03-21

学会制作美味马芬的简单步骤

学会制作美味马芬的简单步骤

...漂一起摇摆起来!做法都先告诉你哦,榴莲斑斓蛋糕卷<br>40克斑斓叶洗净、剪碎,放入榨汁机。加入90克水打成糊。然后过滤叶渣保留45克斑斓汁备用。<br><br>鸡蛋将蛋黄和蛋清分离分别装在无水无油的盆里。蛋清打...

2024-03-02

仿寓意草&gt;&gt;---牙痛治效

仿寓意草&gt;&gt;---牙痛治效

牙痛,大肠,无几,玉女煎,阳明,下牙床,痛不可忍,刘桐,三钱,加升麻,景岳,得安,得大,西茵陈,赵义,阳明胃,六味全,作痛,少阴,天明,大便,对症,拜服,月余,柴胡,武生,槐花,潜阳,牙龈,面额

2000-04-05

佛说:唯有熟悉佛经的人才算缘的觉者

佛说:唯有熟悉佛经的人才算缘的觉者

...行的目的,实现内心的平静与觉悟。作者 赵建林【一】<佛说>:学佛人不能有妄想佛在心中心中有佛,佛是心中的意念。学佛是精神上的一种升华,是在尘世中一种修行。佛祖再伟大,也不能叫某个人不劳而获。就没有天下...

2024-02-07