(部分转载)Presto上使用SQL遇到的一些坑

最新推荐文章于 2022-10-01 10:16:31 发布

weixin_34008805

最新推荐文章于 2022-10-01 10:16:31 发布

阅读量1.7k

点赞数

文章标签：数据库大数据 python

原文链接：https://my.oschina.net/u/3204727/blog/1923777

版权

2019独角兽企业重金招聘Python工程师标准>>>

遇到过的问题：

很多的时候，在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。

所以定时归档是一个很好的选择，这里还要注意，在归档的时候我们要选择一个归档字段，如果是按日归档，我们可以用日期作为这个字段的值，采用yyyyMMdd的形式，例如20180123.

一般创建归档数据库的SQL语句如下：

CREATE TABLE IF NOT EXISTS table_1 (
id INTEGER,
........
partition_date INTEGER
)WITH ( format = 'ORC', partitioned_by = ARRAY['partition_date'] );

查看创建的库结构：

SHOW CREATE TABLE table_1; /*Only Presto*/

带有分区的表创建完成之后，每天只要更新分区字段partition_date就可以了，聪明的Presto就能将数据放置到规划好的分区了。

如果要查看一个数据表的分区字段是什么，可以下面的语句：

SHOW PARTITIONS FROM table_1 /*Only Presto*/

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

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

多多使用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;

利用子查询，减少读表的次数，尤其是大数据量的表
具体做法是，将使用频繁的表作为一个子查询抽离出来，避免多次read。

转载于:https://my.oschina.net/u/3204727/blog/1923777