ClickHouse入门篇

ClickHouse简介

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

参考文档：

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

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复制表后，新建同名表失败原因分析

安装ClickHouse

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

使用ClickHouse

客户端

使用dbeaver，可以图形化连接clickhouse。
使用clickhouse-client，可以命令行连接clickhouse。

使用分布式表

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;

更新延迟处理

从使用场景来说，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的使用

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，效率会很低。

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;

从MySQL同步数据到ClickHouse

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
• MaterializeMySQL引擎

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

clickhouse-client

【使用ClickHouse】一节提到，clickhouse-client是一个命令行工具，支持通过输入数据文件、管道、JDBC等方式导入数据。可以将MySQL中的数据导出成csv文件，然后使用clickhouse-client导入到ClickHouse中。

参考文档：

• Command-Line Client

阿里云工具

参考文档：

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