> ## Documentation Index > Fetch the complete documentation index at: https://private-7c7dfe99-parallel-read-in-order-multi-part.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. > This engine provides a read-only integration with existing Apache Paimon tables in Amazon S3, Azure, HDFS and locally stored tables. # Paimon table engine This engine provides a read-only integration with existing Apache [Paimon](https://paimon.apache.org/) 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`. ```sql theme={null} 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](/concepts/features/configuration/server-config/named-collections)

Example

```sql theme={null} CREATE TABLE paimon_table ENGINE=PaimonS3('http://test.s3.amazonaws.com/clickhouse-bucket/test_table', 'test', 'test') ``` Using named collections: ```xml theme={null} http://test.s3.amazonaws.com/clickhouse-bucket/ test test ``` ```sql theme={null} 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): ```sql theme={null} 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: ```sql theme={null} 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: ```sql theme={null} 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: ```sql theme={null} 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 '/committed_snapshot' ''" ``` 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: ```bash theme={null} # Abandons snapshots 2 and 3; the next read resumes at 4. clickhouse-keeper-client -q "set '/clickhouse/tables//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: ```sql theme={null} -- 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; ``` ```bash theme={null} # Now only snapshot 3 is abandoned. clickhouse-keeper-client -q "set '/clickhouse/tables//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: ```sql theme={null} 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):** ```sql theme={null} CREATE TABLE paimon_mv_dest AS paimon_mv_source ENGINE = MergeTree() ORDER BY tuple(); ``` **Step 3 — Create the refreshable Materialized View:** ```sql theme={null} 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:** ```sql theme={null} 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`