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
SQLContextdoes not intercept are delegated to DataFusion. This includes the DataFusion SQL surface forSELECTqueries, CTEs (including recursive CTEs), subqueries, joins includingLATERALjoins, SQL lambda functions, grouping,HAVING, window clauses,QUALIFY, set operations,ORDER BY,LIMIT/OFFSET,EXPLAIN, information-schema commands such asSHOW TABLES,DESCRIBE,COPY, and ordinaryINSERT. - Paimon-specific table management and row-level writes are implemented by
SQLContext. This includes PaimonCREATE TABLE,ALTER TABLE,DROP TABLE,CREATE TEMPORARY TABLE,CREATE TEMPORARY VIEW, REST Catalog persistentCREATE VIEW,DROP VIEW,CREATE FUNCTION, andDROP 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, andSET/RESET 'paimon.*'.MERGE INTOuses 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.
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:
Drop a persistent view with this syntax:
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:
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.datafusionexists and hastype: "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:
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, andvariant_explode_outerare not implemented yet.variant_getcurrently casts to scalar types andVARIANT. It does not yet cast directly toARRAY,MAP, orSTRUCT.- Simple
variant_getandtry_variant_getexpressions over aVARIANTcolumn, a literal path, and a scalar literal type can be pushed into scans as Variant extraction fields for projections and filters. Predicate translation throughvariant_getis 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:
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 mostnbytes and right-pads shorter values with zero bytes.VARBINARY(n)truncates to at mostnbytes 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¶
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:
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 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:
Note: When using
VALUESwithout explicit column types, DataFusion infers integer literals asInt64. If the temporary table will be used as a source inMERGE INTOagainst a Paimon table withInt32columns, specify the column types explicitly to avoid type mismatch errors.
CREATE TEMPORARY VIEW¶
Create a temporary view from a query:
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:
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 ... SELECT ... is also supported:
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.
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:
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:
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:
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:
Multiple partition key-value pairs can be specified:
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:
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:
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:
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:
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:
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:
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:
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:
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):
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:
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:
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':
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.):
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:
concatpropagates NULL inputs.modandpmodreturn NULL for a zero divisor by default. Setdatafusion.execution.enable_ansi_mode = trueto report division by zero. Their numeric coercion and decimal result types follow the Spark package.sizereturns INT and uses the package's legacy NULL-to-minus-one behavior.- Two-argument
date_diff/datediffreturn INT. The three-argument Paimondate_diff(unit, start, end)extension still returns BIGINT. hourtakes timestamps; cast dates or strings to TIMESTAMP explicitly.hexnumeric 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:
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:
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 LIKEandILIKEare 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
LIMITpushdown, 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.
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:
| 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;
Vector Search¶
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¶
| 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:
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:
Then run vector_search as usual:
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.
Hybrid Search¶
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¶
| 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.
Full-Text Search¶
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.
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¶
| 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:
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:
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:
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:
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:
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.
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):
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):
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:
Returns two columns: key (STRING) and value (STRING).
$schemas¶
View the schema history of a table:
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:
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:
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:
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:
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:
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:
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:
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:
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
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).
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:
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=trueignores retract cells for that field. It conflicts withaggregation.remove-record-on-delete=true.fields.<field>.list-agg-delimiterandfields.<field>.distinctforlistagg;fields.<field>.distinctalso applies tocollect.fields.<field>.sum.fail-on-overflow/fields.<field>.product.fail-on-overflowreject integer overflow instead of wrapping. Floating-point addition is unaffected; Decimal overflow still yields NULL as in Java.fields.<field>.ts-fieldselects the key-time field formerge_map_with_keytime(defaults to the last ROW field).fields.<field>.nested-key,fields.<field>.nested-sequence-field,fields.<field>.nested-key-null-strategy, andfields.<field>.count-limitfor nested array aggregators.nested_partial_updaterequiresnested-key.fields.<field>.count-limitalso applies tocollect, 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(())
}