Skip to content

SQL Integration

Apache DataFusion is a fast, extensible query engine for building data-centric systems in Rust. The paimon-datafusion crate provides a full SQL integration that lets you create, query, and modify Paimon tables.

Setup

[dependencies]
paimon = "0.6.0"
paimon-datafusion = "0.6.0"
datafusion = "55.2.0"
tokio = { version = "1", features = ["full"] }

Mosaic support is always available. SQL queries can read existing .mosaic files, and writes to ordinary Paimon tables can create them with file.format=mosaic.

SQL Support Scope

paimon-datafusion currently targets Apache DataFusion 55.x. The workspace uses datafusion = "55.2.0".

SQL support has two layers:

  • DataFusion provides the parser, query planner, optimizer, execution engine, expressions, scalar functions, aggregate functions, and window functions. SQL statements that SQLContext does not intercept are delegated to DataFusion. This includes the DataFusion SQL surface for SELECT queries, CTEs (including recursive CTEs), subqueries, joins including LATERAL joins, SQL lambda functions, grouping, HAVING, window clauses, QUALIFY, set operations, ORDER BY, LIMIT/OFFSET, EXPLAIN, information-schema commands such as SHOW TABLES, DESCRIBE, COPY, and ordinary INSERT.
  • Paimon-specific table management and row-level writes are implemented by SQLContext. This includes Paimon CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE TEMPORARY TABLE, CREATE TEMPORARY VIEW, REST Catalog persistent CREATE VIEW, DROP VIEW, CREATE FUNCTION, and DROP FUNCTION, DROP TEMPORARY TABLE / VIEW, INSERT OVERWRITE ... PARTITION, UPDATE, DELETE, TRUNCATE TABLE, ALTER TABLE ... ADD PARTITION, ALTER TABLE ... DROP PARTITION, SHOW PARTITIONS, MSCK REPAIR TABLE, ANALYZE TABLE, CALL sys.*, Paimon time travel, and SET / RESET 'paimon.*'. MERGE INTO uses DataFusion's native planner and the Paimon table provider for execution.

Not every DataFusion DDL/DML statement maps to a Paimon table operation. For Paimon catalogs, CREATE EXTERNAL TABLE, LOCATION, CREATE MATERIALIZED VIEW, and persistent CREATE TABLE AS SELECT are rejected or not implemented. Persistent CREATE FUNCTION is supported only for the REST Catalog SQL scalar form documented below. DataFusion COPY can export query results to files; it does not create or commit Paimon table files.

For the exact delegated SQL grammar, see the DataFusion SQL Reference.

SQL compatibility

GROUP_CONCAT joins non-NULL values with a comma by default. SEPARATOR accepts a string literal, including an empty string. Multiple arguments are concatenated within each row; a row with any NULL argument is skipped. Empty groups and groups containing only NULL values return NULL. DISTINCT, aggregate ORDER BY, and FILTER are supported. DISTINCT accepts one expression, and its ordering expressions must match that expression, as required by DataFusion's distinct aggregation. Numeric ORDER BY positions refer to the aggregate arguments. Calls reject positions that reference volatile expressions, because those arguments cannot safely be evaluated again as native aggregate sort keys. Compute such a value in a subquery first, then pass its column to GROUP_CONCAT. String conversion follows DataFusion's CAST(... AS STRING) after typed deduplication and sorting.

SELECT group_concat(DISTINCT name ORDER BY name SEPARATOR ' | ') FROM users;

GROUP_CONCAT also works in Paimon persistent and temporary view definitions.

DataFusion 55 integration

Paimon Parquet scans refresh supported dynamic predicates before reading each remaining row group. This lets an ORDER BY ... LIMIT threshold skip groups that can no longer contribute. Missing statistics, unsupported predicates, and snapshot errors keep the group readable; the original runtime filter still checks decoded rows. Floating-point predicates stay with DataFusion's row filter because Parquet bounds do not account for NaN ordering.

The NULL-propagating parse_json, try_parse_json, variant_get, try_variant_get, path_to_descriptor, descriptor_to_string, and blob_view functions declare DataFusion's strict UDF metadata. This enables outer join elimination when a filter rejects their NULL results. is_variant_null keeps its distinct SQL NULL behavior.

Regular and audit-log scans report actual output rows, batches, bytes, and compute time to DataFusion. For example, PostgreSQL-compatible JSON plans can be requested with:

EXPLAIN (ANALYZE, FORMAT pgjson, METRICS 'rows')
SELECT * FROM paimon.default.my_table WHERE id > 10;

DataFusion's built-in array functions (including array_scale, array_sum, and unnest_outer) and its planner, aggregation, and spill improvements are available through the normal session. Custom spill backends can be configured on the RuntimeEnv passed to SQLContext::builder.

Paimon MERGE INTO uses DataFusion's native DML plan and TableProvider::merge_into hook. Planning and EXPLAIN do not modify data; collecting or executing the plan performs the write and returns an affected-row count. SQLOptions DML restrictions apply to MERGE statements. file_row_index() and input_file_name() for Paimon scans and custom query-planner FFI are not provided. Paimon's logical row IDs are not physical file row offsets.

Scans declare DataFusion Range partitioning when every table partition key is present unchanged in the scan output and uses a supported type: Boolean, integers, strings, Date, millisecond Time, or Decimal128. Composite keys use lexicographic order with ASC NULLS LAST. Whole splits with equal partition keys stay together; adjacent key groups are combined into at most the configured datafusion.execution.target_partitions. This can avoid exchanges for compatible aggregations and joins, but does not declare that rows within a range are sorted. Parallelism may be lower than the target when there are fewer distinct keys.

Unpartitioned tables, empty scans, scans with only one execution partition or one distinct key, missing/projected-out keys, unsupported types (including floating point, binary, and timestamp keys), unreadable partition metadata, and scans with query authorization or attached authorization rules retain UnknownPartitioning and their original split assignments. Native primary-key tables also retain UnknownPartitioning unless every partition column belongs to the primary key: current or historical value aggregation can change a non-key partition column. File min/max statistics and hash buckets alone are not used to infer ranges.

Registering Catalog

Register an entire Paimon catalog so all databases and tables are accessible via paimon.database.table syntax:

use std::sync::Arc;
use paimon::{CatalogOptions, FileSystemCatalog, Options};
use paimon_datafusion::SQLContext;

async fn example() -> Result<(), Box<dyn std::error::Error>> {
    let mut options = Options::new();
    options.set(CatalogOptions::WAREHOUSE, "file:///tmp/paimon-warehouse");
    let catalog = Arc::new(FileSystemCatalog::new(options)?);

    let mut ctx = SQLContext::new();
    ctx.register_catalog("paimon", catalog).await?;
    let df = ctx.sql("SELECT * FROM paimon.default.my_table").await?;
    df.show().await?;
    Ok(())
}

SQLContext::new creates a session context with the Paimon relation planner and the catalog-independent path_to_descriptor and descriptor_to_string scalar functions pre-registered. Use register_catalog(...).await to add one or more Paimon catalogs; registering a catalog also registers the built-in scalar function blob_view (alias sys.blob_view) and the built-in table-valued functions (vector_search, hybrid_search, and full_text_search) against it. It also manages session-scoped dynamic options internally for SET/RESET support.

Reading files with read_files

SQLContext registers a Databricks-style batch table function without requiring a catalog:

SELECT * FROM read_files('oss://my-bucket/imports/users.csv',
    format => 'csv', header => true, schema => 'id BIGINT, name STRING');
SELECT id FROM read_files('oss://my-bucket/imports/*.parquet');
SELECT * FROM read_files('oss://my-bucket/imports/events.ndjson', format => 'json');
SELECT * FROM read_files('oss://my-bucket/imports/document.json',
    format => 'json', multiLine => true);
SELECT value FROM read_files('oss://my-bucket/imports/logs/', format => 'text');
SELECT path, length FROM read_files('oss://my-bucket/imports/images/',
    format => 'binaryFile');

The function is registered by default. Local paths use the process's filesystem permissions; no separate directory registration is required. Loaded tables share their FileIO routes with blob readers, including REST credential refresh and FUSE routing. In addition, read_files can use the warehouse FileIO from a registered filesystem catalog; these warehouse fallbacks do not enable table-less blob reads. The longest matching location wins, with table IO preferred for equivalent locations. Other paths use a configured DataFusion object store or Paimon's default FileIO for that path.

Each query captures its selected store and credentials without replacing the shared RuntimeEnv registry. Catalog FileIO credentials retain the permissions of the underlying storage provider; reading a raw file does not perform a catalog table permission check. Credentials are configured on the catalog or application, never as SQL arguments. Default local reads retain an open directory handle, reject special files and skip symlinks during recursive discovery. HTTP/HTTPS file URLs are unsupported.

ADLS container URLs (abfss://container@host/...) require a matching FileIO route or a container-aware object store registry; DataFusion's default registry discards the container portion of those URLs and is rejected for these reads.

Supported formats and options:

Format Behavior and options
csv Infer columns or use a DDL schema; header, sep/delimiter, quote, escape, nullValue, multiLine, mode => 'FAILFAST'. Without schema, headers default to enabled; with schema, headers default to disabled. Headerless inference names columns _c0, _c1, etc. The default escape is backslash; quote => '' disables quoting. Quoted line breaks require multiLine => true.
parquet Native Parquet schema, column pruning and predicate pushdown; optional schema.
json One object per line by default, including .jsonl/.ndjson files. multiLine => true reads one object or an array of objects per file. Optional schema and mode => 'FAILFAST'.
text One UTF-8 line per value STRING row; default separators are LF, CRLF and CR. lineSep sets a separator; wholeText => true reads each file as one row, including empty files.
binaryFile One row per file: path STRING, modificationTime TIMESTAMP, length BIGINT, content BINARY. Selecting only metadata avoids reading file contents.

Options are named, case-insensitive and must evaluate to constants. Duplicate, unknown and format-inappropriate options are rejected. format can be omitted for recognized extensions; mixed formats require an explicit format. Directories are scanned recursively. Path globs support *, ? and character classes, including Hadoop-style negation [^a]. Unsupported brace expansion is rejected. Each wildcard matches one path component; a matched directory includes its descendants. pathGlobFilter (alias fileNamePattern) filters leaf names. Hidden path components beginning with . or _ are skipped. No matching files is an error.

Every source row exposes _metadata with file_path, file_name, file_size, file_modification_time, file_block_start and file_block_length, unless the data already contains a column named _metadata, which takes precedence. SQLContext.sql excludes generated metadata from SELECT * and alias.*; select _metadata explicitly to include it. Metadata explicitly exported by a subquery, CTE or view remains an ordinary visible column. Each file is exposed as one logical block with offset zero and length equal to the file size. Wildcard selection through an alias on a parenthesized join containing read_files is unsupported; alias the individual sources instead. With DataFusion's default identifier normalization, quote the camel-case binary column as "modificationTime" when selecting it explicitly. CSV and Parquet inference uses DataFusion's inference limits. NDJSON inference samples up to 1,000 records per file; multi-line JSON inference uses the whole document. JSON merges fields across the discovered files, including numeric widening. Supply schema for a fixed contract.

This is a supported subset of Databricks read_files. It does not add the read_csv, read_parquet, read_json, read_text, read_blob or read_ndjson aliases. Hive partition inference, compressed text inputs, schema hints/rescued-data modes, streaming ingestion, Avro, ORC, XML and service-specific connections are not implemented. CSV/JSON currently use strict parsing, including when mode is omitted; this differs from Databricks' default PERMISSIVE mode. Use mode => 'FAILFAST', schemaEvolutionMode => 'none' for an explicit strict-reading contract with rescued data disabled. Only none is supported for schema evolution. Other parsing modes are rejected; malformed input fails rather than being rescued or silently dropped. JSON single-quoted strings and non-finite numbers are also unsupported. Whole-file text, binary content and multi-line JSON materialize a file in memory; ordinary text and CSV are streamed in batches.

REST Catalog Views and SQL Functions

When the registered catalog is a Paimon REST Catalog, SQLContext can read, execute, create, and drop persistent views and SQL scalar functions.

Create a persistent view with this syntax:

CREATE VIEW [IF NOT EXISTS] view_name [(column_name, ...)] AS query;

Drop a persistent view with this syntax:

DROP VIEW [IF EXISTS] view_name;

For example:

CREATE VIEW paimon.reporting.daily_orders (order_date, order_count) AS
SELECT order_date, COUNT(*)
FROM orders
GROUP BY order_date;

The defining query is planned before the view is created. Its output types and nullability become the stored REST view schema, with field IDs assigned from zero. An optional column list changes only the output names and must contain exactly one unique name per query column. IF NOT EXISTS is passed to the catalog so the REST server handles concurrent creates atomically.

Unqualified relations and REST SQL functions in the defining query resolve in the new view's owning catalog and database, not the session's current database. The canonical query is stored as both the default query and the datafusion dialect definition.

SHOW CREATE VIEW view_name returns the owning catalog, database, view name, and a fully qualified CREATE VIEW definition with its declared column names and types. The exported query includes the same type casts as view reads. It prefers the stored datafusion query and reads catalog metadata directly, without planning or executing the defining query. Temporary views shadow persistent objects and return CREATE TEMPORARY VIEW; session-native DataFusion views are also supported. Temporary definitions bind table and Catalog function names to the namespace used at creation, so recreating them after USE preserves their dependencies. Native views created through SQLContext::sql also bind their table dependencies at creation. Definitions created directly through SessionContext must fully qualify physical table references as catalog.schema.table; exports with unqualified dependencies are rejected because their creation namespace cannot be recovered reliably. Repeated exports and recreations keep the type projection stable. Table targets are rejected.

Persistent CREATE VIEW and DROP VIEW are currently implemented by REST Catalog. DROP VIEW sends a direct delete request; IF EXISTS ignores only a missing-view response. Bare, two-part, and three-part names are supported, but only one target may be dropped per statement. Other catalog implementations may return Unsupported. CREATE OR REPLACE VIEW, materialized/secure views, view comments or options, vendor-specific create modifiers, persistent ALTER VIEW, and DROP VIEW modifiers such as CASCADE, RESTRICT, or PURGE are not supported.

Persistent views resolve through the normal DataFusion catalog path, so they can be queried wherever a table can be used:

SELECT * FROM analytics_view;
SELECT * FROM paimon.reporting.daily_orders;

For a view, the datafusion entry in schema.dialects is preferred. If that entry is absent, DataFusion uses the view's default schema.query. Unqualified relations inside the stored query resolve against the view's owning catalog and database, not the caller's current database. The REST-declared output fields are authoritative: query results are matched by position, renamed to the declared field names, and cast to the declared types. Recursive view dependencies are rejected during planning.

REST SQL scalar functions support bare names in the current catalog/database and fully qualified three-part names:

SELECT normalize_score(score) FROM scores;
SELECT paimon.reporting.normalize_score(score) FROM scores;

Create a persistent REST SQL scalar function with this syntax:

CREATE FUNCTION [IF NOT EXISTS] function_name(
    [parameter_name data_type, ...]
)
RETURNS data_type
[LANGUAGE SQL]
RETURN scalar_expression;

For example:

CREATE FUNCTION paimon.reporting.add_tax(amount DECIMAL(12, 2))
RETURNS DECIMAL(12, 2)
RETURN amount * DECIMAL '1.10';

SELECT add_tax(total) FROM orders;

Drop a persistent function using a bare, two-part, or three-part name:

DROP FUNCTION add_tax;
DROP FUNCTION IF EXISTS reporting.add_tax;
DROP FUNCTION IF EXISTS paimon.reporting.add_tax;

Persistent DROP FUNCTION is implemented by REST Catalog and sends a direct delete request. IF EXISTS ignores only a missing function; permission and server errors are preserved. Only one function may be dropped per statement. Argument signatures, CASCADE, RESTRICT, and DROP TEMPORARY FUNCTION are not supported for Paimon catalogs. Catalogs without persistent function support may return Unsupported.

Bare, two-part (database.function), and three-part (catalog.database.function) names are accepted as creation targets. Unquoted names are normalized and quoted names are preserved. Calls remain limited to bare or three-part names; two-part function calls are not supported.

Parameters must be named and cannot have modes or defaults. Zero parameters are allowed. Inputs and the single return value are stored as nullable fields; parameter IDs start at zero and the return field has ID 0 and name result. The canonical, unexpanded RETURN expression is stored in definitions.datafusion with type: "sql".

LANGUAGE SQL is optional and SQL is the default, matching Databricks SQL function syntax. IMMUTABLE is not required; when omitted, determinism is inferred from the planned expression. An explicit IMMUTABLE clause remains accepted for compatibility.

Before the REST create request is sent, SQLContext expands dependencies using the new function as a candidate, validates argument substitution and the declared return cast, and builds both logical and physical DataFusion plans in the function's owning catalog/database. This rejects undeclared identifiers, recursive dependencies (including indirect recursion), non-deterministic REST dependencies, subqueries/table access, aggregate or window functions, Stable/Volatile DataFusion functions, and incompatible return types. The function is stored as deterministic only after the planned expression passes these checks.

IF NOT EXISTS still validates the proposed definition first. The REST server then handles the create atomically; only an already-existing function error is ignored.

A function is executable only when all of the following are true:

  • definitions.datafusion exists and has type: "sql";
  • input parameters are declared and the call supplies the exact number of positional expression arguments;
  • exactly one return parameter is declared;
  • the function is deterministic;
  • the SQL definition is a scalar expression that references inputs by their declared parameter names.

Nested REST SQL functions are supported. Bare function names inside a stored definition resolve in that function's owning catalog/database. Recursive function dependencies, missing DataFusion SQL definitions, undeclared identifiers, named arguments, and incompatible return types fail during planning. If no REST function exists for a bare name, normal DataFusion built-in or registered-function resolution continues.

Function expansion is implemented by SQLContext::sql. Queries executed directly through a raw DataFusion SessionContext do not expand REST SQL functions. Two-part function names such as database.function(...), lambda or file definitions, aggregate/table/multi-return functions, and non-deterministic functions are not supported. CREATE OR REPLACE/ALTER/TEMPORARY FUNCTION, non-SQL bodies, STABLE/VOLATILE, null-input/parallel/security/SET clauses, options/remote functions, and persistent ALTER FUNCTION are also not supported. Catalog implementations other than REST Catalog may return Unsupported for persistent function creation or deletion.

Data Types

The following SQL data types are supported in CREATE TABLE and mapped to their corresponding Paimon types:

SQL Type Paimon Type Notes
BOOLEAN BooleanType
TINYINT TinyIntType
SMALLINT SmallIntType
INT / INTEGER IntType
BIGINT BigIntType
FLOAT / REAL FloatType
DOUBLE / DOUBLE PRECISION DoubleType
VARCHAR / TEXT / STRING VarCharType
CHAR(n) CharType Fixed-length; defaults to CHAR(1) when n is omitted
VARBINARY / BYTEA / BYTES VarBinaryType
BINARY(n) BinaryType Fixed-length; defaults to BINARY(1) when n is omitted
VARIANT VariantType Semi-structured value encoded as value + metadata binary buffers
BLOB BlobType Binary large object
GEOMETRY, GEOMETRY('crs') GeometryType Planar WKB with a default CRS of OGC:CRS84
GEOGRAPHY, GEOGRAPHY('crs'[, algorithm]) GeographyType WKB with a default CRS of OGC:CRS84. Edge algorithms are spherical (default), vincenty, thomas, andoyer, and karney.
DATE DateType
TIMESTAMP[(p)] TimestampType Precision p: 0/3/6/9, default 3
TIMESTAMP WITH TIME ZONE LocalZonedTimestampType
DECIMAL(p, s) DecimalType
ARRAY<element> ArrayType e.g. ARRAY<INT>
VECTOR<element, n> VectorType Fixed-size vector; n is between 1 and 2147483647
MAP(key, value) MapType e.g. MAP(STRING, INT)
STRUCT<field TYPE, ...> RowType e.g. STRUCT<city STRING, zip INT>

Supply WKB bytes in the column's declared CRS on insert. Direct projections return Arrow binary fields with the geoarrow.wkb extension, CRS, and geography edge algorithm. Assignments copy the WKB bytes. The target column supplies the CRS and edge algorithm, so reproject the coordinates before inserting if needed. Equality compares WKB bytes, even across different CRSs. Spatial functions such as ST_Intersects require a separate spatial function provider. Use Parquet when exchanging geospatial data with Java Paimon. The Rust Row format also accepts WKB, but the Java Row reader and writer do not handle these types. For an array of WKB literals, use INSERT INTO shapes VALUES ([decode('0101000000000000000000F03F0000000000000040', 'hex')]).

VECTOR<element, n> follows Paimon's logical vector type. Elements must be BOOLEAN, TINYINT, SMALLINT, INT / INTEGER, BIGINT, FLOAT / REAL, or DOUBLE / DOUBLE PRECISION. The vector and its elements are nullable by default; for example, embedding VECTOR<FLOAT NOT NULL, 128> NOT NULL disallows both null vectors and null elements. The syntax is supported in CREATE TABLE, ALTER TABLE, and CAST, including vectors nested in arrays, maps, and structs. SHOW CREATE TABLE preserves the element type, dimension, and nullability. Nested vectors can also be non-null, for example ARRAY<VECTOR<FLOAT NOT NULL, 128> NOT NULL>. In CAST targets, SQL fixed-size arrays (T[n]) inside a MAP containing vectors are not supported; use ARRAY<T> for variable-length collections.

For vector search embeddings, use VECTOR<FLOAT, n> or ARRAY<FLOAT>. DataFusion represents VECTOR<FLOAT, n> as Arrow FixedSizeList<Float32>, and vindex index creation uses n from the type as the vector dimension.

Blob Columns

BLOB columns store large binary values using Paimon's dedicated BLOB layout. Declare them as top-level columns and enable data evolution:

CREATE TABLE paimon.my_db.assets (
    id INT,
    picture BLOB
) WITH (
    'data-evolution.enabled' = 'true'
);

For Java-compatible DDL, DataFusion also supports the BLOB comment directives used by Java Paimon. A binary column with one of these directive comments is normalized to a Paimon BLOB column in the core schema layer:

CREATE TABLE paimon.my_db.assets (
    id INT,
    picture BYTES COMMENT '__BLOB_FIELD; original image',
    thumbnail BYTES COMMENT '__BLOB_DESCRIPTOR_FIELD; descriptor bytes',
    picture_ref BYTES COMMENT '__BLOB_VIEW_FIELD; upstream image reference'
) WITH (
    'data-evolution.enabled' = 'true'
);

The directive is stripped from the stored column comment; text after the first semicolon is kept as the real comment. The directives also populate the matching table options. A comment directive that starts with __BLOB but is not one of the supported directives is rejected.

Comment directive Table option Storage semantics
__BLOB_FIELD blob-field Store BLOB bytes in dedicated .blob files
__BLOB_DESCRIPTOR_FIELD blob-descriptor-field Store serialized BlobDescriptor bytes inline
__BLOB_VIEW_FIELD blob-view-field Store serialized BlobViewStruct bytes inline

For serialized BlobDescriptor values supplied by another Paimon engine, length = -1 means reading from offset to the end of the referenced object. The offset must be non-negative, and lengths below -1 are invalid.

The same directives are supported by ALTER TABLE ... ADD COLUMN.

Append-only data-evolution tables can set video-frame-field to a comma-separated list of scalar BLOB columns. Native writers accept serialized VideoFrameDescriptor values for these columns and pack complete encoded videos into .video files, deduplicating each source URI/range within one file and indexing logical frame runs. Readers return lazy frame descriptors. Video packing does not decode or re-encode frames. Raw video bytes, ordinary BlobDescriptors, video-column updates, video compaction, and BlobConsumer callbacks are unsupported.

Blob Descriptor Functions

path_to_descriptor(path) converts a string path into Java-compatible BlobDescriptor bytes with offset 0 and length -1. Its alias is sys.path_to_descriptor(path). The function only serializes the path; it does not access the referenced object or validate that it exists.

descriptor_to_string(descriptor) converts serialized descriptor bytes to the same string representation used by Java Paimon. Its alias is sys.descriptor_to_string(descriptor). Invalid descriptor bytes return an error. Both functions return NULL for NULL input.

SELECT sys.descriptor_to_string(
    sys.path_to_descriptor('file:///tmp/image.png')
);
-- BlobDescriptor{version=2, uri='file:///tmp/image.png', offset=0, length=-1}

Blob View

Blob View stores an inline reference to a BLOB value in another table, using a Java-compatible BlobViewStruct payload. It is useful when one table should point at media or large binary content owned by an upstream table without copying the bytes at write time.

Declare Blob View columns as top-level BLOB columns and list them in the blob-view-field table option:

CREATE TABLE paimon.my_db.asset_refs (
    id INT,
    picture BLOB
) WITH (
    'data-evolution.enabled' = 'true',
    'row-tracking.enabled' = 'true',
    'blob-view-field' = 'picture'
);

Use blob_view(table, field_name_or_id, row_id) or sys.blob_view(...) to create the reference. The table argument may be table, database.table, or catalog.database.table; the stored reference contains the resolved database.table, field id, and row id. In typical SQL, read _ROW_ID from a row-tracking source table:

CREATE TABLE paimon.my_db.assets (
    id INT,
    picture BLOB
) WITH (
    'data-evolution.enabled' = 'true',
    'row-tracking.enabled' = 'true'
);

INSERT INTO paimon.my_db.asset_refs (id, picture)
SELECT
    id,
    sys.blob_view('my_db.assets', 'picture', "_ROW_ID")
FROM paimon.my_db.assets;

By default, RESTCatalog-backed reads resolve Blob View fields to the upstream BLOB value by reusing the table's REST environment. Other catalog types currently preserve the raw serialized BlobViewStruct bytes. Set the dynamic option paimon.blob-view.resolve.enabled to false to preserve raw references even for RESTCatalog-backed reads:

SET 'paimon.blob-view.resolve.enabled' = 'false';
SELECT id, picture FROM paimon.my_db.asset_refs;
RESET 'paimon.blob-view.resolve.enabled';

Like ordinary BLOB reads, paimon.blob-as-descriptor = true makes resolved Blob View columns return serialized BLOB descriptors instead of loading the BLOB bytes.

Variant Usage

VARIANT stores semi-structured data using the same logical value + metadata binary shape as Paimon Java. Use it for JSON-like fields whose schema may differ row by row.

Parquet and ROW files support ordinary VARIANT reads and writes. Set 'file.format' = 'row' to use ROW storage. ROW preserves the value and metadata buffers in the Java-compatible v1 layout; extracting a path still reads the containing compressed row block. The shredding options below apply to Parquet.

Create VARIANT columns like ordinary table columns:

CREATE TABLE paimon.my_db.user_events (
    user_id BIGINT NOT NULL,
    event_time TIMESTAMP,
    payload VARIANT,
    attributes VARIANT,
    dt STRING,
    PRIMARY KEY (user_id, dt)
) PARTITIONED BY (dt)
WITH ('bucket' = '4');

VARIANT columns can be nullable or NOT NULL:

CREATE TABLE paimon.my_db.variant_examples (
    id INT NOT NULL,
    payload VARIANT NOT NULL,
    optional_payload VARIANT
);

Do not use VARIANT as a partition column. Partition values must be scalar strings, numbers, dates, or timestamps that can be encoded as stable partition names.

Use parse_json when inserting JSON text into a VARIANT column:

INSERT INTO paimon.my_db.user_events VALUES
(
    1,
    TIMESTAMP '2024-01-01 10:00:00',
    parse_json('{"event":"login","device":{"os":"ios","version":17},"score":98.5}'),
    parse_json('{"city":"Beijing","tags":["new","mobile"],"vip":true}'),
    '2024-01-01'
);

parse_json rejects invalid JSON and duplicate object keys. Use try_parse_json when malformed JSON should become SQL NULL instead of failing the query:

INSERT INTO paimon.my_db.user_events
SELECT
    user_id,
    event_time,
    try_parse_json(raw_payload),
    try_parse_json(raw_attributes),
    dt
FROM staging_events;

SQLContext::new registers Spark-compatible scalar functions for common VARIANT workflows:

SELECT
    user_id,
    variant_get(payload, '$.event', 'string') AS event_name,
    variant_get(payload, '$.device.os', 'string') AS os,
    variant_get(payload, '$.score', 'double') AS score,
    variant_get(attributes, '$.tags[0]', 'string') AS first_tag
FROM paimon.my_db.user_events
WHERE variant_get(attributes, '$.vip', 'boolean') = true;

Supported functions:

Function Notes
parse_json(json) Parses a JSON string into VARIANT; invalid JSON returns an error
try_parse_json(json) Parses a JSON string into VARIANT; invalid JSON returns NULL
variant_get(v, path[, type]) Extracts a path; missing paths return NULL; invalid casts return an error
try_variant_get(v, path[, type]) Extracts a path; missing paths, invalid paths, and invalid casts return NULL
is_variant_null(v) Returns true for JSON null inside VARIANT; SQL NULL returns false

Path syntax supports the root path $, object access ($.field), quoted object access ($["field"] or $['field']), array indexes ($[0]), and nested combinations such as $.items[0].price.

The optional type argument is a string literal. Supported result types are variant (or omitted), boolean, byte / tinyint, short / smallint, int / integer, long / bigint, float, double, decimal(p, s), and string.

When type is omitted or set to variant, variant_get returns a nested VARIANT value that can be passed to another variant_get call:

SELECT
    variant_get(
        variant_get(payload, '$.device'),
        '$.os',
        'string'
    ) AS os
FROM paimon.my_db.user_events;

Missing paths return SQL NULL. JSON null is represented as a non-SQL-null Variant value, so use is_variant_null when you need to distinguish it:

SELECT
    is_variant_null(parse_json('null')) AS json_null,
    is_variant_null(NULL) AS sql_null;

JSON Text Functions

SQLContext registers these functions without requiring a catalog:

Function Result
json_value(text, path), json_extract_scalar(text, path) An unquoted string, number, or boolean; objects and arrays return NULL
get_json_object(text, path) Unquoted strings, scalar text, or serialized object/array fragments
get_json_string, json_extract_string, json_get_str Aliases of get_json_object with the same two arguments
json_valid(text) Whether text can be parsed as a Paimon JSON Variant
json_format(text_or_variant) Compact JSON serialization, retaining JSON string quotes and JSON null
SELECT json_value('{"a":[1,"hello"]}', '$.a[1]'); -- hello
SELECT get_json_object('{"a":[1,2]}', '$.a'); -- [1,2]
SELECT json_format(parse_json('{"a":1}')); -- {"a":1}

Extraction supports the existing Variant path subset: $, .name, ['name'] / ["name"], and zero-based [index]. It does not implement wildcards, recursive descent, JSONPath predicates, or SQL/JSON RETURNING, ON EMPTY, and ON ERROR clauses. Extraction returns SQL NULL for invalid JSON, invalid/missing paths, JSON null, or SQL null arguments.

The functions use the same parser as parse_json, including its rejection of duplicate object keys. json_valid returns false for rejected text and SQL NULL for SQL null. json_format reports malformed text as an error rather than converting it to null. JSON text and the binary Paimon Variant encoding are distinct: extraction takes text; json_format accepts either representation. Dictionary-encoded string columns have the same JSON text semantics.

Variant Shredding

Variant shredding stores selected fields from a VARIANT column as typed physical fields in Parquet files while keeping the logical table schema as VARIANT. Reads are automatic: when a projected VARIANT column is stored in shredded physical form, Paimon Rust assembles it back into the normal value + metadata representation before returning the batch.

Use a configured shredding schema when the hot fields are known in advance:

CREATE TABLE paimon.my_db.shredded_events (
    user_id BIGINT,
    payload VARIANT
) WITH (
    'file.format' = 'parquet',
    'variant.shreddingSchema' =
        '{"type":"ROW","fields":[{"name":"payload","type":{"type":"ROW","fields":[{"name":"event","type":"STRING"},{"name":"score","type":"DOUBLE"},{"name":"city","type":"STRING"}]}}]}'
);

The configured schema is a Paimon ROW type encoded as JSON. Field IDs may be omitted; Paimon Rust assigns them by position. Each top-level field name must match a VARIANT column to shred. The field's type describes the typed fields to extract from that Variant value; values that do not match the typed field still remain in the Variant payload so the logical value can be rebuilt on read.

Use inferred shredding when the hot fields should be discovered from the first rows written by each data-file writer:

CREATE TABLE paimon.my_db.inferred_events (
    user_id BIGINT,
    payload VARIANT
) WITH (
    'file.format' = 'parquet',
    'variant.inferShreddingSchema' = 'true',
    'variant.shredding.maxInferBufferRow' = '4096',
    'variant.shredding.maxSchemaDepth' = '50',
    'variant.shredding.maxSchemaWidth' = '300',
    'variant.shredding.minFieldCardinalityRatio' = '0.1'
);

When both configured and inferred shredding are set, the configured schema takes precedence. Shredding currently applies to Parquet data-file writes; ordinary non-shredded VARIANT files continue to read normally.

Current limitations:

  • schema_of_variant, schema_of_variant_agg, to_variant_object, variant_explode, and variant_explode_outer are not implemented yet.
  • variant_get currently casts to scalar types and VARIANT. It does not yet cast directly to ARRAY, MAP, or STRUCT.
  • Simple variant_get and try_variant_get expressions over a VARIANT column, a literal path, and a scalar literal type can be pushed into scans as Variant extraction fields for projections and filters. Predicate translation through variant_get is still not applied to Paimon/Parquet statistics; DataFusion evaluates those filters after reading the extracted field.

With a raw DataFusion SessionContext, register these scalar functions explicitly:

use paimon_datafusion::register_variant_functions;

register_variant_functions(&ctx);

Binary casts in queries

CAST and TRY_CAST accept BINARY, BINARY(n), VARBINARY, VARBINARY(n) and VARBINARY(MAX) through SQLContext.

  • Without a length, casts preserve the bytes. Strings use their UTF-8 bytes.
  • BINARY(n) truncates to at most n bytes and right-pads shorter values with zero bytes.
  • VARBINARY(n) truncates to at most n bytes without padding.
  • Widths are byte counts, including for multibyte text. They must be positive and fit an Arrow binary offset. SQL NULL remains NULL.
SELECT to_base64(CAST('ab' AS BINARY(4))); -- YWIAAA==
SELECT to_base64(CAST('abcd' AS VARBINARY(2))); -- YWI=

The source conversions supported by DataFusion's binary cast remain in effect. TRY_CAST preserves its native conversion-failure behavior; an invalid type length is a planning error.

DDL

DATABASE

SHOW DATABASES;
CREATE DATABASE paimon.my_db;
USE paimon.my_db;
DROP DATABASE paimon.my_db CASCADE;

CREATE DATABASE supports IF NOT EXISTS, and DROP DATABASE supports IF EXISTS and CASCADE. CREATE SCHEMA and DROP SCHEMA remain supported as compatibility aliases.

Metadata Listings

SHOW CATALOGS;
SHOW CATALOGS LIKE 'paim%';
SHOW DATABASES LIKE 'report%';
SHOW SCHEMAS IN paimon LIKE 'report%';
SHOW TABLES IN reporting;
SHOW TABLES FROM paimon.reporting LIKE 'daily_%';

SHOW SCHEMAS is an alias for SHOW DATABASES; both return database_name. SHOW CATALOGS returns catalog_name. Database listings default to the current catalog and accept IN catalog or FROM catalog.

Table listings retain the information_schema.tables output columns. Unqualified SHOW TABLES retains DataFusion's listing across catalogs. A scope or filter restricts the listing to the current catalog/database unless an explicit database or catalog.database is supplied. Identifiers follow the session's normal quoting and case rules. LIKE uses SQL % and _ wildcards; ILIKE ignores case. Table providers and data files are not loaded for listing. Scoped and filtered table listings access only the selected namespace and propagate its metadata-listing errors. Session-only temporary namespaces and the enabled information_schema namespace are included. FULL, EXTENDED, HISTORY, LIMIT, STARTS WITH, typed scopes, and WHERE filters are rejected rather than silently ignored.

CREATE TABLE

CREATE TABLE paimon.my_db.users (
    id INT NOT NULL,
    name STRING,
    age INT,
    PRIMARY KEY (id)
) WITH ('bucket' = '4');

IF NOT EXISTS is supported:

CREATE TABLE IF NOT EXISTS paimon.my_db.users (
    id INT NOT NULL
);

Top-level column identifiers in persistent Paimon DDL follow datafusion.sql_parser.enable_ident_normalization. With the default value true, unquoted identifiers are stored in lowercase, while quoted identifiers preserve their spelling. This applies consistently to column definitions, primary keys, partition keys, and ALTER TABLE column operations.

CREATE TABLE paimon.my_db.unquoted_example (ID INT);   -- stores `id`
CREATE TABLE paimon.my_db.quoted_example ("ID" INT);   -- stores `ID`

Set the option to false to preserve unquoted spelling:

SET datafusion.sql_parser.enable_ident_normalization = false;
CREATE TABLE paimon.my_db.preserved_example (ID INT);  -- stores `ID`

Changing the option does not rename existing schema fields. Quote an existing mixed-case or uppercase field when normalization is enabled.

Unsupported syntax (will return an error): - CREATE EXTERNAL TABLE - LOCATION - CREATE TABLE AS SELECT

Partitioned Tables

Use PARTITIONED BY to specify partition columns. Partition columns must already be declared in the column definitions and must not include a type:

CREATE TABLE paimon.my_db.events (
    id INT NOT NULL,
    name STRING,
    dt STRING,
    PRIMARY KEY (id, dt)
) PARTITIONED BY (dt)
WITH ('bucket' = '2');

Invalid usage (will return an error):

-- Partition columns must not specify a type
CREATE TABLE paimon.my_db.events (
    id INT NOT NULL,
    dt STRING
) PARTITIONED BY (dt STRING);

Complex Types

CREATE TABLE paimon.my_db.complex_types (
    id INT NOT NULL,
    tags ARRAY<STRING>,
    props MAP(STRING, INT),
    address STRUCT<city STRING, zip INT>,
    PRIMARY KEY (id)
);

DROP TABLE

DROP TABLE paimon.my_db.users;
DROP TABLE IF EXISTS paimon.my_db.users;

DROP VIEW

Drop one persistent view from a REST Catalog:

DROP VIEW active_users;
DROP VIEW IF EXISTS my_db.active_users;
DROP VIEW IF EXISTS paimon.my_db.active_users;

IF EXISTS ignores only a missing view; authorization, server, and network errors are still returned. Only one persistent view target is supported per statement. CASCADE, RESTRICT, PURGE, and other drop modifiers are rejected. Catalog implementations without persistent view support return Unsupported.

CREATE TEMPORARY TABLE

Create an in-memory temporary table from a query result. Temporary tables exist only for the lifetime of the SQLContext instance and are automatically cleaned up when the context is dropped.

-- Without column types (types inferred from the query)
CREATE TEMPORARY TABLE paimon.my_db.source AS SELECT * FROM (VALUES (1, 'alice'), (2, 'bob')) AS t(id, name);

-- With explicit column types (recommended when integer precision matters)
CREATE TEMPORARY TABLE paimon.my_db.source (id INT, name STRING) AS SELECT * FROM (VALUES (1, 'alice'), (2, 'bob')) AS t(id, name);

IF NOT EXISTS is supported — if the table already exists, the statement is silently ignored:

CREATE TEMPORARY TABLE IF NOT EXISTS paimon.my_db.source AS SELECT 1;

Note: When using VALUES without explicit column types, DataFusion infers integer literals as Int64. If the temporary table will be used as a source in MERGE INTO against a Paimon table with Int32 columns, specify the column types explicitly to avoid type mismatch errors.

CREATE TEMPORARY VIEW

Create a temporary view from a query:

CREATE TEMPORARY VIEW paimon.my_db.active_users AS SELECT * FROM paimon.my_db.users WHERE id > 0;

IF NOT EXISTS is supported:

CREATE TEMPORARY VIEW IF NOT EXISTS paimon.my_db.active_users AS SELECT * FROM paimon.my_db.users WHERE id > 0;

DROP TEMPORARY TABLE / DROP TEMPORARY VIEW

Remove a temporary table or view:

DROP TEMPORARY TABLE paimon.my_db.source;
DROP TEMPORARY TABLE IF EXISTS paimon.my_db.source;
DROP TEMPORARY VIEW paimon.my_db.active_users;
DROP TEMPORARY VIEW IF EXISTS paimon.my_db.active_users;

ALTER TABLE

-- Add a column
ALTER TABLE paimon.my_db.users ADD COLUMN email STRING;

-- Drop a column
ALTER TABLE paimon.my_db.users DROP COLUMN age;

-- Rename a column
ALTER TABLE paimon.my_db.users RENAME COLUMN name TO username;

-- Rename a table
ALTER TABLE paimon.my_db.users RENAME TO members;

-- Set table properties
ALTER TABLE paimon.my_db.users SET TBLPROPERTIES('data-evolution.enabled' = 'true');

IF EXISTS is supported:

ALTER TABLE IF EXISTS paimon.my_db.users ADD COLUMN age INT;

Alternate column changes and extended descriptions

ALTER TABLE ... MODIFY COLUMN name type [NULL | NOT NULL] [COMMENT 'text'] uses existing scalar schema-evolution validation. Unspecified nullability and comments, field IDs and stored data are preserved. The existing alter-column-null-to-not-null.disabled policy still applies. Temporary objects, FIRST/AFTER, defaults and nested/container type changes are rejected before a schema update; nested field IDs must not be reassigned by a type declaration.

DESCRIBE [TABLE] EXTENDED table and FORMATTED return Paimon columns with comments plus catalog/database/table, schema ID, location, table comment, keys and sorted table properties. Credential-like property values are redacted. Identifier normalization follows the session setting and quoted names are preserved. Temporary/native objects and views keep existing native DESCRIBE behavior.

Table and column comments

CREATE TABLE db.t (id INT COMMENT 'identifier') COMMENT 'description';
ALTER TABLE db.t ALTER COLUMN id COMMENT 'new description';
COMMENT ON TABLE db.t IS 'new table description';
COMMENT ON COLUMN db.t.id IS 'new column description';
COMMENT ON TABLE db.t IS NULL;
COMMENT ON COLUMN db.t.id IS NULL;

These statements update catalog metadata without changing field IDs, types or stored rows. Quoted identifiers retain their case; unquoted names follow the session's identifier-normalization setting. Column comments address top-level columns. Clearing a column comment uses Paimon's empty-description encoding.

ALTER TABLE IF EXISTS ... ALTER COLUMN ... COMMENT ignores a missing table, but still reports a missing column. The nonstandard COMMENT IF EXISTS form is rejected. Comments on temporary objects, including temporary objects that shadow persistent tables, and writes to branch references are rejected.

DML

The table type determines which row-level DML operations are supported:

Operation Append-only table Primary-key table Data-evolution row-tracking table (no primary key)
INSERT INTO Supported Supported Supported
INSERT OVERWRITE Supported Supported Supported
INSERT OVERWRITE ... PARTITION Supported for partitioned tables Supported for partitioned tables Supported for partitioned tables
TRUNCATE TABLE Supported Supported Supported
ALTER TABLE ... DROP PARTITION Supported for partitioned tables Supported for partitioned tables Supported for partitioned tables
UPDATE Supported via Copy-on-Write Not supported Supported via row-id update
DELETE Supported via Copy-on-Write Supported for eligible deduplicate tables Supported when deletion vectors are enabled
MERGE INTO Supported via Copy-on-Write Not supported Supported for matched UPDATE, matched DELETE with deletion vectors, and not-matched INSERT

A data-evolution row-tracking table must have both 'data-evolution.enabled' = 'true' and 'row-tracking.enabled' = 'true', and must not have primary keys. DELETE and matched DELETE in MERGE INTO additionally require 'deletion-vectors.enabled' = 'true'. Primary-key row-level UPDATE and MERGE INTO are not supported.

INSERT INTO

INSERT INTO paimon.my_db.users VALUES (1, 'alice'), (2, 'bob'), (3, 'carol');

INSERT INTO ... SELECT ... is also supported:

INSERT INTO paimon.my_db.users SELECT * FROM source_table;

An INSERT INTO an append-only table with 'bucket' = '-1' (the default) can encode data files with several writers. The writer count is the table option sink.parallelism, capped at datafusion.execution.target_partitions; the sink deals input batches to the writers round-robin, and their files are committed together as one snapshot. Without the option, or with -1 (Flink's default parallelism), one writer is used. Primary-key tables, fixed-bucket tables and INSERT OVERWRITE always use one writer, but the option must still be a positive integer or -1.

Each writer rolls its own files in every partition it receives rows for, so on a partitioned table the file count grows to writers × partitions where one writer leaves one file per partition. sink.parallelism is the same option Flink reads for its sink, so a table that already sets it for Flink jobs also writes in parallel from DataFusion, with at most target_partitions writers whatever the Flink value is.

ALTER TABLE paimon.my_db.events SET TBLPROPERTIES ('sink.parallelism' = '4');

For VARIANT columns, convert JSON text with parse_json or try_parse_json:

INSERT INTO paimon.my_db.user_events (user_id, event_time, payload, attributes, dt)
VALUES (
    1,
    TIMESTAMP '2024-01-01 10:00:00',
    parse_json('{"event":"login","device":{"os":"ios"}}'),
    try_parse_json('{"vip":true,"tags":["mobile"]}'),
    '2024-01-01'
);

For primary-key tables, records with duplicate keys are deduplicated according to the merge engine (default: Deduplicate engine, where the last written value wins).

Mosaic Read Scope

The Mosaic reader supports scalar, temporal, array, and map columns. It uses row-group statistics for conservative pruning when they are present. This pruning is not row-level filter enforcement; DataFusion still applies SQL filters above the reader to produce exact query results.

Mosaic writes use Zstd and support mosaic.num-buckets, file.block-size, and mosaic.stats-columns. Selected statistics are written to the manifest for file pruning. Mosaic bloom filters are not supported.

INSERT OVERWRITE

For partitioned tables, INSERT OVERWRITE replaces only the affected partitions. For unpartitioned tables, it replaces the entire table:

-- Dynamic partition overwrite: overwrites only the dt='2024-01-01' partition
INSERT OVERWRITE paimon.my_db.events VALUES ('2024-01-01', 10, 'new_alice');

Hive-style static partition overwrite is also supported via the PARTITION clause. The source query provides only non-partition columns, and partition values are specified explicitly:

-- Static partition overwrite: explicitly specify the target partition
INSERT OVERWRITE paimon.my_db.events PARTITION (dt = '2024-01-01')
VALUES (10, 'new_alice'), (20, 'new_bob');

-- With a SELECT source
INSERT OVERWRITE paimon.my_db.events PARTITION (dt = '2024-01-01')
SELECT id, name FROM source_table;

For multi-level partitioned tables, you can specify a subset of partition columns. Unspecified partition columns are read from the source query (dynamic partition). All sub-partitions under the specified partition are replaced:

-- Only dt is static; all data under dt='2024-01-01' is replaced.
-- region comes from the source data.
INSERT OVERWRITE paimon.my_db.events PARTITION (dt = '2024-01-01')
VALUES ('us', 10, 'alice'), ('eu', 20, 'bob');

UPDATE

For append-only tables (no primary key), updates are executed using Copy-on-Write:

UPDATE paimon.my_db.t SET name = 'a_new' WHERE id = 1;

For data-evolution row-tracking tables without primary keys, updates are executed with row-id-based partial-column writes.

Ordinary primary-key tables using merge-engine=deduplicate support UPDATE by writing complete updated rows with the UPDATE_AFTER row kind. All SET expressions use the original row; unassigned columns retain their values. Primary-key and partition columns cannot be assigned.

The target and its live self-references in predicates and SET expressions use one captured snapshot, including uncompacted deletion-vector versions. Target validation and read-option restrictions follow the primary-key DELETE rules below. Explicit historical and incremental views keep their selected read view.

Configured sequence values are preserved unless assigned by SET, and the normal sequence sort order applies to the updated rows. A lower-priority assigned sequence or a higher-priority concurrent upsert can leave the stored row unchanged. The affected-row count reports rows matching the predicate in the captured snapshot, including updates that lose under these merge rules. This does not provide serializable updates.

No matches publish no snapshot. Publication retries reuse prepared files without rescanning; repeating the SQL statement is a new operation. A commit error can occur after publication, so prepared files are retained and the error identifies the table and commit user.

Other merge engines, rowkind.field, query-authorized targets, aliases, joins, FROM, RETURNING, OUTPUT, ORDER BY, LIMIT, target modifiers and tuple or qualified/nested SET targets are not supported by this primary-key UPDATE path.

DELETE

For append-only tables, deletes are executed using Copy-on-Write:

DELETE FROM paimon.my_db.t WHERE name = 'b';

For data-evolution row-tracking tables without primary keys, deletes are executed via deletion vectors and require 'deletion-vectors.enabled' = 'true'.

Primary-key tables using merge-engine=deduplicate with data evolution disabled support DELETE by writing tombstones for the logical rows matching the predicate. DELETE without WHERE targets every row in the captured read snapshot. A delete with no matches returns zero without publishing a snapshot.

The target and its live self-references, including those through views, use one captured snapshot and merge uncompacted primary-key versions before applying predicates. On deletion-vector tables this applies even when deletion-vectors.merge-on-read is disabled. Ordinary queries retain their configured read behavior. Explicitly historical and incremental views retain their selected snapshots or timestamp windows and configured read behavior. DELETE targets cannot use time-travel or incremental read options.

The target and its live self-references must use the table's configured merge engine, sequence fields, sort order, bucket count, bucket keys and bucket function. Conflicting session options or options captured by a view are rejected before scanning, as are overrides that enable or disable deletion vectors or data evolution, or change row-kind handling or query authorization. Read tuning options, such as read.batch-size, remain supported.

Tombstones preserve configured sequence values and sort order, so concurrent upserts follow the normal primary-key merge rules. With the default ascending order, a higher-sequence upsert survives; without a sequence field, normal write ordering applies. This does not provide serializable predicate deletion.

The affected-row count reports logical rows matching the predicate in the captured snapshot. It includes keys whose concurrent upserts survive under the merge rules above.

Publication retries reuse prepared files without rescanning. Repeating the SQL statement scans a new snapshot and is a new operation. A commit error does not necessarily mean that the deletion was rolled back. Prepared files are retained on publication errors; the error identifies the table and commit user for investigating an uncertain outcome.

Other primary-key merge engines, ignore-delete=true (including its fallback options), rowkind.field, query-authorized primary-key tables, aliases, multi-target/join deletes, USING, RETURNING, ORDER BY, LIMIT, OUTPUT, and target table modifiers are not supported by this path.

MERGE INTO

Standard SQL MERGE INTO syntax is supported, allowing INSERT, UPDATE, and DELETE in a single statement:

MERGE INTO paimon.my_db.target
USING source ON target.a = source.a
WHEN MATCHED THEN UPDATE SET a = source.a, b = source.b, c = source.c
WHEN NOT MATCHED THEN INSERT (a, b, c) VALUES (source.a, source.b, source.c);

Delete matched rows only:

MERGE INTO paimon.my_db.target
USING source ON target.a = source.a
WHEN MATCHED THEN DELETE;

UPDATE + INSERT combination:

MERGE INTO paimon.my_db.target
USING source ON target.a = source.a
WHEN MATCHED THEN UPDATE SET b = source.b
WHEN NOT MATCHED THEN INSERT (a, b, c) VALUES (source.a, source.b, source.c);

The source can also be a subquery:

MERGE INTO paimon.my_db.target
USING (SELECT * FROM other_table WHERE active = true) AS source
ON target.id = source.id
WHEN MATCHED THEN UPDATE SET name = source.name;

For append-only tables, MERGE INTO uses Copy-on-Write file rewriting and supports matched UPDATE, matched DELETE, and not-matched INSERT. For data-evolution row-tracking tables without primary keys, MERGE INTO uses the _ROW_ID virtual column for row-level tracking and supports matched UPDATE, matched DELETE when deletion vectors are enabled, and not-matched INSERT. Primary-key tables are not supported for MERGE INTO.

The source is captured once per execution. On row-ID or CoW delete conflicts, the operation retries up to five attempts, refreshing the target scan and writers while replaying the captured source. Update, delete, and insert messages are published in one commit. Multiple source rows selecting conflicting actions for the same target row fail without publishing any actions. A sole unconditional matched DELETE collapses duplicate matches and counts each deleted target once.

Append-only tables support multiple matched clauses and execute the first applicable action. Data-evolution tables support a single matched UPDATE or DELETE with an optional predicate. A clause predicate must evaluate to TRUE for its action to apply. If no matched action applies, the target row is preserved. Both table types support conditional WHEN NOT MATCHED inserts, which apply only to source rows without an ON match. The Paimon executor does not support NOT MATCHED BY SOURCE or subqueries inside ON or action expressions. Native planner restrictions also apply, including UPDATE SET *, INSERT *, OUTPUT, and action-level WHERE predicates. After declaring a table alias, use that alias in column references.

SQLContext expands positional MERGE inserts to persisted column names for data-evolution tables, excluding the virtual _ROW_ID. When using a raw DataFusion SessionContext, list the insert columns explicitly for these tables.

TRUNCATE TABLE

Truncate an entire table or specific partitions:

-- Truncate the entire table
TRUNCATE TABLE paimon.my_db.users;

-- Truncate specific partitions
TRUNCATE TABLE paimon.my_db.events PARTITION (dt = '2024-01-01');

DROP PARTITION

Drop specific partitions from a table using ALTER TABLE ... DROP PARTITION:

ALTER TABLE paimon.my_db.events DROP PARTITION (dt = '2024-01-01');

Multiple partition key-value pairs can be specified:

ALTER TABLE paimon.my_db.events DROP PARTITION (dt = '2024-01-01', region = 'us');

Format Table Partitions

A type=format-table table loaded from a REST Catalog can have its partitions managed by the catalog instead of discovered from the directory layout. The catalog registration is then the authoritative partition set: it decides what a scan reads, and a directory nobody registered is not part of the table.

A table opts in with 'metastore.partitioned-table' = 'true'. The statements below apply only to such a table — a partitioned internal Format Table, loaded from a REST Catalog, with a non-engine format-table.implementation.

A partition the catalog holds at a custom location (the partition option path) is not read from that location yet: a scan that reaches it fails rather than reading the table directory in its place.

No statement of this client writes rows into a Format Table. INSERT, UPDATE, DELETE and MERGE INTO are all refused, whether or not the catalog manages the partitions.

SHOW PARTITIONS

SHOW PARTITIONS paimon.my_db.events;
SHOW PARTITIONS paimon.my_db.events PARTITION (dt = '2024-01-01');

Partition names are returned in the escaped key=value/... form, sorted. The optional PARTITION clause keeps only the partitions matching the given values; it may fix any subset of the partition keys.

ADD PARTITION

ALTER TABLE paimon.my_db.events ADD PARTITION (dt = '2024-01-01', region = 'us');
ALTER TABLE paimon.my_db.events ADD IF NOT EXISTS PARTITION (dt = '2024-01-01')
                                                 PARTITION (dt = '2024-01-02');

Every specification must fix all partition keys. A value is read with its column type the way Paimon reads partition strings, so month = '01' and month = 01 both register the INT value 1, and a BOOLEAN column accepts t, true, y, yes and 1 or their false counterparts. The partitions are registered with the catalog first and their directories are created afterwards, so a failure to create a directory leaves the registration in place and re-running the statement with IF NOT EXISTS completes it. Without IF NOT EXISTS, an already registered partition is an error. A custom LOCATION is not supported: the directory always follows the table's partition layout.

DROP PARTITION

ALTER TABLE paimon.my_db.events DROP PARTITION (dt = '2024-01-01', region = 'us');
ALTER TABLE paimon.my_db.events DROP IF EXISTS PARTITION (dt = '2024-01-01');

A specification that fixes only some of the partition keys drops every registered partition it matches, and the fixed keys need not be a leading prefix — on a (dt, region) table, DROP PARTITION (region = 'us') drops the us partition of every date. A specification that fixes all keys names one partition, so a missing one is an error unless IF EXISTS is given; a partial one describes a set that is allowed to come out empty.

One statement may carry several specifications, each DROP PARTITION in its own clause:

ALTER TABLE paimon.my_db.events DROP PARTITION (dt = '2024-01-01'),
                                DROP PARTITION (dt = '2024-01-02');

The catalog registration is removed first, then the directory is deleted by the client — the catalog never deletes data. If the deletion fails the partition is already invisible and the directory may survive; repair the file system and remove it there rather than re-registering it. A partition at a custom location is only unregistered; its directory is left where it is.

MSCK REPAIR TABLE

Reconcile the catalog registrations with the directories that actually exist:

MSCK REPAIR TABLE paimon.my_db.events;                  -- same as ADD PARTITIONS
MSCK REPAIR TABLE paimon.my_db.events ADD PARTITIONS;   -- register discovered directories
MSCK REPAIR TABLE paimon.my_db.events DROP PARTITIONS;  -- unregister vanished directories
MSCK REPAIR TABLE paimon.my_db.events SYNC PARTITIONS;  -- both

Repair is metadata-only: it never deletes data. Directory discovery and the catalog listing both complete before any change is made, so a listing failure cannot turn a truncated view of the table into a DROP diff. There is no dry-run and no scope argument — repair always covers the whole table. A partition at a custom location is never unregistered by repair.

ANALYZE TABLE

Measure what the registered partitions hold and report it to the catalog:

ANALYZE TABLE paimon.my_db.events COMPUTE STATISTICS NOSCAN;  -- files, size, last file time
ANALYZE TABLE paimon.my_db.events COMPUTE STATISTICS;         -- also row counts
ANALYZE TABLE paimon.my_db.events PARTITION (dt = '2024-01-01') COMPUTE STATISTICS;

Each partition is measured through the listing a scan uses, so it counts exactly the files a query reads and leaves staging entries such as _temporary out. NOSCAN stops at the listing: it reports the file count, the total size and the latest file modification time. Without NOSCAN the row count is also read from each file's footer, which Parquet and ORC keep; for other formats it stays unknown. A footer that cannot be read leaves the row count of its partition unknown rather than short, and a partition without files holds exactly zero rows. A field the statement does not measure, such as the row count under NOSCAN, is reported as unknown (-1).

The measurement replaces the statistics the catalog holds for each partition; it never adds or removes a partition. PARTITION (...) must give values for a leading run of the partition keys and selects every registered partition under them: on a (dt, region) table, PARTITION (dt = '2024-01-01') measures every region of that date, while PARTITION (region = 'us') is rejected. It is an error when no registered partition matches. A selected partition at a custom location fails the statement, and FOR COLUMNS is not supported.

A listing failure fails the statement before anything is reported. format-table.statistics.parallelism (default 8) bounds the storage requests in flight, listings and footer reads alike. Set it for the session:

SET 'paimon.format-table.statistics.parallelism' = '16';

TRUNCATE TABLE

TRUNCATE TABLE paimon.my_db.events;
TRUNCATE TABLE paimon.my_db.events PARTITION (dt = '2024-01-01');

Unlike the statements above, this works on any Format Table, including one that discovers its partitions from the directory layout. It deletes data files only: partition directories and catalog registrations stay. Every non-hidden file in a partition directory is deleted, whatever its extension, because other engines read those files too; staging entries such as _temporary are left alone.

With catalog-managed partitions, the statement empties the registered partitions and reports zero statistics for each of them, replacing what the catalog holds. A directory nobody registered is not part of the table and is not touched. Without them, it empties the partition directories found below the table, and an unpartitioned table loses the data files in its table directory. PARTITION (...) must give values for a leading run of the partition keys, as in ANALYZE TABLE, but a column named without a value is rejected rather than read as every value. It is an error when no partition matches. A selected partition at a custom location fails the statement before anything is deleted.

Procedures

Use CALL to invoke built-in procedures. All procedures are under the sys namespace.

Compatibility procedures

These procedures accept named arguments and positional arguments in the order shown. Other procedures continue to use named arguments. When qualifying one of these procedures, use sys or catalog.sys. Unquoted procedure names follow the session's identifier normalization setting. An explicit catalog qualifier checks the exact registered name first, then applies that setting if the name is unquoted and no exact match exists.

Procedure Arguments in positional order Result
rollback table, version, snapshot, tag previous_snapshot_id and current_snapshot_id as BIGINT
drop_partition table, partitions... OK after committing the partition drop
create_function function, inputParams, returnParams, deterministic, comment, options Boolean result
drop_function function Boolean result
vector_search table, vector_column, query_vector, top_k, projection Rows from the existing vector search table function
full_text_search table, column, query, top_k, projection Rows from the existing full-text search table function

rollback takes exactly one of version, snapshot, or tag. A version containing only digits means a snapshot ID. Use tag for a numeric tag name. For a REST catalog, rollback first tries the resolved snapshot ID. A catalog that only supports restoring an expired snapshot by tag receives a tag rollback after the client checks the tag still points to that snapshot. The client verifies the resulting head before reporting success. A failed verification can follow an applied rollback, so inspect the table state before retrying. For a filesystem catalog, stop concurrent writes before calling rollback. For drop_partition, specify every partition key in each key=value list. Each positional argument represents one partition and can contain a semicolon in a partition value. Use semicolons to separate specifications in the named partitions argument. Whitespace after = is significant for string and binary values. Use ALTER TABLE ... DROP PARTITION for values containing commas. The procedure operates on Paimon FileStore tables.

CALL sys.rollback('my_db.events', 'release_1');
CALL sys.drop_partition('my_db.events', 'dt=2026-10-01', 'dt=2026-10-02');

create_function registers Catalog metadata on catalogs that support persistent functions. Both parameter lists are JSON arrays of Paimon data fields. Omit IDs from an entire list to assign distinct IDs starting at zero, including nested fields. Function names inside string arguments preserve case. Quote a name component to include a dot. The optional deterministic argument defaults to true and options takes comma-separated key=value pairs. Use CREATE FUNCTION to register an executable SQL definition.

CALL sys.create_function(
    'my_db.f',
    '[{"name":"x","type":"BIGINT"}]',
    '[{"name":"result","type":"BIGINT"}]'
);
CALL sys.drop_function('my_db.f');

The search procedures accept top_k between 1 and 10000 and an optional comma-separated projection. vector_search accepts a JSON array or a comma-separated vector. Both return typed table rows and honor session time-travel options such as paimon.scan.version. Per-call search options, filters and Flink's JSON score output are unsupported by these adapters.

CALL sys.vector_search('my_db.items', 'embedding', '1.0, 0.0', 10, 'id');
CALL sys.full_text_search('my_db.docs', 'content', 'paimon', 10, 'id, title');

reset_consumer

Create or overwrite a consumer's next snapshot to consume:

CALL sys.reset_consumer(
    table => 'paimon.test_db.events',
    consumer_id => 'pipeline_a',
    next_snapshot_id => 12
);

next_snapshot_id must be a positive BIGINT identifying an existing live snapshot. Missing, expired, or invalid snapshots are rejected before changing consumer progress; tag-retained metadata alone is not sufficient. The ID is the next snapshot to process using the existing incremental or changelog scan rules, rather than a full-state read.

Omit next_snapshot_id to delete the consumer record. Deleting a missing consumer succeeds:

CALL sys.reset_consumer(
    table => 'paimon.test_db.events',
    consumer_id => 'pipeline_a'
);

Only filesystem-managed FileStore tables are supported. REST-managed tables and format tables are not supported. Consumer IDs must be non-empty names without path separators or trailing whitespace; IDs are never trimmed. Arguments use the existing named => syntax; explicit NULL and positional arguments are not supported. Successful calls return OK. These operations change only consumer metadata, without publishing snapshots or changing table data, snapshot files, or manifests.

A new scan with the same consumer ID resumes the stored progress unless the caller explicitly restores a checkpoint, which takes precedence. After deletion, a new scan without an explicit checkpoint follows its existing initial scan behavior. Scans that have already loaded their progress do not switch positions when the record changes. A running consumer's checkpoint completion can overwrite or recreate the record, so stop the consumer before adjusting its progress manually.

clear_consumers

Delete a group of consumer records while retaining exceptions:

CALL sys.clear_consumers(
    table => 'paimon.test_db.events',
    including_consumers => 'pipeline_.*',
    excluding_consumers => 'pipeline_critical|pipeline_backup'
);

Both expressions match the entire consumer ID, excluding the consumer- filename prefix. A record is deleted only when inclusion matches and exclusion does not. For example, pipeline_a does not match pipeline_a_backup; use pipeline_a.* to match that suffix. Alternation and inline multiline flags do not bypass the whole-ID requirement.

Patterns use Rust regex syntax, including groups, alternation, character classes, repetition, Unicode classes, and inline flags such as (?i), (?m), (?s), and (?x). Look-around and backreferences are not supported. This is not the Java Pattern dialect: for example, \w, \d, and \s are Unicode-aware by default, and . excludes LF but matches CR. Use explicit classes or flags when those distinctions matter. Invalid expressions fail before any files are deleted, including an invalid exclusion when inclusion matches nothing.

Omitting including_consumers, or passing an empty or Java-whitespace-only string, uses .*. The equivalent exclusion means no exclusions. Nonbreaking spaces such as U+00A0 are literal patterns, not blank placeholders. Nonblank patterns are never trimmed. The default .* does not match IDs containing LF; use (?s).* if needed. To clear all ordinary consumer IDs:

CALL sys.clear_consumers(table => 'paimon.test_db.events');

The procedure uses the same table resolution and branch-scoped manager as reset_consumer; it does not add branch-qualified name resolution to catalogs. REST-managed tables and format tables are not supported. Arguments require named => syntax; explicit NULL and positional arguments are not supported. Successful calls return OK, including when the consumer directory is absent or nothing matches.

Only filenames are inspected, so corrupt consumer JSON does not prevent cleanup. Directories and unrelated files are skipped. A matching listed path with trailing whitespace is rejected before deletion because OpenDAL could otherwise normalize it to a different consumer's path. Filenames are taken from the storage listing; the filesystem backend normalizes trailing whitespace before returning entries. Retained consumer records, table data, snapshots, and manifests are unchanged. Storage errors are propagated; deletion is not atomic and an error can leave some matching records already removed.

This operation does not determine whether a consumer has stopped. Stop the target consumers before cleanup: running scans retain their loaded positions, and checkpoint completion can recreate deleted records. A fresh scan without an explicit checkpoint follows its existing initial scan behavior after its consumer record is deleted.

create_branch

Create a branch from the main source table:

-- Copy the latest schema without inheriting data, snapshots, or tags.
CALL sys.create_branch(table => 'paimon.my_db.my_table', branch => 'empty_branch');

-- Start from a tag's historical schema and data.
CALL sys.create_branch(
    table => 'paimon.my_db.my_table',
    branch => 'release_branch',
    tag => 'release',
    ignore_if_exists => true
);

table and branch are required. table accepts database.table or catalog.database.table. Omitting tag creates a branch containing schema metadata only, even when the source table has data. A specified tag must exist; its retained snapshot can be used even after the live snapshot file has expired, provided the referenced schemas, manifests, and data files remain available. The source table is not modified. Query the new branch through $branch_<name> and list it through $branches.

ignore_if_exists defaults to false: an existing branch causes an error. With true, an existing branch is left unchanged and the tag is not resolved, even if the supplied tag does not exist. Both creation and an ignored request return one row with result = 'OK'. Boolean literals and case-insensitive 'true' / 'false' strings are accepted; other values are rejected. This parameter uses the Rust SQL name ignore_if_exists, rather than Java Spark's ignoreIfExists.

Branch names must pass the Core name validator: main, empty or whitespace-only names, pure ASCII numeric names, ., .., path separators, and control characters are rejected. SQL also rejects $ in target branch names because it separates branch and system-table suffixes in object names. Branch names preserve leading and trailing spaces. Tag names are passed unchanged. The REST server validates them for REST tables; Core validates them for filesystem-managed tables and rejects trailing whitespace. Branch-name and source-table validation also apply when ignore_if_exists is true.

Only FileStore tables resolved on main are supported. Format tables, external engine tables, and non-main source tables are rejected. The table argument is passed to the selected catalog without additional branch or system-table suffix resolution. The procedure does not create from another branch.

For tables managed by a REST catalog, the branch is created through the catalog, which owns branch metadata, and the procedure does not write table storage itself. ignore_if_exists checks the catalog's branch list. An existing branch reported by a concurrent creation is also ignored when this option is true. Other catalog errors are returned to the caller.

For filesystem-managed tables, creation reuses the Core metadata-copy path. Existence checks and file copies are not transactional; storage errors are returned and can leave partial branch metadata. ignore_if_exists checks name existence and does not repair a partial creation.

delete_branch

Delete one or more branches:

CALL sys.delete_branch(table => 'paimon.my_db.my_table', branch => 'dev,release_branch');

table and branch are required. table accepts database.table or catalog.database.table. The comma-separated branch names are processed in order, empty entries are skipped, and leading and trailing spaces are preserved. Missing branches are ignored. Names must pass the Core name validator; main and branches configured by scan.primary-branch or scan.fallback-branch cannot be deleted. Unset the corresponding option before deleting such a branch.

REST tables require a main-branch source table and delete branches through the catalog without writing table storage. Filesystem-managed tables use Core's branch manager. The operation returns one row with result = 'OK'. A validation or catalog/storage error stops the batch; earlier deletions are not rolled back.

fast_forward

Fast-forward the main branch to a branch:

CALL sys.fast_forward(table => 'paimon.my_db.my_table', branch => 'release_branch');

table and branch are required. table accepts database.table or catalog.database.table. Only tables managed by a REST catalog are supported: the REST server performs the fast-forward, and the procedure does not write table storage itself. Filesystem-managed tables are rejected. The table argument must resolve to the main branch, and the branch name must pass the Core name validator, so main is rejected. A missing branch returns an error. Success returns one row with result = 'OK'.

create_tag

Create a named tag from a snapshot:

CALL sys.create_tag(table => 'paimon.my_db.my_table', tag => 'my_tag', snapshot_id => 1);

snapshot_id is optional; omitting it uses the latest snapshot. The optional time_retained parameter accepts duration strings such as '1d', '12h', '30min', or '500ms'. A duration string without a unit denotes milliseconds.

CALL sys.create_tag(
    table => 'paimon.my_db.my_table',
    tag => 'daily_backup',
    snapshot_id => 1,
    time_retained => '1d'
);

For filesystem-managed tables, specifying time_retained persists retention and tag creation time in the tag file. For example, '1d' appears as PT24H in the time_retained column of $tags. Omitting time_retained creates a tag without these metadata fields. This procedure does not automatically delete expired tags.

For tables managed by a REST catalog, the tag is created through the catalog: snapshot_id and time_retained are forwarded unchanged, and the server resolves the snapshot (the latest one when snapshot_id is omitted), validates retention, and stores the tag. The table's tag/ directory is not read or written. An existing tag name fails with Tag '...' already exists, as for other tables.

replace_tag

Replace an existing manual tag's snapshot and retention without changing its name:

CALL sys.replace_tag(
    table => 'paimon.my_db.my_table',
    tag => 'release',
    snapshot_id => 1,
    time_retained => '2d'
);

table and tag are required. The tag must already exist. snapshot_id is an optional positive BIGINT; omitting it selects the latest live snapshot when the operation resolves its target. An explicit ID first resolves a live snapshot, then falls back to snapshots preserved by tags in the same branch if the live snapshot does not exist. Other snapshot read errors are returned. No suitable snapshot causes an error without changing the tag. snapshot is not an alias for snapshot_id. Success returns one row with result = 'OK'.

time_retained uses the same duration format as create_tag. Providing it replaces the TTL and resets tagCreateTime to the replacement time, even when the snapshot ID stays the same. Omitting it removes the previous TTL and creation time: the tag is written as plain Snapshot JSON. It does not inherit the old TTL or apply a table's default tag retention. Argument, name, retention, and snapshot resolution errors leave the original tag file unchanged.

Only non-REST FileStore tables and ordinary manual tags are supported. Format tables and engine-served table types are rejected. This procedure does not add branch-qualified SQL name resolution; an already resolved branch Table uses its own snapshot and tag managers. Automatic tag management and external tag callbacks are outside its scope.

Replacement overwrites the tag metadata directly. It does not create a snapshot or clean up old snapshots, manifests, or data files, and it does not run tag expiration. Storage write failures are returned, but atomic replacement and rollback are not guaranteed across storage backends. The existence check and write are separate operations; concurrent tag modifications are not protected by a conditional update or transaction.

create_tag_from_timestamp

Create a named tag from an epoch timestamp in milliseconds (finds the earliest retained snapshot at or after the given time):

CALL sys.create_tag_from_timestamp(table => 'paimon.my_db.my_table', tag => 'my_tag', timestamp => 1234567890000);

This procedure is not supported for tables managed by a REST catalog. Their snapshots may exist only in the catalog, while timestamp selection currently relies on stored snapshot files.

create_tag_from_watermark

This procedure is not supported for tables managed by a REST catalog. Their snapshots may exist only in the catalog, while watermark selection currently relies on stored snapshot files. REST-managed tables are rejected before candidate selection or tag creation.

Create a named tag from the earliest retained snapshot whose watermark is greater than or equal to the requested value. Candidates include live snapshots and historical snapshots preserved by existing tags. Equal watermarks select the earlier snapshot ID; snapshots without a watermark, including the no-watermark sentinel -9223372036854775808, are skipped.

CALL sys.create_tag_from_watermark(
    table => 'paimon.my_db.my_table',
    tag => 'business_checkpoint',
    watermark => 1704067200000,
    time_retained => '7d'
);

table, tag, and watermark are required. watermark is a signed 64-bit integer in milliseconds. It represents the input processing progress recorded by the writer, whereas timestamp in create_tag_from_timestamp refers to snapshot commit time. Selecting by watermark fixes an entire snapshot; it does not filter rows by event time.

The optional time_retained uses the same duration format and metadata as create_tag. This procedure does not automatically delete expired tags. An existing tag name, an invalid argument, or no matching snapshot causes an error without creating or replacing a tag. The call does not wait for a future snapshot.

delete_tag

Delete a named tag:

CALL sys.delete_tag(table => 'paimon.my_db.my_table', tag => 'my_tag');

tag accepts a comma-separated list. Each name is trimmed, empty names are skipped, and missing tags are ignored. For tables managed by a REST catalog, each tag is deleted through the catalog, and the table's tag/ directory is not read or written. Any other error stops the batch; earlier deletions are not rolled back.

rename_tag

Rename a tag:

CALL sys.rename_tag(table => 'paimon.my_db.my_table', tag => 'old_tag', target_tag => 'new_tag');

The source tag must exist and the target tag must not. This procedure is not supported for tables managed by a REST catalog, which has no tag-rename endpoint.

rollback_to

Rollback a table to a specific snapshot or tag:

-- Rollback to a snapshot
CALL sys.rollback_to(table => 'paimon.my_db.my_table', snapshot_id => 1);

-- Rollback to a tag
CALL sys.rollback_to(table => 'paimon.my_db.my_table', tag => 'my_tag');

rollback_to_timestamp

Rollback a table to an epoch timestamp in milliseconds (selects the latest retained snapshot at or before that time):

CALL sys.rollback_to_timestamp(table => 'paimon.my_db.my_table', timestamp => 1234567890000);

This procedure is not supported for tables managed by a REST catalog, for the same reason as create_tag_from_timestamp. Use rollback_to with an explicit snapshot_id or tag instead.

Both timestamp procedures take epoch milliseconds. The $snapshots.commit_time column is a local TIMESTAMP; its raw Arrow value includes the local timezone offset and must not be passed directly as an epoch timestamp.

create_global_index

Build and commit a global index for a table column.

As in Paimon's Flink SQL and Spark SQL integrations, global indexes are created through CALL sys.create_global_index. CREATE INDEX ... ON ... DDL is not supported.

To maintain indexes synchronously after writes, declare columns through options such as global-index.btree.index-columns='id'. Maintenance runs by default when write-only=false; see Automatic global index maintenance for the declaration format and recovery behavior.

CALL sys.create_global_index(
  table => 'paimon.my_db.my_table',
  index_column => 'id',
  index_type => 'btree',
  options => 'btree-index.block-size=64kb,btree-index.bloom-filter.enabled=true,btree-index.compression=zstd,btree-index.compression-level=1'
);

CALL sys.create_global_index(
  table => 'paimon.my_db.my_table',
  index_column => 'tag',
  index_type => 'bitmap',
  options => 'bitmap-index.dictionary-block-size=16kb,bitmap-index.compression=lz4,bitmap-index.compression-level=1'
);

CALL sys.create_global_index(
  table => 'paimon.my_db.my_table',
  index_column => 'tags',
  index_type => 'multivalue',
  options => 'multivalue-index.dictionary-block-size=16kb,multivalue-index.compression=zstd,multivalue-index.compression-level=1'
);

CALL sys.create_global_index(
  table => 'paimon.my_db.places',
  index_column => 'shape',
  index_type => 'rtree'
);

SELECT id FROM paimon.my_db.places WHERE bbox_intersects(shape, 10, 20, 30, 40);

CALL sys.create_global_index(
  table => 'paimon.my_db.my_table',
  index_column => 'message',
  index_type => 'fm',
  options => 'fm-index.partition-size=16mb,fm-index.sa-sample-rate=32,fm-index.compression=lz4'
);

CALL sys.create_global_index(
  table => 'paimon.my_db.my_table',
  index_column => 'content',
  index_type => 'full-text',
  options => 'full-text.tokenizer=jieba,full-text.remove-stop-words=false'
);

Set partitions to build indexes only for matching partitions. As in Flink SQL and Spark SQL, , combines constraints with AND and ; combines alternatives with OR. Omitted partition keys are unrestricted; omitting partitions builds all partitions. Values are parsed using each partition column's type, and the table's partition.default-name selects NULL values. Invalid filters and partition filters on unpartitioned tables are rejected before files are written.

CALL sys.create_global_index(
  table => 'paimon.my_db.my_table',
  index_column => 'id',
  index_type => 'btree',
  partitions => 'dt=2026-10-10,hr=1;dt=2026-10-11,hr=2'
);

Index coverage outside the selected partitions is preserved. If the selected data is already indexed and unchanged, or no data matches, no snapshot is committed.

index_type defaults to btree. It is case-insensitive and surrounding whitespace is ignored. BTree and bitmap global indexes support scalar columns. Multivalue global indexes support ARRAY columns whose element type is supported by sorted indexes, and accelerate array_has/array_contains, array_has_any/arrays_overlap, and array_has_all predicates. Null arrays, empty arrays, and null elements do not create postings; duplicate elements in a row are indexed once. All three sorted index types accept sorted-index.records-per-range (with the legacy btree-index.records-per-range fallback). Their Java-compatible writer options are btree-index.block-size, bitmap-index.dictionary-block-size, or multivalue-index.dictionary-block-size, together with the corresponding *.compression (none, zstd, lz4, or lzo) and *.compression-level options. BTree additionally accepts btree-index.bloom-filter.enabled (default false) to accelerate equality and IN lookups. Per-call options override table options. Bitmap and multivalue global indexes use Java-compatible bitmap files.

BTree reads both Java-compatible V1 and V2 index files. Writing defaults to V1 for compatibility with older readers. Set btree-index.file-version=2 explicitly to use adaptive single-row, delta-list, and Roaring postings; every reader of the table must support V2 before enabling it. Existing index files do not need to be rebuilt for the reader to handle a mix of versions.

BTree also supports composite indexes. List scalar columns in tuple-key order:

CALL sys.create_global_index(
  table => 'paimon.my_db.my_table',
  index_column => 'category,item_number',
  index_type => 'btree'
);

SELECT * FROM paimon.my_db.my_table
WHERE category IN ('a', 'b') AND item_number >= 10;

Equality, IN, or IS NULL on every key column uses tuple point lookups. A leading prefix of those conditions may be followed by >, >=, <, <=, BETWEEN, or IS NOT NULL on the next column. Predicates after the first range column remain data filters; a predicate only on a later key column cannot use this index. Repeated IN values are deduplicated and intersected with other conditions on the same column before expansion, with a maximum of 256 tuple intervals. Larger expansions fall back to another index or an ordinary scan. Decimal literals are rescaled exactly to the column's scale. If a literal cannot be encoded without losing precision (including sub-millisecond bounds on compact timestamps), this definition is declined and data filtering retains the original literal. Independent indexes on other columns continue to be evaluated and intersected for AND predicates.

Prefix and range scans honor btree-index.fallback-scan-max-size for selected files in each source row-ID range group. If any group exceeds the budget, the whole definition is declined; tuple point lookups bypass this budget. When multiple definitions match, selection prefers more bounded columns, then more point-constrained columns, then fewer total key columns. A dedicated BTree or bitmap index on the leading column is preferred for a leading-column-only predicate when it covers the composite definition's coverage. full and detail search modes retain unindexed rows for data filtering.

The Rust builders accept .with_index_columns(&["category", "item_number"]) or .with_index_column("category,item_number"). The list API preserves exact column names, including commas and whitespace. The string API first resolves an exact column name; otherwise it parses a comma-separated list. Rebuilding the same ordered definition fills missing row ranges. Composite, reversed-column, and scalar definitions can coexist. Bitmap, multivalue, and FM builds accept one column.

BTree min/max metadata can also avoid index-file reads when every key matches, there are no nulls, and the file set's row counts prove complete coverage of the same source row-ID range. These all-matching files do not consume the fallback index-scan byte budget; partially matching files still do. The proof is applied separately to each conjunct, so an all-matching bound does not force a full posting-list scan alongside a selective equality on the same column. Multiple lower/upper bounds (including BETWEEN) on one column are tightened into one range query; contradictory bounds produce no indexed candidates without opening index files. Unindexed-tail handling and residual filtering continue to follow the selected global-index search mode.

FM global indexes support character-string columns and exact byte-substring contains, IS NULL, and IS NOT NULL predicates. They use the Java-compatible partitioned V1 format. sorted-index.records-per-range bounds the source rows streamed into each FM index file; fm-index.partition-size and fm-index.partition-row-count bound the encoded partitions within that file. Build options are fm-index.partition-size, fm-index.partition-row-count, fm-index.sa-sample-rate, fm-index.compression, and fm-index.compression-level. Scan-time table options are fm-index.read-cache-size, fm-index.demand-page-size, and fm-index.locate-cost-ratio. When locating a dense result would cost more than the configured ratio, the FM index safely declines evaluation and the normal source scan applies the predicate.

An R-tree indexes one planar GEOMETRY column. The arguments to bbox_intersects(shape, min_x, min_y, max_x, max_y) define a closed rectangle in the column's coordinate system. Touching envelopes match. Empty geometries have no envelope but still match IS NOT NULL. SQL returns null for a null argument. The reader and writer use the Apache Paimon R-tree V1 file format. Exact shape intersection requires a separate geometry predicate.

A bbox lookup in fast mode returns matches from indexed ranges. In full and detail, the scan also checks original geometries in uncovered ranges. A covered range with zero matches needs no additional scan.

Full-text global indexes support character-string columns and serve the full_text_search function. The build splits the table's row IDs into global-index.row-count-per-shard shards and writes one Java-compatible full-text index file per shard, so Java readers can use the files as well. Every full-text.* option is passed to the native index writer with the prefix removed, as in Java; examples are full-text.tokenizer, full-text.lower-case, full-text.stem, full-text.language, and full-text.remove-stop-words. Invalid options are rejected before any data is read. NULL values are counted in a shard's row count but are not indexed. A later call indexes only rows that no full-text index on the column covers yet.

Full-text and vindex builds honor global-index.build.parallelism from table or build options. The default is 1; higher values bound the number of shards built concurrently. Each shard owns its writer and may also use native worker threads. Prepared commit messages retain shard plan order. On a build failure, all started shards finish before private outputs are cleaned. Files in messages already returned to a caller are retained, including after a failed commit.

Global-index builders require a non-primary-key table with row tracking enabled and unaware buckets (bucket=-1). Data evolution and query-side global-index optimization may be disabled. Full-text builds additionally reject tables with deletion vectors enabled:

CREATE TABLE paimon.my_db.items (
  id INT,
  embedding ARRAY<FLOAT>
) WITH (
  'bucket' = '-1',
  'row-tracking.enabled' = 'true',
  'data-evolution.enabled' = 'true',
  'global-index.enabled' = 'true',
  'global-index.row-count-per-shard' = '100000'
);

For vector indexes backed by vindex, set index_type to ivf-flat, ivf-pq, ivf-sq, ivf-rq, or diskann:

CALL sys.create_global_index(
  table => 'paimon.my_db.items',
  index_column => 'embedding',
  index_type => 'ivf-flat',
  options => 'ivf-flat.dimension=4,ivf-flat.nlist=256,ivf-flat.distance.metric=inner_product'
);

Examples for the additional vindex index types:

-- IVF-SQ uses a fixed 8-bit scalar code.
CALL sys.create_global_index(
  table => 'paimon.my_db.items',
  index_column => 'embedding',
  index_type => 'ivf-sq',
  options => 'ivf-sq.dimension=4,ivf-sq.nlist=256,ivf-sq.distance.metric=cosine'
);

CALL sys.create_global_index(
  table => 'paimon.my_db.items',
  index_column => 'embedding',
  index_type => 'ivf-rq',
  options => 'ivf-rq.dimension=4,ivf-rq.nlist=256,ivf-rq.distance.metric=cosine,ivf-rq.rq.bits=4'
);

CALL sys.create_global_index(
  table => 'paimon.my_db.items',
  index_column => 'embedding',
  index_type => 'diskann',
  options => 'diskann.dimension=4,diskann.distance.metric=cosine,diskann.deployment-profile=local_storage,diskann.build-preset=balanced'
);

For vector search, we recommend vindex DiskANN. Select index_type => 'diskann' and configure it with the diskann.* options below.

The options argument is a comma-separated key=value string. User options override table options. Use keys prefixed by the selected index type, or set field-level table options with fields.<column>.<option>. For example, diskann.max-degree becomes fields.embedding.max-degree for an embedding column:

CREATE TABLE paimon.my_db.image_items (
  id INT,
  embedding ARRAY<FLOAT>
) WITH (
  'bucket' = '-1',
  'row-tracking.enabled' = 'true',
  'data-evolution.enabled' = 'true',
  'global-index.enabled' = 'true',
  'fields.embedding.dimension' = '768',
  'fields.embedding.distance.metric' = 'cosine',
  'fields.embedding.nlist' = '1024'
);

Supported vindex options:

Option Default Applies To Description
<index-type>.dimension 128 all vindex types Vector dimension for ARRAY<FLOAT> columns. VECTOR<FLOAT,N> columns use N from the type.
<index-type>.distance.metric inner_product all vindex types Distance metric: inner_product, cosine, or l2.
<index-type>.nlist automatic all IVF types Number of IVF lists. When omitted or auto, infer it from expected-vector-count. DiskANN rejects this option.
<index-type>.expected-vector-count unset, or inferred all vindex types Expected number of vectors used for automatic sizing. When omitted, an automatic IVF build counts the shard's non-NULL vectors before training.
<index-type>.target-recall unset all vindex types Optional tuning objective in [0, 1]; IVF-PQ enables automatic OPQ at values of at least 0.9.
<index-type>.max-bytes-per-vector unset all vindex types Optional persisted-size tuning objective; PQ and RQ can use it to infer encoding width.
<index-type>.ivf.coarse-assignment auto all IVF types Build-time list assignment mode: auto uses Vamana for large centroid matrices; exact always uses exact assignment.
<index-type>.ivf.train.max-points-per-centroid 256 all IVF types Positive limit on coarse K-means training data: at most nlist × value vectors.
<index-type>.train.sample-ratio or fields.<field>.train.sample-ratio 1.0 all vindex types Fraction of non-NULL vectors selected for training. Must be in (0, 1]; all non-NULL vectors are still added to the index. The field-specific option takes precedence.
vindex.build.granule.enabled true all IVF types Use the granule build pipeline when nlist or expected-vector-count is explicit. NULL vectors switch to full spill. Set to false to always use full spill.
<index-type>.pq.m automatic ivf-pq Number of product-quantization sub-vectors, inferred from dimension and the code budget when omitted. The dimension must be divisible by an explicit value.
<index-type>.pq.code-ratio 0.0625 ivf-pq Ratio of PQ-code bytes to raw f32 vector bytes, in (0, 0.25]. An explicit pq.m overrides it.
<index-type>.pq.use-opq inferred ivf-pq Whether to enable OPQ before PQ encoding. When omitted or auto, enable it if target-recall is at least 0.9; otherwise disable it.
ivf-pq.ivf.pq-encoding auto ivf-pq Build-time PQ encoding mode: auto selects an accelerated backend when supported; canonical uses the canonical encoder.
<index-type>.pq.train.max-points-per-centroid 256 ivf-pq, diskann Positive limit on PQ training data: at most 2^pq.bits × value vectors per subquantizer.
ivf-rq.rq.bits 4, or inferred ivf-rq Residual-quantization width in the range 1 to 8. When omitted, ivf-rq.max-bytes-per-vector can select it.
ivf-rq.max-bytes-per-vector unset ivf-rq Optional positive persisted-code budget used to infer rq.bits.

IVF-SQ has no sq.bits option; it always stores one 8-bit scalar code per dimension. DiskANN accepts these build options:

Option Default Description
diskann.deployment-profile auto Intended serving medium: auto, memory, local_storage, remote_storage, or object_store.
diskann.target-recall unset Value in [0, 1] used to choose a build preset when one is not specified. This is a tuning hint, not a recall guarantee.
diskann.max-bytes-per-vector unset Optional positive persisted-size budget that guides PQ width and raw-vector encoding.
diskann.pq.code-ratio 0.0625 Ratio of resident PQ-code bytes to raw f32 vector bytes; must be in (0, 0.25] for 8-bit PQ or (0, 0.125] for 4-bit PQ.
diskann.pq.m automatic Explicit PQ chunk count in 1..=dimension; overrides pq.code-ratio.
diskann.pq.bits 8 PQ width; must be 4 or 8.
diskann.build-preset inferred fast_build, balanced, or high_recall; without target-recall or an explicit value, uses balanced.
diskann.seed 42 Reproducible graph-build seed.
diskann.memory-budget-bytes 8589934592 Positive internal build-state budget. This is not the query Reader budget or a process RSS limit.
diskann.max-degree preset value Maximum graph out-degree in 1..=1023.
diskann.build-search-list-size preset value Candidate-list width used while building the graph; must be at least max-degree.
diskann.alpha preset value Finite robust-pruning threshold at least 1.
diskann.storage-layout preset value auto, compact, or interleaved.
diskann.raw-vector-encoding preset value auto, f32, or f16 rerank-vector encoding.
diskann.build-distance preset value auto, full_precision, or product_quantized.

For procedure calls, prefer the index-prefixed option names shown above. Native vindex aliases are also accepted in the options string: dimension, metric, nlist, pq.m, use-opq, rq.bits, max-bytes-per-vector, deployment-profile, target-recall, pq.code-ratio, pq.bits, ivf.coarse-assignment, ivf.pq-encoding, ivf.train.max-points-per-centroid, pq.train.max-points-per-centroid, and the diskann.* build keys listed in the table. Build options for another index family are rejected rather than ignored.

NULL vector rows are skipped, while index manifest row_count and the global row range still include them. Non-NULL vectors retain their original relative row IDs. An all-NULL shard produces no index file. Full-spill training samples the compacted non-NULL vector stream at evenly spaced positions, matching Java.

Inspect committed index files with the $table_indexes system table:

SELECT index_type, index_field_name, row_count, row_range_start, row_range_end
FROM paimon.my_db.items$table_indexes;

drop_global_index

Drop a committed global index:

CALL sys.drop_global_index(
  table => 'paimon.my_db.my_table',
  index_column => 'id',
  index_type => 'btree'
);

index_type accepts every type the create procedures build: btree, bitmap, multivalue, fm, rtree, full-text, and the vindex types ivf-flat, ivf-pq, ivf-sq, ivf-rq, and diskann. It defaults to btree, is case-insensitive and surrounding whitespace is ignored.

To drop a composite BTree definition, pass the same ordered index_column list used to build it, such as 'category,item_number'. Scalar indexes and definitions with a different column order remain available.

Set dry_run => true to return the number of matching index files without committing changes to the table:

CALL sys.drop_global_index(
  table => 'paimon.my_db.my_table',
  index_column => 'id',
  index_type => 'btree',
  dry_run => true
);

The result column contains Would drop N global index file(s), including when N is zero. dry_run defaults to false; an explicit false or an omitted argument performs the drop and returns OK. Other values are rejected. The preview uses the latest snapshot at the time of the call; concurrent changes can affect the number of files matched by a later drop. Reads retain the usual local-cache behavior. The optional partitions argument uses the same filter syntax as create_global_index and restricts the drop to matching partitions.

compact

Compact an append, primary-key or data-evolution table:

CALL sys.compact(table => 'paimon.my_db.my_table');

Set partitions to compact only matching partitions. Alternatives are separated by ; and the constraints of each alternative by ,; omitted partition keys are unrestricted. It is rejected on unpartitioned tables.

CALL sys.compact(
  table => 'paimon.my_db.my_table',
  partitions => 'dt=2026-10-10,hr=1;dt=2026-10-11,hr=2'
);

The result is one row with tasks, files_removed, files_added and rows_written. files_removed counts files replaced in the new snapshot; they are kept for time travel until snapshot expiration. rows_written counts physical rows in the new files, which may include deleted rows retained by the table format. When no compaction task is planned, nothing is committed and every column is 0. All tasks are committed in one snapshot; if a task fails, files written by earlier tasks are deleted on a best-effort basis and a warning names any file left behind. Compaction memory is charged to the session memory pool.

Primary-key tables follow the incremental compaction strategy of Paimon core, so existing higher-level files may be kept instead of rewritten.

On primary-key tables, compaction with the lookup changelog producer, the first-row merge engine or deletion vectors requires local-cache.enabled = true on the catalog.

grant_permission

Grant an access to a principal on a REST catalog. Only the REST catalog supports permission and policy management:

CALL sys.grant_permission(
  resource_type => 'TABLE',
  database => 'sales',
  table => 'orders',
  access => 'SELECT',
  principal => 'user:alice'
);

resource_type is one of CATALOG, CATALOG_ALL, DATABASE, DATABASE_ALL, TABLE, COLUMN, VIEW or FUNCTION, and decides which locators are required: DATABASE and DATABASE_ALL need database, while TABLE, COLUMN, VIEW and FUNCTION need database plus the matching table, view or function. expire_time is an ISO-8601 UTC instant ending in Z.

A COLUMN grant narrows an access to part of a table and takes exactly one of column_names or excluded_column_names. Both are comma-separated strings here, where Java's Spark procedure declares ARRAY<STRING>, because a CALL argument in this engine is always a scalar:

CALL sys.grant_permission(
  resource_type => 'COLUMN',
  database => 'sales',
  table => 'orders',
  access => 'SELECT',
  principal => 'role:analyst',
  column_names => 'id, region'
);

revoke_permission

Remove an access from a principal. Revoking one that was never granted succeeds:

CALL sys.revoke_permission(
  resource_type => 'TABLE',
  database => 'sales',
  table => 'orders',
  access => 'SELECT',
  principal => 'user:alice'
);

list_permissions

List the assignments on an exact resource, optionally filtered by principal and access, and paged with max_results and page_token. Only grants made on that exact resource are returned, so a DATABASE_ALL or COLUMN grant that also covers the table does not appear:

CALL sys.list_permissions(
  resource_type => 'TABLE',
  database => 'sales',
  table => 'orders'
);

Every row carries next_page_token, so an empty page returns no rows and no token. The column_names and excluded_column_names columns are comma-joined.

create_policy

Attach a row filter or a column mask to a table for one principal. A policy carries exactly one of the two, so ROW_FILTER takes predicate_json and rejects on_column and transform_json, while COLUMN_MASKING takes on_column and transform_json and rejects predicate_json:

CALL sys.create_policy(
  database => 'sales',
  table => 'orders',
  policy_type => 'ROW_FILTER',
  principal => 'role:analyst',
  predicate_json => '{"kind":"LEAF","transform":{"name":"FIELD_REF","fieldRef":{"index":1,"name":"region","type":"STRING"}},"function":"EQUAL","literals":["APAC"]}'
);

predicate_json and transform_json are serialized Paimon Predicate and Transform values, not SQL. They are the same JSON the catalog returns for a table's query authorization.

drop_policy

Remove a policy by its identity, which is the table, the principal, the type, and for a column mask the column. if_exists => 'true' swallows a missing policy, but not a missing table:

CALL sys.drop_policy(
  database => 'sales',
  table => 'orders',
  policy_type => 'COLUMN_MASKING',
  principal => 'role:analyst',
  column => 'email',
  if_exists => 'true'
);

list_policies

List the policies on a table, optionally filtered by policy_type and principal, and paged like list_permissions. A column filter is only meaningful for a column mask, so it requires policy_type => 'COLUMN_MASKING':

CALL sys.list_policies(database => 'sales', table => 'orders');

Constant procedure arguments

All CALL procedures accept named name => expression syntax. In addition to literals, SQLContext accepts immutable scalar constant expressions such as casts, arithmetic, string functions, CASE and boolean predicates.

CALL sys.create_tag(
    table => concat('my_db.', 'events'),
    tag => concat('release_', '1'),
    snapshot_id => CAST(1 + 0 AS BIGINT)
);

Expressions must reduce to non-NULL strings, integers or booleans. Column references, subqueries, aggregate/window functions and non-immutable functions are rejected. Argument names, duplicates and procedure-specific types are validated as before. All arguments are evaluated and validated before the procedure performs any metadata changes; evaluation errors are returned.

Queries

Basic Queries

All DataFusion query capabilities are supported (JOINs, aggregations, subqueries, CTEs, etc.):

SELECT id, name FROM paimon.my_db.users WHERE id > 10 ORDER BY id LIMIT 100;

Variant Queries

Use variant_get to extract fields from VARIANT columns. Provide a target type string when the query needs a scalar result:

SELECT
    user_id,
    variant_get(payload, '$.event', 'string') AS event_name,
    variant_get(payload, '$.device.os', 'string') AS device_os,
    variant_get(attributes, '$.vip', 'boolean') AS is_vip
FROM paimon.my_db.user_events
WHERE variant_get(payload, '$.event', 'string') = 'login';

Use try_variant_get when incompatible values should return NULL:

SELECT
    user_id,
    try_variant_get(payload, '$.score', 'double') AS score
FROM paimon.my_db.user_events;

Spark-compatible functions

SQLContext::new() and its builder enable the matching datafusion-spark function packages and Spark expression planner by default. This includes numeric, string, date/time, hash, array/map and aggregate functions such as pmod, factorial, soundex, last_day, unix_date, crc32, sha1, collect_set and try_sum. EXTRACT and SQL SUBSTRING syntax use the Spark expression planner. See the upstream Spark function package for the versioned function inventory and signatures.

Spark functions take precedence over DataFusion defaults with the same name. Functions registered by the application after constructing SQLContext take priority. Registered functions resolve locally without REST function lookups; qualified catalog calls retain catalog resolution.

The upstream package's behavior applies, including these migration differences:

  • concat propagates NULL inputs.
  • mod and pmod return NULL for a zero divisor by default. Set datafusion.execution.enable_ansi_mode = true to report division by zero. Their numeric coercion and decimal result types follow the Spark package.
  • size returns INT and uses the package's legacy NULL-to-minus-one behavior.
  • Two-argument date_diff/datediff return INT. The three-argument Paimon date_diff(unit, start, end) extension still returns BIGINT.
  • hour takes timestamps; cast dates or strings to TIMESTAMP explicitly. hex numeric inputs use upstream BIGINT coercion.
  • The upstream 54 AVG implementation returns DOUBLE. DISTINCT plans that DataFusion can lower to a deduplication step are supported; remaining AVG and TRY_SUM DISTINCT plans report an unsupported error. COLLECT_LIST ORDER BY is unsupported. COLLECT_LIST DISTINCT delegates to upstream COLLECT_SET. Collection aggregates return empty arrays for empty growing window frames. Window frames requiring retraction follow the upstream version's limits, including unsupported AVG suffix frames.

SQLContext normalizes dictionary-encoded inputs for the migrated scalar functions, types bare NULL arguments, and bridges ordinary SQL integer arguments to the INT signatures of date arithmetic and factorial. Values outside that integer range fail during conversion. These adapters delegate computation to upstream functions. Paimon Variant handling, metadata SQL, selection aggregates and the additional functions documented below remain available. This enables the upstream function subset; it does not enable every Spark SQL syntax, cast mode or configuration option.

SQLContext also adapts binary inputs for length and substring functions in the pinned upstream version. octet_length and bit_length accept strings and binary values and return Spark INT results, including for Arrow large-offset representations. String byte lengths count UTF-8 bytes; length continues to count characters for strings. Fixed-size and dictionary binary inputs work with length functions and both function-call and SQL SUBSTRING syntax without decoding bytes as text.

Split-index extension

split_index(text, delimiter, index) provides Flink's zero-based splitting contract without changing Spark split or one-based split_part. The delimiter is literal and empty pieces are preserved. NULL arguments, an empty input, negative indexes and indexes outside the result return NULL. An empty delimiter uses Java whitespace as the separator.

Aggregate compatibility

SQLContext accepts the following aggregates in queries and view definitions. Session registrations take priority, and stored REST definitions retain their original SQL. REST SQL function bodies must still be scalar expressions. Bare compatibility names follow built-in precedence; use a qualified catalog function name to call a persistent function with the same name.

Function Contract
count_if(condition) Counts TRUE; FALSE and NULL do not contribute; an empty group returns 0
any(condition) Boolean OR; an empty or all-NULL group returns NULL
first(value[, ignoreNulls]), any_value(value[, ignoreNulls]) Native first-value aggregation; the optional NULL flag must be a boolean literal; without ordering the chosen value is not deterministic
approx_count_distinct(value[, relativeSD]) BIGINT estimate with native fixed-precision HLL; optional relativeSD must be a numeric literal between 0.008125 and 0.39; smaller errors are rejected
max_by(value, key), min_by(value, key) Select by greatest/least non-NULL key; a selected NULL value remains NULL; empty/all-NULL-key groups return NULL
arg_max(value, key) Like max_by but ignores rows where value or key is NULL
collect_list(value) Collects non-NULL values with duplicates; empty/all-NULL groups return an empty array
listagg(value[, delimiter]) WITHIN GROUP (ORDER BY ...) Native ordered string aggregation; NULL values are skipped; the default delimiter is empty

Native FILTER and supported ordering/DISTINCT clauses are retained. The selection aggregates reject DISTINCT, OVER, additional ordering and NULL modifiers. Tied keys may choose any eligible value. LISTAGG overflow and top-k selection forms are unsupported. Approximate counts use native HLL estimates rather than Spark's serialized HLL++ state. COLLECT_LIST always ignores NULLs and rejects explicit IGNORE/RESPECT NULLS clauses. Aggregate values and selection keys are evaluated once, including volatile expressions.

JSON output and Variant string casts

to_json(value) produces compact JSON from structs, lists, maps, Variant and scalar values. SQL NULL returns SQL NULL; nested NULLs become JSON null. Binary values use padded Base64. Decimals retain their digits; non-finite floats are rejected. Temporal values use Arrow string formatting. Map keys must be non-NULL. Spark JSON formatting options are not supported.

json_object(key, value, ...) constructs an object. Keys are converted to strings, NULL keys are rejected, NULL values are retained, and the last repeated key wins. json_object() returns {}.

CAST(variant_value AS STRING) unwraps Variant strings, formats scalar values and serializes arrays/objects as JSON. SQL NULL and Variant JSON null become SQL NULL. TRY_CAST returns NULL for malformed Variant values. Only the exact Paimon Variant representation uses this conversion; ordinary casts retain native behavior.

Additional scalar compatibility

Function Contract
assert_true(condition[, message]) Returns NULL for TRUE; FALSE or NULL raises an execution error with the optional message
conv(text, from_base, to_base) Source base 2 through 36 conversion of a 64-bit value; a negative destination base formats a signed result; invalid bases, including negative source bases, return NULL
date_diff(end, start) Upstream Spark INT calendar days after DATE coercion
date_diff(unit, start, end) Signed whole-unit difference at microsecond precision, from microsecond through week and calendar month/quarter/year; calendar fields use UTC and month-end clamping
convert_tz(datetime, from_zone, to_zone) Interprets a timezone-less datetime in the source zone, returning a timezone-less microsecond timestamp in the destination zone

CONV reads an optional sign and valid digit prefix, uses uppercase output, and saturates overflowing unsigned input at 2^64-1 before applying the sign. No valid digits means zero. NULL inputs propagate except that assert_true(NULL) fails. CONVERT_TZ accepts IANA zones and fixed offsets; invalid inputs, ambiguous local times and nonexistent local times return NULL.

Custom LIKE escape characters

LIKE, NOT LIKE and ILIKE accept a one-character ESCAPE literal, including Unicode:

SELECT value LIKE 'item!_%' ESCAPE '!' FROM events;

The escape quotes the next pattern character. % matches any number of Unicode characters and _ matches one, including newlines. A trailing escape is an error; NULL values/patterns return NULL. The backslash path remains native. LIKE ANY with a custom escape is unsupported.

Compatibility Functions

SQLContext registers the following functions without requiring a catalog:

Function Behavior
mod(value, divisor) Upstream Spark numeric remainder, with NULL on zero by default and errors in ANSI mode
regexp_extract(text, pattern[, index]) The first matched group; default index 1; index 0 selects the complete match
regexp_extract_all(text, pattern[, index]) A list of matched groups, in occurrence order
date_sub(date, days) Subtracts integral calendar days and returns DATE; negative days add days
sha2(text_or_binary, bits) Lowercase SHA-2 hex with upstream INT coercion for bits; 224/256/384/512 are supported, 0 means 256, and other INT values return NULL
to_base64(text_or_binary) Alias of upstream Spark base64, with standard = padding; strings use UTF-8
curdate() Alias of current_date()
timestamp_millis(value) Alias of to_timestamp_millis(value), preserving millisecond epoch units
to_timestamp_ltz(value[, format...]) Alias of to_timestamp, preserving its session time zone and nanosecond result
approx_percentile(value, percentage[, accuracy]) Spark-compatible discrete approximate percentile; percentage is a scalar or array; alias percentile_approx

Regular expressions use Rust regex syntax, like DataFusion's existing regex functions, rather than the full Java regex dialect. No match or an unmatched optional group produces an empty string; no matches for regexp_extract_all produce an empty list. Null arguments produce null. Invalid patterns, negative or out-of-range group indices, and date arithmetic overflow produce errors. SHA-2 and Base64 preserve raw bytes across Arrow binary representations, including dictionary encodings and null dictionary values.

The timestamp aliases retain DataFusion semantics: optional formats are Chrono strftime strings, not Spark/Java patterns; numeric to_timestamp_ltz inputs are epoch seconds. The Flink (number, precision) overload is not implemented. The existing date_format function also retains DataFusion's Chrono format strings; Java-style Spark/Flink datetime patterns are not translated.

approx_percentile and percentile_approx follow Spark's Greenwald–Khanna summary algorithm and select observed values rather than interpolate between them. accuracy is a constant integer in [1, 2147483647], defaulting to 10000; the relative rank error is 1 / accuracy. Higher accuracy retains a larger summary. The percentage must be a constant number or array of numbers between 0 and 1, inclusive. Constant expressions such as 1.0 / 2 are accepted. NULL percentages, NULL array elements, and invalid accuracy values are errors. SQL literal parsing and implicit casts retain DataFusion semantics.

Scalar percentages produce the input column's type. Percentage arrays produce an array of that type, retaining the order and duplicates in the percentage array. NULL inputs are ignored. Empty/all-NULL groups and an empty percentage array produce NULL. Numeric, date, timestamp, and ANSI year-month or day-time interval inputs are supported; a mixture of the two interval families is rejected. GROUP BY, FILTER, DISTINCT, and window aggregation use the same contract. DISTINCT aggregation additionally retains the distinct input values for merging. Moving window frames retain their current rows and rebuild the summary when rows leave; ordinary aggregation and growing frames retain only the compact summary. Timestamp observations use Spark's microsecond precision, flooring Arrow timestamps to whole microseconds. Decimal results use Spark's decimal rounding and become NULL on precision overflow. Decimal result arrays expose nullable Arrow elements so those overflow positions can be represented safely; Spark's declared non-null element metadata can otherwise fail during result decoding. DataFusion's approx_percentile_cont remains available with its native t-digest and continuous interpolation semantics.

SELECT approx_percentile(latency_ms, 0.95, 10000) FROM events;
SELECT approx_percentile(latency_ms, array(0.5, 0.9, 0.99), 10000) FROM events;
-- Spark example: [1, 1, 0], preserving the order of requested percentages.
SELECT approx_percentile(v, array(0.5, 0.4, 0.1), 100)
FROM (VALUES (0), (1), (2), (10)) AS t(v);

Maximum partition

max_pt('table'), sys.max_pt('database.table'), and catalog.sys.max_pt('catalog.database.table') return the maximum non-null first-level partition value as a string, matching Paimon's max_pt contract. Comparison uses the partition column's logical type: integer 10 is greater than 9, while string '9' is greater than '10'. Subpartition columns do not affect the result. Only partitions containing live data files qualify; empty catalog partitions and dropped partitions are skipped. Unpartitioned tables and tables without any qualifying partition produce an error.

The argument must be one non-null string literal. Table names can have one, two, or three components, with SQL quoting for individual identifiers. Omitted table components use the session's current catalog/database even when the function is catalog-qualified. An explicit catalog in the table argument takes precedence. Paimon branch references are supported; system tables are rejected. Resolution reads scan metadata during planning and substitutes a literal so partition filters can be pushed down. Repeated calls are resolved once per statement, and later statements read fresh metadata. Temporary SQL views that depend on max_pt, including through other views, retain their SQL and resolve it again for each query. They retain the creation namespace and output schema, while reading current scan options and grants. The metadata lookup follows the same session scan options, including historical snapshots, as a normal table read. Temporary shadows are resolved normally and must still be Paimon data tables; a view cannot be passed as the source table. Because the result comes from raw partition metadata, grants with row filters or column masks are rejected; unrestricted grants are supported. Catalog SQL functions are required to be immutable and cannot contain max_pt, which reads table metadata that can change between statements.

SELECT * FROM events WHERE dt = max_pt('events');
SELECT * FROM daily_counts WHERE day_id = CAST(sys.max_pt('default.daily_counts') AS INT);
SELECT approx_percentile(latency_ms, 0.95) FROM events;

Additional scalar compatibility functions

Function Contract
split(text, regex[, limit]) Returns a string array using Rust regex syntax. A positive limit caps the number of parts and retains the remaining suffix in the last part; a nonpositive limit is unlimited. An empty pattern splits Unicode characters.
locate(needle, text[, start]) Returns a one-based Unicode-character position, or zero if no match exists or start is nonpositive/out of range. Start defaults to one.
date(value) Alias of native to_date, retaining its accepted inputs and conversion errors.
typeof(value) Returns SQL-style names for common scalar, array, map, struct and Variant types, including typed NULLs. Other Arrow types retain their Arrow type labels.
hex(value) Uppercase hexadecimal UTF-8/string or raw binary bytes; integer inputs use hexadecimal digits, with negative signed values represented as 64-bit two's complement.
unhex(text) Decodes case-insensitive hex. An odd number of digits is left-padded with zero; invalid hex returns NULL.
size(collection) Counts array elements or map entries, including NULL values, and returns INT.
hour(timestamp) Upstream Spark INT hour extraction; timestamp timezone metadata is honored.

Except for typeof and size, NULL inputs produce NULL. SIZE returns -1 for NULL collections. Empty collections have size zero. These functions are registered locally and do not require REST function metadata. Split patterns follow the same Rust regex restrictions as the other regex compatibility functions. SQLContext's date alias follows native DataFusion behavior when session configuration changes.

Column Projection

Only the required columns are read, reducing I/O:

SELECT name FROM paimon.my_db.users;

Lateral collection expansion

SQLContext supports LATERAL VIEW explode, posexplode, explode_outer, and posexplode_outer for arrays and maps. The LATERAL VIEW OUTER spelling also preserves empty/NULL collections.

SELECT t.id, expanded.pos, expanded.value
FROM events t
LATERAL VIEW posexplode_outer(t.items) expanded AS pos, value;

Positions are zero-based INT values. Array defaults are col, or pos, col; map defaults are key, value, or pos, key, value. An explicit AS list must have the matching number of column names. A NULL array element remains an element with its position. Outer expansion of an empty or NULL collection emits one row with NULL positions and values; ordinary expansion emits no rows. Empty parent inputs emit no rows in either mode.

Multiple lateral views can reference columns produced by earlier views. Qualified/quoted aliases, joins, filters, subqueries and set operations retain their normal SQL behavior. Use one FROM relation with explicit joins instead of comma-separated FROM items. Unsupported generators and modifiers report planning errors.

Filter Pushdown

The following filter predicates are pushed down to the Paimon storage layer:

  • Comparison: =, !=, <, <=, >, >=
  • Logical: AND, OR
  • Null checks: IS NULL, IS NOT NULL
  • Range: IN, NOT IN, BETWEEN
  • String predicates: positive LIKE, including no-wildcard, prefix, suffix, contains, and more complex patterns. NOT LIKE and ILIKE are evaluated by DataFusion as residual filters.

Filters on partition columns enable exact partition pruning, avoiding scans of irrelevant data.

COUNT(*) Pushdown

When the following conditions are met, COUNT(*) retrieves exact row counts directly from split metadata without a full table scan:

  • All splits have a known merged_row_count
  • No LIMIT clause
  • Filter predicates only involve partition columns (Exact level)

Query Authorization

Row filters cannot reference a resolving BLOB view: the stored reference's nullness can differ from its upstream value. Other BLOB payloads and output-only BLOB views are resolved after row authorization.

When a table in a REST catalog has 'query-auth.enabled' = 'true', planning a query asks the catalog what the current user may read, and the row filters and column masks it returns (see create_policy) are applied to the result:

  • Rows are filtered on their stored values; masked columns return masked values.
  • BLOB masks keep payload reads lazy. A NULL mask does not read the original BLOB payload.
  • Query filters on masked columns, including partition keys, match masked values.
  • Statistics are not used under a row filter or a mask, and a row filter turns off LIMIT pushdown, so such queries read the data.
  • Time travel, branches, system tables, incremental and audit-log reads, and search are refused on such tables.

Regular expression operators

SQLContext supports value RLIKE pattern, value REGEXP pattern, and their NOT forms in queries, expressions, views and SQL functions. They use DataFusion's native case-sensitive regex matching: a match may occur anywhere unless the pattern contains anchors. Inline flags such as (?i) can change case sensitivity.

SELECT phone RLIKE '^1[3-9][0-9]{9}$' FROM users;
SELECT name NOT REGEXP '(?i)^test';

Patterns use Rust regex syntax, including its restrictions on look-around and backreferences. A NULL value or pattern produces SQL NULL; an invalid pattern reports an error. Operator text inside strings, comments and quoted identifiers is not interpreted as SQL syntax. Persistent Paimon view and function definitions retain their portable SQL text.

Python Multimodal Helper Functions

When you use pypaimon_rust.datafusion.SQLContext, the Python binding registers a small set of scalar helper functions for BLOB-backed media and vector workflows. These helpers are Python-binding built-ins; they are not registered by the Rust paimon_datafusion::SQLContext.

Media helpers require the optional Python media dependencies:

pip install "pypaimon-rust[video]"
Function Return Type Description
media_info(blob) STRING JSON metadata for image, video, or audio input
media_thumbnail(blob) BINARY PNG thumbnail, using a default 320x320 bounding box
media_thumbnail(blob, max_width, max_height) BINARY PNG thumbnail constrained to the given dimensions
video_snapshot(blob) BINARY PNG frame near timestamp 0ms
video_snapshot(blob, timestamp_ms) BINARY PNG frame near the given timestamp
video_frame(blob, frame_index) BINARY PNG frame by zero-based decoded frame index
vector_from_json(json) List<Float32> Converts a JSON float array string into an Arrow float vector
vector_to_json(vector) STRING Converts an Arrow float vector back to a JSON array string

Invalid, NULL, unsupported, or undecodable media inputs return SQL NULL. Media functions read either inline bytes or BLOB descriptor bytes when the SQLContext has a registered Paimon catalog that can resolve the descriptor.

Example:

SELECT
    id,
    media_info(content) AS info_json,
    media_thumbnail(content, 160, 90) AS preview_png,
    video_frame(content, 10) AS frame_png
FROM paimon.my_db.assets;

Use vector_from_json to bridge JSON-encoded embeddings into lateral vector search queries:

WITH queries AS (
    SELECT id, vector_from_json(embedding_json) AS embedding
    FROM paimon.my_db.query_embeddings
)
SELECT q.id AS query_id, r.id AS result_id
FROM queries q
CROSS JOIN LATERAL vector_search(
    'paimon.my_db.items',
    'embedding',
    q.embedding,
    10
) AS r;

Paimon supports approximate nearest neighbor (ANN) vector search through global vector indexes. DataFusion can search vindex indexes created by CALL sys.create_global_index. We recommend vindex DiskANN (index_type => 'diskann'). The vector_search table-valued function is registered as a UDTF on the DataFusion session context.

Registration

When you use a SQLContext, vector_search is registered automatically for every catalog you register — no extra setup is needed.

With a raw DataFusion SessionContext, register it explicitly:

use paimon_datafusion::register_vector_search;

register_vector_search(&ctx, catalog.clone(), "default");

Usage

SELECT * FROM vector_search('table_name', 'column_name', 'query_vector_json', limit)
Argument Type Description
table_name STRING Table name, fully qualified (catalog.db.table) or short form
column_name STRING The vector column to search
query_vector_json STRING Query vector as a JSON array of floats
limit INT Maximum number of results (top-k)

Example:

SELECT * FROM vector_search('paimon.my_db.items', 'embedding', '[1.0, 0.0, 0.0, 0.0]', 10);

The function performs ANN search across all matching vector index files for the target column, merges results, and returns the top-k rows ordered by relevance score. If no matching index is found, an empty result is returned.

Scalar Pre-Filters

Add a WHERE clause to restrict the rows returned by vector Top-K:

SELECT id, event_time
FROM vector_search(
    'paimon.my_db.items',
    'embedding',
    '[1.0, 0.0, 0.0, 0.0]',
    10
)
WHERE event_time >= TIMESTAMP '2026-08-01 00:00:00';

On data-evolution tables, exact scalar global-index answers are passed to each vector shard as a row-ID filter. When no scalar index can evaluate the predicate, each shard retrieves ANN candidates once and checks their scalar values against the same pinned snapshot before returning results. Batch queries share a candidate row-ID union for each filtering read.

vector-index.post-filter.candidates-factor defaults to 10. Each query's candidate count is max(original Top-K * factor, required candidate limit), capped by shard size and the backend limit (2147483647). The required limit includes reranking/deletion slack; neither that slack nor batch size multiplies the factor. For example, Top-K 10 requests 100 candidates with the default factor unless a larger required allowance applies.

Every returned row satisfies the predicate, but filtering may leave fewer than Top-K rows, including no rows even when matches exist outside the candidates. There is no retry or automatic raw scoring of indexed ranges to fill results. Increase the factor to check more candidates, at additional query and scalar read cost. Post-filtering retains ANN's approximate recall. Raw work outside vector-index coverage still follows fast, full, and detail search modes; explicitly supplied Raw sources retain their exact scoring. When a scalar index can evaluate the predicate but covers only part of the row range, scalar full and detail modes still plan Raw work for its coverage gaps.

Only predicates that can be translated completely to Paimon predicates are pushed into vector search. DataFusion keeps its residual filter for ordinary vector_search queries as an additional correctness check.

Refine / Rerank

Vector index search can optionally refine ANN results by reading the raw vectors for a larger candidate set, recomputing exact scores, and reranking the final top-k results. This is useful for quantized indexes, such as IVF-PQ, where ANN scores are approximate.

Refine is disabled by default. Configure a positive refine factor as a table property. The search first requests limit * refine_factor candidates from the index, then reranks those candidates by exact raw-vector scores and keeps the requested limit rows. A factor of 1 still performs exact reranking over the original limit candidates, but does not over-fetch additional candidates. Omit the option to keep the default ANN-only behavior.

Set the option when creating the table:

CREATE TABLE paimon.my_db.items (
  id INT,
  embedding ARRAY<FLOAT>
) WITH (
  'bucket' = '-1',
  'row-tracking.enabled' = 'true',
  'data-evolution.enabled' = 'true',
  'global-index.enabled' = 'true',
  'fields.embedding.ivf.refine-factor' = '3'
);

Or enable it on an existing table:

ALTER TABLE paimon.my_db.items SET TBLPROPERTIES(
  'fields.embedding.ivf.refine-factor' = '3'
);

Then run vector_search as usual:

SELECT *
FROM vector_search('paimon.my_db.items', 'embedding', '[1.0, 0.0, 0.0, 0.0]', 10);

Supported refine option names are refine_factor, refine-factor, rerank_factor, and rerank-factor. Option lookup checks field-specific and index-specific keys before global keys. For example, for an ivf-flat index on column embedding, these keys are accepted, from more specific to more general:

Example Key Scope
fields.embedding.ivf-flat.refine-factor This column and index type
fields.embedding.ivf_flat.refine-factor This column and normalized index type
fields.embedding.ivf.refine-factor This column and all IVF vector indexes
fields.embedding.refine-factor This column
ivf-flat.refine-factor This index type
ivf_flat.refine-factor This normalized index type
ivf.refine-factor All IVF vector indexes
refine-factor All vector searches on the table

Larger refine factors may improve recall and ordering quality, but they also increase index result merging, raw-vector reads, and exact scoring work. Use the smallest factor that provides the desired recall.

Lateral Joins

Use CROSS JOIN LATERAL when query vectors come from another relation. In this mode, the third vector_search argument is a column reference from the left side of the join instead of a JSON literal:

SELECT q.id AS query_id, r.id AS result_id
FROM paimon.my_db.queries q
CROSS JOIN LATERAL vector_search(
    'paimon.my_db.items',
    'embedding',
    q.embedding,
    10
) AS r
ORDER BY query_id, result_id;

The query-vector column must have Arrow type List<Float32> or FixedSizeList<Float32>. Null query-vector rows produce no joined results, and null elements inside a vector are rejected. The lateral form returns the left row joined with the top-k matching rows from the target Paimon table for that row's query vector.

Fully translatable target-table predicates are also applied before each lateral Top-K:

SELECT q.id AS query_id, r.id AS result_id
FROM paimon.my_db.queries q
CROSS JOIN LATERAL vector_search(
    'paimon.my_db.items',
    'embedding',
    q.embedding,
    10
) AS r
WHERE r.event_time >= TIMESTAMP '2026-08-01 00:00:00'
ORDER BY query_id, result_id;

For conjunctions, target-only predicates such as r.event_time >= ... are pushed into vector search. Predicates that reference the left relation or both sides remain normal join-result filters. Unsupported or inexact target predicates also remain post-Top-K residual filters, so they may return fewer than the requested number of rows.

Supported Metrics

The distance metric is configured at index creation time via table options:

Metric Description
inner_product Inner product (default)
cosine Cosine similarity
l2 Euclidean (L2) distance

Vindex Index Options

For vindex-backed search, build the index with CALL sys.create_global_index and an index type such as ivf-flat, ivf-sq, ivf-pq, ivf-rq, or diskann. See create_global_index for the table requirements and build option keys.

With Paimon's SQLContext, set query-time vindex options for the session before calling vector_search, then reset them when they are no longer needed:

-- IVF only; omit it to use the automatic search width.
SET 'paimon.ivf.nprobe' = '32';
SELECT * FROM vector_search('paimon.my_db.items', 'embedding', '[1.0, 0.0, 0.0, 0.0]', 10);
RESET 'paimon.ivf.nprobe';

-- DiskANN only; must be at least 1. Omit it to use the automatic search width.
SET 'paimon.diskann.l_search' = '100';
SELECT * FROM vector_search('paimon.my_db.items', 'embedding', '[1.0, 0.0, 0.0, 0.0]', 10);
RESET 'paimon.diskann.l_search';

Query options are index-family specific and non-applicable options are ignored: IVF readers consume only ivf.nprobe, while DiskANN readers consume only diskann.l_search. Setting both options is allowed; each index uses its own. When the applicable option is omitted, both index families select a search width automatically. IVF searches may progressively expand nprobe when the initial width underfills the requested top-k.

vindex.reader.memory-budget-bytes sets the per-Reader resident-data and cache budget (default 4 GiB). It is distinct from the DiskANN build option diskann.memory-budget-bytes and from process RSS:

SET 'paimon.vindex.reader.memory-budget-bytes' = '4294967296';
SELECT * FROM vector_search('paimon.my_db.items', 'embedding', '[1.0, 0.0, 0.0, 0.0]', 10);
RESET 'paimon.vindex.reader.memory-budget-bytes';

These SET/RESET values are provided by Paimon's SQLContext; registering vector_search directly on a raw DataFusion SessionContext does not install that dynamic-option path. Rust callers can pass the same unprefixed keys, such as diskann.l_search, through VectorSearchBuilder::with_options or BatchVectorSearchBuilder::with_options.

Paimon supports hybrid search by combining multiple search routes and ranking the merged results. The hybrid_search table-valued function is registered as a UDTF on the DataFusion session context.

Vector and full-text routes are built in and can be combined without enabling additional Cargo features.

Registration

When you use a SQLContext, hybrid_search is registered automatically for every catalog you register — no extra setup is needed.

With a raw DataFusion SessionContext, register it explicitly:

use paimon_datafusion::register_hybrid_search;

register_hybrid_search(&ctx, catalog.clone(), "default");

Usage

SELECT * FROM hybrid_search(
    'table_name',
    vector_routes,
    full_text_routes,
    limit,
    'ranker'
)
Argument Type Description
table_name STRING Table name, fully qualified (catalog.db.table) or short form
vector_routes ARRAY Vector route definitions; use array() when no vector route is needed
full_text_routes ARRAY Full-text route definitions; use array() for vector-only hybrid search
limit INT Maximum number of merged results (top-k)
ranker STRING Optional ranker: rrf (default), weighted_score, or mrr

Route definitions use Spark-compatible array(named_struct(...)) syntax. A vector route accepts field (or vector_column), query_vector, optional limit, optional weight, and optional options:

SELECT *
FROM hybrid_search(
    'paimon.my_db.items',
    array(
        named_struct(
            'field', 'title_embedding',
            'query_vector', array(1.0, 0.0, 0.0, 0.0),
            'limit', 20,
            'weight', 1.0
        ),
        named_struct(
            'field', 'body_embedding',
            'query_vector', array(0.9, 0.1, 0.0, 0.0),
            'limit', 20,
            'weight', 0.7
        )
    ),
    array(),
    10,
    'rrf'
);

A full-text route accepts column, query, optional limit, and optional weight:

SELECT *
FROM hybrid_search(
    'paimon.my_db.docs',
    array(
        named_struct(
            'field', 'embedding',
            'query_vector', array(1.0, 0.0, 0.0, 0.0),
            'limit', 20,
            'weight', 1.0
        )
    ),
    array(
        named_struct(
            'column', 'content',
            'query', 'paimon search',
            'limit', 20,
            'weight', 0.8
        )
    ),
    10,
    'weighted_score'
);

The function searches each route independently, merges route results with the selected ranker, and returns the top-k matching rows from the target table. Its output also includes a nullable FLOAT metadata column named __paimon_search_score, which contains the final score produced by the selected ranker:

SELECT id, __paimon_search_score
FROM hybrid_search(
    'paimon.my_db.docs',
    array(
        named_struct(
            'field', 'embedding',
            'query_vector', array(1.0, 0.0, 0.0, 0.0)
        )
    ),
    array(),
    10,
    'rrf'
)
ORDER BY __paimon_search_score DESC;

The metadata column is part of the DataFusion table function schema, so SELECT * includes it. Use an explicit ORDER BY __paimon_search_score DESC when result ranking order matters; scan output order is not an ordering guarantee.

Paimon supports full-text search via the Tantivy search engine. The full_text_search table-valued function is registered as a UDTF on the DataFusion session context.

Full-text search is built into both paimon and paimon-datafusion; no Cargo feature flag is required.

[dependencies]
paimon = "0.6.0"
paimon-datafusion = "0.6.0"

Registration

When you use a SQLContext, full_text_search is registered automatically for every catalog you register — no extra setup is needed.

With a raw DataFusion SessionContext, register it explicitly:

use paimon_datafusion::register_full_text_search;

register_full_text_search(&ctx, catalog.clone(), "default");

Usage

SELECT * FROM full_text_search('table_name', 'column_name', 'query_text', limit)
Argument Type Description
table_name STRING Table name, fully qualified (catalog.db.table) or short form
column_name STRING The text column to search
query_text STRING Search query (Tantivy query syntax)
limit INT Maximum number of results (top-k)

Example:

SELECT * FROM full_text_search('paimon.my_db.docs', 'content', 'paimon search', 10);

The function searches across all Tantivy full-text index files for the target column, merges results by relevance score, and returns the top-k matching rows. If no matching index is found, an empty result is returned.

Build the index files with CALL sys.create_global_index(..., index_type => 'full-text') (see create_global_index), or with Java Paimon; both write the same format.

Rust full-text builder

The core builder accepts the structured full-text query DSL used by Java and PyPaimon. Scalar and partition predicates are applied before the final Top-K.

```rust,ignore let predicate = PredicateBuilder::new(table.schema().fields()) .equal("category", Datum::String("news".into()))?; let mut builder = table.new_full_text_search_builder(); builder .with_query("content", r#"{"match":{"query":"paimon"}}"#) .with_filter(predicate) .with_limit(10); let plan = builder.new_scan()?.scan().await?; let result = builder.new_read()?.read(plan).await?;

`FullTextScanPlan` owns a pinned snapshot and can outlive its scan. Readers
for the same table, branch, column and filter can reuse the plan with different
queries or limits. Pinning preserves the query's bound schema, including
schema-only column renames. `execute()` performs both stages directly and returns
`GlobalIndexResult`, including scores and the captured read context.

For Data Evolution, full-text and scalar `fast`, `full`, and `detail` modes
follow Java's coverage rules. Inexact scalar candidates are read back only
when `global-index.filter.refine-from-data=true`. Composite scalar indexes
are excluded from search pre-filters, as in Java. Filtering preserves the
unfiltered corpus for BM25 scores; Boolean and boosted queries collect leaf
candidates before applying the final limit. Index shard searches use
`global-index.thread-num` workers.

Non-Data-Evolution tables also support row filters when they are append tables
without primary keys, use Parquet, enable row tracking, and use full-text `fast`
mode. Scalar `fast`, `full`, and `detail` retain their coverage rules, with data
reads restricted to the full-text index coverage. Candidate refinement keeps
the Rust default of `global-index.filter.refine-from-data=true`. Matching rows
are checked against the pinned snapshot's live data and deletion vectors before
full-text Top-K; filtering preserves the original index corpus and BM25 scores.
Full-text `full`/`detail` row filtering on these tables remains unsupported.

The existing `with_text_column()` / `with_query_text()` API continues to
accept plain text for the SQL table function. These pre-filters are configured
through the Rust core builder's `with_filter()`; the SQL table function does not
push an outer `WHERE` into full-text search. Primary-key materialized reads use
`execute_read()`.


## Time Travel

Paimon supports time travel queries to read historical data.

### By Snapshot ID

```sql
SELECT * FROM paimon.default.my_table VERSION AS OF 1;

By Tag Name

Use a quoted tag name with VERSION AS OF:

SELECT * FROM paimon.default.my_table VERSION AS OF 'my_tag';

Resolution order: first resolves a tag with that name, then tries watermark-<value> or a numeric snapshot ID.

For tables managed by a REST catalog, tags named by VERSION AS OF or scan.tag-name are resolved through the catalog; tag files in table storage are ignored. Legacy tags named . or .. cannot be resolved through the REST API. If the tag endpoint returns HTTP 501, scan.version (including VERSION AS OF) can still resolve a numeric snapshot ID or watermark-<value> from stored snapshots. scan.tag-name always requires the tag endpoint, including for numeric tag names. Other catalog errors fail the query.

By Timestamp

Read data as of a specific point in time. The format is YYYY-MM-DD HH:MM:SS:

SELECT * FROM paimon.default.my_table TIMESTAMP AS OF '2024-01-01 00:00:00';

This finds the latest snapshot whose commit time is less than or equal to the given timestamp. The timestamp is interpreted in the local timezone.

The session option scan.timestamp also accepts a local timestamp string, including fractional seconds (up to nine digits, truncated to milliseconds):

SET 'paimon.scan.timestamp' = '2024-01-01 00:00:00.123';
SELECT * FROM paimon.default.my_table;
RESET 'paimon.scan.timestamp';

It can be combined with scan.mode=from-timestamp, but cannot be combined with another time-travel selector such as scan.timestamp-millis or scan.snapshot-id. An invalid timestamp or a time before the earliest available snapshot fails the read.

By Watermark

Use VERSION AS OF 'watermark-<value>' syntax:

SELECT * FROM paimon.default.my_table
VERSION AS OF 'watermark-1704067200000';

This resolves the tag first if a tag with that exact name exists. Otherwise, the suffix is parsed as a watermark in milliseconds. The session-scoped dynamic option scan.watermark is also available:

SET 'paimon.scan.watermark' = '1704067200000';
SELECT * FROM paimon.default.my_table;
RESET 'paimon.scan.watermark';

This reads the earliest snapshot whose watermark is greater than or equal to the given value (snapshots without a watermark are skipped). It is mutually exclusive with the other time-travel selectors. If no matching snapshot exists, scan planning fails.

Dynamic Options (SET / RESET)

Use SET to configure session-scoped Paimon dynamic options that apply to subsequent table loads:

-- Set an option
SET 'paimon.scan.version' = '1';

-- Reset an option
RESET 'paimon.scan.version';

Quoted options prefixed with paimon. are handled as Paimon table options; all others are delegated to DataFusion. Dynamic table options are applied at table load time via table.copy_with_options().

Example — enable BLOB descriptor mode:

SET 'paimon.blob-as-descriptor' = 'true';
SELECT * FROM paimon.my_db.assets;
RESET 'paimon.blob-as-descriptor';

Example — preserve Blob View references instead of resolving upstream BLOB values on RESTCatalog-backed reads:

SET 'paimon.blob-view.resolve.enabled' = 'false';
SELECT * FROM paimon.my_db.asset_refs;
RESET 'paimon.blob-view.resolve.enabled';

Temporary Tables

You can register in-memory temporary tables under any catalog. Temporary tables exist only for the lifetime of the SQLContext instance and are automatically cleaned up when the context is dropped.

The table name accepts flexible references, similar to DataFusion: - "my_table" — uses the current catalog and current database - "database.my_table" — uses the current catalog with the specified database - "catalog.database.my_table" — fully qualified

register_temp_table

Register any Arc<dyn TableProvider> as a temporary table (including MemTable, ViewTable, custom providers, etc.):

use datafusion::arrow::array::Int32Array;
use datafusion::arrow::datatypes::{DataType as ArrowDataType, Field, Schema};
use datafusion::arrow::record_batch::RecordBatch;
use datafusion::datasource::MemTable;

let schema = Arc::new(Schema::new(vec![
    Field::new("id", ArrowDataType::Int32, false),
    Field::new("name", ArrowDataType::Utf8, true),
]));
let batch = RecordBatch::try_new(
    schema.clone(),
    vec![
        Arc::new(Int32Array::from(vec![1, 2, 3])),
        Arc::new(StringArray::from(vec!["alice", "bob", "carol"])),
    ],
)?;

// Register a MemTable as a temp table
let mem_table = Arc::new(MemTable::try_new(schema.clone(), vec![vec![batch.clone()]])?);
ctx.register_temp_table("paimon.my_db.users", mem_table)?;
let df = ctx.sql("SELECT * FROM paimon.my_db.users WHERE id > 1").await?;
df.show().await?;

// Register a ViewTable as a temp table
use datafusion::datasource::ViewTable;
let view_table = Arc::new(ViewTable::new(logical_plan, Some(query_sql)));
ctx.register_temp_table("paimon.my_db.my_view", view_table)?;

CREATE TEMPORARY TABLE

You can also create temporary tables directly from SQL. See the DDL section for details.

CREATE TEMPORARY TABLE paimon.my_db.source (id INT, name STRING) AS SELECT * FROM (VALUES (1, 'alice'), (2, 'bob')) AS t(id, name);

CREATE TEMPORARY VIEW

Create a temporary view directly from SQL. See the DDL section for details.

CREATE TEMPORARY VIEW paimon.my_db.active_users AS SELECT * FROM paimon.my_db.users WHERE id > 0;

Deregister

Use deregister_temp_table to remove a temporary table or view programmatically, or use the DROP TEMPORARY TABLE / DROP TEMPORARY VIEW SQL statements (see the DDL section):

ctx.deregister_temp_table("paimon.my_db.users")?;

Multiple temporary tables can share the same database — the database is created automatically on first use:

let mem_a = Arc::new(MemTable::try_new(schema_a, vec![vec![batch_a]])?);
let mem_b = Arc::new(MemTable::try_new(schema_b, vec![vec![batch_b]])?);
ctx.register_temp_table("my_db.table_a", mem_a)?;
ctx.register_temp_table("my_db.table_b", mem_b)?;

// Join two temp tables
let df = ctx.sql("SELECT * FROM paimon.my_db.table_a JOIN paimon.my_db.table_b ON a.id = b.id").await?;

System Tables

Access table metadata and audit rows via the $ syntax.

$audit_log

Read the current table state with each row's Paimon row kind (+I, -U, +U, or -D):

SELECT * FROM paimon.default.my_table$audit_log;

rowkind is the first column, followed by the table columns. Append-only rows are reported as +I; deduplicate primary-key reads retain the latest physical retract row instead of dropping it. Other primary-key merge engines retain or reject retracts according to their merge-engine options. As in Paimon Java, rows masked by deletion vectors are not reconstructed as retract records. When table-read.sequence-number.enabled=true, _SEQUENCE_NUMBER appears after rowkind.

$options

View all configuration options for a table:

SELECT key, value FROM paimon.default.my_table$options;

Returns two columns: key (STRING) and value (STRING).

$schemas

View the schema history of a table:

SELECT * FROM paimon.default.my_table$schemas;

Columns:

Column Type Description
schema_id BIGINT Schema ID
fields STRING Field definitions (JSON)
partition_keys STRING Partition keys (JSON)
primary_keys STRING Primary keys (JSON)
options STRING Table options (JSON)
comment STRING Comment
update_time TIMESTAMP Update time

$snapshots

View the snapshot history of a table:

SELECT * FROM paimon.default.my_table$snapshots;

Columns:

Column Type Description
snapshot_id BIGINT Snapshot ID
schema_id BIGINT Schema ID
commit_user STRING Commit user
commit_identifier BIGINT Commit identifier
commit_kind STRING APPEND / COMPACT / OVERWRITE / ANALYZE
commit_time TIMESTAMP Commit time
base_manifest_list STRING Base manifest list file
delta_manifest_list STRING Delta manifest list file
changelog_manifest_list STRING Changelog manifest list file
total_record_count BIGINT Total record count
delta_record_count BIGINT Delta record count
changelog_record_count BIGINT Changelog record count
watermark BIGINT Watermark
next_row_id BIGINT Next row id

$consumers

View the stored progress of streaming consumers:

SELECT * FROM paimon.default.my_table$consumers;

Columns:

Column Type Description
consumer_id STRING Consumer identifier
next_snapshot_id BIGINT Next snapshot the consumer will read

$tags

View all named tags of a table:

SELECT * FROM paimon.default.my_table$tags;

Columns:

Column Type Description
tag_name STRING Tag name
snapshot_id BIGINT Snapshot ID
schema_id BIGINT Schema ID
commit_time TIMESTAMP Commit time
record_count BIGINT Record count
create_time TIMESTAMP Tag creation time; NULL for tags written without one
time_retained STRING Tag retention as an ISO-8601 duration (for example PT72H); NULL for tags written without one

For tables managed by a REST catalog, the tags are listed from the catalog rather than from tag files in table storage. Legacy tags named . or .., which cannot be addressed safely by the REST API, are omitted with a warning so they cannot prevent other tags from being queried. Their storage files are ignored, and direct time travel by these legacy names is rejected.

create_time is a local TIMESTAMP without a timezone. REST catalog creation times are converted from epoch milliseconds to the client's local time, matching the local date-time fields in filesystem tag metadata. Its raw Arrow integer is not an epoch timestamp.

$branches

View all branches of a table:

SELECT * FROM paimon.default.my_table$branches;

Columns:

Column Type Description
branch_name STRING Branch name
create_time TIMESTAMP Branch creation time

Unlike the other system tables, $branches ignores a $branch_<name> prefix and always reports the branches of the base table.

$manifests

View manifest files of the latest snapshot:

SELECT * FROM paimon.default.my_table$manifests;

Columns:

Column Type Description
file_name STRING Manifest file name
file_size BIGINT File size in bytes
num_added_files BIGINT Number of added data files
num_deleted_files BIGINT Number of deleted data files
schema_id BIGINT Schema ID
min_partition_stats STRING Minimum partition stats, formatted as a Java row cast string
max_partition_stats STRING Maximum partition stats, formatted as a Java row cast string
min_row_id BIGINT Minimum row id covered (when row tracking is enabled)
max_row_id BIGINT Maximum row id covered (when row tracking is enabled)

$files

View the data files of the current snapshot, with per-file statistics:

SELECT * FROM paimon.default.my_table$files;

Columns:

Column Type Description
partition STRING Partition spec, formatted as a Java row cast string; {} for unpartitioned tables
bucket INT Bucket id the file belongs to
file_path STRING Full data file path, or external_path when the file has one
file_format STRING Data file format, such as parquet or orc
schema_id BIGINT Id of the schema the file was written with
level INT LSM level of the file (0 for unmerged files)
record_count BIGINT Number of rows in the file, including deletes
file_size_in_bytes BIGINT File size in bytes
min_key STRING Minimum primary key in the file, NULL for append tables
max_key STRING Maximum primary key in the file, NULL for append tables
null_value_counts STRING Per-column null counts, as a {col=count} map
min_value_stats STRING Per-column minimum values, as a {col=value} map
max_value_stats STRING Per-column maximum values, as a {col=value} map
min_sequence_number BIGINT Minimum sequence number in the file
max_sequence_number BIGINT Maximum sequence number in the file
creation_time TIMESTAMP File creation time
delete_row_count BIGINT Number of delete rows in the file
file_source STRING How the file was produced: APPEND or COMPACT
first_row_id BIGINT First row id in the file (when row tracking is enabled)
write_cols ARRAY Columns actually written, for data-evolution tables

$partitions

View all partitions of a table with aggregated record counts and file sizes:

SELECT * FROM paimon.default.my_table$partitions;

Columns:

Column Type Description
partition STRING Partition spec, formatted as key1=val1/key2=val2
record_count BIGINT Total record count across all data files in the partition
file_size_in_bytes BIGINT Total file size in bytes
file_count BIGINT Number of data files
last_update_time TIMESTAMP Latest data-file creation time
created_at TIMESTAMP Partition creation time (only available with metastore-tracked catalogs)
created_by STRING User who created the partition (catalog-tracked only)
updated_by STRING User who last updated the partition (catalog-tracked only)
options STRING Per-partition options as flat JSON (catalog-tracked only)
total_buckets INT Total bucket count for the partition (0 unless catalog-tracked)
done BOOLEAN Whether the partition is marked done (false unless catalog-tracked)

$table_indexes

View committed global index files, including BTree indexes, vector indexes, and deletion-vector metadata:

SELECT * FROM paimon.default.my_table$table_indexes;

Columns:

Column Type Description
partition STRING Partition spec for the indexed data, formatted as a Java row cast string; {} for unpartitioned tables
bucket INT Bucket id covered by the index file
index_type STRING Index type, such as btree, bitmap, multivalue, fm, ivf-flat, diskann, or DELETION_VECTORS
file_name STRING Index file name. It resolves to the table index/ directory, or to the bucket's data-file directory when index-file-in-data-file-dir is set; an index file with an external path is read from that path instead
file_size BIGINT Index file size in bytes
row_count BIGINT Number of rows covered by the index file
dv_ranges ARRAY Deletion-vector ranges, only populated for deletion-vector metadata
row_range_start BIGINT First row id covered by the index file
row_range_end BIGINT Last row id covered by the index file
index_field_id INT Field id of the indexed column
index_field_name STRING Name of the indexed column

$physical_files_size

Scan the table directory recursively and compute the total size of recognized physical files on disk, categorized by file type. This table is a diagnostic size summary; orphan cleanup needs file-level candidates and retention checks, not just aggregate size differences.

Files are classified by their table-relative path: - manifest/manifest-*, manifest/manifest-list-*, and manifest/index-manifest-* → manifest - statistics/* → manifest file counters for the current compatible output schema - index/* → index - <partition>/bucket-*/index-* and <partition>/bucket-postpone/index-* → index, where index-file-in-data-file-dir puts them; classification follows the file's physical form, not the current option value - <partition>/bucket-*/* and <partition>/bucket-postpone/* → data, using the table's partition depth - unknown files are ignored by this summary

SELECT * FROM paimon.default.my_table$physical_files_size;

Columns:

Column Type Description
manifest_file_count BIGINT Number of manifest files on disk
manifest_file_size BIGINT Total size of manifest files (bytes)
data_file_count BIGINT Number of recognized data files on disk
data_file_size BIGINT Total size of recognized data files (bytes)
index_file_count BIGINT Number of index files on disk
index_file_size BIGINT Total size of index files (bytes)

$referenced_files_size

Compute aggregated manifest/data/index file size summaries for all snapshots referenced by a table, including snapshots from the main branch, tags, and other branches. This is useful for understanding storage usage and for orphan file analysis.

Historical snapshots may be in the process of being cleaned up — if a manifest file has already been deleted, it is gracefully skipped (counted as 0 files/bytes).

SELECT * FROM paimon.default.my_table$referenced_files_size;

Columns:

Column Type Description
source STRING Scope: total or branch:<name>
manifest_file_count BIGINT Number of manifest files
manifest_file_size BIGINT Total size of manifest files (bytes)
data_file_count BIGINT Number of data files
data_file_size BIGINT Total size of data files (bytes)
index_file_count BIGINT Number of index files
index_file_size BIGINT Total size of index files (bytes)

The output contains one row per scope: - total — unique referenced files across all branches and tags - branch:main — main branch snapshots + tag snapshots - branch:<name> — one row per other branch

For tables managed by a REST catalog, branches and tags come from the catalog, including branches with no corresponding storage directory. Current snapshots and tag snapshots returned by the catalog are counted even when their snapshot files are absent, together with the snapshot history visible in table storage. REST metadata API failures, including HTTP 501, fail the query rather than return an incomplete count. Invalid or unaddressable branch names also fail the query. Legacy . or .. tags fail this query because their catalog snapshots cannot be read safely; they are not replaced by storage files.

To estimate possible orphan file size for recognized data files:

SELECT p.data_file_size - r.data_file_size AS orphan_data_size
FROM paimon.default.my_table$physical_files_size p,
     paimon.default.my_table$referenced_files_size r
WHERE r.source = 'total';

Branch References

Read a table branch with Java-compatible $branch_<name> syntax:

SELECT * FROM paimon.default.my_table$branch_b1;

System tables support the same branch syntax:

SELECT * FROM paimon.default.my_table$branch_b1$options;
SELECT * FROM paimon.default.my_table$branch_b1$snapshots;

Branch references are read-only in DataFusion. INSERT, UPDATE, DELETE, MERGE INTO, TRUNCATE TABLE, and ALTER TABLE against a branch reference are rejected.

Table Options

Set via WITH ('key' = 'value') at table creation time, or dynamically via SET.

Bucket Configuration

Option Description
'bucket' = 'N' Fixed N buckets (e.g. 1, 2, 4)
'bucket' = '-1' Dynamic bucket mode (HASH index)
'bucket' = '-2' Postpone bucket mode (deferred assignment)
'bucket-key' = 'col' Explicit bucket key column
'bucket-function.type' = 'default' \| 'mod' \| 'hive' Function used to map fixed bucket keys to bucket ids

Merge Engine

Option Description
'merge-engine' = 'deduplicate' Deduplicate engine (default for PK tables), last write wins
'merge-engine' = 'first-row' Keeps the first written row
'merge-engine' = 'partial-update' Basic partial-update engine for PK tables
'merge-engine' = 'aggregation' Basic aggregation engine for PK tables

For deletion-vector-enabled primary-key tables using the default deduplicate engine, batch scans hide uncompacted level-0 files by default. Set 'deletion-vectors.merge-on-read' = 'true' to include those files and merge their key versions on read. Existing deletion vectors are applied before the key merge. This option affects batch snapshot reads only; it does not change streaming or changelog behavior. It takes effect only when 'deletion-vectors.enabled' = 'true'; otherwise it is ignored.

Rust supports the basic partial-update engine with latest-non-null semantics. Set either 'ignore-delete' = 'true' or 'partial-update.ignore-delete' = 'true' to ignore DELETE and UPDATE_BEFORE rows during writes and when reading existing files. The default and an explicit false continue to reject these retract rows. Once enabled on an existing partial-update table, ignore-delete cannot be changed back to false.

Rust supports reading and writing partial-update tables that define fields.<sequence-field>.sequence-group=<protected-fields>. Single and composite sequence fields, multiple independent groups, and projected reads are supported. Rows whose sequence tuple is entirely null do not update the group; an accepted group update can set protected fields to null.

Set fields.<sequence-fields>.sequence-group.sort-order independently for each group: ascending (the default) retains larger ordering values, while descending retains smaller ones. Composite sequence fields are compared lexicographically in their declared order, using the same direction for every component. Non-null ordering values take precedence over null in both directions, and equal ordering tuples still replace protected fields, including with null. For example, fields.first_event_time.sequence-group=channel with fields.first_event_time.sequence-group.sort-order=descending retains 50/referral from updates 100/organic and 50/referral.

Sort-order options require a corresponding sequence group on a primary-key table with merge-engine=partial-update. Projected reads and column renames preserve the direction. Choose it before writing data: changing the direction cannot recover values discarded by earlier merges. All readers, writers, and compaction jobs must support the option before using descending; older clients may ignore it.

Existing partial-update tables may also configure fields.<field>.aggregate-function or fields.default-aggregate-function. Rust supports the same aggregate-function set as the basic aggregation engine (see below). last_non_null_value may be used without a sequence group; other functions require the target field to be protected by a sequence group. When a lower-precedence group sequence arrives, order-sensitive functions use Java compatible reversed aggregation. Unknown functions and incompatible function/type combinations fail closed.

Rust can read deletion-vector-enabled partial-update and aggregation tables. Fully materialized compacted files can be read as raw-convertible splits when every file has a known zero delete-row count. Uncompacted overlapping files can also be read with deletion-vectors.merge-on-read=true. Primary-key deletion-vector compaction still requires merge-engine=deduplicate, so these engines do not produce DV compaction output in Rust.

Rust currently supports merge-engine=aggregation in basic mode only. It works with fixed buckets and dynamic buckets ('bucket' = '-1'). Cross-partition updates ('bucket' = '-1' when the primary key does not contain every partition field) keep the existing partition/bucket location, matching Java UseOldExistingProcessor. Each write-buffer flush aggregates duplicate primary keys before writing data files, so a file contains at most one row per key. Input changelog files retain the original rows.

Per-field aggregate functions are selected in this order: sequence fields always use last_value; primary-key columns pass through without an aggregator; otherwise fields.<field>.aggregate-function, then fields.default-aggregate-function, then last_non_null_value. Defining fields.<sequence-field>.aggregate-function is rejected, matching Java schema validation. The undocumented Java SPI alias first_not_null_value is accepted and resolved to first_non_null_value.

Supported aggregate functions:

Function Column type
sum, product integer, FLOAT, DOUBLE, DECIMAL
min, max comparable scalars (not MAP, MULTISET, ROW, ARRAY, VECTOR, VARIANT, or BLOB)
last_value, first_value, last_non_null_value, first_non_null_value, primary-key any
bool_and, bool_or BOOLEAN
listagg unbounded STRING (VARCHAR with max length)
collect ARRAY
merge_map MAP
merge_map_with_keytime MAP whose value is a ROW with at least two fields
nested_update, nested_partial_update ARRAY<ROW>
rbm32, rbm64, hll_sketch, theta_sketch VARBINARY

Field-scoped options used by these functions:

  • fields.<field>.ignore-retract=true ignores retract cells for that field. It conflicts with aggregation.remove-record-on-delete=true.
  • fields.<field>.list-agg-delimiter and fields.<field>.distinct for listagg; fields.<field>.distinct also applies to collect.
  • fields.<field>.sum.fail-on-overflow / fields.<field>.product.fail-on-overflow reject integer overflow instead of wrapping. Floating-point addition is unaffected; Decimal overflow still yields NULL as in Java.
  • fields.<field>.ts-field selects the key-time field for merge_map_with_keytime (defaults to the last ROW field).
  • fields.<field>.nested-key, fields.<field>.nested-sequence-field, fields.<field>.nested-key-null-strategy, and fields.<field>.count-limit for nested array aggregators. nested_partial_update requires nested-key.
  • fields.<field>.count-limit also applies to collect, see below.

collect supports fields.<field>.distinct and fields.<field>.count-limit. A positive limit N keeps the first N elements in merge order, a negative limit -N keeps the last N, and 0 keeps none. There is no limit by default. With distinct=true, the limit counts distinct elements: a positive limit keeps the first occurrence of each element and a negative limit keeps the last, both in their original order. For example, merging rows [1, 2] and [1, 3] of one key with distinct=true gives [1, 2] for count-limit=2 and [1, 3] for count-limit=-2. Both options apply when two or more rows of the same key are merged, including in write-buffer flushes and compaction, and the merged array is what gets written to data files. As in Java, a key with only one row keeps the array it was written with until another row of that key arrives or, with changelog-producer=lookup, until it is compacted.

DELETE / UPDATE_BEFORE rows are fed to each field aggregator's retract path (AggregateMergeFunction), except that aggregation.remove-record-on-delete=true turns a DELETE into a full-row tombstone via replace_with_delete instead of retract. Native retract is implemented for sum, product, collect, merge_map, nested_update, last_value, last_non_null_value, and primary-key (which applies agg on retract). Aggregators without native retract — including min, max, bool_and / bool_or, listagg, first_value, first_non_null_value, nested_partial_update, merge_map_with_keytime, and the bitmap/sketch functions — reject retract unless fields.<field>.ignore-retract=true.

This is not full Java feature parity. Aggregation tables still reject fields.*.sequence-group. Primary-key deletion-vector compaction remains limited to deduplicate, so aggregation tables do not write deletion-vector compaction output; reading DV-enabled aggregation tables, including merge-on-read of uncompacted files, is supported.

Primary-key compaction supports changelog-producer=lookup, including deletion-vector-enabled deduplicate tables, generating INSERT, UPDATE_BEFORE, UPDATE_AFTER and DELETE events when L0 files are compacted. The executor requires local-cache.enabled=true on the catalog/FileIO. See Lookup changelog producer.

Global Index Options

Set these options when building global indexes with CALL sys.create_global_index or automatic builds. Builders require row tracking, bucket=-1, and no primary key. Data evolution and query-side global-index optimization may be disabled. Full-text builds reject deletion-vector tables.

Option Default Description
row-tracking.enabled false Enables stable row ids required by global index files.
data-evolution.enabled false Enables row-id-aware table evolution and partial-column writes.
global-index.enabled true Enables global index metadata and global-index-aware reads.
global-index.<family>.index-columns unset Comma-separated columns maintained automatically with write-only=false. Families: btree, bitmap, multivalue, fm, rtree, full-text, vector. Each column owns one index.
fields.<column>.global-index.<family>.index-options unset Per-column JSON object of native writer options, following primary-key index option resolution.
fields.<column>.global-index.vector.index-type required for declared vector columns Vector algorithm, such as diskann or ivf-flat. Changes replace incompatible payloads atomically during maintenance.
fields.<column>.global-index.vector.distance-metric inner_product Vector distance metric for builds and FULL/DETAIL raw fallback: inner_product, cosine, or l2. Changes replace incompatible payloads atomically during maintenance.
global-index.row-count-per-shard 100000 Maximum row count per vector global-index shard.
sorted-index.records-per-range 100000 Maximum row count per BTree, bitmap, multivalue, FM, or R-tree global-index shard; falls back to legacy btree-index.records-per-range.
btree-index.block-size 64kb Target BTree data-block size.
btree-index.file-version 1 BTree file version to write: 1 or 2. V2 uses adaptive postings and requires V2-capable readers.
btree-index.bloom-filter.enabled false Writes a Bloom filter used to avoid BTree data-block reads for missing equality and IN keys.
btree-index.compression none BTree block compression: none, zstd, lz4, or lzo.
btree-index.compression-level 1 BTree compression level (used by codecs that support levels).
bitmap-index.dictionary-block-size 16kb Target bitmap dictionary-block size.
bitmap-index.compression none Bitmap dictionary/index block compression: none, zstd, lz4, or lzo.
bitmap-index.compression-level 1 Bitmap compression level (used by codecs that support levels).
multivalue-index.dictionary-block-size 16kb Target multivalue dictionary-block size.
multivalue-index.compression none Multivalue dictionary/index block compression: none, zstd, lz4, or lzo.
multivalue-index.compression-level 1 Multivalue compression level (used by codecs that support levels).
rtree-index.page-size 256 Entries per packed page (2 to 4096).
rtree-index.partition-row-count 65536 Source rows per physical R-tree file (1 to 1048576).
rtree-index.read-cache-size 8 MiB Shared page-cache budget. Zero disables caching.
rtree-index.read-batch-size 1 MiB Maximum bytes per coalesced read (4 KiB to 16 MiB, with at least one page per read).
rtree-index.read-file-concurrency 4 Maximum concurrent file traversals sharing an index read context.
fm-index.partition-size 16mb Maximum encoded symbol count targeted by each FM partition. A single encoded value must be smaller than this limit.
fm-index.partition-row-count 100000 Maximum row count per FM partition.
fm-index.sa-sample-rate 32 Power-of-two suffix-array sampling rate used by FM locate operations.
fm-index.compression lz4 FM block compression: none, zstd, lz4, or lzo.
fm-index.compression-level 1 FM compression level (used by codecs that support levels).
fm-index.read-cache-size 64mb Scan-scoped decoded FM block cache size.
fm-index.demand-page-size 512kb Target compressed-block read-ahead size for FM demand paging.
fm-index.locate-cost-ratio 0.001 Maximum estimated locate work as a fraction of indexed text; denser matches fall back to the source scan.
btree-index.fallback-scan-max-size 256mb Maximum total size of BTree global-index files requiring fallback scans for range/between and suffix/contains/complex LIKE predicates; files proven to match completely by metadata need no scan and are excluded. 0 disables BTree fallback index scans.
bitmap-index.fallback-scan-max-size 256mb Maximum total size of selected bitmap global-index files for fallback scans used by range/between and suffix/contains/complex LIKE predicates; 0 disables bitmap fallback index scans.
global-index.search-mode fast Global index coverage mode for reads: fast, full, or detail.
vector-index.post-filter.candidates-factor 10 Multiplier of each query's original Top-K for one ANN query followed by exact scalar post-filtering. At least the required reranking/deletion allowance, capped by shard size and the backend limit. Positive integer no greater than 2147483647. Short results are returned without retry.
global-index.thread-num 32 Number of concurrent global-index search tasks; must be greater than 0 and must not exceed the runtime's task limit. This does not limit Vindex file range reads.
global-index.vindex.read-thread-num 64 Maximum number of concurrent Vindex file range reads shared by one search operation; must be greater than 0 and must not exceed the runtime semaphore limit.
global-index.column-update-action THROW_ERROR What a commit does when it updates an indexed column: THROW_ERROR rejects the commit, DROP_PARTITION_INDEX drops the affected partition index instead.

global-index.vindex.read-thread-num is independent of global-index.thread-num. When upgrading a table that sets global-index.thread-num, set the new option explicitly to the same value if Vindex range reads should keep the previous limit; otherwise they use the new default of 64.

Variant Shredding Options

Set these as table options when writing VARIANT columns to Parquet. The logical table schema remains VARIANT; the options only affect the physical file layout and automatic read-time assembly.

Option Default Description
variant.shreddingSchema unset Configured shredding schema as a Paimon ROW type JSON string. Top-level field names match VARIANT column names, and their nested types describe the typed fields to extract.
parquet.variant.shreddingSchema unset Parquet-scoped alias for variant.shreddingSchema.
variant.inferShreddingSchema false Enables per-writer schema inference for VARIANT columns when no configured shredding schema is set.
parquet.variant.inferShreddingSchema false Parquet-scoped alias for variant.inferShreddingSchema.
variant.shredding.maxInferBufferRow 4096 Number of initial rows buffered per data-file writer before inferring the shredding schema. If fewer rows are written, inference runs when the writer is flushed or closed.
variant.shredding.maxSchemaDepth 50 Maximum nested depth considered by inference.
variant.shredding.maxSchemaWidth 300 Maximum number of inferred typed fields across inferred Variant schemas.
variant.shredding.minFieldCardinalityRatio 0.1 Minimum ratio of sampled non-null Variant values that must contain a field before inference keeps it as a typed field.

Configured shredding takes precedence over inferred shredding. If a table has no VARIANT columns, or none of these options enable shredding, Paimon Rust writes the normal physical format without wrapping the writer.

Other Options

Option Description
'sequence.field' = 'col' Sequence field used to determine which record wins during deduplication
'row-tracking.enabled' = 'true' Enable stable row ids
'data-evolution.enabled' = 'true' Enable data evolution (partial-column writes, row-level UPDATE/MERGE/DELETE)
'manifest.sidecar.enabled' = 'true' Write and read manifest block sidecars for partition, row-id, and bucket pruning; when unset, inherits manifest-sort.enabled
'global-index.enabled' = 'true' Enable global index metadata and reads
'deletion-vectors.enabled' = 'true' Enable deletion vectors
'deletion-vectors.merge-on-read' = 'true' Include and key-merge uncompacted level-0 files in DV-enabled deduplicate batch reads
'changelog-producer' = 'input' Changelog producer; primary-key tables support reads and writes in this mode

Manifest sidecars use the Java-compatible <manifest-name>.avro.sidecar format and are published through the manifest metadata's _EXTRA_FILES list. Missing, corrupt, unsupported, or mismatched sidecars fall back to reading the complete manifest. Entry filters and ADD/DELETE reconciliation still run after block selection.

Cross-partition updates are not configured by an option: a primary-key table is in cross-partition update mode when 'bucket' = '-1' and the primary key does not contain every partition field.

Full Example

use std::sync::Arc;
use paimon::{CatalogOptions, FileSystemCatalog, Options};
use paimon_datafusion::SQLContext;

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    // Create catalog
    let mut options = Options::new();
    options.set(CatalogOptions::WAREHOUSE, "file:///tmp/paimon-warehouse");
    let catalog = Arc::new(FileSystemCatalog::new(options)?);

    // Create SQL context and register catalog
    let mut ctx = SQLContext::new();
    ctx.register_catalog("paimon", catalog).await?;

    // Create database and table
    ctx.sql("CREATE SCHEMA paimon.my_db").await?;
    ctx.sql(
        "CREATE TABLE paimon.my_db.users (
            id INT NOT NULL,
            name STRING,
            PRIMARY KEY (id)
        ) WITH ('bucket' = '1')"
    ).await?;

    // Insert data
    ctx.sql("INSERT INTO paimon.my_db.users VALUES (1, 'alice'), (2, 'bob')")
        .await?.collect().await?;

    // Query
    let df = ctx.sql("SELECT * FROM paimon.my_db.users ORDER BY id").await?;
    df.show().await?;

    Ok(())
}