Data Lake Formation:Access DLF from Hologres

Prerequisites

Before you begin, ensure that you have:

• An active DLF service with at least one catalog. See Grant permissions and activate DLF and Data Catalog

• A Hologres instance with the data lake acceleration service enabled. The instance must be in the same region as DLF. See Environment configuration

Limitations

• Writing to Paimon tables is not supported. INSERT, UPDATE, DELETE, and TRUNCATE operations are unavailable.

• Time travel queries, branch queries, and data mirroring are only available for Paimon tables mapped through an external database — not through a foreign server.

• Time travel is not supported on tables that have undergone schema evolution, or on dynamic tables.

• Data mirroring does not support Paimon tables with primary keys.

• Reading from default partitions where the partition key is NULL or empty is not supported.

• The OSS data lake acceleration feature in HoloWeb cannot visually create a DLF Catalog.

• The data lake acceleration feature cannot be enabled for read-only secondary instances.

Choose an auth mode

Hologres supports two modes for authenticating access to a DLF Catalog:

Start with SLR mode unless cross-account access or BASIC account support is required.

Map a catalog using an external database

Accelerate queries using lake table mirroring

Hologres V3.2 and later support lake table mirroring, which syncs metadata and data from an external data source into Hologres — either in near real time or at scheduled intervals. Mirroring supports full tables and specific partitions of partitioned tables. See Lake table mirroring.

External schema operations

CREATE EXTERNAL SCHEMA [IF NOT EXISTS] <ext_database_name>.<ext_schema_name>;

REFRESH CACHE FOR EXTERNAL SCHEMA <ext_database_name>.<ext_schema_name> WITH (cache_level = 'metadata');

DROP EXTERNAL SCHEMA [IF NOT EXISTS] <ext_database_name>.<ext_schema_name>;

SELECT * FROM hologres.hg_external_tables('<ext_database_name>', '<ext_schema_name>');

External table operations

-- Non-partitioned table
CREATE EXTERNAL TABLE <ext_database_name>.<ext_schema_name>.<ext_table_name>(
    id TEXT,
    created_at BIGINT,
    type TEXT,
    actor_id TEXT,
    actor_login TEXT,
    repo_id TEXT,
    repo_name TEXT,
    org TEXT,
    org_login TEXT,
    PRIMARY KEY(id)
) WITH (
  "changelog-producer"='input',
  "bucket"=6,
  "bucket-key"='id'
);

-- Partitioned table
CREATE EXTERNAL TABLE <ext_database_name>.<ext_schema_name>.<ext_table_name>(
    id TEXT,
    created_at BIGINT,
    type TEXT,
    actor_id TEXT,
    actor_login TEXT,
    repo_id TEXT,
    repo_name TEXT,
    org TEXT,
    org_login TEXT
)
LOGICAL PARTITION BY LIST(created_at)
WITH (
  "file_format" = 'orc',
  "bucket"=6,
  "bucket-key"='id'
);

REFRESH CACHE FOR EXTERNAL TABLE <ext_db_name>.<ext_schema_name>.<ext_table_name> WITH (cache_level = 'metadata');

DROP EXTERNAL TABLE IF EXISTS <ext_database_name>.<ext_schema_name>.<ext_table_name>;

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

-- Collect statistics for specific columns (higher sample rate — use for columns with conditional filters)
ANALYZE <ext_database_name>.<ext_schema_name>.<ext_table_name>(<colname>, <colname>);

-- Enable auto-analyze for the external database
ALTER EXTERNAL DATABASE <ext_database_name> WITH enable_auto_analyze 'true';

Time travel queries

Hologres V3.2 and later support querying historical snapshots of Paimon tables. All three approaches use the FOR ... AS OF syntax:

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

-- Query by version (version = snapshot ID for Paimon tables)
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>';

To find available snapshots or tags, query the Paimon system tables first.

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

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

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

Branch queries

Hologres V3.2 and later support querying data from a specific branch in a Paimon table.

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

-- Query the current state of a branch
SELECT * FROM '<ext_database_name>.<ext_schema_name>.<ext_table_name>' FOR branch AS OF '<branch_name>';

-- Query a historical snapshot of 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>';

-- Query a historical snapshot of a branch by tag
SELECT * FROM '<ext_database_name>.<ext_schema_name>.<ext_table_name>' FOR branch AS OF '<branch_name>' TAG AS OF '<tag_name>';

Access Paimon tables through a foreign server

As an alternative to external databases, you can use a foreign server and foreign tables to access Paimon data. This approach uses the standard PostgreSQL foreign data wrapper (FDW) interface.

Time travel queries, branch queries, and data mirroring are not available through foreign tables. Use external databases for those features.

1. Create a foreign server.

   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 additional server options, see More operations on foreign servers (SQL).

2. Create foreign tables using one of the following methods. Option 1: Import an entire schema at once

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

   See IMPORT FOREIGN SCHEMA. Option 2: Create individual tables

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

   See CREATE FOREIGN TABLE.

Read Paimon data using dynamic tables

A dynamic table automatically refreshes its contents from one or more base tables on a configurable schedule. Hologres V3.0 and later support MaxCompute foreign tables and DLF foreign tables as base tables for dynamic tables. See Dynamic table.

All three refresh modes use the same CREATE DYNAMIC TABLE structure. The auto_refresh_mode parameter controls how data is read from the Paimon source.

-- Full refresh: re-reads all data on each cycle
CREATE DYNAMIC TABLE paimon_dt_full
WITH (
    auto_refresh_mode = 'full',
    freshness = '3 minutes'
) AS SELECT * FROM <ext_database_name>.<ext_schema_name>.<ext_table_name>;

-- Incremental refresh: reads only new or changed data
CREATE DYNAMIC TABLE paimon_dt_incremental
WITH (
    auto_refresh_mode = 'incremental',
    freshness = '3 minutes'
) AS SELECT * FROM <ext_database_name>.<ext_schema_name>.<ext_table_name>;

-- Auto mode: Hologres selects full or incremental refresh based on data characteristics
CREATE DYNAMIC TABLE paimon_dt_auto
WITH (
    auto_refresh_mode = 'auto',
    freshness = '3 minutes'
) AS SELECT * FROM <ext_database_name>.<ext_schema_name>.<ext_table_name>;

What's next

• EXTERNAL DATABASE

• CREATE DYNAMIC TABLE

• ANALYZE and AUTO ANALYZE

• Lake table mirroring

• Manage Branch