ClickHouse（五）ClickHouse整合其它存储系统

1.ClickHouse整合MySQL

1.1.连接创建

MySQL引擎用于将远程的MySQL服务器中的表映射到ClickHouse中，并允许您对表进行INSERT和SELECT查询，以方便您在ClickHouse与MySQL之间进行数据交换。ClickHouse的MySQL引擎可以对存储在远程MySQL服务器上的数据执行SELECT查询。这个模式类似于Hive的外部表。

官网链接：https://clickhouse.tech/docs/en/engines/table-engines/integrations/mysql/

MySQL引擎：https://clickhouse.tech/docs/zh/engines/database-engines/mysql/

语法示例：

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MySQL('host:port', ['database' | database], 'user', 'password')

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
    ...
) ENGINE = MySQL('host:port', 'database', 'table', 'user', 'password' [,replace_query,'on_duplicate_clause']);

• host:port — MySQL 服务器地址。
• database — 数据库的名称。
• table — 表名称。
• user — 数据库用户。
• password — 用户密码。
• replace_query — 将 INSERT INTO 查询是否替换为 REPLACE INTO 的标志。如果 replace_query=1，则替换查询
• 'on_duplicate_clause' — 将 ON DUPLICATE KEY UPDATE 'on_duplicate_clause' 表达式添加到 INSERT 查询语句中。例如：impression = VALUES(impression) + impression。如果需要指定 'on_duplicate_clause'，则需要设置 replace_query=0。如果同时设置 replace_query = 1 和 'on_duplicate_clause'，则会抛出异常。

注意：

1、此时，简单的 WHERE 子句（例如 =, !=, >, >=, <, <=）是在 MySQL 服务器上执行。

2、其余条件以及 LIMIT 采样约束语句仅在对MySQL的查询完成后才在 ClickHouse中执行。

3、ClickHouse引擎不支持Nullable数据类型，因此，当从MySQL表中读取数据时，NULL将转换为指定列类型的默认值（通常为0或空字符串）。

1.2.映射方式

1.2.1.库的映射

如果这个库里面需要有大量的表需要映射到clickhouse做分析，可以不用移动数据，直接映射就可以执行分析（不推荐使用）

-- 关联MySQL库：
drop database if exists mydb;
create database if not exists mydb ENGINE = MySQL('hadoop2:3306', 'mydb','root', '123456');
-- 查看库和表列表：
show databases;
show tables from mydb;
-- 查询数据：
select * from mydb.testck;

1.2.2.表的映射

drop table if exists testck;
CREATE TABLE mydb.testck
(
    id  int,
    name String
) ENGINE = MySQL('hadoop2:3306', 'mydb', 'testck', 'root', '123456');

注意：当mysql或者ClickHouse数据有修改时会同步到另一端。当向ck中插入数据有主键重复也会直接报错。

1.2.3.IS语法和CTAS语法

IS语法：-- insert into ... select ... 语法

--数据迁移：从mysql中，把数据弄到clickhouse中
insert into mydb.testck2 select * from mysql('hadoop2:3306', 'mydb','testck2', 'root', '123456');
select * from mydb.testck2; //从clickhouse中查询出来的

CTAS语法：-- create table ... as select ... 语法

drop table if exists mydb.testck3;
create table if not exists mydb.testck3 engine = Log as select * from MySQL('hadoop2:3306', 'mytable','root', '123456');
select * from mydb.testck3;

注意：这样的方法不会修改原始数据，但对mysql修改的数据也不会实时同步。

2.ClickHouse整合HDFS

从ClickHouse18.16.0版本开始支持从HDFS读文件，在19.1.6版本对HDFS访问功能进行了增强，支持读和写，在19.4版本以后开始支持Parquet格式。

先在HDFS上准备一个数据文件：student.csv CSV格式的数据

数据如下：

95002,刘晨,女,19,IS
95017,王风娟,女,18,IS
95018,王一,女,19,IS
95013,冯伟,男,21,CS
95014,王小丽,女,19,CS
95019,邢小丽,女,19,IS
95020,赵钱,男,21,IS
95003,王敏,女,22,MA
95004,张立,男,19,IS
95012,孙花,女,20,CS
95010,孔小涛,男,19,CS
95005,刘刚,男,18,MA
95006,孙庆,男,23,CS
95007,易思玲,女,19,MA
95008,李娜,女,18,CS
95021,周二,男,17,MA
95022,郑明,男,20,MA
95001,李勇,男,20,CS
95011,包小柏,男,18,MA
95009,梦圆圆,女,18,MA
95015,王君,男,18,MA
95016,钱国,男,21,MA

上传到HDFS：hadoop fs -put student.csv /clickhouse_data/student/

2.1.从HDFS读取数据

从HDFS上读取数据类似于将HDFS作为外部存储，然后去拉取HDFS上的数据。所以这种模式，肯定要比直接从 ClickHouse 中读取数据要慢的多。

ClickHouse 建表语句：

create database if not exists mydb;
use mydb;
drop table if exists mydb.testck4_csv;
create table if not exists  mydb.testck4_csv(
    id Int8,
    name String,
    sex String,
    age Int8,
    department String
) Engine = HDFS('hdfs://hadoop1:9000/stu*.csv','CSV');

注意：

1. 支持CSV, TSV, Parquet 等格式，注意CSV是大写的！

2. 执行并行读写操作（Reads and writes can be parallel）

3. 字符串中的是否有引号，都能被自动解析，有引号也行，没有引号也行。如果需要关联多个文件，ClickHouse虽然不支持直接关联文件夹，但是对于文件路径还是提供给了

4. 多个支持，具体可以参照官网：https://clickhouse.tech/docs/en/engines/table-engines/integrations/hdfs/

5. 支持虚拟列 _path 和 _file，分别代表文件路径，和文件名。

6. 不支持的操作：alter 和 select ... sample 语法， Indexes 语法，和 Replication 操作

2.2.从HDFS导入数据

先在ClickHouse中创建一张表testck5_csv:

create database if not exists mydb;
use mydb;
drop table if exists mydb.testck5_csv;
create table if not exists testck5_csv(
    id Int8,
    name String,
    sex String,
    age Int8,
    department String
) engine = TinyLog;

从 HDFS 上导入数据到 ClickHouse：

insert into mydb.testck5_csv select * from hdfs('hdfs://hadoop1:9000/student.csv', 'CSV', 'id Int8, name String, sex String, age Int8, department String');

详细语法可以参考官网：https://clickhouse.tech/docs/zh/sql-reference/table-functions/hdfs/