部署数据集

https://clickhouse.com/docs/zh/getting-started/example-datasets

官方文档给出了一堆示例数据集。

我们这里选择食谱，https://clickhouse.com/docs/zh/getting-started/example-datasets/recipes。
稍微小一点

下载并解压数据集

进入下载页面https://recipenlg.cs.put.poznan.pl/dataset。
接受条款和条件并下载 zip 文件。
使用 unzip 解压 zip 文件，得到 full_dataset.csv 文件。

创建表

运行 clickhouse-client 并执行以下 CREATE 请求：

CREATE TABLE recipes
(
    title String,
    ingredients Array(String),
    directions Array(String),
    link String,
    source LowCardinality(String),
    NER Array(String)
) ENGINE = MergeTree ORDER BY title;

其中字段含义为

title 食谱标题
ingredients 成分
directions 烹饪指导
link 出处
LowCardinality 低基数类型
Named Entity Recognition，简称NER，类似标签

这里可以看到clickhouse支持Array

插入数据

运行以下命令：

clickhouse-client --query "
    INSERT INTO recipes
    SELECT
        title,
        JSONExtract(ingredients, 'Array(String)'),
        JSONExtract(directions, 'Array(String)'),
        link,
        source,
        JSONExtract(NER, 'Array(String)')
    FROM input('num UInt32, title String, ingredients String, directions String, link String, source LowCardinality(String), NER String')
    FORMAT CSVWithNames
" --input_format_with_names_use_header 0 --format_csv_allow_single_quote 0 --input_format_allow_errors_num 10 < full_dataset.csv

如果一切顺利，这里执行完毕数据就插入完毕了。

可以查看插入了多少行数据。

fenixs :) select count() from recipes;

SELECT count()
FROM recipes

Query id: b3ac8973-8772-4347-a1a0-4b9ea393f2f0

┌─count()─┐
│ 2231142 │
└─────────┘

1 row in set. Elapsed: 0.002 sec.

比如我们想查找包含柠檬（lemon）的最短的10个食谱

1
2
3


select directions,title,NER from recipes 
where  has(NER, 'lemon') order by length(directions) limit 10 \G

这里我们使用has函数从判断NER中是否包含lemon。那么clickhouse到底有哪些类型呢？

数据类型

数值类型

整型

没啥好说，目前已经支持8位到256位整型

整型范围

Int8 — [-128 : 127]
Int16 — [-32768 : 32767]
Int32 — [-2147483648 : 2147483647]
Int64 — [-9223372036854775808 : 9223372036854775807]
Int128 — [-170141183460469231731687303715884105728 : 170141183460469231731687303715884105727]
Int256 — [-57896044618658097711785492504343953926634992332820282019728792003956564819968 : 57896044618658097711785492504343953926634992332820282019728792003956564819967]
别名：

Int8 — TINYINT, BOOL, BOOLEAN, INT1.
Int16 — SMALLINT, INT2.
Int32 — INT, INT4, INTEGER.
Int64 — BIGINT.

无符号整型范围

UInt8 — [0 : 255]
UInt16 — [0 : 65535]
UInt32 — [0 : 4294967295]
UInt64 — [0 : 18446744073709551615]
UInt128 — [0 : 340282366920938463463374607431768211455]
UInt256 — [0 : 115792089237316195423570985008687907853269984665640564039457584007913129639935]

浮点

浮点则只有Float32,Float64，为IEEE标准浮点，如果想自定义其精度，则需使用Decimal。

字符串

String

长度不限，且不需要指定字符集的编码。

从设计上clickhouse希望替我们屏蔽掉sql语句中的String，到网络传输过程中转换为字节，再到server端又转换为字符，再到存储时又转换为字节。我们在一些传统数据库服务器中需要小心地配置这些编码使其保持一致。clickhouse更希望这一切对我们透明。

FixString

和String类似，不过是定长。
如果字符串包含的字节数少于`N’,将对字符串末尾进行空字节填充。
如果字符串包含的字节数大于N,将抛出Too large value for FixedString(N)异常。

UUID

fenixs :) create table test_c (a UUID) engine MergeTree order by a;

CREATE TABLE test_c
(
    `a` UUID
)
ENGINE = MergeTree
ORDER BY a

直接调用函数在server侧生成UUID

1	INSERT INTO test_c SELECT generateUUIDv4()

时间

DateTime

支持到时分秒，占用4字节，支持字符串写入

1 2	fenixs :) create table test_d (a Datetime) engine Memory; fenixs :) INSERT INTO Datetime_TEST VALUES('2019-06-22 00:00:00');

DateTime64

顾名思义64位占用8字节，可以提供高于秒的精度。

Date

精度到天

复合类型

数组

定义方式1 array(1,2,3)

1	fenixs :) select array(1,2,3)

定义方式2 [1,2,3]

1	select [1,2,3]

创建array不需要显式声明其类型，clickhouse会自动推断，如果存在Null会推断为Nullable：


fenixs :) SELECT [1, 2, null] as a , toTypeName(a)

SELECT
    [1, 2, NULL] AS a,
    toTypeName(a)

Query id: 1a4019e2-3e3b-4073-934b-81115c7fd35b

┌─a──────────┬─toTypeName([1, 2, NULL])─┐
│ [1,2,NULL] │ Array(Nullable(UInt8))   │
└────────────┴──────────────────────────┘

1 row in set. Elapsed: 0.008 sec.

定义表时则必须明确指出其类型


fenixs :) create table test_e (a Array(DateTime)) engine Memory;

CREATE TABLE test_e
(
    `a` Array(DateTime)
)
ENGINE = Memory

Tuple

元组支持不同的类型

1 2	SELECT tuple(1,'a',now()) AS x, toTypeName(x)

Enum

ClickHouse提供了Enum8和Enum16两种枚举类型，它们除了取值范围不同之外，别无二致。枚举固定使用(String:Int)Key/Value键值对的形式定义数据。

1
2
3

CREATE TABLE Enum_TEST (
 c1 Enum8('ready' = 1, 'start' = 2, 'success' = 3, 'error' = 4)
) ENGINE = Memory

Nested

fenixs :) CREATE TABLE nested_test (
 name String,
 age UInt8 ,
 dept Nested(
    id UInt8,
    name String
 )
) ENGINE = Memory;

这种数据的插入方式需要注意，首先嵌套表只能有一层。其次，嵌套表和外层不是一对一的关系。

fenixs :) INSERT INTO nested_test VALUES ('bruce' , 30 , [10000,10001,10002], ['研发部','技术支持中心','测试部']);

SELECT *
FROM nested_test

Query id: b689f40a-c74c-4734-9dcd-a4be236e3015

┌─name──┬─age─┬─dept.id────┬─dept.name────────────────────────────┐
│ bruce │  30 │ [16,17,18] │ ['研发部','技术支持中心','测试部']     │
└───────┴─────┴────────────┴──────────────────────────────────────┘

而是Array。

特殊类型

Nullable

用来修饰其他类型，表示这个字段可以为null。

在使用Nullable类型的时候还有两点值得注意：

首先，它只能和基础类型搭配使用，不能用于数组和元组这些复合类型，也不能作为索引字段。
其次，应该慎用Nullable类型，包括Nullable的数据表，不然会使查询和写入性能变慢。因为在正常情况下，每个列字段的数据会被存储在对应的[Column].bin文件中。如果一个列字段被Nullable类型修饰后，会额外生成一个[Column].null.bin文件专门保存它的Null值。这意味着在读取和写入数据时，需要一倍的额外文件操作。

IPV4 IPV6

IPV4使用Uint32存储数据。IPV6使用FixedString(16)，效率更高，更节省空间。

数据库引擎

创建数据库可以配置数据库引擎，比较常用的：

Atomic
默认引擎，数据库Atomic中的所有表都有唯一的UUID，并将数据存储在目录/clickhouse_path/store/xxx/xxxyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy/，其中xxxyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy是该表的UUID。
MySql
可以将远端mysql映射为本地database，并允许您对表进行INSERT和SELECT查询，以方便您在ClickHouse与MySQL之间进行数据交换。

MySQL数据库引擎会将对其的查询转换为MySQL语法并发送到MySQL服务器中，因此可以执行诸如SHOW TABLES或SHOW CREATE TABLE之类的操作。

1 2	CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster] ENGINE = MySQL('host:port', ['database' \| database], 'user', 'password')

可以直接联查两个远端实例的数据。

Memory
内存引擎，用于存放临时数据。此类数据库下的数据
表只会停留在内存中，不会涉及任何磁盘操作，当服务重启后数据会
被清除内存
SQLite
允许连接到SQLite数据库，并支持ClickHouse和SQLite交换数据，执行 INSERT 和 SELECT 查询。
1
CREATE DATABASE sqlite_database ENGINE = SQLite('db_path')
PostgreSQL
允许连接到远程PostgreSQL服务。支持读写操作(SELECT和INSERT查询)，以在ClickHouse和PostgreSQL之间交换数据。

1 2	CREATE DATABASE test_database ENGINE = PostgreSQL('host:port', 'database', 'user', 'password'[, `use_table_cache`]);

表引擎

MergeTree

clickhouse的核心表引擎为MergeTree和MergeTree的变种。

存储的数据按主键排序。
如果指定了分区键的话，可以使用分区。
支持数据副本。

数据结构为LSM（Log-Structured Merge-Tree ）这个数据结构在HBase，LevelDb，Rocksdb，等处都有广泛的应用。主要的思想是把随机写转为顺序写，提升写效率，但是会损失一些读性能。后面我会专门分析LSM树和它的简单实现。

一个标准的clickhouse建表语句如下：

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],
    ...
    INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
    INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2
) ENGINE = MergeTree()
ORDER BY expr
[PARTITION BY expr] 
[PRIMARY KEY expr]
[SAMPLE BY expr]
[TTL expr [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'], ...]
[SETTINGS name=value, ...]

分区键

创建一个支持分片的sql语句通常如下

CREATE TABLE visits
(
    VisitDate Date,
    Hour UInt8,
    ClientID UUID
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(VisitDate)
ORDER BY Hour;

PartitionId

PARTITION BY 后面跟分区键，也可以套一个函数，比如上面的例子将Date转为了月维度。clickhouse会按照分区将数据切分到分区键为名字的目录下。官方给出的建议是，大多数情况下，不需要分区键。即使需要使用，也不需要使用比月更细粒度的分区键。分区不会加快查询（这与 ORDER BY 表达式不同）。永远也别使用过细粒度的分区键。不要使用客户端指定分区标识符或分区字段名称来对数据进行分区（而是将分区字段标识或名称作为 ORDER BY 表达式的第一列来指定分区）。

分区键可以是整型，日期，字符串。

整型，比如用户直接指定某个整型作为分区键，或者套了某个会返回整型的函数。这类分区键会直接作为分区id。
日期，如果日期作为分区类型，会转为 YYYYMMDD 作为分区id。
如果不是上面两者类型会将分区键做hash后作为分区id。

最终我们分区的目录格式是这样的

1	{PartitionId}_{MinBlock}_{MaxBlock}_{Level}

blockNum

blockNum 在同一个MergeTree表内全局递增。n从1开始，每当新创建一个分区目录时，计数n就会累积加1。对于一个新的分区目录而言，MinBlockNum与MaxBlockNum取值一样，同等于n，例如
201905_1_1_0、201906_2_2_0以此类推。

1 2	fenix@fenixs:~$ sudo ls /var/lib/clickhouse/data/default/table_ttl2 all_10_10_0 all_9_9_0 detached format_version.txt

在clickhouse的使用过程中，随着每次insert都会或多或少的创建分区目录，ClickHouse会通过后台任务再将属于相同分区的多个目录合并成一个新的目录。已经存在的旧分区目录并不会立即被删除，而是在之后的某个时刻通过后台任务被删除（默认8分钟）。

MinBlockNum：取同一分区内所有目录中最小的
MinBlockNum值。
MaxBlockNum：取同一分区内所有目录中最大的
MaxBlockNum值。
Level：取同一分区内最大Level值并加1。

在system.parts 中可以查到我们创建的分区和具体的物理文件是一致的


fenix@fenixs:~$ sudo ls -alh /var/lib/clickhouse/data/default/table_ttl2/
总用量 39K
drwxr-x--- 6 clickhouse clickhouse  7 10月 21 14:46 .
drwxr-x--- 3 clickhouse clickhouse  3 10月 21 11:11 ..
drwxr-x--- 2 clickhouse clickhouse 12 10月 21 14:46 all_11_11_0
drwxr-x--- 2 clickhouse clickhouse 12 10月 21 14:46 all_12_12_0
drwxr-x--- 2 clickhouse clickhouse 12 10月 21 14:46 all_13_13_0
drwxr-x--- 2 clickhouse clickhouse  2 10月 21 11:11 detached
-rw-r----- 1 clickhouse clickhouse  1 10月 21 11:11 format_version.txt

fenixs :) select path from system.parts where table = 'table_ttl2' \G;

SELECT path
FROM system.parts
WHERE table = 'table_ttl2'

Query id: 6448ae48-7cef-4cf2-9fc5-1701207d4be4

Row 1:
──────
path: /var/lib/clickhouse/store/d90/d90e89a7-544b-4bf5-a143-505c103003aa/all_11_11_0/

Row 2:
──────
path: /var/lib/clickhouse/store/d90/d90e89a7-544b-4bf5-a143-505c103003aa/all_12_12_0/

Row 3:
──────
path: /var/lib/clickhouse/store/d90/d90e89a7-544b-4bf5-a143-505c103003aa/all_13_13_0/

3 rows in set. Elapsed: 0.002 sec.

删除/更新分区

删除分区

1	ALTER TABLE tb_name DROP PARTITION partition_expr

替换分区

1	ALTER TABLE B REPLACE PARTITION partition_expr FROM A

卸载/装载

执行卸载DETACH，执行后其数据会被转移到detached目录下，每个mergeTree表目录下都包含这个目录，这个目录下的文件不被clickhouse管理，这个功能长用于备份恢复数据

卸载

1	ALTER TABLE tb_name DETACH PARTITION partition_expr

装载

1	ALTER TABLE tb_name ATTACH PARTITION partition_exp

数据的增删改

insert

插入数据的几种方式

insert into … values (),(),()…

1	INSERT INTO [TABLE] [db.]table [(c1, c2, c3)] VALUES (v11, v12, v13), (v21, v22, v23), ...

insert into … from …

1	INSERT INTO [db.]table [(c1, c2, c3)] FORMAT format_name data_set

insert into … select …

update/delete

update

1	ALTER TABLE [db.]table UPDATE column1 = expr1 [, ...] WHERE filter_expr

delete

1 2	ALTER TABLE [db.]table [ON CLUSTER cluster] DELETE WHERE filter_expr

为什么这里有ALTER TABLE ,官方文档表示这是为了语义刻意设计的：

ALTER TABLE 的前缀使这个语法与其他大多数支持SQL的系统不同。它的目的是表明，与OLTP数据库中的类似查询不同，这是一个繁重的操作，不是为频繁使用而设计。

ByteDrift

Clickhouse学习笔记二