ByteHouse Cloud Data Warehouse version supports integrating Iceberg Catalog. You do not need to manually create external databases or tables; you can directly search data in Iceberg via Iceberg Catalog. This article introduces how to create and use Iceberg Catalog.

Iceberg supports multiple metadata storage forms, including the following:

• Self-contained form stored in storage media (such as S3-compatible object storage service)
• Open-source Hive metastore
• BytePlus AI Data Lake Service LAS

ByteHouse Iceberg external Catalog currently supports Iceberg metadata storage forms including S3-compatible object storage service and LAS. The creation method for Iceberg external Catalog varies depending on the metadata storage service used. You can select the corresponding creation method according to the actual service used.

Please follow the metadata storage form used by your Iceberg cluster to obtain the following privileges and connection information.

• Manual addition of databases, tables, and related ALTER actions in Iceberg Catalog is not supported yet.
• Iceberg Catalog requires ByteHouse engine version 2.3.2.0 or above. Before use, confirm that your engine version satisfies this requirement. You can view the current engine version on the ByteHouse Console Tenant management > Basic information page. If your engine version does not meet the requirement, submit a ticket and contact the ByteHouse team to upgrade the engine.

The parameter configuration for Iceberg varies depending on the metadata service used. This section introduces the creation method for Iceberg Catalog based on the type of metadata service.

S3 #

CREATE EXTERNAL CATALOG `{catalogName}`
PROPERTIES type='iceberg',
metastore_type='hadoop', 
warehouse='{warehouse}', 
endpoint='{endpoint}', 
ak_id='{accessKey}', 
ak_secret='{accessSecretKey}';

LAS #

CREATE EXTERNAL CATALOG `{catalogName}` 
PROPERTIES
type='iceberg',
metastore_type='lf',
endpoint='{endpoint}', 
ak_id='{accessKey}', 
ak_secret='{accessSecretKey}', 
lf_metastore_ak_id='{lasAccessKey}',
lf_metastore_ak_secret='{lasAccessSecretKey}',
lf_metastore_catalog='{lasCatalogName}',
lf_metastore_region='{lasEndpoint}',
lf_metastore_url = '{lasUri}';

Assume you have already created an external Catalog named iceberg_s3.

View Iceberg Catalog #

View all Catalogs to list all created external Catalogs.

SHOW EXTERNAL CATALOGS;

View the schema information of a Catalog.

SHOW CREATE EXTERNAL CATALOG catalog_name;

Search tables in Iceberg Catalog #

Users can directly access tables in Iceberg Catalog by using the three-part naming convention catalog_name.db_name.table_name, for example:

SELECT * FROM iceberg_s3.iceberg_db_name.iceberg_table_name;

ByteHouse native CnchMergeTree tables can also be accessed using the following SQL. Here, cnch (short for cloud-native-clickhouse) is used as the default Catalog name for ByteHouse CDW.

SELECT * FROM cnch.cnch_db_name.cnch_db_name;
-- Equivalent to SELECT * FROM cnch_db_name.cnch_db_name;

Cross-Catalog search #

By using external Catalogs, you can directly join Iceberg external tables and ByteHouse CnchMergeTree tables.

SELECT * FROM iceberg_s3.iceberg_db.iceberg_table 
UNION ALL 
SELECT (1) FROM cnch.cnch_db.cnch_table;

List databases and tables in Iceberg Catalog #

List database names in Iceberg Catalog.

SHOW DATABASES FROM catalog_name;

List table names in a database.

SHOW TABLES FROM catalog_name.database_name;

Obtain the create statement of a table.

SHOW CREATE TABLE catalog_name.database_name.table_name;

Switch Catalog #

You can use the following SQL to change the default Catalog. After execution, subsequent queries will use the databases and tables under this Catalog by default:

SWITCH CATALOG catalog_name;

After running the query statement again, ByteHouse will read data from the specified database and table under the Iceberg Catalog.

SELECT * FROM iceberg_db.iceberg_table;

To switch back to the ByteHouse default Catalog, you can use any of the following commands:

# 方式 1
SWITCH CATALOG default;

# 方式 2
SWITCH CATALOG cnch;

You can also use the following command to directly switch the default database to the specified database under the Iceberg Catalog:

USE iceberg_s3.iceberg_db;

To switch back to ByteHouse internal tables, use the following command:

USE cnch.cnch_database_name;

Query the currently used Catalog #

You can query the current Catalog by using the currentCatalog() function.

SELECT currentCatalog();

Query Iceberg Catalog metadata #

You can use the following statements to query metadata of databases, tables, and partitions in the Iceberg Catalog, including database name, storage path, attributes, and more.

• Query metadata of database tables in the Iceberg Catalog

  SELECT * FROM catalog_name.database_name.table_name$metadata$properties
  

• Query metadata of table partitions in the Iceberg Catalog

  SELECT * FROM {catalog_name.}database_name.table_name$metadata$partitions
  

Delete Iceberg Catalog #

You can use the following command to delete the Iceberg Catalog.

DROP EXTERNAL CATALOG [IF EXISTS] catalog_name;

Use Iceberg Catalog via ByteHouse Cloud Data Warehouse Console #

ByteHouse Cloud Data Warehouse also supports creating and using Iceberg Catalog via the ByteHouse Cloud Data Warehouse Console. For details, see Manage external catalog.

Use Iceberg external tables #

In addition to accessing Iceberg data via Iceberg Catalog, ByteHouse also supports querying and writing Iceberg data via external tables. For details, see Iceberg external table.