mysql

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

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.

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.

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.

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');

or

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.

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

Related

• The ‘MySQL’ table engine
• Using MySQL as a dictionary source
• mysql_datatypes_support_level
• mysql_map_fixed_string_to_text_in_show_columns
• mysql_map_string_to_text_in_show_columns
• mysql_max_rows_to_insert