MaxCompute:Hologres external tables

DML restrictions

• UPDATE and DELETE are not supported on Hologres external tables.

• INSERT OVERWRITE is not supported. To overwrite data from MaxCompute into Hologres, read the external table that maps to the MaxCompute table from Hologres and use Hologres's INSERT OVERWRITE semantics.

• The upsert (INSERT ON CONFLICT) mechanism of Hologres is not supported when writing to an external table. If the Hologres source table has a primary key, the data you write must not violate the primary key uniqueness constraint.

Write risk

Parallel writes across multiple processes to a Hologres external table may occasionally cause data duplication.

Table type restrictions

• Hologres external tables do not support the cluster property.

• Hologres external tables cannot be mapped to Dynamic Tables in Hologres.

• Partitioned tables in Hologres do not correspond to partitioned tables in MaxCompute. Hologres external tables do not support partitions. Exception: in direct read mode, if you query a Hologres external table mapped to a partitioned parent table in Hologres, partition pruning applies when the query conditions match the partition key columns of the child tables.

• Both parent and child tables in Hologres can be mapped to a Hologres external table, but parent tables are read-only.

Schema compatibility

If the schema of the Hologres source table differs from that of the external table:

• Fewer columns in Hologres than in the DDL: An error occurs when reading. For example: column "xxx" does not exist.

• More columns in Hologres than in the DDL: Extra columns are ignored.

• Incompatible column types: MaxCompute does not support using the INT type to receive STRING data from Hologres. Using STRING to receive INT data is allowed but not recommended.

Prerequisites

Before you begin, ensure that you have:

• Installed and configured the MaxCompute client

• A MaxCompute project. See Create a MaxCompute project

• (STS mode only) A RAM role authorized to access the Hologres instance. See Authorize a regular RAM role in STS mode for Hologres

• (Double-signature mode only) An account with the same name as your MaxCompute account in Hologres, with read and write permissions on the target table; Hologres V1.3 or later

Syntax

All examples use the com.aliyun.odps.jdbc.JdbcStorageHandler StorageHandler and the org.postgresql.Driver PostgreSQL JDBC driver. The two modes differ in how authentication is specified.

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'='<ram_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',
  'odps.federation.jdbc.colmapping'='<table_column1>:<source_column1>, <table_column2>:<source_column2>,...'
);

-- Enable double-signature mode.
SET odps.sql.common.table.planner.ext.hive.bridge=true;

-- 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',
  ['odps.federation.jdbc.colmapping'='<table_column1>:<source_column1>, <table_column2>:<source_column2>,...']
);

Parameters

Prepare sample data

Skip this step if you already have a Hologres database, table, and data.

Create a Hologres database:

1. Log on to the Hologres console and select the region.Hologres Management Console

2. In the left navigation pane, click Instances.

3. If you don't have an instance, purchase one. Then click the instance name.

4. On the instance details page, click Connect to Instance.

5. Click the Metadata Management tab, then click Create Database. Enter a database name and keep the other defaults.

Create a Hologres table with sample data:

1. On the instance details page, click Connect to Instance, then click the SQL Editor tab.

2. 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;

Create and query a Hologres external table (STS mode)

1. Log on to the MaxCompute client and switch to your project.

2. Create the external table:

   CREATE EXTERNAL TABLE IF NOT EXISTS my_table_holo_jdbc
   (
     id   BIGINT,
     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-hangzhou-internal.hologres.aliyuncs.com:80/<holo database name>?ApplicationName=MaxCompute&currentSchema=public&useSSL=true&table=<table name>/'
   TBLPROPERTIES (
     'mcfed.mapreduce.jdbc.driver.class'='org.postgresql.Driver',
     'odps.federation.jdbc.target.db.type'='holo',
     'odps.federation.jdbc.colmapping'='id:id,name:name'
   );

3. Query the external table:

   -- Required session properties for accessing a Hologres external table.
   SET odps.sql.split.hive.bridge=true;
   SET odps.sql.hive.compatible=true;
   SET odps.table.api.enable.holo.table=true; -- Enable direct read mode.
   
   SELECT * FROM my_table_holo_jdbc LIMIT 10;

   Expected output:

   +------------+------------+
   | id         | name       |
   +------------+------------+
   | 9          | hanmeimei  |
   | 4          | tom        |
   | 7          | haward     |
   | 2          | mary       |
   | 5          | lulu       |
   | 8          | lilei      |
   | 10         | lily       |
   | 1          | kate       |
   | 6          | mark       |
   | 11         | lucy       |
   +------------+------------+

4. Write processed data back to Hologres:

   SET odps.sql.split.hive.bridge=true;
   SET odps.sql.hive.compatible=true;
   SET odps.table.api.enable.holo.table=true;
   
   INSERT INTO my_table_holo_jdbc VALUES (12, 'alice');

5. Join the Hologres external table with a MaxCompute internal table for federated analysis:

   SET odps.sql.split.hive.bridge=true;
   SET odps.sql.hive.compatible=true;
   
   -- Create a MaxCompute internal table from the external table.
   CREATE TABLE holo_test AS SELECT * FROM my_table_holo_jdbc;
   
   -- Join the internal table and the external table.
   SELECT * FROM my_table_holo_jdbc t1 INNER JOIN holo_test t2 ON t1.id = t2.id;

Create and query a Hologres external table (double-signature mode)

Double-signature is an authentication protocol jointly developed by MaxCompute and Hologres. MaxCompute signs your account credentials and passes them to Hologres. Hologres authenticates the same account based on the agreed protocol, so you can access the external table without configuring additional credentials.

1. Log on to the MaxCompute client and switch to your project.

2. Create the external table:

   -- Enable double-signature mode before creating the table.
   SET odps.sql.common.table.planner.ext.hive.bridge=true;
   
   CREATE EXTERNAL TABLE IF NOT EXISTS holo_mc_external_dbl
   (
     id   INT,
     name STRING
   )
   STORED BY 'com.aliyun.odps.jdbc.JdbcStorageHandler'
   LOCATION 'jdbc:postgresql://hgpostcn-cn-****-cn-hangzhou-internal.hologres.aliyuncs.com:80/<holo database name>?ApplicationName=MaxCompute&currentSchema=public&preferQueryMode=simple&useSSL=false&table=<table name>/'
   TBLPROPERTIES (
     'mcfed.mapreduce.jdbc.driver.class'='org.postgresql.Driver',
     'odps.federation.jdbc.target.db.type'='holo',
     'odps.federation.jdbc.colmapping'='id:id,name:name'
   );

3. Query the external table. The SET command must be run in the same session as the query:

   SET odps.sql.common.table.planner.ext.hive.bridge=true;
   SELECT * FROM holo_mc_external_dbl;

Requirements

Enable direct read

Add the following parameter before each query to enable direct read at the statement level:

SET odps.table.api.enable.holo.table=true;

To enable direct read at the project level:

-- Enable direct read for all queries in the project.
setproject odps.table.api.enable.holo.table=true;  -- true: enable, false: disable

-- Disable automatic fallback to JDBC mode.
setproject odps.table.api.allow.fallback.jdbc=false;  -- true: fall back, false: do not fall back

Verify the read mode

View the job logs in Logview to confirm whether a query used direct read mode. For details on using Logview, see Use Logview V2.0 to view job information.

On the Summary tab, locate the external holo tables field. The format is:

<project_name>.<table_name>:<Access mode>[(<Fallback reason>)]

Fallback reasons and solutions:

Handling fallback under heavy load:

If a task running in direct read mode hits a limit and falls back to JDBC mode during heavy load, the fallback consumes Hologres connection pool resources and data transmission efficiency drops. To prevent unexpected impacts on other services, disable automatic fallback:

SET odps.table.api.allow.fallback.jdbc=false;

Type limits

• DECIMAL: Fixed at DECIMAL(38,18). If the Hologres source table uses fewer decimal places, define the column as STRING in the external table and use CAST for conversion when needed.

• Complex types: ARRAY, MAP, and STRUCT are not supported in external tables. Array types (INT4[], INT8[], etc.) are supported only in JDBC read and direct read—not in JDBC write.

• Unsupported types: Hologres types with no MaxCompute equivalent—including MONEY, OID, UUID, BIT(n), VARBIT(n), INTERVAL, TIMETZ, TIME, INET, RoaringBitmap, and RoaringBitmap64—cannot be read or written through external tables. Queries against columns of these types return an error.

Type mapping table

ODPS-0130071 error when directly reading from Hologres

When MaxCompute directly reads from Hologres, the following error appears:

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

MaxCompute uses a default mapper split policy of input data volume / 256 MB. For large tables, this generates more concurrent mappers than the 7,777 limit. Increase the limit or the split size:

SET odps.external.holo.mapper.instances=10000;  -- Maximum: 10,000
SET odps.sql.mapper.split.size=512;             -- Maximum: 512 MB

Slow queries on a Hologres external table

External tables support only full table scans. If you need fast lookup performance, load the data into a MaxCompute internal table instead.

Keyword used as a column name causes a syntax error

If a Hologres column name is a reserved keyword, the following errors appear:

ODPS-0123131:User defined function exception - SQLException in nextKeyValue
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near ","

Use odps.federation.jdbc.colmapping to map the Hologres field explicitly. For example, if the Hologres column is named offset (a reserved keyword):

'odps.federation.jdbc.colmapping'='offset:"offset"'