Docs

ByteHouse Cloud Data Warehouse Version

Appearance

Paimon external table

ByteHouse Cloud Data Warehouse Edition supports federated queries using external tables. Users do not need to import data; they can directly query data in Paimon through Paimon external tables, providing flexibility and convenience in data processing. This article will introduce how to use Paimon external tables in ByteHouse.

Background information

Paimon data mapping methods

For Paimon data tables, ByteHouse currently supports three methods for data mapping between ByteHouse and Paimon: Create Paimon external table, Create Paimon database, and Create Paimon external catalog.

Data mapping method	Data mapping description
Create Paimon external table	Establish mapping from ByteHouse table to Paimon table at the table dimension. Schema changes in the source table are not directly synchronized; if changes occur, the external table in ByteHouse must be deleted and a new external table created.
Create Paimon external database	Establish mapping from ByteHouse Database to Paimon Database at the database dimension. After establishing the database mapping, manual creation of table mappings is not required. Table mappings under the database can be automatically synchronized, and additions or deletions of Paimon tables can be detected. Schema changes in the source table can be directly synchronized to ByteHouse.
Create Paimon external catalog	Establish mapping from ByteHouse Catalog to Paimon Catalog at the catalog (data directory) dimension. After establishing the catalog mapping, manual creation of database or table mappings is not required. Database and table mappings under the catalog can be automatically synchronized, and additions or deletions of Paimon databases or tables can be detected. Schema changes in the source table can be directly synchronized to ByteHouse. For details on creating a Paimon external catalog, see Paimon Catalog.

Fee note

The external table feature is designed to provide the capability to process data other than ByteHouse internal tables. With a simple DDL statement, you can create an external table in ByteHouse to establish an association between a ByteHouse table and an external data source.

External tables in ByteHouse only incur compute fees, following ByteHouse's compute resource billing rules.
When using the external table feature, the data in the external table will not be copied to ByteHouse, nor will it incur storage fees. For storage fees, please refer to the storage billing rules of the external data source.
When accessing external data sources using ByteHouse external tables, the external data source may incur compute, access, data transmission, and other fees. The specific charges are subject to the billing method of the external data source; please refer to the relevant product documentation.

Usage restrictions

Sub-items	Restriction description
Alter operation	Currently, Alter operations cannot be performed on ByteHouse external tables or ByteHouse external databases. For example, external tables do not support adding columns, deleting columns, or renaming columns; external databases do not support `RENAME DATABASE`.
Time Travel	The current implementation hides version information in the table name, that is, `tbl$version$<identifier>`. Currently, other types of version descriptors natively supported by Paimon are not supported, such as: Timestamp (string, for example 2024-12-01) snapshotId (integer) Branch name or tag name, which can be any string value In ByteHouse, `<identifier>` can only be: snapshotId (integer) A string that meets identifier naming rules
Automatic schema change detection	Database method: Automatically detects schema changes, as well as table addition and deletion. Table method: Automatically detects schema changes.
Complex data types	Currently, complex data types are not supported as Nullable.

Create Paimon external table

By creating a ByteHouse external table, you establish a table-level mapping from the ByteHouse table to the Paimon table. Paimon's data can be stored on different distributed storage systems, such as HDFS or S3. The following will introduce the table creation statements for different distributed storage systems:

Paimon on HDFS

When creating an external table, you need to specify the associated external table database and table details. Currently, you can specify the column details of the external table, or omit column details and obtain the corresponding columns through automatic inference.

Specifying column details: All specified column names must exist in the corresponding Paimon table, and their types must be equivalent or compatible; otherwise, an error will occur.
If column information is not specified, the corresponding column information is automatically inferred: The enable_schema_covert_fault_tolerance parameter controls how to handle cases where the automatically inferred type does not exist. When a type in Paimon cannot find a compatible type in ByteHouse: a value of 1 means to ignore; 0 means to report an error; the default value is 1.

Manually specify column information

CREATE TABLE `{tableName}` (
    `col_boolean` BOOLEAN,
    `col_tinyint` Int8,
    `col_smallint` Int16,
    `col_int` Int32,
    `col_bigint` Int64,
    `col_float` Float32,
    `col_double` Float64,
    `col_decimal` DECIMAL(10, 2),
    `col_date` DATE32,
    `col_time` DateTime,
    `col_timestamp` DateTime64(6),
    `col_char` FixedString(10),
    `col_varchar` String,
    `col_binary` FixedString(50),
    `col_varbinary` String,
    `col_array_int` Array(Int32),
    `col_map` Map(String, Int32),
    `col_struct` Tuple(`field_smallint` Int16, `field_varchar` String, `field_time` DateTime, `field_decimal` DECIMAL(28, 6), `field_varbinary` String)
) 
ENGINE = CnchPaimon('filesystem', '{warehouse}', '{paimonDatabaseName}', '{paimonTableName}');

Parameter	Configuration description
Table description	Paimon table column names and types. For column type mapping rules, see the Column type mapping section below. tip When specifying column names, types, partition keys, and other descriptions: Column names must match those in the Paimon table; the order of columns does not need to be the same. When creating an external table, you can include only some of the columns from the Paimon table, but all partition columns must be included. The partitions for external table columns must be specified using the partition by statement, and must also be defined in the description list just like regular columns.
Engine parameters (Engine)	`filesystem`: Fixed parameter. warehouse: Configure the address for connecting to Paimon's data warehouse, for example: `hdfs://haruna/home/byte_flink_test/table_store/bytehouse_test`. warning Enter the top-level directory of Paimon here. paimonDatabaseName: Specify the database in Paimon. paimonTableName: Specify the table in Paimon.

Parameter

Configuration description

Table description

Paimon table column names and types. For column type mapping rules, see the Column type mapping section below.

tip

When specifying column names, types, partition keys, and other descriptions:

Column names must match those in the Paimon table; the order of columns does not need to be the same.
When creating an external table, you can include only some of the columns from the Paimon table, but all partition columns must be included.
The partitions for external table columns must be specified using the partition by statement, and must also be defined in the description list just like regular columns.

Engine parameters (Engine)

filesystem: Fixed parameter.
warehouse: Configure the address for connecting to Paimon's data warehouse, for example: hdfs://haruna/home/byte_flink_test/table_store/bytehouse_test.
warning
Enter the top-level directory of Paimon here.
paimonDatabaseName: Specify the database in Paimon.
paimonTableName: Specify the table in Paimon.

(Recommended) Automatically infer column information

CREATE TABLE `{tableName}`
ENGINE = CnchPaimon('filesystem', '{warehouse}', '{paimonDatabaseName}', '{paimonTableName}')
SETTINGS enable_schema_covert_fault_tolerance = 0;

Parameter	Configuration description
Table description	tableName: Name of the external table. Paimon table column names and types, as well as Partition By key, will be automatically inferred and created during table creation. For column type mapping rules, see the Column type mapping section below.
Engine parameters (Engine)	`filesystem`: Fixed parameter. warehouse: Configure the address for connecting to Paimon's data warehouse, for example: `hdfs://haruna/home/byte_flink_test/table_store/bytehouse_test`. warning Enter the top-level directory of Paimon here. paimonDatabaseName: Specify the database in Paimon. paimonTableName: Specify the table in Paimon.
Settings (SETTINGS)	enable_schema_covert_fault_tolerance: Controls how to handle cases where automatic type inference fails. When a type in Paimon cannot find a compatible type in ByteHouse: a value of `1` means ignore; `0` means report an error; the default value is `1`.

Paimon on S3

Manually specify column information

CREATE TABLE `{tableName}` (
    `col_boolean` BOOLEAN,
    `col_tinyint` Int8,
    `col_smallint` Int16,
    `col_int` Int32,
    `col_bigint` Int64,
    `col_float` Float32,
    `col_double` Float64,
    `col_decimal` DECIMAL(10, 2),
    `col_date` DATE32,
    `col_time` DateTime,
    `col_timestamp` DateTime64(6),
    `col_char` FixedString(10),
    `col_varchar` String,
    `col_binary` FixedString(50),
    `col_varbinary` String,
    `col_array_int` Array(Int32),
    `col_map` Map(String, Int32),
    `col_struct` Tuple(`field_smallint` Int16, `field_varchar` String, `field_time` DateTime, `field_decimal` DECIMAL(28, 6), `field_varbinary` String)
)
ENGINE = CnchPaimon('filesystem', '${warehouse}', '{paimonDatabaseName}', '{paimonTableName}')
SETTINGS
endpoint='{endpoint}', 
ak_id='{accessKey}', 
ak_secret='{accessSecretKey}',
s3_extra_options='fs.s3a.connection.maximum=1000';

Parameter	Configuration description
Table description	Paimon table column names and types. For column type mapping rules, see the Column type mapping section below.
Engine parameters (Engine)	`filesystem`: Fixed parameter. warehouse: Configure the address for connecting to Paimon's data warehouse, for example: `s3://test-dev-hcf/paimon`. warning Enter the top-level directory of Paimon here. If Paimon's distributed storage system is BytePlus TOS, this field must also be configured with an address starting with `s3://`. For example, after copying the storage path of the external data source: `bhlasinfo/paimon`. Then, the address to be configured after concatenation is: `s3://bhlasinfo/paimon`. paimonDatabaseName: Specify the database in Paimon. paimonTableName: Specify the table in Paimon.
Settings (SETTINGS)	endpoint: Connection address of the external data source. For example, if you are using BytePlus Object Storage Service TOS, log in to the TOS console and view the required endpoint via the Bucket list > Bucket name > Overview > Access domain path. As shown in the figure below, the endpoint is `tos-s3-cn-beijing.volces.com`. ak_id/ak_secrent: Key with S3 access permission.
S3 parameter settings	s3_extra_options: Optional parameter for setting S3 tuning parameters. You can refer to the following format to set tuning parameters: `s3_extra_options='s3.xxx=value1,s3.yyy=value2,s3.zzz=value3'` `fs.s3a.connection.maximum` is a common tuning parameter (default value is 500), used to set the maximum size of the connection pool when establishing connections to the S3 storage service. For all parameters, please refer to the Hadoop Help document.

(Recommended) Automatically infer column information

CREATE TABLE `{tableName}`
ENGINE = CnchPaimon('filesystem', '${warehouse}', '{paimonDatabaseName}', '{paimonTableName}')
SETTINGS
endpoint='{endpoint}', 
ak_id='{accessKey}', 
ak_secret='{accessSecretKey}',
s3_extra_options='fs.s3a.connection.maximum=1000',
enable_schema_covert_fault_tolerance = 0;

Parameter	Configuration description
Table creation description	Customize the Paimon external table name. The column names and types of the table, as well as the Partition By key, will be automatically inferred and created during table creation. For column type mapping rules, see the Column type mapping section below.
Engine parameters (Engine)	`filesystem`: Fixed parameter. warehouse: Configure as the address for connecting to Paimon's data warehouse, for example: `s3://test-dev-hcf/paimon`. warning Enter the top-level directory of Paimon here. If Paimon's distributed storage system is BytePlus TOS, this field must also be configured with an address starting with `s3://`. For example, after copying the storage path of the external data source: `bhlasinfo/paimon`. Then, the address to be configured after concatenation is: `s3://bhlasinfo/paimon`. paimonDatabaseName: Specify the database in Paimon. paimonTableName: Specify the table in Paimon.
Settings (SETTINGS)	enable_schema_covert_fault_tolerance: Controls how to handle cases where automatic type inference is not possible. When a type in Paimon cannot find a compatible type in ByteHouse: value `1` means ignore; `0` means report an error; the default value is `1`. endpoint: The connection address of the external data source. For example, if you use the Volcano Engine object storage service TOS, log in to the TOS console and view the required endpoint via the Bucket list>Bucket name>Overview>Access domain name path. As shown in the figure below, the endpoint is `tos-s3-cn-beijing.volces.com`. ak_id/ak_secrent: Keys with S3 access permissions.
S3 parameter settings	s3_extra_options: Optional parameter used to set S3 tuning parameters. You can refer to the following format to set tuning parameters: `s3_extra_options='s3.xxx=value1,s3.yyy=value2,s3.zzz=value3'` `fs.s3a.connection.maximum` is a common tuning parameter (default value is 500), used to set the maximum size of the connection pool when establishing connections to the S3 storage service. For all parameters, please refer to the Hadoop Help document.

Create Paimon external database

By creating a ByteHouse external database, a mapping is established from ByteHouse Database to Paimon Database at the database level. After establishing the database mapping, it is no longer necessary to manually create table mappings; table mappings within the database can be automatically synchronized.
Paimon's data can be stored on different distributed storage systems, such as HDFS and S3. The following specifically introduces the table creation statements for different distributed storage systems:

Paimon on HDFS

CREATE DATABASE {databaseName}
ENGINE = Paimon('filesystem', '{warehouse}', '{paimonDatabaseName}')
SETTINGS enable_schema_covert_fault_tolerance = 1;

Parameter	Configuration description
Database description	Customize the external database name.
Engine parameters (Engine)	`filesystem`: Fixed parameter. warehouse: Configure as the address for connecting to Paimon's data warehouse, for example: `hdfs://haruna/home/byte_flink_test/table_store/bytehouse_test`. warning Enter the top-level directory of Paimon here. paimonDatabaseName: Specify the database in Paimon.
Settings (SETTINGS)	enable_schema_covert_fault_tolerance: Controls how to handle cases where automatic type inference is not possible. When a type in Paimon cannot find a compatible type in ByteHouse: value `1` means ignore; `0` means report an error; The default value is `1`. When set to `0`, tables with type compatibility issues will not be displayed.

Paimon on S3

CREATE DATABASE {databaseName}
ENGINE = Paimon('filesystem', '{warehouse}', '{paimonDatabaseName}')
SETTINGS
endpoint='{endpoint}', 
ak_id='{accessKey}', 
ak_secret='{accessSecretKey}',
s3_extra_options='fs.s3a.connection.maximum=1000';

Parameter	Configuration description
Database description	Customize the external database name.
Engine parameters (Engine)	`filesystem`: Fixed parameter. warehouse: Configure as the address for connecting to Paimon's data warehouse, for example: `s3://test-dev-hcf/paimon`. warning Enter the top-level directory of Paimon here. If Paimon's distributed storage system is BytePlus TOS, this field must also be configured with an address starting with `s3://`. For example, after copying the storage path of the external data source: `bhlasinfo/paimon`. Then, the address to be configured after concatenation is: `s3://bhlasinfo/paimon`. paimonDatabaseName: Specify the database in Paimon.
Settings (SETTINGS)	enable_schema_covert_fault_tolerance: Controls how to handle cases where automatic type inference is not possible. When a type in Paimon cannot find a compatible type in ByteHouse: a value of `1` means ignore; `0` means report an error; The default value is `1`. When set to `0`, tables with type compatibility issues will not be displayed. endpoint: The connection address of the external data source. For example, if you are using BytePlus Object Storage Service TOS, you can log in to the TOS console and view the required endpoint via the Bucket List > Bucket Name > Overview > URL Full Domain path. As shown in the figure below, the endpoint is `tos-s3-cn-beijing.volces.com`. ak_id/ak_secret, a key with S3 access permission.
S3 Parameter setting	s3_extra_options: Optional parameter, used to set S3 tuning parameters. You can refer to the following format to set tuning parameters: `s3_extra_options='s3.xxx=value1,s3.yyy=value2,s3.zzz=value3'` `fs.s3a.connection.maximum` is a common tuning parameter (default value is 500), used to set the maximum size of the connection pool when establishing a connection with the S3 storage service; for all parameters, please refer to the Hadoop Help document.

Write to external table

You can use INSERT INTO or INSERT OVERWRITE to write to an external table.

INSERT INTO

You can use a basic INSERT INTO statement to write data.

# 方式 1
INSERT INTO database.table VALUES (val1, val2, ...), (val1, val2, ...), ...;

# 方式 2
INSERT INTO database.table SELECT [col1, col2, ... | *] FROM source_db.source_table;

Parameter	Configuration description
Database and table description	database.table: Specify the name of the database and table to query. If you have already executed `USE database_name` before running this statement, you do not need to specify the database.
Description of write parameters	VALUES: fill in the values to be inserted. The format is `(val1, val2, ...), (val1, val2, ...),...`. Example: `INSERT INTO example_table VALUES (1, 'a', '2021-07-27'), (2, 'b', '2021-07-27')` SELECT: Use the results of a SELECT query to write data. You can fill in according to the syntax of the SELECT statement. Example: `# SELECT 源表中的所有数据插入目标表 INSERT INTO example_table SELECT * FROM example_table # SELECT 源表中的指定数据插入目标表 INSERT INTO example_table SELECT s,d,n FROM example_table`

Parameter

Configuration description

Database and table description

database.table: Specify the name of the database and table to query. If you have already executed USE database_name before running this statement, you do not need to specify the database.

Description of write parameters

VALUES: fill in the values to be inserted. The format is (val1, val2, ...), (val1, val2, ...),.... Example:
```
INSERT INTO example_table VALUES (1, 'a', '2021-07-27'), (2, 'b', '2021-07-27')
```

SELECT: Use the results of a SELECT query to write data. You can fill in according to the syntax of the SELECT statement. Example:

# SELECT 源表中的所有数据插入目标表
INSERT INTO example_table SELECT * FROM example_table

# SELECT 源表中的指定数据插入目标表
INSERT INTO example_table SELECT s,d,n FROM example_table

INSERT OVERWRITE

This statement is used to overwrite the table.

# 方式 1
INSERT OVERWRITE database.table VALUES (val1, val2, ...), (val1, val2, ...), ...;

# 方式 2
INSERT OVERWRITE database.table SELECT [col1, col2, ... | *] FROM source_db.source_table;

Parameter	Configuration description
Database and table description	database.table: Specify the name of the database and table to query. If you have already executed `USE database_name` before running this statement, you do not need to specify the database.
Description of write parameters	VALUES: fill in the values to be inserted. The format is `(val1, val2, ...), (val1, val2, ...),...`. Example: `INSERT OVERWRITE example_table VALUES (1, 'a', '2021-07-27'), (2, 'b', '2021-07-27')` SELECT: Use the results of a SELECT query to write data. You can fill in according to the syntax of the SELECT statement. Example: `# SELECT 源表中的所有数据插入目标表 INSERT OVERWRITE example_table SELECT * FROM example_table # SELECT 源表中的指定数据插入目标表 INSERT OVERWRITE example_table SELECT s,d,n FROM example_table`

Parameter

Configuration description

Database and table description

database.table: Specify the name of the database and table to query. If you have already executed USE database_name before running this statement, you do not need to specify the database.

Description of write parameters

VALUES: fill in the values to be inserted. The format is (val1, val2, ...), (val1, val2, ...),.... Example:
```
INSERT OVERWRITE example_table VALUES (1, 'a', '2021-07-27'), (2, 'b', '2021-07-27')
```

SELECT: Use the results of a SELECT query to write data. You can fill in according to the syntax of the SELECT statement. Example:

# SELECT 源表中的所有数据插入目标表
INSERT OVERWRITE example_table SELECT * FROM example_table

# SELECT 源表中的指定数据插入目标表
INSERT OVERWRITE example_table SELECT s,d,n FROM example_table

Query external table

The query operation for external tables is the same as for regular tables.

General approach.
```
SELECT * FROM  t WHERE xxx;
```

Delete external database table

The deletion operation for external database tables is the same as for regular tables.

Delete external database.
warning
After deleting an external database, all external tables under the external database will also be deleted at the same time. Please operate with caution.
```
DROP DATABASE example_db
```
Delete external data table.
```
DROP TABLE example_db.example_table
```

Snapshot query

-- Specify snapshotId=123456789
SELECT * FROM tbl$version$123456789;

-- Specify branch name=branch_1
SELECT * FROM tbl$version$branch_1;

-- Specify tag name=tag_1
SELECT * FROM tbl$version$tag_1;

Query table metadata

The following metadata tables are currently supported for Paimon tables:

manifests: Contains information about data file manifests, used to track the actual physical storage location of the data.
snapshots: Provides snapshot-level information, allowing users to view the state of the data at different points in time.
options: Contains configuration options or parameter settings when creating the table.
schemas: Describes the structure of the dataset, including column names, data types, and other information.
partitions: If the data is stored in partitions, this table provides information about the partitions.
audit_log: Records the operations performed on the data, which can be used for audit purposes.
files: Contains information about the stored data files.
tags: Mark specific important data states to facilitate quick location and rollback to a particular version.
branches: Provide branch information to support parallel development or experimentation with different versions of the dataset.
consumers: May involve information about data subscribers or users, especially in streaming data processing environments.
ro: Optimize read performance.
aggregation_fields: Information related to aggregation fields, which helps in understanding or operating on summary data.
statistics: Provide various statistics about the dataset, such as size, distribution, and more, which are very useful for optimizing query performance.

SELECT * FROM tbl$metadata$manifests;
SELECT * FROM tbl$metadata$snapshots;
SELECT * FROM tbl$metadata$options;
SELECT * FROM tbl$metadata$schemas;
SELECT * FROM tbl$metadata$partitions;
SELECT * FROM tbl$metadata$audit_log;
SELECT * FROM tbl$metadata$files;
SELECT * FROM tbl$metadata$tags;
SELECT * FROM tbl$metadata$branches;
SELECT * FROM tbl$metadata$consumers;
SELECT * FROM tbl$metadata$ro;
SELECT * FROM tbl$metadata$aggregation_fields;
SELECT * FROM tbl$metadata$statistics;

Reference: Data type mapping

tip

You can use the enable_schema_covert_fault_tolerance parameter to control how to handle unsupported data types when mapping data. When a type in Paimon cannot find a compatible type in ByteHouse: a value of 1 means ignore; 0 means report an error; the default value is 1.

Paimon type	ByteHouse type
Boolean	UInt8
TinyInt	Int8
SmallInt	Int16
Int	Int32
BigInt	Int64
Float	Float32
Double	Float64
Date	Date32
Time	Not supported
Timestamp Wihtout TZ	DateTime64(UTC)
Timestamp With Local TZ	DateTime64(local time zone)
Char	FixedString
Varchar	String
Binary	FixedString
VarBinary	String
Decimal	Deciaml
Array	Array
Map	Map
Row	Tuple

Last updated: 2026.03.31 16:25:02

ByteHouse Cloud Data Warehouse Version

Paimon data mapping methods #

Fee note #

Paimon on HDFS #

Manually specify column information #

(Recommended) Automatically infer column information #

Paimon on S3 #

Manually specify column information #

(Recommended) Automatically infer column information #

Paimon on HDFS #

Paimon on S3 #

INSERT INTO #

INSERT OVERWRITE #

Paimon data mapping methods

Fee note

Paimon on HDFS

Manually specify column information

(Recommended) Automatically infer column information

Paimon on S3

Manually specify column information

(Recommended) Automatically infer column information

Paimon on HDFS

Paimon on S3

INSERT INTO

INSERT OVERWRITE