一个计算机技术爱好者与学习者

0%

ClickHouse入门篇

1. ClickHouse简介

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

参考文档:

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语句的。

参考文档:

3. 安装ClickHouse服务端

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

4. 安装ClickHouse客户端

4.1. dbeaver

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

4.2. clickhouse-client

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

安装方法参考ClickHouse - 安装

4.2.1. centos7中安装

安装:

1
2
3
4
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

测试:

1
2
3
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中安装

安装:

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

测试:

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

5. 使用ClickHouse

5.1. 使用分布式表

1、新建分布式表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 查看宏变量
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、增删查改

1
2
3
4
5
6
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就不擅长做数据更新删除操作。

参考文档:

6.1. mutations_sync

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

python客户端连接参数:

1
2
3
4
5
6
7
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时,老的数据才会被删除。

1
2
3
4
5
6
7
8
9
10
11
12
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官方工具

1
2
3
4
5
6
7
8
9
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'
);

参考文档:

注意:官方文档中的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中。

参考文档:

7.4. 阿里云工具

参考文档:

  • 本文作者: 好好学习的郝
  • 原文链接: https://www.voidking.com/dev-clickhouse-start/
  • 版权声明: 本文采用 BY-NC-SA 许可协议,转载请注明出处!源站会即时更新知识点并修正错误,欢迎访问~
  • 微信公众号同步更新,欢迎关注~