Access MaxCompute through the Common Table link - Hologres

Starting with V3.0.38, Hologres introduces an upgraded pathway for accessing MaxCompute, known as Common Table. This document explains how to use Common Table to access MaxCompute.

Introduction

With Common Table, Hologres reads data from MaxCompute using a C++ native reader. It offers the following advantages over the existing SQE/HQE mechanism:

Note

已开通 indicates supported and 未开通 indicates unsupported.

Comparison item	SQE/HQE	Common Table
Performance	High	Very high. Improves performance by 33% over the SQE/HQE mechanism and by over 200% compared to reading data via the open storage Storage API.
Dynamic data masking	Requires separate configuration on tables.	Inherits data masking rules from MaxCompute, ensuring consistent query results with those from MaxCompute.
Schema evolution	Does not support direct reads, resulting in slightly lower performance.	Supports direct reads for high performance.
Encryption at rest	Supports tables encrypted with a KMS key. Does not support tables encrypted with the default key.	Supports tables encrypted with a KMS key. Supports tables encrypted with the default key.
ACID 1.0 tables
PK Delta Table
Append Delta Table

Permissions

To access MaxCompute foreign/external tables via Common Table, you must have relevant permissions listed in the table. For more information about granting these permissions, see Manage user permissions by using commands.

Note that Hologres V4.0+ introduce changes to permission verification.

Table type	Operation	Required MaxCompute permissions (Hologres V3.0, V3.1, and V3.2)	Required MaxCompute permissions (Hologres V4.0+)
Foreign table	IMPORT FOREIGN SCHEMA	Describe, List	Describe, List
Foreign table	Querying data	Describe, Select	Select
External table	Viewing metadata	Describe	Describe
External table	Querying data	Describe, Select	Select

Hologres currently supports reading only the latest snapshot of a MaxCompute Delta Table.
Writing data from Hologres back to a MaxCompute Delta Table is not supported.

Features

Feature	Supported versions
Querying MaxCompute Append Delta tables	>= 3.0.40 >= 3.1.10 >=3.2.0 >=4.0.0
Using `spillRead` for queries on MaxCompute Delta Tables to resolve errors caused by too many unspilled files `MaxCompute csdk open reader failed: storage/formats/orcfile/change_log_reader_util.cpp(255): InvalidArgument: ODPS-0010000:InvalidArgument:Need spill read but TmpDir or TmpCapability no specified.`	>= 3.0.41 >= 3.1.10 >= 3.2.0 >= 4.0.0
Reading MaxCompute tables that contain default values	>=3.0.40 >= 3.1.10 >=3.2.0 >=4.0.0
Auto Split Size, which automatically determines the split size based on instance specifications	>=3.1.33 >=3.2.0 >=4.0.0
Querying TIMESTAMP_NTZ data	>= 3.0.41 >= 3.1.10 >=3.2.0 >=4.0.0
Predicate pushdown for equality filters on attributes like clustering keys and primary keys in Delta Tables improves filter efficiency and query performance.	>= 4.0.6

Enable Common Table

Choose a method that suits your scenario.

Session level

-- Enable Common Table at the session level. Execute this along with a query or DML statement.
SET hg_experimental_external_catalog_routing = 'odps:common_table,dlf:hqe';

Database level

-- Enable Common Table at the database level.
ALTER DATABASE <database name> SET hg_experimental_external_catalog_routing = 'odps:common_table,dlf:hqe';

Instance level

-- Enable Common Table at the instance level.
ALTER ROLE ALL SET hg_experimental_external_catalog_routing = 'odps:common_table,dlf:hqe';

Disable Common Table

Session level

-- Disable Common Table at the session level. Execute this with your query or DML statement.
SET hg_experimental_external_catalog_routing = 'odps:holo_native,dlf:hqe';

Database level

-- Disable Common Table at the database level.
ALTER DATABASE <database name> SET hg_experimental_external_catalog_routing = 'odps:holo_native,dlf:hqe';

Instance level

-- Disable Common Table at the instance level.
ALTER ROLE ALL SET hg_experimental_external_catalog_routing = 'odps:holo_native,dlf:hqe';

Check the current access pathway

Run the following SQL statement to check the current mechanism for accessing MaxCompute. odps:common_table,dlf:hqe indicates Common Table is used.
```
SHOW hg_experimental_external_catalog_routing;
```
For historical queries, check the query_extinfo field in the slow query log. If driver:CommonTable appears, Common Table was used for these queries.

Auto Split Size

The Auto Split Size parameter is set to off by default. You can enable it as needed.

Enable Auto Split Size

V4.0+

Session level

-- Enable Auto Split Size at the session level
SET hg_experimental_enable_maxcompute_sdk_auto_split_size = 'on';

Database level

-- Enable Auto Split Size at the database level
ALTER DATABASE <database name> SET hg_experimental_enable_maxcompute_sdk_auto_split_size = 'on';

Instance level

-- Enable Auto Split Size at the instance level
ALTER ROLE ALL SET hg_experimental_enable_maxcompute_sdk_auto_split_size = 'on';

Versions earlier than V4.0

Session level

-- Enable Auto Split Size at the session level
SET hg_experimental_enable_common_table_auto_split_size = 'on';

Database level

-- Enable Auto Split Size at the database level
ALTER DATABASE <database name> SET hg_experimental_enable_common_table_auto_split_size = 'on';

Instance level

-- Enable Auto Split Size at the instance level
ALTER ROLE ALL SET hg_experimental_enable_common_table_auto_split_size = 'on';

Disable Auto Split Size

V4.0+

Session level

-- Disable Auto Split Size at the session level
SET hg_experimental_enable_maxcompute_sdk_auto_split_size = 'off';

Database level

-- Disable Auto Split Size at the database level
ALTER DATABASE <database name> SET hg_experimental_enable_maxcompute_sdk_auto_split_size = 'off';

Instance level

-- Disable Auto Split Size at the instance level
ALTER ROLE ALL SET hg_experimental_enable_maxcompute_sdk_auto_split_size = 'off';

Versions earlier than V4.0

Session level

-- Disable Auto Split Size at the session level
SET hg_experimental_enable_common_table_auto_split_size = 'off';

Database level

-- Disable Auto Split Size at the database level
ALTER DATABASE <database name> SET hg_experimental_enable_common_table_auto_split_size = 'off';

Instance level

-- Disable Auto Split Size at the instance level
ALTER ROLE ALL SET hg_experimental_enable_common_table_auto_split_size = 'off';