Syntax
Arguments
Description of the arguments coincides with description of arguments in table functionss3, 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 a primary-key table is read only where Paimon reads it without merging: a bucket is read when all its data files are above level 0, are known to have no retract rows, and are in one level. Withdeletion-vectors.enabled or 'merge-engine' = 'first-row' the files may be in several levels, and, unless
batch-scan-mode is set to a mode other than none, level-0 files are not read until they are compacted, as in Paimon.
Reading any other bucket throws; a full compaction in Paimon (compact_strategy => 'full') makes it readable.
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.
Each data file is read with the schema it was written with, and its top-level columns are matched to the current schema by
Paimon field id, so renamed columns and columns dropped and re-added under the same name read as in Spark.
Fields inside a ROW column are matched to data files by name, not by Paimon field id, so data files written before a
field inside a ROW was renamed, or before such a field was dropped and another one with the same name (compared
case-insensitively) was added, cannot be read: the result would contain NULL for the renamed field and the dropped
field’s values for the re-added one. Reading such a data file therefore throws. Adding and dropping fields is supported.
Rewriting the table with the current schema in Paimon, for example with INSERT OVERWRITE <table> SELECT * FROM <table>,
makes the snapshots created after it readable.
Defining a named collection
Here is an example of configuring a named collection for storing the URL and credentials:Aliases
Table functionpaimon 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 isNULL._time— Last modified time of the file. Type:Nullable(DateTime). If the time is unknown, the value isNULL._etag— The etag of the file. Type:LowCardinality(String). If the etag is unknown, the value isNULL.
Data Types supported
Partition supported
Data types supported in Paimon partition keys:CHARVARCHARBOOLEANDECIMALTINYINTSMALLINTINTEGERDATETIMETIMESTAMPTIMESTAMP WITH LOCAL TIME ZONEBIGINTFLOATDOUBLE