You need to enable JavaScript to run this app.
ByteHouse Cloud Data Warehouse Version

ByteHouse Cloud Data Warehouse Version

  • Documentation
    • ByteHouse Cloud Data Warehouse VersionUser guideData modelingAppearanceIceberg external table
Copy page
Download PDF
Appearance
Iceberg external table
Copy page
Download PDF
Iceberg external table

ByteHouse cloud data warehouse edition supports federated Search using the Method / way of external tables. User do not need to Import Data; they can directly Carry on Search for Data in Iceberg Medium via Iceberg external tables, providing flexibility and convenience for Data Processing. This article will Intro how to use Iceberg external tables in ByteHouse.

Background information

Iceberg Data mapping Method / way

For Iceberg Table, ByteHouse currently Support project create Iceberg external table, create Iceberg External Database, create Iceberg External Catalog in three ways to achieve Data mapping between ByteHouse and Iceberg.

Data mapping method

Data mapping description

Create Iceberg external tables

Establish a mapping from ByteHouse tables to Iceberg tables at the table dimension. Schema changes in the source table are not synced directly. If there are changes, you need to delete the external table in ByteHouse and recreate the external table.

Create Iceberg external databases

Establish the mapping from ByteHouse Database to Iceberg Database at the database level. After establishing the database mapping, manual table mapping is no longer required. Table mappings under the database can be automatically synchronized, and changes to the source table schema can be directly synchronized to ByteHouse.

Create Iceberg external catalog

Establish the mapping from ByteHouse Catalog to Iceberg Catalog at the catalog level. After establishing the catalog mapping, manual database and table mapping is no longer required. Database and table mappings under the catalog can be automatically synchronized. Additions and deletions of Iceberg databases and tables can be detected, and changes to the source table schema can be directly synchronized to ByteHouse. For details on creating an Iceberg external catalog, see Iceberg Catalog.

Fee description

The Function of external tables is to provide Processing capabilities for Other Data besides ByteHouse Internal tables. With a simple DDL statement, you can create an external table in ByteHouse, establishing an association between ByteHouse tables and External data source.

  • External tables in ByteHouse only incur Calculate fees, following ByteHouse Calculate Resources Billing method Rules.
  • When using external tables, the Data in external tables will not be copied to ByteHouse, and will not incur storage fees. For storage fees, please Follow the Rules for storage on the External data source side.
  • When accessing External data source with ByteHouse external tables, the External data source may incur Calculate, access, Data transmission, and others fees. The specific fees depend on the External data source's Billing method; please Follow the Description of the relevant Products.

Usage limitations

Items

Restriction description

Schema Evolution

ByteHouse supports querying tables with Schema Evolution, but there are restrictions on the operation types for the source table schema:

  • Supported source table operations: add columns, delete columns, change column types. ByteHouse can perform normal queries.
  • Unsupported operation: change column name. ByteHouse query will return an error.

Time Travel

The current method is to hide version information in the table name, that is, tbl$version$<identifier>. Currently, other types of version descriptors natively supported by Iceberg are not supported, such as:

  • Timestamp (string, for example 2024-12-01)
  • snapshotId (integer)
  • Branch name or tag name, which can be any string content

To use the current method of passing version descriptors supported by ByteHouse, <identifier> can only be:

  • snapshotId (integer)
  • String that meets identifier naming rules

Automatic detection of table structure changes

  • Database method: can automatically detect table structure changes, as well as additions and deletions of tables
  • Table method: can automatically detect table structure changes

Create Iceberg External Database

By creating a ByteHouse External Database, you establish a mapping from ByteHouse Database to Iceberg Database at the Database Dimension. After establishing the Database mapping, you do not need to manually establish table mappings; the table mappings under the Database can be automatically synced.
Iceberg Data can be stored in different file systems, for example, HDFS and S3. The following introduces table creation statements for different file systems:

Iceberg on HDFS 

CREATE DATABASE test_iceberg_db_hdfs
ENGINE = Iceberg('hadoop', '{warehouse}', '{icebergDatabaseName}')
SETTINGS enable_schema_covert_fault_tolerance = 1;

Parameter

Configuration description

Database description

Customize external database name.

Engine parameter (Engine)

  • hadoop: Fixed configuration is hadoop.
  • warehouse: Configure the address for Iceberg's data warehouse connection, for example: hdfs://haruna/home/byte_flink_test/table_store/ByteHouse_test.

    warning

    Here you fill in the top-level directory of Iceberg.

  • IcebergDatabaseName: Specify the database in Iceberg.

Settings parameter (SETTINGS)

enable_schema_convert_fault_tolerance: Controls the processing method when automatic type inference does not exist.
When a type in Iceberg 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.

Iceberg on S3 

CREATE DATABASE {databaseName}
ENGINE = Iceberg('hadoop', '{warehouse}', '{icebergDatabaseName}')
SETTINGS
endpoint='{endpoint}', 
ak_id='{accessKey}', 
ak_secret='{accessSecretKey}',
s3_extra_options='s3.paging.maximum=1000';

Parameter

Configuration description

Database description

Customize external database name.

Engine parameter (Engine)

  • hadoop: Fixed configuration as hadoop.
  • warehouse: Configure as the address for the Iceberg data warehouse connection, for example: tos://test-dev-hcf/iceberg.

    warning

    • Here you fill in the top-level directory of Iceberg.
    • If the file system for Iceberg data storage is BytePlus TOS, this field can be configured with a domain name starting with tos:// or s3://.
      For example, after copying the storage path of the external data source, it is: bhlasinfo/Iceberg.
      Then, the address to be configured after concatenation can be: tos://bhlasinfo/Iceberg, s3://bhlasinfo/Iceberg.
  • IcebergDatabaseName: Specify the database in Iceberg.

Parameter setting (SETTINGS)

  • enable_schema_covert_fault_tolerance: Controls the method for processing when automatic type inference fails.
    When a type in Iceberg 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.
  • endpoint: 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 navigate via Bucket List > Bucket Name > Overview > URL Full Domain path to view the required endpoint. As shown below, the endpoint is tos-s3-cn-beijing.volces.com.

Image

  • ak_id/ak_secret: Key with S3 access privilege.

S3 parameter setting

s3_extra_options: Optional parameter for tuning S3 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 Hadoop Help Documentation.

Create Iceberg external table

When creating an external table, you need to specify the associated external Database and table information. Currently, you can specify the Column information of the external table, or obtain the corresponding Column information through Automatic inference.

  • Specified column information: All specified column names must exist in the corresponding Iceberg 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 inferred automatically: The enable_schema_covert_fault_tolerance parameter controls how to handle cases where the inferred type does not exist. When a type in Iceberg cannot be matched with a compatible type in ByteHouse: a value of 1 means to ignore; 0 means to report an error; the default value is 1.

Iceberg data can be stored on different file systems, such as HDFS and S3. The following section provides an introduction to the table creation statements for different file systems:

Iceberg on HDFS

Manual specification of column information 

CREATE TABLE `{tableName}` (
    `col_boolean` BOOLEAN,
    `col_integer` Int32,
    `col_long` Int64,
    `col_float` Float32,
    `col_double` Float64,
    `col_decimal` DECIMAL(10, 2),
    `col_date` DATE32,
    `col_timestamp` DateTime64(3),
    `col_uuid` UUID,
    `col_string` String,
    `col_fixed` FixedString(50),
    `col_binary` String,
    `col_list_integer` Array(Int32),
    `col_map` Map(String, Int32),
    `col_struct` Tuple(`field_integer` Int32, `field_string` String, `field_timestamp` DateTime64(3), `field_decimal` DECIMAL(28, 6), `field_binary` String)
) 
ENGINE = CnchIceberg('hadoop', '{warehouse}', '{icebergDatabaseName}', '{icebergTableName}');

Parameter

Configuration description

Table creation description

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

tip

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

  • Column names need to correspond one-to-one with the Iceberg table; the order of columns does not need to correspond one-to-one.
  • When creating an external table, you can select only some columns from the Iceberg table, but all partition columns must be included.
  • The partition of external table columns needs to be specified using the partition by statement, and must also be defined in the description list like normal columns.

Engine parameters (Engine)

  • hadoop: Fixed configuration as hadoop.
  • warehouse: Configure as the address for the Iceberg data warehouse connection, for example: hdfs://haruna/home/byte_flink_test/table_store/ByteHouse_test.

    warning

    Here you fill in the top-level directory of Iceberg.

  • IcebergDatabaseName: Specify the database in Iceberg.
  • IcebergTableName: Specify the table in Iceberg.

Automatic inference of column information 

CREATE TABLE `{tableName}`
ENGINE = CnchIceberg('hadoop', '{warehouse}', '{icebergDatabaseName}', '{icebergTableName}')
SETTINGS enable_schema_covert_fault_tolerance = 1;

Parameter

Configuration description

Table creation description

The column names and types of the Iceberg table, as well as the Partition By key, will be automatically inferred and established during table creation. For column type mapping rules, see the Column type mapping section below.

Engine parameters (Engine)

  • hadoop: Fixed configuration as hadoop.
  • warehouse: Configure as the address for the Iceberg data warehouse connection, for example: hdfs://haruna/home/byte_flink_test/table_store/ByteHouse_test.

    warning

    Here you fill in the top-level directory of Iceberg.

  • IcebergDatabaseName: Specify the database in Iceberg.
  • IcebergTableName: Specify the table in Iceberg.

Parameter setting (SETTINGS)

enable_schema_covert_fault_tolerance: Controls the method for processing when automatic type inference fails.
When a type in Iceberg 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.

Iceberg on S3（TOS）

Manual specification of column information 

CREATE TABLE `{tableName}` (
    `col_boolean` BOOLEAN,
    `col_integer` Int32,
    `col_long` Int64,
    `col_float` Float32,
    `col_double` Float64,
    `col_decimal` DECIMAL(10, 2),
    `col_date` DATE32,
    `col_timestamp` DateTime64(3),
    `col_uuid` UUID,
    `col_string` String,
    `col_fixed` FixedString(50),
    `col_binary` String,
    `col_list_integer` Array(Int32),
    `col_map` Map(String, Int32),
    `col_struct` Tuple(`field_integer` Int32, `field_string` String, `field_timestamp` DateTime64(3), `field_decimal` DECIMAL(28, 6), `field_binary` String)
) 
ENGINE = CnchIceberg('hadoop', '{warehouse}', '{icebergDatabaseName}', '{icebergTableName}')
SETTINGS
endpoint='{endpoint}', 
ak_id='{accessKey}', 
ak_secret='{accessSecretKey}',
s3_extra_options='s3.paging.maximum=1000';

Parameter

Configuration description

Table creation description

The column names and types of the Iceberg table. For the column type mapping rules, see the column type mapping section below.

tip

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

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

Engine parameters (Engine)

  • hadoop: Fixed configuration as hadoop.
  • warehouse: Configure the address for the Iceberg data warehouse connection, for example: tos://test-dev-hcf/iceberg.

    warning

    • Enter the top-level directory of Iceberg here.
    • If the data storage file system for Iceberg is BytePlus TOS, you can configure this field with a domain name starting with tos:// or s3://.
      For example, after copying the storage path of the external data source, it is: bhlasinfo/Iceberg.
      After concatenation, the address to configure can be: tos://bhlasinfo/Iceberg, s3://bhlasinfo/Iceberg.
  • IcebergDatabaseName: Specify the database in Iceberg.
  • IcebergTableName: Specify the table in Iceberg.

SETTINGS parameters

  • endpoint: Connection address of the external data source. For example, if you use BytePlus Object Storage Service TOS, log in to the TOS Console and navigate through Bucket List > Bucket Name > Overview > URL Full Domain to view the required endpoint. As shown in the figure below, the endpoint is tos-s3-cn-beijing.volces.com.

Image

  • ak_id/ak_secrent: Key with S3 privilege.

S3 parameter settings

s3_extra_options: Optional parameter for S3 tuning. 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 (the default value is 500), used to set the maximum size of the connection pool when establishing a connection to the S3 storage service. For all parameters, refer to the Hadoop Help Documentation.

Automatic inference of column information 

CREATE TABLE `{tableName}` 
ENGINE = CnchIceberg('hadoop', '{warehouse}', '{icebergDatabaseName}', '{icebergTableName}')
SETTINGS
endpoint='{endpoint}', 
ak_id='{accessKey}', 
ak_secret='{accessSecretKey}',
s3_extra_options='s3.paging.maximum=1000';

Parameter

Configuration description

Table creation description

Customize the external table name. The column names and types of the Iceberg table, as well as the partition key, will be automatically inferred and created during table creation. For the column type mapping rules, see the column type mapping section below.

Engine parameters (Engine)

  • hadoop: Fixed configuration as hadoop.
  • warehouse: Configure the address for the Iceberg data warehouse connection, for example: tos://test-dev-hcf/iceberg.

    warning

    • Enter the top-level directory of Iceberg here.
    • If the data storage file system for Iceberg is BytePlus TOS, you can configure this field with a domain name starting with tos:// or s3://.
      For example, after copying the storage path of the external data source, it is: bhlasinfo/Iceberg.
      After concatenation, the address to configure can be: tos://bhlasinfo/Iceberg, s3://bhlasinfo/Iceberg.
  • IcebergDatabaseName: Specify the database in Iceberg.

SETTINGS parameters

  • enable_schema_covert_fault_tolerance: Controls the method for handling cases where automatic type inference does not exist.
    When a type in Iceberg cannot find a compatible type in ByteHouse: a value of 1 indicates to ignore; 0 indicates to report an error;
    • The default value is 1.
    • When set to 0, tables with type compatibility issues will not be displayed.
  • endpoint: Connection address of the external data source. For example, if you use BytePlus Object Storage Service TOS, log in to the TOS Console and navigate through Bucket List > Bucket Name > Overview > URL Full Domain to view the required endpoint. As shown in the figure below, the endpoint is tos-s3-cn-beijing.volces.com.

Image

  • ak_id/ak_secrent: Key with S3 privilege.

S3 parameter settings

s3_extra_options: Optional parameter for S3 tuning. 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 Hadoop help documentation.

Writing to external tables

You can use INSERT INTO or INSERT OVERWRITE to write to external tables.

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 database and table name to query. If you have run USE database_name before executing this statement, database can be left blank.

Write parameter description

  • VALUES: Fill in the values to be inserted. The format is (val1, val2, ...), (val1, val2, ...),.... Examples are as follows:

    INSERT INTO example_table VALUES (1, 'a', '2021-07-27'), (2, 'b', '2021-07-27')

  • SELECT: Use the result of the SELECT query to write data. You can fill in according to the syntax of the SELECT statement. Examples are as follows:

    # 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 overwrites 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 database and table name to query. If you have run USE database_name before executing this statement, database can be left blank.

Write parameter description

  • VALUES: Fill in the values to be inserted. The format is (val1, val2, ...), (val1, val2, ...),.... Examples are as follows:

    INSERT OVERWRITE example_table VALUES (1, 'a', '2021-07-27'), (2, 'b', '2021-07-27')

  • SELECT: Use the result of the SELECT query to write data. You can fill in according to the syntax of the SELECT statement. Examples are as follows:

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

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

Query external tables

The query operations for external tables are the same as those for normal tables.

  • General method
    SELECT * FROM  t WHERE xxx;

Deleting external databases and tables

The delete operations for external databases and tables are the same as those for normal tables.

  • Delete external databases.

    warning

    After you delete the external database, all external tables under the external database will also be deleted at the same time. Please proceed with caution.

    DROP DATABASE example_db

  • Delete external tables.

    DROP TABLE example_table

Snapshot search 
-- 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;

Search for table metadata

The following are all currently supported metadata searches for Iceberg tables:

  • snapshots: Records all snapshot information of the dataset, used to track and restore historical versions of the data.
  • refs: Contains references (branches and tags) to snapshots, supporting parallel development and marking important versions.
  • history: Records the change history of the dataset, helping to audit and backtrack data changes.
  • files: Provides information about the actual physical data files stored in the dataset, facilitating data management and performance optimization.
  • properties: Schema-related configuration properties.
select * from tbl$metadata$snapshots;
select * from tbl$metadata$refs;
select * from tbl$metadata$history;
select * from tbl$metadata$files;
select * from tbl$metadata$properties;

Reference: Data type mapping

Iceberg type

ByteHouse type

Boolean

UInt8

Integer

Int32

Long

Int64

Float

Float32

Double

Float64

Date

Date32

Time

Not supported

Timestamp

DateTime64(UTC)

String

String

UUID

UUID

Fixed

FixedString

Binary

String

Decimal

Deciaml

Struct

Tuple

List

Array

Map

Map

Last updated: 2026.03.31 16:25:02