注: 本文使用的环境是:Spark:2.2, Hive: 1.2.1
最近在编写一个SQL的过程中遇到了这样一个陷阱:为了便于工作,SQL的编写是通过一个SQL的IDE环境以Hive JDBC驱动的方式连接到HIVE上去执行的,SQL在HIVE上执行正常,有结果数据返回。但是SQL开发好在程序执行时通过Spark-SQL来运行的,SQL可以成功执行,没有报出任何语法错误,但是没有任何结果数据!在经过仔细查询后,发现问题出现在where条件中对理日期/时间字段的比较上。 以下where字句:
where creation_date >&#61; start_time and creation_date < end_time ....
其中creation_date是date类型&#xff0c;start_time和end_time是timestamp类型。虽然是两种时间类型&#xff0c;但是确实是可以直接比较大小的&#xff0c;人们会默认的转换规则是&#xff1a;对于date类型&#xff0c;如果要与timestamp类型进行比较&#xff0c;会默认在时间部分上补齐"00:00:00.00000000", 但是如果在不显式地指定这种转换下&#xff0c;默认的两个类型间的比较结果如下&#xff1a;
SQL | HIVE执行结果 | SPARK-SQL执行结果 |
---|
select ‘2019-01-01’ <‘2019-01-01 00:00:00’; | true | true |
select ‘2019-01-01’ &#61; ‘2019-01-01 00:00:00’; | false | false |
select ‘2019-01-01’ <‘2019-01-01 00:00:00’; | false | false |
date类型的’2019-01-01’一定是比timestamp类型的’2019-01-01 00:00:00’要小。虽然这有些怪异&#xff0c;但在hive和spark-sql上这一比较是一致的
SQL | HIVE执行结果 | SPARK-SQL执行结果 |
---|
select cast(‘2019-01-01’ as date) false | true | |
select cast(‘2019-01-01’ as date) &#61; cast(‘2019-01-01 00:00:00’ as timestamp); | true | false |
select cast(‘2019-01-01’ as date) > cast(‘2019-01-01 00:00:00’ as timestamp); | false | false |
这比较让人更加费解&#xff0c;本质上对于’2019-01-01’和’2019-01-01 00:00:00’两个字面量的类型转换都是它们本来的类型&#xff0c;对于Spark-SQL来说&#xff0c;它保持了和前一个版本以纯字面量比较一致的结果&#xff0c;但Hive的比较结果发生了变化&#xff0c;变成了“默认的那种”结果&#xff0c;这是有问题的。如果我们再一步的比较’2019-01-01’和cast(‘2019-01-01’ as date)&#xff0c;那么对于Hive的行为就更加难以理解了&#xff1a;
select &#39;2019-01-01&#39; &#61; cast(&#39;2019-01-01&#39; as date); -> hive: true, spark-sql: true
总之&#xff0c;Hive在处理date类型与timestamp类型的比较上是有问题的! 结果不符合人们的预期&#xff0c;所以最好的作法是将date类型转化为timestamp之后再进行比较&#xff01; 像下面这样&#xff1a;
SQL | HIVE执行结果 | SPARK-SQL执行结果 |
---|
select cast(‘2019-01-01’ as timestamp) false | false | |
select cast(‘2019-01-01’ as timestamp) &#61; cast(‘2019-01-01 00:00:00’ as timestamp); | true | true |
select cast(‘2019-01-01’ as timestamp) > cast(‘2019-01-01 00:00:00’ as timestamp); | false | false |