Q&A - Presto

1.1.1. Q: 读取array的时候，index为0的时候为啥读取不出来，例如array[0] ?
因为Presto中数组的下标是从1开始

1.1.2. Q: 如何直接比较timestamp字段？
如果直接写

select * from acc_tt_repair_order_1h where start_time > ‘2020-01-01 00:00’
会报错

Presto中需要按如下写法

select * from acc_tt_repair_order_1h where start_time > timestamp ‘2020-01-01 00:00’
1.1.3. Q: 如何从Map结构中获取指定key？
可以直接用[]，例如

SELECT name_to_age_map[‘Bob’] AS bob_age;
但是[]操作有个问题，就是如果key在Map中不存在，会报错。

因此比较建议采用element_at函数，该函数在key不存在的时候，直接返回NULL

1.1.4. Q: 使用count(distinct) 非常慢，怎么办？
Presto本身是分布式查询引擎，快也是因为分布式处理，但是count(distinct)操作会导致最终操作会单节点处理。

考虑使用approx_distinct(x)代替。

但是需要注意这个函数有大约2.3%的标准误差, 如果需要精确统计的情况, 那只能够忍受count(distinct)的慢了。

1.1.5. Q: order by 比较慢
Presto对数据排序是作用在单节点上的。

如果要排序的数据量超过百万行, 要谨慎考虑. 如果非要排序,尽量将排序的字段减少些.

1.1.6. Q: 使用UNION 在处理合并两个数据集时，为什么合并之后的数据集条数少于两个数据集条数和
UNION有个功能是: 如果两条记录一样, 会只保留一条记录(去重).

如果不考虑去重的情况, 请使用UNION ALL

1.1.7. Q: 如果like语句比较慢，有没有更高效的方式？
Presto的查询优化器不能改善许多LIKE语句使用的地方, 导致这样的语句查询速度慢， 可以将多个like的条件放到regexp_like()

例如

SELECT
…
FROM
access
WHERE
method LIKE ‘%GET%’ OR
method LIKE ‘%POST%’ OR
method LIKE ‘%PUT%’ OR
method LIKE ‘%DELETE%’

上面的语句能用regexp_like函数优化成一句

SELECT
…
FROM
access
WHERE
regexp_like(method, ‘GET|POST|PUT|DELETE’)
1.1.8. Q: 如何优化Join性能？
尽量让JOIN的条件简单，最好是ON后面的比较表达式两边必涉及计算。

例如

SELECT a.date, b.name FROM
left_table a
JOIN right_table b
ON a.date = CAST((b.year * 10000 + b.month * 100 + b.day) as VARCHAR)
上面的SQL语句的JOIN性能不高，因为JION条件包含了表达式计算。我们可以通过子查询的形式来优化上面的语句。

SELECT a.date, b.name FROM
left_table a
JOIN (
SELECT
CAST((b.year * 10000 + b.month * 100 + b.day) as VARCHAR) date, # generate join key
name
FROM right_table
) b
ON a.date = b.date # Simple equi-join
上面的语句，就是直接比较两个VARCHAR的值，这样会比比较一个VARCHAR和一个表达式结果的性能高。

我们还能继续优化，使用Presto的WITH语句进行子查询。

WITH b AS (
SELECT
CAST((b.year * 10000 + b.month * 100 + b.day) as VARCHAR) date, # generate join key
name
FROM right_table
)
SELECT a.date, b.name FROM
left_table a
JOIN b
ON a.date = b.date
1.1.9. Q: 查询语句特别复杂，不方便维护和理解，有没有更好的书写方式？
如果你的查询语句非常复杂或者有多层嵌套的子查询，请试着用WITH语句将子查询分离出来。

例如

SELECT a, b, c FROM (
SELECT a, MAX(b) AS b, MIN© AS c FROM tbl GROUP BY a
) tbl_alias
可以被重写为线面的形式

WITH tbl_alias AS (SELECT a, MAX(b) AS b, MIN© AS c FROM tbl GROUP BY a)
SELECT a, b, c FROM tbl_alias
同样，也可以将各个步骤的子查询通过WITH语句罗列出来，子查询之间用“,”分割。

WITH tbl1 AS (SELECT a, MAX(b) AS b, MIN© AS c FROM tbl GROUP BY a),
tbl2 AS (SELECT a, AVG(d) AS d FROM another_tbl GROUP BY a)
SELECT tbl1., tbl2. FROM tbl1 JOIN tbl2 ON tbl1.a = tbl2.a
1.1.10. Q: 用GROUP BY语句时，GROUP BY的目标比较复杂，是否可以简化书写？
在Presto SQL中，GROUP BY语句需要与SELECT语句中的表达式保持一致，不然会提示语法错误。

例如：

SELECT TD_TIME_FORMAT(time, ‘yyyy-MM-dd HH’, ‘PDT’) hour, count(*) cnt
FROM my_table
GROUP BY TD_TIME_FORMAT(time, ‘yyyy-MM-dd HH’, ‘PDT’)
上面的SQL语句的GROUP BY部分可以用GROUP BY 1，2，3 …来表示

SELECT TD_TIME_FORMAT(time, ‘yyyy-MM-dd HH’, ‘PDT’) hour, count(*) cnt
FROM my_table
GROUP BY 1
Note: 这些数字是从1开始的，有别于程序要思维从0开始。

1.1.11. Q: Exceeded max (local) memory 错误
Presto会跟踪每个查询的内存使用情况.可用内存的多少是根据你的查询计划变动的,所以在大多数情况下可以从写查询语句来达到优化内存使用的目的.

下面列出来的就是内存密集型的语句块:

DISTINCT
UNION
ORDER BY
GROUP BY (许多字段的情况)
JOIN (各种JOIN)

尽量减少使用distinct，如果使用建议用approx_distinct
尽量用union all 代替union
尽量避免order by
减少group by 中字段数量
用大表去join小表
下面这种用小数据表去JOIN大数据表的查询会极度消耗内存.

SELECT * FROM small_table, large_table
WHERE small_table.id = large_table.id
Presto 会默认执行广播式的JOIN操作,它会将左表拆分到几个工作节点上, 然后发送整个右表分别到已拆分好的处理左表的工作节点上. 如果右表非常大就会超出工作节点的内存限制,进而出错.
所以需要用小表JOIN大表

SELECT * FROM large_table, small_table
WHERE large_table.id = small_table.id
如果左表和右表都比较大怎么办?

修改配置distributed-joins-enabled (presto version >=0.196)
在每次查询开始使用distributed_join的session选项
– set session distributed_join = ‘true’
SELECT * FROM large_table, large_table1
WHERE large_table1.id = large_table.id
核心点就是使用distributed join. Presto的这种配置类型会将左表和右表同时以join key的hash value为分区字段进行分区. 所以即使右表也是大表,也会被拆分.

缺点是会增加很多网络数据传输, 所以会比broadcast join的效率慢.

1.1.12. Q: 查询生成的大量数据优化的问题

建议不要直接select *
用result_output_redirect=‘true’ 注释
在查询语句前添加注释（result_output_redirect=‘true’），能让查询更快些。

– set session result_output_redirect=‘true’
select a, b, c, d FROM my_table
上面的语句能让Presto用并行的方式生成查询结果，能跳过在Presto协调器进行JSON转换的过程。

Note: 但是，如果使用了ORDER BY语句，这个魔术注释将被忽略。

   3. 如果执行select字段的查询，但是执行的非常慢，建议先改成select count来统计一下结果的行数。Presto本身并不适合把大量数据返回给查询方。

1.1.13. Q: 如何拼接字符串
使用||运算符

SELECT 'hello ’ || ‘presto’
1.1.14. Q: 如何在字段包含NULL的情况下 添加default value
用COALESCE(v1,v2,…)函数

– This retuns ‘N/A’ if name value is null
SELECT COALESCE(name, ‘N/A’) FROM table1
1.1.15. Q: 为什么我在不同地方查出来的同一个时间字段的值不一样？
时间类型(date或者timestamp)本身并非字符串类型，所以最终你看到的代表时间的字符串取决于你的查询工具的行为，有可能取决于本地时区。为了避免时区带来的问题，请采用presto内置函数将时间转成字符串或者数值。例如：

没有指定时区时的查询（请避免使用这样的SQL）

select evaluation_time
from tmp.acc_regular_test_drive_evaluation_1d
where dt=‘20200310’ and evaluation_time between date(‘2020-03-10’) and date(‘2020-03-11’)
指定时区, 将返回的日期字段按iso8601标准显示。

select to_iso8601(evaluation_time) as eval_time
from tmp.acc_regular_test_drive_evaluation_1d
where dt=‘20200310’ and evaluation_time between from_iso8601_timestamp(‘2020-03-10T00:00:00+08:00’) and from_iso8601_timestamp(‘2020-03-11T00:00:00+08:00’)
或者指定时区, 将返回的日期字段转为unix epoch数值。

select to_unixtime(evaluation_time) as eval_time
from tmp.acc_regular_test_drive_evaluation_1d
where dt=‘20200310’ and evaluation_time between from_iso8601_timestamp(‘2020-03-10T00:00:00+08:00’) and from_iso8601_timestamp(‘2020-03-11T00:00:00+08:00’)
其中’2020-03-10T00:00:00+08:00’ 代表北京时间2020年3月10日0点。

其它函数请参见presto官方文档 Date and Time Functions and Operators

1.1.16. Q:current_timestamp在Zeppelin上的结果和Pyhive的结果不一样
比如：执行select to_unixtime(date_trunc(‘day’,current_timestamp))，在zeppelin上用presto查询结果是1587916800， 通过pyhive使用presto查询，结果是1587945600。

这是因为时区的问题，current_timestamp的默认时区跟本地环境相关。因此，需要手动指定当前的时区，比如，修改成 select to_unixtime(date_trunc(‘day’,current_timestamp AT TIME ZONE ‘Asia/Shanghai’))。

在zeppelin时间日期函数指南中 https://prestodb.io/docs/0.217/functions/datetime.html，提到：

1.1.17. Q: 如何format时间字符串
如果要用字符串表示时间，一定要带有时区信息。

在格式化时间字符串时，一定要指定时间字段的时区，不然得到的结果取决于很多因素，无法被信任。

参考zeppelin时间日期函数指南 https://prestodb.io/docs/0.217/functions/datetime.html

format_datetime

format_datetime, 其中使用的format字符串是使用的JodaTime的 DateTimeFormat

假如now()对应的是北京时间2020年5月14日11点12分56秒951毫秒

执行select format_datetime(now() AT TIME ZONE ‘Asia/Shanghai’, ‘yyyy-MM-dd HH:mm:ss.SSS Z’) 或者 select format_datetime(timestamp ‘2020-05-14 03:12:56.951 UTC’ AT TIME ZONE ‘Asia/Shanghai’, ‘yyyy-MM-dd HH:mm:ss.SSS Z’)

结果为 2020-05-14 11:12:56.951 +0800

假如执行的是 select format_datetime(timestamp ‘2020-05-14 03:12:56.951 Asia/Tokyo’ AT TIME ZONE ‘Asia/Shanghai’, ‘yyyy-MM-dd HH:mm:ss.SSS Z’)

结果会是 2020-05-14 02:12:56.951 +0800， 因为Tokyo比北京时间早1个小时。

date_format

select date_format(now() AT TIME ZONE ‘Asia/Shanghai’, ‘%Y-%m-%d %H:%i:%s’)

结果为2020-05-14 11:47:48

to_iso8601

select to_iso8601(now())

结果为 2020-05-14T11:12:56.951+08:00