概述

什么是ClickHouse

ClickHouse，开源的列式存储数据库(DBMS)，使用C++语言编写，主要用于在线分析处理查询(OLAP)，能够使用SQL查询实时生成分析数据报告。

官网：https://clickhouse.com
GitHub地址：https://github.com/ClickHouse/ClickHouse

特点

列式存储

什么是行式存储

假设存在一张表，如下：

采用行式存储时，数据在磁盘上的组织结构如下：

(MySQl就是一种行式存储，我们在《MySQL从入门到实践：4.结构》，提到过"Compact"，一种行格式。)

行式存储的优点，当我们查某个实例所有的属性时，可以通过一次磁盘查找加顺序读取就可以。但是当想查所有实例的某一个熟悉(如年龄)，需要全表扫描，而且遍历的很多数据都是不需要的。

什么是列式存储

采用列式存储时，数据在磁盘上的组织结构如下：

这时想查所有实例的某一个(如年龄)，只需把那一列拿出来就可以了。

列式储存的优点

列式储存的优点：

对于列的聚合，计数，求和等统计操作原因优于行式存储。
由于某一列的数据类型都是相同的，针对于数据存储更容易进行数据压缩，每一列选择更优的数据压缩算法，大大提高了数据的压缩比重。
由于数据压缩比更好，一方面节省了磁盘空间，另一方面对于cache也有了更大的发挥空间。

DBMS的功能

支持标准SQL的语法，包括DDL和DML，以及配套的各种函数。
提供了用户管理及权限管理，数据的备份与恢复功能。

多样化引擎

ClickHouse和MySQL类似，把表级别的存储引擎插件化，可以为表指定不同的存储引擎。

高吞吐写入能力

ClickHouse采用类LSM-Tree的结构，数据写入后定期在后台Compaction。
通过类LSM-Tree的结构，ClickHouse在数据导入时全部是顺序append写，写入后数据段不可更改，在后台Compaction时也是多个段merge sort后顺序写回磁盘。
顺序写的特性，充分利用了磁盘的吞吐能力。

数据分区与线程级并行

ClickHouse将数据划分为多个partition，每个partition再进一步划分为多个index granularity(索引粒度)，然后通过多个CPU核心分别处理其中的一部分来实现并行数据处理。

在这种设计下，单条Query就能利用整机所有CPU，极致的并行处理能力，极大的降低了查询延时。

所以，ClickHouse即使对于大量数据的查询，也能够化整为零，快速处理。

但是，有一个弊端，单条查询使用多CPU不利于同时并发多条查询；对于高QPS的查询业务，这不是ClickHouse的强项。

更擅长单表查询

ClickHouse像很多OLAP数据库一样，单表查询速度优于关联查询。

应用场景

根据上文的讨论，我们能知道：
ClickHouse不适合做初始的存储，适合做已经处理过的，字段特别多的，宽表。

安装

准备工作

检查端口

需要开启以下端口：

8123
用于通过HTTP协议与ClickHouse数据库进行交互。
支持RESTful API和HTTP查询语法。
可以使用浏览器、curl等工具发送HTTP请求，并接收响应。
通常用于执行查询、获取数据和管理ClickHouse集群。
9000
用于通过TCP/IP协议与ClickHouse数据库进行交互。
使用ClickHouse自定义二进制协议进行通信。
支持更高的并发性和性能。
通常用于大规模数据导入、数据写入和数据处理等高性能场景。

开启方法，可以参考《ElasticSearch实战入门(6.X)：1.工具、概念、集群和倒排索引》的"安装ElasticSearch-安装ElasticSearch-五、允许远程连接"部分的讨论。

CentOS取消打开文件数限制

查看限制

通过ulimit -a，查看CentOS的限制。示例代码：

ulimit -a

运行结果：

core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 3766
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 3766
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

修改原因

我们需要修改的是open files和max user processes。

上文，我们讨论了"数据分区与线程级并行"；根据讨论内容，我们可以知道，在ClickHouse中，容易出现这两个值不够的情况。

修改内容

修改方法与《ElasticSearch实战入门(6.X)：1.工具、概念、集群和倒排索引》讨论的"修改系统配置"没有区别，本文不赘述。

需要注意，除了修改/etc/security/limits.conf文件，建议也修改/etc/security/limits.d/20-nproc.conf文件。

/etc/security/limits.conf和/etc/security/limits.d/20-nproc.conf，新增的文件内容相同，如下：

* soft nofile 65535
* hard nofile 65535
* soft nproc 131070
* hard nproc 131070

CentOS取消SELINUX

修改/etc/selinux/config，将SELNUX，设置为disabled。内容如下：

1	SELINUX=disabled

需要注意的是，修改完成后不会立即生效，需要重启。
如果不方便重启，有一个取巧的办法，步骤如下：

修改配置文件。
通过setenforce 0命令，临时关闭。
修改为"Permissive"，仅打印告警而不强制执行。
- setenforce 0，关闭；setenforce 1，打开。
- getenforce，查看状态。
将来有机会重启的话，读取修改过配置，生效。

安装依赖

需要安装两个依赖，安装命令如下：

yum install -y libtool
yum install -y *unixODBC*

因为CentOS7从2024年6月30日开始，不再维护，上述安装步骤可能会报错，可以尝试先执行如下的命令解决。

1 2	sed -i 's/mirrorlist/#mirrorlist/g' /etc/yum.repos.d/CentOS-* sed -i 's\|#baseurl=http://mirror.centos.org\|baseurl=http://vault.centos.org\|g' /etc/yum.repos.d/CentOS-*

安装方法

下载安装包

下载地址：https://packages.clickhouse.com/rpm/stable/

一共需要下载四个安装包，分别是：

clickhouse-client
clickhouse-common-static
clickhouse-common-static-dbg
clickhouse-server

在本文下载的是：clickhouse-client-23.12.6.19.x86_64.rpm、clickhouse-common-static-23.12.6.19.x86_64.rpm、clickhouse-common-static-dbg-23.12.6.19.x86_64.rpm和clickhouse-server-23.12.6.19.x86_64.rpm。

(题外话，ClickHouse的版本号，与一般版本编号不一样，ClickHouse的版本号，是按照年份月份的方式编号的。)

安装

通过rpm -ivh安装上述的4个rpm文件。

示例代码：

1	rpm -ivh *.rpm

在安装clickhouse-server-23.12.6.19-1.x86_64的时候，会提示输入密码：

1 2	Enter password for default user: Password for default user is saved in file /etc/clickhouse-server/users.d/default-password.xml.

解释说明：默认用户名default。

通过rpm -qa查看安装情况，示例代码：

1	rpm -qa\|grep clickhouse

运行结果：

clickhouse-client-23.12.6.19-1.x86_64
clickhouse-server-23.12.6.19-1.x86_64
clickhouse-common-static-dbg-23.12.6.19-1.x86_64
clickhouse-common-static-23.12.6.19-1.x86_64

修改配置文件

修改/etc/clickhouse-server/config.xml，找到Listen specified address部分，把<listen_host>::</listen_host>的注释打开，这样的话才能让ClickHouse能被除本机以外的服务器访问。

在/etc/clickhouse-server/config.xml文件中，有ClickHouse的一些默认路径配置，比较重要的有：

数据文件路径：/var/ib/clickhouse/
日志文件路径：/var/log/clickhouse-server/clickhouse-server.log

启动停止

启动：clickhouse start
停止：clickhouse stop
重启：clickhouse restart
查看状态：clickhouse status

当然，我们也可以通过systemctl start clickhouse-server启动停止。

使用client连接server

命令：clickhouse-client -m。-m的作用是以;表示结束，这样敲命令的时候可以换行，否则换行就执行了。

我们敲一个命令，试一下，示例代码：

1	show databases;

运行结果：


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

ClickHouse各文件目录

bin/：/usr/bin/
conf/：/etc/clickhouse-server/
lib/：/var/lib/clickhouse/
log/：/var/log/clickhouse-server/

数据类型

整型

固定长度的整型，分为：

有符号整型
$[-{2^n}-1,2^{n-1}-1]$ 。
常见的有：Int8、Int16、Int32、Int64。
无符号整型
$[0,{2^n}-1]$ 。
常见的有：UInt8、UInt16、UInt32、UInt64。

浮点型

Float32：foat
Float64：double

注意，浮点型计算时可能引起误差。

示例代码：

1	select 1.0 - 0.9;

运行结果：


┌──────minus(1., 0.9)─┐
│ 0.09999999999999998 │
└─────────────────────┘

布尔型(UInt8)

没有单独的类型来存储布尔值。可以使用 UInt8 类型，取值限制为0或1。

Decimal型

有符号的浮点数，可在加、减和乘法运算过程中保持精度。对于除法，最低有效数字会被丢弃(不舍入)。

有三种声明方式：

Decimal32(s)，相当于Decimal(9-s,s)。
Decimal64(s)，相当于Decimal(18-s,s)。
Decimal128(s)，相当于Decimal(38-s,s)。

字符串

字符串分为两种：

String
字符串可以任意长度的，可以包含任意的字节集，包含空字节。
FixedString(N)
固定长度N的字符串，N必须是严格的正自然数。
当服务端读取长度小于N的字符串时候，通过在字符串末尾添加空字节来达到N字节长度；当服务端读取长度大于N的字符串时候，将返回错误消息。
固定长度的可以保存一些定长的内容，比如一些编码，性别等；但是考虑到一定的变化风险，带来收益不够明显，所以定长字符串使用意义有限。

枚举类型

概述

枚举类型，Enum，保存'string'=integer的对应关系。
分为两种：

Enum8，用'String'=Int8描述。
Enum16，用'String'=Int16描述。

示例

创建一个带有一个枚举Enum8('hello'=1,'world'=2)类型的列。示例代码：

CREATE TABLE t_enum
(
    x Enum8('hello' = 1, 'world' = 2)
)
    ENGINE = TinyLog

这个x列只能存储类型定义中列出的值：'hello'或'world'。示例代码：

INSERT INTO t_enum
VALUES ('hello'),
       ('world'),
       ('hello');

如果尝试保存任何其他值，ClickHouse抛出异常。示例代码：

1 2	insert into t_enum values ('a')

运行结果：


Ok.
Exception on client:
Code: 691. DB::Exception: Unknown element 'a' for enum: while executing 'FUNCTION if(isNull(_dummy_0) : 3, defaultValueOfTypeName('Enum8(\'hello\' = 1, \'world\' = 2)') :: 2, _CAST(_dummy_0, 'Enum8(\'hello\' = 1, \'world\' = 2)') :: 4) -> if(isNull(_dummy_0), defaultValueOfTypeName('Enum8(\'hello\' = 1, \'world\' = 2)'), _CAST(_dummy_0, 'Enum8(\'hello\' = 1, \'world\' = 2)')) Enum8('hello' = 1, 'world' = 2) : 1': While executing ValuesBlockInputFormat: data for INSERT was parsed from query. (UNKNOWN_ELEMENT_OF_ENUM)

如果需要看到对应行的数值，则必须将Enum值转换为整数类型。示例代码：

1 2	SELECT x, CAST(x, 'Int8') FROM t_enum

运行结果：


┌─x─────┬─CAST(x, 'Int8')─┐
│ hello │               1 │
│ world │               2 │
│ hello │               1 │
└───────┴─────────────────┘

谨慎使用

对一些状态、类型的字段算是一种空间优化，也算是一种数据约束。
但是实际使用中往往因为一些数据内容的变化增加一定的维护成本，甚至是数据丢失问题。所以谨慎使用。

时间类型

目前ClickHouse有三种时间类型：

Date，接受"年-月-日"的字符串，比如2019-12-16。
Datetime，接受"年-月-日时:分:秒"的字符串，比如2019-12-16 20:50:10。
Datetime64，接受"年-月-日时:分:秒.亚秒"的字符串，比如2019-12-16 20:50:10.66。

数组

概述

Array(T)，由T类型元素组成的数组。T可以是任意类型，包含数组类型。但不推荐使用多维数组，ClickHouse对多维数组的支持有限。例如，不能在MergeTree表中存储多维数组。

创建数组

array函数
示例代码：
1
SELECT array(1, 2) AS x, toTypeName(x)
方括号
示例代码：
1
SELECT [1, 2] AS x, toTypeName(x)

注意，同一个数组的类型需要一致。示例代码：

1	SELECT array(1, 2, 'a') AS x, toTypeName(x);

运行结果：


Elapsed: 0.353 sec. 

Received exception from server (version 23.12.6):
Code: 386. DB::Exception: Received from localhost:9000. DB::Exception: There is no supertype for types UInt8, UInt8, String because some of them are String/FixedString and some of them are not: While processing [1, 2, 'a'] AS x, toTypeName(x). (NO_COMMON_TYPE)

表引擎

TinyLog

以列文件的形式保存在磁盘上。
不支持索引。
没有并发控制。
一般保存少量数据的小表。
生产环境上作用有限，可以用于平时测试。

Memory

内存引擎，数据以未压缩的原始形式直接保存在内存当中，服务器重启数据就会消失。
不支持索引。
读写操作不会相互阻塞。
简单查询下有非常高的性能表现。
一般在需要非常高的性能，同时数据量又不太大的场景下，才会用到。

集成引擎

ClickHouse中还有集成引擎，例如MySQL引擎，示例代码：

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] | named_collection[, option=value [,..]]})
SETTINGS
    [ connection_pool_size=16, ]
    [ connection_max_tries=3, ]
    [ connection_wait_timeout=5, ]
    [ connection_auto_close=true, ]
    [ connect_timeout=10, ]
    [ read_write_timeout=300 ]
;

我们可以在ClickHouse中创建一张表，然后可以通过这张表select和insert在MySQL中已经存在的一张表。

我个人很难理解，集成引擎到底有什么作用。

MergeTree

概述

MergeTree，合并树，ClickHouse中最非常重要的表引擎；类似InnoDB之于MySQL；支持索引和分区。

建表语句。示例代码：

create table t_order_mt
(
    id           UInt32,
    sku_id       String,
    total_amount Decimal(16, 2),
    create_time  Datetime
) engine MergeTree
      partition by toYYYYMMDD(create_time)
      primary key (id)
      order by (id, sku_id)

插入数据。示例代码：

insert into t_order_mt
values (101, 'sku_001', 1000.00, '2024-07-01 12:00:00'),
       (102, 'sku_002', 2000.00, '2024-07-01 11:00:00'),
       (102, 'sku_004', 2500.00, '2024-07-01 12:00:00'),
       (102, 'sku_002', 2000.00, '2024-07-01 13:00:00'),
       (102, 'sku_002', 12000.00, '2024-07-01 13:00:00'),
       (102, 'sku_002', 600.00, '2024-07-02 12:00:00');

特别的，我们还可以查询看一下。示例代码：

1	select * from t_order_mt;

运行结果：


┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │         1000 │ 2024-07-01 12:00:00 │
│ 102 │ sku_002 │         2000 │ 2024-07-01 11:00:00 │
│ 102 │ sku_002 │         2000 │ 2024-07-01 13:00:00 │
│ 102 │ sku_002 │        12000 │ 2024-07-01 13:00:00 │
│ 102 │ sku_004 │         2500 │ 2024-07-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │          600 │ 2024-07-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘

解释说明：这个查询结果，体现了两个特点

partition by
order by

partition by

概述

作用
分区的目的主要是降低扫描的范围，优化查询速度。
默认值
如果不填，则默认只会使用一个分区。
分区目录
MergeTree是以列文件+索引文件+表定义文件组成的。如果设定了分区，那么这些文件就会保存到不同的分区目录中。
并行
分区后，面对涉及跨分区的查询统计，ClickHouse会以分区为单位并行处理。

分区目录

我们可以具体t_order_mt的结构，示例代码：

1	ll /var/lib/clickhouse/data/default/t_order_mt/

运行结果：

total 4
drwxr-x--- 2 clickhouse clickhouse 259 Aug  3 00:13 20240701_1_1_0
drwxr-x--- 2 clickhouse clickhouse 259 Aug  3 00:13 20240702_2_2_0
drwxr-x--- 2 clickhouse clickhouse   6 Aug  3 00:12 detached
-rw-r----- 1 clickhouse clickhouse   1 Aug  3 00:12 format_version.txt

20240701_1_1_0，PartitionId_MinBlockNum_MaxBlockNum_Level。

PartitionId
- 分区ID生成规则
  - 未定义分区键
    没有定义PARTITION BY，默认生成一个目录名为all的数据分区，所有数据均存放在all目录下。
  - 整型分区键
    分区键为整型，那么直接用该整型值的字符串形式做为分区ID。
  - 日期类分区
    分区键为日期类型，或者可以转化成日期类型。
  - 其他类型分区键
    String、Float类型等，通过128位的Hash算法取其Hash值作为分区ID。
MinBlockNum
最小分区块编号，自增类型，从1开始向上递增。每产生一个新的目录分区就向上递增一个数字。
MaxBlockNum
最大分区块编号，新创建的分区MinBlockNum等于MaxBlockNum的编号。
Level
合并的层级，被合并的次数。合并次数越多，层级值越大。

还可以看一下20240701_1_1_0中的内容，示例代码：

1	ll /var/lib/clickhouse/data/default/t_order_mt/20240701_1_1_0/

运行结果：

total 44
-rw-r----- 1 clickhouse clickhouse 333 Aug  3 00:13 checksums.txt
-rw-r----- 1 clickhouse clickhouse 118 Aug  3 00:13 columns.txt
-rw-r----- 1 clickhouse clickhouse   1 Aug  3 00:13 count.txt
-rw-r----- 1 clickhouse clickhouse 189 Aug  3 00:13 data.bin
-rw-r----- 1 clickhouse clickhouse  66 Aug  3 00:13 data.cmrk3
-rw-r----- 1 clickhouse clickhouse  10 Aug  3 00:13 default_compression_codec.txt
-rw-r----- 1 clickhouse clickhouse   1 Aug  3 00:13 metadata_version.txt
-rw-r----- 1 clickhouse clickhouse   8 Aug  3 00:13 minmax_create_time.idx
-rw-r----- 1 clickhouse clickhouse   4 Aug  3 00:13 partition.dat
-rw-r----- 1 clickhouse clickhouse  42 Aug  3 00:13 primary.cidx
-rw-r----- 1 clickhouse clickhouse 292 Aug  3 00:13 serialization.json

解释说明：

data.bin，数据文件。
在早期的一些版本中，有多个列会有多个数据文件，在本文id.bin、sku_id.bin、total_amount.bin、create_time.bin。
count.txt，记录当前分区的行数。

columns.txt，记录列的信息。
示例代码：

1	cat columns.txt

运行结果：

4 columns:
`id` UInt32
`sku_id` String
`total_amount` Decimal(16, 2)
`create_time` DateTime

primary.cidx，主键索引。

数据写入与分区合并

任何一个批次的数据写入都会产生一个临时分区，不会纳入任何一个已有的分区。写入后的某个时刻(大概10-15分钟后)，ClickHouse会自动执行合并操作(或我们手动通过optimize执行)，把临时分区的数据，合并到已有分区中。

示例代码：

1	optimize table final;

例如，我们再执行一次上面的插入操作，查询数据，发现没有纳入任何分区。示例代码：

1	select * from t_order_mt;

运行结果：


┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │         1000 │ 2024-07-01 12:00:00 │
│ 102 │ sku_002 │         2000 │ 2024-07-01 11:00:00 │
│ 102 │ sku_002 │         2000 │ 2024-07-01 13:00:00 │
│ 102 │ sku_002 │        12000 │ 2024-07-01 13:00:00 │
│ 102 │ sku_004 │         2500 │ 2024-07-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │         1000 │ 2024-07-01 12:00:00 │
│ 102 │ sku_002 │         2000 │ 2024-07-01 11:00:00 │
│ 102 │ sku_002 │         2000 │ 2024-07-01 13:00:00 │
│ 102 │ sku_002 │        12000 │ 2024-07-01 13:00:00 │
│ 102 │ sku_004 │         2500 │ 2024-07-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │          600 │ 2024-07-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │          600 │ 2024-07-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘

看一下分区目录，示例代码：

1	ll /var/lib/clickhouse/data/default/t_order_mt/

运行结果：

total 4
drwxr-x--- 2 clickhouse clickhouse 259 Aug  3 00:13 20240701_1_1_0
drwxr-x--- 2 clickhouse clickhouse 259 Aug  3 01:08 20240701_3_3_0
drwxr-x--- 2 clickhouse clickhouse 259 Aug  3 00:13 20240702_2_2_0
drwxr-x--- 2 clickhouse clickhouse 259 Aug  3 01:08 20240702_4_4_0
drwxr-x--- 2 clickhouse clickhouse   6 Aug  3 00:12 detached
-rw-r----- 1 clickhouse clickhouse   1 Aug  3 00:12 format_version.txt

手动optimize，optimize table t_order_mt final

再次查询，示例代码：

1	select * from t_order_mt;

运行结果：


┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │         1000 │ 2024-07-01 12:00:00 │
│ 101 │ sku_001 │         1000 │ 2024-07-01 12:00:00 │
│ 102 │ sku_002 │         2000 │ 2024-07-01 11:00:00 │
│ 102 │ sku_002 │         2000 │ 2024-07-01 13:00:00 │
│ 102 │ sku_002 │        12000 │ 2024-07-01 13:00:00 │
│ 102 │ sku_002 │         2000 │ 2024-07-01 11:00:00 │
│ 102 │ sku_002 │         2000 │ 2024-07-01 13:00:00 │
│ 102 │ sku_002 │        12000 │ 2024-07-01 13:00:00 │
│ 102 │ sku_004 │         2500 │ 2024-07-01 12:00:00 │
│ 102 │ sku_004 │         2500 │ 2024-07-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │          600 │ 2024-07-02 12:00:00 │
│ 102 │ sku_002 │          600 │ 2024-07-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘

看一下分区目录，示例代码：

1	ll /var/lib/clickhouse/data/default/t_order_mt/

运行结果：

total 4
drwxr-x--- 2 clickhouse clickhouse 259 Aug  3 00:13 20240701_1_1_0
drwxr-x--- 2 clickhouse clickhouse 259 Aug  3 01:09 20240701_1_3_1
drwxr-x--- 2 clickhouse clickhouse 259 Aug  3 01:08 20240701_3_3_0
drwxr-x--- 2 clickhouse clickhouse 259 Aug  3 00:13 20240702_2_2_0
drwxr-x--- 2 clickhouse clickhouse 259 Aug  3 01:09 20240702_2_4_1
drwxr-x--- 2 clickhouse clickhouse 259 Aug  3 01:08 20240702_4_4_0
drwxr-x--- 2 clickhouse clickhouse   6 Aug  3 00:12 detached
-rw-r----- 1 clickhouse clickhouse   1 Aug  3 00:12 format_version.txt

primary key

只提供了数据的一级索引，但不是唯一约束。 这一点和其他数据库不太一样。

主键的设定主要依据是查询语句中的where条件。

order by

设定分区内的数据按照哪些字段顺序进行有序保存。
MergeTree中唯一一个必填项，当用户不设置主键的情况，很多处理(例如去重和汇总)会依照order by的字段进行处理。
主键必须是order by字段的前缀字段。例如，order by字段是(id,sku_id)，那么主键必须是id或者(id,sku_id)。

二级索引

示例代码：

create table t_order_mt2
(
    id           UInt32,
    sku_id       String,
    total_amount Decimal(16, 2),
    create_time  Datetime,
    INDEX a total_amount TYPE minmax GRANULARITY 5
) engine = MergeTree
      partition by toYYYYMMDD(create_time)
      primary key (id)
      order by (id, sku_id)

注意INDEX a total_amount TYPE minmax GRANULARITY 5。

INDEX，声明索引。
a，索引名称。
total_amount，要加索引的字段。
TYPE，声明索引类型。
minmax，索引类型。
GRANULARITY，声明索引粒度。
5，索引粒度5。

TTL

什么是TTL

TTL，Time To Live，MergeTree提供的可以管理数据表或者列的生命周期的功能。

在有些场景中，只需要最新的数据，会用到TTL。

列级别TTL

示例代码：

create table t_order_mt3
(
    id           UInt32,
    sku_id       String,
    total_amount Decimal(16, 2) TTL create_time + interval 10 SECOND,
    create_time  Datetime
) engine = MergeTree
      partition by toYYYYMMDD(create_time)
      primary key (id)
      order by (id, sku_id)

我们插入数据，然后手动合并，会发现到期后，指定的字段数据为0。

表级TTL

示例代码：

1 2	alter table t_order_mt3 MODIFY TTL create_time + INTERVAL 10 SECOND;

注意

涉及判断的字段，不能是主键。
涉及判断的字段必须是Date或者Datetime类型，推荐使用分区的日期字段。
能够使用的时间周期有：SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR。

ReplacingMergeTree

什么是ReplacingMergeTree

ReplacingMergeTree是MergeTree的一个变种，其存储特性完全继承MergeTree, 只是多了一个去重的功能。

如果我们想处理掉重复的数据，可以借助这个ReplacingMergeTree。另外，ReplacingMergeTree是根据order by去重的。

去重时机

数据的去重只会在合并的过程中出现。

合并会在未知的时间在后台进行，所以我们无法预先作出计划，会有一些数据可能仍未被处理。

所以ReplacingMergeTree能力有限，ReplacingMergeTree适用于在后台清除重复的数据以节省空间，但是它不保证没有重复的数据出现。

去重范围

如果表经过了分区，去重只会在分区内部进行去重，不能执行跨分区的去重。

建表语句

示例代码：

create table t_order_rmt
(
    id           UInt32,
    sku_id       String,
    total_amount Decimal(16, 2),
    create_time  Datetime
) engine ReplacingMergeTree(create_time)
      partition by toYYYYMMDD(create_time)
      primary key (id)
      order by (id, sku_id)

注意：ReplacingMergeTree(create_time)。

SummingMergeTree

应用场景

对于不查询明细，只关心以维度进行汇总聚合结果的场景。
如果只使用普通的MergeTree 的话，无论是存储空间的开销，还是查询时临时聚合的开销都比较大。

这时候，就可以考虑能够"预聚合"的引擎SummingMergeTree。

特点

以SummingMergeTree()中指定的列作为汇总数据列。
可以填写多列，且必须是数字列；如果不填，以所有非维度列且为数字列的字段为汇总数据列。
以order by的列作为维度列。
其他的列按插入顺序保留第一行。
不在一个分区的数据不会被聚合。
只有在同一批次插入(新版本)或分片合并时才会进行聚合。

建表语句

示例代码：

create table t_order_smt
(
    id           UInt32,
    sku_id       String,
    total_amount Decimal(16, 2),
    create_time  Datetime
) engine SummingMergeTree(total_amount)
      partition by toYYYYMMDD(create_time)
      primary key (id)
      order by (id, sku_id)

建议

设计聚合表的话，唯一键值、流水号可以去掉，所有字段全部是维度、度量或者时间戳。

SQL

ClickHouse中的SQL和MySQL中的SQL非常相似，在这里讨论一下，和MySQL中的SQL不一样的地方。

delete和update

ClickHouse提供了Delete和Update的能力，这类操作被称为Mutation查询，它可以看做Alter的一种。

虽然可以实现修改和删除，但是和一般的OLTP数据库不一样，Mutation语句是一种很"重"的操作，而且不支持事务。

"重"的原因主要是每次修改或者删除都会导致放弃目标数据的原有分区，重建新分区。所以尽量做批量的变更，不要进行频繁小数据的操作。

删除操作，示例代码：

alter
table
t_order_mt
delete
where sku_id = 'sku_001';

修改操作，示例代码：

alter
table
t_order_mt
update total_amount = toDecimal32(2000.00, 2)
where id = 102;

由于操作比较"重"，所以 Mutation语句分两步执行，同步执行的部分其实只是进行新增数据、新增分区和并把旧分区打上逻辑上的失效标记。直到触发分区合并的时候，才会删除旧数据释放磁盘空间。

上文，我们分别执行删除和修改后，再看一下分区目录，示例代码：

1	ll /var/lib/clickhouse/data/default/t_order_mt/

运行结果：

total 12
drwxr-x--- 2 clickhouse clickhouse 259 Aug  3 01:09 20240701_1_3_1
drwxr-x--- 2 clickhouse clickhouse 259 Aug  3 03:55 20240701_1_3_1_5
drwxr-x--- 2 clickhouse clickhouse 259 Aug  3 03:55 20240701_1_3_1_6
drwxr-x--- 2 clickhouse clickhouse 259 Aug  3 01:09 20240702_2_4_1
drwxr-x--- 2 clickhouse clickhouse 259 Aug  3 03:55 20240702_2_4_1_5
drwxr-x--- 2 clickhouse clickhouse 259 Aug  3 03:55 20240702_2_4_1_6
drwxr-x--- 2 clickhouse clickhouse   6 Aug  3 00:12 detached
-rw-r----- 1 clickhouse clickhouse   1 Aug  3 00:12 format_version.txt
-rw-r----- 1 clickhouse clickhouse  95 Aug  3 03:55 mutation_5.txt
-rw-r----- 1 clickhouse clickhouse 120 Aug  3 03:55 mutation_6.txt

解释说明：整体拷贝了一份原来的分区。

在一段时间后，会删去历史的数据。示例代码：

1	ll /var/lib/clickhouse/data/default/t_order_mt/

运行结果：

total 12
drwxr-x--- 2 clickhouse clickhouse 259 Aug  3 03:57 20240701_1_3_2_6
drwxr-x--- 2 clickhouse clickhouse 259 Aug  3 03:57 20240702_2_4_2_6
drwxr-x--- 2 clickhouse clickhouse   6 Aug  3 00:12 detached
-rw-r----- 1 clickhouse clickhouse   1 Aug  3 00:12 format_version.txt
-rw-r----- 1 clickhouse clickhouse  95 Aug  3 03:55 mutation_5.txt
-rw-r----- 1 clickhouse clickhouse 120 Aug  3 03:55 mutation_6.txt

limit by

假设存在一张表，数据如下。示例代码：

1	select * from t1;

运行结果：


┌─id─┬─name───┬───birthday─┐
│  1 │ First  │ 2011-01-01 │
│  2 │ Second │ 2012-02-02 │
│  3 │ Second │ 2011-01-01 │
└────┴────────┴────────────┘

通过limit by，每个birthday，只保留1个。示例代码：

1	select * from t1 limit 1 by birthday;

运行结果：


┌─id─┬─name───┬───birthday─┐
│  1 │ First  │ 2011-01-01 │
│  2 │ Second │ 2012-02-02 │
└────┴────────┴────────────┘

参考资料：

https://www.bilibili.com/video/BV1Yh411z7os

https://blog.csdn.net/qq_41081716/article/details/131260991

https://forum.butian.net/question/854

https://www.gbase8.cn/5237

文章作者: Kaka Wan Yifan

文章链接: https://kakawanyifan.com/12101

留言板

概述

什么是ClickHouse

特点

列式存储

什么是行式存储

什么是列式存储

列式储存的优点

DBMS的功能

多样化引擎

高吞吐写入能力

数据分区与线程级并行

更擅长单表查询

应用场景

安装

准备工作

检查端口

CentOS取消打开文件数限制

查看限制

修改原因

修改内容

CentOS取消SELINUX

安装依赖

安装方法

下载安装包

安装

修改配置文件

启动停止

使用client连接server

ClickHouse各文件目录

数据类型

整型

浮点型

布尔型(UInt8)

Decimal型

字符串

枚举类型

概述

示例

谨慎使用

时间类型

数组

概述

创建数组

更多数据类型

表引擎

TinyLog

Memory

集成引擎

MergeTree

概述

partition by

概述

分区目录

数据写入与分区合并

primary key

order by

二级索引

TTL

什么是TTL

列级别TTL

表级TTL

注意

ReplacingMergeTree

什么是ReplacingMergeTree

去重时机

去重范围

建表语句

SummingMergeTree

应用场景

特点

建表语句

建议

SQL

delete和update

limit by