Hologres:Access Paimon data with DLF

Use an External Database (recommended)

An External Database maps a DLF catalog into Hologres so you can query, manage, and mirror Paimon tables. It supports two authentication modes:

Use a foreign server (legacy)

The foreign server approach does not support Time Travel, branch queries, or data mirroring. Use an External Database for those features.

Step 1: Create a foreign server.

For DLF V2.5 and later:

CREATE SERVER IF NOT EXISTS <server_name> FOREIGN DATA WRAPPER dlf_fdw OPTIONS (
  catalog_type 'paimon',
  metastore_type 'dlf-rest',
  dlf_catalog '<dlf_catalog_name>'
);

For DLF V1.0:

CREATE SERVER IF NOT EXISTS <server_name> FOREIGN DATA WRAPPER dlf_fdw OPTIONS (
  dlf_region '',
  dlf_endpoint 'dlf-share.<region>.aliyuncs.com',
  oss_endpoint 'oss-<region_id>-internal.aliyuncs.com',
  dlf_catalog '<dlf_catalog_name>'
);

For more foreign server operations, see More foreign server operations.

Step 2: Create foreign tables.

Use IMPORT FOREIGN SCHEMA to import all tables in a DLF database:

-- Import all tables
IMPORT FOREIGN SCHEMA <dlf_db_name>
FROM SERVER <server_name>
INTO <holo_schema_name>
OPTIONS (if_table_exist 'update');

-- Import specific tables
IMPORT FOREIGN SCHEMA <dlf_db_name>
LIMIT TO (<table_name>[, ...])
FROM SERVER <server_name>
INTO <holo_schema_name>
OPTIONS (if_table_exist 'update');

Or create a single foreign table with CREATE FOREIGN TABLE:

CREATE FOREIGN TABLE <foreign_table_name>
(
  <column_name> <data_type>
  [, ...]
)
SERVER <server_name>
OPTIONS
(
  schema_name '<dlf_db_name>',
  table_name '<dlf_table_name>'
);

For syntax details, see IMPORT FOREIGN SCHEMA and CREATE FOREIGN TABLE.

Collect statistics for query optimization

Run ANALYZE or AUTO ANALYZE to collect table statistics. The query optimizer uses these statistics to build optimal query plans.

-- Collect statistics for all columns
ANALYZE <ext_database_name>.<ext_schema_name>.<ext_table_name>;

-- Collect statistics for specific columns (more accurate; use for filter columns)
ANALYZE <ext_database_name>.<ext_schema_name>.<ext_table_name>(<col_name>[, <col_name>]);

-- Enable AUTO ANALYZE for an External Database
ALTER EXTERNAL DATABASE <ext_database_name> WITH enable_auto_analyze 'true';

For more details, see ANALYZE and AUTO ANALYZE.

Query with Time Travel

Hologres V3.2 and later supports querying historical snapshots of Paimon tables by timestamp, version (snapshot ID), or tag.

List available snapshots:

SELECT * FROM hologres.hg_list_snapshots('<ext_database_name>.<ext_schema_name>.<ext_table_name>');

The snapshots table returns the following fields:

You can also query the Paimon Snapshots system table using the hg_list_versions function. For Paimon tables, Version corresponds to Snapshot ID.

SELECT * FROM hologres.hg_list_versions('<ext_database_name>.<ext_schema_name>.<ext_table_name>');

List available tags:

SELECT * FROM hologres.hg_list_tags('<ext_database_name>.<ext_schema_name>.<ext_table_name>');

Query by timestamp:

SELECT * FROM <ext_database_name>.<ext_schema_name>.<ext_table_name>
FOR TIMESTAMP AS OF '<timestamp>';

Query by version (snapshot ID):

SELECT * FROM '<ext_database_name>.<ext_schema_name>.<ext_table_name>'
FOR VERSION AS OF '<version>';

Query by tag:

SELECT * FROM '<ext_database_name>.<ext_schema_name>.<ext_table_name>'
FOR TAG AS OF '<tag>';

Hologres V3.2.6 and V4.0.6 and later support reading data from tags that correspond to expired Paimon snapshots.

For more information on Paimon system tables, see Paimon system tables.

Query Paimon branches

Hologres V3.2 and later supports querying data from a specific Paimon branch and querying historical snapshots within a branch.

List available branches:

SELECT * FROM hologres.hg_list_branches('<ext_database_name>.<ext_schema_name>.<ext_table_name>');

Query the current data of a branch:

SELECT * FROM '<ext_database_name>.<ext_schema_name>.<ext_table_name>'
FOR branch AS OF '<branch_name>';

If scan.fallback-branch is set on the Paimon table, Hologres automatically reads missing partitions from the fallback branch. See Manage branches.

Query historical snapshots within a branch:

-- By timestamp
SELECT * FROM '<ext_database_name>.<ext_schema_name>.<ext_table_name>'
FOR branch AS OF '<branch_name>' TIMESTAMP AS OF '<timestamp>';

-- By tag
SELECT * FROM '<ext_database_name>.<ext_schema_name>.<ext_table_name>'
FOR branch AS OF '<branch_name>' TAG AS '<tag_name>';

Accelerate queries with lake table mirroring

Hologres V3.2 and later supports lake table mirroring, which syncs Paimon table metadata and data into Hologres in near-real-time or at scheduled intervals. Mirrored data is served from Hologres storage, significantly reducing query latency. Both full table mirroring and partial mirroring of partitioned tables are supported.

For setup details, see Lake table mirroring.

Use Dynamic Table for continuous refresh

A Dynamic Table automatically processes and stores aggregated results from one or more Paimon foreign tables, with built-in refresh strategies. Starting from V3.0, MaxCompute foreign tables and DLF foreign tables are supported as base tables for a Dynamic Table.

Full refresh — replaces all data on each refresh cycle:

CREATE DYNAMIC TABLE paimon_dt_table
WITH (
  auto_refresh_mode = 'full',
  freshness = '3 minutes'
) AS SELECT * FROM <ext_database_name>.<ext_schema_name>.<ext_table_name>;

Incremental refresh — processes only new or changed data:

CREATE DYNAMIC TABLE paimon_dt_table_incremental
WITH (
  auto_refresh_mode = 'incremental',
  freshness = '3 minutes'
) AS SELECT * FROM <ext_database_name>.<ext_schema_name>.<ext_table_name>;

Auto refresh — automatically selects full or incremental refresh based on data changes:

CREATE DYNAMIC TABLE paimon_dt_table_auto
WITH (
  auto_refresh_mode = 'auto',
  freshness = '3 minutes'
) AS SELECT * FROM <ext_database_name>.<ext_schema_name>.<ext_table_name>;

For more details, see Dynamic Table overview.

INSERT INTO

The following example creates a Paimon primary key table and inserts rows.

-- Create a Paimon primary key table
CREATE EXTERNAL TABLE <ext_database_name>.<ext_schema_name>.<ext_table_name> (
  id         BIGINT,
  customer_id BIGINT,
  item       TEXT,
  amount     FLOAT,
  dt         TEXT,
  PRIMARY KEY (id)
)
WITH (
  "table_format"  = 'paimon',
  "file_format"   = 'orc',
  "merge-engine"  = 'deduplicate',  -- Adjust as needed
  "bucket"        = '4'             -- Adjust as needed
);

-- Insert rows
INSERT INTO <ext_database_name>.<ext_schema_name>.<ext_table_name>
  (id, customer_id, item, amount, dt)
VALUES
  (1, 1001, 'apple',  3.50, '2025-09-10'),
  (2, 1002, 'banana', 2.00, '2025-09-10'),
  (3, 1003, 'orange', 4.20, '2025-09-11');

INSERT OVERWRITE

The following example creates a partitioned Paimon append-only table and overwrites a partition.

-- Create a partitioned Paimon append-only table
CREATE EXTERNAL TABLE <ext_database_name>.<ext_schema_name>.<ext_table_name> (
  id     BIGINT,
  name   TEXT,
  amount FLOAT8,
  dt     TEXT
)
LOGICAL PARTITION BY LIST(dt)
WITH (
  "table_format" = 'paimon',
  "file_format"  = 'parquet',
  "merge-engine" = 'deduplicate',
  "bucket-key"   = 'id',
  "bucket"       = '2'
);

-- Overwrite data
INSERT OVERWRITE <ext_database_name>.<ext_schema_name>.<ext_table_name>
  (id, name, amount, dt)
VALUES
  (1, 'Alice', 100.50, '2025-09-01'),
  (2, 'Bob',   200.00, '2025-09-01'),
  (3, 'Carol', 150.75, '2025-09-02');

UPDATE

UPDATE is supported only on Paimon primary key tables where the Merge Engine is deduplicate or partial-update. Updating primary key columns is not supported.

UPDATE <ext_database_name>.<ext_schema_name>.<ext_table_name>
SET amount = 5
WHERE id = 1;

DELETE

DELETE is supported only on Paimon primary key tables where the Merge Engine is deduplicate or partial-update. For partial-update tables, enable the partial-update.remove-record-on-delete property before running DELETE.

DELETE FROM <ext_database_name>.<ext_schema_name>.<ext_table_name>
WHERE id = 2;