Presto--常见语法

前言

• 我们的查询引擎有hive、spark、presto，presto查询速度快，但是很多语法和sparksql不兼容，这里记录一下经常用的语法，方便自己使用，也供大家参考
• 官方文档

一、隐式转换

• 这里要特别注意，我数据探查的时候喜欢用presto，但是调度脚本是spark，数据对不上有的时候要看看是不是 where 当中的隐式转换导致的，这里列一下和 hive/spark 的对比~
  • hive隐式转换规则

--Hive/Spark隐式转换
select '07' >= 6 
; -- true (CAST('07' AS DOUBLE) >= CAST(6 AS DOUBLE))
select 'test' <> 1
; -- NULL
select '1' = 1.0
; -- true
 
--Presto隐式转换
select '07' >= 6
; -- false (CAST('07' AS Varchar) >= CAST(6 AS Varchar))
select 'test' <> 1
; -- true
select '1' = 1.0 
; -- ERROR:io.prestosql.spi.PrestoException: Unexpected parameters (varchar(1), decimal(2,1)) for function $operator$equal. Expected: $operator$equal(T, T) T:comparable

• 下图为Hive和Presto的隐式转换规则
  • 蓝色区域是Presto和Hive都支持的类型转换
  • 绿色区域是Presto不支持但是Hive支持的类型转换
  • 红色区域是两者都不支持的类型转换
  • 可以看到，hive的隐式转换更为广泛，而presto尤其在字符类型的隐式转换中更为严格。
  • 举个例子，判断字符串’1’和1.0是否相等，以hive引擎为例
    • 等号两边分别是varchar类型和double类型，下表可以看出两者可以互转。
    • 那这时候应该应用哪条规则呢？通过查询结果为true反推，这里是应用了varchar到double的转换规则，因为如果是double转换为varchar的话，字符串’1’和’1.0’是不相等的。
    • 当表格中的类型可以相互转换时，我们可以用这种查询结果倒推的方法来判断Hive使用了哪一条隐式类型转换规则。
    • 一般的经验是，在涉及数学运算时（关系运算、四则运算、数学函数等），都会将字符串转为数字。

二、时间函数

当日

select current_date ; -- 2024-01-16 

精确到今天的时分秒

select now(); -- 2024-01-16 14:33:23.000 Asia/Shanghai

昨天

SELECT date_add('day', - 1, current_date) ; -- 2024-01-15

上周

SELECT date_add('week', - 1, current_date) ; -- 2024-01-09

上月

select date_add('month',-1,current_date) ;-- 2023-12-16

上季度(3个月前)的日期

SELECT date_add('quarter', - 1, CURRENT_DATE) -- 2023-10-16

当月月初

select date_trunc('month', current_date) ; -- 2024-01-01 

当月月末

SELECT date_add('day',-1,date_add('month', 1 ,date_trunc('month', current_date))) ; --2024-01-31

上月月初

select date_add('month', -1 ,date_trunc('month', current_date)); -- 2023-12-01

上月月末

SELECT date_add('day', -1 ,date_trunc('month', current_date));  -- 2023-12-31

下月月初

select date_add('month', 1 ,date_trunc('month', current_date)); -- 2024-02-01

下月月末

SELECT date_add('day',-1,date_add('month', 2,date_trunc('month', current_date))); --2024-02-29

上年的日期

SELECT date_add('year', -1, CURRENT_DATE); -- 2023-01-16

下年的日期

SELECT date_add('year', 1, CURRENT_DATE); 
-- 2025-01-16

yyyyMMdd格式转化

• 当前时间20240116 转化为2024-01-16

select format_datetime(date_parse('20240116','%Y%m%d'),'yyyy-MM-dd'); 
-- 2024-01-16

时间戳转日期

select from_unixtime(1705386803);  
-- 2024-01-16 14:33:23.000
--加格式
select format_datetime(from_unixtime(1705386803),'yyyy-MM-dd'); 
-- 2024-01-16

日期转时间戳

select cast(to_unixtime(cast('2024-01-16' as date)) as bigint); 
-- 1705363200 不转换 bigint 可能会是科学计数法 1.7053632E9
select cast(to_unixtime(cast(format_datetime(date_parse('20240116','%Y%m%d'),'yyyy-MM-dd') as date)) as bigint); 
-- 1705363200 不转换 bigint 可能会是科学计数法 1.7053632E9

计算两个日期之间的diff

select date_diff('day',cast('2024-01-28' as date),cast('2024-01-16' as date));  
-- -12 

三、其他函数

3.1 collect_set/collect_list

• collect_set()与collect_list()在Presto中无法兼容。
• array_distinct(array_agg(字段)) 替换collect_set()

select 
	aaa
	,array_distinct(array_agg(bbb)) as bbb  -- array_distinct对数组进行去重操作
from (select 1 as aaa,'sd' as bbb  
    union all 
    select 1 as aaa,'sd' as bbb  
    union all 
    select 1 as aaa,'qw' as bbb  
    union all 
    select 2 as aaa,'sd' as bbb  
    ) t
group by aaa
;
-- aaa	   bbb
-- 2	   [sd]	
-- 1	   [sd, qw]	

3.2 lateral VIEW explode

• Hive/Spark query:

SELECT student, score
FROM tests
LATERAL VIEW explode(scores) t AS score
;

• Presto query:
  • Presto支持UNNEST来扩展array和map。文档

SELECT student, score
FROM tests
CROSS JOIN UNNEST(scores) AS t (score)
;