ByteHouse Cloud Data Warehouse Edition Support project Integrate MySQL Catalog. You do not need to Manual input External Database or tables; you can Search the Data Of which in MySQL Catalog. This article will Intro how to create and use MySQL Catalog.

Please obtain the following MySQL connection information in advance. You can refer to the official Document of the MySQL Service you use to obtain it:

• IP address
• Port number
• Username and Password

• Not supported yet to Manual Add Database, tables, or related ALTER Actions in MySQL Catalog.
• MySQL Catalog requires the ByteHouse engine Version to be 2.3.2.0 or above. Before use, Confirm your engine Version Satisfy this requirement. You can View the current engine Version on the ByteHouse Console's Tenant management > Basic information Page. If your engine Version is Not as expected, Submit a ticket and contact the ByteHouse team for Level Upgrade of the engine.

CREATE EXTERNAL CATALOG [IF NOT EXISTS] {catalog_name}
PROPERTIES type='mysql', 
address='ip:port',
username='<user>',
password='<password>';

View all Catalog you create.

SHOW EXTERNAL CATALOGS;

View the Schema information of the specified Catalog.

SHOW CREATE EXTERNAL CATALOG your_catalog_name;

Suppose you have create an External Catalog named mysql_catalog. You can Search the Data in this Catalog by the following commands.

Search tables in MySQL Catalog #

You can Direct traffic tables in MySQL Catalog by using the three-part naming Method / way catalog_name.db_name.table_name, for example:

SELECT * FROM mysql_catalog.mysql_db_name.mysql_table_name;

You can also use this Method / way to Direct traffic ByteHouse Cloud Data Warehouse Edition Native CnchMergeTree tables. Of which, cnch (abbreviation for cloud-native-clickhouse) is the Default Catalog Name of ByteHouse Cloud Data Warehouse Edition.

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

Cross Catalog Search #

By using External Catalog, you can directly join MySQL External tables and ByteHouse CnchMergeTree tables.

SELECT * FROM mysql_catalog.mysql_db.mysql_table
UNION ALL
SELECT (1) FROM cnch.cnch_db.cnch_table;

List Database, tables in MySQL Catalog #

List Database name in Catalog.

SHOW DATABASES FROM catalog_name;

List Table name in Database.

SHOW TABLES FROM catalog_name.database_name;

Obtain the create statement of the table.

SHOW CREATE TABLE catalog_name.database_name.table_name;

Switch Catalog #

You can use the following SQL to Switch the Catalog for Search:

SWITCH CATALOG catalog_name;

After running the Search statement again, ByteHouse will read Data from the specified Database and table under MySQL Catalog.

SELECT * FROM mysql_db.mysql_table;

To Switch back to the Default Catalog of ByteHouse, 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 MySQL Catalog:

USE mysql_catalog.mysql_db;

To Switch back to ByteHouse Native tables, use the following command:

USE cnch.cnch_database_name;

Search the currently used Catalog #

You can Search the current Catalog by the currentCatalog() function.

SELECT currentCatalog();

Delete MySQL Catalog #

When you no longer need to access MySQL Data through this Catalog, you can execute this command to Delete the Catalog and clean up Resources.

DROP EXTERNAL CATALOG [IF EXISTS] catalog_name;

Use MySQL Catalog via ByteHouse Cloud Data Warehouse Edition Console #

ByteHouse Cloud Data Warehouse Edition also Support project create and use MySQL Catalog via its Console, details please refer to Manage external catalog.

Use MySQL external tables #

In addition to accessing MySQL Data via MySQL Catalog, ByteHouse also Support project querying MySQL Data via external tables, details please refer to MySQL external table.