MySQL

Allows to connect to databases on a remote MySQL server and perform INSERT and SELECT queries to exchange data between ClickHouse and MySQL.

The MySQL database engine translate queries to the MySQL server so you can perform operations such as SHOW TABLES or SHOW CREATE TABLE.

You cannot perform the following queries:

• RENAME
• CREATE TABLE
• ALTER

Creating a database

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MySQL('host:port', ['database' | database], 'user', 'password')
[SETTINGS enable_compression=0]

Engine Parameters

• host:port — MySQL server address.
• database — Remote database name.
• user — MySQL user.
• password — User password.

Settings

enable_compression

Enables zlib compression for the MySQL protocol connection. When set to 1, ClickHouse requests protocol-level compression from the MySQL server.

Default value: 0.

Example:

CREATE DATABASE mysql_db
ENGINE = MySQL('localhost:3306', 'test', 'my_user', 'user_password')
SETTINGS enable_compression = 1;

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.

Data types support

The conversion of the spatial types (other than POINT, which is always converted) is controlled by the geometry flag of the mysql_datatypes_support_level setting, enabled by default. The generic GEOMETRY column type is mapped to the umbrella Geometry type (a Variant over the concrete geometric types). Because such a column can hold a value of any subtype, reading a value whose subtype has no ClickHouse counterpart (GEOMETRYCOLLECTION) throws an exception at read time; this incompatibility is accepted in exchange for a proper geometric type. Columns declared with the GEOMETRYCOLLECTION type are converted into String like all other MySQL data types.

Nullable is supported. A spatial column maps to String (Nullable(String) if it is nullable) instead of a geometric type in three cases: it is declared GEOMETRYCOLLECTION; the geometry flag is disabled and the type is not POINT; or the column is nullable and the type is not POINT, since Point is the only geometric type that can be nested inside Nullable. In all three the string holds the value exactly as MySQL returns it: a 4-byte SRID prefix followed by the WKB payload, so strip those 4 leading bytes before passing it to a WKB decoder.

Global variables support

For better compatibility you may address global variables in MySQL style, as @@identifier.

These variables are supported:

• version
• max_allowed_packet

Example:

SELECT @@version;

Examples of use

Table in MySQL:

mysql> USE test;
Database changed

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

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

mysql> select * from mysql_table;
+------+-----+
| int_id | value |
+------+-----+
|      1 |     2 |
+------+-----+
1 row in set (0,00 sec)

Database in ClickHouse, exchanging data with the MySQL server:

CREATE DATABASE mysql_db ENGINE = MySQL('localhost:3306', 'test', 'my_user', 'user_password') SETTINGS read_write_timeout=10000, connect_timeout=100;

SHOW DATABASES

┌─name─────┐
│ default  │
│ mysql_db │
│ system   │
└──────────┘

SHOW TABLES FROM mysql_db

┌─name─────────┐
│  mysql_table │
└──────────────┘

SELECT * FROM mysql_db.mysql_table

┌─int_id─┬─value─┐
│      1 │     2 │
└────────┴───────┘

INSERT INTO mysql_db.mysql_table VALUES (3,4)

SELECT * FROM mysql_db.mysql_table

┌─int_id─┬─value─┐
│      1 │     2 │
│      3 │     4 │
└────────┴───────┘