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. CreatingPaimon* tables is gated by enable_paimon_storage_engine (disabled by default), so enable it before running CREATE TABLE.
Engine arguments
Description of the arguments coincides with description of arguments in enginesS3, 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
Capabilities
- Snapshot reads from the latest table snapshot.
- Incremental reads based on committed snapshot id when enabled.
- Partition pruning when
use_paimon_partition_pruningis enabled. - Optional background refresh of metadata when configured.
- Stable table UUID when using Atomic/Replicated databases, enabling
{uuid}macros in Keeper paths. - Schema evolution: each data file is read with the schema it was written with, and its top-level columns are matched to the current schema by Paimon field id, so renamed columns and columns dropped and re-added under the same name read as in Spark.
Primary-key tables
Merge-on-read is not implemented, so a primary-key table is read only where Paimon reads it without merging: a bucket is read when all its data files are above level 0, are known to have no retract rows, and are in one level. Withdeletion-vectors.enabled or 'merge-engine' = 'first-row' the files may be in several levels, and, unless
batch-scan-mode is set to a mode other than none, level-0 files are not read until they are compacted, as in Paimon.
Reading any other bucket throws; a full compaction in Paimon (compact_strategy => 'full') makes it readable. Incremental
reads of primary-key tables are not supported.
Tables with deletion vectors
Deletion vectors are not applied, so snapshots with deletion vectors cannot be read: the result would still contain the rows that laterDELETE and UPDATE statements removed. Reading a snapshot whose index manifest lists
deletion vectors therefore throws, even after deletion-vectors.enabled is turned off.
A full compaction in Paimon (compact_strategy => 'full') applies the deletion vectors, and the snapshots created after
it can be read again.
An incremental read stops at the first unconsumed snapshot with deletion vectors and stays there even after a full
compaction, because every snapshot up to that compaction lists the same deletion vectors. The cursor is not advanced.
The read resumes once Paimon expires those snapshots, or once they are abandoned by moving the cursor to the snapshot of
the full compaction, which drops every snapshot not yet consumed up to it (see
When a snapshot cannot be read):
Settings
This engine uses the same settings as the corresponding object storage engines and adds Paimon-specific settings:enable_paimon_storage_engine— enables creation ofPaimon,PaimonS3,PaimonAzure,PaimonHDFS, andPaimonLocaltable engines. Default:0(disabled).use_paimon_metadata_files_cache— enables the Paimon metadata files cache (caches deserialized manifest lists and manifests). Set to1to enable,0to 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_incremental_read_append_overwrite— the incremental read also delivers the rows thatOVERWRITEsnapshots write, like Paimon’sstreaming-read-append-overwrite. Default:0.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}.
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 passSETTINGS use_paimon_metadata_files_cache = 1directly in theSELECT. - Persistent table engines (
PaimonS3,PaimonAzure,PaimonHDFS,PaimonLocal, and thePaimonalias): 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, passingSETTINGS use_paimon_metadata_files_cache = 1in aSELECTagainst an already-initialized persistent table has no effect — the previously latched decision keeps being used. To change it, setuse_paimon_metadata_files_cachebefore the table’s metadata is initialized, orDROPand re-CREATEthe table with the desired value.
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:Query-level settings for incremental read
The following settings are query-level (passed viaSELECT ... 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.0means no limit. Default:0.
Rewinding the warehouse
The Keeper cursor atpaimon_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. The cursor also records which snapshot it points at, so once the writer has reused the cursor’s snapshot id, the read fails with INVALID_STATE: nothing is read and the cursor is left untouched. To recover, point the cursor at the snapshot the warehouse was rewound to, which reads every snapshot written since the rewind, or set it to its current value, which skips the snapshots at the reused ids. A cursor set by hand, or written by an older version, is trusted as written until a read advances it. Nothing can be checked once Paimon has expired the snapshot the cursor points at, nor for a rewind that keeps the cursor’s snapshot and lands while a read is loading the snapshots after it.
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:
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: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 inAPPEND 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:
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):
SELECT * FROM paimon_mv_source, which returns only the rows added since the last committed snapshot, and appends them to paimon_mv_dest.
Cleanup:
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_pathto be set and unique per table. paimon_replica_namemust 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 ... DELETEon the MergeTree table to clean up stale data. - The first incremental read returns every row of the current snapshot. After that, an incremental read delivers the rows of
APPENDsnapshots only, like Paimon’s streaming read of an append table:COMPACT,OVERWRITEandANALYZEsnapshots are skipped. Paimon commits every write that deletes or rewrites data files asOVERWRITE(INSERT OVERWRITE,TRUNCATE, and the copy-on-write rewrites ofDELETE,UPDATEand of aMERGE INTOthat updates or deletes rows), so the rows such a commit writes, such as the new versions of updated rows, are not delivered, and a warning is logged for every skippedOVERWRITEsnapshot. Withpaimon_incremental_read_append_overwrite = 1they are delivered, like with Paimon’sstreaming-read-append-overwrite, together with the unchanged rows of every data file the commit rewrote, except for the data files that a later snapshot read by the same query removes.COMPACTsnapshots are not read, so a query that reads anAPPENDsnapshot, aCOMPACTthat merges the file it added and anOVERWRITEthat rewrites the merged file returns the rows of both the original and the rewritten file: the unchanged rows appear twice in its result, and the changed ones in their old and new versions.paimon_target_snapshot_idstill returns the delta of any snapshot. - After the first incremental read, a snapshot with a commit kind that ClickHouse does not know fails the incremental read, and every later one fails too until ClickHouse is upgraded or the cursor is moved past the snapshot in Keeper, as the error message describes.
- If the underlying Paimon table is dropped and recreated at the same object-storage path (e.g. via Flink or Spark), you must
DROPand re-CREATEthe 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. - Fields inside a
ROWcolumn are matched to data files by name, not by Paimon field id. Reading a data file written before a field inside aROWwas renamed, or before such a field was dropped and another one with the same name (compared case-insensitively) was added, therefore throws: the result would containNULLfor the renamed field and the dropped field’s values for the re-added one. Adding and dropping fields is supported. Rewriting the table with the current schema in Paimon, for example withINSERT OVERWRITE <table> SELECT * FROM <table>, makes the snapshots created after it readable. An incremental read that reaches such a data file throws without advancing its cursor. To move on, wait until Paimon expires the snapshots committed before the change, or abandon them by moving the cursor to the last of them, which drops every snapshot not yet consumed up to it (see When a snapshot cannot be read).
Aliases
ThePaimon 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 isNULL._time— Last modified time of the file. Type:Nullable(DateTime). If the time is unknown, the value isNULL._etag— The etag of the file. Type:LowCardinality(String). If the etag is unknown, the value isNULL.
Data Types supported
Partition supported
Data types supported in Paimon partition keys:CHARVARCHARBOOLEANDECIMALTINYINTSMALLINTINTEGERDATETIMETIMESTAMPTIMESTAMP WITH LOCAL TIME ZONEBIGINTFLOATDOUBLE