Presto上使用SQL遇到的一些坑

Vicky 发布于2019-06-21 15:05 / 1913人阅读

摘要：经过一段时间的工作，有了些感觉，决定将遇到的一些坑记录下来。利用子查询，减少读表的次数，尤其是大数据量的表具体做法是，将使用频繁的表作为一个子查询抽离出来，避免多次。

最近换了新工作，在数据处理方面，公司是用Presto连接各个业务部的数据库，直接上SQL处理数据。一度是不是很适应。经过一段时间的工作，有了些感觉，决定将遇到的一些坑记录下来。

Presto的是什么？优势是什么呢？从官方文档中我们了解到

Presto是一个分布式SQL查询引擎，用于查询分布在一个或多个不同数据源中的大数据集。
千万不要以为Presto可以解析SQL，那么Presto就是一个标准的数据库。
Presto被设计为数据仓库和数据分析产品：数据分析、大规模数据聚集和生成报表。这些工作经常通常被认为是线上分析处理操作。

所以说，当公司业务有跨库分析时（一般情况是，业务数据库分布在各个部门），一些数据需要配合其他部门的数据进行关联查询，这个时候可以考虑Presto。但是目前，对于MySQL统计查询在性能上有瓶颈。可考虑将数据按时间段归档到HDFS中，以提高统计效率。

如果需要对业务数据库进行较为实时的统计，而且不需要跨库操作的时候，建议还是直连数据库。

遇到过的问题 如何加快在Presto上的数据统计

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

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

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

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

SELECT id FROM table_1 WHERE condition=1;

对结果进行count计算

返回结果

所以说，对于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。

只查询需要的字段

一定要避免在查询中使用 SELECT *这样的语句，换位思考，如果让你去查询数据是不是告诉你的越具体，工作效率越高呢。

对于我们的数据库而言也是这样，任务越明确，工作效率越高。

对于要查询全部字段的需求也是这样，没有偷懒的捷径，把它们都写出来。

Join查询优化

Join左边尽量放小数据量的表，而且最好是重复关联键少的表

字段名引用

Presto中的字段名引用使用双引号分割，这个要区别于MySQL的反引号`。
当然，你可以不加这个双引号。

时间函数

对于timestamp，需要进行比较的时候，需要添加timestamp关键字，而MySQL中对timestamp可以直接进行比较。

/*MySQL的写法*/
SELECT t FROM a WHERE t > "2017-01-01 00:00:00"; 

/*Presto中的写法*/
SELECT t FROM a WHERE t > timestamp "2017-01-01 00:00:00";

MD5函数的使用

Presto中MD5函数传入的是binary类型，返回的也是binary类型，要对字符串进行MD5操作时，需要转换.

SELECT to_hex(md5(to_utf8("1212")));

不支持INSERT OVERWRITE语法

Presto中不支持insert overwrite语法，只能先delete，然后insert into。

ORC格式

Presto中对ORC文件格式进行了针对性优化，但在impala中目前不支持ORC格式的表，hive中支持ORC格式的表，所以想用列式存储的时候可以优先考虑ORC格式。

PARQUET格式

Presto目前支持parquet格式，支持查询，但不支持insert。

混合云超融合服务器配置阿里云window服务器的一些坑使用遇到问题布局遇到的问题 php开发遇到的难点

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/10454.html

Presto上使用SQL遇到的一些坑

摘要：经过一段时间的工作，有了些感觉，决定将遇到的一些坑记录下来。利用子查询，减少读表的次数，尤其是大数据量的表具体做法是，将使用频繁的表作为一个子查询抽离出来，避免多次。最近换了新工作，在数据处理方面，公司是用Presto连接各个业务部的数据库，直接上SQL处理数据。一度是不是很适应。经过一段时间的工作，有了些感觉，决定将遇到的一些坑记录下来。 Presto的是什么？优势是什么呢？从官方...

superw 2019-07-01 15:51 评论0 收藏0
SQL on Hadoop在快手大数据平台的实践与优化 | 分享实录

摘要：报表系统系统主要是处理以及常见的报表生成，额外的元数据系统是对外进行服务的。大表优化一个表有大量的子分区，它的过程会与元数据交互，获取所有的分区。 showImg(https://segmentfault.com/img/bVbtqW9?w=1080&h=720); 快手大数据架构工程师钟靓本文是根据快手大数据架构工程师钟靓于 5月18-19日在A2M人工智能与机器学习创新峰会《SQ...

hedge_hog 2019-07-01 16:14 评论0 收藏0
使用Presto SQL一些常见问题总结

摘要：我们还能继续优化，使用的语句进行子查询。解决方法不要用解决方法用注释在查询语句前添加注释，能让查询更快些。但是，如果使用了语句，这个魔术注释将被忽略。查询速度慢, 如何优化? 解决方法1: 避免单节点处理虽然Presto是分布式查询引擎, 但是一些操作是必须在单节点中处理的. 例如: count(distinct x) 考虑使用approx_distinct(x)代替但是需...

wangshijun 2019-07-01 15:52 评论0 收藏0
2018年第19周-Presto概念（搭建过程）

摘要：跟踪每个的活动情况并协调查询语句的执行。是负责执行任务和处理数据。是负责从获取结果并返回最终结果给。例如，一个表的权限定名是，则是表名，是，是。运行启动命令日志在目录下记录服务初始化情况和一些的诊断。 Presto简介不是什么虽然Presto可以解析SQL，但它不是一个标准的数据库。不是MySQL、PostgreSQL或者Oracle的代替品，也不能用来处理在线事务（OLTP）是...

snowLu 2019-05-28 18:43 评论0 收藏0
在工作中常用到的SQL

摘要：前言只有光头才能变强。文本已收录至我的仓库，欢迎最近在公司做了几张报表，还记得刚开始要做报表的时候都快把给忘光了当时在广州休假了个月多，在实习期间也没咋写过，回到公司的第一个需求就是做报表。前言只有光头才能变强。文本已收录至我的GitHub仓库，欢迎Star：https://github.com/ZhongFuCheng3y/3y 最近在公司做了几张报表，还记得刚开始要做报表的时...

acrazing 2019-08-19 10:14 评论0 收藏0