ByteHouse Cloud Data Warehouse version supports Integrate Paimon Catalog. You do not need to Manual input External Database or tables, and you can Search Data in Paimon via Paimon Catalog. This article will introduce how to create and use Paimon Catalog.

Paimon's Data supports being stored in a self-contained Form on storage media. When ByteHouse accesses the Paimon Cluster, it needs to have privilege to the storage media. Currently, ByteHouse supports Paimon Data stored in S3-compatible object storage Service.
Please obtain the following privilege and connection information according to the S3-compatible object storage used by your Paimon Cluster.

• Currently, manual addition of Database and tables, as well as related ALTER Actions, is not supported in Paimon Catalog.
• Paimon Catalog requires ByteHouse engine Version to be 2.3.2.0 or above. Before use, please Confirm your engine Version meets this requirement. You can View the current engine Version via the ByteHouse Console's Tenant management > Basic information Page. If your engine Version does not meet the requirement, you can Submit a ticket and contact the ByteHouse team to Level Upgrade the engine.

This section will introduce how to create a Paimon Catalog stored in S3-compatible object storage.

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

View all catalogs you have created.

SHOW EXTERNAL CATALOGS;

View the Schema information of the specified Catalog.

SHOW CREATE EXTERNAL CATALOG your_catalog_name;

Suppose you have created an External Catalog named paimon_s3. You can Search Data in this Catalog using the following command.

Search tables in Paimon Catalog #

You can directly access tables in Paimon Catalog using the three-part naming method catalog_name.db_name.table_name, for example:

SELECT * FROM paimon_catalog.paimon_db_name.paimon_table_name;

You can also use this method to access native CnchMergeTree tables in ByteHouse Cloud Data Warehouse version. Among them, cnch (abbreviation for cloud-native-clickhouse) is the default Catalog Name in ByteHouse Cloud Data Warehouse version.

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

Cross Catalog Search #

Using External Catalogs, you can directly perform joint queries (such as UNION ALL Actions) between Paimon External tables and ByteHouse's CnchMergeTree tables.

SELECT * FROM paimon_s3.paimon_db.paimon_table
UNION ALL
SELECT (1) FROM cnch.cnch_db.cnch_table;

List Database and tables in Paimon Catalog #

List Database names in the Catalog.

SHOW DATABASES FROM catalog_name;

List Table names in the Database.

SHOW TABLES FROM catalog_name.database_name;

Obtain the CREATE statement for the table.

SHOW CREATE TABLE catalog_name.database_name.table_name;

Switch Catalog #

You can use the following SQL to switch the Catalog for queries:

SWITCH CATALOG catalog_name;

At this time, when you run the query statement again, ByteHouse will read Data from the specified Database and table under Paimon Catalog.

SELECT * FROM paimon_db.paimon_table;

To switch back to ByteHouse's 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 a specified database under the Paimon Catalog:

USE paimon_s3.paimon_db;

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

USE cnch.cnch_database_name;

Search the current catalog in use #

You can use the currentCatalog() function to search the current Catalog.

SELECT currentCatalog();

Search Paimon Catalog metadata #

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

• Query metadata of database tables in Paimon Catalog

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

• Search the metadata of the partitions of tables in the Paimon Catalog

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

You can use the following command to delete Paimon Catalog.

DROP EXTERNAL CATALOG [IF EXISTS] catalog_name;

Pass ByteHouse cloud data warehouse Console to use Paimon Catalog #

ByteHouse Cloud Data Warehouse Edition also supports creating and using the Paimon Catalog via the ByteHouse Cloud Data Warehouse Edition Console. For details, please refer to Manage external catalog.

Use the Paimon appearance #

In addition to accessing Paimon Data Pass Paimon Catalog, ByteHouse also Support project Pass external table Method / way to Search and write Paimon Data, for details please refer to Paimon external table.