This topic explains how to create a Hologres external table, including how to specify a Hologres data source, grant access using a RAM role or service-linked role, map a target table, and configure JDBC driver settings in the CREATE TABLE statement.
Overview
Hologres is a real-time interactive analytical data warehouse that is compatible with the PostgreSQL protocol and seamlessly integrated with MaxCompute. You can create a Hologres external table in MaxCompute to query a Hologres data source by using the PostgreSQL JDBC driver and Hologres RAM role authentication. This approach eliminates redundant storage, avoids data imports and exports, and delivers fast query results.
Limitations
-
Data manipulation (DML)
-
MaxCompute does not support
UPDATEorDELETEoperations on Hologres external tables. -
Hologres external tables do not support the
INSERT OVERWRITEstatement.To overwrite data in a Hologres table with data from MaxCompute, run an
INSERT OVERWRITEstatement in Hologres. This statement reads from an external table that maps to a MaxCompute table. For more information, see INSERT OVERWRITE.
-
-
Risk of data duplication
Writing large volumes of data to a Hologres external table uses parallel processes. In rare cases, one of these processes might rewrite data, causing data duplication.
-
Partitions
Partitioned tables in Hologres and MaxCompute do not map directly to each other. Hologres external tables do not support partitions. However, in direct-read mode, partition pruning is still possible. This occurs when you query a Hologres external table that maps to a Hologres partitioned parent table, and your query filters on the partition column of an underlying partitioned child table.
-
Hologres external tables do not support the
clusterattribute. -
Hologres external tables do not support mapping to a Hologres Dynamic Table.
-
Hologres external tables do not support direct reads of tables with row-level permissions enabled.
-
For a list of compatible data types, see Supported data types.
Permissions
Hologres external tables support two authorization methods:
-
RAM role-based authorization
-
Whether you use an Alibaba Cloud account (primary account), a RAM user, or a RAM role, you specify the role for accessing Hologres data in the
odps.properties.rolearnparameter. Create a RAM role, grant it permissions to access the Hologres database, and then set the role's ARN in theodps.properties.rolearnparameter. -
For detailed steps to create a role and grant permissions, see RAM role-based authorization for Hologres.
-
-
Service-linked role authorization (identity passthrough)
A service-linked role passes the accessor's identity to Hologres for authorization.
-
The accessor (Alibaba Cloud account, RAM user, or RAM role) must have permissions to access the Hologres database.
-
For detailed steps to create a service-linked role, see Service-linked role for Hologres (Identity Passthrough).
-
Create a Hologres external table
You can create a Hologres external table using one of the following two authorization modes: RAM role authorization mode and service-linked role authorization mode.
Usage notes
Guidelines for creating external tables
-
If you enable an IP whitelist for external access on your Hologres instance, upgrade your instance to the required version:
-
For the RAM role authorization mode, upgrade Hologres to V3.2 or later.
-
For the service-linked role authorization mode, upgrade Hologres to V2.0.28 or later.
After the upgrade, Hologres can trust the IP addresses of MaxCompute nodes based on the MaxCompute service identity or a service-linked role. You no longer need to add the IP addresses of MaxCompute elastic nodes to the IP whitelist for your Hologres instance.
-
-
You can map both parent tables and child tables in Hologres to an external table in MaxCompute. However, you can only read from, but not write to, an external table that is mapped to a parent table.
-
When you write data to a Hologres external table, the INSERT ON CONFLICT (UPSERT) mechanism is not supported. If the Hologres source table has a primary key, ensure that the data you write does not cause a primary key conflict with existing data in the source table.
-
Table names and column names are case-insensitive. You cannot enforce case sensitivity when you create or query tables and columns.
Schema compatibility
If the schema of the Hologres source table does not match the schema of the external table:
-
Mismatched column count: If the source table has fewer columns than the external table defined in the DDL statement, Hologres reports an error such as
column "xxx" does not existwhen you read data. If the source table has more columns than the external table, the extra columns are ignored. -
Mismatched column types: MaxCompute does not support reading STRING data from the Hologres source table into an INT column. Casting INT data to a STRING type is supported but not recommended.
Syntax
When you create an external table, you must specify the StorageHandler, provide authorization through either a RAM role or a service-linked role, and define the JDBC connection URL in the DDL statement to access the Hologres data source.
RAM role authorization
For a complete code sample, see Create a Hologres external table (RAM role authorization mode).
CREATE EXTERNAL TABLE [IF NOT EXISTS] <table_name>(
<col1_name> <data_type>,
<col2_name> <data_type>,
......
)
stored BY 'com.aliyun.odps.jdbc.JdbcStorageHandler'
WITH serdeproperties (
'odps.properties.rolearn'='<role_arn>')
location 'jdbc:postgresql://<endpoint>:<port>/<database>?
ApplicationName=MaxCompute&[currentSchema=<schema>&]
[useSSL={true|false}&]table=<holo_table_name>/'
tblproperties (
'mcfed.mapreduce.jdbc.driver.class'='org.postgresql.Driver',
'odps.federation.jdbc.target.db.type'='holo',
-- If the column names in Hologres and MaxCompute are different, you must add the odps.federation.jdbc.colmapping parameter to map the columns. You can omit this parameter if the column names are identical.
['odps.federation.jdbc.colmapping'='<table_column1>:<source_column1>,
<table_column2>:<source_column2>,...']
);
Service-linked role authorization
For a complete code sample, see Create a Hologres external table (service-linked role authorization mode).
The service-linked role authorization mode allows a RAM user to access authorized tables in both MaxCompute and Hologres without requiring manual authorization. This mode also supports the Hologres IP whitelist feature, which simplifies access.
-- Create an external table
CREATE EXTERNAL TABLE [IF NOT EXISTS] <table_name>(
<col1_name> <data_type>,
<col2_name> <data_type>,
......
)
STORED BY 'com.aliyun.odps.jdbc.JdbcStorageHandler'
LOCATION 'jdbc:postgresql://<endpoint>:<port>/<database>?
ApplicationName=MaxCompute&[currentSchema=<schema>&]
[useSSL={true|false}&]table=<holo_table_name>/'
tblproperties (
'mcfed.mapreduce.jdbc.driver.class'='org.postgresql.Driver',
'odps.federation.jdbc.target.db.type'='holo',
-- If the column names in Hologres and MaxCompute are different, you must add the odps.federation.jdbc.colmapping parameter to map the columns. You can omit this parameter if the column names are identical.
['odps.federation.jdbc.colmapping'='<table_column1>:<source_column1>,
<table_column2>:<source_column2>,...']
);
Parameters
Examples
Step 1: Prepare Hologres data
You can skip this step if you already have a Hologres database, a table, and test data.
Create a Hologres database
-
Log on to the Hologres management console. In the upper-left corner, select a region.
-
In the navigation pane on the left, click Instances.
-
If you do not have an instance, you must first purchase a Hologres instance.
On the Instances page, click the name of the target instance.
-
On the instance details page, click Connect to Instance.
-
Click the Metadata Management tab.
Click Create Database, enter the Database Name in the dialog box that appears, and leave the other parameters at their default settings.
Create a Hologres table
-
On the instance details page, click Connect to Instance.
-
Click the SQL Editor tab.
-
Run the following statements:
CREATE TABLE IF NOT EXISTS holo ( id INT PRIMARY KEY, name TEXT ); INSERT INTO holo (id, name) VALUES (1, 'kate'), (2, 'mary'), (3, 'bob'), (4, 'tom'), (5, 'lulu'), (6, 'mark'), (7, 'haward'), (8, 'lilei'), (9, 'hanmeimei'), (10, 'lily'), (11, 'lucy'); SELECT * FROM holo ORDER BY id;
Step 2: Create a Hologres external table
RAM role authorization
In this mode, you integrate a RAM role with Hologres data access permissions into the external table's definition. You can then grant permissions on this external table to other MaxCompute users. When a user accesses the external table, the system uses this integrated RAM role to access the mapped Hologres table.
Hologres does not support cross-account RAM roles. Therefore, both the RAM role and the MaxCompute project that accesses the Hologres external table must belong to the same Alibaba Cloud account.
Prerequisites
-
You have a MaxCompute project in which to create the Hologres external table.
For more information about how to create a MaxCompute project, see Create a MaxCompute project.
-
You have created and authorized a RAM role. For more information, see RAM role authorization for Hologres.
Create the external table
-
Log on to the MaxCompute client and switch to the target MaxCompute project.
-
Run the following command to create a Hologres external table.
The following example uses the Hologres instance information prepared in Step 1: Prepare Hologres data:
-
Hologres database name:
holo_external_test -
Hologres database schema:
public -
Hologres table name:
holo -
Hologres classic network endpoint:
hgprecn-cn-oew210ut****-cn-hangzhou-internal.hologres.aliyuncs.com:80
CREATE EXTERNAL TABLE IF NOT EXISTS my_table_holo_jdbc ( user_id bigint, user_name string ) stored BY 'com.aliyun.odps.jdbc.JdbcStorageHandler' WITH serdeproperties ( 'odps.properties.rolearn'='acs:ram::139699392458****:role/<role name>') location 'jdbc:postgresql://hgprecn-cn-oew210ut****-cn-<region>-internal.hologres.aliyuncs.com:80 /<holo database name>?ApplicationName=MaxCompute ¤tSchema=public&table=<table name>/' tblproperties ( 'mcfed.mapreduce.jdbc.driver.class'='org.postgresql.Driver', 'odps.federation.jdbc.target.db.type'='holo', -- If the column names of the Hologres source table and the Hologres external table are different, -- you must add the odps.federation.jdbc.colmapping parameter to map the columns. -- You can omit this parameter if the column names are identical. 'odps.federation.jdbc.colmapping'='user_id:id,user_name:name' ); -
-
Run the following commands to query the Hologres source table using the newly created external table.
-- The following properties are required to access the Hologres external table. SET odps.sql.split.hive.bridge=true; SET odps.sql.hive.compatible=true; SET odps.table.api.enable.holo.table=true; --Enables direct read access over JDBC. -- Query data from the Hologres external table. SELECT * FROM my_table_holo_jdbc limit 10; -- The command returns the following result. +------------+------------+ | user_id | user_name | +------------+------------+ | 9 | hanmeimei | | 4 | tom | | 7 | haward | | 2 | mary | | 5 | lulu | | 8 | lilei | | 10 | lily | | 1 | kate | | 6 | mark | | 11 | lucy | +------------+------------+ -
Use the Hologres external table to exchange data with Hologres and perform federated analysis.
-
Write data processed in MaxCompute to Hologres using the external table for accelerated analytics and online services. The following code provides an example.
-- The following properties are required to access the Hologres external table. SET odps.sql.split.hive.bridge=true; SET odps.sql.hive.compatible=true; SET odps.table.api.enable.holo.table=true; --Enables direct read access over JDBC. -- Insert data into the Hologres external table. INSERT INTO my_table_holo_jdbc VALUES (12,'alice'); -- Query data from the Hologres external table. SELECT * FROM my_table_holo_jdbc; -- The command returns the following result. +------------+------------+ | user_id | user_name | +------------+------------+ | 9 | hanmeimei | | 4 | tom | | 7 | haward | | 2 | mary | | 5 | lulu | | 12 | alice | | 8 | lilei | | 10 | lily | | 1 | kate | | 11 | lucy | | 6 | mark | | 3 | bob | +------------+------------+ -
Store frequently updated dimension tables in Hologres to support real-time updates. MaxCompute accesses the dimension table using an external table and joins it with a fact table in MaxCompute for federated analysis. The following code provides an example.
-- The following properties are required to access the Hologres external table. SET odps.sql.split.hive.bridge=true; SET odps.sql.hive.compatible=true; -- Create a MaxCompute internal table. CREATE TABLE holo_test AS SELECT * FROM my_table_holo_jdbc; -- Join the MaxCompute internal table with the Hologres external table for analysis. SELECT * FROM my_table_holo_jdbc t1 INNER JOIN holo_test t2 ON t1.user_id=t2.user_id; -- The command returns the following result. +------------+------------+------------+------------+ | user_id | user_name |user_id2 | user_name2 | +------------+------------+------------+------------+ | 9 | hanmeimei | 9 | hanmeimei | | 4 | tom | 4 | tom | | 7 | haward | 7 | haward | | 2 | mary | 2 | mary | | 5 | lulu | 5 | lulu | | 12 | alice | 12 | alice | | 8 | lilei | 8 | lilei | | 10 | lily | 10 | lily | | 1 | kate | 1 | kate | | 11 | lucy | 11 | lucy | | 6 | mark | 6 | mark | | 3 | bob | 3 | bob | +------------+------------+------------+------------+
-
Service-linked role authorization
In this mode, you must create a service-linked role to access Hologres data. To prevent unauthorized access, this mode forwards the user's identity to Hologres for authentication. Therefore, the user must have permissions on both the external table and the underlying Hologres table.
Prerequisites
-
You have a MaxCompute project in which to create the Hologres external table.
For more information about how to create a MaxCompute project, see Create a MaxCompute project.
-
An account with the same name as your MaxCompute account must exist in Hologres. This account needs read permissions on the corresponding table.
-
Only Hologres V1.3 and later support creating Hologres external tables in MaxCompute by using the service-linked role authorization mode. Currently, this mode supports only reading from, not writing to, Hologres external tables.
Create the external table
-
Log on to the MaxCompute client and switch to the target MaxCompute project.
-
Run the following command to create a Hologres external table.
The following example uses the Hologres instance information prepared in Step 1: Prepare Hologres data:
-
Hologres database name:
holo_external_test -
Hologres database schema:
public -
Hologres table name:
holo -
Hologres classic network endpoint:
hgprecn-cn-oew210ut****-cn-hangzhou-internal.hologres.aliyuncs.com:80
CREATE EXTERNAL TABLE IF NOT EXISTS holo_mc_external_dbl ( user_id int, user_name string ) STORED BY 'com.aliyun.odps.jdbc.JdbcStorageHandler' location 'jdbc:postgresql://hgpostcn-cn-****-cn-<region>-internal.hologres.aliyuncs.com:80 /<holo database name>?ApplicationName=MaxCompute ¤tSchema=public&table=<table name>/' TBLPROPERTIES ( 'mcfed.mapreduce.jdbc.driver.class'='org.postgresql.Driver', 'odps.federation.jdbc.target.db.type'='holo', -- If the column names of the Hologres source table and the Hologres external table are different, -- you must add the odps.federation.jdbc.colmapping parameter to map the columns. -- You can omit this parameter if the column names are identical. 'odps.federation.jdbc.colmapping'='user_id:id,user_name:name' ); -
-
Query the external table.
SELECT * FROM holo_mc_external_dbl; -- The command returns the following result. +------------+------------+ | user_id | user_name | +------------+------------+ | 9 | hanmeimei | | 4 | tom | | 7 | haward | | 2 | mary | | 5 | lulu | | 12 | alice | | 8 | lilei | | 10 | lily | | 1 | kate | | 11 | lucy | | 6 | mark | | 3 | bob | +------------+------------+
Enable direct read for Hologres external tables
By default, MaxCompute reads data from Hologres external tables through a JDBC connection. To improve performance, you can enable direct read mode, which allows MaxCompute to access the Hologres storage layer directly. This mode offers several benefits:
-
Improved read stability.
-
Significantly reduces read latency and accelerates data queries.
-
Integrates with predicate pushdown to push query filter conditions to Hologres, reducing the amount of data scanned and lowering costs.
-
Drastically reduces connections to the Hologres frontend (FE), minimizing performance overhead on the Hologres instance. Most queries require only a single connection.
Limitations
Direct read mode has the following limitations. If these conditions are not met, a query automatically falls back to JDBC mode.
-
Version requirements
Your Hologres instance must be V1.3.34 or later. Direct read mode is not supported in earlier versions.
-
Table type restrictions
-
Direct read mode is not supported for Hologres cold storage tables.
-
Direct read mode is not supported for Hologres row-oriented tables.
-
-
Data type mapping limitations
In direct read mode, mapping a Timestamp With Time Zone type in Hologres to a TIMESTAMP type in MaxCompute can cause minor time discrepancies:
-
Time value discrepancy
-
For timestamps before
1900-12-31 15:54:15, the time returned by MaxCompute is ahead by 5 minutes and 44 seconds. -
For timestamps between
1900-12-31 15:54:16and1969-12-31 23:59:58, the time returned by MaxCompute is ahead by 1 second. -
For timestamps after
1969-12-31 23:59:59, the times in Hologres and MaxCompute are identical.
-
-
Time zone offset
-
For example, if the MaxCompute time zone is UTC+8 and a Timestamp With Time Zone column in Hologres is
2000-01-01 00:00:00, MaxCompute returns2000-01-01 08:00:00. -
For example, if the MaxCompute time zone is UTC+8 and a Timestamp With Time Zone column in Hologres is
1969-01-01 00:00:00, MaxCompute returns1969-01-01 08:00:01.
-
-
-
Same-region restriction
Due to network connectivity constraints, MaxCompute can access only Hologres instances in the same region. Cross-region access fails with the following error:
FAILED: ODPS-0010000:System internal error - fuxi job failed, caused by: Pangu request failed with error code 3. -
If your Hologres instance uses a primary/secondary architecture, you can specify only the primary instance in the connection URL, not a secondary instance.
-
Additional limitation for Foreign Server mode: You must enable the schema-level syntax switch for the MaxCompute project.
Enable direct read mode
To enable direct read mode for your session, add the following command before your SQL query in MaxCompute:
SET odps.table.api.enable.holo.table=true;
You can also enable direct read mode at the project level.
-- You can enable direct read and disable JDBC fallback at the project level.
-- Enable direct read at the project level:
setproject odps.table.api.enable.holo.table=true; -- Set to true to enable, false to disable.
-- Disable the default fallback to JDBC:
setproject odps.table.api.allow.fallback.jdbc=false; -- Set to true to allow fallback, false to prevent it.
Verify direct read mode
You can verify that a query used direct read mode by checking the logs in Logview. For more information, see Use Logview 2.0 to view job running information.
On the Summary tab in Logview, find the external holo tables field. The format is:
<project_name>.<table_name>:<access_mode>[<(fallback_reason)>]
Parameters:
|
Parameter |
Description |
|
project_name |
The name of the project. |
|
table_name |
The name of the table. |
|
access_mode |
The access mode for the external table. Valid values:
|
When a job using direct read mode encounters a limitation, it automatically reverts to JDBC mode. Under heavy workloads, this fallback can consume significant resources from the Hologres connection pool. Because data transfer in JDBC mode is less efficient than in direct read mode, resources are released more slowly. This increases the load on your Hologres instance.
Frequent fallbacks can impact other business-critical services that rely on the same Hologres instance. To prevent this, you can disable the automatic fallback mechanism by adding the set odps.table.api.allow.fallback.jdbc=false; parameter to your jobs. This setting causes jobs to fail instead of silently falling back, preventing unexpected performance degradation.
Predicate pushdown for Hologres external tables
Predicate pushdown for regular columns
When MaxCompute accesses a Hologres external table, it can push down query filter conditions on regular columns to Hologres for execution. This process, called predicate pushdown (PPD), filters data at the source to reduce data transfer and improve query performance. The following scenarios are currently supported:
-
Hologres snapshot PPD: Converts the filter expression into a SQL
WHEREclause and passes it to the Hologres server as thefilterparameter of theholo_create_table_snapshot()function. -
Hologres JDBC PPD: Appends the
WHEREclause to the JDBC SQL statement, allowing Hologres to filter data at the SQL layer. -
ORC row group column-level filtering: Performs file-level filtering within MaxCompute. For each ORC file, a
SearchArgumentis built and passed to the ORC Reader. The reader uses column statistics (min, max, and count) to skip row groups that do not match the filter.
The following scenarios will be supported soon:
-
Segment key filtering: After a snapshot is returned from Hologres, MaxCompute parses the min/max segment key from each file's metadata and compares it with the filter expression. If a file's segment key range does not overlap with the filter, the file is skipped.
-
Distribution ID filtering: This feature is only available for Liquid Tables. Each file may contain rows with multiple distribution IDs. After reading a RecordBatch, the system filters rows based on the distribution ID range.
Scenarios
Scenario 1: Hologres snapshot PPD
In a direct read scenario, when you query a logical partition table, MaxCompute converts the filter expression into a SQL WHERE clause. This clause is then passed to the Hologres server as the filter parameter of the holo_create_table_snapshot() function. This allows Hologres to filter files on the server side when creating the snapshot, preventing non-matching files from being returned to MaxCompute and reducing data transfer.
|
Scenario |
Description |
|
Key parameter |
Valid values for the
Syntax example:
|
|
Supported types |
INT, BIGINT, STRING, DATE, TIMESTAMP, BINARY |
|
Limitations |
|
|
Applicable scenarios |
Your Hologres instance must support PPD. This requires one of the following versions:
|
Scenario 2: Hologres JDBC PPD
When Hologres direct read is disabled (odps.table.api.enable.holo.table=false), queries are executed over a JDBC connection. In this case, PPD works by appending the WHERE clause to the JDBC SQL statement sent to Hologres, which then performs filtering at the SQL layer. This mechanism differs from PPD in direct read scenarios:
-
Direct read PPD: Filters files at the snapshot level.
-
JDBC PPD: Filters rows at the SQL query level.
|
Scenario |
Description |
|
Key parameter |
Valid values for the
Syntax example:
|
|
Supported types |
INT, BIGINT, DOUBLE, FLOAT, STRING, DATE, TIMESTAMP, TIMESTAMP_NTZ |
|
Limitations |
|
|
Applicable scenarios |
|
Scenario 3: ORC row group filtering
MaxCompute performs file-level filtering by building a SearchArgument for each ORC file and passing it to the ORC Reader. The reader then uses column statistics (min, max, and count) from each stripe or row group to determine whether the entire row group can be skipped.
-
MaxCompute performs file-level pruning.
-
This feature requires PPD to be enabled on the Hologres side.
-
Supported for non-partitioned tables, physical partition tables, and logical partition tables.
|
Scenario |
Description |
|
Key parameter |
|
|
Supported types |
BOOLEAN, INT, BIGINT, FLOAT, DOUBLE, DECIMAL, STRING, DATE, TIMESTAMP |
Usage examples
Hologres snapshot PPD
-
Prepare the data. Create a test table in your Hologres instance and insert data into it.
CREATE TABLE test_multi_type_ppd ( c_id INT NOT NULL, c_boolean BOOLEAN, c_int INT, c_bigint BIGINT, c_double DOUBLE precision, c_float REAL, c_string TEXT, c_date DATE, c_timestamp_tz TIMESTAMPTZ, c_timestamp_ntz TIMESTAMP ) WITH (SEGMENT_KEY = 'c_id'); -- Insert 10 rows of data. INSERT INTO test_multi_type_ppd VALUES (1, true, 10, 1000, 1.25, 1.5, 'alpha', '2023-01-01', '2023-01-01 10:00:00+08', '2023-01-01 02:00:00'), (2, false, 20, 2000, -2.50, 2.5, 'beta', '2023-01-02', '2023-01-02 10:00:00+08', '2023-01-02 02:00:00'), (3, true, 30, 3000, 3.75, -3.5, 'gamma', '2023-01-03', '2023-01-03 10:00:00+08', '2023-01-03 02:00:00'), (4, false, 40, 4000, 4.00, 4.5, 'delta', '2023-01-04', '2023-01-04 10:00:00+08', '2023-01-04 02:00:00'), (5, true, 50, 5000, 5.50, 5.5, 'epsilon', '2023-01-05', '2023-01-05 10:00:00+08', '2023-01-05 02:00:00'), (6, false, 60, 6000, 6.60, 6.0, 'zeta', '2023-01-06', '2023-01-06 10:00:00+08', '2023-01-06 02:00:00'), (7, true, 70, 7000, 7.70, -7.0, 'eta', '2023-01-07', '2023-01-07 10:00:00+08', '2023-01-07 02:00:00'), (8, false, 80, 8000, 8.80, 8.0, 'theta', '2023-01-08', '2023-01-08 10:00:00+08', '2023-01-08 02:00:00'), (9, true, 90, 9000, -9.90, 9.0, 'iota', '2023-01-09', '2023-01-09 10:00:00+08', '2023-01-09 02:00:00'), (10, false, 100, 10000, 10.10, 10.0, 'kappa', '2023-01-10', '2023-01-10 10:00:00+08', '2023-01-10 02:00:00'); SELECT * FROM test_multi_type_ppd; -
Create a Hologres external table in MaxCompute.
CREATE EXTERNAL TABLE test_multi_type_ppd ( c_id int, c_boolean boolean, c_int int, c_bigint bigint, c_double double, c_float float, c_string string, c_date date, c_timestamp_tz timestamp, c_timestamp_ntz timestamp_ntz ) STORED BY 'com.aliyun.odps.jdbc.JdbcStorageHandler' WITH serdeproperties ('odps.properties.rolearn'='acs:ram::139****:role/<role name>') LOCATION 'jdbc:postgresql://<holo-endpoint>/<db>?currentSchema=public&table=test_multi_type_ppd' TBLPROPERTIES ( 'mcfed.mapreduce.jdbc.driver.class'='org.postgresql.Driver', 'odps.federation.jdbc.target.db.type'='holo' ); -
Query data from the external table.
-- Enable Hologres direct read. SET odps.table.api.enable.holo.table=true; -- Set the PPD mode (off/on/debug). SET odps.external.holo.read.table.rules="{predicate_pushdown_mode:on}"; -- Configure the type system. SET odps.sql.type.system.odps2=true; SET odps.sql.decimal.odps2=true; -- Allow expressions to be pushed down to the TableScan operator. This is typically enabled by default. SET odps.use.builtin.function.input.range=true; -- set odps.mcqa.disable=true; SELECT * FROM test_multi_type_ppd WHERE c_id = 3; -- The command returns the following result. +------+-----------+-------+------------+------------+---------+----------+--------+----------------+-----------------+ | c_id | c_boolean | c_int | c_bigint | c_double | c_float | c_string | c_date | c_timestamp_tz | c_timestamp_ntz | +------+-----------+-------+------------+------------+---------+----------+--------+----------------+-----------------+ | 3 | true | 30 | 3000 | 3.75 | -3.5 | gamma | 2023-01-03 | 2023-01-03 10:00:00 | 2023-01-03 02:00:00 | +------+-----------+-------+------------+------------+---------+----------+--------+----------------+-----------------+ -- In the Summary section of the Logview, a message similar to the following appears: external holo tables: **.default.test_multi_type_ppd:Optimized Holo-side PPD enabled --> Filter pushdown applied successfully Snapshot Id: 56-1-100*** With-filter file count: 2 With-filter create snapshot duration: 19ms
Hologres JDBC PPD
-
Prepare the data. Create a test table in your Hologres instance and insert data into it.
CREATE TABLE test_multi_type_ppd ( c_id INT NOT NULL, c_boolean BOOLEAN, c_int INT, c_bigint BIGINT, c_double DOUBLE PRECISION, c_float REAL, c_string TEXT, c_date DATE, c_timestamp_tz TIMESTAMPTZ, c_timestamp_ntz TIMESTAMP ) WITH (SEGMENT_KEY = 'c_id'); -- Insert 10 rows covering different int/string distributions to facilitate combined queries. INSERT INTO test_multi_type_ppd VALUES (1, TRUE, 10, 1000, 1.25, 1.5, 'alpha', '2023-01-01', '2023-01-01 10:00:00+08', '2023-01-01 02:00:00'), (2, FALSE, 20, 2000, -2.50, 2.5, 'beta', '2023-01-02', '2023-01-02 10:00:00+08', '2023-01-02 02:00:00'), (3, TRUE, 30, 3000, 3.75, -3.5, 'gamma', '2023-01-03', '2023-01-03 10:00:00+08', '2023-01-03 02:00:00'), (4, FALSE, 40, 4000, 4.00, 4.5, 'delta', '2023-01-04', '2023-01-04 10:00:00+08', '2023-01-04 02:00:00'), (5, TRUE, 50, 5000, 5.50, 5.5, 'epsilon', '2023-01-05', '2023-01-05 10:00:00+08', '2023-01-05 02:00:00'), (6, FALSE, 60, 6000, 6.60, 6.0, 'zeta', '2023-01-06', '2023-01-06 10:00:00+08', '2023-01-06 02:00:00'), (7, TRUE, 70, 7000, 7.70, -7.0, 'eta', '2023-01-07', '2023-01-07 10:00:00+08', '2023-01-07 02:00:00'), (8, FALSE, 80, 8000, 8.80, 8.0, 'theta', '2023-01-08', '2023-01-08 10:00:00+08', '2023-01-08 02:00:00'), (9, TRUE, 90, 9000, -9.90, 9.0, 'iota', '2023-01-09', '2023-01-09 10:00:00+08', '2023-01-09 02:00:00'), (10, FALSE, 100, 10000, 10.10, 10.0, 'kappa', '2023-01-10', '2023-01-10 10:00:00+08', '2023-01-10 02:00:00'); -
Create a Hologres external table in MaxCompute.
CREATE EXTERNAL TABLE test_multi_type_ppd ( c_id int, c_boolean boolean, c_int int, c_bigint bigint, c_double double, c_float float, c_string string, c_date date, c_timestamp_tz timestamp, c_timestamp_ntz timestamp_ntz ) STORED BY 'com.aliyun.odps.jdbc.JdbcStorageHandler' WITH serdeproperties ( 'odps.properties.rolearn'='acs:ram::122**79014:role/<role_name>') LOCATION 'jdbc:postgresql://hgprecn-cn-oew210ut****-cn-<region>-internal.hologres.aliyuncs.com:80 /<holo database name>?ApplicationName=MaxCompute ¤tSchema=public&useSSL=true&table=<table name>/' TBLPROPERTIES ( 'mcfed.mapreduce.jdbc.driver.class'='org.postgresql.Driver', 'odps.federation.jdbc.target.db.type'='holo' ); -
Query data from the external table.
-- Disable Hologres direct read to use the JDBC connection. SET odps.table.api.enable.holo.table=false; SET odps.table.api.enable.holo.table.auto.fallback=false; -- Enable JDBC PPD. SET odps.external.hive.scan.ppd.enabled=true; -- Allow expressions to be pushed down to the TableScan operator. This is typically enabled by default. SET odps.use.builtin.function.input.range=true; -- Enable common table. SET odps.sql.common.table.planner.ext.hive.bridge=true; SET odps.sql.common.table.planner.ext.hive.bridge.v2=true; -- After enabling PPD, the TableScan operator reads only one record. SELECT * FROM test_multi_type_ppd WHERE c_id = 3; -- The command returns the following result: +------+-----------+-------+------------+------------+---------+----------+--------+----------------+-----------------+ | c_id | c_boolean | c_int | c_bigint | c_double | c_float | c_string | c_date | c_timestamp_tz | c_timestamp_ntz | +------+-----------+-------+------------+------------+---------+----------+--------+----------------+-----------------+ | 3 | true | 30 | 3000 | 3.75 | -3.5 | gamma | 2023-01-03 | 2023-01-03 10:00:00 | 2023-01-03 02:00:00 | +------+-----------+-------+------------+------------+---------+----------+--------+----------------+-----------------+ -- In the stdout details of the instance in Logview, a message similar to the following appears: [2026-07-22 10:02:41] Volcano instance executor [MainThreadID: 222960] Jdbc HoloRecordReader query conditions: (c_id = 3) JDBC HoloRecordReader: Query predicate pushdown succeeded. JdbcShardRecordReader getSelectQuery: SELECT c_id, c_boolean, c_int, c_bigint, c_double, c_float, c_string, c_date, c_timestamp_tz, c_timestamp_ntz FROM test_multi_type_ppd0721 AS test_multi_type_ppd0721 WHERE ( hg_shard_id = 9) AND ((c_id = 3))
ORC row group filtering
This scenario is best for large datasets. In ORC, every 10,000 rows form a row group. If the dataset is too small, the filtering benefit is limited.
-
Prepare the data. Create a test table in the Hologres instance.
DROP TABLE IF EXISTS test_2m_data; BEGIN; CREATE TABLE test_2m_data ( id INT NOT NULL, name TEXT NOT NULL ); -- Set a distribution key to prevent data skew and improve bulk write performance. CALL set_table_property('test_2m_data', 'distribution_key', 'id'); COMMIT; INSERT INTO test_2m_data (id, name) SELECT g AS id, 'user_' || g AS name FROM generate_series(1, 2000000) AS g; SELECT COUNT(*) FROM test_2m_data; -
Create a Hologres external table in MaxCompute.
CREATE EXTERNAL TABLE ext_holo_test_2m_data ( id INT , name STRING ) STORED BY 'com.aliyun.odps.jdbc.JdbcStorageHandler' WITH serdeproperties ( 'odps.properties.rolearn'='acs:ram::*****:role/<role_name>') LOCATION 'jdbc:postgresql://hgpostcn-cn-2bj4lnv16008-<region>-internal.hologres.aliyuncs.com:80 /<holo_database>?currentSchema=public&table=test_2m_data' TBLPROPERTIES ( 'mcfed.mapreduce.jdbc.driver.class'='org.postgresql.Driver', 'odps.federation.jdbc.target.db.type'='holo' ); -
Query data from the external table.
-- Enable Hologres direct read. SET odps.table.api.enable.holo.table=true; -- Enable PPD. SET odps.external.holo.use.predicate.pushdown=true; -- Set the PPD mode. SET odps.external.holo.read.table.rules="{predicate_pushdown_mode:on}"; SELECT * FROM ext_holo_test_2m_data where id>100 and id<100000;To confirm that filtering was successful, check for the following in the stdout details in Logview:
-
Check TableScan1 for
IOCountandIOLatencyPercentiles. -
TableScan1 checks whether
ReadRowCountis less thanRequestedRowCount
37 Filter1 99899 0 99998998 {"PMS":{"S":24584}} 38 GlobalInit 0 769 0 {"CallBack":{"T":1},"CodeGenDCE":{"T":6393},"CodeGenMajorPass":{"T":13954},"CreateCursor":{"T":41350}, "CreateOperator":{"T":309351},"JitFunction":{"T":13027},"LoadAndParseIR":{"T":38273},"OpenShuffleRW":{"T":30}, "parseTableScanInfo":{"T":4551}} 39 TableScan1 163840 1634 100269 {"PMS":{"S":20480},"actualSplitFileCount":0,"actualSplitFileStats":{"avg":0,"max":0,"min":9223372036854775807}, "plannedSplitFileCount":0,"plannedSplitFileStats":{"avg":0,"max":0,"min":9223372036854775807}} {"DecodingCount":320,"DecodingInBytes":147068,"DecodingLatencyUs":1224,"DecodingOutBytes":1310720, "DeltaFileInclusiveLatencyUs":76,"IOBlockingLatencyUs":424255,"IOCount":60,"IOLatencyPercentiles": "{\"Count\":\"60\",\"Percentiles\":\"[[0,2835], [0.5,6183], [0.9,6183], [0.95,6183], [0.99,6183], [0.999,6752], [1, 15206]]\",\"Total\":\"495889\"}","IOReadBytes":27988440,"MaxMemoryUsageBytes":1936240,"ReadRawSize":2346495, "ReadRowCount":163840,"ReaderCount":1,"ReaderInclusiveLatencyUs":447950,"RequestedRowCount":2000000, "StringDirectReadOutBytes":169113,"Type":"OrcReader","VectorBatchConversionLatencyUs":1636} 40 TableScan1@CommonTableReader 0 0 nan {"PMS":{"S":19456}} 41 fuxi 0 0 nan {"PMS":{"S":0}} 42 inst 0 0 nan {"APMS":{"S":0},"PMFM":{"S":1073741824},"PMS":{"S":3246003}}In the output above, the TableScan1 operator contains the
IOCountandIOLatencyPercentilesfields, and theReadRowCount(163840) is less than theRequestedRowCount(2000000). This indicates that the predicate pushdown was successful. -
Partition pruning
MaxCompute does not support creating external tables for Hologres partitioned tables. This topic describes how to create external tables in MaxCompute to read data from Hologres physical partition tables and logical partition tables and implement partition pruning.
Hologres currently supports two types of partitioned tables.
-
Hologres physical partition table
A parent table is divided into different child tables based on the values of the partition key. The child tables are externally visible. Before you use a partitioned table, you must create its child tables. Use the CREATE PARTITION TABLE command to create a partitioned table. Use the dynamic partition management feature to automatically create partitioned child tables.
-
Hologres logical partition table
Hologres V3.1 and later support logical partition tables (LOGICAL PARTITION TABLE). The parent table is a physical table, while the child tables are logical concepts. For more information, see CREATE LOGICAL PARTITION TABLE.
For physical partition tables
Partition pruning is supported only for Hologres physical partitioned parent tables. To read data from a partitioned child table, you must create a separate external table. This scenario involves only predicate pushdown (PPD) and not partition pruning.
-
Prepare data in Hologres
-
Create a Hologres external table. Do not specify a partition field. Instead, define the partition field as a regular column.
-
Implement partition pruning
-
JDBC read mode
Add pushdown parameters when you query the external table partition to implement partition pruning. For more information, see Scenario 2: Hologres JDBC PPD.
-- Disable Hologres direct read to use the JDBC link. SET odps.table.api.enable.holo.table=false; SET odps.table.api.enable.holo.table.auto.fallback=false; -- Enable JDBC PPD. SET odps.external.hive.scan.ppd.enabled=true; -- Allow expressions to be pushed down to TableScan. This is enabled by default. SET odps.use.builtin.function.input.range=true; -- Enable common table. SET odps.sql.common.table.planner.ext.hive.bridge=true; SET odps.sql.common.table.planner.ext.hive.bridge.v2=true; SELECT * FROM test_partition_table WHERE ds = '202401'; -- You can view the filter information in the Summary section of the Log view. (The original table has 6 records, but only 2 are queried.) inputs: project_name.test_partition_table: 2 (0 bytes) outputs: SQL_0_1_0_job_0:AdhocSink1: 2 (2659 bytes) -
Direct read mode
You can directly specify query conditions to implement partition pruning.
SET odps.table.api.enable.holo.table=true; SELECT * FROM test_partition_table WHERE ds = '202401'; -- You can view the results in the Summary section of the Log view. (The original table has 6 records, but only 2 are queried.) inputs: project_name.test_partition_table: 2 (5521 bytes) outputs: SQL_0_1_0_job_0:AdhocSink1: 2 (1434 bytes)
-
For logical partition tables
-
Prepare data in Hologres
-
Create a Hologres external table. Do not specify a partition field. Instead, define the partition field as a regular column.
-
Implement partition pruning
-
JDBC read mode
Add PPD parameters when you query the external table partition data to implement partition pruning. For more information, see Scenario 2: Hologres JDBC PPD.
-- Disable Hologres direct read to use the JDBC link. SET odps.table.api.enable.holo.table=false; SET odps.table.api.enable.holo.table.auto.fallback=false; -- Enable JDBC PPD. SET odps.external.hive.scan.ppd.enabled=true; -- Allow expressions to be pushed down to TableScan. This is enabled by default. SET odps.use.builtin.function.input.range=true; -- Enable common table. SET odps.sql.common.table.planner.ext.hive.bridge=true; SET odps.sql.common.table.planner.ext.hive.bridge.v2=true; SELECT * FROM dwd_trade_orders_partitioned WHERE dt = '20231103'; -- You can view the filtered inputs in the Summary section of the Log view. (The original table has 10 records, but only 4 are queried.) inputs: project_name.dwd_trade_orders_partitioned: 4 (0 bytes) outputs: SQL_0_1_0_job_0:AdhocSink1: 4 (3243 bytes) -- Other filter information. Check the stdout details of the instance in the Log view. The following information is displayed: Jdbc HoloRecordReader query conditions: (dt = '20231103') JDBC HoloRecordReader: Query predicate pushdown succeeded. JdbcShardRecordReader getSelectQuery: SELECT order_id, shop_id, user_id, payment_type, dt, order_time FROM "public".dwd_trade_orders_partitioned AS dwd_trade_orders_partitioned WHERE ( hg_shard_id = 15) AND ((dt = '20231103')) -
Direct read mode for external tables
Specify pushdown parameters when you query the external table partition data to implement partition pruning. For more information, see Scenario 1: Hologres Snapshot PPD.
-- Enable Hologres direct read. SET odps.table.api.enable.holo.table=true; -- Set the PPD mode (off/on/debug). SET odps.external.holo.read.table.rules="{predicate_pushdown_mode:on}"; -- Type system settings. SET odps.sql.type.system.odps2=true; SET odps.sql.decimal.odps2=true; -- Allow expressions to be pushed down to TableScan. This is enabled by default. SET odps.use.builtin.function.input.range=true; SELECT * FROM dwd_trade_orders_partitioned WHERE dt = '20231103'; -- You can view the filter information in the Summary section of the Log view. (The original table has 10 records, but only 4 are queried.) inputs: project_name.dwd_trade_orders_partitioned: 4 (6446 bytes) outputs: SQL_0_1_0_job_0:AdhocSink1: 4 (1176 bytes) external holo tables: project_name.default.dwd_trade_orders_partitioned:Optimized Holo-side PPD enabled --> Filter pushdown applied successfully Snapshot Id: 40-1-1001004824714724501 With-filter file count: 3 With-filter create snapshot duration: 20ms
-
Hologres data import and export
Supported data types
-
DECIMAL type limitations:
For a Hologres external table created in MaxCompute, the
DECIMALdata type is fixed todecimal(38,18)and cannot be modified. If the source table has fewer decimal places, you can define the column as theSTRINGtype in the MaxCompute external table and use theCASTfunction to explicitly cast the data when it is used. -
Complex type limitations:
Hologres external tables created in MaxCompute do not support complex data types such as
MapandStruct. TheArraytype is supported with limitations, as detailed in the table that follows. -
Other incompatible types:
Hologres data types such as
MONEYare not supported because they lack a corresponding type in MaxCompute.
The following table lists the data type mappings between Hologres and MaxCompute.
|
Hologres data type |
MaxCompute data type |
JDBC read |
JDBC write |
Direct read (odps.table.api.enable.holo.table=true) |
Description |
|
INTEGER (alias: INT or INT4) |
INT |
|
|
|
A 32-bit signed integer. |
|
TEXT |
STRING, VARCHAR |
|
|
|
A string type with a current length limit of 8 MB. |
|
SMALLINT |
SMALLINT |
|
|
|
A 16-bit signed integer. |
|
INT2 |
SMALLINT |
|
|
|
A 16-bit signed integer. |
|
BIGINT (alias: INT8) |
BIGINT |
|
|
|
A 64-bit signed integer. |
|
BYTEA |
BINARY |
|
|
|
A binary data type. This mapping has a length limit of 8 MB, while the native |
|
REAL (alias: FLOAT4) |
FLOAT |
|
|
|
A 32-bit binary floating-point number. |
|
DOUBLE PRECISION (alias: FLOAT8) |
DOUBLE |
|
|
|
A 64-bit binary floating-point number. |
|
BOOLEAN (alias: BOOL) |
BOOLEAN |
|
|
|
A Boolean value ( |
|
TIMESTAMP |
TIMESTAMP_NTZ |
|
|
|
The Hologres It maps to the |
|
TIMESTAMP WITH TIME ZONE (alias: TIMESTAMPTZ) |
TIMESTAMP |
|
|
|
The Hologres The system handles precision conversion, but the time zone information is lost. |
|
DECIMAL (alias: NUMERIC) |
DECIMAL(precision,scale) |
|
|
|
A base-10 exact numeric type.
The |
|
CHAR(n) |
CHAR(n) |
|
|
|
In MaxCompute, In Hologres, |
|
VARCHAR(n) |
VARCHAR(n) |
|
|
|
MaxCompute's In Hologres, the maximum size of |
|
DATE |
DATE |
|
|
|
Date type. The format is |
|
INT4[] |
ARRAY<INT> |
|
|
|
Hologres arrays map to the corresponding MaxCompute |
|
INT8[] |
ARRAY<BIGINT> |
|
|
|
|
|
FLOAT4[] |
ARRAY<FLOAT> |
|
|
|
|
|
FLOAT8[] |
ARRAY<DOUBLE> |
|
|
|
|
|
BOOLEAN[] |
ARRAY<BOOLEAN> |
|
|
|
|
|
TEXT[] |
ARRAY<STRING> |
|
|
|
|
|
JSONB |
JSON |
|
|
|
Hologres |
|
JSON |
STRING |
|
|
|
In JDBC mode, the Hologres |
|
SERIAL (auto-increment) |
INT |
|
|
|
MaxCompute reads Hologres |
|
RoaringBitmap |
Not supported |
|
|
|
MaxCompute has no corresponding type. |
|
RoaringBitmap64 |
Not supported |
|
|
|
MaxCompute has no corresponding type. |
|
BIT(n) |
Not supported |
|
|
|
MaxCompute has no corresponding type. |
|
VARBIT(n) |
Not supported |
|
|
|
MaxCompute has no corresponding type. |
|
INTERVAL |
Not supported |
|
|
|
MaxCompute has no corresponding type. |
|
TIMETZ |
Not supported |
|
|
|
MaxCompute has no corresponding type. |
|
TIME |
Not supported |
|
|
|
MaxCompute has no corresponding type. |
|
INET |
Not supported |
|
|
|
MaxCompute has no corresponding type. |
|
MONEY |
Not supported |
|
|
|
MaxCompute has no corresponding type. |
|
OID |
Not supported |
|
|
|
MaxCompute has no corresponding type. |
|
UUID |
Not supported |
|
|
|
MaxCompute has no corresponding type. |
FAQ
ODPS-0130071 error in direct read mode
-
Problem
When you read data from Hologres in direct read mode, the job fails with an error similar to
ODPS-0130071 Failed to split to equal size...max count: 7777:ODPS-0130071:[0,0] Semantic analysis exception - physical plan generation failed: storage/table/src/input_splits_builder.cpp(195): StorageException: Failed to split to equal size, total size: 2143570729934, min size: 268435456, max size: 272629760, max count: 7777, split size: 275629513, split count: 7777 -
Cause
MaxCompute's default data splitting strategy for mappers divides the input data volume by a
split.sizeof 256 MB. This can cause the number of concurrent mappers to exceed the maximum of 7,777.NoteThis limit prevents jobs from creating too many mappers, protecting the stability of Hologres files and network connections.
-
Solution
To resolve this error, set the following parameters.
SET odps.external.holo.mapper.instances=10000; -- Increases the concurrency limit to a value no greater than 10,000. SET odps.sql.mapper.split.size=512; -- Adjusts the mapper split size to a value no greater than 512 MB.
Slow SQL jobs on Hologres external tables
-
Problem
Performance is slow when you use an SDK to query data in a MaxCompute external table.
-
Solution
Queries on external tables are slow because external tables support only full table scans. For better performance, use a MaxCompute internal table instead.
Error with keyword column names
-
Problem
If a column name in your Hologres table is a keyword, queries fail with the following errors:
ODPS-0123131:User defined function exception - SQLException in nextKeyValueCaused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "," -
Solution
Add the
odps.federation.jdbc.colmappingparameter to map the columns between the Hologres source table and the Hologres external table.For example, if the Hologres source table has a column named
"offset", which is a keyword, add the'odps.federation.jdbc.colmapping'='offset:"offset"'parameter when you create the Hologres external table.
Why, when I use the service-linked role authorization mode to read an external table, is the error reported password authentication failed for user "mcslr$STS.******", and how do I resolve it?
-
Problem
When a RAM user or a user who assumes a RAM role reads a Hologres external table in service-linked role authorization mode, the job fails with the
password authentication failed for user "mcslr$STS.******"error.Caused by: java.lang.RuntimeException: java.lang.RuntimeException: org.postgresql.util.PSQLException: ERROR: pooler: c0a981a2413b5: password authentication failed for user "*******$V2#****#TMP.3KvMuCpXhuMbeZsbpaE****m738WfEw73rze ohJUeXxhsKza1khCxdKSJQAU5CFac*****qDCA#STS.NZFkZ3TbJNmAjS2TKLdUHHdiW#**.*.*.***" at org.apache.hadoop.mapreduce.lib.db.DBInputFormat.setConf(DBInputFormat.java:171) at com.aliyun.odps.jdbc.wrapper.InputFormatWrapper.checkAndInitDbInputFormat(InputFormatWrapper.java:48) ... 45 more Caused by: java.lang.RuntimeException: org.postgresql.util.PSQLException: ERROR: pooler: c0a981a2413b5: password authentication failed for user "*******$V2#****#TMP.3KvMuCpXhuMbeZsbpaE****m738WfEw73rzeohJUeXxhsKza1khCxdKSJQAU 5CFac*****qDCA#STS.NZFkZ3TbJNmAjS2TKLdUHHdiW#**.*.*.***" at org.apache.hadoop.mapreduce.lib.db.DBInputFormat.createConnection(DBInputFormat.java:205) at com.aliyun.odps.jdbc.impl.JdbcDBInputFormat.superCreateConnection(JdbcDBInputFormat.java:69) at com.aliyun.odps.jdbc.impl.JdbcDBInputFormat.createConnection(JdbcDBInputFormat.java:56) at org.apache.hadoop.mapreduce.lib.db.DBInputFormat.setConf(DBInputFormat.java:164) -
This error occurs because the service-linked role authorization mode was not applied successfully.
-
Solution
Add the
set odps.external.holo.enable.slr = force;parameter before the SQL statement to enforce the service-linked role authorization mode.
PPD error in direct read modeHolo-side PPD disabled --> The Holo snapshot PPD mode is enabled, but FAILED to push down the filter into Holo
-
Error message
Holo-side PPD disabled --> The Holo snapshot PPD mode is enabled, but FAILED to push down the filter into Holo, errorMsg[storage/table/src/holo/snapshot.cpp(319): StorageException: Create/Get snapshot failed for table: test_multi_type_ppd partition: Query:select holo_create_table_snapshot('public.test_multi_type_ppd', 259200, filter=>$$c_id = 3$$) with error ERROR: internal error: Failed to build fragment dag desc: node type 65 is not supported -
Solution
Ensure that your Hologres internal table meets the requirements in Usage notes.
Troubleshooting fallback to JDBC mode
On the Summary tab in Logview, if the external holo tables section displays "Fallback", it indicates that the query did not run in direct read mode and fell back to JDBC mode. The following table lists common causes and solutions.
|
Error details |
Root cause |
Solution |
|
Column name <col> Odps type <X> Holo type <Y>. Examples:
|
Column type mapping error |
The column types of the Hologres external table do not match the source table. To enable direct read mode, modify the data types in the external table according to the data type mapping. |
|
Odps table schema invalid |
This is a general error. For details, see the Error Logs in Logview. |
If the issue persists, join the MaxCompute developer community on DingTalk for support by clicking the application link or searching for group ID |
|
Holo connection error |
Hologres instance connection error |
This error can be caused by incorrect permissions or an abnormal Hologres instance status. Verify that the current user has the required permissions to access the Hologres database. Also, check whether the Hologres instance is in a serviceable state. |
|
Select hg_version error/Hologres version check error/Fetch hg_version data error |
Incorrect Hologres instance version |
Upgrade the Hologres instance to V1.3.34 or later. For more information about how to upgrade an instance, see Upgrade an instance. |
Related documents
-
For common issues with Hologres foreign tables, see Lakehouse and Foreign Table FAQ.
-
To create a cross-account Hologres foreign table, see Create a cross-account Hologres foreign table.