All Products
Search
Document Center

MaxCompute:Hologres external tables

Last Updated:Oct 27, 2026

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 UPDATE or DELETE operations on Hologres external tables.

    • Hologres external tables do not support the INSERT OVERWRITE statement.

      To overwrite data in a Hologres table with data from MaxCompute, run an INSERT OVERWRITE statement 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 cluster attribute.

  • 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.rolearn parameter. Create a RAM role, grant it permissions to access the Hologres database, and then set the role's ARN in the odps.properties.rolearn parameter.

    • 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.

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 exist when 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

Click to view parameter details

Parameter

Required

Description

IF NOT EXISTS

No

  • If you do not specify the IF NOT EXISTS option and a table with the same name already exists, an error is reported.

  • If you specify IF NOT EXISTS, the statement executes successfully regardless of whether a table with the same name exists. The metadata of the existing table is not modified, even if its schema differs from the new table's schema.

table_name

Yes

The name of the Hologres external table that you want to create in MaxCompute.

col_name

Yes

The name of a column in the Hologres external table.

data_type

Yes

The data type of a column in the Hologres external table.

STORED BY

Yes

Specifies the StorageHandler that defines how to query the Hologres external table.

The value is fixed at com.aliyun.odps.jdbc.JdbcStorageHandler, which indicates that the JDBC connection method is used.

role_arn

Yes

Required when using the RAM role authorization mode. This parameter specifies the ARN of the RAM role for authentication. For more information about how to create a RAM role and grant permissions, see RAM role authorization for Hologres.

  1. Log in to the RAM console.

  2. In the left navigation bar, select Identities > Roles.

  3. On the Roles page, click the target Role Name to go to the role details page.

  4. In the Basic Information section, you can obtain the ARN.

To create a cross-account Hologres external table, see the tutorial in Create a cross-account Hologres external table.

No

This parameter is not required when using the service-linked role authorization mode.

LOCATION

Yes

The JDBC connection URL of the Hologres instance. The URL contains the following parameters:

  • endpoint: Required. The classic network domain name of the Hologres instance.

    Important

    Currently, you can connect to Hologres only over the classic network. Connections using a VPC network domain name are not supported.

  • port: Required. The network port of the Hologres instance.

    1. Log on to the Hologres management console. In the upper-left corner, select a region.

    2. In the navigation pane on the left, click Instances.

      On the Instances page, click the name of the target instance.

    3. In the Network Information section, find the classic network domain name and port of the Hologres instance.

  • database: Required. The name of the Hologres database to which you want to connect. For more information about Hologres databases, see CREATE DATABASE.

  • ApplicationName: Required. The default value is MaxCompute. Do not change this value.

  • schema: Optional. You can omit this parameter if the table name is unique within the database or if the source table is in the default schema. For more information about schemas, see CREATE SCHEMA.

  • holo_table_name: Required. The name of the Hologres source table. For more information about Hologres source tables, see CREATE TABLE.

  • useSSL: Optional. Specifies whether to enable SSL for the classic network connection from MaxCompute to Hologres.

    • true: enables SSL for secure and encrypted connections.

    • false: disables SSL.

tblproperties

Yes

  • mcfed.mapreduce.jdbc.driver.class: Required.

    Specifies the driver for connecting to the Hologres database. The value must be org.postgresql.Driver.

  • odps.federation.jdbc.target.db.type: Required.

    Specifies the target database type. The value must be holo.

  • odps.federation.jdbc.colmapping: Optional.

    To map only a subset of columns, configure this parameter to specify the mapping between the source table columns and the external table columns.

    • If you do not configure this parameter, MaxCompute maps columns by name between the source table and the Hologres external table.

    • If you configure this parameter for only some columns, the specified columns are mapped as defined, while the remaining columns are mapped by name. An error occurs if the names or data types of the automatically mapped columns do not match.

    • If you configure this parameter and a Hologres column name contains uppercase letters, you must enclose the column name in double quotation marks (""). Format: MaxCompute_column_1:"Hologres_column_1"[, MaxCompute_column_2:"Hologres_column_2", ...].

      Note

      Assume that the Hologres source table contains the columns c bool, map_B string, a bigint, and the Hologres external table contains the columns a bigint, x string, c bool.

      If colmapping is set to 'x:"map_B"', the data can be successfully mapped and queried from Hologres.

  • mcfed.mapreduce.jdbc.input.query: Optional.

    Reads data from the Hologres source table. The external table's columns, column names, and data types must match those returned by the query. If the query uses an alias, the external table definition must match the alias. The select_sentence must be in the SELECT xxx FROM <holo_database_name>.<holo_schema_name>.<holo_table_name> format.

  • mcfed.holo.read.freshness.seconds: Optional.

    • When you directly read data from Hologres, you can configure the freshness parameter to control data freshness. This ensures that the generated snapshot contains at least all data from the now() - freshness point in time. This can reduce the generation of small files and improve query performance.

    • The default value is 60 seconds. The valid range is ≥ -1 and ≤ 86400. The closer the value of this parameter is to 0, the more frequently MaxCompute queries the Hologres table, which increases the load on Hologres. Set this parameter based on your requirements.

    • You can also set this parameter at the session level by adding the SET odps.external.holo.read.freshness.seconds = <num>; statement before your SQL statement.

    • Parameter priority: session-level setting > table-level setting > Hologres GUC default value (60 seconds).

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

  1. Log on to the Hologres management console. In the upper-left corner, select a region.

  2. In the navigation pane on the left, click Instances.

  3. 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.

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

  5. 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

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

  2. Click the SQL Editor tab.

  3. 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

  1. You have installed and configured the MaxCompute client.

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

  3. You have created and authorized a RAM role. For more information, see RAM role authorization for Hologres.

Create the external table

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

  2. 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
      &currentSchema=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'
    );
  3. 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       | 
    +------------+------------+
  4. 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

  1. You have installed and configured the MaxCompute client.

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

  3. An account with the same name as your MaxCompute account must exist in Hologres. This account needs read permissions on the corresponding table.

  4. 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

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

  2. 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
      &currentSchema=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'
    );
  3. 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.

  1. Version requirements

    Your Hologres instance must be V1.3.34 or later. Direct read mode is not supported in earlier versions.

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

  3. 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:16 and 1969-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 returns 2000-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 returns 1969-01-01 08:00:01.

  4. 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.

  5. If your Hologres instance uses a primary/secondary architecture, you can specify only the primary instance in the connection URL, not a secondary instance.

  6. 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:

  • Optimized: Indicates that the query ran in direct read mode. In Logview, the entry on the Summary tab appears as external holo tables: <project_name>.<table_name>:Optimized.

  • Fallback indicates that the system fell back to JDBC mode. For example, on the Summary tab in Logview, if the external holo tables section displays a Fallback message such as aliorc_test.holo_lineitem:Fallback (Column type map error Column name l_orderkey Odps type 3 Holo type 4), it indicates that the system did not use the direct read mode and instead fell back to Fallback mode due to a column type mapping error. For more information about the reasons for a fallback, see Fallback in direct read mode.

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 WHERE clause and passes it to the Hologres server as the filter parameter of the holo_create_table_snapshot() function.

  • Hologres JDBC PPD: Appends the WHERE clause 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 SearchArgument is 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 predicate_pushdown_mode parameter:

  • off: Disables predicate pushdown. All data is read. Use this option for debugging and comparison in development environments.

  • on: Enables predicate pushdown. Only a filtered snapshot is created. This setting is recommended for production environments.

  • debug: Creates two snapshots to compare performance. Use this option for performance testing and troubleshooting.

Syntax example:

set odps.external.holo.read.table.rules="{predicate_pushdown_mode:on}";

Supported types

INT, BIGINT, STRING, DATE, TIMESTAMP, BINARY

Limitations

  • UDFs and built-in functions are not supported.

  • CAST expressions that rely on implicit type conversion are not supported.

  • The jsonb type is not supported.

  • Hologres primary key tables are not supported.

Applicable scenarios

Your Hologres instance must support PPD. This requires one of the following versions:

  • >=3.1.36

  • >=3.2.18

  • >=4.0.7

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 odps.external.hive.scan.ppd.enabled parameter:

  • true: Enables predicate pushdown.

  • false: Disables predicate pushdown.

Syntax example:

set odps.external.hive.scan.ppd.enabled=true;

Supported types

INT, BIGINT, DOUBLE, FLOAT, STRING, DATE, TIMESTAMP, TIMESTAMP_NTZ

Limitations

  • UDFs and built-in functions are not supported.

  • CAST expressions are not supported.

  • The jsonb type is not supported.

  • Hologres primary key tables are not supported.

Applicable scenarios

  • When your Hologres instance does not support PPD.

  • As a fallback solution for direct read 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

odps.external.holo.use.predicate.pushdown

  • true: Enables filtering.

  • false: Disables filtering (default).

Supported types

BOOLEAN, INT, BIGINT, FLOAT, DOUBLE, DECIMAL, STRING, DATE, TIMESTAMP

Usage examples

Hologres snapshot PPD

  1. 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;
  2. 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'
    );
  3. 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

  1. 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');
  2. 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
      &currentSchema=public&useSSL=true&table=<table name>/'
    TBLPROPERTIES (
      'mcfed.mapreduce.jdbc.driver.class'='org.postgresql.Driver',
      'odps.federation.jdbc.target.db.type'='holo'
    );
  3. 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.

  1. 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;
  2. 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'
    );
  3. 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 IOCount and IOLatencyPercentiles.

    • TableScan1 checks whether ReadRowCount is less than RequestedRowCount

    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 IOCount and IOLatencyPercentiles fields, and the ReadRowCount (163840) is less than the RequestedRowCount (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.

  1. Prepare data in Hologres

    Prepare data in Hologres

    -- Create a partitioned parent table.
    CREATE TABLE test_partition_table (
        id          BIGINT      NOT NULL,
        name        VARCHAR(64) NOT NULL,
        code        CHAR(10),
        description TEXT,
        level       INT,
        score       SMALLINT,
        age         INT,
        ds          TEXT        NOT NULL   -- Partition key. In Hologres, the partition key must be included in the table.
    )
    PARTITION BY LIST(ds);
    
    -- Set properties (optional, adjust as needed).
    CALL set_table_property('test_partition_table', 'orientation', 'column');
    CALL set_table_property('test_partition_table', 'bitmap_columns', 'name,code');
    
    -- Partition 1: 202401
    CREATE TABLE test_partition_table_202401 
    PARTITION OF test_partition_table FOR VALUES IN ('202401');
    
    -- Partition 2: 202402
    CREATE TABLE test_partition_table_202402 
    PARTITION OF test_partition_table FOR VALUES IN ('202402');
    
    -- Partition 3: 202403
    CREATE TABLE test_partition_table_202403 
    PARTITION OF test_partition_table FOR VALUES IN ('202403');
    
    INSERT INTO test_partition_table (id, name, code, description, level, score, age, ds) VALUES
    -- Two records for the 202401 partition
    (1, 'Alice',   'A001', 'First record in Jan',  1, 90, 25, '202401'),
    (2, 'Bob',     'B002', 'Second record in Jan', 2, 85, 30, '202401'),
    
    -- Two records for the 202402 partition
    (3, 'Charlie', 'C003', 'First record in Feb',  3, 78, 28, '202402'),
    (4, 'Diana',   'D004', 'Second record in Feb', 1, 92, 22, '202402'),
    
    -- Two records for the 202403 partition
    (5, 'Eve',     'E005', 'First record in Mar',  2, 88, 35, '202403'),
    (6, 'Frank',   'F006', 'Second record in Mar', 3, 76, 40, '202403');
    
    -- -- View the data volume of each partition to confirm that each partition has exactly two records.
    SELECT * FROM test_partition_table;
  2. Create a Hologres external table. Do not specify a partition field. Instead, define the partition field as a regular column.

    Create a Hologres external table

    CREATE EXTERNAL TABLE test_partition_table
    (
        id          BIGINT ,
        name        VARCHAR(64),
        code        CHAR(10),
        description STRING ,
        level       INT,
        score       SMALLINT,
        age         INT,
        ds          STRING  
    )
    STORED BY 'com.aliyun.odps.jdbc.JdbcStorageHandler'
    LOCATION 'jdbc:postgresql://<hologres_net>:<port>/<database>?currentSchema=public&table=test_partition_table'
    TBLPROPERTIES (
      'mcfed.mapreduce.jdbc.driver.class'='org.postgresql.Driver',
      'odps.federation.jdbc.target.db.type'='holo'
    );
  3. 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

  1. Prepare data in Hologres

    Prepare data in Hologres

    CREATE TABLE public.dwd_trade_orders_partitioned (
        order_id BIGINT NOT NULL,
        shop_id INT NOT NULL,
        user_id TEXT NOT NULL,
        payment_type INT DEFAULT 0,
        dt TEXT NOT NULL,                               -- Partition key
        order_time TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
    ) 
    LOGICAL PARTITION BY LIST (dt)                              -- Enable list partitioning.
    WITH (
        orientation = 'column',
        distribution_key = 'order_id',
        event_time_column = 'order_time',
        clustering_key = 'order_time:asc',
        partition_require_filter = 'false'
    );
    
    INSERT INTO public.dwd_trade_orders_partitioned  partition(dt='20231101') VALUES 
    (50001, 101, 'U678', 1,  '20231101', '2023-11-01 10:00:01+08'),
    (50002, 102, 'U992', 2,  '20231101', '2023-11-01 10:05:12+08'),
    (50003, 101, 'U441', 1,  '20231101', '2023-11-01 10:10:45+08');
    
    INSERT INTO public.dwd_trade_orders_partitioned  partition(dt='20231102') VALUES 
    (50004, 105, 'U219', 3,  '20231102', '2023-11-01 10:20:11+08'),
    (50005, 102, 'U883', 1,  '20231102', '2023-11-01 10:32:00+08'),
    (50006, 110, 'U007', 2,  '20231102', '2023-11-01 10:45:33+08');
    
    INSERT INTO public.dwd_trade_orders_partitioned  partition(dt='20231103') VALUES 
    (50007, 101, 'U321', 1,  '20231103', '2023-11-01 11:02:19+08'),
    (50008, 108, 'U556', 2,  '20231103', '2023-11-01 11:15:04+08'),
    (50009, 101, 'U112', 3,  '20231103', '2023-11-01 11:25:55+08'),
    (50010, 105, 'U449', 1,  '20231103', '2023-11-01 11:40:22+08');
    
    select * FROM public.dwd_trade_orders_partitioned where dt='20231103';
  2. Create a Hologres external table. Do not specify a partition field. Instead, define the partition field as a regular column.

    Create a Hologres external table

    CREATE EXTERNAL TABLE dwd_trade_orders_partitioned
    (
        order_id        BIGINT,
        shop_id         INT ,
        user_id         STRING,
        payment_type    INT,
        dt              STRING,                               -- Partition key
        order_time      TIMESTAMP
    )
    STORED BY 'com.aliyun.odps.jdbc.JdbcStorageHandler'
    LOCATION 'jdbc:postgresql://<hologres_net>:<port>/<database>?currentSchema=public&table=dwd_trade_orders_partitioned'
    TBLPROPERTIES (
      'mcfed.mapreduce.jdbc.driver.class'='org.postgresql.Driver',
      'odps.federation.jdbc.target.db.type'='holo'
    );
  3. 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

  • To import data from Hologres into a MaxCompute table or partition, use the LOAD command.

  • To export data from a MaxCompute project to Hologres as external storage for use by other compute engines, use the UNLOAD command.

Supported data types

  • DECIMAL type limitations:

    For a Hologres external table created in MaxCompute, the DECIMAL data type is fixed to decimal(38,18) and cannot be modified. If the source table has fewer decimal places, you can define the column as the STRING type in the MaxCompute external table and use the CAST function 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 Map and Struct. The Array type is supported with limitations, as detailed in the table that follows.

  • Other incompatible types:

    Hologres data types such as MONEY are 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

Supported

Supported

Supported

A 32-bit signed integer.

TEXT

STRING, VARCHAR

Supported

Supported

Supported

A string type with a current length limit of 8 MB.

SMALLINT

SMALLINT

Supported

Supported

Supported

A 16-bit signed integer.

INT2

SMALLINT

Supported

Supported

Supported

A 16-bit signed integer.

BIGINT (alias: INT8)

BIGINT

Supported

Supported

Supported

A 64-bit signed integer.

BYTEA

BINARY

Supported

Supported

Supported

A binary data type. This mapping has a length limit of 8 MB, while the native BYTEA type in Hologres supports up to 1 GB.

REAL (alias: FLOAT4)

FLOAT

Supported

Supported

Supported

A 32-bit binary floating-point number.

DOUBLE PRECISION (alias: FLOAT8)

DOUBLE

Supported

Supported

Supported

A 64-bit binary floating-point number.

BOOLEAN (alias: BOOL)

BOOLEAN

Supported

Supported

Supported

A Boolean value (true or false).

TIMESTAMP

TIMESTAMP_NTZ

Supported

Not supported

Supported

The Hologres TIMESTAMP type has microsecond precision and does not include a time zone.

It maps to the TIMESTAMP_NTZ type in MaxCompute, which has nanosecond precision. This difference in precision can cause slight discrepancies in time values.

TIMESTAMP WITH TIME ZONE (alias: TIMESTAMPTZ)

TIMESTAMP

Supported

Not supported

Supported

The Hologres TIMESTAMPTZ type has microsecond precision and includes a time zone. It maps to the MaxCompute TIMESTAMP type, which has nanosecond precision and no time zone.

The system handles precision conversion, but the time zone information is lost.

DECIMAL (alias: NUMERIC)

DECIMAL(precision,scale)

Supported

Supported

Supported

A base-10 exact numeric type.

  • precision: Specifies the maximum number of digits. The value must be in the range of 1 to 38.

  • scale: Specifies the number of decimal places. The value must be in the range of 0 to 18.

The DECIMAL data type in MaxCompute defaults to (38,18) if no precision is specified. When you create a table by using the IMPORT FOREIGN SCHEMA statement, the system automatically converts the precision.

CHAR(n)

CHAR(n)

Supported

Supported

Supported

In MaxCompute, CHAR(n) is a fixed-length character type, and n specifies the length. The maximum value for n is 255. If an input string is shorter than the specified length, it is padded with spaces.

In Hologres, CHAR(n) has a maximum size of 1 GB.

VARCHAR(n)

VARCHAR(n)

Supported

Supported

Supported

MaxCompute's VARCHAR(n) is a variable-length character type, where n specifies the length. The value range is from 1 to 65535.

In Hologres, the maximum size of VARCHAR(n) is 1 GB.

DATE

DATE

Supported

Supported

Supported

Date type. The format is yyyy-mm-dd.

INT4[]

ARRAY<INT>

Supported

Not supported

Supported

Hologres arrays map to the corresponding MaxCompute ARRAY types. Note that writing array data to Hologres via JDBC is not supported.

INT8[]

ARRAY<BIGINT>

Supported

Not supported

Supported

FLOAT4[]

ARRAY<FLOAT>

Supported

Not supported

Supported

FLOAT8[]

ARRAY<DOUBLE>

Supported

Not supported

Supported

BOOLEAN[]

ARRAY<BOOLEAN>

Supported

Not supported

Supported

TEXT[]

ARRAY<STRING>

Supported

Not supported

Supported

JSONB

JSON

Not supported

Not supported

Supported

Hologres JSONB maps to the MaxCompute JSON type. This mapping is supported only in direct read mode.

JSON

STRING

Supported

Not supported

Not supported

In JDBC mode, the Hologres JSON type maps to the MaxCompute STRING type, which has a length limit of 8 MB.

SERIAL (auto-increment)

INT

Supported

Not supported

Supported

MaxCompute reads Hologres SERIAL fields as the INT type. Writing to these fields from MaxCompute via JDBC is not supported.

RoaringBitmap

Not supported

Not supported

Not supported

Not supported

MaxCompute has no corresponding type.

RoaringBitmap64

Not supported

Not supported

Not supported

Not supported

MaxCompute has no corresponding type.

BIT(n)

Not supported

Not supported

Not supported

Not supported

MaxCompute has no corresponding type.

VARBIT(n)

Not supported

Not supported

Not supported

Not supported

MaxCompute has no corresponding type.

INTERVAL

Not supported

Not supported

Not supported

Not supported

MaxCompute has no corresponding type.

TIMETZ

Not supported

Not supported

Not supported

Not supported

MaxCompute has no corresponding type.

TIME

Not supported

Not supported

Not supported

Not supported

MaxCompute has no corresponding type.

INET

Not supported

Not supported

Not supported

Not supported

MaxCompute has no corresponding type.

MONEY

Not supported

Not supported

Not supported

Not supported

MaxCompute has no corresponding type.

OID

Not supported

Not supported

Not supported

Not supported

MaxCompute has no corresponding type.

UUID

Not supported

Not supported

Not supported

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.size of 256 MB. This can cause the number of concurrent mappers to exceed the maximum of 7,777.

    Note

    This 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 nextKeyValue

    Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near ","

  • Solution

    Add the odps.federation.jdbc.colmapping parameter 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 name created_at Odps type DATETIME Holo type TIMESTAMP

  • Column name create_date Odps type STRING Holo type TIMESTAMP

  • Column name create_time Odps type STRING Holo type TIMESTAMP_NTZ

  • Column name manual_enabled Odps type INT Holo type BIGINT

  • Column name cpa_bid Odps type DECIMAL(38,18) Holo type DECIMAL(38,10)

  • Column name details Odps type STRING Holo type JSON

  • Column name history_score_median Odps type STRING Holo type DOUBLE

  • Column name l_orderkey Odps type 3 Holo type 4

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.

  • Possible cause 1: Incorrect column mapping configuration. The mapping string is invalid, multiple columns map to the same Hologres column, the mapping points to a non-existent column, or a column is not mapped and its name does not exist in the Hologres table.

  • Possible cause 2: Missing columns. Check whether the DDL statements for the Hologres external table and the source table match.

If the issue persists, join the MaxCompute developer community on DingTalk for support by clicking the application link or searching for group ID 11782920.

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