Clickhouse基础（一）

操作命令：

sudo clickhouse start
sudo clickhouse restart
sudo clickhouse status进入clickhouse
clickhouse-client -m

CREATE TABLE db_13.t_assist
(`modelId` UInt64,`taskId` UInt64,`testNo` String,`tdId` UInt64,`eventDay` String,`eventDaytime` UInt64,`eventBatch` UInt64,`eventId` UInt64,`imageIndex` String,`gcz_50` String,`ymz_50` String,`gcz_51` String,....`gcz_89` String,`ymz_89` String
)
ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/86ee6ab8-6acb-46e8-b80f-a1786df37850/{shard}','{replica}')
PARTITION BY tdId
ORDER BY (modelId,tdId,eventBatch,eventDay,eventId)
SETTINGS index_granularity = 8192

1）表引擎类型

ReplicatedReplacingMergeTree

2）PARTITION BY tdId

表示按照tdId【通道id】进行分区存储

3）index_granularity = 8192

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

2、表引擎

ck中的表引擎类似于mysql中的表引擎

1.1 MergeTree

Clickhouse中最强大的表引擎当属MergeTree（合并树）引擎及该系列（*MergeTree）中的其他引擎，支持索引和分区，地位可以相当于innodb在Mysql。还基于MergeTree衍生很多其他有特色的引擎。

docker中安装的clickhouse，需要进入ck命令

# docker exec -it clickhouse /bin/bash
# /bin/clickhouse-client --password输入命令进入

查看default库下的表

2.1 建表关键字说明

1、建表语句：

CREATE TABLE default.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)
SETTINGS index_granularity = 8192

插入数据：

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

2、分区：PARTITION BY toYYYYMMDD(create_time) 【可选】

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

如果不填，只会使用一个分区

查看表出现分片的情况

为什么分为两片数据？

因为建表中通过年月月进行分区存储

3、主键id出现重复：PRIMARY KEY id【可选****】

Clickhouse中的PRIMARY KEY语法不会将主键做唯一约束，可重复

3、排序：ORDER BY (id,sku_id) 【必选】

排序是分区内进行排序的

注意：ORDER BY 是必须要的

4、目录说明

文件夹名称说明：

20200601_1_1_0

20200601：就是按照建表语句中的年月日进行分区存储

（2）metadata：建表sql语句【元数据】

5、并行

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

6、数据写入与分区合并

  <font style="background-color:#FBDE28;">任何一个批次的数据写入都会产生一个临时分区，不会纳入任何一个已有的分区。写入 后的某个时刻（大概 10-15 分钟后），ClickHouse 会自动执行合并操作（等不及也可以手动 通过 optimize 执行），把临时分区的数据，合并到已有分区中</font>

optimize table 表名 final;

再次插入相同的数据

从命令行查询表数据

查看数据并没有纳入任何分区，发现出现四个分区及对应的四个分区文件

手动 optimize 之后

2.2 二级索引

2.3 ReplacingMergeTree引擎

ReplacingMergeTree 是 MergeTree 的一个变种，它存储特性完全继承 MergeTree，只是多了一个去重的功能。尽管 MergeTree 可以设置主键，但是 primary key 其实没有唯一约束的功能。如果你想处理掉重复的数据，可以借助这个 ReplacingMergeTree。该表引擎只能保证最终一致性。

（1）创建表

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() 填入的参数为版本字段，重复数据保留版本字段值最大的。如果不填版本字段，默认按照插入顺序保留最后一条。

（2）向表中插入数据

内部会自动合并

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

（3）执行一次查询

首先按照日期时间进行分区，再通过id,sku_id两个字段分组去重，默认按照插入顺序保留最后一条。

2.4 SummingMergeTree

对于不查询明细，只关心以维度进行<font style="color:#DF2A3F;">汇总聚合</font>结果的场景。如果只使用普通的MergeTree 的话，无论是存储空间的开销，还是查询时临时聚合的开销都比较大。 ClickHouse 为了这种场景，提供了一种能够“预聚合”的引擎 SummingMergeTree。

1、创建表

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

2、插入数据

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

3、查询

将id、sku_id数据相同，并且在同一分区的数据，统计total_amount的数值

3、SQL操作

3.1 insert与mysql语法一致

3.2 Update和delete

生产环境不建议使用

（1）删除操作

alter table t_order_smt delete where sku_id ='sku_001';// 清空表
alter table t_order_smt delete where 1 = 1;

（2）修改操作

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

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

3.3 alter操作

同 MySQL 的修改字段基本一致

1）新增字段

alter table t_order_smt add column testNo String after create_time;

2）修改字段类型

alter table t_order_smt modify column testNo UInt32;

3）删除字段

alter table t_order_smt drop column testNo;
select * from t_order_smt;

4）导出数据

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

4、副本

<font style="background-color:#FBDE28;">副本的目的主要是保障数据的高可用性，即使一台 ClickHouse 节点宕机，那么也可以从 其他服务器获得相同的数据。</font>

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

4.1 副本写入流程

clickhouse中没有主从之分，人人平等，任意一个节点收到数据后，都会向其他节点同步数据。

4.2 配置步骤

（1）启动zookeeper集群

（2）在 hadoop102 的/etc/clickhouse-server/config.d 目录下创建一个名为 metrika.xml 的配置文件,内容如下：

修改config.xml配置文件

vi /etc/clickhouse-server/config.xml

添加以下配置

<zookeeper incl="zookeeper-servers" optional="true" / >
<zookeeper><node><host>192.168.58.135</host><port>2181</port></node><node><host>192.168.58.136</host><port>2181</port></node><node><host>192.168.58.137</host><port>2181</port></node>
</zookeeper>