如何优化查询MaxCompute外部表数据的性能_实时数仓 Hologres(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导入。