Presto应用实践2

问题导读：
1、Presto的基本使用有哪些？
2、Presto可视化客户端如何安装？
3、Presto的优化方式有哪些？
4、有哪些会踩的坑？

4、Presto 的基本使用
1、 启动 presto 客户端并选择连接的数据源(这里以 hive 为例)

[root@node01 bin]$ ./presto \ 
--server node01:8080 \ 
--catalog hive \(可选) 
--schema test \(可选) 
--user xiaobai (可选)
说明:
– server 指定的是 coordinator 的地址
– catalog 指定的是连接的数据源.(跟配置文件里面的名称一致)
– schema 指定的是连接哪个数据库,这里是 test 数据库
– user 指定的是连接使用哪个用户,这里是xiaobai

2、Presto 命令行操作

#查看所有的数据库 
presto:test> show schemas; 
#查看某个库下的所有表 
presto:test> show tables; 
#查看一条 sql 查询(6 亿多条数据比 hive 快很多) 
presto:test> select count(1) from test.test_hive; 
_col0 
----------- 
620756992

5、Presto可视化客户端的安装
Presto 可视化客户端有多种，这里我们选择使用 yanagishima-20.0 版本
1、将 yanagishima-20.0.zip 安装包上传至 linux121 服务器上
2、解压 yanagishima-20.0.zip 安装包

[root@node01 software]# unzip yanagishima-20.0.zip -d /export/servers/

3、.进入到 /opt/servers/yanagishima-20.0/conf/目录下,修改 yanagishima.properties 配置文件

#指定 jetty 的端口,类似 tomcat 的 web 容器的一个组件
jetty.port=7788
#指定数据源
presto.datasources=presto_test
presto.coordinator.server.presto_test=http://node01:8080
catalog.presto_test=hive
schema.presto_test=ods
sql.query.engines=presto

4、启动 yanagishima

[root@node01 yanagishima-20.0]# nohup bin/yanagishima-start.sh 1>/dev/null 2>&1 &

5、webUI访问界面

通过http://node01:7788/，即可查询到相关信息

6、查看 Presto 表结构，如下图所示：

在“Treeview”页面下可以查看所有表的结构，包括Schema，Table，Column等。
比如，执行

SELECT * FROM hive.company."action" LIMIT 100

    每张表后面都有一个复制图标，单击此图标可以复制完整的表名，然后在上面的文本框中输入 SQL 语句 即可，如图所示：<br />![](https://cdn.nlark.com/yuque/0/2021/jpeg/766178/1618888319279-0b6cc0b1-d0c3-43d9-b98d-66183f30912f.jpeg#clientId=u0237fb5f-e64d-4&from=paste&id=ue4996746&margin=%5Bobject%20Object%5D&originHeight=552&originWidth=1121&originalType=url&status=done&style=none&taskId=u5f84ce58-cff5-4e8e-8cea-640e6b70e9e)<br />   还可以查询列表中其他的表格，比如，想要执行现在已经写好了的SQL语句，SELECT * FROM hive.company."demo01" LIMIT 100，按Ctrl + Enter组合键显示查询结果，如图所示：<br />![](https://cdn.nlark.com/yuque/0/2021/jpeg/766178/1618888332595-830a48a3-b4f7-4632-a0be-67f4e4e4eed9.jpeg#clientId=u0237fb5f-e64d-4&from=paste&id=u25b4e162&margin=%5Bobject%20Object%5D&originHeight=569&originWidth=1114&originalType=url&status=done&style=none&taskId=u99b46f3c-61a4-43bb-a2e5-586981a35c8)<br />当然，我们也可以通过Query List模块查询我们执行过的SQL详细信息，包括State，query ID，Elapsed，Query，Source，User…

更多使用，就不一一剧透介绍，小伙伴们可以自行去研究~

6、Presto的优化
Presto 的优化是一个非常有水平的问题，大致总结下，分如下几个类别：

6.1 数据存储
想要使用 Presto 更高效地查询数据，需要在数据存储方面利用一些优化手段。

6.1.1 合理设置分区
与 Hive 类似，Presto 会根据元数据信息读取分区数据，合理地设置分区能减少 Presto 数据读取量，提升查询性能。

6.1.2 使用 ORC 格式存储
Presto 对 ORC文件 读取进行了特定优化，因此，在 Hive 中创建 Presto 使用的表时，建议采用 ORC 格式存储。相对于 Parquet 格式，Presto 对 ORC 格式支持得更好。

6.1.3 使用压缩
数据压缩可以减少节点间数据传输对 IO 带宽的压力，对于即席查询需要快速解压，建议采用 Snappy压缩。

6.1.4 预先排序
对于已经排序的数据，在查询的数据过滤阶段，ORC格式支持跳过读取不必要的数据。比如对于经常需要过滤的字段可以预先排序。

INSERT INTO table nation_orc partition(p) SELECT * FROM nation SORT BY n_name;

如果需要过滤 n_name 字段，则性能将提升。

SELECT count(*) FROM nation_orc WHERE n_name=’AUSTRALIA’;

6.2 SQL查询
想要使用 Presto更高效地查询数据，需要在编写查询SQL语句方面利用一些优化手段。

6.2.1 只选择需要的字段
由于采用列式存储，所以只选择需要的字段可加快字段的读取速度，减少数据量。避免采用 * 读取所有字段。

[GOOD]: SELECT time,user,host FROM tbl
[BAD]:  SELECT * FROM tbl

6.2.2 过滤条件必须加上分区字段
对于有分区的表，where语句中优先使用分区字段进行过滤。acct_day 是分区字段，visit_time 是具体访问时间。

[GOOD]: SELECT time,user,host FROM tbl where acct_day=20171101
[BAD]:  SELECT * FROM tbl where visit_time=20171101

6.2.3 Group By语句优化
合理安排 Group by语句中字段顺序对性能有一定提升。将 Group By 语句中字段按照每个字段 distinct 数据多少进行降序排列。

[GOOD]: SELECT GROUP BY uid, gender
[BAD]:  SELECT GROUP BY gender, uid

6.2.4 Order by时使用Limit
Order by 需要扫描数据到单个 worker 节点进行排序，导致单个worker需要大量内存。如果是查询 Top N 或者 Bottom N，使用 limit 可减少排序计算和内存压力。

[GOOD]: SELECT * FROM tbl ORDER BY time LIMIT 100
[BAD]:  SELECT * FROM tbl ORDER BY time

6.2.5 使用近似聚合函数
Presto有一些近似聚合函数，对于允许有少量误差的查询场景，使用这些函数对查询性能有大幅提升。比如使用approx_distinct()函数比Count(distinct x)有大概2.3%的误差。

SELECT approx_distinct(user_id) FROM access

6.2.6 用regexp_like代替多个like语句
Presto查询优化器没有对多个 like 语句进行优化，使用regexp_like对性能有较大提升。

[GOOD]
SELECT
  ...
FROM
  access
WHERE
  regexp_like(method, 'GET|POST|PUT|DELETE')
[BAD]
SELECT
  ...
FROM
  access
WHERE
  method LIKE '%GET%' OR
  method LIKE '%POST%' OR
  method LIKE '%PUT%' OR
  method LIKE '%DELETE%'

6.2.7 使用Join语句时将大表放在左边
Presto中 join 的默认算法是broadcast join，即将 join 左边的表分割到多个 worker ，然后将join 右边的表数据整个复制一份发送到每个worker进行计算。如果右边的表数据量太大，则可能会报内存溢出错误。

[GOOD] SELECT ... FROM large_table l join small_table s on l.id = s.id
[BAD] SELECT ... FROM small_table s join large_table l on l.id = s.id

6.2.8 使用Rank函数代替row_number函数来获取Top N
在进行一些分组排序场景时，使用rank函数性能更好

[GOOD]
SELECT checksum(rnk)
FROM (
  SELECT rank() OVER (PARTITION BY l_orderkey, l_partkey ORDER BY l_shipdate DESC) AS rnk
  FROM lineitem
) t
WHERE rnk = 1
[BAD]
SELECT checksum(rnk)
FROM (
  SELECT row_number() OVER (PARTITION BY l_orderkey, l_partkey ORDER BY l_shipdate DESC) AS rnk
  FROM lineitem
) t
WHERE rnk = 1

6.3 注意事项
ORC和Parquet 都支持列式存储，但是ORC对Presto支持更好（Parquet对Impala支持更好）
对于列式存储而言，存储文件为二进制的，对于经常增删字段的表，建议不要使用列式存储（修改文件元数据代价大）。对比数据仓库，dwd层建议不要使用ORC，而dm层则建议使用。

6.4 可能会踩的坑
使用 Presto 需要注意如下几点：
引用：https://segmentfault.com/a/1190000013120454?utm_source=tag-newest

6.4.1 如何加快在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。
所以定时归档是一个很好的选择，这里还要注意，在归档的时候我们要选择一个归档字段，如果是按日归档，我们可以用日期作为这个字段的值，采用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*/

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

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

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

6.4.5 只查询需要的字段
一定要避免在查询中使用 SELECT * 这样的语句，就像只有告诉我们需要查询数据越具体，工作效率越高。
对于我们的数据库而言也是这样，任务越明确，工作效率越高。
对于要查询全部字段的需求也是这样，没有偷懒的捷径，把它们都写出来。

6.4.6 Join查询优化
Join左边尽量放小数据量的表，而且最好是重复关联键少的表。

6.4.7 字段名引用
避免字段名与关键字冲突：MySQL对于关键字冲突的字段名加反引号，Presto 对与关键字冲突的字段名加双引号。当然，如果字段名不与关键字冲突，则可以不加双引号。

6.4.8 时间函数
对于 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';

6.4.9 MD5 函数的使用

Presto 中 MD5 函数传入的 是 binary 类型，返回的也是binary类型，要对字符串进行 MD5操作时，需要转换：
SELECT to_hex(md5(to_utf8(‘1212’)));
复制代码


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

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

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


巨人的肩膀

1、《大数据分析数据仓库项目实战》
2、《大数据技术实战》
3、《大数据私房菜_Presto的安装和使用》
4、 《impala与Presto的性能比较》https://blog.csdn.net/u012551524/article/details/79124532
5、《Presto踩坑指南》https://segmentfault.com/a/1190000013120454?utm_source=tag-newest

小结

本篇内容为大家介绍的是关于从 0 到 1 学习 Presto 的过程，内容算是比较的完整！包括从Presto的简介，安装部署，命令行 Client 的安装，基本使用，可视化客户端的安装与基本使用，以及使用任何一个组件我们都很注重的优化 … 希望大家看完之后能够有所收获！你知道的越多，你不知道的也越多，我是大数据梦想家，我们下一期见！