Presto上使用SQL遇到的一些坑

Lorry_Lu發表於2018-02-03
RESTSQL

最近換了新工作，在資料處理方面，公司是用Presto連線各個業務部的資料庫，直接上SQL處理資料。一度是不是很適應。經過一段時間的工作，有了些感覺，決定將遇到的一些坑記錄下來。

Presto的是什麼？優勢是什麼呢？從官方文件中我們瞭解到

Presto是一個分散式SQL查詢引擎，用於查詢分佈在一個或多個不同資料來源中的大資料集。
千萬不要以為Presto可以解析SQL，那麼Presto就是一個標準的資料庫。
Presto被設計為資料倉儲和資料分析產品：資料分析、大規模資料聚集和生成報表。這些工作經常通常被認為是線上分析處理操作。

所以說，當公司業務有跨庫分析時（一般情況是，業務資料庫分佈在各個部門），一些資料需要配合其他部門的資料進行關聯查詢，這個時候可以考慮Presto。但是目前，對於MySQL統計查詢在效能上有瓶頸。可考慮將資料按時間段歸檔到HDFS中，以提高統計效率。

如果需要對業務資料庫進行較為實時的統計，而且不需要跨庫操作的時候，建議還是直連資料庫。

遇到過的問題

如何加快在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;
  1. 對結果進行count計算
  2. 返回結果

所以說，對於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*/

查詢條件中儘量帶上分割槽欄位進行過濾

如果資料被規當到HDFS中，並帶有分割槽欄位。在每次查詢歸檔表的時候，要帶上分割槽欄位作為過濾條件，這樣可以加快查詢速度。因為有了分割槽欄位作為查詢條件，就能幫助Presto避免全區掃描，減少Presto需要掃描的HDFS的檔案數。

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

利用子查詢，減少讀表的次數，尤其是大資料量的表

具體做法是，將使用頻繁的表作為一個子查詢抽離出來，避免多次read。

只查詢需要的欄位

一定要避免在查詢中使用 SELECT *這樣的語句，換位思考，如果讓你去查詢資料是不是告訴你的越具體，工作效率越高呢。

對於我們的資料庫而言也是這樣，任務越明確，工作效率越高。

對於要查詢全部欄位的需求也是這樣，沒有偷懶的捷徑，把它們都寫出來。

Join查詢優化

Join左邊儘量放小資料量的表，而且最好是重複關聯鍵少的表

欄位名引用

Presto中的欄位名引用使用雙引號分割，這個要區別於MySQL的反引號`。
當然，你可以不加這個雙引號。

時間函式

對於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`;

MD5函式的使用

Presto中MD5函式傳入的是binary型別，返回的也是binary型別，要對字串進行MD5操作時，需要轉換.

SELECT to_hex(md5(to_utf8(`1212`)));

不支援INSERT OVERWRITE語法

Presto中不支援insert overwrite語法，只能先delete，然後insert into。

ORC格式

Presto中對ORC檔案格式進行了針對性優化，但在impala中目前不支援ORC格式的表，hive中支援ORC格式的表，所以想用列式儲存的時候可以優先考慮ORC格式。

PARQUET格式

Presto目前支援parquet格式，支援查詢，但不支援insert。

相關文章