clickhouse学习笔记(一)
2024-07-09 18:28:54 # clickhouse # 学习笔记

clickhouse学习笔记(一)

目录

特点

列式存储

  • 行式存储

    • 这种行式子存储的方式如果想要查询一个人的属性直接查就好,但是如果想要所有人的总年龄就需要查全表了
  • 列式存储

    • 采用列式存储的时候如果想要查询所有人的年龄把年龄这一列拿出来就好了
  • 列式存储好处

    • 列式存储对于列的操作会优于行存储

由于某一列的数据类型相同,所以对于这种一列的数据更加容易进行压缩
数据压缩更好节省磁盘空间

SQL语法

多样化引擎

高吞吐

  • LSM Tree结构

  • 顺序写数据,写入后数据不可更改

数据分区和并行处理

  • 将数据划分成多个Partition,每个Partition划分成多个索引,然后多个cpu核分别处理一部分来并行处理

不足

  • 由于clickhouse现在是根据多个cpu核心来处理数据的,这就会导致在高并发的情况下也需要cpu核心来处理就会造成资源抢占。大量并发查询时,频繁的上下文切换会增加CPU的负载,影响整体性能

数据类型

Decimal

  • 有符号的浮点数,可以在加减乘的运算中保持精度

枚举类型

  • Enum保存”String=Integer”的关系
  • Enum用“String”=Int8描述
  • Enum用“String=Int16”描述
1
2
3
4
5
CREATE TABLE t_enum
(
x Enum8('hello' = 1, 'world' = 2)
)
ENGINE = TinyLog;
1
INSERT INTO t_enum VALUES ('hello'), ('world'), ('hello');

这个x列只能存储类型定义中列出的值hello和world,如果要保存其他的值就会抛出异常

  • 如果要查看对于行的数值就需要把这个Enum值转换为整数的类型
    1
    SELECT CAST(x,'Int8') from t_enum;

alt text

数组

Array(T) T可以是任意类型,包含数组类型,但是不推荐使用多维数组

  1. 使用array函数创建数组
    1
    SELECT array(1, 2) AS x, toTypeName(x) ;
  2. 使用方括号创建数组
    1
    SELECT [1, 2] AS x, toTypeName(x);

表引擎

表引擎是clickhouse的一大特色

表引擎决定了如何存储表的数据:
- 数据的存储方式和位置,写到哪里以及从哪里读取数据。
- 支持哪些查询以及如何支持。
- 并发数据访问。
- 索引的使用(如果存在)。
- 是否可以执行多线程请求。
- 数据复制参数。

需要注意的是引擎的名称大小写比较敏感

TinyLog

列文件的形式,不支持索引,没有并发控制。一般保存少量数据的小表

1
CREATE TABLE t_tinyLog (id String,name String) engine = TinyLog;

Memory

内存引擎,数据写入后不进行压缩写入到内存中,服务器重启了数据就消失了,有非常高的性能

MergeTree

Clickhouse中最强大的表引擎,合并树引擎和该系列中的其他的引擎。支持索引和分区,非常好用

1
2
3
4
5
6
7
8
9
10
CREATE TABLE t_mergeTree (
id String,
sku_id String,
total_amount Decimal(10,2),
create_time DateTime
)
engine = MergeTree
PARTITION BY toYYYYMM(create_time)
PRIMARY KEY (id)
ORDER BY (id,sku_id);

MergeTree其实还有很多的参数,但是只有Partition by ,Oreder by,Primary key这三个参数是最重要的

Partition by分区

分区目录

MergeTree是以列文件+索引文件+表定义文件组成的,设置了分区的话会按照分区就文件放到不同的分区下面
alt text

这里面我们熟悉的有columns.txt这里是文件的列文件

primary.idx就是主键的索引文件

count.txt是表数据量的统计文件

并行

分区后,面对跨分区的查询,clickhouse会以分区为单位处理

数据写入与分区合并

每个批次的数据写入的时候会先写到一个临时的分区,然后大概10多分钟之后再进行合并操作,把临时分区的数据合并到已有的分区

这个时候写入的数据还没有进行分区
alt text

我们手动执行一下分区的操作

1
optimize table t_mergeTree final;

手动执行后,数据就进行了分区合并

alt text

Primary key主键

primary key是order by的语法糖,primary key是一个显式的关键字的主键

order by后面的字段也叫做主键

ClickHouse中的主键,和其他数据库不太一样,它只提供了数据的一级索引,但是却不是唯一约束。这就意味着是可以存在相同主键的数据的。

ClickHouse 中的主键主要用于排序和优化查询,而不是唯一性约束。允许相同主键值的存在,主要目的是提高查询性能和灵活性,以适应大规模数据分析的需求。

比方说

1
2
3
4
5
6
7
8
CREATE TABLE example_table
(
id UInt32,
name String,
value Float32
)
ENGINE = MergeTree()
ORDER BY id;

这里的id就是主键,但是没有唯一性约束,我们可以向这个表中插入id相同的多条数据

我们一般把主键设置在where条件中

我们创建了一个表后会发现后台会给我们维护一个索引粒度,这里先说一下稀疏索引

稀疏索引

我们每个数据都有一个索引,比如有索引abcdefg,我们取adg为稀疏索引,隔一段取一个做稀疏索引,前面的这个索引粒度就是这个每隔一段隔了多久。这样当我们需要查询c这个数据的时候我们就知道在a和d之间了,我们顺序去找c这个索引的数据

Order by(必选)

这个是必选的,必须添加这个order by字段

分区内排序

因为我们是基于稀疏索引做的,如果客户不指定主键,那么久使用order by字段作为主键,如果指定了主键,那么就用指定的主键,保证稀疏索引有序才行

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

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

二级索引

20.1.2.4版本之前需要开启一下这个二级索引才行

1
set allow_experimental_data_skipping_indices=1;
1
2
3
4
5
6
7
8
9
10
11
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

指定加索引的字段是total_amount,命名为a,
二级索引的类型有很多种,最常用的minmax
GRNULARITY 5是指定了粒度

这里的GRNULARITY 5是指定的是每隔5个数据取一个索引,也就是每隔5条数据取一个索引

示例

1
2
3
4
5
6
7
insert into  t_order_mt2 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');

alt text

数据的TTL

TTL就是数据表的生命周期

列级别的TTL

1
2
3
4
5
6
7
8
9
10
create table t_order_mt3(
id UInt32,
sku_id String,
total_amount Decimal(16,2) TTL create_time+interval 20 SECOND,
create_time Datetime
) engine =MergeTree
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id, sku_id);

这里的TTL create_time+interval 20 SECOND是达到这个数据的create_time+20秒后,数据就会被删除

1
2
3
4
insert into  t_order_mt3 values
(106,'sku_001',1000.00,'2024-07-08 09:19:00'),
(107,'sku_002',2000.00,'2020-06-12 22:52:30'),
(110,'sku_003',600.00,'2020-06-13 12:00:00');

alt text

表级别的TTL

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

这里的表级别的TTL就是在这个表内每条数据都会在创建时间+10秒后删除

ReplacingMergeTree

这个就是完全继承了Merge,只是多了一个去重的功能。为了解决,MergeTree的主键primary key没有唯一性的约束,所以这个可以加一个去重,去掉重复的数据

但是这个的数据去重只会在合并的过程中进行去重,所以可能有一些数据仍未被处理

去重范围:表经过了分区,去重只会在分区内部进行去重,不能执行跨分区的去重

ReplacingMergeTree只适合后台清除重复数据节省空间,不保证没有重复数据出现

1
2
3
4
5
6
7
8
9
10
CREATE TABLE t_order_rmt(
id Int32,
sku_id String,
total_amount Decimal(15,2),
create_time DateTime
)
engine = ReplaceMergeTree(create_time)
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id,sku_id);
1
2
3
4
5
6
7
insert into  t_order_rmt 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');

alt text

最后结论

  • 实际上是使用order by 字段作为唯一键
  • 去重不能跨分区
  • 只有同一批插入(新版本)或合并分区时才会进行去重
  • 认定重复的数据保留,版本字段值最大的
  • 如果版本字段相同则按插入顺序保留最后一笔

SummingMergeTree

预聚合,这种merge满足只关心维度汇总聚合的结果的场景

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

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

这里其实就是在这个表之前就进行了聚合,所以查询的时候直接查出来就行了,节省资源

分区内聚合,以order by列为准

SQL操作

这里的sql操作,对和其他数据库相同的内容不做描述,只描述不同的内容

Update And Delete

Clickhouse最好做批量更改,不建议一点点改

删除:alter table t_order_smt delete where sku_id ='sku_001';

修改数据:alter table t_order_smt update total_amount=toDecimal32(2000.00,2) where id =102;

这里的删除和更新操作,都比较重,因为这里的操作是两步的,首先增加新分区,把旧分区打上失效的标记。等触发分区合并的时候才会删除数据释放磁盘空间

多维分析函数

rollup:上卷

group by

group by a

group by a,b

只能是按照顺序来group by

cube:多维分析

group by

group by a

group by a,b

group by b

什么顺序都可以group by

total:总计

group by

group by a,b

示例

1
2
3
4
5
6
7
8
9
10
11
12
insert into  t_order_mt values
(101,'sku_001',1000.00,'2020-06-01 12:00:00'),
(101,'sku_002',2000.00,'2020-06-01 12:00:00'),
(103,'sku_004',2500.00,'2020-06-01 12:00:00'),
(104,'sku_002',2000.00,'2020-06-01 12:00:00'),
(105,'sku_003',600.00,'2020-06-02 12:00:00'),
(106,'sku_001',1000.00,'2020-06-04 12:00:00'),
(107,'sku_002',2000.00,'2020-06-04 12:00:00'),
(108,'sku_004',2500.00,'2020-06-04 12:00:00'),
(109,'sku_002',2000.00,'2020-06-04 12:00:00'),
(110,'sku_003',600.00,'2020-06-01 12:00:00');

with rollup
1
select id , sku_id,sum(total_amount) from  t_order_mt group by id,sku_id with rollup;

alt text

with cube
1
select id , sku_id,sum(total_amount) from  t_order_mt group by id,sku_id with cube;

alt text

with total
1
select id , sku_id,sum(total_amount) from  t_order_mt group by id,sku_id with totals;

alt text

导出数据

1
clickhouse-client --query "select * from t_order_mt where create_time='2020-06-01 12:00:00'" --format CSVWithNames> /opt/module/data/rs1.csv

副本(高可用)

具体查看教程

副本没有主从关系

分片集群

副本虽然可以提高数据的可用性,降低丢失数据的风险,但是数据的横向扩容问题还是没有解决,分布式架构的问题还是没有解决。

这里引入一个分片的概念,分片就是把一份数据切成好几片,不同的分片在不同的节点上,然后再用ddistributed表引擎把数据拼接起来用

这个方式具体使用的时候再进行查看,可以配置类似于分布式的分片集群