OpenLake:Access Paimon data with DLF Catalog

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

Starting from V3.2, Hologres 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

Starting from V3.2, Hologres can query data from a specific Paimon branch, including historical snapshots within that 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

Starting from V3.2, Hologres 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. Full table mirroring and partial mirroring of partitioned tables are both supported.

For setup details, see Lake table mirroring.

Accelerate queries with the Bitmap File Index

Starting from V5.0, Hologres reads the Bitmap File Index (index format V2) on Paimon tables directly. When you filter an indexed column with an equality (=) or IN predicate, the Hologres reader uses the bitmap to locate matching rows and skip irrelevant rows. This cuts the amount of data scanned and speeds up point queries and multi-value queries on lake tables. The capability is enabled by default.

How it works and when it applies:

• Paimon writers generate the index. Flink, Spark, and other engines can write it, and Hologres also generates it automatically when you run INSERT against a Paimon foreign table—as long as file-index.bitmap.columns is declared on the table.

• The index is stored per data file. Each data file gets a companion .index file whose name is prefixed with the data file name.

• The Hologres reader detects the index format version automatically, and both formats can coexist in the same table: index format V2 uses the V2 parser, index format V1 keeps using the V1 parser. Existing queries are unaffected.

• Supported index column types include fixed-length types (SMALLINT, INT, BIGINT, DATE, TIME, TIMESTAMP, and so on) and variable-length types (STRING or TEXT). FLOAT, DOUBLE, and DECIMAL are not supported.

• Only equality (=) and IN predicates benefit. Range predicates, LIKE, and other predicates do not use the bitmap index.

Steps:

1. Declare the bitmap index columns when you create the table.

   -- Create a Paimon foreign table and declare the bitmap index columns
   CREATE EXTERNAL TABLE EXT_DATABASE_NAME.EXT_SCHEMA_NAME.EXT_TABLE_NAME (
     id        INT,
     c_id      INT,
     c_name    TEXT
   )
   WITH (
     "table_format"              = 'paimon',
     "file_format"               = 'parquet',       -- Both Parquet and ORC support reading the Bitmap File Index
     "bucket"                    = '4',
     "bucket-key"                = 'id',
     "file-index.bitmap.columns" = 'c_id,c_name'    -- Columns to index, separated by commas
   );

   Note

   To downgrade a specific column to index format V1, set "file-index.bitmap.<COLUMN_NAME>.version" = '1' for that column. The default is index format V2.

2. Verify that the Bitmap File Index switches are enabled. Hologres turns the following switches on by default starting from V5.0. To confirm their state or turn them on manually, run:

   -- Global flags that control reading the Bitmap File Index
   SELECT hg_admin_command('set_global_flag','enable_paimon_bitmap_index=true');                    -- Master switch
   SELECT hg_admin_command('set_global_flag','enable_push_bitmap_to_fse_parquet_reader=true');      -- Pushdown in the Parquet reader
   SELECT hg_admin_command('set_global_flag','enable_push_bitmap_to_fse_orc_reader=true');          -- Pushdown in the ORC reader

3. Run an equality or IN query, then confirm that the index took effect with EXPLAIN (ANALYZE).

   -- Query and verify
   EXPLAIN (ANALYZE)
   SELECT count(*) FROM EXT_DATABASE_NAME.EXT_SCHEMA_NAME.EXT_TABLE_NAME WHERE c_id = 1;

   In the query plan, the bottom-level Seq Scan node reports bitmap_used=N, where N is the number of predicates served by the bitmap index. scan_rows converges to the number of matching rows instead of the full table row count. Both signals mean the index took effect. Example output:

   ->  Seq Scan on EXT_TABLE_NAME  (cost=0.00..5.03 rows=1000 width=1)
         Filter: (c_id = 1)
         Engine: FSE
         Foreign Table Type: DLF
       [id=1 split_count=1 rows=4(4/4/4) scan_rows=4(4/4/4) bitmap_used=1]

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. MaxCompute foreign tables and DLF foreign tables have been supported as base tables since V3.0.

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;