clickhouse

1.入门

1.2列式数据库

clickHouse将数据划分为多个partition，每个partition再进一步划分为多个index granularity（索引粒度），然后通过多个cpu核心分别处理其中的一部分来实现并行数据处理。在这种设计下，单条sql就能利用整个机所有CPU,极致的并行处理能力，极大的降低了查询延时。所以，clickhouse即使对于大数据量的查询也能化整为零平行处理，但是有一个弊端就是对于单条查询使用多cpu就不利于并发多条查询，所以对已高qps的查询业务，clickHouse并不是强项。

使用clickHouse避免关联操作

1.2 列式存储更适合查询的场景

对于列的聚合、计数、求和等统计操作原因优于行式存储

由于某一列的数据类型是相同的，针对于数据存储更容易进行数据压缩，每一列选择更优的数据压缩算法，大大提高了数据的压缩比重。

由于数据压缩比更好，一方面节省了磁盘空间，另一方面对于cache也有了更大的发挥空间。

1.3DBMS的功能

几乎覆盖了标准sql的大部分语法，包括DDL、DML,以及配套的各种函数、用户管理、权限管理、数据的备份与恢复

1.4多样化引擎

clickhouse和mysql类似，把表级的存储引擎插件化，根据表的不同需求可以设定不同的存储引擎，目前包括合并树、日志、接口、其他四大类20多种引擎。

1.5高吞吐写入能力

clickhouse采用类LSM Tree的结构，数据写入后期在后台Compaction。通过类LSM tree的结构，clickhouse在数据导入时全部是顺序append写，写入后数据段不可更改

数据分区与线程并行

clickhouse将数据划分为多个partition，每个partition再进一步划分为多个index granularity 索引粒度，然后通过多个cpu核心分别处理其中的一部分来实现并行数据处理。在这种情况下，单条query就能利用整机所有cpu，极致的并行处理能力，极大的降低了查询延时。所以 clickhouse即使对于大量数据的查询也能够化整为零平行处理，但是有一个弊端就是单条查询使用多cpu，就不利用同时并发多条查询。所以对于高qps的查询业务，clickhouse并不是强项。

clickhouse 做关联查询 性能不快，避免关联查询

1.6 安装

先关闭防火墙

ulimit -a

centos取消打开文件数限制

/etc/security/limits.conf文件末尾添加

* soft nofile 65536

* hard nofile 65536

* soft nproc 131072

* hard nproc 131072

*代表所有用户和用户组

highly@highly 表示highly用户和highly用户组

soft 、hard  软 硬

- 表示软、硬 一起配

/etc/security/limits.d/20-nproc.conf尾添加

* soft nofile 65536

* hard nofile 65536

* soft nproc 131072

* hard nproc 131072

退出，重新登录，配置即可生效

安装依赖 yum install -y libtool

yum install -y *unixODBC*

centos 取消SELINUX

/etc/selinux/config SELINUX=disabled

可以临时生效:setenforce  0 0表示关闭

2.官网

www.clickhouse.tech

安装需要四个rpm包:client、static、dbg、server

rpm 安装 rpm -ivh *.rpm

rpm安装路径，默认/etc/clickhouse

/var/lib/clickhouse/

/var/log/clickhouse

/usr/bin

配置文件:/etc/clickhouse

config.xml 服务的相关配置
users.xml 参数配置 如CPU,内存

config.xml:

搜索listen 打开

 3.启动

clickhouse start;

查看启动状态:clickhouse status;

客户端连接: clickhouse-client -m

-m 代表可以换行  

；表示执行

2.数据类型

2.1 整型

有符号、无符号

整型范围:

Int8、Int16、Int32、Int64

有符号:

Int8: -128-127

无符号:0-255

2.2 浮点型

2.3布尔型

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

2.4 decimal型

2.5 字符串

String 、FixedString(N）固定长度

2.6 枚举类型

Enum8、Enum16类型

create table t_enum(
 x Enum8('hell0'=1,'world'=2)) ENGINE=TinyLog;

Insert into t_enum values('hell0'),('world'),('hell0');
查看对应的值:
select cast(x,'Int8') from t_enum;

2.7 时间类型

2.8 数组

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

T是任意类型，包含数组类型，但不推荐使用多维数组，clickhouse对多维数组的支持有限；如不能在MergeTree表中存储多维数组

toTypeName(字段) 表示把当前字段的类型打印出来

select array(1,2) as x,toTypeName(x);

select [1,2] as x,toTypeName(x);

同一个数组的类型得一样

使用Nullable几乎总是对性能产生负面影响，在设计数据库时要记住这一点。

3.表引擎

表引擎是clickhous的一大特色，可以说，表引擎决定了如何存储表的数据包括：

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

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

并发数据访问

索引的使用(如果存在)

是否可以执行多线程请求

数据复制参数

引擎的名称大小写敏感；

表引擎的使用方式必须显示在创建表时定义该表使用的引擎，以及引擎使用的相关参数

3.1TinyLog

以列文件的形式保存在磁盘上，不支持索引，没有并发控制，一般保存量少的数据的小表

3.2Memory

内存引擎，数据以未压缩的原始形式直接保存在内存当中，服务器重启数据就会消失，读写操作不会相互阻塞，不支持索引，简单查询有非常非常高的性能表现

3.3 MergeTree

clickHouse中最强大的引擎当属MergeTree合并树引擎及该系列中的其他引擎，支持索引和分区，地位相当于innodb之于mysql。基于MergeTree，还衍生出了很多小弟，也是非常有特色的引擎。

primary key(id) 主键，但是不会帮助建立唯一约束

primary key 可以重复

3.4 partition by 分区（可选）

作用：目的主要是降低扫描的范围，优化查询速度

如果不填，只会使用一个分区，分区目录：MergeTree是以列文件+索引文件+表定义文件组成的，但是如果设定了分区那么这些文件就会保存到不同的分区目录中。

order by 是分区内排序 order by 是必须的

3.5并行

分区后，面对涉及跨分区的查询统计，clickhouse会以分区为单位并行处理

元数据：

        bin文件：数据文件

        mrk文件：标记文件

标记文件在idx索引文件和bin数据文件之间起到了桥梁作用

以mrk2结尾的文件，表示该表启用了自适应索引间隔。

primary.idx文件，主键索引文件，用于加快查询效率

minmax_create_time.idx 分区键的最大值最小值

checksums.txt         校验文件，用于校验各个文件的的正确性，存放各个文件的size和hash值。

3.6数据写入与分区合并

任何一个批次的数据写入都会产生一个临时分区，不会纳入任何一个已有的分区，

写入后的某个时刻（大概10-15分钟后），clickHouse会自动执行合并操作（等不及也可以手动通过optimize执行），把临时分区的数据，合并到已有分区中。

optimize table xxx final;

/var/lib/clickhouse 下

data 目录 数据存储的目录

metadata 表的存储目录

第一个分区的值，第二个数字表示块的最小编号，第三个数字表示块的最大编号，第四个表示合并的等级

进入分区:

 分区值-最小分区块编号-最大分区块编号-合并层级

mrk文件:标记文件，标记文件在idx索引文件和bin文件之间起到了桥梁的作用

以mrk2结尾的文件，标识该表启用了自适应索引间隔。

primary.idx文件:主键索引文件，用于加快查询效率

minmax_create_time.idx ：分区键的最大最小值

checksums.txt: 校验文件，用于校验各个文件的正确性，存放各个文件的size和hash值。

---

partitionId:

数据分区id生成规则：数据分区规则由分区id决定，分区id由partition by分区键决定，根据分区键字段类型，ID生成规则可分为：

未定义分区键：没有定义PARTITION BY,默认生成一个目录名为all的数据分区，所有数据均存在all目录下。

整型分区键:

分区键为整型，那么直接用该整型值的字符串作为分区id

日期类分区键：

分区为日期类型，或者可以转化为日期类型。

其他类型分区键:

String、Float类型等，通过128位的Hash算法取hash值作为分区ID.

MinBlockNum:

        最小分区块编号，自增类型，从1开始向上递增，每产生一个新的目录分区就向上递增一个数字

MaxBlockNum:

        最大分区编号，新创建的分区MinBlockNum等于MaxBlockNum的编号。

create table t_oder_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,'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 13:00:00),
(102,'sku_002',600.00,'2020-06-02 12:00:00)

3.7 order by

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

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

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

3.8 二级索引

目前在clickhouse的官网上二级索引的功能在v20.1.2.4之前是被标注为实验性的，在这个版本之后默认是开启的。

老版本使用二级索引前需要增加设置：

set allow_experimental_data_skipping_indicates=1;

建立语法：INDEX 名称 列表 TYPE minmax GRANULARITY 5

GRANULARITY是设定二级索引对于一级索引粒度的粒度。5指粒度

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

---

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

clickhouse-client --send_logs_level=trace 这个值之间在客户端打印日志

3.9 数据TTL

TTL:Time to Live,MergeTree提供了可以管理数据的表或者列的生命周期的功能

create table t_order_mt(
    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);


表级TTL
alter table t_order_mt MODIFY TTL create_time + INTERVAL 10 SECOND;
涉及判断的字段必须是Date或DateTime类型，推荐使用分区的日期字段。

ttl后面的字段不能是主键字段。

ttl可以是表级别的或者字段级别的

alter table t_order modify ttl create_time+Interval 10 seconds

涉及判断的字段必须是date或datetime类型，推荐使用分区的日期字段

3.10ReplacingMergeTree(去重最终一致性)

是MergeTree的一个变种，它存储特性完全继承MergeTree，只是多了一个去重的功能，尽管MergeTree可以设置主键，但是Primary key其实没有唯一约束的功能，如果你想处理调重复的数据，可以借助ReplcaingMergeTree

去重时机:数据的去重只会在合并的过程中出现，合并会在未知的时间在后台进行，所以无法预先做出计划，有一些数据可能仍未被处理。

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

create table t_order_mt(
    id UInt32,
    sku_id String,
    total_amount Decimal(16,2),
    create_time DateTime
)engine=RelpacingMergeTree (create_time)
partition by toYYYYMMDD(create_time)
primary key(id)
order by(id,sku_id);
ReplcaingMergeTree()填入的参数为版本字段，重复数据保留版本字段值最大的，如果不填版本字段，默认按照插入顺序保留最后一条。

通过测试得到结论：

实际上是使用order by字段作为唯一键

去重不能跨分区

只有同一批插入或合并分区才会进行去重

认定重复的数据保留，版本字段值最大的

如果版本字段相同按插入顺序保留最后一笔

3.11SummingMergeTree

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

通过结果可以得到以下结论：

以SummingMergeTree()中指定的列作为汇总数据列

可以填写多列，必须数字列，如果不填，以所有非维度列且为数字列的字段为汇总数据列

以order by的列为准，作为纬度列

其他的列按插入顺序保留第一行

不在一个分区的数据不会被聚合

聚合的时候，非聚合字段的其他字段取的是最早的一条

3.12开发引擎的选择

4. sql

clickhouse提供了update和delete的能力，这类操作被称为Muation 查询，它可以看做是alter的一种。虽然可以实现修改和删除，但是和一般的oltp数据库不一样，Mutation语句是一种很"重"的操作，而且不支持事务。

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

删除操作:

alter table t_order delete where sku_id='001'

 修改操作:

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

 由于操作比较“重”，所以mutation语句分两步执行，同步执行的部分其实只是进行新增数据新增分区和并把旧分区打上逻辑上的失效标记。直到触发分区合并的时候，才会删除旧数据释放磁盘空间，一般不会开放这样的功能给用户，由管理员完成。

4.1 insert

基本与标准sql(mysql)基本一致

标准：

insert into [table name] values(...),(...)

从表到表的插入

insert into [tablename] select a,b,c from tablename2

4.2 update 和 delete

clickhose 提供连了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;

4.3 查询操作

clickhouse基本上与标准sql差别不大

支持子查询

支持CTE(common table expression 公用表表达式 with 子句)

支持各种join，但是join操作无法使用缓存，所以即使是两次相同的join语句，clickhouse也会被视为两条新sql

4.4 多维分析函数

rollup 上卷；cube 多维分析；total总计；

5.副本

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

 副本只能同步数据，不能同步表结构，所需需要我们在每台机器上手动创建表

必须使用ReplicatedMergeTree引擎

6.分片集群 

副本虽然能够提高数据的可用性，降低丢失风险，但是每台服务器上必须容纳全量数据，对数据的横向扩容没有解决。要解决数据水平切分的问题，需要引入 分片的概念，通过分片把一份完整的数据进行切分，不同的分片分布到不同的节点上，再通过Distributed表引擎把数据拼接起来一同使用。Distributed表引擎本身不存储数据，有点类似Mycat之于mysql，成为一种中间件，通过分布式逻辑表来写入、分发、路由来操作多台节点不同分片的分布式数据。

注意：Clickhous的集群是表级别的，实际企业中，大部做了高可用，但是没用分片，避免降低查询性能以及操作集群的复杂性。

建表语句：

create table st_table_mt on cluster gm_cluster(配置的集群名称)(
    id UInt32,
    sku_id String,
    total Decimal(16,2),
    create_time DateTime    


)engine = ReplicationMergeTree('clickhouse/tables/{shard}/st_table_mt_1109','{replica}')
partition by toYYYYMMDD(create_time)
primary key(id),
order by(id,sku_id);

 创建分布式表：

create table st_table_mt_dist on cluster gm_cluster(
    id UInt32,
    sku_id String,
    total Decimal(16,2),
    create_time DateTime


)enging=Distributed(gm_cluster,default,st_table_mt,hiveHash(sku_id));

参数含义：

        Distributed(集群名称,库名,本地表表名,分片键)

分片键必须是整型数字，所以用hiveHash函数转换，也可以用rand();

2.高级

1.基本语法

EXPLAIN [AST |SYNTAX |PLAN|PIPELINE ] [setting = value,....]

select ...[format...]

PLAN 用于查看执行计划，默认值

header 打印计划中各个步骤的head说明，默认关闭，默认值0

description 打印计划中各个步骤的描述，默认开启 默认值1

actions 打印计划中各个步骤的详细信息，默认关闭，默认值是0

AST:用于查看语法树

SYNTAX: 用于优化语法；

PIPELINE:用于查看PIPEIINE计划

  header: 打印计划中各个步骤的head说明，默认关闭

 graph: 用DOT图形语言描述管道图，默认关闭，需要查看相关的图形需要配合grahviz查看；

actions:如果开启了graph，紧凑打印打，默认开启

2.数据类型

时间字段的类型：

建表时能用数值型或日期时间型表示的字段就不要用字符串，全String类型在以hive为中心的数仓建设中常见，但clickhouse环境不受此影响。

虽然clickhouse底层将DateTime存储为时间戳Long类型，但不建议存储Long类型，因为DateTime不需要经过函数转换处理，执行效率高，可读性好。