mysql - ClickHouse Documentation

Allows SELECT and INSERT queries to be performed on data that are stored on a remote MySQL server.

Syntax

mysql({host:port, database, table, user, password[, replace_query, on_duplicate_clause] | named_collection[, option=value [,..]]})

Arguments

Argument	Description
`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`. Possible values: - `0` - The query is executed as `INSERT INTO`. - `1` - The query is executed as `REPLACE INTO`.
`on_duplicate_clause`	The `ON DUPLICATE KEY on_duplicate_clause` expression that is added to the `INSERT` query. Can be specified only with `replace_query = 0` (if you simultaneously pass `replace_query = 1` and `on_duplicate_clause`, ClickHouse generates an exception). Example: `INSERT INTO t (c1,c2) VALUES ('a', 2) ON DUPLICATE KEY UPDATE c2 = c2 + 1;` `on_duplicate_clause` here is `UPDATE c2 = c2 + 1`. See the MySQL documentation to find which `on_duplicate_clause` you can use with the `ON DUPLICATE KEY` clause.

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 currently 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.

Passing a query instead of a table name

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

SELECT * FROM mysql('localhost:3306', 'test', (SELECT a, b FROM t1 JOIN t2 USING (id) WHERE a > 0), 'user', 'password');
SELECT * FROM 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 engine.

The subquery form (SELECT ...) is parsed by ClickHouse and re-serialized in the MySQL dialect (backtick identifier quoting) before being sent to the server. It must therefore be valid ClickHouse SQL. To pass MySQL-specific syntax that ClickHouse does not parse, use the query('...') form, whose text is sent to MySQL verbatim.Any outer WHERE, LIMIT, aggregation, etc. of the surrounding ClickHouse query is not pushed down into the passed query — it is applied in ClickHouse after the full query result is fetched. To restrict the data read from MySQL, put the filter inside the passed query. With external_table_strict_query = 1 an outer filter that cannot be pushed down is rejected with an exception instead of being applied locally.

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

SELECT name FROM mysql(`mysql{1|2|3}:3306`, 'mysql_database', 'mysql_table', 'user', 'password');

SELECT name FROM mysql(`mysql1:3306|mysql2:3306|mysql3:3306`, 'mysql_database', 'mysql_table', 'user', 'password');

Returned value

A table object with the same columns as the original MySQL table.

Some data types of MySQL can be mapped to different ClickHouse types - this is addressed by query-level setting mysql_datatypes_support_level

In the INSERT query to distinguish table function mysql(...) from table name with column names list, you must use keywords FUNCTION or TABLE FUNCTION. See examples below.

Examples

Table in MySQL:

mysql> CREATE TABLE `test`.`test` (
    ->   `int_id` INT NOT NULL AUTO_INCREMENT,
    ->   `float` FLOAT NOT NULL,
    ->   PRIMARY KEY (`int_id`));

mysql> INSERT INTO test (`int_id`, `float`) VALUES (1,2);

mysql> SELECT * FROM test;
+--------+-------+
| int_id | float |
+--------+-------+
|      1 |     2 |
+--------+-------+

Selecting data from ClickHouse:

SELECT * FROM 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';
SELECT * FROM mysql(creds, table='test');

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

`enable_compression`

Enables compression for the MySQL protocol connection. Default value: false. This setting applies to:

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

When enabled, ClickHouse requests compression for the connection. Example:

SELECT *
FROM mysql(
    'mysql80:3306',
    'clickhouse',
    'test_table',
    'root',
    'password',
    SETTINGS enable_compression = 1
);

Replacing and inserting:

INSERT INTO FUNCTION mysql('localhost:3306', 'test', 'test', 'bayonet', '123', 1) (int_id, float) VALUES (1, 3);
INSERT INTO TABLE FUNCTION mysql('localhost:3306', 'test', 'test', 'bayonet', '123', 0, 'UPDATE int_id = int_id + 1') (int_id, float) VALUES (1, 4);
SELECT * FROM mysql('localhost:3306', 'test', 'test', 'bayonet', '123');

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

Copying data from MySQL table into ClickHouse table:

CREATE TABLE mysql_copy
(
   `id` UInt64,
   `datetime` DateTime('UTC'),
   `description` String,
)
ENGINE = MergeTree
ORDER BY (id,datetime);

INSERT INTO mysql_copy
SELECT * FROM mysql('host:port', 'database', 'table', 'user', 'password');

Or if copying only an incremental batch from MySQL based on the max current id:

INSERT INTO mysql_copy
SELECT * FROM mysql('host:port', 'database', 'table', 'user', 'password')
WHERE id > (SELECT max(id) FROM mysql_copy);

​Syntax

​Arguments

​Passing a query instead of a table name

​Returned value

​Examples

​enable_compression

​Related

Syntax

Arguments

Passing a query instead of a table name

Returned value

Examples

`enable_compression`

Related