ClickHouse入门篇

1. ClickHouse简介

ClickHouse 是一个用于联机分析(OLAP)的列式数据库管理系统(DBMS)。

参考文档：

• GitHub - ClickHouse
• 什么是ClickHouse？
• ClickHouse深度揭秘

2. ClickHouse基础概念

在clickhouse集群中，clickhouse的表有两种类型：一种是本地表，一种是分布式表。
在单机情况下，使用本地表就可以了；在集群情况下，一般使用分布式表。
ch的单机和分布式的增删查改差异很大，使用的时候需要注意。
分布式表首先需要在集群所有节点上创建本地表，然后在本地表的基础上创建分布式表（本质是分布式视图），通过分布式表引擎聚合本地表引擎的数据。

一般写入使用本地表，查询使用分布式表。

写分布式表的优点：可以让ClickHouse控制数据到分片的路由。
写分布式表的缺点：

• 数据是先写到一个分布式表的实例中并缓存起来，再逐渐分发到各个分片上去，实际是双写了数据（写入放大），浪费资源；
• 数据写入默认是异步的，短时间内可能造成不一致；
• 目标表中会产生较多的小parts，使merge（即compaction）过程压力增大。

写本地表优点：同步操作，更快，parts的大小也比较合适。
写本地表缺点：要求应用层额外实现sharding和路由逻辑，如轮询或者随机等。

表引擎特殊说明：

• 20版本的ch只有使用了replicated开头的engine的引擎的表，才能够在拥有on cluster xxx条件的ddl语句中进行集群更新；其他engine的表，只能够每个node进行update；21版本修复了这个bug。
• 目前阿里云20版本的ch的mergeTree引擎是支持on cluster xxx这样的ddl语句的。

参考文档：

• ClickHouse - 基本概念
• ClickHouse创建分布式表
• ClickHouse常见问题
• 「Clickhouse系列」分布式表&本地表详解
• CK 分布式表和本地表
• clickhouse单机的增删查询实现方案和clickhouse分布式部署的增删查改实现方案
• ClickHouse - Data Replication
• clickhouse删除ReplicatedMergeTree复制表后，新建同名表失败原因分析

3. 安装ClickHouse服务端

参考文档《K8S中安装配置ClickHouse》

4. 安装ClickHouse客户端

4.1. dbeaver

使用dbeaver，可以图形化连接clickhouse。
安装方法：下载安装包，双击安装。

4.2. clickhouse-client

使用clickhouse-client，可以命令行连接clickhouse。

安装方法参考ClickHouse - 安装

4.2.1. centos7中安装

安装：

wget https://packages.clickhouse.com/rpm/stable/clickhouse-common-static-21.8.15.7-2.x86_64.rpm
wget https://packages.clickhouse.com/rpm/stable/clickhouse-client-21.8.15.7-2.noarch.rpm
rpm -ivh clickhouse-common-static-21.8.15.7-2.x86_64.rpm
rpm -ivh clickhouse-client-21.8.15.7-2.noarch.rpm

测试：

clickhouse-client -h 192.168.56.101 --port 9000 \
-u default --password xxx \
--query "select hostName()"

如果执行报错：Code: 102. DB::NetException: Unexpected packet from server 192.168.56.101:9000 (expected Hello or Exception, got Unknown packet). (UNEXPECTED_PACKET_FROM_SERVER)

请检查clickhouse服务端端口是否正确，改成正确的端口。

4.2.2. macos中安装

安装：

curl -O 'https://builds.clickhouse.com/master/macos/clickhouse' && chmod a+x ./clickhouse

测试：

./clickhouse client -h 192.168.56.101 --port 9000 \
-u default --password xxx \
--query "select hostName()"

5. 使用ClickHouse

5.1. 使用分布式表

1、新建分布式表

-- 查看宏变量
select * from `system`.macros;

-- 查看cluster
select * from `system`.clusters;

-- 创建本地表
-- create table `default`.optest on cluster cluster (a UInt64) Engine = MergeTree() order by tuple();

create table `default`.optest on cluster cluster (a UInt64) Engine = ReplicatedMergeTree('/clickhouse/tables/{shard}/default.optest','{replica}') order by tuple();

-- 创建本地表对应的分布式表
create table `default`.optest_dis on cluster cluster as optest Engine = Distributed(cluster, default, optest, cityHash64(a));

-- 删除本地表
drop table `default`.optest on cluster cluster;
-- 要等待480s才会删除zk中的数据

order by指定的字段是对分区内数据进行排序，在ReplicatedMergeTree引擎下，会对相邻的重复数据进行删除。

2、增删查改

INSERT INTO `default`.optest(a) values(10);
SELECT * FROM `default`.optest_dis;
ALTER TABLE `default`.optest  on cluster cluster UPDATE `a` = '11' WHERE `a` = '10';
SELECT * FROM `default`.optest_dis;
ALTER TABLE `default`.optest on cluster cluster DELETE WHERE `a`='11';
SELECT * FROM `default`.optest_dis;

6. 更新延迟处理

从使用场景来说，Clickhouse是个分析型数据库。这种场景下，数据一般是不变的，因此Clickhouse对update、delete的支持是比较弱的，实际上并不支持标准的update、delete操作。

Clickhouse通过alter方式实现更新、删除，它把update、delete操作叫做mutation(突变)。

标准SQL的更新、删除操作是同步的，即客户端要等服务端反回执行结果（通常是int值）；而Clickhouse的update、delete是通过异步方式实现的，当执行update语句时，服务端立即反回，但是实际上此时数据还没变，而是排队等着。

ClickHouse本身对update的执行是低效的，因为ClickHouse的MergeTree存储一旦生成一个Data Part，这个Part就不支持更改，而是需要删除旧Part, 重写整个Part。所以从MergeTree存储内核层面，ClickHouse就不擅长做数据更新删除操作。

参考文档：

• ClickHouse多种实时更新方法总结
• clickhouse数据实时更新实现的三种方式
• Clickhouse中update/delete的使用

6.1. mutations_sync

使用mutations_sync参数，是最简单的方法。
方法一：客户端和clickhouse建立连接时，添加参数mutations_sync=2
方法二：执行sql时，添加参数settings mutations_sync=2
方法三：修改clickhouse服务端配置，设置mutations_sync=2

python客户端连接参数：

ck_config = {
  "host": ck_host, 
  "port": ck_port, 
  "user": ck_user, 
  "password": ck_pwd, 
  "settings": {"mutations_sync": "2"}
}

但是，如果存在大量更新，这种方法会大量重建Part，效率会很低。

6.2. ReplacingMergeTree

ReplacingMergeTree + INSERT + Final 可以实现虚拟更新，以insert代替alter操作，每次select时都拉取最新一条数据。
当执行optimize时，老的数据才会被删除。

CREATE TABLE tb_test_replacing(
ts DateTime,
uid String,
biz String
) ENGINE = ReplacingMergeTree(ts) ORDER BY (ts) SETTINGS index_granularity = 8192;

INSERT INTO tb_test_replacing VALUES ('2019-06-07 20:01:01', 'c', 'c1');
INSERT INTO tb_test_replacing VALUES ('2019-06-07 20:01:01', 'c', 'c2');
SELECT * FROM tb_test_replacing;
SELECT * FROM tb_test_replacing FINAL;
optimize table tb_test_replacing;
SELECT * FROM tb_test_replacing;

7. 从MySQL同步数据到ClickHouse

7.1. ClickHouse官方工具

set allow_experimental_database_materialize_mysql = 1;
select value from system.settings where name = 'allow_experimental_database_materialize_mysql';
CREATE DATABASE db1_mysql
ENGINE = MaterializeMySQL(
  'mysql-host.domain.com:3306',
  'db1',
  'clickhouse_user',
  'ClickHouse_123'
);

参考文档：

• Replicate a MySQL Database in ClickHouse
• Integrating MySQL with ClickHouse
• [experimental] MaterializedMySQL
• MaterializeMySQL引擎
• ClickHouse和他的朋友们（9）MySQL实时复制与实现

注意：官方文档中的materialized，需要改成materialize。

7.2. MaterializeMySQL同步原理（ChatGPT）

使用 MaterializeMySQL 引擎同步数据时，首先会进行一次全量同步，然后再进行增量同步。

7.2.1. 全量同步

全量同步的过程：

1、建立连接：首先需要建立到 MySQL 服务器的连接，提供主机名、端口、用户名和密码。

2、创建数据库：在 ClickHouse 中创建一个使用 MaterializeMySQL 引擎的数据库。这将自动触发全量同步过程。

3、获取数据表结构：ClickHouse 会从 MySQL 数据库获取表的结构信息，包括列名、数据类型等，并在 ClickHouse 中创建相应的表结构。

4、导出数据：ClickHouse 会从 MySQL 数据库导出所有表的数据。这一过程通常使用 mysqldump 工具或类似方法来完成。

5、导入数据：将导出的数据导入到 ClickHouse 中相应的表中。在这个过程中，数据可能需要进行转换，以便与 ClickHouse 的数据格式相匹配。

6、启动增量同步：完成全量同步后，ClickHouse 会自动开始监听 MySQL 服务器的二进制日志，以实时捕获和应用数据更改。

7.2.2. 增量同步

使用ClickHouse的MaterializeMySQL引擎增量同步数据的原理主要基于MySQL的二进制日志（Binary Log，简称binlog）。二进制日志是MySQL用于记录所有更改（如数据修改和表结构变更）的日志文件。binlog是一种行级别的日志记录格式，包含足够的信息来重放所有已执行的数据更改。

以下是MaterializeMySQL引擎同步数据的主要步骤：

1、连接到 MySQL：首先，ClickHouse 需要连接到 MySQL 服务器。为此，需要提供 MySQL 服务器的连接信息，如主机名、端口、用户名和密码。

2、读取 binlog：ClickHouse 从 MySQL 服务器读取二进制日志，这些日志记录了数据库中的所有更改。ClickHouse 使用 mysqlbinlog 工具解析二进制日志文件。

3、解析更改：ClickHouse 解析二进制日志中的事件，如插入、更新、删除操作。这些事件将被转换为 ClickHouse 可以理解的格式。

4、应用更改：根据解析的更改事件，ClickHouse 更新其数据库。此过程涉及将更改应用到相应的表和列中。

5、实时同步：MaterializeMySQL 引擎会持续监听 MySQL 服务器的二进制日志，当 MySQL 数据库中发生数据更改事件时，ClickHouse 会立即捕获这些事件并应用相应的更改。

7.3. clickhouse-client

clickhouse-client支持通过输入数据文件、管道、JDBC等方式导入数据。可以将MySQL中的数据导出成csv文件，然后使用clickhouse-client导入到ClickHouse中。

参考文档：

• Command-Line Client

7.4. 阿里云工具

参考文档：

• 阿里云ClickHouse - 从MySQL导入数据
• 阿里云ClickHouse - 从RDS MySQL同步