当前位置:   article > 正文

Hive与SparkSQL语法差异_sparksql和hivesql语法有什么区别

sparksql和hivesql语法有什么区别

一、相同函数差异

1、Spark运行时用到的hash函数,与Hive的哈希算法不同,如果使用hash(),结果和Hive的hash()会有差异

2、Hive和SparkSQL使用grouping sets生成的GROUPING_ID不一致

3、regexp_extract未匹配上的话,在HIVE里返回是null,但在Spark里返回是空字符

示例:

regexp_extract(‘00000000’, '^(0{1,})?([0-9a-zA-Z]+)?', 2)

HIVE返回null,Spark返回空字符。

4、SparkSQL中row_number的over中不能省略sort by 或order by

示例:

row_number()over(partition by 字段1,字段2 order by 字段3as row_num

row_number()over(distribute by 字段1,字段2 sort by 字段3as row_num

其中order by或sort by不能省略。

5.grouping_id()函数生成的数据不同

6.reflect()函数中,如果入参有非法数据或者null,hive会返回null,而spark会抛出异常

7、to_date()函数有些值的情况不一致

示例:Spark中to_date('2017-12-13-15') 为null,而Hive中则为2017-12-13,因为Hive使用的SimpleDateFormat.parse(xxx,"yyyy-MM-dd"),只会解析到pattern的位置,会忽略-15的字符。

8、Spark中字符串小数比较结果可能与预期有差别

示例:SELECT '0.1' = 0会返回true,因为此时0.1会被转换为int,相当于(CAST(‘0.1’ AS INT) = 0)。

9.parse_url()函数中,如果http URL中有不符合格式的脏子串(如空格等),Hive会去掉脏子串且匹配成功,而Spark的匹配方式更严格,要求URL字符串本身是符合正常格式的,否则会匹配不到返回空

10、datediff()函数中,比如0000-00-00执行datediff两者会存在差异

示例:

datediff(CURRENT_DATE, '0000-00-00')

结果:(1)hive: 737986      (2)spark: NULL

11、unix_timestamp()函数中,对于24点Spark认为是非法的返回NULL,而Hive任务是正常的

示例:

unix_timestamp(concat('2020-06-01', ' 24:00:00'))

结果: (1)hive:1591027200    (2)spark: NULL

12、date_sub()、date_add()函数中,当月或者日是00时Hive仍然会返回一个日期,但是Spark会返回NULL

二、仅Hive支持

1、SparkSQL关联on条件不支持函数rand()

示例:

on (concat(rand(), t1.xxx)) = t2.xxx

解决方案:在on语句之前利用子查询将rand()处理好,如“concat(rand(), t1.xxx) as bitrary”,然后放到on语句后进行关联,如“on t1.bitrary = t2.xxx” 这样会遇到报错:Error in query: nondeterministic expressions are only allowed in Project, Filter, Aggregate or Window

2、创建临时表时,Spark不支持直接赋值null

示例:

  1. create table tmp.tmp_xxx
  2. stored as orc as
  3. select pv_id,
  4.        null as apply_tp_cd
  5.   from sourceTable

解决方案:在SparkSQL中将null强转成期望类型,比如字段类型如果是string,则使用"cast (null as string)"强制转换类型

3、SparkSQL无法读取字段类型为void的表

示例:null as xxx,或create table xxx stored as orc as select xxx语句建表时,from和where等条件后的源头数据中正好某个字段等值全为null

解决方案:此情况一般在Hive建临时表时字段值全为null时出现,Hive会将该字段类型识别为void,而SparkSQL不支持void类型,按照第2条将表重建即可解决

4、SparkSQL中如果表达式没有指定别名,SparkSQL会将整个表达式作为别名。如果表达式中包含特殊符号(如逗号),则CTAS建表会失败

示例:

create table test_bigdata using parquet as select nvl(dummy, '1'from dual;

上述查询中,SparkSQL会把nvl(dummy, '1')直接作为别名建表,导致建表失败,会报错:Error in query: Cannot create a table having a column whose name contains commas in Hive metastore.

解决方案:在复杂表达式后指定别名:

  1. create table test_bigdata
  2. using parquet as
  3. select nvl(dummy, '1'as dummy from dual;

5、SparkSQL没有regexp udf

示例:

  1. where 1 regexp 1         --sparksql 和hive均ok
  2. where 1 rlike 1          --sparksql 和hive均ok
  3. where regexp(1,1)        --sparksql没有此udf,hive有

6、SparkSQL的from_utc_timestamp()中参数只支持timestamp、date、string类型,而Hive支持bigint等各种类型的输入

示例:

SELECT from_utc_timestamp(1L, 'GMT');

解决方案:通过自带的udf组合使用,例如:

  1. select concat(from_unixtime(floor(cast(ts as bigint)/1000))
  2.       ,regexp_extract(format_number(cast(ts as bigint)/1000, 3), '(.*?)(\\.[0-9][0-9][0-9])', 2))
  3.  from ( select 1527061637123as ts) a

7、个别Avro表有字段定义成uniontype,Spark解析字段定义会报错:Cannot recognize hive type string: xxxx

8、有些UDF用到了SimpleDateFormat,Spark在执行时会涉及多线程,Hive没这个问题,SimpleDateFormat非线程安全

解决方案:(1)修改UDF代码,把static相关变量去掉换成非static的变量

                   (2)把SimpleDateFormat设置成ThreadLocal的: java - Date Conversion with ThreadLocal - Stack Overflowhttps://stackoverflow.com/questions/18589986/date-conversion-with-threadlocal

                   (3)使用 joda-time来转换日期时间

三、仅Spark支持

1、SparkSQL允许在join on条件中使用or等不等值关联语句,Hive中不允许,只能用等值关联

示例:

  1. select t1.xx, t2.xxx from students t1
  2. left join class t2
  3.        on t1.classId >= t2.id
  4.        or t1.classname = t2.name
  5. order by t1.id;

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/喵喵爱编程/article/detail/767046
推荐阅读
相关标签
  

闽ICP备14008679号