ClickHouse

• 学习资料

• 1.ClickHouse中文官网：https://clickhouse.com/docs/zh
• 2.ClickHouse官网https://clickhouse.com/
• 3.ClickHouse GitHub https://github.com/ClickHouse/ClickHouse
• 4.DBeaver 地址 https://dbeaver.io/download/

• 安装部署

拉取服务端镜像：docker pull yandex/clickhouse-server
拉取客户端端镜像： docker pull yandex/clickhouse-client
启动Server端：docker run -d --name clickhouse-server --ulimit nofile=262144:262144 yandex/clickhouse-server
启动Client并连接到Server端：docker run -it --rm --link clickhouse-server:clickhouse-server yandex/clickhouse-client --host clickhouse-server
客户端常用参数：
clickhouse-client
--host, -h ：服务端host名称，默认 localhost
--port ：连接端口，默认9000
--user, -u ：用户名，默认 default
--password ：密码，默认空
--query, -q ：非交互模式下的查询语句
--database, -d ：默认当前操作的数据库，默认default
--multiline, -m ：允许多行语句查询，在clickhouse中默认回车即为sql结束，可使用该参数多行输入
--format, -f ：使用指定的默认格式输出结果 csv,以逗号分隔
--time, -t ：非交互模式下会打印查询执行的时间
--stacktrace ：出现异常会打印堆栈跟踪信息
--config-file ：配置文件名称
停止clickhouse-server

wudy.yu@wudyyudeMacBook-Pro ~ % docker stop clickhouse-server
clickhouse-server

指定端口、挂载目录启动

docker run -d --name clickhouse-server --ulimit nofile=262144:262144  -p 8123:8123 -p 9000:9000 -p 9009:9009 --privileged=true -v  /Users/wudy.yu/docker-volumn/clickhouse/log:/var/log/clickhouse-server -v /Users/wudy.yu/docker-volumn/clickhouse/data:/var/lib/clickhouse   yandex/clickhouse-server

• 基本操作

1.创建数据库

ec430d220d73 :) CREATE  DATABASE wudy

CREATE DATABASE wudy

Query id: d7f76e59-3fb6-4404-b482-f69657d3756f

Ok.

0 rows in set. Elapsed: 0.055 sec. 

ec430d220d73 :) show databases

SHOW DATABASES

Query id: 0e8ac65d-f551-41a8-a816-960edfd0d7f9

┌─name───────────────┐
│ INFORMATION_SCHEMA │
│ default            │
│ information_schema │
│ system             │
│ wudy               │
└────────────────────┘

5 rows in set. Elapsed: 0.026 sec. 

2.创建数据表

CREATE TABLE wudy.clickstream
(
    `customer_id` String,
    `time_stamp` Date,
    `click_event_type` String,
    `page_code` FixedString(20),
    `source_id` UInt64
)
ENGINE = MergeTree() ORDER BY time_stamp

Query id: f8699293-3182-4d82-bb6b-557270d4de28

Ok.

0 rows in set. Elapsed: 0.064 sec. 

3.插入数据

ec430d220d73 :) INSERT INTO wudy.clickstream VALUES('wudy.yu', '2023-07-09', 'create_order', 'home_enter', 800820082)

INSERT INTO wudy.clickstream FORMAT Values

Query id: fad68be9-39da-4076-a493-f5fedfeebbea

Ok.

1 rows in set. Elapsed: 0.110 sec. 

ec430d220d73 :) INSERT INTO wudy.clickstream VALUES('peter.li', '2023-07-10', 'add_to_cart', 'home_enter', 812820097)

INSERT INTO wudy.clickstream FORMAT Values

Query id: bb05cff2-0fd6-4aaa-8864-ce1023ab9455

Ok.

1 rows in set. Elapsed: 0.074 sec. 
ec430d220d73 :) INSERT INTO wudy.clickstream VALUES('timo.peng', '2023-12-12', 'add_to_cart', 'product_center', 832821831)

INSERT INTO wudy.clickstream FORMAT Values

Query id: d5789224-720d-4db0-b357-cb786e3e85c3

Ok.

1 rows in set. Elapsed: 0.051 sec. 

ec430d220d73 :) INSERT INTO wudy.clickstream VALUES('tony.zhang', '2023-11-11', 'create_order', 'order_center', 812651931)

INSERT INTO wudy.clickstream FORMAT Values

Query id: ccd03fef-67c8-47d3-9d58-0496b75a96d6

Ok.

1 rows in set. Elapsed: 0.055 sec. 

4.查询数据

ec430d220d73 :) SELECT * FROM wudy.clickstream WHERE time_stamp > '2023-01-01'

SELECT *
FROM wudy.clickstream
WHERE time_stamp > '2023-01-01'

Query id: 3443d7f9-d7cc-4a50-96ad-2212981c0ba4

┌─customer_id─┬─time_stamp─┬─click_event_type─┬─page_code──┬─source_id─┐
│ peter.li    │ 2023-07-10 │ add_to_cart      │ home_enter │ 812820097 │
└─────────────┴────────────┴──────────────────┴────────────┴───────────┘
┌─customer_id─┬─time_stamp─┬─click_event_type─┬─page_code────┬─source_id─┐
│ tony.zhang  │ 2023-11-11 │ create_order     │ order_center │ 812651931 │
└─────────────┴────────────┴──────────────────┴──────────────┴───────────┘
┌─customer_id─┬─time_stamp─┬─click_event_type─┬─page_code──────┬─source_id─┐
│ timo.peng   │ 2023-12-12 │ add_to_cart      │ product_center │ 832821831 │
└─────────────┴────────────┴──────────────────┴────────────────┴───────────┘
┌─customer_id─┬─time_stamp─┬─click_event_type─┬─page_code──┬─source_id─┐
│ wudy.yu     │ 2023-07-09 │ create_order     │ home_enter │ 800820082 │
└─────────────┴────────────┴──────────────────┴────────────┴───────────┘

4 rows in set. Elapsed: 0.095 sec. 

ec430d220d73 :) DESCRIBE wudy.clickstream

DESCRIBE TABLE  wudy.clickstream

Query id: 460a3ee3-433d-4833-b934-5c6a796ec7ea

┌─name─────────────┬─type────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ customer_id      │ String          │              │                    │         │                  │                │
│ time_stamp       │ Date            │              │                    │         │                  │                │
│ click_event_type │ String          │              │                    │         │                  │                │
│ page_code        │ FixedString(20) │              │                    │         │                  │                │
│ source_id        │ UInt64          │              │                    │         │                  │                │
└──────────────────┴─────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

5 rows in set. Elapsed: 0.019 sec. 

// 操作billFlow表
CREATE TABLE wudy.billflow
(
    `id` Int64,
    `batch_no` UUID,
    `project_id` String,
    `status` Enum8('unpaid' = 1, 'frozen' = 2, 'paid' = 3),
    `create_time` DateTime,
    `require_amount` Decimal(12, 8),
    `del_flag` Bool
)
ENGINE = MergeTree()  ORDER BY create_time

Query id: 1ceeeb80-43f4-43ad-99a6-944bbf1d4549

Ok.

0 rows in set. Elapsed: 0.057 sec. 

ec430d220d73 :) DESCRIBE wudy.billflow

DESCRIBE TABLE  wudy.billflow

Query id: 4fde98cd-77c7-4394-a745-859f5f6945a7

┌─name───────────┬─type──────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id             │ Int64                                         │              │                    │         │                  │                │
│ batch_no       │ UUID                                          │              │                    │         │                  │                │
│ project_id     │ String                                        │              │                    │         │                  │                │
│ status         │ Enum8('unpaid' = 1, 'frozen' = 2, 'paid' = 3) │              │                    │         │                  │                │
│ create_time    │ DateTime                                      │              │                    │         │                  │                │
│ require_amount │ Decimal(12, 8)                                │              │                    │         │                  │                │
│ del_flag       │ Bool                                          │              │                    │         │                  │                │
└────────────────┴───────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

7 rows in set. Elapsed: 0.018 sec. 

ec430d220d73 :) INSERT INTO wudy.billflow VALUES ('20230710123456789', generateUUIDv4(), 'd7whdr3jd83yr8hde73dh37hfd', 'unpaid', '2100-01-01 11:11:11', 123.45678, TRUE)

INSERT INTO wudy.billflow FORMAT Values

Query id: c4eef0f0-cac6-4f46-a225-fc60d8918b4e

Ok.

1 rows in set. Elapsed: 0.150 sec. 

ec430d220d73 :) INSERT INTO wudy.billflow VALUES ('20230710223457611', generateUUIDv4(), 'f6343234h4543m4543n45678n88', 'frozen', '2100-02-02 12:12:12', 888.99999, FALSE)

INSERT INTO wudy.billflow FORMAT Values

Query id: 7a1abdc0-ce27-43fc-99d9-b08cd5db2558

Ok.

1 rows in set. Elapsed: 0.066 sec. 

ec430d220d73 :) INSERT INTO wudy.billflow VALUES ('20230711871524984', generateUUIDv4(), 'p53674b45bb4nn243b334534j342', 'paid', '2024-12-02 07:07:07', 666.77777, TRUE)

INSERT INTO wudy.billflow FORMAT Values

Query id: e9a91a8e-594a-4b15-abad-8b7b0bab2bcd

Connecting to clickhouse-server:9000 as user default.
Connected to ClickHouse server version 22.1.3 revision 54455.

Ok.

1 rows in set. Elapsed: 0.112 sec. 

• 构造账单测试表

主键	SQL
1	insert into wudy.bill_data values(1,101,'202308191052001','custom','201108003118','wudy.yu','new','10086','msp',99.99, '2023-08-19 22:57:00');
2	insert into wudy.bill_data values(2,102,'202308191108001','custom','201108003119','张磊','new','30045','msp',66.66, '2023-08-19 23:08:11');
3	insert into wudy.bill_data values(3,103,'202308191109002','custom','201108003120','彭梦婷','renew','10011','msp',43.88, '2023-08-20 10:01:23');
4	insert into wudy.bill_data values(4,104,'202308201001001','project','201108003121','孙铁','change','20071','tenant',20.88, '2023-08-20 10:26:45');

image.png

进入clickhouse容器可以看到有4个分区文件:

image.png

计算出统计的总价: 231.41

image.png

• ReplacingMergeTree引擎去重

去重分为: 物理去重(重复的数据直接被删除掉)、查询去重(不处理物理数据，但是查询结果是已经将重复数据过滤掉的)
插入重复数据（孙铁）: 除了version、require_amount外，其他字段都一样
| 主键 | SQL |
| ------------- |:-------------:|
| 4 |insert into wudy.bill_data values(4,105,'202308201001001','project','201108003121','孙铁','change','20071','tenant',105.88, '2023-08-20 10:26:45');|
| 4 |insert into wudy.bill_data values(4,106,'202308201001001','project','201108003121','孙铁','change','20071','tenant',106.88, '2023-08-20 10:26:45');|

方式一：final 去重查询: 查询结果已去重，物理数据未去重(未合并分区文件)

image.png

方式二：argMax方式去重查询: 查询结果已去重，物理数据未去重(未合并分区文件)

image.png

方式三：普通查询: 查询结果未去重，物理数据未去重(未合并分区文件)

image.png

上诉 final 和 argMax 查询示例都是基于本地表做的操作，final和argMax在结果上没有差异，但是如果基于分布式表进行试验，两条数据落在了不同数据分片（注意这里不是数据分区），那么final和argMax的结果将会产生差异: final的结果将是未去重的，原因是final只能对本地表做去重查询，不能对跨分片的数据进行去重查询，但是argMax的结果是去重的。argMax是通过比较第二参数version的大小，来取出我们要查询的最新数据来达到过滤掉重复数据的目的，其原理是将每个Shard的数据搂到同一个Shard的内存中进行比较计算，所以支持跨分片的去重

上诉三种查询方式均未对物理数据去重, 物理数据依然是6条，如下图:

image.png

再插入一条孙铁数据, id、version字段都一样， 只有创建时间不同
| 主键 | SQL |
| ------------- |:-------------:|
| 4 |insert into wudy.bill_data values(4,106,'202308201001001','project','201108003121','孙铁','change','20071','tenant',106.88, '2023-08-21 20:12:23');|

image.png
image.png
手动触发合并分区: optimize table bill_data final;

• ReplacingMergeTree优缺点:

• 1.使用主键作为判断重复数据的唯一键，支持插入相同主键数据。
  在合并分区的时候会触发删除重复数据的逻辑。但是合并的时机不确定，所以在
• 2.查询的时候可能会有重复数据，但是最终会去重。可以手动调用optimize，但是会引发对数据大量的读写，不建议生产使用。
• 3.以数据分区为单位删除重复数据，当分区合并时，同一分区内的重复数据会被删除，不同分区的重复数据不会被删除。
• 4.可以通过final，argMax方式做查询去重，这种方式无论有没有做过数据合并，都可以得到正确的查询结果。

• ReplacingMergeTree最佳使用方案：

• 1. 普通select查询：对时效不高的离线查询可以采用ClickHouse自动合并配合，但是需要保证同一业务单据落在同一个数据分区，分布式表也需要保证在同一个分片(Shard)，这是一种最高效，最节省计算资源的查询方式。
• 2.final方式查询：对于实时查询可以使用final，final是本地去重，需要保证同一主键数据落在同一个分片(Shard)，但是不需要落在同一个数据分区，这种方式效率次之，但是与普通select相比会消耗一些性能，如果where条件对主键索引，二级索引，分区字段命中的比较好的话效率也可以完全可以使用。
• 3.argMax方式查询：对于实时查询可以使用argMax，argMax的使用要求最低，咋查都能去重，但是由于它的实现方式，效率会低很多，也很消耗性能，不建议使用。后面9.4.3会配合压测数据与final进行对比

• 5.与MYSQL对比

ClickHouse	MySQL	说明
UInt8	UNSIGNED TINYINT
Int8	TINYINT
Int16	UNSIGNED SMALLINT
Int16	SMALLINT
UInt32	UNSIGNED INT, UNSIGNED MEDIUMINT
Int32	INT, MEDIUMINT
UInt64	UNSIGNED BIGINT
Int64	BIGINT
Float32	FLOAT
Float64	DOUBLE
Date	DATE
DATETIME	DATETIME, TIMESTAMP
FixedString	Char
String	VARCHAR, BLOB, TEXT
Decimal32(3)	Decimal	Decimal32:表示最大位数9位，小数部分3位 （123456.789）, Decimal64:表示最大位数18位，Decimal128:表示最大位数38位

修改数据:
1.早期的ClickHouse不支持可变数据
2.不支持事物，建议批量操作，避免高频率小数据量的修改
3.删除和更新是一个异步的操作，语句提交立即返回但不一定已经完成

注意事项：每次更新或者删除，会废弃目标数据的原有分区，而重建新分区

ec430d220d73 :) ALTER TABLE wudy.billflow  UPDATE   require_amount = 222.33333 WHERE id = '20230710123456789'

ALTER TABLE wudy.billflow
    UPDATE require_amount = 222.33333 WHERE id = '20230710123456789'

Query id: 00f3a410-47c5-4295-9398-3eafc06b9121

Ok.

0 rows in set. Elapsed: 0.062 sec.

• 6.ClickHouse分片分区