zhaojp0 2019-06-26
Presto的是什么?优势是什么呢?从官方文档中我们了解到
Presto是一个分布式SQL查询引擎,用于查询分布在一个或多个不同数据源中的大数据集。所以说,当公司业务有跨库分析时(一般情况是,业务数据库分布在各个部门),一些数据需要配合其他部门的数据进行关联查询,这个时候可以考虑Presto。但是目前,对于MySQL统计查询在性能上有瓶颈。可考虑将数据按时间段归档到HDFS中,以提高统计效率。
如果需要对业务数据库进行较为实时的统计,而且不需要跨库操作的时候,建议还是直连数据库。
很多的时候,在Presto上对数据库跨库查询,例如Mysql数据库。这个时候Presto的做法是从MySQL数据库端拉取最基本的数据,然后再去做进一步的处理,例如统计等聚合操作。
举个栗子:
SELECT count(id) FROM table_1 WHERE condition=1;
上面的SQL语句会分为3个步骤进行:
SELECT id FROM table_1 WHERE condition=1;
所以说,对于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的文件数。
使用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左边尽量放小数据量的表,而且最好是重复关联键少的表
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';
Presto中MD5函数传入的是binary类型,返回的也是binary类型,要对字符串进行MD5操作时,需要转换.
SELECT to_hex(md5(to_utf8('1212')));
Presto中不支持insert overwrite语法,只能先delete,然后insert into。
Presto中对ORC文件格式进行了针对性优化,但在impala中目前不支持ORC格式的表,hive中支持ORC格式的表,所以想用列式存储的时候可以优先考虑ORC格式。
Presto目前支持parquet格式,支持查询,但不支持insert。