MySQL table engine

The MySQL engine allows you to perform SELECT and INSERT queries on data that is stored on a remote MySQL server.

Creating a table

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = MySQL({host:port, database, table, user, password[, replace_query, on_duplicate_clause] | named_collection[, option=value [,..]]})
SETTINGS
    [ connection_pool_size=16, ]
    [ connection_max_tries=3, ]
    [ connection_wait_timeout=5, ]
    [ connection_auto_close=true, ]
    [ connect_timeout=10, ]
    [ read_write_timeout=300, ]
    [ enable_compression=false ]
;

See a detailed description of the CREATE TABLE query.

The table structure can differ from the original MySQL table structure:

• Column names should be the same as in the original MySQL table, but you can use just some of these columns and in any order.
• Column types may differ from those in the original MySQL table. ClickHouse tries to cast values to the ClickHouse data types.
• The external_table_functions_use_nulls setting defines how to handle Nullable columns. Default value: 1. If 0, the table function does not make Nullable columns and inserts default values instead of nulls. This is also applicable for NULL values inside arrays.

Engine Parameters

• host:port — MySQL server address.
• database — Remote database name.
• table — Remote table name, or a query passed to MySQL as is (see Passing a query instead of a table name).
• user — MySQL user.
• password — User password.
• replace_query — Flag that converts INSERT INTO queries to REPLACE INTO. If replace_query=1, the query is substituted.
• on_duplicate_clause — The ON DUPLICATE KEY on_duplicate_clause expression that is added to the INSERT query. Example: INSERT INTO t (c1,c2) VALUES ('a', 2) ON DUPLICATE KEY UPDATE c2 = c2 + 1, where on_duplicate_clause is UPDATE c2 = c2 + 1. See the MySQL documentation to find which on_duplicate_clause you can use with the ON DUPLICATE KEY clause. To specify on_duplicate_clause you need to pass 0 to the replace_query parameter. If you simultaneously pass replace_query = 1 and on_duplicate_clause, ClickHouse generates an exception.

Arguments also can be passed using named collections. In this case host and port should be specified separately. This approach is recommended for production environment.

Simple WHERE clauses such as =, !=, >, >=, <, <= are executed on the MySQL server.

The rest of the conditions and the LIMIT sampling constraint are executed in ClickHouse only after the query to MySQL finishes.

TLS/SSL

The credentials of an encrypted connection to MySQL are passed as named collection keys (or as key-value arguments):

The values are the contents of the corresponding PEM files, which can be copied into a named collection or into a query. They are masked in logs and in SHOW queries, the same way passwords are.

The same credentials can also be given as paths to files on the server, in ssl_ca, ssl_cert and ssl_key — but only in a named collection defined in the server configuration file, and such a value cannot be overridden in a query. The server opens those files with its own privileges, so accepting a path from SQL would let any user who is able to define a MySQL source probe the local filesystem, and authenticate with a certificate and key they are not allowed to read themselves.

<named_collections>
    <mysql_creds>
        <host>mysql-host</host>
        <port>3306</port>
        <user>mysql_user</user>
        <password>****</password>
        <ssl_ca>/etc/clickhouse-server/mysql-ca.crt</ssl_ca>
    </mysql_creds>
</named_collections>

Passing a query instead of a table name

Instead of a table name, the table argument can be a SELECT query that is passed to MySQL as is. The structure of the table is inferred from the query result. The query can be written either as a subquery, or wrapped into the query function:

CREATE TABLE mysql_table ENGINE = MySQL('localhost:3306', 'test', (SELECT a, b FROM t1 JOIN t2 USING (id) WHERE a > 0), 'user', 'password');
CREATE TABLE mysql_table ENGINE = MySQL('localhost:3306', 'test', query('SELECT a, b FROM t1 JOIN t2 USING (id) WHERE a > 0'), 'user', 'password');

This is useful to push down joins, aggregations or any other processing to MySQL. Such a table is read-only: INSERT into it is not allowed. The same syntax is supported by the mysql table function.

Supports multiple replicas that must be listed by |. For example:

CREATE TABLE test_replicas (id UInt32, name String, age UInt32, money UInt32) ENGINE = MySQL(`mysql{2|3|4}:3306`, 'clickhouse', 'test_replicas', 'root', 'clickhouse');

Usage example

Create table in MySQL:

mysql> CREATE TABLE `test`.`test` (
    ->   `int_id` INT NOT NULL AUTO_INCREMENT,
    ->   `int_nullable` INT NULL DEFAULT NULL,
    ->   `float` FLOAT NOT NULL,
    ->   `float_nullable` FLOAT NULL DEFAULT NULL,
    ->   PRIMARY KEY (`int_id`));
Query OK, 0 rows affected (0,09 sec)

mysql> insert into test (`int_id`, `float`) VALUES (1,2);
Query OK, 1 row affected (0,00 sec)

mysql> select * from test;
+------+----------+-----+----------+
| int_id | int_nullable | float | float_nullable |
+------+----------+-----+----------+
|      1 |         NULL |     2 |           NULL |
+------+----------+-----+----------+
1 row in set (0,00 sec)

Create table in ClickHouse using plain arguments:

CREATE TABLE mysql_table
(
    `float_nullable` Nullable(Float32),
    `int_id` Int32
)
ENGINE = MySQL('localhost:3306', 'test', 'test', 'bayonet', '123')

Or using named collections:

CREATE NAMED COLLECTION creds AS
        host = 'localhost',
        port = 3306,
        database = 'test',
        user = 'bayonet',
        password = '123';
CREATE TABLE mysql_table
(
    `float_nullable` Nullable(Float32),
    `int_id` Int32
)
ENGINE = MySQL(creds, table='test')

Retrieving data from MySQL table:

SELECT * FROM mysql_table

┌─float_nullable─┬─int_id─┐
│           ᴺᵁᴸᴸ │      1 │
└────────────────┴────────┘

Settings

Default settings are not very efficient, since they do not even reuse connections. These settings allow you to increase the number of queries run by the server per second.

connection_auto_close

Allows to automatically close the connection after query execution, i.e. disable connection reuse.

Possible values:

• 1 — Auto-close connection is allowed, so the connection reuse is disabled
• 0 — Auto-close connection is not allowed, so the connection reuse is enabled

Default value: 1.

connection_max_tries

Sets the number of retries for pool with failover.

Possible values:

• Positive integer.
• 0 — There are no retries for pool with failover.

Default value: 3.

connection_pool_size

Size of connection pool (if all connections are in use, the query will wait until some connection will be freed).

Possible values:

• Positive integer.

Default value: 16.

connection_wait_timeout

Timeout (in seconds) for waiting for free connection (in case of there is already connection_pool_size active connections), 0 - do not wait.

Possible values:

• Positive integer.

Default value: 5.

connect_timeout

Connect timeout (in seconds).

Possible values:

• Positive integer.

Default value: 10.

read_write_timeout

Read/write timeout (in seconds).

Possible values:

• Positive integer.

Default value: 300.

enable_compression

Enables compression for the MySQL protocol connection.

Default value: false.

This setting applies to:

• the MySQL table engine;
• the MySQL database engine;
• the mysql table function;
• named collections used by MySQL integrations.

When enabled, ClickHouse requests compression for the connection.

Example:

CREATE TABLE mysql_engine_compression
(
    id UInt32,
    name String,
    age UInt32,
    money UInt32
)
ENGINE = MySQL('mysql80:3306', 'clickhouse', 'test_table', 'root', 'password')
SETTINGS enable_compression = 1;

See also

• The mysql table function
• Using MySQL as a dictionary source