mysql - ClickHouse Documentation

允许对存储在远程 MySQL 服务器中的数据执行 SELECT 和 INSERT 查询。

语法

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

参数

参数	描述
`host:port`	MySQL 服务器地址。
`database`	远程数据库名称。
`table`	远程表名，或原样传递给 MySQL 的查询 (请参见传递查询而不是表名) 。
`user`	MySQL 用户。
`password`	用户密码。
`replace_query`	用于将 `INSERT INTO` 查询转换为 `REPLACE INTO` 的标志。可能的值： - `0` - 以 `INSERT INTO` 执行该查询。 - `1` - 以 `REPLACE INTO` 执行该查询。
`on_duplicate_clause`	添加到 `INSERT` 查询中的 `ON DUPLICATE KEY on_duplicate_clause` 表达式。只能在 `replace_query = 0` 时指定 (如果同时传入 `replace_query = 1` 和 `on_duplicate_clause`，ClickHouse 会抛出异常) 。示例：`INSERT INTO t (c1,c2) VALUES ('a', 2) ON DUPLICATE KEY UPDATE c2 = c2 + 1;` 此处的 `on_duplicate_clause` 为 `UPDATE c2 = c2 + 1`。请参阅 MySQL 文档，了解 `ON DUPLICATE KEY` 子句支持哪些 `on_duplicate_clause`。

参数也可以通过命名集合传递。在这种情况下，需要分别指定 host 和 port。这种方式推荐在生产环境中使用。目前，诸如 =, !=, >, >=, <, <= 这样的简单 WHERE 子句会在 MySQL 服务器上执行。其余条件以及 LIMIT 采样约束，只会在对 MySQL 的查询完成后由 ClickHouse 执行。

传递查询而非表名

第三个参数除了表名外，也可以是一个原样传递给 MySQL 的 SELECT 查询。返回表的结构会根据查询结果自动推断。该查询既可以写成子查询，也可以封装在 query 函数中：

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

这对于将 joins、聚合或其他任何处理下推到 MySQL 很有用。此类表为只读：不允许对其执行 INSERT。MySQL 表引擎也支持相同的语法。

子查询形式 (SELECT ...) 会先由 ClickHouse 解析，再在发送到 server 之前按 MySQL 方言 (使用反引号引用标识符) 重新序列化。因此，它必须是有效的 ClickHouse SQL。若要传递 ClickHouse 无法解析的 MySQL 特定语法，请使用 query('...') 形式，其文本会原样发送给 MySQL。外层 ClickHouse 查询中的任何 WHERE、LIMIT、聚合等都不会下推到传入的查询中，而是在拉取完整查询结果后由 ClickHouse 应用。要限制从 MySQL 读取的数据，请将过滤条件放在传入的查询内部。启用 external_table_strict_query = 1 时，无法下推的外层过滤器会直接抛出异常，而不是在本地应用。

支持多个副本，必须使用 | 分隔列出。例如：

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

返回值

一个表对象，其列与原始 MySQL 表的列相同。

MySQL 的某些数据类型可映射为不同的 ClickHouse 类型——可通过查询级设置 mysql_datatypes_support_level 来处理这一点。

在 INSERT 查询中，为了将表函数 mysql(...) 与带列名列表的表名区分开来，必须使用关键字 FUNCTION 或 TABLE FUNCTION。请参见下面的示例。

示例

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 |
+--------+-------+

从 ClickHouse 查询数据：

SELECT * FROM mysql('localhost:3306', 'test', 'test', 'bayonet', '123');

或者使用命名集合：

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`

为 MySQL 协议连接启用压缩。默认值：false。此设置适用于：

mysql 表函数；
MySQL 表引擎；
MySQL 数据库引擎；
用于 MySQL 集成的命名集合。

启用后，ClickHouse 会为该连接请求压缩。示例：

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

替换与插入：

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

将 MySQL 表中的数据复制到 ClickHouse 表中：

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

或者，如果只想根据当前的最大 ID 从 MySQL 复制一个增量批次：

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

​语法

​参数

​传递查询而非表名

​返回值

​示例

​enable_compression

​相关

语法

参数

传递查询而非表名

返回值

示例

`enable_compression`

相关