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):

Parameter	Description
`ssl_ca_pem`	Contents of the CA certificate that the MySQL server certificate is verified against.
`ssl_cert_pem`	Contents of the client certificate, for certificate-based authentication.
`ssl_key_pem`	Contents of the private key belonging to `ssl_cert_pem`.

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

MySQL	ClickHouse
UNSIGNED TINYINT	UInt8
TINYINT	Int8
UNSIGNED SMALLINT	UInt16
SMALLINT	Int16
UNSIGNED INT, UNSIGNED MEDIUMINT	UInt32
INT, MEDIUMINT	Int32
UNSIGNED BIGINT	UInt64
BIGINT	Int64
FLOAT	Float32
DOUBLE	Float64
DATE	Date
DATETIME, TIMESTAMP	DateTime
BINARY	FixedString
POINT	Point
LINESTRING	LineString
POLYGON	Polygon
MULTILINESTRING	MultiLineString
MULTIPOLYGON	MultiPolygon
GEOMETRY	Geometry

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 (the raw WKB) like all other MySQL data types. Nullable is supported.

Global variables support

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

version
max_allowed_packet

By now these variables are stubs and don’t correspond to anything.

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 │
└────────┴───────┘

SQL Reference

Data Types

Engines

Functions

Formats

Settings

System Tables

Data Lakes

Creating a database

`enable_compression`

TLS/SSL

Data types support

Global variables support

Examples of use

​Creating a database

​enable_compression

​TLS/SSL

​Data types support

​Global variables support

​Examples of use

Creating a database

`enable_compression`

TLS/SSL

Data types support

Global variables support

Examples of use