MySQL database engine

Not supported in ClickHouse Cloud

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;

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 (MULTIPOINT, GEOMETRYCOLLECTION) throws an exception at read time; this incompatibility is accepted in exchange for a proper geometric type. Columns declared with the MULTIPOINT or 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

Note

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