clickhouse学习

一、Clickhouse概述

OLTP(联机事务处理系统)
    例如mysql等关系型数据库，在对于存储小数据量的时候，查询数据并分析速度很快，OLTP本身其实是一个逻辑上的概念，指的是某个数据库，主要是针对增删改操作的。
    里面的数据会经常的发生变化。
OLAP(联机分析处理系统)
    指的是数据库中的数据长期不变，有着大量的历史数据，并且可以随时的做分析，而增删改操作很少。

OLAP 种类系统架构的的特点:

1、绝大多数是读请求
2、数据以相当大的批次(> 1000行)更新，而不是单行更新;或者根本没有更新。
3、已添加到数据库的数据不能修改。
4、对于读取，从数据库中提取相当多的行，但只提取列的一小部分。
5、宽表，即每个表包含着大量的列
6、查询相对较少(通常每台服务器每秒查询数百次或更少)
7、对于简单查询，允许延迟大约50毫秒
8、列中的数据相对较小：数字和短字符串(例如，每个URL 60个字节)
9、处理单个查询时需要高吞吐量(每台服务器每秒可达数十亿行)
10、事务不是必须的
11、对数据一致性要求低
12、每个查询有一个大表。除了他以外，其他的都很小。
13、查询结果明显小于源数据。

二、表操作

数据类型

注意事项：

1、建表写数据类型的时候，严格区分大小写Int32,不能写成int32

2、建表的时候，必须要指定表引擎

1. 整数类型

UInt8, UInt16, UInt32, UInt64, UInt128, UInt256, Int8, Int16, Int32, Int64, Int128, Int256

Int8 — [-128 : 127]
Int16 — [-32768 : 32767]
Int32 — [-2147483648 : 2147483647]
Int64 — [-9223372036854775808 : 9223372036854775807]
Int128 — [-170141183460469231731687303715884105728 : 170141183460469231731687303715884105727]
Int256 — [-57896044618658097711785492504343953926634992332820282019728792003956564819968 : 57896044618658097711785492504343953926634992332820282019728792003956564819967]

UInt8 — [0 : 255]
UInt16 — [0 : 65535]
UInt32 — [0 : 4294967295]
UInt64 — [0 : 18446744073709551615]
UInt128 — [0 : 340282366920938463463374607431768211455]
UInt256 — [0 : 115792089237316195423570985008687907853269984665640564039457584007913129639935]

2. 字符串类型

String：可变长字符串
​
FixedString(长度)：固定长字符串，参数是字节数，执行效率比String要高

c. 日期类型

Date 年-月-日
​
Date32 年-月-日
​
DateTime 年-月-日 时-分-秒
​
DateTime64 年-月-日 时-分-秒.毫秒
​
案例：
# 建表语句：
    create table date_test (date1 Date,date2 Date32,date3 DateTime,date4 DateTime64) ENGINE = TinyLog;
​
# 插入语句：
    insert into date_test values ('2024-6-21','2024-6-21','2024-6-21','2024-6-21');

3. UUID类型

clickhouse提供了一个函数：generateUUIDv4() 生成一个 00000000-0000-0000-0000-000000000000 的编号 编号的类型就是UUID类型   bee32020-a6cb-49a6-a10b-427381b11613

4. 可为空 Nullable

create table test1 (id Int32,name String) ENGINE = TinyLog;
​
insert into students_test values (null,'张三','男','bigdata30');

5. 数组 Array(T)

字段类型是数组，对于同一个数组，在建表的时候指定数据类型，注意：在MergeTree表引擎中是不允许出现数组嵌套的
注意：需要使用array()函数，将元素组成数组，将来还可以使用toTypeName()查看某一列的数据类型

g. 小数类型

# Decimal(P,S),Decimal32(S),Decimal64(S),Decimal128(S)
有符号的定点数，可在加、减和乘法运算过程中保持精度。对于除法，最低有效数字会被丢弃（不舍入）
P - 精度。有效范围：[1:38]，决定可以有多少个十进制数字（包括分数）。
S - 规模。有效范围：[0：P]，决定数字的小数部分中包含的小数位数。
Decimal(4,2)
举例：12.12234  Decimal(7,5)
P: 7
S: 5

1、建表语句

-- ck安装后的数据根目录：/var/lib/clickhouse
​
create table users3 (id Int8,name FixedString(12),gender Nullable(FixedString(3)),clazz String) ENGINE = TinyLog;

2、插入数据

# 基本格式
    INSERT INTO [db.]table [(c1, c2, c3)] VALUES (v11, v12, v13), (v21, v22, v23), ...
​
# 查看表结构
    desc 表名;

三、引擎

1、数据库引擎

1.1 Atomic

clickhouse数据库建库默认指定的数据库引擎

它支持非阻塞的DROP TABLE和RENAME TABLE查询和原子的EXCHANGE TABLES t1 AND t2查询。默认情况下使用Atomic数据库引擎。

1.2 MySQL

MySQL引擎用于将远程的MySQL服务器中的表映射到ClickHouse中，并对表进行INSERT和SELECT查询，以方便在ClickHouse与MySQL之间进行数据交换

MySQL数据库引擎会将对其的查询转换为MySQL语法并发送到MySQL服务器中，可以执行诸如SHOW TABLES或SHOW CREATE TABLE之类的操作。

但无法对其执行以下操作：

• RENAME

• CREATE TABLE

• ALTER

# 建库语句
    CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
    ENGINE = MySQL('host:port', ['database' | database], 'user', 'password')
    
# 参数理解：
    host:port — MySQL服务地址，既可以是ip地址，也可以是主机名（如果是主机名，要配置hosts映射）
    database — MySQL数据库名称
    user — MySQL用户名
    password — MySQL用户密码
    
# 操作的注意事项
1、mysql的数据和ck中数据库映射的数据几乎是同步的
2、在任意一端添加数据，另一端都可以观察添加后的结果
3、对于删除数据，只能在mysql端删除，不能够在ck端删除

2、表引擎

2.1 日志引擎

1. Log

Log 与 TinyLog 的不同之处在于，«标记» 的小文件与列文件存在一起。这些标记写在每个数据块上，并且包含偏移量，这些偏移量指示从哪里开始读取文件以便跳过指定的行数。这使得可以在多个线程中读取表数据，Log引擎适用于临时数据，write-once 表以及测试或演示目的。

# 建表，指定表引擎为Log
    create table students_log (id Int32,name String,gender FixedString(3),clazz String) ENGINE = Log;

2. TinyLog

最简单的表引擎，用于将数据存储在磁盘上。每列都存储在单独的压缩文件中。写入时，数据将附加到文件末尾。

并发数据访问不受任何限制：

• 如果同时从表中读取并在不同的查询中写入，则读取操作将抛出异常

• 如果同时写入多个查询中的表，则数据将被破坏。

这种表引擎的典型用法是 write-once：首先只写入一次数据，然后根据需要多次读取。查询在单个流中执行。不支持索引。

3. StripeLog

写数据：

StripeLog 引擎将所有列存储在一个文件中。对每一次 Insert 请求，ClickHouse 将数据块追加在表文件的末尾，逐列写入。

ClickHouse 为每张表写入以下文件：

• data.bin — 数据文件。

• index.mrk — 带标记的文件。标记包含了已插入的每个数据块中每列的偏移量。

StripeLog 引擎不支持 ALTER UPDATE 和 ALTER DELETE 操作。

读取数据：

带标记的文件使得 ClickHouse 可以并行的读取数据。 SELECT 请求返回行的顺序是不可预测的,使用 ORDER BY 子句对行进行排序。

CREATE TABLE stripe_log_table(timestamp DateTime,message_type String,message String) ENGINE = StripeLog
​
​
INSERT INTO stripe_log_table VALUES (now(),'REGULAR','The first regular message')
​
# 建表，指定表引擎为Log
    create table students_stripelog (id Int32,name String,gender FixedString(3),clazz String) ENGINE = StripeLog;
# 添加数据
    insert into students_stripelog values (1001,'张三','男','理科一班'),(1002,'李四','男','文科二班'),(1003,'王五','男','文科二班');

2.2 合并树家族

MergeTree

Clickhouse 中最强大的表引擎当属 MergeTree （合并树）引擎及该系列（MergeTree）中的其他引擎。

MergeTree 系列的引擎被设计用于插入极大量的数据到一张表当中。

主要特点:

• 存储的数据按主键排序。

• 如果指定了 分区键 的话，可以使用分区。

  在相同数据集和相同结果集的情况下 ClickHouse 中某些带分区的操作会比普通操作更快。查询中指定了分区键时 ClickHouse 会自动截取分区数据。

• 支持数据副本。

  ReplicatedMergeTree 系列的表提供了数据副本功能。

# 建表语句规范：

CREATE TABLE [IF NOT EXISTS] table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
    ...
    INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
    INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2
) ENGINE = MergeTree()
ORDER BY expr
[PARTITION BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[TTL expr [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'], ...]
[SETTINGS name=value, ...]
​
​

# 建表语句
    create table goods_orders (id String,uname String,goods_name String,price Int64,date Date32) ENGINE = MergeTree() order by date PARTITION BY date;
    
# 插入语句 
    insert into goods_orders values ('1001','张三','oppo手机',7000,'2024-6-21'),('1002','李四','机械革命电脑',10000,'2024-6-22'),('1003','陆小曼','iphone14',5000,'2024-6-21'),('1004','龙女','AI吸尘器',17000,'2024-6-22');

注意：默认是针对每一批数据按照分区字段的值进行分区

optimize table 表名 final;
​
optimize table goods_orders final;

四、常用函数

4.1 算术函数

对于所有算术函数，结果类型为结果适合的最小数值类型（如果存在这样的类型）。最小数值类型是根据数值的位数，是否有符号以及是否是浮点类型而同时进行的。会自动的根据数值大小，来选用最适合的数据类型存储。

# plus(a, b), a + b operator
计算数值的总和。 可以将Date或DateTime与整数进行相加。在Date的情况下，和整数相加整数意味着添加相应的天数。对于DateTime，意味着添加相应的秒数。
​
# minus(a, b), a - b operator
计算数值之间的差，结果总是有符号的。
​
# multiply(a, b), a * b operator
计算数值的乘积。
​
# divide(a, b), a / b operator
计算数值的商。结果类型始终是浮点类型。 它不是整数除法。对于整数除法，使用’intDiv’函数。 当除以零时，得到’inf’，‘- inf’或’nan’。
​
# intDiv(a,b)
计算数值的商，向下舍入取整（按绝对值）。 除以零或将最小负数除以-1时抛出异常。
​
# max2(a,b)
value1 — 第一个值，类型为Int/UInt或Float。
value2 — 第二个值，类型为Int/UInt或Float。
​
# max2(value1, value2)
value1 — 第一个值，类型为Int/UInt or Float。
value2 — 第二个值，类型为Int/UInt or Float。

4.2 比较函数

比较函数始终返回0或1（UInt8）。

可以比较以下类型：

• 数字

• String 和 FixedString

• 日期

• 日期时间

每个组内的类型均可互相比较，但是对于不同组的类型间不能够进行比较。

例如，无法将日期与字符串进行比较。必须使用函数将字符串转换为日期，反之亦然。

字符串按字节进行比较。较短的字符串小于其开头并且至少包含一个字符的所有字符串。

等于，a=b和a==b 运算符
不等于，a!=b和a<>b 运算符
小于, < 运算符
大于, > 运算符
小于等于, <= 运算符
大于等于, >= 运算符