零基礎熟悉 Presto 的概念、安裝、使用及優化

前言

  Presto 作爲現在在企業中流行使用的即席查詢框架，已經在不同的領域得到了越來越多的應用。本期內容，我會從一個初學者的角度，帶着大家從 0 到 1 學習 Presto，希望大家能夠有所收穫！

文章目錄

• 前言

• 1. Presto 簡介

• 1.1 Presto 概念

• 1.2 Presto 應用場景

• 1.3 Presto 架構

• 1.4 Presto 數據模型

• 1.5 Presto 優缺點

• 1.6  Presto、Impala 性能比較

• 1.7 官網地址

• 1.8 Prestodb VS Prestosql(trino)

• 2. Presto 安裝部署

• 2.1 prestosql 版本的選擇

• 2.2 集羣安裝規劃

• 2.3 Presto Server 的安裝

• 2.4 Node Properties 配置

• 2.5 JVM Config 配置

• 2.6 Config Properties 配置

• 2.7 Log Properties 配置

• 2.8 Catalog Properties 配置

• 2.9 分發安裝目錄到集羣中其它節點上

• 2.10 修改 node.id

• 2.11 修改 work 節點的配置信息

• 2.12 啓動服務

• 3、Presto 命令行 Client 的安裝

• 4、Presto 的基本使用

• 5、Presto 可視化客戶端的安裝

• 6、Presto 的優化

• 6.1 數據存儲

• 6.2 SQL 查詢

• 6.3 注意事項

• 6.4 可能會踩的坑

• 巨人的肩膀

• 小結

• 彩蛋

1. Presto 簡介

1. 1 Presto 概念

        Presto 是  Facebook  推出的一個開源的分佈式 SQL 查詢引擎，數據規模可以支持 GB 到 PB 級，主要應用於處理秒級查詢的場景。Presto 的設計和編寫完全是爲了解決像 Facebook 這樣規模的商業數據倉庫的交互式分析和處理速度的問題。

注意： 雖然 Presto 可以解析 SQL，但它不是一個標準的數據庫。不是 MySQL、Oracle 的代替品，也不能用來處理在線事務（OLTP）。

1.2 Presto 應用場景

        Presto 支持在線數據查詢，包括 Hive，關係數據庫（MySQL、Oracle）以及專有數據存儲。一條 Presto 查詢可以將多個數據源的數據進行合併，可以跨越整個組織進行分析。

        Presto 主要用來處理 響應時間小於 1 秒到幾分鐘的場景 。

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 特定的數據源

• 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 集羣。

• 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 的配置目錄中。

• Schema

        Schema 是用於組織 table。把 catelog 和 schema 結合在一起來包含一組的表。當通過 Presto 訪問 hive 或 Mysq 時，一個 schema 會同時轉爲 hive 和 mysql 的同等概念。

• Table

        Table 跟關係型的表定義一樣，但數據和表的映射是交給 Connector。

1.4 Presto 數據模型

        1）Presto 採取三層表結構：

        Catalog：對應某一類數據源，例如 Hive 的數據，或 MySql 的數據

        Schema：對應 MySql 中的數據庫

        Table：對應 MySql 中的表

        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， 然後到字典中獲取真實的值。

1.5 Presto 優缺點

        學習一個新的框架，免不了來探討一下它的優缺點：

        通過下面一張圖，我們來看看 Presto 中 SQL 運行過程：MapReduce vs Presto

        我們可以很明顯地感受到，Presto 使用內存計算，減少與硬盤交互

1.5.1 優點

        1）Presto 與 Hive 對比，都能夠處理 PB 級別的海量數據分析，但 Presto 是基於內存運算，減少沒必要的硬盤 IO，所以更快。

        2）能夠連接多個數據源，跨數據源連表查，如從 Hive 查詢大量網站訪問記錄，然後從 Mysql 中匹配出設備信息。

        3）部署也比 Hive 簡單，因爲 Hive 是基於 HDFS 的，需要先部署 HDFS。

        找了張對比圖，大家感受下：        

1.5.2 缺點

        1）雖然能夠處理 PB 級別的海量數據分析，但不是代表 Presto 把 PB 級別都放在內存中計算的。而是根據場景，如 count，avg 等聚合運算，是邊讀數據邊計算，再清內存，再讀數據再計算，這種耗的內存並不高。但是連表查，就可能產生大量的臨時數據，因此速度會變慢，反而 Hive 此時會更擅長。

        2）爲了達到實時查詢，可能會想到用它直連 MySql 來操作查詢，這效率並不會提升， 瓶頸依然在 MySql，此時還引入網絡瓶頸，所以會比原本直接操作數據庫要慢。

1.6  Presto、Impala 性能比較

        Presto 和 Impala 這兩種典型的內存數據庫之間具體的性能測試比較就不詳細展開敘述，感興趣可以去看這篇鏈接：https://blog.csdn.net/u012551524/article/details/79124532

        我就說下總結的結論：

他們的共同點就是喫內存，當然在內存充足的情況下，並且有規模適當的集羣，性能應該會更可觀。並且從幾次性能的比較查詢來看，Impala 性能稍領先於 presto，但是 presto 在數據源支持上非常豐富，包括 hive、圖數據庫、傳統關係型數據庫、Redis 等

        大家也可以根據上面的鏈接，自己也嘗試去做下對比測試。

1.7 官網地址

        就在 2020 年 12 月 27 日，prestosql 與 facebook 正式分裂, 並改名爲trino。分裂之前和之後的官網分別是：https://prestosql.io/ 和 https://trino.io。

最新的官網

1.8 Prestodb VS Prestosql(trino)

        根據目前社區活躍度和使用廣泛度, 更加推薦 prestosql。具體的區別詳見：

        http://armsword.com/2020/05/02/the-difference-between-prestodb-and-prestosql/

2. Presto 安裝部署

2.1 prestosql 版本的選擇

        在 presto330 版本里已經提到，jdk8 只支持到 2020-03 月發行的版本. 詳情參考: https://prestosql.io/docs/current/release/release-330.html。在 2020 年 4 月 8 號 presto 社區發佈的 332 版本開始，需要 jdk11 的版本. 由於現在基本都使 用的是 jdk8，所以我們選擇 presto315 版本的, 此版本在 jdk8 的環境下是可用的。如果我們生產環境是 jdk8，但是又想使用新版的 presto，可以爲 presto 單獨指定 jdk11 也可使用。

2.2 集羣安裝規劃

vRW9F6

2.3 Presto Server 的安裝

        1、安裝包下載地址：

https://repo1.maven.org/maven2/io/prestosql/presto-server/315/presto-server-315.tar.gz

        2、將 presto-server-315.tar.gz 上傳到服務器上, 這裏導入到 node01 服務器上的 /export/software/目錄下，並解壓至 /export/servers/目錄下：

[root@node01 software]# tar -zvxf presto-server-315.tar.gz -C /export/servers/

        3、創建 presto 的數據目錄 ( presto 集羣的每臺機器都要創建)，用來存儲日誌這些

[root@node01 presto-server-315]# mkdir -p /file/data/presto

        4、在安裝目錄 /export/servers/presto-server-315 下創建 etc 目錄, 用來存放各種配置文件

[node01@node01 presto-server-315]# mkdir etc

2.4 Node Properties 配置

        在 /export/servers/presto-server-315/etc 路徑下, 配置 node 屬性 (注意：集羣中每臺 presto 的 node.id 必須不一樣, 後面需要修改集羣中其它節點的 node.id 值)

[root@node01 etc]# vim node.properties
#環境名稱,自己任取.集羣中的所有 Presto 節點必須具有相同的環境名稱.
node.environment=develop
#支持字母,數字.對於每個節點,這必須是唯一的.這個標識符應該在重新啓動或升級 Presto 時保持一致
node.id=1
#指定 presto 的日誌和其它數據的存儲目錄,自己創建前面創建好的數據目錄
node.data-dir=/file/data/presto

2.5 JVM Config 配置

        在/exports/servers/presto-server-315/etc 目錄下添加 jvm.config 配置文件, 並填入如下內容

#參考官方給的配置,根據自身機器實際內存進行配置
-server
#最大 jvm 內存
-Xmx16G
#指定 GC 的策略
-XX:+UseG1GC
-XX:G1HeapRegionSize=32M
-XX:+UseGCOverheadLimit
-XX:+ExplicitGCInvokesConcurrent
-XX:+HeapDumpOnOutOfMemoryError
-XX:+ExitOnOutOfMemoryError
-XX:ReservedCodeCacheSize=256M

2.6 Config Properties 配置

        Presto 是由一個 coordinator 節點和多個 worker 節點組成。由於在單獨一臺服務器上配置 coordinator ，有利於提高性能，所以在 node01 上配置成 coordinator，在 node02,node03 上配 置爲 worker(如果實際機器數量不多的話可以將在協調器上部署 worker.) 在 /export/servers/presto-server-315/etc目錄下添加 config.properties 配置文件

# 該節點是否作爲 coordinator,如果是 true 就允許該 Presto 實例充當協調器
coordinator=true
# 允許在協調器上調度工作(即配置 worker 節點).爲 false 就是不允許.對於較大的集羣,協調器上的處理工作可能會影響查詢性能,因爲機器的資源無法用於調度,管理和監視查詢執行的關鍵任務
# 如果需要在協調器所在節點配置 worker 節點改爲 true 即可
node-scheduler.include-coordinator=false
# 指定 HTTP 服務器的端口.Presto 使用 HTTP 進行所有內部和外部通信
http-server.http.port=8080
# 每個查詢可以使用的最大分佈式內存量
query.max-memory=50GB
#查詢可在任何一臺計算機上使用的最大用戶內存量
query.max-memory-per-node=1GB
# 查詢可在任何一臺計算機上使用的最大用戶和系統內存量，其中系統內存是讀取器，寫入器和網絡緩衝區等在執行期間使用的內存
query.max-total-memory-per-node=2GB
# discover-server 是 coordinator 內置的服務,負責監聽 worker
discovery-server.enabled=true
# 發現服務器的 URI.因爲已經在 Presto 協調器中啓用了 discovery,所以這應該是 Presto 協調器的 URI
discovery.uri=http://node01:8080

2.7 Log Properties 配置

        日誌配置文件：etc/log.properties。類似 Java 的日誌級別，包括 INFO、DEBUG、ERROR。

com.facebook.presto=INFO

2.8 Catalog Properties 配置

        Presto 可以支持多個數據源，在 Presto 裏面叫 catalog，這裏以配置支持 Hive 的數據源爲例，配置一個 Hive 的 catalog ：

#在 etc 目錄下創建 catalog 目錄 
[root@node01 etc]# mkdir catalog

Hive 的 catalog:

[root@node01 catalog]# vim hive.properties
#代表 hadoop2 代版本,並不是單單指 hadoop2.x 的版本,而是 hadoop 第二代.固定寫法
connector.name=node02
#指定 hive 的 metastore 的地址(hive 必須啓用 metastore)
hive.metastore.uri=thrift://node01:9083
#如果 hdfs 是高可用必須增加這個配置.如果不是高可用,可省略.如果 Presto 所在的節點>沒 有安裝 Hadoop,需要從其它 hadoop 節點複製這些文件到 Presto 的節點
hive.config.resources=/export/servers/hadoop-2.6.0-cdh5.14.0/etc/hadoop/core-site.xml, /export/servers/hadoop-2.6.0-cdh5.14.0/etc/hadoop/hdfs-site.xml
hive.allow-drop-table=true
hive.storage-format=ORC
hive.metastore-cache-ttl=1s
hive.metastore-refresh-interval=1s
hive.metastore-timeout=35m
hive.max-partitions-per-writers=1000

2.9 分發安裝目錄到集羣中其它節點上

        將 node01 上配置好的 presto 安裝包分發到集羣中的其它節點 (這裏使用的是自己寫的分發腳本)

[root@node01 servers]# xsync script /export/servers/presto-server-315/

2.10 修改 node.id

        修改 node02 和 node03 機器上 node.properties 配置文件中的 node.id (因爲每臺機器 node.id 必須要不一樣)

[root@node02 etc]# vim node.properties 
node.id=2 

[root@node03 etc]# vim node.properties 
node.id=3

2.11 修改 work 節點的配置信息

        修改 worker 節點 (即 linux122 和 linux123 機器) 上的 config.properties 配置文件裏面的配置內容與 coordinator 所在的節點是不一樣的

#該節點是否作爲 coordinator,因爲是 worker 節點,這裏是 false
coordinator=false
#訪問端口,可以自己指定
http-server.http.port=8080
#每個查詢可以使用的最大分佈式內存量
query.max-memory=50GB
#查詢可在任何一臺計算機上使用的最大用戶內存量
query.max-memory-per-node=1GB
#查詢可在任何一臺計算機上使用的最大用戶和系統內存量，其中系統內存是讀取器，寫 入器和網絡緩衝區等在執行期間使用的內存
query.max-total-memory-per-node=2GB
#指定 discovery-server 的地址,這樣 worker 才能找到它.與上面的端口須一致
discovery.uri=http://node01:8080

2.12 啓動服務

        啓動腳本在安裝目錄的bin/launcher目錄下，我們可以使用如下命令作爲一個後臺進程啓動：

bin/launcher start

        另外，也可以用在前臺啓動的方式運行，日誌和目錄輸出將會寫入到 stdout/stderr（可以使用類似daemontools的工具捕捉這兩個數據流）

bin/launcher run

        啓動完之後，日誌將會寫在var/log目錄下，該目錄下有如下文件：

• launcher.log：這個日誌文件由 launcher 創建，並且 server 的 stdout 和 stderr 都被重定向到了這個日誌文件中。這份日誌文件中只會有很少的信息，包括：

        在 server 日誌系統初始化的時候產生的日誌和 JVM 產生的診斷和測試信息。

        server.log：這個是 Presto 使用的主要日誌文件。一般情況下，該文件中將會包括 server 初始化失敗時產生的相關信息。

        http-request.log：這是 HTTP 請求的日誌文件，包括 server 收到的每個 HTTP 請求信息。

        啓動成功之後，我們可以通過jps查看到多了一個 PrestoServer 的進程。

[root@node01 etc]# jps
6051 PrestoServer

        此時我們就可以通過ip:端口的方式訪問到 presto 的 webui 界面。

        關於 webui 中各項指標的含義，我整理了 2 張圖，大家可以借鑑一下

        另外，關於 Stage，Task 階段的各參數指標含義，就不細講了，更多內容詳情見官網....

3、Presto 命令行 Client 的安裝

        Presto 的 命令行 Client 下載步驟也很簡單：

        1、下載 Presto 的客戶端（下載 presto 對應的版本）👉https://repo1.maven.org/maven2/io/prestosql/presto-cli/315/presto-cli-315-executable.jar

        2、將 presto-cli-315-executable.jar 上傳至服務器, 放在 node01 的 /export/servers/presto-server- 315/bin 目錄下

        3、爲方便使用，修改 jar 包名稱爲 presto

[root@node01 bin]$ mv presto-cli-315-executable.jar presto

        4. 給文件增加執行權限

[root@node01 bin]# chmod +x presto

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 版本

本篇文章中談到的所有安裝包資源，在公衆號【大數據夢想家】後臺回覆 “presto” 即可獲得！

• 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 語句 即可，如圖所示：        還可以查詢列表中其他的表格，比如，想要執行現在已經寫好了的 SQL 語句，SELECT * FROM hive.company."demo01" LIMIT 100，按Ctrl + Enter組合鍵顯示查詢結果，如圖所示：        當然，我們也可以通過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 的安裝，基本使用，可視化客戶端的安裝與基本使用，以及使用任何一個組件我們都很注重的優化 ...... 希望大家看完之後能夠有所收穫！

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/1N7Kd9E2dLB1OPhY_MTs6A