【数据库】Presto学习之路

一、Presto简介

【1】Presto概念

Presto是一个开源的分布式SQL查询引擎，适用于交互式分析查询，数据量支持GB到PB字节

Presto的设计和编写完全是为了解决像Facebook这样规模的商业数据仓库的交互式分析和处理速度的问题

注意：虽然Presto可以解析SQL，但它不是一个标准的数据库，不能像数据库一样存储数据，所以不是MySQL、Oracle的代替品，因此也不能用来处理在线事务（OLTP）

【2】Presto应用场景

Presto支持在线数据查询，包括Hive，关系数据库（MySQL、Oracle）以及专有数据存储

一条Presto查询可以将多个数据源的数据进行合并，可以跨越整个组织进行分析

Presto主要用来处理响应时间小于1秒到几分钟的场景

【3】Presto架构

Presto是一个运行在多台服务器上的分布式系统

完整安装包括一个Coordinator和多个Worker。由客户端提交查询，从Presto命令行CLI提交到Coordinator。Coordinator进行解析，分析并执行查询计划，然后分发处理队列到Worker

Presto有两类服务器：Coordinator 和 Worker

• 1）Coordinator
  Coordinator服务器是用来解析语句，执行计划分析和管理Presto的Worker结点。Presto安装必须有一个Coordinator和多个Worker。如果用于开发环境和测试，则一个Presto实例可以同时担任这两个角色
  Coordinator跟踪每个Work的活动情况并协调查询语句的执行。Coordinator为每个查询建立模型，模型包含多个Stage，每个Stage再转为Task分发到不同的Worker上执行。
  Coordinator与Worker、Client通信是通过REST API

• 2）Worker
  Worker是负责执行任务和处理数据。Worker从Connector获取数据。Worker之间会交换中间数据。Coordinator是负责从Worker获取结果并返回最终结果给Client。
  当Worker启动时，会广播自己去发现 Coordinator，并告知 Coordinator它是可用，随时可以接受Task
  Worker与Coordinator、Worker通信是通过REST API

• 3）数据源
  贯穿全文，你会看到一些术语：Connector、Catelog、Schema和Table。这些是Presto特定的数据源

  • （1）Connector
    Connector是适配器，用于Presto和数据源（如Hive、RDBMS）的连接。你可以认为类似JDBC那样，但却是Presto的SPI的实现，使用标准的API来与不同的数据源交互
    Presto有几个内建Connector：JMX的Connector、System Connector（用于访问内建的System table）、Hive的Connector、TPCH（用于TPC-H基准数据）。还有很多第三方的Connector，所以Presto可以访问不同数据源的数据
    每个Catalog都有一个特定的Connector。如果你使用catelog配置文件，你会发现每个文件都必须包含connector.name属性，用于指定catelog管理器（创建特定的Connector使用）。一个或多个catelog用同样的connector是访问同样的数据库。例如，你有两个Hive集群。你可以在一个Presto集群上配置两个catelog，两个catelog都是用Hive Connector，从而达到可以查询两个Hive集群

  • （2）Catelog
    一个Catelog包含Schema和Connector。例如，你配置JMX的catelog，通过JXM Connector访问JXM信息。当你执行一条SQL语句时，可以同时运行在多个catelog。
    Presto处理table时，是通过表的完全限定（fully-qualified）名来找到catelog。例如，一个表的全限定名是hive.test_data.test，则test是表名，test_data是schema，hive是catelog，Catelog的定义文件是在Presto的配置目录中

  • （3）Schema
    Schema是用于组织table。把catelog好schema结合在一起来包含一组的表。当通过Presto访问hive或Mysq时，一个schema会同时转为hive和mysql的同等概念

  • （4）Table
    Table跟关系型的表定义一样，但数据和表的映射是交给Connector

【4】Presto数据模型

1）Presto采取三层表结构：

• Catalog：对应某一类数据源，例如Hive的数据，或MySql的数据
• Schema：对应数据库中的库
• Table：对应数据库中的表

2）Presto的存储单元包括：

Page：多行数据的集合，包含多个列的数据，内部仅提供逻辑行，实际以列式存储

Block：一列数据，根据不同类型的数据，通常采取不同的编码方式，了解这些编码方式，有助于自己的存储系统对接presto

3）不同类型的Block：

• （1）Array类型Block，应用于固定宽度的类型，例如int，long，double。block由两部分组成：
  boolean valueIsNull[]表示每一行是否有值。
  T values[] 每一行的具体值。
• （2）可变宽度的Block，应用于String类数据，由三部分信息组成
  Slice：所有行的数据拼接起来的字符串。
  int offsets[]：每一行数据的起始便宜位置。每一行的长度等于下一行的起始便宜减去当前行的起始便宜。
  boolean valueIsNull[] 表示某一行是否有值。如果有某一行无值，那么这一行的便宜量等于上一行的偏移量。
• （3）固定宽度的String类型的block，所有行的数据拼接成一长串Slice，每一行的长度固定。
• （4）字典block：对于某些列，distinct值较少，适合使用字典保存。主要有两部分组成：
  字典，可以是任意一种类型的block(甚至可以嵌套一个字典block)，block中的每一行按照顺序排序编号。
  int ids[]表示每一行数据对应的value在字典中的编号。在查找时，首先找到某一行的id，然后到字典中获取真实的值

【5】Presto优缺点

Presto中SQL运行过程：MapReduce vs Presto

使用内存计算，减少与硬盘交互

优点

1）Presto与Hive对比，都能够处理PB级别的海量数据分析，但Presto是基于内存运算，减少没必要的硬盘IO，所以更快
2）能够连接多个数据源，跨数据源连表查，如从Hive查询大量网站访问记录，然后从Mysql中匹配出设备信息
3）部署也比Hive简单，因为Hive是基于HDFS的，需要先部署HDFS

缺点

1）虽然能够处理PB级别的海量数据分析，但不是代表Presto把PB级别都放在内存中计算的。而是根据场景，如count，avg等聚合运算，是边读数据边计算，再清内存，再读数据再计算，这种耗的内存并不高。但是连表查，就可能产生大量的临时数据，因此速度会变慢，反而Hive此时会更擅长

2）为了达到实时查询，可能会想到用它直连MySql来操作查询，这效率并不会提升，瓶颈依然在MySql，此时还引入网络瓶颈，所以会比原本直接操作数据库要慢

二、Presto SQL

【1】GREATEST 和 LEAST

这两个函数不是SQL标准函数，它们是Presto特有的函数，参数中不能有Null值，否则获取的值为Null

语法：

greatest(value1, value2, ..., valueN) 
返回提供的最大值

least(value1, value2, ..., valueN)
返回提供的最小值

【2】ALL, ANY 和SOME

语法：

SELECT 'hello' = ANY (VALUES 'hello', 'world') -- true
SELECT 21 < ALL (VALUES 19, 20, 21) -- false
SELECT 42 >= SOME (SELECT 41 UNION ALL SELECT 42 UNION ALL SELECT 43)-- true

【3】COALESCE

语法：

coalesce(value1, value2, ..., valueN) 
返回参数列表中的第一个非空 value 
注意：如果参数列表全部为null，在presto中则返回null，在hive中会报错 ERROR Error while compiling statement: FAILED: RuntimeException Hive internal error: conversion of void to void not supported yet.

【4】NULLIF

语法：

nullif(value1, value2)

如果 value1 与 value2 相等，返回空，否则返回 value1 

【4】format_datetime

语法：

select format_datetime(时间戳,'yyyy-MM-dd')
时间戳 转 时间 

三、Presto优化

【1】数据存储

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

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

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

【2】查询SQL优化

1）只选择使用必要的字段
由于采用列式存储，选择需要的字段可加快字段的读取、减少数据量。避免采用 * 读取所有字段。

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

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

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

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

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

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

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%'

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

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

【3】利用子查询，减少读表的次数，尤其是大数据量的表

具体做法是，将使用频繁的表作为一个子查询抽离出来，避免多次read

【4】只查询需要的字段

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

【5】Join查询优化

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

【6】字段名引用

Presto中的字段名引用使用双引号分割，这个要区别于MySQL的反引号

【7】时间函数

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