Paimon table engine

This engine provides a read-only integration with existing Apache Paimon tables in Amazon S3, Azure, HDFS and locally stored tables. It supports snapshot reads, incremental reads, and basic partition pruning provided by the engine.

Create table

Note that the Paimon table must already exist in the storage, this command does not take DDL parameters to create a new table. Creating Paimon* tables is gated by allow_experimental_paimon_storage_engine (disabled by default), so enable it before running CREATE TABLE.

SET allow_experimental_paimon_storage_engine = 1;

CREATE TABLE paimon_table_s3
    ENGINE = PaimonS3(url, [, access_key_id, secret_access_key] [,format] [,compression])

CREATE TABLE paimon_table_azure
    ENGINE = PaimonAzure(connection_string|storage_account_url, container_name, blobpath, [,account_name], [,account_key] [,format] [,compression_method])

CREATE TABLE paimon_table_hdfs
    ENGINE = PaimonHDFS(path_to_table, [,format] [,compression_method])

CREATE TABLE paimon_table_local
    ENGINE = PaimonLocal(path_to_table, [,format] [,compression_method])

Engine arguments

Description of the arguments coincides with description of arguments in engines S3, AzureBlobStorage, HDFS and File correspondingly. format stands for the format of data files in the Paimon table. Engine parameters can be specified using Named Collections

Example

CREATE TABLE paimon_table ENGINE=PaimonS3('http://test.s3.amazonaws.com/clickhouse-bucket/test_table', 'test', 'test')

Using named collections:

<clickhouse>
    <named_collections>
        <paimon_conf>
            <url>http://test.s3.amazonaws.com/clickhouse-bucket/</url>
            <access_key_id>test</access_key_id>
            <secret_access_key>test</secret_access_key>
        </paimon_conf>
    </named_collections>
</clickhouse>

CREATE TABLE paimon_table ENGINE=PaimonS3(paimon_conf, filename = 'test_table')

Capabilities

Snapshot reads from the latest table snapshot.
Incremental reads based on committed snapshot id when enabled.
Partition pruning when use_paimon_partition_pruning is enabled.
Optional background refresh of metadata when configured.
Stable table UUID when using Atomic/Replicated databases, enabling {uuid} macros in Keeper paths.

Primary-key tables

Merge-on-read is not implemented, so primary-key tables cannot be read: the reader returns the raw union of the snapshot’s data files, which still contains the row versions superseded by later upserts. Reading a table whose schema declares primary-key therefore throws.

Settings

This engine uses the same settings as the corresponding object storage engines and adds Paimon-specific settings:

allow_experimental_paimon_storage_engine — enables creation of Paimon, PaimonS3, PaimonAzure, PaimonHDFS, and PaimonLocal table engines. Default: 0 (disabled).
use_paimon_metadata_files_cache — enables the Paimon metadata files cache (caches deserialized manifest lists and manifests). Set to 1 to enable, 0 to disable. Default: 0. How this setting takes effect differs between table functions and persistent table engines — see the note below.
paimon_incremental_read — enable incremental read mode.
paimon_metadata_refresh_interval_sec — background metadata refresh interval in seconds. When set to a value greater than 0, a background task periodically pulls the latest snapshot and schema from object storage. Default: 30.
paimon_keeper_path — Keeper path for incremental read state. Must be set and unique per table; supports macros such as {database}, {table}, {uuid}.
paimon_replica_name — Replica name for incremental read state. Must be set and unique per replica; supports macros such as {replica}.

Example (enable experimental Paimon engine and metadata files cache):

SET allow_experimental_paimon_storage_engine = 1;
SET use_paimon_metadata_files_cache = 1;

CREATE TABLE paimon_cached
ENGINE = PaimonS3(paimon_conf, filename = 'paimon_all_types');

How use_paimon_metadata_files_cache is applied depends on how the Paimon table is accessed:

Table functions (e.g. SELECT ... FROM paimonS3(...)): the cache decision is evaluated per query, so you can pass SETTINGS use_paimon_metadata_files_cache = 1 directly in the SELECT.
Persistent table engines (PaimonS3, PaimonAzure, PaimonHDFS, PaimonLocal, and the Paimon alias): the cache decision is latched once when the table’s metadata is initialized and is stored in immutable persistent components; the metadata update path deliberately does not re-read the setting. Therefore, passing SETTINGS use_paimon_metadata_files_cache = 1 in a SELECT against an already-initialized persistent table has no effect — the previously latched decision keeps being used. To change it, set use_paimon_metadata_files_cache before the table’s metadata is initialized, or DROP and re-CREATE the table with the desired value.

The server-level cache capacity (paimon_metadata_files_cache_size) is not latched: it is a runtime setting that can be changed via SYSTEM RELOAD CONFIG and takes effect immediately even for already-initialized tables.

Incremental read examples

Incremental read with Keeper state:

CREATE TABLE paimon_inc
ENGINE = PaimonS3(paimon_conf, filename = 'paimon_all_types')
SETTINGS
    paimon_incremental_read = 1,
    paimon_keeper_path = '/clickhouse/{database}/{uuid}',
    paimon_replica_name = '{replica}';

Query-level settings for incremental read

The following settings are query-level (passed via SELECT ... SETTINGS, not in CREATE TABLE). They control per-query behavior of incremental reads:

paimon_target_snapshot_id — read only the delta of the specified snapshot. The committed watermark in Keeper is not advanced, so the same snapshot can be re-read any number of times. Default: -1 (disabled).
max_consume_snapshots — maximum number of snapshots to consume in a single incremental read. When the source has accumulated many unread snapshots, this limits how many are consumed per query to control batch size. 0 means no limit. Default: 0.

Targeted snapshot read — always returns the delta of snapshot 1, regardless of the current watermark:

SELECT count()
FROM paimon_inc
SETTINGS paimon_target_snapshot_id = 1;

Limiting snapshots per batch — if three new snapshots are pending, consume at most two per query:

SELECT count()
FROM paimon_inc
SETTINGS max_consume_snapshots = 2;

Rewinding the warehouse

The Keeper cursor at paimon_keeper_path records how far the stream has consumed, and incremental reads assume the warehouse only ever moves forward — Paimon snapshot ids increase monotonically and are never reused. Expiring old snapshots is fine: it removes a prefix and leaves the ids above it untouched. Moving the warehouse backwards breaks that assumption. Restoring the warehouse from an older backup, rolling it back with another engine, or dropping and recreating the Paimon table at the same path all rewind the snapshot ids, and the writer then reuses ids the cursor has already consumed. Rewinding the warehouse requires resetting the cursor in the same operation. ClickHouse cannot reconstruct which snapshots a consumer already received once ids are reused, so a cursor left behind after a rewind produces undefined delivery: snapshots at reused ids may be skipped. When the rewind leaves the cursor pointing past the warehouse’s newest snapshot, the read fails with INVALID_STATE rather than reporting no new data, and the error names the recovery command. Nothing is read and the cursor is left untouched, so every subsequent poll fails identically until it is resolved:

clickhouse-keeper-client -q "set '<paimon_keeper_path>/committed_snapshot' '<latest snapshot id>'"

Do not delete the committed_snapshot node to recover. An absent cursor means “never consumed”, which makes the next read a full re-read of the whole table rather than a resume. Before resetting the cursor, pause all consumers sharing paimon_keeper_path, including refreshable materialized views, and wait for in-flight reads to finish. A read’s commit is conditioned on the cursor it observed. If the cursor changes after that observation but before the commit, the read fails with INVALID_STATE, delivers nothing, and leaves the value you set in place. A read that has already committed can still deliver its batch after the cursor is reset; rewinding the cursor can then cause that batch to be delivered again. Do not delete or replace processing_lock manually. It is an ephemeral node owned by the ClickHouse Keeper session that is running the incremental read; its lifecycle is not an operator recovery interface.

When a snapshot cannot be read

Snapshots that Paimon expired are skipped automatically: expiration removes a prefix of the snapshot ids, so anything below the warehouse’s earliest snapshot is known to be gone and the cursor moves past it. Any other failure to read a snapshot — a transient object storage error, a corrupted snapshot file — fails the query and leaves the cursor where it is. There is deliberately no setting to tolerate this. Skipping an unread snapshot means permanently dropping the data committed in it, and a standing “tolerate errors” switch would turn every future network blip into silent data loss. Because the cursor is untouched, a transient error needs no intervention at all: the next poll re-reads the same range and succeeds. If a snapshot is genuinely unreadable and the stream must move on, abandon it explicitly. The error message names the command, but note what it costs: the failing read delivered nothing, so moving the cursor to the unreadable snapshot abandons every snapshot still unconsumed up to and including it — not only the unreadable one. With a cursor at 1 and snapshots 2, 3 and 4 pending where 3 is unreadable:

# Abandons snapshots 2 and 3; the next read resumes at 4.
clickhouse-keeper-client -q "set '/clickhouse/tables/<uuid>/committed_snapshot' '3'"

To keep the readable ones, drain up to the unreadable snapshot first. Each poll consumes one snapshot and advances the cursor, until it reaches the one that cannot be read:

-- Delivers snapshot 2 and advances the cursor to 2; the next poll fails on 3 again.
SELECT * FROM paimon_inc SETTINGS max_consume_snapshots = 1;

# Now only snapshot 3 is abandoned.
clickhouse-keeper-client -q "set '/clickhouse/tables/<uuid>/committed_snapshot' '3'"

Either way the decision is recorded as an explicit operator action rather than inferred from a setting.

Paimon to MergeTree via Refreshable Materialized View

You can build an end-to-end pipeline that continuously syncs data from a Paimon table into a MergeTree table using a refreshable Materialized View in APPEND mode. Each refresh cycle reads only new incremental data from Paimon and appends it to the destination table. Step 1 — Create the Paimon source table with incremental read and metadata refresh enabled. The example below uses PaimonLocal. Replace the engine with PaimonS3, PaimonAzure, PaimonHDFS, or the auto-detecting Paimon engine as appropriate for your storage backend:

SET allow_experimental_paimon_storage_engine = 1;

-- Local storage
CREATE TABLE paimon_mv_source
ENGINE = PaimonLocal('/path/to/paimon/table')
SETTINGS
    paimon_incremental_read = 1,
    paimon_keeper_path = '/clickhouse/tables/{uuid}',
    paimon_replica_name = '{replica}',
    paimon_metadata_refresh_interval_sec = 1;

-- S3 storage (the `Paimon` engine defaults to the S3 implementation when no `disk` is specified)
CREATE TABLE paimon_mv_source
ENGINE = Paimon('http://minio:9000/bucket/path/to/table', 'access_key', 'secret_key')
SETTINGS
    paimon_incremental_read = 1,
    paimon_keeper_path = '/clickhouse/tables/{uuid}',
    paimon_replica_name = '{replica}',
    paimon_metadata_refresh_interval_sec = 1;

paimon_metadata_refresh_interval_sec sets the background metadata refresh interval in seconds. When greater than 0, a background task periodically pulls the latest snapshot and schema from object storage, so that the MV refresh cycle can see newly committed data without waiting for a query to trigger the metadata update. Default is 30. Use cautiously on many tables to avoid excessive object storage and Keeper I/O. Step 2 — Create the MergeTree destination table (schema cloned from the Paimon table):

CREATE TABLE paimon_mv_dest AS paimon_mv_source
ENGINE = MergeTree()
ORDER BY tuple();

Step 3 — Create the refreshable Materialized View:

CREATE MATERIALIZED VIEW paimon_mv
REFRESH EVERY 10 SECOND
APPEND
TO paimon_mv_dest
AS SELECT * FROM paimon_mv_source;

Every 10 seconds the MV fires a SELECT * FROM paimon_mv_source, which returns only the rows added since the last committed snapshot, and appends them to paimon_mv_dest. Cleanup:

SYSTEM STOP VIEW paimon_mv;
DROP VIEW IF EXISTS paimon_mv SYNC;
DROP TABLE IF EXISTS paimon_mv_dest SYNC;
DROP TABLE IF EXISTS paimon_mv_source SYNC;

Stop the MV before dropping it to prevent background refresh from blocking DDL operations.

Limitations

Incremental read requires Keeper (ZooKeeper) to be configured.
Incremental read requires paimon_keeper_path to be set and unique per table.
paimon_replica_name must be unique per replica within the same Keeper path.
Incremental read uses at-most-once delivery: the committed snapshot is advanced when data files are collected, before the data is actually consumed. If the query fails after file collection, the skipped snapshots will not be re-read on retry.
The table engine is read-only; data modification is not supported.
Incremental read does not handle historical data deletions from the Paimon source. If upstream Paimon data is deleted or updated, the corresponding rows already written to a ClickHouse MergeTree destination table will not be automatically removed. You must manually issue ALTER TABLE ... DELETE on the MergeTree table to clean up stale data.
If the underlying Paimon table is dropped and recreated at the same object-storage path (e.g. via Flink or Spark), you must DROP and re-CREATE the corresponding ClickHouse table. ClickHouse detects the recreation by comparing the schema-0 creation timestamp and raises an error; the stale ClickHouse table cannot be used until it is recreated.

Aliases

The Paimon table engine auto-detects the storage backend from the disk setting and dispatches to PaimonS3, PaimonAzure, or PaimonLocal accordingly. When no disk is specified, it defaults to the PaimonS3 implementation.

Virtual Columns

_path — Path to the file. Type: LowCardinality(String).
_file — Name of the file. Type: LowCardinality(String).
_size — Size of the file in bytes. Type: Nullable(UInt64). If the file size is unknown, the value is NULL.
_time — Last modified time of the file. Type: Nullable(DateTime). If the time is unknown, the value is NULL.
_etag — The etag of the file. Type: LowCardinality(String). If the etag is unknown, the value is NULL.

Data Types supported

Paimon Data Type	ClickHouse Data Type
BOOLEAN	Bool
TINYINT	Int8
SMALLINT	Int16
INTEGER	Int32
BIGINT	Int64
FLOAT	Float32
DOUBLE	Float64
STRING,VARCHAR,BYTES,VARBINARY	String
DATE	Date
TIME(p),TIME	Time(‘UTC’)
TIMESTAMP(p) WITH LOCAL TIME ZONE	DateTime64
TIMESTAMP(p)	DateTime64(‘UTC’)
CHAR	FixedString(1)
BINARY(n)	FixedString(n)
DECIMAL(P,S)	Decimal(P,S)
ARRAY	Array
MAP	Map

Partition supported

Data types supported in Paimon partition keys:

CHAR
VARCHAR
BOOLEAN
DECIMAL
TINYINT
SMALLINT
INTEGER
DATE
TIME
TIMESTAMP
TIMESTAMP WITH LOCAL TIME ZONE
BIGINT
FLOAT
DOUBLE

SQL Reference

Data Types

Engines

Functions

Formats

Settings

System Tables

Data Lakes

Create table

Engine arguments

Example

Capabilities

Primary-key tables

Settings

Incremental read examples

Query-level settings for incremental read

Rewinding the warehouse

When a snapshot cannot be read

Paimon to MergeTree via Refreshable Materialized View

Limitations

Aliases

Virtual Columns

Data Types supported

Partition supported

​Create table

​Engine arguments

​Example

​Capabilities

​Primary-key tables

​Settings

​Incremental read examples

​Query-level settings for incremental read

​Rewinding the warehouse

​When a snapshot cannot be read

​Paimon to MergeTree via Refreshable Materialized View

​Limitations

​Aliases

​Virtual Columns

​Data Types supported

​Partition supported

Create table

Engine arguments

Example

Capabilities

Primary-key tables

Settings

Incremental read examples

Query-level settings for incremental read

Rewinding the warehouse

When a snapshot cannot be read

Paimon to MergeTree via Refreshable Materialized View

Limitations

Aliases

Virtual Columns

Data Types supported

Partition supported