Skip to main content
Last updated on

Paimon Catalog

Doris currently supports accessing Paimon table metadata through various metadata services and querying Paimon data.

At present, only read operations on Paimon tables are supported. Write operations to Paimon tables will be supported in the future.

Quick start with Apache Doris and Apache Paimon.

Applicable Scenarios​

ScenarioDescription
Query AccelerationUse Doris's distributed computing engine to directly access Paimon data for query acceleration.
Data IntegrationRead Paimon data and write it into Doris internal tables, or perform ZeroETL operations using the Doris computing engine.
Data Write-backNot supported yet.

Configuring Catalog​

Syntax​

CREATE CATALOG [IF NOT EXISTS] catalog_name PROPERTIES (
    'type' = 'paimon',
    'paimon.catalog.type' = '<paimon_catalog_type>',
    'warehouse' = '<paimon_warehouse>'
    {MetaStoreProperties},
    {StorageProperties},
    {PaimonProperties},
    {CommonProperties}
);

  • <paimon_catalog_type>

    The type of Paimon Catalog, supporting the following:

    • filesystem: Default. Directly accesses metadata stored on the file system.

    • hms: Uses Hive Metastore as the metadata service.

    • dlf: Uses Alibaba Cloud DLF as the metadata service.

    • jdbc: Uses JDBC interface to connect to a relational database as the metadata service (supported since version 4.1.0).

  • <paimon_warehouse>

    The warehouse path for Paimon. This parameter must be specified when <paimon_catalog_type> is filesystem.

    The warehouse path must point to the level above the Database path. For example, if your table path is: s3://bucket/path/to/db1/table1, then warehouse should be: s3://bucket/path/to/.

  • {MetaStoreProperties}

    The MetaStoreProperties section is used to fill in connection and authentication information for the Metastore metadata service. Refer to the section on [Supported Metadata Services] for details.

  • {StorageProperties}

    The StorageProperties section is used to fill in connection and authentication information related to the storage system. Refer to the section on [Supported Storage Systems] for details.

  • {PaimonProperties}

    The PaimonProperties section is used to fill Paimon-related properties.

    When Doris accesses Paimon metadata, it partially uses the Paimon Java SDK, so some custom parameters may need to be passed through to the Paimon Java SDK. You can use the following prefixes:

    paimon.s3.xxx
    paimon.s3a.xxx
    paimon.fs.xxx

    These parameters will be automatically converted and passed through. Here are some examples:

    Input ParameterConverted Parameter for Paimon Java SDK
    "paimon.s3.list.version" = "1""fs.s3a.list.version" = "1"
    "paimon.s3.paging.maximum" = "100""fs.s3a.paging.maximum" = "100"
    "paimon.fs.s3.read.ahead.buffer.size" = "1""fs.s3a.read.ahead.buffer.size" = "1"
    "paimon.s3a.replication.factor" = "3""fs.s3a.replication.factor" = "3"

    Paimon Read Options

    Doris supports the following bounded Paimon batch-read options. Set Catalog-wide query defaults with paimon.table-option.<option-key>, or override one table reference with table@options('<option-key>'='<value>'). These settings affect only Doris reads and never update the physical Paimon table metadata.

    Paimon optionPaimon defaultValues accepted by DorisEffect
    read.batch-size1024Integer from 1 to 65536Number of rows requested from each Paimon JNI reader batch. An explicitly configured value is preserved when Doris opens the reader.
    file-reader-async-threshold10 MBMemory size from 1 MB to 1 GBFile size threshold at which the Paimon JNI reader switches to asynchronous reading.
    file-index.read.enabledtrueBooleanEnables Paimon file-index pruning during a read.
    source.split.target-size128 MBPositive memory sizeTarget size used when Paimon combines data files into Doris scan splits.
    source.split.open-file-cost4 MBNon-negative memory sizeEstimated cost of opening a file when Paimon combines files into splits.
    scan.manifest.parallelismNumber of available processorsInteger from 1 to 256Requested manifest-read parallelism. At execution time, Doris caps each planning branch independently to the smaller of this value, 256, and the processors available to that FE or BE.
    scan.plan-sort-partitionfalseBooleanSorts partitions during Paimon scan planning.

    For example, the following Catalog settings override values stored in the physical Paimon table and become the defaults for Doris queries:

    CREATE CATALOG paimon_hms PROPERTIES (
        "type" = "paimon",
        "paimon.catalog.type" = "hms",
        "hive.metastore.uris" = "thrift://127.0.0.1:9083",
        "warehouse" = "s3://bucket/paimon-warehouse",
        "paimon.table-option.read.batch-size" = "4096",
        "paimon.table-option.file-reader-async-threshold" = "32 MB",
        "paimon.table-option.source.split.target-size" = "64 MB",
        "paimon.table-option.scan.manifest.parallelism" = "1"
    );

    A query can override these defaults without modifying the Catalog or Paimon table metadata:

    SELECT *
    FROM paimon_hms.db_name.table_name@options(
        'read.batch-size' = '8192',
        'source.split.target-size' = '32 MB'
    );

    Each table reference has an independent option set. For example, two aliases of the same table can use different batch sizes in one statement:

    SELECT small.id
    FROM paimon_hms.db_name.table_name@options('read.batch-size' = '1') small
    JOIN paimon_hms.db_name.table_name@options('read.batch-size' = '8192') large
      ON small.id = large.id;

    The precedence from highest to lowest is:

    relation @options > Doris Catalog property > physical Paimon table option > Paimon default

    Doris validates the final value after applying this precedence. Therefore, a safe Catalog or relation value can replace an invalid physical table value; if the final value is still invalid, Doris rejects the query before the affected planning or reader stage begins. Manifest limits are also enforced before partition, row-count/statistics, fallback-branch, and system-table manifest planning.

    The first five options in the table are metadata-neutral and can reuse the cached latest partition projection. scan.manifest.parallelism and scan.plan-sort-partition affect metadata planning, so Doris plans them from the effective relation-specific table handle. Per-query values do not resize Paimon's JVM-global executor.

    Doris rejects unknown or invalid paimon.table-option.* properties during CREATE CATALOG and ALTER CATALOG. ALTER CATALOG validates the complete candidate configuration before publishing it, so a failed change leaves the previous Catalog configuration effective. Catalogs persisted by an older Doris version remain loadable, but unsupported or invalid legacy reader properties are ignored instead of being applied; their persisted values are retained for image and edit-log compatibility.

    Snapshot and startup-position options are relation context selectors and must use @options, as described in Time Travel; they cannot be Catalog defaults. Doris also excludes scan.max-splits-per-task, which belongs to Paimon's Flink source enumerator, scan.fallback-branch, and streaming, layout, write, and compaction options. Configure Bucket, Primary Key, Partition, Merge Engine, and other physical table behavior in Paimon itself.

    Statement consistency

    Within one statement, Doris keeps schema binding, partition loading, row-count/statistics collection, system-table planning, and data scanning on the same Paimon snapshot and table generation. A snapshot or tag selected by @options uses the schema that belongs to that historical version, including nested STRUCT, MAP, and ARRAY fields. Each execution of a prepared statement obtains fresh statement state, so commits between executions are visible according to the new execution's selectors and cache settings.

    For operational safety, Paimon scanner DEBUG configuration messages report only the batch size and projected-field count; Doris does not dump the raw scanner parameter map, which can contain credentials. The PaimonJniAsyncReaderThreadCount profile gauge is sampled once per second across scanners, so it can be up to one second stale; this sampling does not affect query execution or scheduling. Reader and IOManager cleanup are independent and retry-safe after a partial close failure.

  • {CommonProperties}

    The CommonProperties section is used to fill in common properties. Please refer to the Catalog Overview section on [Common Properties].

Metadata Cache​

To improve the performance of accessing external data sources, Apache Doris caches Paimon metadata. Metadata includes table structure (Schema) and table objects.

tip

For versions before Doris 4.1.x, metadata caching is mainly controlled globally by FE configuration items. For details, see Metadata Cache. Starting from Doris 4.1.x, Paimon Catalog's external metadata cache is configured using the unified meta.cache.* keys.

Cache Property Configuration (4.1.x+)​

Each engine's cache entry uses a unified configuration key format: meta.cache.<engine>.<entry>.{enable,ttl-second,capacity}.

PropertyExampleMeaning
enabletrue/falseWhether to enable this cache module.
ttl-second600, 0, -10 means disable cache (takes effect immediately, can be used to see the latest data); -1 means never expire; other positive integers mean TTL in seconds based on access time.
capacity10000Maximum number of cache entries (by count). 0 means disable.

Effective Logic: The module cache only takes effect when enable=true, ttl-second != 0, and capacity > 0.

Cache Modules​

Paimon Catalog includes the following cache modules:

Module (<entry>)Property Key PrefixCached Content and Impact
schemameta.cache.paimon.schema.Caches table structure. Impact: Visibility of table column information. If disabled, the latest Schema is pulled for each query.
tablemeta.cache.paimon.table.Caches Paimon table metadata objects. Impact: Reduces metadata loading overhead during query planning.

Legacy Parameter Mapping and Conversion​

In version 4.1.x and later, unified keys are recommended. The following is the mapping between legacy Catalog properties and 4.1.x+ unified keys:

Legacy Property Key4.1.x+ Unified KeyDescription
schema.cache.ttl-secondmeta.cache.paimon.schema.ttl-secondExpiration time of table structure cache

Best Practices​

  • Real-time access to the latest data: If you want each query to see the latest data changes or schema changes for Paimon tables, you can set the ttl-second for schema or table to 0. 
    -- Disable table object cache to detect the latest snapshots of Paimon tables
    ALTER CATALOG paimon_ctl SET PROPERTIES ("meta.cache.paimon.table.ttl-second" = "0");
  • Performance optimization: Changes via ALTER CATALOG ... SET PROPERTIES support hot-reload in Paimon Catalog.

Observability​

Cache metrics can be observed through the information_schema.catalog_meta_cache_statistics system table:

SELECT catalog_name, engine_name, entry_name,
       effective_enabled, ttl_second, capacity,
       estimated_size, hit_rate, load_failure_count, last_error
FROM information_schema.catalog_meta_cache_statistics
WHERE catalog_name = 'paimon_ctl' AND engine_name = 'paimon'
ORDER BY entry_name;

See the documentation for this system table: catalog_meta_cache_statistics.

Supported Paimon Versions​

The currently dependent Paimon version is 1.0.0.

Supported Paimon Formats​

  • Supports reading Paimon Deletion Vector

Supported Metadata Services​

Note: The service types and parameters supported by different Doris versions are slightly different. Please refer to the [Examples] section.

Supported Storage Systems​

Note: The service types and parameters supported by different Doris versions are slightly different. Please refer to the [Examples] section.

Supported Data Formats​

Column Type Mapping​

Paimon TypeDoris TypeComment
booleanboolean
tinyinttinyint
smallintsmallint
integerint
bigintbigint
floatfloat
doubledouble
decimal(P, S)decimal(P, S)
varcharstring
charstring
binary, varbinarystring/varbinaryControlled by the enable.mapping.varbinary property of Catalog (supported since 4.0.2). The default is false, which maps to string; when true, it maps to varbinary type.
datedate
timestamp_without_time_zonedatetime(N)Mapped according to precision. If precision is greater than 6, it maps to a maximum of 6 (may cause precision loss).
timestamp_with_local_time_zonedatetime(N)/timestamptz(N)Mapped according to precision. If precision is greater than 6, it maps to a maximum of 6 (may cause precision loss). The enable.mapping.timestamp_tz property (supported since version 4.0.3) controls the mapping behavior. By default, it is set to false, in which case the type is mapped to datetime. When set to true, it is mapped to the timestamptz type.
arrayarray
mapmap
rowstruct
otherUNSUPPORTED

Doris preserves the exact spelling of quoted top-level columns and nested STRUCT fields on both Paimon JNI scanner paths. Names containing delimiters such as commas (region,code), hash signs (nested#value), colons (colon:name), spaces, or Unicode characters do not require escaping beyond normal SQL identifier quoting.

Note

Doris currently does not support Timestamp types with timezone. All timestamp_without_time_zone and timestamp_with_local_time_zone will be uniformly mapped to datetime(N) type. However, during reading, Doris will correctly handle timezones based on the actual source type. For example, after specifying a timezone with SET time_zone=<tz>, it will affect the return results of timestamp_with_local_time_zone columns.

You can check whether the source type contains timezone information in the Extra column of the DESCRIBE table_name statement. If it displays WITH_TIMEZONE, it indicates that the source type is a timezone-aware type. (This feature is supported since 3.0.8)

Supported since version 4.0.3, timestamp_with_local_time_zone can be mapped to Doris timestamptz type.

Examples​

Hive Metastore​

Version 3.1+
CREATE CATALOG test_paimon_on_hms_hdfs_catalog PROPERTIES (
    'type' = 'paimon',
    'paimon.catalog.type' = 'hms',
    'warehouse' = 'hdfs://127.0.0.1:8320/user/hive/warehouse',
    'hive.metastore.uris' = 'thrift://127.0.0.1:9383',
    'fs.defaultFS' = 'hdfs://127.0.0.1:8320',
    'hadoop.username' = 'doris'
);
Version 2.1 & 3.0

Access HMS without Kerberos authentication

CREATE CATALOG paimon_hms_on_hdfs_catalog PROPERTIES (
    'type' = 'paimon',
    'paimon.catalog.type' = 'hms',
    'hive.metastore.uris' = 'thrift://127.0.0.1:9383',
    'warehouse' = 'hdfs://127.0.0.1:8320/user/paimon/warehouse/paimon-hms-hdfs-warehouse',
    'hadoop.username' = 'doris',
    'fs.defaultFS' = 'hdfs://127.0.0.1:8320'
);

Access HMS with Kerberos authentication enabled

CREATE CATALOG paimon_hms_on_hdfs_kerberos_hdfs_catalog PROPERTIES (
    'type' = 'paimon',
    'paimon.catalog.type' = 'hms',
    'warehouse' = 'hdfs://127.0.0.1:8520/paimon-hms-hdfs-warehouse',
    'hive.metastore.uris' = 'thrift://127.0.0.1:9583',
    'hive.metastore.kerberos.principal' = 'hive/hadoop-master@LABS.TERADATA.COM',
    'hive.metastore.sasl.enabled' = 'true',
    'fs.defaultFS' = 'hdfs://127.0.0.1:8520',
    'hadoop.security.auth_to_local' = 'RULE:[2:\$1@\$0](.*@LABS.TERADATA.COM)s/@.*//
        RULE:[2:\$1@\$0](.*@OTHERLABS.TERADATA.COM)s/@.*//
        RULE:[2:\$1@\$0](.*@OTHERREALM.COM)s/@.*//
        DEFAULT',
    'hadoop.security.authentication' = 'kerberos',
    'hadoop.kerberos.principal' = 'hive/presto-master.docker.cluster@LABS.TERADATA.COM',
    'hadoop.kerberos.keytab' = '/keytabs/hive-presto-master.keytab'
);

Aliyun DLF Metastore​

Version 3.1+
CREATE CATALOG paimon_dlf_catalog PROPERTIES (
    'type' = 'paimon',
    'paimon.catalog.type' = 'dlf',
    'warehouse' = 'oss://bucket/p2_regression_case',
    'dlf.uid' = '772905',
    'dlf.catalog_id' = 'p2_regression_case',
    'dlf.region' = 'cn-beijing',
    'dlf.endpoint' = 'dlf.cn-beijing.aliyuncs.com',
    'dlf.access_key' = '<ak>',
    'dlf.secret_key' = '<sk>'
);
Version 2.1 & 3.0
CREATE CATALOG paimon_dlf_catalog PROPERTIES (
    'type' = 'paimon',
    'paimon.catalog.type' = 'dlf',
    'warehouse' = 'oss://bucket/paimon-dlf-oss-warehouse',
    'dlf.uid' = '7716890',
    'dlf.catalog.id' = 'p2_regression_case',
    'dlf.region' = 'cn-beijing',
    'dlf.endpoint' = 'dlf.cn-beijing.aliyuncs.com',
    'dlf.access_key' = '<ak>',
    'dlf.secret_key' = '<sk>'
);

FileSystem Metastore​

Version 3.1+

Access HMS and HDFS services without Kerberos authentication

CREATE CATALOG ctl_test_paimon_catalog PROPERTIES (
    'type' = 'paimon',
    'paimon.catalog.type' = 'filesystem',
    'warehouse' = 'hdfs://127.0.0.1:8020/user/doris/paimon1',
    'fs.defaultFS' = 'hdfs://127.0.0.1:8020'
);
Version 2.1 & 3.0

Access HMS and HDFS services without Kerberos authentication

CREATE CATALOG ctl_test_paimon_catalog PROPERTIES (
    'type' = 'paimon',
    'paimon.catalog.type' = 'filesystem',
    'warehouse' = 'hdfs://127.0.0.1:8020/user/doris/paimon1',
    'fs.defaultFS' = 'hdfs://127.0.0.1:8020'
);

Paimon JDBC Catalog​

Note

This is an experimental feature, supported since version 4.1.0.

Version 4.1+

Using PostgreSQL as metadata storage with S3 storage

CREATE CATALOG paimon_jdbc_postgresql PROPERTIES (
    'type' = 'paimon',
    'paimon.catalog.type' = 'jdbc',
    'paimon.jdbc.uri' = 'jdbc:postgresql://127.0.0.1:5432/paimon_db',
    'paimon.jdbc.user' = 'paimon_user',
    'paimon.jdbc.password' = 'password',
    'paimon.jdbc.driver_class' = 'org.postgresql.Driver',
    'paimon.jdbc.driver_url' = '<jdbc_driver_jar>',
    'warehouse' = 's3://bucket/warehouse',
    's3.access_key' = '<ak>',
    's3.secret_key' = '<sk>',
    's3.endpoint' = 'https://s3.us-east-1.amazonaws.com',
    's3.region' = 'us-east-1'
);

Query Operations​

Basic Query​

Once the Catalog is configured, you can query the table data in the Catalog as follows:

-- 1. Switch to catalog, use database, and query
SWITCH paimon_ctl;
USE paimon_db;
SELECT * FROM paimon_tbl LIMIT 10;

-- 2. Use Paimon database directly
USE paimon_ctl.paimon_db;
SELECT * FROM paimon_tbl LIMIT 10;

-- 3. Use fully qualified name to query
SELECT * FROM paimon_ctl.paimon_db.paimon_tbl LIMIT 10;

Batch Incremental Query​

Since version 3.1.0

Supports Batch Incremental queries for Paimon, similar to Flink.

Supports querying incremental data within specified snapshot or timestamp intervals. The interval is left-closed and right-open.

-- between snapshots [0, 5)
SELECT * FROM paimon_table@incr('startSnapshotId'='0', 'endSnapshotId'='5');

-- between snapshots [0, 5) with specified scan mode
SELECT * FROM paimon_table@incr('startSnapshotId'='0', 'endSnapshotId'='5', 'incrementalBetweenScanMode'='diff');

-- read from start timestamp
SELECT * FROM paimon_table@incr('startTimestamp'='1750844949000');

-- read between timestamp
SELECT * FROM paimon_table@incr('startTimestamp'='1750844949000', 'endTimestamp'='1750944949000');

Parameter:

ParameterDescriptionExample
startSnapshotIdStarting snapshot ID, must be greater than 0. Must be specified with endSnapshotId together.'startSnapshotId'='3'
endSnapshotIdEnding snapshot ID, must be greater than startSnapshotId. Must be specified with startSnapshotId together.'endSnapshotId'='10'
incrementalBetweenScanModeSpecifies the incremental read mode, default is auto, supports delta, changelog and diff'incrementalBetweenScanMode'='delta'
startTimestampStarting snapshot timestamp, must be greater than or equal to 0. Unit is millisecond.'startTimestamp'='1750844949000'
endTimestampEnding snapshot timestamp, must be greater than startTimestamp. Optional, if not specified, reads from startTimestamp to the latest snapshot. Unit is millisecond.'endTimestamp'='1750944949000'
Notice

startSnapshotId and endSnapshotId will compose the Paimon parameter 'incremental-between'='3,10'

startTimestamp and endTimestamp will compose the Paimon parameter 'incremental-between-timestamp'='1750844949000,1750944949000'

incrementalBetweenScanMode corresponds to the Paimon parameter incremental-between-scan-mode.

Refer to the Paimon documentation for further details about these parameters.

Time Travel​

Since version 3.1.0

Supports reading specified snapshots of Paimon tables.

By default, read requests only read the latest version of snapshots.

You can query snapshots of a specified Paimon table through the $snapshots table function:

Doris > SELECT snapshot_id,commit_time FROM paimon_tbl$snapshots;
+-------------+-------------------------+
| snapshot_id | commit_time             |
+-------------+-------------------------+
|           1 | 2025-08-17 06:05:52.740 |
|           2 | 2025-08-17 06:05:52.979 |
|           3 | 2025-08-17 06:05:53.240 |
|           4 | 2025-08-17 06:05:53.561 |
+-------------+-------------------------+

You can use FOR TIME AS OF and FOR VERSION AS OF statements to read historical version data based on snapshot ID or the time when the snapshot was created. Examples:

-- Notice: Before 3.1.1, only support format like YYYY-MM-DD HH:MM:SS.SSS
-- After 3.1.2, support YYYY-MM-DD HH:MM:SS and YYYY-MM-DD
SELECT * FROM paimon_tbl FOR TIME AS OF "2025-08-17 06:05:52.740";
SELECT * FROM paimon_tbl FOR TIME AS OF "2025-08-17 06:05:52";
SELECT * FROM paimon_tbl FOR TIME AS OF "2025-08-17";

-- Notice: The timestamp must be precise down to the millisecond.
SELECT * FROM paimon_tbl FOR TIME AS OF 1755381952740;
-- Use snapshot id
SELECT * FROM paimon_tbl FOR VERSION AS OF 1;

Time Travel with @options​

For Paimon-specific selectors, use @options on a query relation. Doris accepts scan.snapshot-id, scan.tag-name, scan.version, scan.timestamp, scan.timestamp-millis, scan.watermark, scan.file-creation-time-millis, scan.creation-time-millis, and scan.mode. The selected snapshot or tag also selects its historical schema; Doris does not bind the latest schema to historical data.

-- Read snapshot 1 with the schema stored for snapshot 1.
SELECT id, old_name
FROM paimon_tbl@options('scan.snapshot-id' = '1');

-- A retained tag remains readable even after its ordinary snapshot expires.
SELECT id, old_name
FROM paimon_tbl@options('scan.tag-name' = 'tag1');

Only one startup-position option can be specified in a table reference. scan.mode can be combined with a position only when Paimon defines the pair as compatible; for example, scan.mode='from-creation-timestamp' requires scan.creation-time-millis. An unknown or expired snapshot, branch, or tag fails explicitly instead of silently falling back to the latest table. The @options syntax is supported on query relations and can be stored in a view definition, but it cannot be applied to a CTE reference or non-query commands such as SHOW.

Branch and Tag​

Since version 3.1.0

Supports reading branches and tags of specified Paimon tables.

You can use the $branches and $tags system tables to view branches and tags of Paimon tables:

Doris > SELECT * FROM paimon_tbl$branches;
+-------------+-------------------------+
| branch_name | create_time             |
+-------------+-------------------------+
| b_1         | 2025-08-17 06:34:37.294 |
| b_2         | 2025-08-17 06:34:37.297 |
+-------------+-------------------------+

Doris > SELECT * FROM paimon_tbl$tags;
+----------+-------------+-----------+-------------------------+--------------+-------------+---------------+
| tag_name | snapshot_id | schema_id | commit_time             | record_count | create_time | time_retained |
+----------+-------------+-----------+-------------------------+--------------+-------------+---------------+
| t_1      |           1 |         0 | 2025-08-17 06:05:52.740 |            3 | NULL        | NULL          |
| t_2      |           2 |         0 | 2025-08-17 06:05:52.979 |            6 | NULL        | NULL          |
| t_3      |           3 |         0 | 2025-08-17 06:05:53.240 |            9 | NULL        | NULL          |
| t_4      |           4 |         0 | 2025-08-17 06:05:53.561 |           12 | NULL        | NULL          |
+----------+-------------+-----------+-------------------------+--------------+-------------+---------------+

Supports various syntax forms to be compatible with systems like Spark/Trino:

-- BRANCH
SELECT * FROM paimon_tbl@branch(branch1);
SELECT * FROM paimon_tbl@branch("name" = "branch1");

-- TAG
SELECT * FROM paimon_tbl@tag(tag1);
SELECT * FROM paimon_tbl@tag("name" = "tag1");
SELECT * FROM paimon_tbl FOR VERSION AS OF 'tag1';

For the FOR VERSION AS OF syntax, Doris will automatically determine whether the parameter is a timestamp

System Tables​

Since version 3.1.0

Doris supports querying Paimon system tables to retrieve table-related metadata. System tables can be used to view snapshot history, manifest files, data files, partitions, and other information.

To access metadata of a Paimon table, add a $ symbol after the table name, followed by the system table name:

SELECT * FROM my_table$system_table_name;

The audit_log, binlog, manifests, partitions, ro, row_tracking, and table_indexes system tables accept relation @options. The selected snapshot or tag is applied to the system-table rows and, for system tables that expose source columns, the matching historical source-table schema:

SELECT rowkind, id, old_name
FROM paimon_tbl$audit_log@options('scan.tag-name' = 'tag1');

Other system tables reject @options because they cannot guarantee that every row-producing stage observes the selected snapshot. Paimon system tables also reject scan.file-creation-time-millis; silently dropping this file filter could return rows outside the requested range.

Note

Doris does not support reading Paimon global system tables, which are only supported in Flink.

schemas​

Shows current and historical schema information of the table. When modifying table schema using ALTER TABLE, CREATE TABLE AS, or CREATE DATABASE AS statements, each modification generates a record in the schemas table:

SELECT * FROM my_table$schemas;

Result:

+-----------+--------------------------------------------------------------------------------------------------------------------+----------------+--------------+---------+---------+-------------------------+
| schema_id | fields                                                                                                             | partition_keys | primary_keys | options | comment | update_time             |
+-----------+--------------------------------------------------------------------------------------------------------------------+----------------+--------------+---------+---------+-------------------------+
|         0 | [{"id":0,"name":"k","type":"INT NOT NULL"},{"id":1,"name":"f0","type":"INT"},{"id":2,"name":"f1","type":"STRING"}] | []             | ["k"]        | {}      |         | 2025-03-04 22:48:41.666 |
+-----------+--------------------------------------------------------------------------------------------------------------------+----------------+--------------+---------+---------+-------------------------+

snapshots​

Shows all valid snapshot information of the table, including snapshot creation time, commit user, operation type, etc.:

SELECT * FROM my_table$snapshots;

Result:

+-------------+-----------+--------------------------------------+---------------------+-------------+-------------------------+------------------------------------------------------+------------------------------------------------------+-------------------------+--------------------+--------------------+------------------------+----------------------+
| snapshot_id | schema_id | commit_user                          | commit_identifier   | commit_kind | commit_time             | base_manifest_list                                   | delta_manifest_list                                  | changelog_manifest_list | total_record_count | delta_record_count | changelog_record_count | watermark            |
+-------------+-----------+--------------------------------------+---------------------+-------------+-------------------------+------------------------------------------------------+------------------------------------------------------+-------------------------+--------------------+--------------------+------------------------+----------------------+
|           1 |         0 | d7ea4996-92c7-469f-b9ff-c76525954f1c | 9223372036854775807 | APPEND      | 2025-03-04 22:48:45.575 | manifest-list-dc5490ba-420c-445a-b6f7-6962d394935c-0 | manifest-list-dc5490ba-420c-445a-b6f7-6962d394935c-1 | NULL                    |                  1 |                  1 |                      0 | -9223372036854775808 |
|           2 |         0 | 34de47f6-31d1-4f06-b378-c85ef4fbca41 | 9223372036854775807 | APPEND      | 2025-07-01 23:11:35.406 | manifest-list-dca6aa5b-6fc6-4b4f-ac22-acfa15bbf171-0 | manifest-list-dca6aa5b-6fc6-4b4f-ac22-acfa15bbf171-1 | NULL                    |                  2 |                  1 |                      0 | -9223372036854775808 |
|           3 |         0 | 89f67183-a1f8-4ee9-b73c-3f7e992b79a7 | 9223372036854775807 | APPEND      | 2025-07-01 23:11:45.114 | manifest-list-6d624d1b-c774-4d95-905e-8258a7b89ecb-0 | manifest-list-6d624d1b-c774-4d95-905e-8258a7b89ecb-1 | NULL                    |                  3 |                  1 |                      0 | -9223372036854775808 |
|           4 |         0 | 31924a7c-1389-490c-adf1-3bb805b33cd7 | 9223372036854775807 | APPEND      | 2025-07-01 23:12:42.042 | manifest-list-09097a51-afde-485e-929b-d2cc39eb6eb2-0 | manifest-list-09097a51-afde-485e-929b-d2cc39eb6eb2-1 | NULL                    |                  5 |                  2 |                      0 | -9223372036854775808 |
|           5 |         0 | 1e90a80b-41cb-4242-b97c-889728f76810 | 9223372036854775807 | APPEND      | 2025-07-01 23:14:26.445 | manifest-list-b8471969-9c4d-41cd-b790-64f6efb2d142-0 | manifest-list-b8471969-9c4d-41cd-b790-64f6efb2d142-1 | NULL                    |                  6 |                  1 |                      0 | -9223372036854775808 |
|           6 |         0 | 1e90a80b-41cb-4242-b97c-889728f76810 | 9223372036854775807 | COMPACT     | 2025-07-01 23:14:29.317 | manifest-list-b8471969-9c4d-41cd-b790-64f6efb2d142-2 | manifest-list-b8471969-9c4d-41cd-b790-64f6efb2d142-3 | NULL                    |                  5 |                 -1 |                      0 | -9223372036854775808 |
+-------------+-----------+--------------------------------------+---------------------+-------------+-------------------------+------------------------------------------------------+------------------------------------------------------+-------------------------+--------------------+--------------------+------------------------+----------------------+

options​

Shows current configuration options of the table. If a table option is not included in the table, that option is set to its default value:

SELECT * FROM my_table$options;

Result:

+------------------------+--------------------+
|         key            |        value       |
+------------------------+--------------------+
| snapshot.time-retained |         5 h        |
+------------------------+--------------------+

files​

Shows information about all data files pointed to by the current snapshot, including file format, record count, file size, etc.:

SELECT * FROM my_table$files;

Result:

mysql> SELECT * FROM my_table$files;
+-----------+--------+------------------------------------------------------------------------------------------------------------------------+-------------+-----------+-------+--------------+--------------------+---------+---------+-------------------+---------------------+---------------------+---------------------+---------------------+-------------------------+-------------+
| partition | bucket | file_path                                                                                                              | file_format | schema_id | level | record_count | file_size_in_bytes | min_key | max_key | null_value_counts | min_value_stats     | max_value_stats     | min_sequence_number | max_sequence_number | creation_time           | file_source |
+-----------+--------+------------------------------------------------------------------------------------------------------------------------+-------------+-----------+-------+--------------+--------------------+---------+---------+-------------------+---------------------+---------------------+---------------------+---------------------+-------------------------+-------------+
| {}        |      0 | s3://paimon-warehouse-dev/test-flink/cookbook.db/my_table/bucket-0/data-b4a49c57-6ef6-4c04-8813-07a4960d987c-0.parquet | parquet     |         0 |     5 |            5 |               1321 | [1]     | [6]     | {f0=0, f1=0, k=0} | {f0=4, f1=111, k=1} | {f0=11, f1=k7, k=6} |                   0 |                   5 | 2025-07-01 23:14:23.967 | COMPACT     |
+-----------+--------+------------------------------------------------------------------------------------------------------------------------+-------------+-----------+-------+--------------+--------------------+---------+---------+-------------------+---------------------+---------------------+---------------------+---------------------+-------------------------+-------------+

tags​

Shows all tag information of the table, including tag names and associated snapshots:

SELECT * FROM my_table$tags;

Result:

+----------+-------------+-----------+-------------------------+--------------+--------------+
| tag_name | snapshot_id | schema_id |             commit_time | record_count |   branches   |
+----------+-------------+-----------+-------------------------+--------------+--------------+
|     tag1 |           1 |         0 | 2025-03-04 14:55:29.344 |            3 |      []      |
|     tag3 |           3 |         0 | 2025-03-04 14:58:24.691 |            7 |  [branch-1]  |
+----------+-------------+-----------+-------------------------+--------------+--------------+

branches​

Shows all known branch information of the table:

SELECT * FROM my_table$branches;

Result:

+----------------------+-------------------------+
|          branch_name |             create_time |
+----------------------+-------------------------+
|              branch1 | 2025-03-04 20:31:39.084 |
|              branch2 | 2025-03-04 21:11:14.373 |
+----------------------+-------------------------+

consumers​

Shows consumer information of the table, used to track data consumption:

SELECT * FROM my_table$consumers;

Result:

+-------------+------------------+
| consumer_id | next_snapshot_id |
+-------------+------------------+
|         id1 |                1 |
|         id2 |                3 |
+-------------+------------------+

manifests​

Shows manifest file information of the table's current snapshot:

SELECT * FROM my_table$manifests;

Result:

+-------------------------------------------------+-----------+-----------------+-------------------+-----------+---------------------+---------------------+
| file_name                                       | file_size | num_added_files | num_deleted_files | schema_id | min_partition_stats | max_partition_stats |
+-------------------------------------------------+-----------+-----------------+-------------------+-----------+---------------------+---------------------+
| manifest-3df9bb64-5c11-4aef-994e-d8717fedfc70-0 |      1949 |               1 |                 0 |         0 | {}                  | {}                  |
| manifest-d7eb4ec4-7238-478a-9ae6-91a4ccebd561-0 |      1946 |               1 |                 0 |         0 | {}                  | {}                  |
| manifest-3b6f4079-c893-4413-aedc-1e8fbcea6db1-0 |      1948 |               1 |                 0 |         0 | {}                  | {}                  |
| manifest-abe5177f-82da-4e86-9864-40efffb391bd-0 |      1964 |               1 |                 0 |         0 | {}                  | {}                  |
| manifest-ee89dff3-a523-4655-a4b8-d7c9e471a1d6-0 |      1949 |               1 |                 0 |         0 | {}                  | {}                  |
| manifest-ee89dff3-a523-4655-a4b8-d7c9e471a1d6-1 |      2232 |               1 |                 5 |         0 | {}                  | {}                  |
+-------------------------------------------------+-----------+-----------------+-------------------+-----------+---------------------+---------------------+

aggregation_fields​

Shows aggregation field information of the table, used for field configuration in aggregate tables:

SELECT * FROM my_table$aggregation_fields;

Result:

+------------+--------------+----------+------------------+---------+
| field_name | field_type   | function | function_options | comment |
+------------+--------------+----------+------------------+---------+
| k          | INT NOT NULL | []       | []               | NULL    |
| f0         | INT          | []       | []               | NULL    |
| f1         | STRING       | []       | []               | NULL    |
+------------+--------------+----------+------------------+---------+

partitions​

Shows partition information of the table, including total record count and total file size for each partition:

SELECT * FROM my_table$partitions;

Result:

+-----------+--------------+--------------------+------------+-------------------------+
| partition | record_count | file_size_in_bytes | file_count | last_update_time        |
+-----------+--------------+--------------------+------------+-------------------------+
| {}        |            5 |               1321 |          1 | 2025-07-01 23:14:23.967 |
+-----------+--------------+--------------------+------------+-------------------------+

buckets​

Shows bucket information of the table, including statistics for each bucket:

SELECT * FROM my_table$buckets;

Result:

+-----------+--------+--------------+--------------------+------------+-------------------------+
| partition | bucket | record_count | file_size_in_bytes | file_count | last_update_time        |
+-----------+--------+--------------+--------------------+------------+-------------------------+
| {}        |      0 |            5 |               1321 |          1 | 2025-07-01 23:14:23.967 |
+-----------+--------+--------------+--------------------+------------+-------------------------+

statistics​

Shows statistical information of the table, including row count, data size, and other statistics:

SELECT * FROM my_table$statistics;

Result:

+--------------+------------+-----------------------+------------------+----------+
|  snapshot_id |  schema_id |     mergedRecordCount | mergedRecordSize |  colstat |
+--------------+------------+-----------------------+------------------+----------+
|            2 |          0 |              2        |         2        |    {}    |
+--------------+------------+-----------------------+------------------+----------+

table_indexes​

Shows index information of the table:

SELECT * FROM my_table$table_indexes;

Result:

+--------------------------------+-------------+--------------------------------+--------------------------------+----------------------+----------------------+--------------------------------+
|                      partition |      bucket |                     index_type |                      file_name |            file_size |            row_count |                      dv_ranges |
+--------------------------------+-------------+--------------------------------+--------------------------------+----------------------+----------------------+--------------------------------+
|                   {2025-03-01} |           0 |                           HASH | index-70abfebf-149e-4796-9f... |                   12 |                    3 |                         <NULL> |
|                   {2025-04-01} |           0 |               DELETION_VECTORS | index-633857e7-cdce-47d2-87... |                   33 |                    1 | [(data-346cb9c8-4032-4d66-a... |
+--------------------------------+-------------+--------------------------------+--------------------------------+----------------------+----------------------+--------------------------------+

System Table Use Cases​

Through system tables, you can easily accomplish the following operations and monitoring tasks.

View the latest snapshot information of a table to understand its current state​

SELECT snapshot_id, commit_time, commit_kind, total_record_count FROM catalog_sales$snapshots ORDER BY snapshot_id DESC;

Result:

+-------------+-------------------------+-------------+--------------------+
| snapshot_id | commit_time             | commit_kind | total_record_count |
+-------------+-------------------------+-------------+--------------------+
|           1 | 2025-07-01 21:21:54.179 | APPEND      |           14329288 |
+-------------+-------------------------+-------------+--------------------+

View table information for snapshots​

SELECT s.snapshot_id, t.schema_id, t.fields FROM store_sales$snapshots s JOIN store_sales$schemas t ON s.schema_id=t.schema_id;

Result:

+-------------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| snapshot_id | schema_id | fields                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+-------------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|           1 |         0 | [{"id":0,"name":"ss_sold_date_sk","type":"INT"},{"id":1,"name":"ss_item_sk","type":"INT NOT NULL"},{"id":2,"name":"ss_ticket_number","type":"INT NOT NULL"},{"id":3,"name":"ss_sold_time_sk","type":"INT"},{"id":4,"name":"ss_customer_sk","type":"INT"},{"id":5,"name":"ss_cdemo_sk","type":"INT"},{"id":6,"name":"ss_hdemo_sk","type":"INT"},{"id":7,"name":"ss_addr_sk","type":"INT"},{"id":8,"name":"ss_store_sk","type":"INT"},{"id":9,"name":"ss_promo_sk","type":"INT"},{"id":10,"name":"ss_quantity","type":"INT"},{"id":11,"name":"ss_wholesale_cost","type":"DECIMAL(7, 2)"},{"id":12,"name":"ss_list_price","type":"DECIMAL(7, 2)"},{"id":13,"name":"ss_sales_price","type":"DECIMAL(7, 2)"},{"id":14,"name":"ss_ext_discount_amt","type":"DECIMAL(7, 2)"},{"id":15,"name":"ss_ext_sales_price","type":"DECIMAL(7, 2)"},{"id":16,"name":"ss_ext_wholesale_cost","type":"DECIMAL(7, 2)"},{"id":17,"name":"ss_ext_list_price","type":"DECIMAL(7, 2)"},{"id":18,"name":"ss_ext_tax","type":"DECIMAL(7, 2)"},{"id":19,"name":"ss_coupon_amt","type":"DECIMAL(7, 2)"},{"id":20,"name":"ss_net_paid","type":"DECIMAL(7, 2)"},{"id":21,"name":"ss_net_paid_inc_tax","type":"DECIMAL(7, 2)"},{"id":22,"name":"ss_net_profit","type":"DECIMAL(7, 2)"}] |
|           2 |         0 | [{"id":0,"name":"ss_sold_date_sk","type":"INT"},{"id":1,"name":"ss_item_sk","type":"INT NOT NULL"},{"id":2,"name":"ss_ticket_number","type":"INT NOT NULL"},{"id":3,"name":"ss_sold_time_sk","type":"INT"},{"id":4,"name":"ss_customer_sk","type":"INT"},{"id":5,"name":"ss_cdemo_sk","type":"INT"},{"id":6,"name":"ss_hdemo_sk","type":"INT"},{"id":7,"name":"ss_addr_sk","type":"INT"},{"id":8,"name":"ss_store_sk","type":"INT"},{"id":9,"name":"ss_promo_sk","type":"INT"},{"id":10,"name":"ss_quantity","type":"INT"},{"id":11,"name":"ss_wholesale_cost","type":"DECIMAL(7, 2)"},{"id":12,"name":"ss_list_price","type":"DECIMAL(7, 2)"},{"id":13,"name":"ss_sales_price","type":"DECIMAL(7, 2)"},{"id":14,"name":"ss_ext_discount_amt","type":"DECIMAL(7, 2)"},{"id":15,"name":"ss_ext_sales_price","type":"DECIMAL(7, 2)"},{"id":16,"name":"ss_ext_wholesale_cost","type":"DECIMAL(7, 2)"},{"id":17,"name":"ss_ext_list_price","type":"DECIMAL(7, 2)"},{"id":18,"name":"ss_ext_tax","type":"DECIMAL(7, 2)"},{"id":19,"name":"ss_coupon_amt","type":"DECIMAL(7, 2)"},{"id":20,"name":"ss_net_paid","type":"DECIMAL(7, 2)"},{"id":21,"name":"ss_net_paid_inc_tax","type":"DECIMAL(7, 2)"},{"id":22,"name":"ss_net_profit","type":"DECIMAL(7, 2)"}] |
+-------------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

View data distribution of buckets​

SELECT `bucket`, COUNT(*) as file_count, SUM(file_size_in_bytes)/1024/1024 as total_size_mb FROM paimon_s3.tpcds.catalog_sales$files GROUP BY `bucket` ORDER BY total_size_mb;
Note

Many fields in Paimon system tables are keywords in Doris, so they need to be enclosed in backticks.

Result:

+--------+------------+--------------------+
| bucket | file_count | total_size_mb      |
+--------+------------+--------------------+
|     35 |          1 | 12.144722938537598 |
|     81 |          1 | 12.143454551696777 |
|     37 |          1 |  12.14071273803711 |
|     36 |          1 | 12.139023780822754 |
|     63 |          1 | 12.137332916259766 |
|      7 |          1 | 12.122495651245117 |
|     15 |          1 | 12.117934226989746 |
|     11 |          1 | 12.116133689880371 |
|     12 |          1 |  12.11155891418457 |
|     46 |          1 | 12.111005783081055 |
+--------+------------+--------------------+

Appendix​

FAQ​

1. Could not find a file io implementation for scheme 's3a' in the classpath

In versions prior to 3.1, when using HMS as metadata storage with 's3a' or 's3' protocol on the storage side, the following error occurs:

java.io.UncheckedIOException: org.apache.paimon.fs.UnsupportedSchemeException: Could not find a file io implementation for scheme 's3a' in the classpath. Hadoop FileSystem also cannot access this path 's3a://`.

Versions prior to 3.1

Versions prior to 3.1 do not support the s3a protocol. If you must use it, you can temporarily add the s3a-related parameters as a workaround.

Version 3.1 and above

Starting from version 3.1, explicitly adding s3a parameters is no longer necessary. If you need to override Paimon storage parameters, you can add parameters with the paimon.fs. prefix. Since Paimon internally uses the HDFS API, all fs.s3a.* parameters are supported.

For example:

CREATE CATALOG `paimon_hms_on_s3a` PROPERTIES (
    "type" = "paimon",
    "paimon.catalog.type" = "hms",
    "hive.metastore.uris" = "thrift://172.20.48.119:9383",
    "warehouse" = "s3a://bucket/paimon_warehouse",
    "s3.access_key" = "<ak>",
    "s3.secret_key" = "<sk>",
    "s3.region" = "ap-east-1",
    "s3.endpoint" = "https://s3.ap-east-1.amazonaws.com",
    -- Explicitly add s3a parameters
    "fs.s3a.access.key" = "<ak>",
    "fs.s3a.secret.key" = "<sk>",
    "fs.s3a.endpoint.region" = "ap-east-1",
    "fs.s3a.endpoint" = "https://s3.ap-east-1.amazonaws.com",
    "fs.s3a.impl" = "org.apache.hadoop.fs.s3a.S3AFileSystem"
);

When using the s3 protocol, you need to explicitly add Paimon S3-related parameters, i.e., add the paimon. prefix. For example:

CREATE CATALOG test_paimon_on_hms_aws_catalog PROPERTIES (
    'type' = 'paimon',
    'paimon.catalog.type' = 'hms',
    'warehouse' = 's3://bucket-hk/paimon_warehouse',
    'hive.metastore.uris' = 'thrift://127.0.0.1:9383',
    's3.region' = 'ap-east-1',
    's3.endpoint' = 'https://s3.ap-east-1.amazonaws.com',
    's3.access_key' = '<ak>',
    's3.secret_key' = '<sk>',
    -- Explicitly add Paimon S3 parameters
    'paimon.s3.region' = 'ap-east-1',
    'paimon.s3.endpoint' = 'ap-east-1',
    'paimon.s3.access-key' = '<ak>',
    'paimon.s3.secret-key' = '<sk>'
);
Report issue
ASF
Resources
Community
Join the community
Copyright © 2026 The Apache Software Foundation,Licensed under the Apache License, Version 2.0. Apache, Doris, Apache Doris, the Apache feather logo and the Apache Doris logo are trademarks of The Apache Software Foundation.