Clickhouse入门之语法

一、数据类型

1.1 整型

固定长度的整型，包括有符号整型或无符号整型。

整型范围（

-2n-1~2n-1-1）：

Int8 - [-128 : 127]

Int16 - [-32768 : 32767]

Int32 - [-2147483648 : 2147483647]

Int64 - [-9223372036854775808 : 9223372036854775807]

无符号整型范围（0~2n-1）：

UInt8 - [0 : 255]

UInt16 - [0 : 65535]

UInt32 - [0 : 4294967295]

UInt64 - [0 : 18446744073709551615]

使用场景： 个数、数量、也可以存储型 id。

1.2 浮点型

Float32 - float

Float64 – double

建议尽可能以整数形式存储数据。例如，将固定精度的数字转换为整数值，如时间用毫秒为单位表示，因为浮点型进行计算时可能引起四舍五入的误差。

1.3 Decimal 型

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

有三种声明：

➢ Decimal32(s)，相当于 Decimal(9-s,s)，有效位数为 1~9

➢ Decimal64(s)，相当于 Decimal(18-s,s)，有效位数为 1~18

➢ Decimal128(s)，相当于 Decimal(38-s,s)，有效位数为 1~38

s 标识小数位

使用场景： 一般金额字段、汇率、利率等字段为了保证小数点精度，都使用 Decimal进行存储

1.4 字符串

1. String

字符串可以任意长度的。它可以包含任意的字节集，包含空字节。

1. FixedString(N)

固定长度 N 的字符串，N 必须是严格的正自然数。当服务端读取长度小于 N 的字符串时候，通过在字符串末尾添加空字节来达到 N 字节长度。当服务端读取长度大于 N 的字符串时候，将返回错误消息。

与 String 相比，极少会使用 FixedString，因为使用起来不是很方便。

1.5 枚举类型

包括 Enum8 和 Enum16 类型。Enum 保存 'string'= integer 的对应关系。

Enum8 用 'String'= Int8 对描述。

Enum16 用 'String'= Int16 对描述。

1.6 时间类型

目前 ClickHouse 有三种时间类型

➢ Date 接受年-月-日的字符串比如 ‘2019-12-16’

➢ Datetime 接受年-月-日 时:分:秒的字符串比如 ‘2019-12-16 20:50:10’

➢ Datetime64 接受年-月-日 时:分:秒.亚秒的字符串比如‘2019-12-16 20:50:10.66’

日期类型，用两个字节存储，表示从 1970-01-01 (无符号) 到当前的日期值。

还有很多数据结构，可以参考官方文档： https://clickhouse.yandex/docs/zh/data_types/

1.7 数组

Array(T)：由 T 类型元素组成的数组。

T 可以是任意类型，包含数组类型。 但不推荐使用多维数组，ClickHouse 对多维数组

的支持有限。例如，不能在 MergeTree 表中存储多维数组。

二、表引擎

2.1 表引擎的使用（区分大小写）

表引擎决定了表数据是如何存储的，包含的内容如下：

1. 数据的存储方式和位置，写到哪里以及从哪里读数据

1. 支持哪些查询以及如何支持

1. 并发数据访问

1. 索引的使用

1. 是否可以执行多线程

1. 数据复制参数

2.2 TinyLog

以列式文件存储在磁盘上，不支持索引，没有并发控制，一般保存少量数据的小表，生产环境不建议使用

操作语句：

create table tinyLog_table_test (

id String,

name String ,

age int

)engine=TinyLog;

insert into tinyLog_table_test values ('1','zhangsan',10);

select * from tinyLog_table_test ;

2.3 Memory

内存引擎，数据将会以未压缩的原始形式直接保存在内存当中，重启服务器就会吊事，不支持索引，性能很高，不建议生产用。

2.4 MergeTree

clickhouse中最强大的表引擎，支持索引和分区，除了mergeTree外还有很多衍生引擎。

操作语句：

create table mergetree_table_test(

id String,

name String,

create_time datetime

)engine=MergeTree

partition by toYYYYMMDD(create_time)

PRIMARY key id

order by(id,name);

insert into mergetree_table_test values('1','zhangsan','2023-05-18 00:00:00');

insert into mergetree_table_test values('2','lisi','2023-05-17 00:00:00');

select * from mergetree_table_test ;

2.4.1 partition by 分区(可选)

和Hive的分区类似，为了减少数据的扫描范围，从而优化查询速度。

分区后跨分区的处理将会是并行处理。

任何一批数据写入后将会被写入临时分区，不会纳入一个已有的分区，写入后的某个时刻（大概10-15分钟）clickhouse将会自动将临时分区的数据合并到已有的分区中。也可手动执行optimize操作。

optimize table t_order_mt final;

2.4.2 primary key主键(可选)

主键，Clickhouse中的主键只提供了数据的一级索引，但是不是唯一索引，因此会存在主键相同的数据。

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

根据条件通过对主键进行某种形式的二分查找，能够定位到对应的 index granularity,避免了全表扫描。

index granularity： 直接翻译的话就是索引粒度，指在稀疏索引中两个相邻索引对应数据的间隔。ClickHouse 中的 MergeTree 默认是 8192。官方不建议修改这个值，除非该列存在大量重复值，比如在一个分区中几万行才有一个不同数据。

稀疏索引：

稀疏索引的好处就是可以用很少的索引数据，定位更多的数据，代价就是只能定位到索引粒度的第一行，然后再进行进行一点扫描。

2.4.3 order by（必选）

order by定义了数据在分区内是按照哪些字段进行有序保存的。

order by 是 MergeTree 中唯一一个必填项，甚至比 primary key 还重要，因为当用户不设置主键的情况，很多处理会依照 order by 的字段进行处理（比如后面会讲的去重和汇总）。

要求：主键必须是 order by 字段的前缀字段。

比如 order by 字段是 (id,sku_id) 那么主键必须是 id 或者(id,sku_id)

2.4.4 二级索引

二级索引就是普通索引

GRANULARITY N 是设定二级索引对于一级索引粒度的粒度：

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);

2.4.5 数据TTL

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

列级TTL

create table t_order_mt4(

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);

insert into t_order_mt4 values

(106,'sku_001',1000.00,now()),

(107,'sku_002',2000.00,now()),

(110,'sku_003',600.00,now());

optimize table t_order_mt4 final;

select * from t_order_mt4;

表级TTL

下面的这条语句是数据会在 create_time 之后 10 秒丢失

alter table t_order_mt3 MODIFY TTL create_time + INTERVAL 10 SECOND;

2.5 ReplacingMergeTree

ReplacingMergeTree是MergeTree的延伸，只是相比MergeTree多了一个去重的功能，因为MergeTree可以设置主键，但是主键并没有唯一约束，还是会重复，如果想去掉重复的数据就使用ReplacingMergeTree。

去重时机：去重的时机是在合并的过程中进行去重，没有合并操作是不会进行去重的。

去重范围：只会在分区内部去重，不能执行跨分区的去重。

2.6 SummingMergeTree

适用于以维度进行汇总聚合的场景：

1. 聚合只会在相同分区内聚合。

1. 以order by 列作为维度列进行聚合。

1. 聚合后其他列按插入顺序保留第一行。

注意事项：

1. 不能直接执行select xx from xx where xx 来得到汇总值，必须使用sum函数，因为会得到还没有合并的数据。

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)

insert into t_order_smt values

(101,'sku_001',1000.00,'2020-06-01 12:00:00'),

(102,'sku_002',2000.00,'2020-06-01 11:00:00'),

(102,'sku_004',2500.00,'2020-06-01 12:00:00'),

(102,'sku_002',2000.00,'2020-06-01 13:00:00'),

(102,'sku_002',12000.00,'2020-06-01 13:00:00'),

(102,'sku_002',600.00,'2020-06-02 12:00:00');

select * from t_order_smt;

-- 如果多次插入没有合并，则可以手动执行合并

OPTIMIZE TABLE t_order_smt FINAL;

三、SQL操作

只记录和mysql不一致的场景

3.2 Update 和 Delete

在Clickhouse中update和delete属于比较重的语法，被称为Mutation查询，虽然可以实现更新和删除，但是不支持事务，而且每次更新或删除后都会放弃原有的分区，重新进行分区。

1、update

alter table t_order_smt update total_amount=toDecimal32(2345678.99,2) where id = 102;

2、delete

alter table t_order_smt delete where sku_id = 'sku_001'

3.4 alter操作

1. 新增字段

alter table table_name add column columnname String after col1;

1. 修改字段类型

alter table table_name modify column newcolumn String;

1. 删除字段

alter table table_name drop column column_name

3.5 导出数据

更过格式查看官网：https://clickhouse.tech/docs/en/interfaces/formats/

clickhouse-client --query "select * from t_order_mt where create_time='2020-06-01 12:00:00'" --format CSVWithNames> \

/opt/module/data/rs1.csv

四、副本

副本的目的主要是保障数据的高可用性，即使一台 ClickHouse 节点宕机，那么也可以从其他服务器获得相同的数据。

https://clickhouse.tech/docs/en/engines/table-engines/mergetree-family/replication/

4.1副本写入流程

4.2 配置步骤

1. 启动zookeeper

1. 在主服务器的/etc/clickhouse-server/config.d 目录下创建一个名为 metrika.xml 的配置文件,内容如下（也可以直接更改config.xml中的zookeeper模块配置）：

<?xml version="1.0"?>

<yandex>

<zookeeper-servers>

<node index="1">

<host>hadoop162</host>

<port>2181</port>

</node>

<node index="2">

<host>hadoop163</host>

<port>2181</port>

</node>

<node index="3">

<host>hadoop164</host>

<port>2181</port>

</node>

</zookeeper-servers>

</yandex>

1. 同步到其他节点

sudo ~/bin/xsync /etc/clickhouse-server/config.d/metrika.xml

1. 在主服务器的/etc/clickhouse-server/config.xml 中增加

<zookeeper incl="zookeeper-servers" optional="true" />

<include_from>/etc/clickhouse-server/config.d/metrika.xml</include_from>

1. 将配置文件分发到其他节点

sudo ~/bin/xsync /etc/clickhouse-server/config.xml

1. 重启每个节点上的服务

五、分片集群

副本是为了保证数据的可用性，但是没有对数据进行横向扩容，每台节点都保存了全量数据，因此得借用数据分片的概念。

通过分片把一份完整的数据进行切分，不同的分片分布到不同的节点上，再通过distributed表引擎把数据拼接起来一同使用。

Distributed表引擎本身不村塾数据，只是一种中间件，通过分布式逻辑表来写入、分发、路由来操作多台节点不同分片的分布式数据。

5.1 集群写入流程（3分片2副本共6个节点）

5.2 集群读取流程（3分片2副本共6个节点）