mysql 

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

Syntax

mysql('host:port', 'database', 'table', 'user', 'password'[, replace_query, 'on_duplicate_clause'])

Arguments

  • host:port — MySQL server address.

  • database — Remote database name.

  • table — Remote 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.

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.

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');
┌─int_id─┬─float─┐
│      1 │     2 │
└────────┴───────┘

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

See Also

Original article

Rating: 5 - 2 votes

Was this content helpful?
★★★★★