clickhouse 学习总结

由于最近工作需要，学习了相关clickhouse相关知识，再此作为总结，后续补充

–clickhouse 是什么？
ClickHouse®是面向列的数据库管理系统（DBMS），用于对查询进行联机分析处理（OLAP）。
–clickhouse使用场景
绝大多数请求是针对读取访问的。
数据以相当大的批量（> 1000行）更新，而不是单行更新；或根本不更新。
数据已添加到数据库，但未修改。
对于读取，从数据库中提取了很多行，但只有一小部分列。
表是“宽的”，这意味着它们包含大量的列。
查询相对较少（通常每台服务器每秒数百个查询或更少查询）。
对于简单查询，允许大约50毫秒的延迟。
列值相当小：数字和短字符串（例如，每个URL 60个字节）。
处理单个查询时需要高吞吐量（每台服务器每秒多达数十亿行）。
交易是没有必要的。
对数据一致性的要求低。
每个查询只有一张大表。除一张桌子外，所有桌子都很小。
查询结果远小于源数据。换句话说，数据经过过滤或聚合，因此结果适合单个服务器的RAM。
–clickhouse 数据类型
整型
有符号整型（-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]
浮点型
Float32 - float
Float64 – double
布尔型
没有单独的类型来存储布尔值。可以使用 UInt8 类型，取值限制为 0 或 1。
字符串
变长字符串 String
字符串可以任意长度的。它可以包含任意的字节集，包含空字节。
定长字符串 FixedString(N)
固定长度 N 的字符串，N 必须是严格的正自然数。当服务端读取长度小于 N 的字符串时候，通过在字符串末尾添加空字节来达到 N 字节长度。 当服务端读取长度大于 N 的字符串时候，将返回错误消息。
与String相比，极少会使用FixedString，因为使用起来不是很方便。
枚举类型
Enum8 用 ‘String’= Int8 对描述。
Enum16 用 ‘String’= Int16 对描述。
Enum 保存 ‘string’= integer 的对应关系。在 ClickHouse 中，尽管用户使用的是字符串常量，但所有含有 Enum 数据类型的操作都是按照包含整数的值来执行。这在性能方面比使用 String 数据类型更有效。
举例：
#新建一张带Enum8类型的表：
localhost 😃 CREATE TABLE enum_t ( et Enum8(‘a’ = 1, ‘b’ = 2, ‘c’ =3)) ENGINE = TinyLog;
localhost 😃 DESC enum_t ;
┌─name─┬─type─────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ et │ Enum8(‘a’ = 1, ‘b’ = 2, ‘c’ = 3) │ │ │ │ │ │
└──────┴──────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

#插入数据
localhost 😃 INSERT INTO enum_t(et) VALUES (‘a’),(‘a’),(‘b’);

#查看
localhost 😃 SELECT * FROM enum_t ;
┌─et─┐
│ a │
│ a │
│ b │
└────┘

#如果需要看到对应行的数值，则必须将 Enum 值转换为整数类型：
localhost 😃 SELECT CAST(et, ‘Int8’) FROM enum_t ;
┌─CAST(et, ‘Int8’)─┐
│ 1 │
│ 1 │
│ 2 │
└──────────────────┘
数据组
Array(T)
由 T 类型元素组成的数组。T 可以是任意类型，包含数组类型，但不推荐使用多维数组，ClickHouse 对多维数组的支持有限。
可以使用array()函数和中括号来创建数组
举例：
#新建两张带Array类型的表：
localhost 😃 CREATE TABLE array_t (arr Array(UInt8)) ENGINE = TinyLog;
localhost 😃 CREATE TABLE array_ta (arr Array(String)) ENGINE = TinyLog;

#插入数组
localhost 😃 INSERT INTO array_t VALUES([1,2,3,4,5]),(array(11,22,33,44,55));
localhost 😃 INSERT INTO array_ta VALUES([‘a’,‘b’,‘c’]),(array(‘x’,‘y’,‘z’,‘123’));

#查看结果
localhost 😃 SELECT * FROM array_t ;
┌─arr──────────────┐
│ [1,2,3,4,5] │
│ [11,22,33,44,55] │
└──────────────────┘

localhost 😃 SELECT * FROM array_ta;
┌─arr─────────────────┐
│ [‘a’,‘b’,‘c’] │
│ [‘x’,‘y’,‘z’,‘123’] │
└─────────────────────┘
元组
Tuple(T1, T2, …)
元组，其中每个元素都有单独的类型。
举个例子：
#创建一张带tuple字段的表：
localhost 😃 CREATE TABLE tuple_t (ttt Tuple(Int8, String, Array(String), Array(Int8))) ENGINE = TinyLog;

#插入数据
localhost 😃 INSERT INTO tuple_t VALUES((1, ‘a’, [‘a’, ‘b’, ‘c’], [1, 2, 3])),(tuple(11, ‘A’, [‘A’, ‘B’, ‘C’], [11, 22, 33]));

#查看数据
localhost 😃 SELECT * FROM tuple_t ;
┌─ttt───────────────────────────────┐
│ (1,‘a’,[‘a’,‘b’,‘c’],[1,2,3]) │
│ (11,‘A’,[‘A’,‘B’,‘C’],[11,22,33]) │
└───────────────────────────────────┘
日期
Date
用两个字节存储，表示从 1970-01-01 (无符号) 到当前的日期值, 最小值输出为0000-00-00。
时间戳
DateTime
用四个字节（无符号的）存储 Unix 时间戳，允许存储与日期类型相同的范围内的值。最小值为 0000-00-00 00:00:00，时间戳类型值精确到秒。
Nested : 这个和元祖有点类似
create table test_Nested(
name string,
hobby Nested(
id int 8,
hname Sting
)
)
–ipv4 存入ipv4 相关的数据，可以进行校验

–数据库引擎
–本地
–log（生产不怎么用）
日志与TinyLog的不同之处在于，列文件中存在一个小的“标记”文件。这些标记写在每个数据块上，并包含偏移量，这些偏移量指示从何处开始读取文件以跳过指定的行数。这使得可以在多个线程中读取表数据。
对于并发数据访问，读操作可以同时执行，而写操作则阻止读操作和其他操作。
日志引擎不支持索引。同样，如果写入表失败，则表将损坏，并且从表中读取将返回错误。日志引擎适用于临时数据，一次写入表以及测试或演示目的。
–Tinylog（生产不怎么用）
该表引擎通常与一次写入方法一起使用：一次写入数据，然后根据需要多次读取。例如，您可以将TinyLog-type表用于小批量处理的中间数据。请注意，将数据存储在大量小表中效率很低。
查询在单个流中执行。换句话说，此引擎旨在用于相对较小的表（最多约1,000,000行）。如果您有许多小表，则使用此表引擎很有意义，因为它比Log引擎更简单（需要打开的文件更少）。
–StripeLog（生产不怎么用）
当您需要编写少量数据（少于一百万行）的表时，请使用此引擎。
–memory 数据只在内存中，重启就没了

–mysql 引擎 并执行INSERT和SELECT查询以在ClickHouse和MySQL之间交换数据。 （创建了一种索引指向）

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MySQL(‘host:port’, [‘database’ | database], ‘user’, ‘password’)

–创建表结构
基本创建
CREATE TABLE visits
(
userid int,
visitdate date,
website String
)
ENGINE = log()
–复制类型创建
create table t as 数据库.表名

–分区表
CREATE TABLE visits
(
userid int,
visitdate date,
website String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(visitdate)
ORDER BY userid
–查询分区表的信息
select table , partition , engine , path from Domeny.pl where table=‘visits’
–表合并操作
optimize table visits
–分区目录合并规则
MergeTree的分区目录是在数据写入过程中被创建的。也就是一张新建的表，如果没有任何数据，那么也不会有任何的分区存在。
MergeTree的分区目录伴随着每一批数据的写入(一次insert语句)，mergetree都会生成一批新的分区目录，即便不同批次写入的数据属于相同分区，也会生成不同的分区目录。
每次insert都会产生一个分区，那么就存在多个相同分区的情况，clickhouse会通过后台任务再将相同分区的多个目录合并成一个新的目录。已经存在的旧目录并不会立即被删除，而是在之后的某个时刻通过后台被删除。
同属于一个分区的多个目录，在合并之后会形成一个全新的目录，目录中的索引和数据文件也会相应的进行合并。新目录名称的合并规则如下：
MinBlockNum: 取同一分区内所有目录中最小的MinBlockNum值。
MaxBlockNum: 取同一分区内所有目录中最大的MaxBlockNum值。
Level: 取同一分区内最大Level值并加1。

–修改表的结构支持的引擎
MergeTree Merge Distributed
添加字段：alter table name add column col type
去除字段：alter table name drop column col type
修改字段：alter table name modify column col type
–移动表
rename table a to b
rename table a to newdb.b
–删除分区表
alter table account drop partition ‘sh’

–移动分区表
alter table account replace partition ‘bj’ from account
–清除分区表中的字段
alter table account clear column in partition ‘bj’
–卸载分区
alter table account detach partition ‘bj’;

–装载分区
alter table account attach partition ‘bj’;

–更新和删除 MergeTree 支持
mutation 操作 批量
alter table account delete where ID=‘’
–更新不能修改 主键 排序字段 分区字段
alter table account update account=’’ where ID=‘’
–表物理存储相关文件介绍
.bin 存储的各个字段的二进制压缩数据
.mrk2 标记对应的 bin文件中数据块的 偏移量
primary.idx 主键

checksums.txt 校验数据的完整性和正确性
columns.txt 当前表的所有字段
count.TXT 所有行数

partition.dat 分区信息
minmax_visitdate.idx 数据的最小最大范围
– ReplacingMergeTree
–去重重复的数据引擎 同一个区域内 合并以后进行重复数据删除

–CollapsingMergeTree 折叠引擎 后续