数仓4.0之即席查询

雨点打透心脏的1/2处 2023-09-28 21:07 35阅读 0赞

**目录**

一：Presto

1.1 Presto简介

1.1.1 Presto概念

1.1.2 Presto架构

1.1.3 Presto优缺点

1.1.4 Presto、Impala性能比较

1.2 Presto安装

1.2.1 Presto Server安装

1.2.2 Presto命令行Client安装

1.2.3 Presto可视化Client安装

1.3 Presto优化之数据存储

1.3.1 合理设置分区

1.3.2 使用列式存储

1.3.3 使用压缩

1.4 Presto优化之查询SQL

1.4.1 只选择使用的字段

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

1.4.3 Group By语句优化

1.4.4 Order by时使用Limit

1.4.5 使用Join语句时将大表放在左边

1.5 注意事项

1.5.1 字段名引用

1.5.2 时间函数

1.5.3 不支持INSERT OVERWRITE语法

1.5.4 PARQUET格式

二：Kylin

2.1 Kylin简介

2.1.1 Kylin 定义

2.1.2 Kylin架构

2.1.3 Kylin特点

--------------------

# 一：Presto #

## **1****.1** **Presto****简介** ##

### **1****.1****.1****Presto****概念** ###

> Presto是一个开源的**分布式SQL查询引擎**，数据量支持GB到PB字节，主要用来处理**秒级查询**的场景。
> 
> 注意：虽然Presto可以解析SQL，但它不是一个标准的数据库。不是MySQL、Oracle的代替品，也不能用来处理在线事务（OLTP）

### **1.1.2** **Presto架构** ###

Presto是由一个Coordinator和多个Worker组成。

![16f0bf48af2e4dabbe5a9cb0c8395cf7.png][]

### 1.1.3 Presto优缺点 ###

1）优点：

1.Presto基于内存运算，减少了硬盘IO，计算更快。

2.能够连接多个数据源，跨数据源连表查，如从Hive查询大量网站访问记录，然后从MYSQL中匹配出设备信息。

2）缺点：

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

### **1****.1.4** **Presto、Impala性能比较** ###

测试结论：Impala性能稍领先于Presto，但是Presto在数据源支持上非常丰富，包括Hive、图数据库、传统关系型数据库、Redis等。

## **1****.****2****Presto****安装** ##

### **1.2.1** **Presto** **Server****安装** ###

1.将presto-server-0.196.tar.gz导入hadoop102的/opt/software目录下，并解压到/opt/module目录

tar -zxvf presto-server-0.196.tar.gz -C /opt/module/

2.修改名称为presto

mv presto-server-0.196/ presto

3.进入到/opt/module/presto目录，并创建存储数据文件夹

mkdir data

4.进入到/opt/module/presto目录，并创建存储配置文件文件夹

mkdir etc

5.配置在/opt/module/presto/etc目录下添加jvm.config配置文件

添加如下内容
    -server
    -Xmx16G
    -XX:+UseG1GC
    -XX:G1HeapRegionSize=32M
    -XX:+UseGCOverheadLimit
    -XX:+ExplicitGCInvokesConcurrent
    -XX:+HeapDumpOnOutOfMemoryError
    -XX:+ExitOnOutOfMemoryError

6.Presto可以支持多个数据源，在Presto里面叫catalog，这里我们配置支持Hive的数据源，配置一个Hive的catalog

[axing@hadoop102 etc]$ mkdir catalog
    [axing@hadoop102 catalog]$ vim hive.properties 
    
    添加如下内容
    connector.name=hive-hadoop2
    hive.metastore.uri=thrift://hadoop102:9083

7.将hadoop102上的presto分发到hadoop103、hadoop104

xsync presto

8.分发之后，分别进入hadoop102、hadoop103、hadoop104三台主机的/opt/module/presto/etc的路径。配置node属性，node id每个节点都不一样。

[atguigu@hadoop102 etc]$vim node.properties
    node.environment=production
    node.id=ffffffff-ffff-ffff-ffff-ffffffffffff
    node.data-dir=/opt/module/presto/data
    
    [atguigu@hadoop103 etc]$vim node.properties
    node.environment=production
    node.id=ffffffff-ffff-ffff-ffff-fffffffffffe
    node.data-dir=/opt/module/presto/data
    
    [atguigu@hadoop104 etc]$vim node.properties
    node.environment=production
    node.id=ffffffff-ffff-ffff-ffff-fffffffffffd
    node.data-dir=/opt/module/presto/data

9.Presto是由一个coordinator节点和多个worker节点组成。在hadoop102上配置成coordinator，在hadoop103、hadoop104上配置为worker。

（1）hadoop102上配置coordinator节点
    [atguigu@hadoop102 etc]$ vim 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://hadoop102:8881
    
    （2）hadoop103、hadoop104上配置worker节点
    
    [atguigu@hadoop103 etc]$ vim config.properties
    添加内容如下
    
    coordinator=false
    http-server.http.port=8881
    query.max-memory=50GB
    discovery.uri=http://hadoop102:8881
    
    [atguigu@hadoop104 etc]$ vim config.properties
    添加内容如下
    
    coordinator=false
    http-server.http.port=8881
    query.max-memory=50GB
    discovery.uri=http://hadoop102:8881

10.在hadoop102的/opt/module/hive目录下，启动Hive Metastore

[axing@hadoop102 hive]$
    nohup bin/hive --service metastore >/dev/null 2>&1 &

11.分别在hadoop102、hadoop103、hadoop104上启动Presto Server

（1）前台启动Presto，控制台显示日志
    [axing@hadoop102 presto]$ bin/launcher run
    [axing@hadoop103 presto]$ bin/launcher run
    [axing@hadoop104 presto]$ bin/launcher run
    （2）后台启动Presto
    [axing@hadoop102 presto]$ bin/launcher start
    [axing@hadoop103 presto]$ bin/launcher start
    [axing@hadoop104 presto]$ bin/launcher start

12.日志查看路径/opt/module/presto/data/var/log

### **1.2.2** **Presto命令****行Client****安装** ###

1.将presto-cli-0.196-executable.jar上传到hadoop102的/opt/module/presto文件夹下

2.修改文件名称

[axing@hadoop102 presto]$ mv presto-cli-0.196-executable.jar  prestocli

3.增加执行权限

4.启动prestocli

./prestocli --server hadoop102:8881 --catalog hive --schema default

5.Presto命令行操作

Presto的命令行操作，相当于Hive命令行操作。每个表必须要加上schema。
    例如：
    select * from schema.table limit 100

### **1.2.3 Presto****可视化Client****安装** ###

1）将yanagishima-18.0.zip上传到hadoop102的/opt/module目录

2）解压缩yanagishima

[axing@hadoop102 module]$ unzip yanagishima-18.0.zip
    cd yanagishima-18.0

3）进入到/opt/module/yanagishima-18.0/conf文件夹，编写yanagishima.properties配置

[axing@hadoop102 conf]$ vim yanagishima.properties
    
    添加如下内容
    jetty.port=7080
    presto.datasources=atguigu-presto
    presto.coordinator.server.atguigu-presto=http://hadoop102:8881
    catalog.atguigu-presto=hive
    schema.atguigu-presto=default
    sql.query.engines=presto

4）在/opt/module/yanagishima-18.0路径下启动yanagishima

[axing@hadoop102 yanagishima-18.0]$
    nohup bin/yanagishima-start.sh >y.log 2>&1 &

5）启动web页面

http://hadoop102:7080

看到界面，进行查询了。

6）查看表结构

![0cb4729a26f84842844ec5aa92ce8c12.png][]

这里有个Tree View，可以查看所有表的结构，包括Schema、表、字段等。

比如执行select \* from hive.dw\_weather.tmp\_news\_click limit 10，这个句子里Hive这个词可以删掉，是上面配置的Catalog

![5c117dbb71fe43e4971420e4b077be37.png][]

每个表后面都有个复制键，点一下会复制完整的表名，然后再上面框里面输入sql语句，ctrl+enter键执行显示结果

![a0fea7651f1942b4b1b9dc32b9005f59.png][]

## **1****.3** **Presto优化之****数据存储** ##

### **1.3.1** **合理设置分区** ###

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

### **1.3.2** **使用列式存储** ###

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

### **1.3.3** **使用压缩** ###

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

## **1.4 Presto优化之****查询SQL** ##

### **1.4.1** **只选择使用的字段** ###

> 由于采用列式存储，选择需要的字段可加快字段的读取、减少数据量。避免采用\*读取所有字段。
> 
> \[GOOD\]: SELECT time, user, host FROM tbl
> 
> \[BAD\]: SELECT \* FROM tbl

### **1.4.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

### **1.4.3** **Group By语句优化** ###

> 合理安排Group by语句中字段顺序对性能有一定提升。将Group By语句中字段按照每个字段distinct数据多少进行降序排列。
> 
> \[GOOD\]: SELECT GROUP BY uid, gender
> 
> \[BAD\]: SELECT GROUP BY gender, uid

### **1.4.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

### **1.4.5** **使用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

## **1.5** **注意事项** ##

### **1.5****.****1** **字段名引用** ###

> 避免和关键字冲突：MySQL对字段加反引号**\`、**Presto对字段加双引号分割
> 
> 当然，如果字段名称不是关键字，可以不加这个双引号。

### **1.5.2** **时间函数** ###

对于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';

### **1.5.3** **不支持INSERT OVERWRITE语法** ###

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

### **1.5.4** **PARQUET格式** ###

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

# **二：Kylin** #

## **2****.1 Kylin****简介** ##

### **2.1.1****Kylin****定义** ###

> Apache Kylin是一个开源的分布式分析引擎，提供Hadoop/Spark之上的SQL查询接口及多维分析（OLAP）能力以支持超大规模数据，最初由eBay Inc开发并贡献至开源社区。它能在亚秒内查询巨大的Hive表。

### **2.1.2****Kylin架构** ###

![7532e4d4a8004703af7ec42e6018925f.png][]

1）REST Server

REST Server是一套面向应用程序开发的入口点，旨在实现针对Kylin平台的应用开发工作。 此类应用程序可以提供查询、获取结果、触发cube构建任务、获取元数据以及获取用户权限等等。另外可以通过Restful接口实现SQL查询。

2）查询引擎（Query Engine）

当cube准备就绪后，查询引擎就能够获取并解析用户查询。它随后会与系统中的其它组件进行交互，从而向用户返回对应的结果。

3）路由器（Routing）

在最初设计时曾考虑过将Kylin不能执行的查询引导去Hive中继续执行，但在实践后发现Hive与Kylin的速度差异过大，导致用户无法对查询的速度有一致的期望，很可能大多数查询几秒内就返回结果了，而有些查询则要等几分钟到几十分钟，因此体验非常糟糕。最后这个路由功能在发行版中默认关闭。

4）元数据管理工具（Metadata）

Kylin是一款元数据驱动型应用程序。元数据管理工具是一大关键性组件，用于对保存在Kylin当中的所有元数据进行管理，其中包括最为重要的cube元数据。其它全部组件的正常运作都需以元数据管理工具为基础。 Kylin的元数据存储在hbase中。

5）任务引擎（Cube Build Engine）

这套引擎的设计目的在于处理所有离线任务，其中包括shell脚本、Java API以及Map Reduce任务等等。任务引擎对Kylin当中的全部任务加以管理与协调，从而确保每一项任务都能得到切实执行并解决其间出现的故障。

### **2.****1.****3****Kylin特点** ###

Kylin的主要特点包括支持SQL接口、支持超大规模数据集、亚秒级响应、可伸缩性、高吞吐率、BI工具集成等。

1）标准SQL接口：Kylin是以标准的SQL作为对外服务的接口。

2）支持超大数据集：Kylin对于大数据的支撑能力可能是目前所有技术中最为领先的。早在2015年eBay的生产环境中就能支持百亿记录的秒级查询，之后在移动的应用场景中又有了千亿记录秒级查询的案例。

3）亚秒级响应：Kylin拥有优异的查询相应速度，这点得益于预计算，很多复杂的计算，比如连接、聚合，在离线的预计算过程中就已经完成，这大大降低了查询时刻所需的计算量，提高了响应速度。

4）可伸缩性和高吞吐率：单节点Kylin可实现每秒70个查询，还可以搭建Kylin的集群。

5）BI工具集成

Kylin可以与现有的BI工具集成，具体包括如下内容。

ODBC：与Tableau、Excel、PowerBI等工具集成

JDBC：与Saiku、BIRT等Java工具集成

RestAPI：与JavaScript、Web网页集成

Kylin开发团队还贡献了**Zepplin**的插件，也可以使用Zepplin来访问Kylin服务。

--------------------

这里省去了kylin的安装步骤。

--------------------

##  ##

[16f0bf48af2e4dabbe5a9cb0c8395cf7.png]: https://img-blog.csdnimg.cn/16f0bf48af2e4dabbe5a9cb0c8395cf7.png
[0cb4729a26f84842844ec5aa92ce8c12.png]: https://img-blog.csdnimg.cn/0cb4729a26f84842844ec5aa92ce8c12.png
[5c117dbb71fe43e4971420e4b077be37.png]: https://img-blog.csdnimg.cn/5c117dbb71fe43e4971420e4b077be37.png
[a0fea7651f1942b4b1b9dc32b9005f59.png]: https://img-blog.csdnimg.cn/a0fea7651f1942b4b1b9dc32b9005f59.png
[7532e4d4a8004703af7ec42e6018925f.png]: https://img-blog.csdnimg.cn/7532e4d4a8004703af7ec42e6018925f.png