paimon

Provides a read-only table-like interface to Apache Paimon tables in Amazon S3, Azure, HDFS or locally stored.

Syntax

paimon(url [,access_key_id, secret_access_key] [,format] [,structure] [,compression] [,extra_credentials])

paimonS3(url [,access_key_id, secret_access_key] [,format] [,structure] [,compression] [,extra_credentials])

paimonAzure(connection_string|storage_account_url, container_name, blobpath, [,account_name], [,account_key] [,format] [,compression_method])

paimonHDFS(path_to_table, [,format] [,compression_method])

paimonLocal(path_to_table, [,format] [,compression_method])

Arguments

Description of the arguments coincides with description of arguments in table functions s3, azureBlobStorage, HDFS and file correspondingly. format stands for the format of data files in the Paimon table. For paimonS3, an optional extra_credentials parameter can be used to pass a role_arn for role-based access in ClickHouse Cloud. See Secure S3 for configuration steps.

Returned value

A table with the specified structure for reading data in the specified Paimon table.

Limitations

Merge-on-read is not implemented, so primary-key tables cannot be read: the reader returns the raw union of the snapshot’s data files, which still contains the row versions superseded by later upserts. Reading a table whose schema declares primary-key therefore throws. Deletion vectors are not applied, so snapshots with deletion vectors cannot be read: the result would still contain the rows that later DELETE and UPDATE statements removed. Reading a snapshot whose index manifest lists deletion vectors therefore throws, even after deletion-vectors.enabled is turned off. A full compaction in Paimon (compact_strategy => 'full') applies the deletion vectors, and the snapshots created after it can be read again.

Defining a named collection

Here is an example of configuring a named collection for storing the URL and credentials:

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

SELECT * FROM paimonS3(paimon_conf, filename = 'test_table')
DESCRIBE paimonS3(paimon_conf, filename = 'test_table')

Aliases

Table function paimon is an alias to paimonS3 now.

Virtual Columns

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

Data Types supported

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

Partition supported

Data types supported in Paimon partition keys:

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

SQL Reference

Data Types

Engines

Functions

Formats

Settings

System Tables

Data Lakes

Syntax

Arguments

Returned value

Limitations

Defining a named collection

Aliases

Virtual Columns

Data Types supported

Partition supported

See Also

​Syntax

​Arguments

​Returned value

​Limitations

​Defining a named collection

​Aliases

​Virtual Columns

​Data Types supported

​Partition supported

​See Also

Syntax

Arguments

Returned value

Limitations

Defining a named collection

Aliases

Virtual Columns

Data Types supported

Partition supported

See Also