即席查询之presto

即席查询之presto

简介

​ presto是一个开源的分布式sql查询引擎，数据量支持GB到PB，主要用来处理秒级查询的场景。

​ 注意：虽然presto可以解析sql，但它不是一个标准的数据库。不是mysql、oracle的代替品，也不能用来处理在线事务（OLTP）。

架构

presto由一个coordinator和多个worker组成。
1、由客户端提交查询，从presto命令行cli（command line interface命令行界面）提交到coordinator。

2、coordinator解析查询计划，然后把任务分发给worker执行。

3、worker负责执行任务和处理数据。

4、catalog表示数据源，一个catalog包含schema和connector。

5、connector是适配器，用于presto和数据源（如hive、redis）的连接，类似于jdbc。

6、schema类似于mysql中数据库，table类似于mysql中的表。

7、coordinator是负责从worker获取结果并返回最终结果给client。

优缺点

优点：基于内存运算，减少了硬盘io，计算更快；能够连接多个数据源，跨数据源连表查，如从hive查询大量网站记录访问，然后从mysql中匹配出设备信息。

缺点：链表查询可能会产生大量的临时数据，因此速度会变慢，反而hive此时会更擅长。

性能

impala性能稍领先与presto，但presto在数据源支持上非常丰富，包括hive、图数据库、传统关系型数据库、redis等。

presto安装

官网：https://prestodb.io/

使用docker安装（2个G谨慎安装）

安装docker

sudo yum install -y yum-utils
# 安装`yum-utils`软件包（提供`yum-config-manager` 实用程序）

sudo yum-config-manager --add-repo https://download.docker.com/linux/centos/docker-ce.repo
# 并设置稳定的存储库。

sudo yum install -y docker-ce docker-ce-cli containerd.io
# 新版本docker叫docker-ce

sudo systemctl start docker
# 启动docker

sudo docker run hello-world
# 通过运行`hello-world` 镜像来验证是否正确安装了Docker Engine

docker创建一个docker组，但没创建这个用户，可以将普通用户注册到docker组中继承操作docker的权限。

注意：下面的操作可能需要root权限。

使用docker安装presto

docker run -p 8080:8080 --name presto ahanaio/prestodb-sandbox
# 启动presto服务器并公开端口，第一次启动需要下载，再次提示2G大小，谨慎安装

docker exec -it presto  presto-cli
# 启动presto命令行界面

presto> show catalogs;
# 测试：显示catalogs中的内容。

到这，presto就安装完成了！这是官网首选的安装方式，除了比较大，安装的确非常简单，并且直接支持docker管理。

使用安装包安装

安装presto Server

安装：

# 下载安装包
wget https://repo1.maven.org/maven2/com/facebook/presto/presto-server/0.196/presto-server-0.196.tar.gz
# 将压缩包解压到/opt/module
tar -zxvf presto-server-0.196.tar.gz -C /opt/module/
# 切换到/opt/module/presto
cd /opt/module/presto
# 改名：有利于管理，比如发生版本升级，只需要简单替换即可
mv presto-server-0.196/ presto
# 创建presto保存数据的目录
mkdir data
# 创建presto配置文件mul
mkdir etc
# 创建etc/jvm.config
vim etc/jvm.config
-server
-Xmx16G
-XX:+UseG1GC
-XX:G1HeapRegionSize=32M
-XX:+UseGCOverheadLimit
-XX:+ExplicitGCInvokesConcurrent
-XX:+HeapDumpOnOutOfMemoryError
-XX:+ExitOnOutOfMemoryError

# 创建catalog，presto将数据源抽象为catalog，有hive、mysql、kafka等
mkdir catalog
# 配置支持hive的数据源
vim hive.properties
connector.name=hive-bigdata1
hive.metastore.uri=thrift://bigdata201:9083
# 将presto分发到bigdata201、bigdata202
xsync presto
# 在每台机器上创建node配置文件
# bigdata201
vim /opt/module/presto/etc/node.properties
node.environment=production
node.id=ffffffff-ffff-ffff-ffff-ffffffffffff
node.data-dir=/opt/module/presto/data
# bigdata202
vim /opt/module/presto/etc/node.properties
node.environment=production
node.id=ffffffff-ffff-ffff-ffff-fffffffffffe
node.data-dir=/opt/module/presto/data
# bigdata203
vim /opt/module/presto/etc/node.properties
node.environment=production
node.id=ffffffff-ffff-ffff-ffff-fffffffffffd
node.data-dir=/opt/module/presto/data
# presto是由一个coordinator节点和多个worker节点组成。在bigdata201配置coordinator，在其它节点配置worker
# bigdata201
vim /opt/module/presto/etc/config.properties
coordinator=true
node-scheduler.include-coordinator=false
http-server.http.port=8881
query.max-memory=50GB
discovery-server.enabled=true
discovery.uri=http://bigdata201:8881
# 在bigdata201、bigdata202
vim /opt/module/presto/etc/config.properties
coordinator=false
http-server.http.port=8881
query.max-memory=50GB
discovery.uri=http://bigdata201:8881

# 启动hive metastore，开放hive的元数据
nohup bin/hive --service metastore >/dev/null 2>&1 &
# 分别在三台机器上启动presto server
# 前台启动，控制台日志
bin/launcher run
# 后台启动
bin/launcher start

安装presto client

# 下载presto client jar包
wget https://repo1.maven.org/maven2/com/facebook/presto/presto-cli/0.196/presto-cli-0.196-executable.jar
# 改名
mv presto-cli-0.196-executable.jar  prestocli
# 将prestocli移动到/opt/module/presto下面
mv prestocli /opt/module/presto/
# 修改权限
chmod +x prestocli
# 启动prestocli
./prestocli --server bigdata201:8881 --catalog hive --schema default
# presto支持sql操作

-- 命令行操作
select * from schema.table limit 100

可视化安装

略

presto优化

数据存储

1. 合理设置分区：与hive类似，presto会根据元数据信息读取分区数据，合理的分区能减少presto数据读取量，提升查询效率。
2. 使用列式存储：presto对orc文件读取做了特定优化，因此在hive中创建presto使用的表时，建议采用orc格式存储。相对于parquet，presto对orc支持更好。
3. 使用压缩：数据压缩可以减少节点间数据传输对io带宽压力，对于即席查询需要快速解压，建议采用snappy压缩。

查询sql

只选择使用的字段

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

SELECT time, user, host FROM tbl

过滤条件必须加上分区字段

• 对于有分区的表，where语句中优先使用分区字段进行过滤（在hive中不同分区在不同目录，可以减少查询数据量）。

SELECT time, user, host FROM tbl where acct_day=20171101

group by语句优化

• 合理安排group by语句中字段顺序对性能有一定提升。间group by语句中的字段按照每个字段distinct数据多少进行降序排列。 – 不同uid肯定比不同性别多（性别一般只有2个）

SELECT uid, gender from custom GROUP BY uid, gender

order by时使用limit

• ordery by需要扫描数据到单个worker节点进行排序，导致单个worker需要大量内存。如果时查询topN或者bottomN，使用limit可以减少排序计算和内存压力

SELECT * FROM tbl ORDER BY time LIMIT 100

使用join语句时间大表放在左边

• presto中join的默认算法时broadcast join，即间join左边的表分割到多个worker，然后间join右边的表数据整个复制一份到每个worker中进行计算。如果右边的表太大，则可能包内存溢出错误。

SELECT ... FROM large_table l join small_table s on l.id = s.id

注意事项

字段名反引用

• 如果字段和关键字冲突：mysql对字段加反引号`、presto对字段加双引号分割。

时间函数

• 对于timestamp，mysql可以直接比较，而presto需要加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';

不支持insert overwrite

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

parquet格式

• presto目前支持parquet格式的查询，但不支持insert。

FROM a WHERE t > ‘2017-01-01 00:00:00’;

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

### 不支持insert overwrite

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

### parquet格式

- presto目前支持parquet格式的查询，但不支持insert。