一、坑的背景
今天有同事反映,他的一个sql,在Hive里查不出结果,而在Spark里查出结果,非常诡异。于是拿他的SQL来实验:
select *
from table_a a
inner JOIN
(
SELECT *
FROM
(
SELECT trim(sn) sn
,trim(bn) AS bn
,row_number () over (PARTITION BY sn ORDER BY sn DESC) rank
FROM table_b
) tmp
WHERE tmp.rank = 1
) b
ON trim(a.i) = trim(b.sn)
inner join table_c c on b.bn=c.bn
where a.jhdate = 'null' and date_add(a.fgdate,1)<c.btime
limit 100;
二、定位问题
分别在Hive里和在Spark里执行,发现真的如同事所说,Hive里查不出数据,而Spark里能查出数据。
起初怀疑是jhdate=‘null'的条件,在hive与spark里的判空的语法不同导致的,遂改为:
where a.jhdate is null and date_add(a.fgdate,1)<c.btime
新的SQL在Hive里依然出不出数据。于是想将table_a表里的数据导出来,人肉看一下jhdate里是不是有脏数据导致hive计算出错,检索不出数据。结果一查table_a表有380多万数据,这个方法不现实。于是对table_a的jhdate字段进行groupby,结果傻眼了,的确该字段就是两种取值,一种是日期(如:2022-03-18),另一种就是字符串的“null”。所以a.jhdate='null'的条件没毛病。
思考片刻,既然不是这个where条件出的问题,那就是另外一个条件出的问题。于是就对date_add(a.fgdate,1) < c.bttime这个条件进行验证测试。由于涉及到了table_a与table_c,中间需要table_b做关联查询,所以此时可以从结果出发进行推导。将spark里能查到的数据,分别在a、b、c三张表里查出来
select jhdate, fgdate
from table_a
where imei='60****DV'
+-------------+-------------+
| jhdate | fgdate |
+-------------+-------------+
| null | 2021-12-13 |
+-------------+-------------+
select sn, bn
from table_b
where sn='60****DV'
+----------------+-------------------+
| sn | bn |
+----------------+-------------------+
| 60****DV | td****vh |
+----------------+-------------------+
select btime
from table_c
where btime='td****vh';
+------------------------+
| btime |
+------------------------+
| 2021-12-09 16:18:39.0 |
| 2021-12-14 20:30:52.0 |
+------------------------+
结果一目了然,问题就出在 fgdate的“date_add('2021-12-13',1)”与btime的“2021-12-14 22:30:52.0”的比较里了!Spark认为条件是true的,而Hive认为是false!这明显是Hive的坑,返回true才是我们期望的结果。
三、解决方案
问题找到了,接下来怎么改呢?由于没有源码,只能猜个大概,是hive在执行date_add之后,隐式地将日期字段类型转为字符串类型,与字符串类型的btime做比较,在隐式转换过程中出现了偏差。于是,我们可以利用concat,手动地将fgdate后面的时、分、秒也补全,这样在比较大小的时候,两边都是字符串了,就会按照字符串的比较方式进行比较:
select concat(date_add('2021-12-13',1),' 00:00:00.0') < '2021-12-14 20:30:52.0'
这样,无论hive还是spark,返回的结果都是true了。要不是业务偶然发现这个坑,都不知道数据不准的问题会持续多久。
=