This article provides an Intro to the syntax and usage for creating MySQL External Databases and External tables.

ByteHouse supports MySQL external tables, using the MySQL engine to Run Data Search on data stored on remote MySQL servers.

MySQL data mapping method #

For MySQL tables, ByteHouse currently supports create MySQL external table, create MySQL External Database, create MySQL External Catalog—three methods to achieve data mapping between ByteHouse and MySQL.

Fee description #

The function of External tables is designed to provide processing capabilities for data beyond ByteHouse internal tables. With a simple DDL statement, you can create an External table in ByteHouse and establish an association between the ByteHouse table and the External data source.

• External tables in ByteHouse only incur calculation fees, following ByteHouse's calculation resource billing rules.
• When using the External table function, data in the External table will not be copied to ByteHouse or incur storage fees. For storage fees, please follow the billing rules of the external data source's storage side.
• When accessing an external data source using ByteHouse external tables, the external data source may incur calculation, access, data transmission, and other fees. The specific fees are subject to the external data source's billing method. Please refer to the relevant product's description. Prerequisites

Before performing create, search, update, or delete actions for MySQL external tables, ensure you have acquired the corresponding read and write privileges for the database and table.

Before performing search or management actions for MySQL external tables, ensure you have acquired the corresponding read and write privileges for the table.

• The current network requirements for accessing MySQL external tables are as follows:
  • Private network access requires the Private Link method. To use this method, contact the ByteHouse team to enable it.
  • Public network access requires configuration of ByteHouse's public IP. For settings details, refer to Network information settings.
• If your network environment has access limitations, add the ByteHouse service IP address to the allowlist. For details, refer to Set IP allowlist.

CREATE TABLE [IF NOT EXISTS] [db.]table_name (
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
    ...
) ENGINE = MySQL('host:port', 'database', 'table', 'user', 'password'[, replace_query, 'on_duplicate_clause']);

Example
When using, replace the examples in the command with the actual connection address, database, table, username, and password.

DROP TABLE IF EXISTS demo_db.mysql_table; 

-- create MySQL external table. You need to ensure that the DDL of the sync table and ByteHouse are consistent
CREATE TABLE demo_db.mysql_table(
    id Int32,
    name String
) ENGINE = MySQL('127.x.x.1:3306', 'test_db', 'test_tb', 'demo_user', 'password');

-- View the create table statement
SHOW CREATE TABLE demo_db.mysql_table;

By creating a ByteHouse external database, establish a mapping from ByteHouse Database to MySQL Database at the database level. After establishing the database mapping, there is no need to manually create table mappings; table mappings under the database can be automatically synchronized.

CREATE DATABASE [IF NOT EXISTS] database_name
ENGINE = MySQL('host:port', 'database', 'user', 'password');

Example
When using, please replace the placeholders in the command with the actual connection address, database, username, and password.

-- create MySQL external database
CREATE DATABASE mysql_db
ENGINE = MySQL('127.x.x.1:3306', 'test_db', 'demo_user', 'password');

-- Search the create database statement
SHOW CREATE DATABASE mysql_db;

The query operation for external tables is consistent with that for normal tables.

SELECT * FROM demo_db.mysql_table LIMIT 10;

The delete operation for external database tables is consistent with that for normal tables.

• Delete external database

  warning

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

  DROP DATABASE mysql_db
  

• Delete external table

  DROP TABLE demo_db.mysql_table