优化MaxCompute外部表的查询性能 - 实时数仓Hologres

本文为您介绍在Hologres中如何优化查询MaxCompute外部表数据的性能。

Hologres与MaxCompute在底层资源无缝打通，您可以通过以下方式加速查询MaxCompute的数据：

新建外部表直接加速查询
在Hologres中新建外部表，即可直接加速查询外部表数据。无需数据导入导出、无冗余存储。该方式适用于单次查询的数据量小于200 GB的表（与查询字段的大小无关）。
导入数据至Hologres进行加速查询
当需要大量分析计算外部表数据并建立与内部表的连接时，您可以在Hologres中新建内部表并导入外部表数据。根据业务需求，为内部表指定合适的Distribute Key索引属性，加快查询速度。
导入外部表数据相比新建外部表方式查询速度更快。该方式适用于单次查询的数据量大于等于200 GB的表，以及使用复杂查询、包含索引查询、更新数据或插入数据的场景。
导入MaxCompute外部表数据至Hologres的操作请参见使用SQL导入MaxCompute的数据至Hologres。

您还可以根据实际业务需求，通过优化查询语句、修改MaxCompute数据源表、合理配置资源和参数，优化查询外部表数据的性能。

优化查询语句

使用如下方式优化查询语句，避免查询外部表数据时扫描全表：

查询数据时使用select a from xx语句查询指定内容，不推荐使用select * from xx。
增加过滤分区的条件或减少扫描的分区数，实现减少扫描的数据量。

修改MaxCompute数据源表

通过修改MaxCompute数据源表优化查询数据的性能，方式如下：

转换MaxCompute表为Hash Clustering表
Hash Clustering表可以优化Bucket Pruning、Aggregation以及存储。
创建MaxCompute表时，如果使用Clustered By指定了Hash Key，则MaxCompute对指定列进行Hash运算，并分散Hash值至各个Bucket中。请选择重复键值少的列作为Hash Key。
如果没有指定Hash Key，则使用如下语句指定。
```
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 Key。
新增聚集属性后，新的分区数据写入MaxCompute时直接执行Hash Clustering计算。同时，您可以使用INSERT OVERWRITE语句对原有的源表数据执行Hash Clustering计算。
说明
- Hash Clustering表不支持INSERT INTO语句，您需要使用INSERT OVERWRITE语句添加数据。
- Hash Clustering表不支持通过Tunnel方式上传数据至Range Cluster表。
合并小文件
当MaxCompute中的小文件数量较多时，会降低查询表数据的速度。
您可以在Hologres中执行如下语句，查看Query命中的文件数量：
```
explain analyze <query>;
```
查询结果中的file_count表示MaxCompute中的文件数。如果当前小文件数量较多，影响查询速度，您可以在MaxCompute中对小文件进行合并。

合理配置参数

查询外部表时，Hologres会设置一些默认的参数来提高读取数据的并发度，从而提高查询效率。如果您具有有特殊需求，可以按照业务场景合理配置如下参数（如下参数是经过内部调校和实验的最佳规格，一般情况下，不太建议更改）。

说明

hg_foreign_table_executor_max_dop建议不要设置太低的值，例如设置为1。尤其是在实例既有写入又有查询的时候，不建议直接设置为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 1.1中生效。
set hg_foreign_table_split_size = 128; 

--设置访问外表时的最大并发度，默认为实例的Core数，最大为128，不建议设置大，避免外表query（特别是数据导入场景）影响其它query，导致系统繁忙导致报错。该参数在Hologres 1.1中生效。
set hg_foreign_table_executor_max_dop = 32;

--设置访问外表时执行DML语句的最大并发度，默认值为32，针对数据导入导出场景专门优化的参数，避免导入操作占用过多系统资源，该参数在Hologres 1.1中生效。
set hg_foreign_table_executor_dml_max_dop = 16;

采用全新外部表查询引擎

从Hologres V0.10版本开始，将会采用全新的MaxCompute查询引擎，相比低于V0.10版本的实例，查询性能约有 30% ~ 100%的提升。

使用限制
- 该功能仅Hologres V0.10及以上版本支持，请在Hologres管理控制台的实例详情页查看当前实例版本，如果您的实例是V0.10以下版本，请您使用自助升级或加入Hologres钉钉交流群反馈，详情请参见如何获取更多的在线支持？。
- 从Hologres V1.1版本开始，查询MaxCompute数据时，默认使用全新外表查询引擎。
- 该功能仅适用于独享实例，不适用于共享实例。
- 当前仅对MaxCompute ORC类型的表有加速效果，暂不支持对Cfile等文件进行加速。
- 请确保MaxCompute与Hologres的数据类型映射正确，否则加速效果不明显。

使用方式

Hologres实例升级到V0.10版本之后，您可以使用如下开关参数开启全新外表查询引擎。

--session级别
set hg_experimental_enable_access_odps_orc_via_holo = on;

--DB级别
alter database <databasename> set hg_experimental_enable_access_odps_orc_via_holo = on;

Hologres在V1.1及之后版本中，新外表查询引擎默认启用，可以通过以下参数配置。

--session级别
set hg_enable_access_odps_orc_via_holo = on;

--DB级别
alter database <databasename> set hg_enable_access_odps_orc_via_holo = on;