20190529 presto 常用总结

1、常用时间格式转换

select date_parse('20180101','%Y%m%d');
select date_format(date_parse('20180101','%Y%m%d'),'%Y-%m-%d');
-- 日期转时间戳
select cast(to_unixtime(date_parse('20171120','%Y%m%d')) as bigint);
结果：1511107200
-- 时间戳转日期
SELECT date_format(from_unixtime(1540567703),'%Y%m%d');
结果：20181026

2、如何加快在Presto上的数据统计

很多的时候，在Presto上对数据库跨库查询，例如Mysql数据库。这个时候Presto的做法是从MySQL数据库端拉取最基本的数据，然后再去做进一步的处理，例如统计等聚合操作。
举个栗子：

SELECT count(id) FROM table_1 WHERE condition=1;

上面的SQL语句会分为3个步骤进行：

1. Presto发起到Mysql数据库进行查询

   SELECT id FROM table_1 WHERE condition=1;

2. 对结果进行count计算

3. 返回结果

所以说，对于Presto来说，其跨库查询的瓶颈是在数据拉取这个步骤。若要提高数据统计的速度，可考虑把Mysql中相关的数据表定期转移到HDFS中，并转存为高效的列式存储格式ORC。

总的来说，使用presto的mysql连接器用来查询mysql上的数据不是一种合适的方法，除非有什么不能加载到hdfs的数据需要查询，而且使用mysql不能使用到内存加速

3、查询条件中尽量带上分区字段进行过滤

如果数据被规当到HDFS中，并带有分区字段。在每次查询归档表的时候，要带上分区字段作为过滤条件，这样可以加快查询速度。因为有了分区字段作为查询条件，就能帮助Presto避免全区扫描，减少Presto需要扫描的HDFS的文件数。

4、多多使用WITH语句

使用Presto分析统计数据时，可考虑把多次查询合并为一次查询，用Presto提供的子查询完成。
这点和我们熟知的MySQL的使用不是很一样。
例如：

WITH subquery_1 AS (
    SELECT a1, a2, a3 
    FROM Table_1 
    WHERE a3 between 20180101 and 20180131
),               /*子查询subquery_1,注意：多个子查询需要用逗号分隔*/
subquery_2 AS (
    SELECT b1, b2, b3
    FROM Table_2
    WHERE b3 between 20180101 and 20180131
)                /*最后一个子查询后不要带逗号，不然会报错。*/        
SELECT 
    subquery_1.a1, subquery_1.a2, 
    subquery_2.b1, subquery_2.b2
FROM subquery_1
    JOIN subquery_2
    ON subquery_1.a3 = subquery_2.b3;

5、refer

Presto上使用SQL遇到的一些坑