ClickHouse

[猫TOU馨]

ClickHouse简介

​ ClickHouse开源列式存储数据库（DBMS），使用C++语言编写，主要用于在线分析处理查询（OLAP），能够使用SQL查询实时生成分析数据报告。

传统行式存储

ID	NAME	AGE
1	张三	18
2	李四	22
3	王五	34

磁盘中的存储格式

1	张三	18	2	李四	22
3	王五	14	。。。

列式存储的存储格式

1	2	3	张三	李四	王五
18	22	34	。。。

列式存储的好处：

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

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

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

DBMS的功能

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

多样化引擎

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

高吞吐写入能力

​ ClickHouse采用类LSM Tree（时间戳概念，拿最新的时间戳数据展示，老的数据在合并时删除）的结构，数据写入后定期在后台Compaction。通过类LSM tree的结构，ClickHouse在数据导入时全部是顺序（追加）append写，写入后数据段不可更改（打版本号标记合并后删除），在后台commpaction时也是多个段（归并排序后）merge sort后顺序写入回磁盘，顺序写的特性，充分利用了磁盘的高吞吐能力，即便在HDD上也有着优异的写入性能。（合并期间不能对外服务）

​ 官方公开的测试显示能够达到50-200MB/s的写入negligible，按照每行100Byte估算，大约相当于50W-200W/s的写入速度。

数据分区与线程级并行

​ ClickHouse将数据划分为多个partition，每个分区在进一步划分为多个indexgranularity（索引粒度），然后通过多个CUP核心分别处理其中的一部分来实现并行数据处理。这种设计下，单条Query就能利用整机所有CPU。极致的并行处理能力，极大的降低了查询延迟。

​ 但是，ClickHouse即时对于大量数据的查询也能够化整为零平行处理，但是有一个弊端就是对于单条查询使用多CPU，就不利于同事并发多条查询。所以对于高qps的查询业务，ClickHouse并不是强项。

后记：（qps （TPS）= 并发数/平均响应时间，假设有多个request的请求过来，其中一个request的查询语句需要占用多个cpu，那么别的请求过来，要么能使用的cpu少，要么等上一个请求访问完毕，所以他不适合去做初始存储，但他适合做一些事实表和维度表被关联后被打成宽流的查询，在宽流里做常规的一些业务操作）

Docker安装Clickhouse

直接运行拉取镜像命令

docker run -d --name fsx_clickhouse_doc --ulimit nofile=262144:262144 -p 8123:8123 -p 9000:9000 -p 9009:9009 yandex/clickhouse-server

然后查看镜像是否启动，镜像名为fsx_clickhouse_doc

docker ps

然后进入Clickhouse容器

docker exec -it fsx_clickhouse_doc /bin/bash

如果需要在容器内安装vim

apt-get update
apt-get install vim -y

进入clickhouse-server目录并查看目录

cd /etc/clickhouse-server

然后初始化密码,修改clickhouse的用户密码需要在users.xml中配置

需要注意的是: 密码必须为加密过的形式, 否则会一直连不上。

然后加密密码方式为

PASSWORD=$(base64 < /dev/urandom | head -c8); echo "你的密码"; echo -n "你的密码" | sha256sum | tr -d '-'

人然后编辑user输入vim user.xml修改用户密码

将 <password></password> 的替换为 上边生成的密码密文 `

直接登录

clickhouse-client -h 127.0.0.1 -d default -m -u default --password '你的密码' 
即
clickhouse-client -h 127.0.0.1 -d default -m -u default --password 123456

数据类型

整型范围

Int8 [-128 : 127]

Int16 [-32768 : 32767]

Int32 [-2147483648 : 2147483647]

Int64 [-9223372036854775808 : 9223372036854775807]

无符号整型范围

UInt8 - [0 : 255]

UInt16 - [0:65535]

UInt32 - [0 : 4294967295]

UInt64 - [0：18446744073709551615]

浮点型

Float32 -类似 float

Float64 -类似 double

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

布尔型

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

Decimal型

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

三种声明：（不会四舍五入）123.123123123

​ Decimal32（5），相当于整数加小数一共9位，小数部分有5位 123.12312

​ Decimal64（5），相当于整数加小数一共18位，小数部分有5位

​ Decimal128（5），相当于整数加小数一共38位，小数部分有5位

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

字符串

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

FixedString（N）：固定长度N的字符串，N必须是严格的正自然数。当服务端读取长度小于N的字符串的时候，通过在字符串末尾添加空字节来达到N字节长度。当服务端读取长度大于N的字符串的时候，将返回错误消息。与String相比，极少会使用FixedSting，因为使用起来不方便

枚举类型

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

Enum8 用 'String' = Int8 对描述

Enum16 用 'String' = Int16 对描述

用法演示

创建一个带有一个枚举 Enum8（'hello' = 1,'world'=2）类型的列

CREATE TABLE t_enum
                (
                    `x` Enum8('hello' = 1, 'word' = 2)
                )
                ENGINE = TinyLog;

插入值

insert into value ('hello'),('word'),('hello')

再次插入如

insert into value（‘haha’） -- 会告诉你这个值插入失败

查询方式

select * from t_enum where x = 1;
select * from t_enum where x = 'word'

如果要查看对应行数的数值可以用一下语句

select cast(x,'Int8')from t_enum;

使用场景：对一些状态、类型的字段算是一种空间优化，也算是一种数据约束。但是实际使用中往往因为一些数据内容的变化增加一定的维护成本，甚至死数据丢失问题。所以谨慎使用。

时间类型

目前ClickHouse 有三种时间类型

​ Date 接受年-月-日的字符串比如 2022 - 1 - 10

​ Datetime 接受年-月-日 时：分：秒比如 2022 - 1 - 10 19:30

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

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

还有很多数据结构去官网上看，https://clickhouse.yandex/docs/zh/data_types/

数组

Array（T）:由T类型元素组成的数组。(同一个数组的类型要一样)

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

创建数组方式1，使用array函数

array（T）
select array(1,2)AS x,toTypeName(x);

Nullable：表示类型可以为null

create Table t_null(x Int8 ,y Nullable(Int8)) ENGINE Tinylog

表引擎

表引擎的使用

表引擎是Clickhouse的一大特色。表引擎决定了如何存储表的数据。包括：

​ 数据的存储方式和位置，写到哪里以及从哪里读取数据。(clickhouse不依赖HDFS，资源分配不依赖Yarn，存在本地磁盘)

​ 支持那些查询以及如何支持。

​ 并发数据访问。

​ 索引的使用（如果存在）。

​ 是否可以执行多线程请求。

​ 数据复制参数。

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

注意：引擎的名称大小写敏感。

TinyLog

​ 以列文件的形式保存在磁盘上，不支持索引，没有并发控制。一般保存少量数据的小表（100万行），生产环境上作用有限。可以用于平时练习测试用。

如：

create table t_tinylog(id String,name String)engine = TinyLog

Memory

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

​ 一般用到这个的不多，除了用来测试，就是在需要非常高的性能，同时数据量不太大（上限大概1亿行）的场景

集成引擎

​ 可以映射Mysql，MongoDB以及JDBC，HDFS，ODBC，Kafka，RebbitMQ等，操作方式是Clickchouse直接打通一个通道直接映射到该数据库，然后在clickhouse 中写查询语句，官方文档里有该操作的创表语句。

MergeTree

​ ClickHouse中最强大的表引擎当属MergeTree（合并树）引擎及该系列（*Merge Tree）中的其他引擎，支持索引和分区，地位可以相当于innode之于Mysql。而且基于MergeTree，还衍生出很多种类，也是许多非常有特色的引擎。

建表语句

create table 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);
-- 设置引擎    
-- 按时间分区    
-- 设置主键建立索引（Clickhouse主键可以不唯一）
-- 根据这些字段排序（必须项）

插入数据

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

partition by

（不填默认不分区分区名 = all）

作用

分区的目的和hive一样，都主要是降低扫描的范围，优化查询速度。

分区目录

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

并行

分区后，面对涉及跨分区的查询统计，ClickHouse会以分区为单位并行处理。(官方推荐按天分区)

数据写入与分区合并

任何一个批次的数据写入都会产生一个临时分区不会纳入任何一个已有的分区。写入后的某个时刻（大概10-15分钟后），ClickHouse会自动执行合并操作（等不了也可以手动合并，通过optimize执行），把临时分区的数据，合并到已有分区中。（和Hbase一样合并后会清理过期数据）

optimize table xxxx final;

分区目录里的文件

​ 老版本里一个列就存有一个对应的.bin和.mrk3的文件，一个是列的真实数据一个是列的索引，这样每个列都有索引就查的很块，新版本里只有一个data.bin和data.mrk的文件，还有几个txt文件，库名开头的是压缩格式，和一个记录行数以及合并后行数的文件count.txt，这样10亿数据在count的时候就会很快的被查出来有几条。还有个columns.txt，列的信息，把列的列名和类型记录在里面。一个索引文件primary.idx（稀疏索引），还有一个分区信息partition.dat，还有一个分区内部的索引文件minmax_create_time.idx，记录最小索引和最大索引。

bin文件：数据文件

mrk文件：标记文件

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

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

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

minmax_create_time.id :分区键的最大值和最小值

Checksums.txt:效验文件，用于效验各个文件的正确性。存放各个文件的size以及hash值。

分区文件的命名规则：

大致命名规则为 PartitionID_MinBlockNum_MaxBlockNum_Level（文件拼起来后的全称）

PartitionID

​ 数据分区ID生成规则

​ 数据分区规则由分区ID决定，分区ID由Partition By分区决定。根据分区键字段类型，ID生成规则可分为：

​ 未定义分区键

​ 没有定义Partition By ，默认生成一个目录名为all的数据分区，所有数据均存放在all目录下。

​ 整型分区键

​ 分区键位整型，那么直接用该整型值的字符串形式作为分区ID。列：123，就被分为 1 2 3 。

​ 日期类分区键

​ 分区键为日期类型，或者可以转化成日期类型。例如：表示按日分的分区

​

20200601_1_1_0
20200602_2_2_0

​ 其他类型分区键

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

MinBlockNum

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

MaxBlockNum

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

Level

​ 合并的层级，被合并的次数。合并次数越多，层级越大。

primary key 主键(可选)

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

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

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

​ index granulqrity：直接翻译的话就是索引粒度，指在稀疏索引中两个相邻索引对应数据的间隔。ClickHouse中的MergeTree默认是 8192查询matastor元数据信息会发现建表语句默认给你加上了。官网不建议修改这个值，除非该列存在大量的重复值，比如在一个分区中几万行才有的一个不同数据。

order by(必选)

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

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

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

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

二级索引

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

（1）老版本使用二级索引前需增加设置

​ 是否允许使用实验性的二级索引（v20.1.2.4开始，这个参数已被删除，默认开启）

set allow_experimental_data_skipping_indices = 1;

（2）创建二级索引表

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

其中GRANULARITY N 是设定二级索引对于一级索引粒度的粒度。

（一级索引已经是被分为一块一块的，二级索引就是一次跳几块去检索）

（3）插入数据

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

数据的TTL

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

实时这块可能只需要最新的数据，特别是用户画像这块。

(1) 列级别TTL

创建测试表（必须是时间类型，而且不是主键）

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

插入数据然后等10秒手动执行合并（时间尽量为此时此刻的后10秒）

insert into t_order_mt4 values
(106,'sku_001',1000.00,'2022-01-12 14:30:30'),
(107,'sku_002',2000.00,'2022-01-12 14:30:30'),
(110,'sku_003',600.00,'2022-01-12 14:30:30');

时间到期后，会起一个合并任务。

(注意：会出现合并后没有重置Decimal类型字段的值，原因是本机cpu资源垃圾导致，重启clickHouse会大概率解决这个问题)

(2)表级TTL

这个语句是数据在create_time 之后10秒丢失（修改表结构）（时间到后就删表）

Alter table t_order_mt3 MODIFY TTL create_time + INTERVAL 10 SECOND;

和上面一样涉及判断字段必须是Date或者Datetime类型，推荐使用分区的日期字段。

能够使用的时间周期：SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR

另一种写法

create table t_order_mt3
(
        d DateTime,
      a Int
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(d)
ORDER BY d
TTL d + interval 1 MONTH [DELETE];        -- 时间到后删表，平常都是默认
        d + interval 1 WEEK to DISK 'bbb',        -- 时间到后数据挪到bbb
        d + interval 2 WEEK to VOLLME 'aaa';    -- 时间到后数据挪到aaa

ReplacingMergeTree（最终一致性）

​ ReplacingMergeTree是MergeTree的一个变种，它存储特性完全继承Merge Tree，这是多了一个去重的功能。尽管MergeTree可以设置主键，但是primary key 其实没有唯一约束的功能。如果你想处理掉重复的数据，可以借助这个ReplacingMergeTree。（去重是根据Order by的字段去去重）

去重时机

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

去重范围

​ 如果表经过分区，去重只会在分区内部进行去重，不能执行跨分区的去重

​ 所以ReplacingMergeTree能力有限，ReplacingMergeTree适用于在后台清除重复数据以节省空间，但是他不保证没有重复的数据出现。

创建表

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

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

（看不到结果的可以手动合并一下在查询）

通过测试得到结论

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

​ 去重不能跨分区。

​ 只有同一批插入或合并的数据才会进行去重。

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

​ 如果版本字段相同则按插入顺序保留最近一次。

SummingMergeTree

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

​ ClickHouse 为了这种场景，提供了一种能够“预聚合”的引擎SummingMergeTree

创建表

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-01 12:00:00');

SummingMergeTree的维度聚合不是根据主键来的，而是order by 里的字段来做的。（所以order by很重要）

​ SummingMerrgeTree()中指定的列作为汇总数据列

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

​ 以order by 的列为准，作为维度列

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

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

​ 只有在同一次批次插入（新版本）或分片合并时才会进行聚合

AggregatingMergeTree

​ AggregatingMergeTree，它能够在合并分区的时候，按照预先定义的条件，来聚合函数。

​ 根据预先定义好的聚合函数，计算数据并通过二进制的格式存入表内。将同一分组下的多行函数，聚合成一行，既减少了数据行，又降低了后续聚合查询的开销。

建表语句

-- 建表语句
CREATE TABLE agg_table( 
    id String,
    city String,
    code SimpleAggregateFunction(anyLast, Nullable(String)), -- 懒得写建表语句先放着忘了看官网
                             -- 取最后一个   值可以为空字段为字符串类型
    value AggregateFunction(sum,UInt32),
                                          -- 对当前字段做sum操作写入时要用到函数写入，拿出来时也要用对应函数拿出
    create_time DateTime
)
ENGINE = AggregatingMergeTree() 
PARTITION BY toYYYYMM(create_time) 
ORDER BY (id,city)
PRIMARY KEY id;

其中的uniq、sum是指定的聚合函数。可以在https://clickhouse.tech/docs/en/sql-reference/aggregate-functions/reference/%E4%B8%8B%E6%9F%A5%E7%9C%8B%E6%9B%B4%E5%A4%9A%E7%9A%84%E7%9B%B8%E5%85%B3%E5%87%BD%E6%95%B0%E3%80%82

​ AggregateFunction是ClickHouse提供的一种特殊的数据类型，它能够以二进制的形式存储中间状态结 果。其使用方法也十分特殊，对于AggregateFunction类型的列字段，数据的写入和查询都与寻常不同。在写入数据时，需要调用State函数。而在查询数据时，则需要调用相应的Merge函数。
​

​ 例如：在写入数据时需要调用与uniq、sum对应的uniqState和sumState函数，并使用INSERT SELECT 语法:

INSERT

-- 写入测试数据id = A000, code相同;
INSERT INTO TABLE agg_table SELECT 'A000','test',uniqState('code1'), sumState(toUInt32(100)), '2019-08-10 17:00:00';
INSERT INTO TABLE agg_table SELECT 'A000','test',uniqState('code1'), sumState(toUInt32(100)), '2019-08-10 17:00:00';
-- 写入测试数据id = A001, code不同;
INSERT INTO TABLE agg_table SELECT 'A001','test',uniqState('code1'), sumState(toUInt32(100)), '2019-08-10 17:00:00';
INSERT INTO TABLE agg_table SELECT 'A001','test',uniqState('code2'), sumState(toUInt32(50)), '2019-08-10 17:00:00';

​ 而在查询数据时，如果直接使用列名访问code和value，将会是无法显示的二进制。此时，则需要调用 与uniq、sum对应的uniqMerge、sumMerge函数:

SELECT id,city,uniqMerge(code),sumMerge(value) FROM agg_table GROUP BY id,city;

SimpleAggregateFunction（1）和AggregateFunction（2），2是只能去做聚合，而且写入时要用特定函数，查询时也是要特定函数，不然查出来的是二进制，而且是全状态存储。1是有特定优化在建表时用上特定函数来限定操作，比如取最近一条，或第一条，1不需要全状态存储

SQL操作

Insert

基本操作与标准SQL（MySql）基本一致

标准

insert into [table_name] values(...)(...)

从表到表的插入

Insert into [table_name] select a,b,c from [table_name_2]

Update 和 Delete

​ ClickHouse提供了Delete和Update的能力，这里操作被称为Mutation查询，但它和其他OLAP一样，只是给数据做了一个标记，它可以看做Alter 的一种。

​ 虽然实现修改和删除，但是和一般的OLTP数据库不一样，Mutation语句是一种很“重”的操作，而且不支持事务。

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

删除语句

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;

优化小技巧

实现高性能Update或Delete的思路：

Create table A
(
    a xxxx,
    b xxxx,
    c xxxx,
    _sign UInt8,
    _version Uint32
)

更新：插入一条新的数据，_version + 1

​ 查询的时候加上一个过滤条件， where version 最大

删除：sign，0表示未删除，1表示已删除，同时 version+1

​ 查询的时候加上一个过滤条件，where _sign=0 and version 最大

缺点，时间久了，数据会越积越多，除非有一种合并机制，怎么把过期数据清除掉

查询操作

支持子查询

支持CTE（Common Table Expression 公用表表达式 with 子句）

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

窗口函数（官方正在测试汇总。。）

不支持自定义函数

Group by 操作增加了 with rollup（上卷）\with cube（多维分析）\with toal（总计） 用来计算小计和总计。(就是根据多个维度（分组）做union all的操作而已)

上卷

group by 
group by a
group by a,b

多维分析

group by a,b
group by a
group by b
group by

总计

group by a,b
group by

更多函数可以去看官网，还挺多的。。

试一试多维分析

插入数据

alter table t_order_mt delete where 1=1;  -- 先清空删除表数据
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'),
(101,'sku_001',1000.00,'2020-06-04 12:00:00'),
(101,'sku_002',2000.00,'2020-06-04 12:00:00'),
(103,'sku_004',2500.00,'2020-06-04 12:00:00'),
(104,'sku_002',2000.00,'2020-06-04 12:00:00'),
(105,'sku_003',600.00,'2020-06-01 12:00:00');

With rollup

​ 从右至左去掉维度进行小计

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

Whith cube

​ 从右至左去掉维度进行小计，在从左至右去掉维度进行小计

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

with toal

从右至左去掉维度进行小计，再从左至右去掉维度进行小计

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

alter 操作

同MySql的修改字段基本一至

新增字段

alter table tableName add column newcolname String after col1;

修改字段类型

alter table tableName modify column newcolname String;

删除字段

alter table tableName drop column newcolname;

副本

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

（考虑高可用）

副本写入流程

互为副本，b插入一条数据日志写入zookeeper，a通过zookeepe检测到b的动作，从b下载数据

副本只能同步数据，不能同步表结构，所以我们需要在每台节点上自己动手建表

创建表，表引擎必须指定为ReplicatedMergeTree('/clickhouse/table/01/t_order_rep','rep_102')

create table t_order_smt(
    id UInt32,
    sku_id String,
    total_amount Decimal(16,2),
    create_time Datetime
)engine = ReplicatedMergeTree('/clickhouse/table/01/t_order_rep','rep_102')
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id,sku_id);

ReplicatedMergeTree（clickhous/table/分片/表名，副本名必须不一样）只针对MergeTree家族，这个副本引擎包含了上边写到的MergeTree系列引擎

条件有限只有单机=。=

分片集群

​ 副本虽然能够提高数据的可用性，降低丢失风险，但是每台服务器实际上必须容纳全量数据，对数据的横向扩容没有解决。

​ 要解决数据水平切分的问题，需要引入分片的概念。通过分片把一分完整的数据进行切分，不同的分片分布到不同的节点上，在通过Distributed表引擎吧数据拼接起来一同使用。

​ Distributed表引擎本身不存储数据

​ 有点类似MyCat之于MySql，成为一种中间件，通过分布式逻辑表来写入，分发，路由来操作多台节点不同分片的分布式数据。

​ 注意：Clickhouse的集群是表级别的，很多公司，大部分只做了高可用，但是没有用分片，避免降低查询性能以及操作集群的复杂性。

集群写入流程

内部同步：由分布式表引擎同步给切片，由切片自己同步给副本

非内部同步：（懒得画图了）由分布式表引擎统一分发给节点以及副本节点（但是它的压力很大），生产上一般是打开的。保证一致性

集群读取流程

Java代码操作

引入依赖

<dependency>
      <groupId>ru.yandex.clickhouse</groupId>
      <artifactId>clickhouse-jdbc</artifactId>
      <version>0.2.4</version>
    </dependency>

查询

import org.apache.http.client.utils.DateUtils;
import ru.yandex.clickhouse.ClickHouseConnection;
import ru.yandex.clickhouse.ClickHouseConnectionImpl;
import ru.yandex.clickhouse.ClickHouseDataSource;
import ru.yandex.clickhouse.ClickHouseStatement;
import ru.yandex.clickhouse.domain.ClickHouseFormat;
import ru.yandex.clickhouse.settings.ClickHouseProperties;

import java.io.ByteArrayInputStream;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Random;
import java.util.concurrent.TimeUnit;

import static java.lang.System.currentTimeMillis;

public class ClickHouseUser {

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        ClickHouseConnection conn=null;

        ClickHouseProperties properties=new ClickHouseProperties();

        properties.setUser("default");

        properties.setPassword("123456");

        properties.setDatabase("default");
        try {

            ClickHouseDataSource dataSource = new ClickHouseDataSource("jdbc:clickhouse://127.0.0.1:8123",properties); //default表示数据表 ,port原来为8123
            Random random  = new Random();
            ClickHouseConnectionImpl connection = (ClickHouseConnectionImpl) dataSource.getConnection();
            ClickHouseStatement sth = (ClickHouseStatement) connection.createStatement();
            ResultSet resultSet = sth.executeQuery("select * from t_order_mt2");
            while (resultSet.next()){
                System.out.println(resultSet.toString());
            }
          } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

插入MergeTree引擎表

package org.example;

import org.apache.http.client.utils.DateUtils;
import ru.yandex.clickhouse.ClickHouseConnection;
import ru.yandex.clickhouse.ClickHouseConnectionImpl;
import ru.yandex.clickhouse.ClickHouseDataSource;
import ru.yandex.clickhouse.ClickHouseStatement;
import ru.yandex.clickhouse.domain.ClickHouseFormat;
import ru.yandex.clickhouse.settings.ClickHouseProperties;

import java.io.ByteArrayInputStream;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Random;
import java.util.concurrent.TimeUnit;

import static java.lang.System.currentTimeMillis;

public class ClickHouseUser {

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        ClickHouseConnection conn=null;

        ClickHouseProperties properties=new ClickHouseProperties();

        properties.setUser("default");

        properties.setPassword("123456");

        properties.setDatabase("default");
        try {

            ClickHouseDataSource dataSource = new ClickHouseDataSource("jdbc:clickhouse://127.0.0.1:8123",properties); //default表示数据表 ,port原来为8123
            Random random  = new Random();
            ClickHouseConnectionImpl connection = (ClickHouseConnectionImpl) dataSource.getConnection();
            ClickHouseStatement sth = (ClickHouseStatement) connection.createStatement();
            ResultSet resultSet = sth.executeQuery("select * from t_order_mt2");
            while (resultSet.next()){
                System.out.println(resultSet.toString());
            }
            for (int i=0;i<10000;i++) {
                Long l = currentTimeMillis()/1000;
                String time = l.toString();
                String myRow = random.nextInt(110) + 100 + "," + "sku_" + (random.nextInt(10) + 1) + "," + String.format("%.2f", (random.nextDouble() * 5000 + 1000)) + "," + time;
                String myfields = "(id,sku_id,total_amount,create_time)";
                String myQuery = "INSERT INTO default.t_order_mt " + myfields;

                sth.write().sql(myQuery).data(new ByteArrayInputStream(myRow.getBytes()), ClickHouseFormat.CSV).send();
            }
            if(connection!=null) {
                System.out.println("连接成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

插入AggregatingMergeTree引擎表

package org.example;

import org.apache.http.client.utils.DateUtils;
import ru.yandex.clickhouse.ClickHouseConnection;
import ru.yandex.clickhouse.ClickHouseConnectionImpl;
import ru.yandex.clickhouse.ClickHouseDataSource;
import ru.yandex.clickhouse.ClickHouseStatement;
import ru.yandex.clickhouse.domain.ClickHouseFormat;
import ru.yandex.clickhouse.settings.ClickHouseProperties;

import java.io.ByteArrayInputStream;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Random;
import java.util.concurrent.TimeUnit;

import static java.lang.System.currentTimeMillis;

public class ClickHouseUser {

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        ClickHouseConnection conn=null;

        ClickHouseProperties properties=new ClickHouseProperties();

        properties.setUser("default");

        properties.setPassword("123456");

        properties.setDatabase("default");
        try {

            ClickHouseDataSource dataSource = new ClickHouseDataSource("jdbc:clickhouse://127.0.0.1:8123",properties); //default表示数据表 ,port原来为8123
            Random random  = new Random();
            ClickHouseConnectionImpl connection = (ClickHouseConnectionImpl) dataSource.getConnection();
            ClickHouseStatement sth = (ClickHouseStatement) connection.createStatement();
            for (int i=0;i<10000;i++) {
                Long l = currentTimeMillis()/1000;
                String time = l.toString();
                String myRow = "'" + random.nextInt(9) + 1 + "'," + "'sku_" + (random.nextInt(10) + 1) + "',uniqState('" +random.nextInt(10)+ 10+"'),sumState(toUInt32(" +random.nextInt(1000)+5000+")),'"+ time + "'";
                String myQuery = "INSERT INTO default.agg_table select " + myRow;
                //执行插入
                sth.write().sql(myQuery).data(new ByteArrayInputStream(myRow.getBytes())).send();
            }
            if(connection!=null) {
                System.out.println("连接成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}