最佳化MaxCompute外部表格的查詢效能 - Hologres

MaxCompute與Hologres已在底層無縫打通，Hologres原生向量引擎可以直讀MaxCompute資料，並且充分利用MaxCompute的多級分區過濾和運算元下推來最佳化查詢速度。本文為您介紹在Hologres中如何通過建立外部表格或匯入資料至Hologres的方式加速查詢MaxCompute資料。

建立外部表格

在Hologres中建立外部表格，即可直接加速查詢MaxCompute表資料，無需資料匯入匯出，也無冗餘儲存。該方式相較於在MaxCompute中查詢資料，查詢效能約有2~5倍的提升。如需進一步加速查詢MaxCompute資料，可採取以下最佳化策略。

使用Serverless Computing功能查詢外部表格

Hologres從V2.1.17版本起支援Serverless Computing能力，針對MaxCompute巨量資料量離線匯入、大型ETL作業、外部表格巨量資料量查詢等情境，使用Serverless Computing執行該類任務可以直接使用額外的Serverless資源，避免使用執行個體自身資源，無需為執行個體預留額外的計算資源，顯著提升執行個體穩定性、減少OOM機率，且僅需為任務單獨付費。Serverless Computing詳情請參見Serverless Computing概述，Serverless Computing使用方法請參見Serverless Computing使用指南。

採用全新外部表格執行引擎HQE

從Hologres V0.10版本開始，Hologres採取全新的執行引擎HQE來加速查詢MaxCompute外部表格，相比低於V0.10版本的執行個體，查詢效能約有30% ~ 100%的提升。

執行引擎HQE使用限制如下：

當前僅對MaxCompute ORC類型的表有加速效果，暫不支援對Cfile等檔案進行加速。
不支援加速查詢MaxCompute Transactional表或表結構發生變更（Schema Evolution）的表，系統會自動切換到SQE引擎中執行。
請確保MaxCompute與Hologres表資料類型映射正確，否則會影響加速效果。

避免全表掃描

使用如下方式最佳化查詢語句，避免查詢外部表格資料時掃描全表：

查詢資料時使用select a from xx語句查詢指定內容，不推薦使用select * from xx。
增加過濾分區的條件或減少掃描的分區數，實現減少掃描的資料量。

SQE查詢最佳化

對於HQE不支援的查詢情境，系統會自動切換到SQE引擎中執行，此時可能會發生效能回退，您可通過以下方式修改MaxCompute資料來源表，最佳化資料查詢效能：

轉換MaxCompute表為Hash Clustering表
Hash Clustering表的優勢在於可以實現Bucket Pruning最佳化、Aggregation最佳化以及儲存最佳化。在建立表時，使用Clustered By指定Hash Key後，MaxCompute將對指定列進行Hash運算，按照Hash值分散到各個Bucket裡。Hash Key值請選擇重複索引值少的列。
轉換為Hash Clustering表語句如下。
```
ALTER TABLE <table_name> [CLUSTERED BY (<col_name> [, <col_name>, ...])
                       [SORTED BY (<col_name> [ASC | DESC] [, <col_name> [ASC | DESC] ...])]
                       INTO <number_of_buckets> BUCKETS];
```
使用ALTER TABLE語句增加了新的聚簇屬性之後，新的分區將按照Hash Clustering儲存。關於Hash Clustering更多詳細介紹請參見Hash Clustering。
轉換MaxCompute表為Range Clustering表
Range Clustering作為一種新的資料切分方式，提供了一個全域有序的資料分布，一是可以避免Hash Clustering可能造成的資料扭曲問題；二是在資料有序分布的前提下，建立兩級索引（Index），支援對Clustering Key的區間查詢以及多鍵的組合查詢等情境。
轉換為Range Clustering表語句如下。
```
ALTER TABLE <table_name> [RANGE CLUSTERED BY (<col_name> [, <col_name>, ...])
                         [SORTED BY (<col_name> [ASC | DESC] [, <col_name> [ASC | DESC] ...])]
                         [INTO <number_of_buckets> BUCKETS];
```
使用ALTER TABLE語句增加了新的聚簇屬性之後，新的分區將按照Range Clustering儲存。關於Range Clustering更多詳細介紹請參見Range Clustering。
說明
- Hash Clustering表、Range Clustering表均不支援INSERT INTO語句，您需要使用INSERT OVERWRITE語句覆蓋寫入資料。
- 由於Tunnel方式上傳的資料是無序的，因此Hash Clustering表、Range Clustering表均不支援通過Tunnel方式上傳資料。

合并小檔案

當MaxCompute中的小檔案數量較多時，會降低查詢表資料的速度。

您可以在MaxCompute中執行以下語句查看錶的檔案數量，更多有關MaxCompute小檔案最佳化詳情，請參見小檔案最佳化及作業診斷常見問題。

desc extended <table_name>;

查詢結果中的FileNum表示MaxCompute中目標表的檔案數量，Size表示表總檔案大小（Byte）。當表有100個以上的檔案且平均檔案大小小於64MB時，您可以考慮在MaxCompute中對小檔案進行合并。

參數調優

查詢外部表格時，Hologres會設定一些預設的參數來提高讀取資料的並發度，從而提高查詢效率。如果您具有特殊需求，可以按照業務情境合理配置如下參數。

重要

下列參數是經過內部調校和實驗得到的最佳規格，通常不建議更改。
不建議將hg_foreign_table_executor_max_dop參數值設定過低。例如該參數設定為1，當執行個體中某張表同時存在寫入和查詢操作時，會導致負載集中在某些Worker上，從而引發執行個體OOM。

調整每次Query命中的分區數大小。

--預設512，最大為1024,不建議調整太大，會影響查詢效能。
set hg_foreign_table_max_partition_limit = 128;

調整每次讀取MaxCompute表batch的大小。

--預設8192。
set hg_experimental_query_batch_size = 4096;

設定MaxCompute表訪問切分split的數目，可以調節並發數目。

--預設64MB，當表很大時需要調大，避免過多的split影響效能。該參數在Hologres V1.1中生效。
set hg_foreign_table_split_size = 128;

設定訪問外表時的最大並發度，預設為執行個體的Core數。

--最大為128，不建議設定大，避免外表query（特別是資料匯入情境）影響其它query，導致系統繁忙導致報錯。該參數在Hologres V1.1中生效。
set hg_foreign_table_executor_max_dop = 32;

設定訪問外表時執行DML語句的最大並發度。

--預設值為32，針對資料匯入匯出情境專門最佳化的參數，避免匯入操作佔用過多系統資源，該參數在Hologres V1.1中生效。
set hg_foreign_table_executor_dml_max_dop = 16;

匯入資料至Hologres（推薦）

當對外部表格資料進行大量分析計算，且存在與內部表串連的需求時，推薦在Hologres中建立內部表並匯入外部表格資料，您可根據業務需求，為內部表指定合適的Distribute Key索引屬性，以加快查詢速度。

匯入資料至Hologres相比建立外部表格方式查詢速度更快，查詢效能約有10~100倍的提升。匯入MaxCompute外部表格資料至Hologres的操作請參見使用SQL從MaxCompute匯入。