Access MaxCompute through the Common Table pathway - Hologres

Hologres V3.0.38 and later versions support an upgraded pathway, called the Common Table pathway, to accelerate access to MaxCompute. This topic describes how to use the Common Table pathway to access MaxCompute.

Features

The Common Table pathway reads data from MaxCompute using a C++ native reader. It provides the following advantages over the existing SQE/HQE pathway:

Note

Supported indicates that a feature is supported. Unsupported indicates that a feature is not supported.

Comparison item	SQE/HQE pathway	Common Table pathway
Performance	Strong	Very high. Performance is improved by 33% compared to the SQE/HQE pathway and by more than 200% compared to reading data using the open storage Storage API.
Dynamic data masking tables	Rules must be configured again.	Inherits data masking rules set in MaxCompute. The queried data is consistent with the results from a MaxCompute query.
Schema Evolution	Unsupported Does not support direct file reads. Performance is slightly lower.	Supports high-performance direct file reads.
Tables with encryption at rest	Supports table encryption with a KMS key. Unsupported Does not support tables encrypted with the default key.	Supported Supports tables encrypted with a KMS key. Supports tables encrypted with the default key. Note Requires Hologres V3.2 or later.
ACID 1.0 tables	Unsupported	Supported
PK Delta Table	Unsupported	Supported
Append Delta Table	Unsupported	Supported

Permissions

To access MaxCompute foreign tables or external tables through the Common Table pathway, you must have the permissions listed in the following table. To grant users permissions to access MaxCompute projects and tables, see Manage user permissions using commands.

To align with the permissions for querying data in MaxCompute, permission verification has been changed in Hologres V4.0 and later versions.

Method for creating MaxCompute foreign tables	Operation	Required MaxCompute permissions (Hologres V3.0, V3.1, and V3.2)	Required MaxCompute permissions (V4.0 and later)
Foreign table method	Batch create MaxCompute foreign tables using IMPORT FOREIGN SCHEMA	Describe, List	Describe, List
Foreign table method	Query data	Describe, Select	Select
External database method	View the metadata of an external table	Describe	Describe
External database method	Query 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.

Feature updates

Feature description	Supported versions
Supports reading the JSON data type in MaxCompute	>=4.1.1
Supports querying MaxCompute Append Delta Tables	>= 3.0.40 >= 3.1.10 >=3.2.0 >=4.0.0
Supports using spillRead to query MaxCompute Delta Tables. This resolves errors that occur when too many files are not spilled. `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
Supports reading MaxCompute tables that have columns with default values	>=3.0.40 >= 3.1.10 >=3.2.0 >=4.0.0
Supports Auto Split Size. This feature automatically determines the split size based on the instance type. For more information, see the following sections.	>=3.1.33 >=3.2.0 >=4.0.0
Supports querying data of the TIMESTAMP_NTZ type	>= 3.0.41 >= 3.1.10 >=3.2.0 >=4.0.0
Supports predicate pushdown for equality filters on attributes such as clustering keys and primary keys in Delta Tables. This improves filter effectiveness and query performance.	>= 4.0.6

How to Enable

You can enable the pathway based on your scenario.

Session level

-- Enable the pathway at the session level. You must execute this statement together with a query or DML statement.
SET hg_experimental_external_catalog_routing = 'odps:common_table,dlf:hqe';

Database level

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

Instance level

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

Shutdown method

Session level

-- Disable the pathway at the session level. You must execute this statement together with a query or DML statement.
SET hg_experimental_external_catalog_routing = 'odps:holo_native,dlf:hqe';

Database level

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

Instance level

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

Check the current access pathway

You can run the following SQL statement to check the current access pathway. If odps:common_table,dlf:hqe is returned, the Common Table pathway is used.
```
SHOW hg_experimental_external_catalog_routing;
```
For historical queries, you can check the query_extinfo field in the slow query log. If driver:CommonTable appears, the Common Table pathway was used for the query.

Auto Split Size

The Auto Split Size feature is disabled by default, and its parameter is set to off. You can enable or disable this feature as needed. The following sections describe how to configure this feature.

Enable Auto Split Size

V4.0 and later versions

Enable at the session level

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

Enable at the database level

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

Enable at the 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

Enable at the session level

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

Enable at the database level

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

Enable at the 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 and later versions

Disable at the session level

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

Disable at the database level

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

Disable at the 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

Disable at the session level

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

Disable at the database level

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

Disable at the instance level

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