Hologres external tables

RAM role mode

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',
-- When Hologres and MaxCompute column names differ, add the odps.federation.jdbc.colmapping parameter to map columns. 
-- This parameter can be omitted if column names are identical.
  ['odps.federation.jdbc.colmapping'='<table_column1>:<source_column1>, 
                                     <table_column2>:<source_column2>,...']
);

Service-linked role mode

For a complete code sample, see Create a Hologres external table (service-linked role authorization mode).

The service-linked role authorization mode allows the same RAM user to access authorized tables in both MaxCompute and Hologres without manual authorization. This mode also supports the Hologres IP whitelist feature.

-- 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',
-- When Hologres and MaxCompute column names differ, add the odps.federation.jdbc.colmapping parameter to map columns. 
-- This parameter can be omitted if column names are identical.
   ['odps.federation.jdbc.colmapping'='<table_column1>:<source_column1>, 
                                      <table_column2>:<source_column2>,...']
);

RAM role

In this mode, the creator of the external table embeds a RAM role with Hologres data access permissions in the external table definition. Other MaxCompute users can then be granted permissions on this external table. When a user accesses the external table, the system uses the bound RAM role to access data in the mapped Hologres table.

Hologres does not support adding a cross-account RAM role to a database instance. Therefore, Hologres in RAM role mode supports only RAM roles from the same account. Similarly, when MaxCompute accesses Hologres using an external table or external schema, only RAM roles from the same account are supported.

Service-linked role

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, so the user must have permissions on both the external table and the underlying Hologres table.

Scenario 1: Hologres Snatshot PPD

In Hologres direct-read scenarios, filter expressions are converted into SQL WHERE clauses for logical partition tables and sent to the Hologres server as the filter parameter of holo_create_table_snapshot(). This allows Hologres to filter out non-matching files on the server side during snapshot creation, reducing the number of files returned to MaxCompute and minimizing data transfer.

Scenario 2: Hologres JDBC PPD

When Hologres direct read is disabled (odps.table.api.enable.holo.table=false), queries are executed through the JDBC path. In this case, PPD works by appending the WHERE clause to the JDBC SQL sent to Hologres, and Hologres performs the filtering at the SQL level. This differs from PPD in Hologres direct-read scenarios:

• Direct-read PPD: filters files at the snapshot level

• JDBC PPD: filters rows at the SQL query level

Scenario 3: ORC row group column-level filtering

File-level filtering on the MaxCompute side. For each ORC file, a SearchArgument is built and passed to the ORC reader. The ORC reader uses the column statistics (min/max/count) of each stripe/row group to determine whether the entire row group can be skipped.

• File-level pruning is performed on the MaxCompute side

• Depends on whether the PPD parameter is enabled on the Hologres side.

• Non-partitioned tables, physical partitioned tables, and logical partitioned tables are all supported.

Direct-read

1. Prepare the data. In your Hologres instance, create a test table and insert data.

   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 the external table.

   -- Enable Hologres direct read.
   SET odps.table.api.enable.holo.table=true;
   -- Set the PPD mode (off, on, or 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 TableScan. 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 query 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 |
   +------+-----------+-------+------------+------------+---------+----------+--------+----------------+-----------------+
   -- Check the Summary section in Logview. The following information is displayed:
   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

JDBC

1. Prepare the data. In your Hologres instance, create a test table and insert data.

   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::122**79014: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'
   );

3. Query the external table.

   -- Disable Hologres direct read and use the JDBC path.
   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 in most cases.
   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 PPD is enabled, TableScan reads only one record.
   SELECT * FROM test_multi_type_ppd WHERE c_id = 3;
   
   -- The query result is as follows:
   +------+-----------+-------+------------+------------+---------+----------+--------+----------------+-----------------+
   | 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 |
   +------+-----------+-------+------------+------------+---------+----------+--------+----------------+-----------------+
   
   -- Check the stdout details of the instance in Logview. The following messages are displayed:
   [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 column-level filtering

This method applies to scenarios with large data volumes. Each row group in ORC contains 10,000 rows, so the filtering effect is not obvious when the data volume is too small

1. Prepare 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 avoid data skew and improve batch 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-cn-<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 external table data

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

   Check the stdout details of the instance in LogView. Filtering succeeds if the following occurs:

   • IOCount and IOLatencyPercentiles appear in TableScan1.

   • ReadRowCount is smaller than RequestedRowCount in TableScan1.