# clickhouse-learn

**Repository Path**: ray2chen/clickhouse-learn

## Basic Information

- **Project Name**: clickhouse-learn
- **Description**: 按照官网文档学习clickhouse
- **Primary Language**: Rust
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2024-07-08
- **Last Updated**: 2024-07-12

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# clickhouse-learn

#### 介绍
按照官网文档学习clickhouse https://clickhouse.com/docs/zh/introduction/distinctive-features

#### 软件架构
软件架构说明


clickhouse 是什么？
clickhouse 不同于mysql ，它数据存储是列式的


## OLAP场景的关键特征
绝大多数是读请求

数据以相当大的数量大于1000的更新，而不是单行的更新，几乎没有更新

已添加到数据库中的数据不能更新

对于读取来说，读取很多行，但是只读取某一些列的数据

宽表，每一张表列数比较多

## 值得注意点

ClickHouse不单单是一个数据库， 它是一个数据库管理系统。因为它允许在运行时创建表和数据库、加载数据和运行查询，而无需重新配置或重启服务.


## 多服务器分布式处理

## 多核心并行处理

## 支持SQL

ClickHouse支持一种基于SQL的声明式查询语言，它在许多情况下与ANSI SQL标准相同。

支持的查询GROUP BY, ORDER BY, FROM, JOIN, IN以及非相关子查询。
## 向量引擎

为了高效的使用CPU，数据不仅仅按列存储，同时还按向量(列的一部分)进行处理，这样可以更加高效地使用CPU
## 支持数据复制和数据完整性

ClickHouse使用异步的多主复制技术。当数据被写入任何一个可用副本后，系统会在后台将数据分发给其他副本，以保证系统在不同副本上保持相同的数据。在大多数情况下ClickHouse能在故障后自动恢复，在一些少数的复杂情况下需要手动恢复。

## 限制

没有完整的事务支持。
缺少高频率，低延迟的修改或删除已存在数据的能力。
## 实操
### 创建数据库
create database if not EXISTS test;
数据库其实只是用于存放表的一个目录
### 创建表
1 要创建的表的名称。
2 表结构，例如：列名和对应的数据类型。
3 表引擎及其设置，这决定了对此表的查询操作是如何在物理层面执行的所有细节。

#### 数据类型
uuid  generateUUIDv4() 函数来生成
int 8-256
uint 8-256
DateTime64 时间类型
LowCardinality（datatype） 使用低基数类型来减低存储成本
Map(key, value) 
AggregateFunction
array
FixedString
Float32,Float64
## 集群部署
ClickHouse集群是一个同质集群。 设置步骤:

在群集的所有机器上安装ClickHouse服务端
在配置文件中设置集群配置
在每个实例上创建本地表
创建一个分布式表
分布式表实际上是一种view，映射到ClickHouse集群的本地表。 从分布式表中执行SELECT查询会使用集群所有分片的资源。 您可以为多个集群指定configs，并创建多个分布式表，为不同的集群提供视图。

具有三个分片，每个分片一个副本的集群的示例配置:
```xml
    <remote_servers>
        <perftest_3shards_1replicas>
            <shard>
                <replica>
                    <host>example-perftest01j.yandex.ru</host>
                    <port>9000</port>
                </replica>
            </shard>
            <shard>
                <replica>
                    <host>example-perftest02j.yandex.ru</host>
                    <port>9000</port>
                </replica>
            </shard>
            <shard>
                <replica>
                    <host>example-perftest03j.yandex.ru</host>
                    <port>9000</port>
                </replica>
            </shard>
        </perftest_3shards_1replicas>
    </remote_servers>
```
## 数据库引擎
默认情况下，ClickHouse使用Atomic数据库引擎。它提供了可配置的table engines和SQL dialect。

您还可以使用以下数据库引擎：

MySQL

MaterializeMySQL

Lazy

Atomic

PostgreSQL

MaterializedPostgreSQL

Replicated

SQLite
## 表引擎
表引擎（即表的类型）决定了：

数据的存储方式和位置，写到哪里以及从哪里读取数据
支持哪些查询以及如何支持。
并发数据访问。
索引的使用（如果存在）。
是否可以执行多线程请求。
数据复制参数。

## 引擎类型
### MergeTree

适用于高负载任务的最通用和功能最强大的表引擎。这些引擎的共同特点是可以快速插入数据并进行后续的后台数据处理。 MergeTree系列引擎支持数据复制（使用Replicated* 的引擎版本），分区和一些其他引擎不支持的其他功能。

该类型的引擎：

MergeTree
ReplacingMergeTree
SummingMergeTree
AggregatingMergeTree
CollapsingMergeTree
VersionedCollapsingMergeTree
GraphiteMergeTree

### 日志

具有最小功能的轻量级引擎。当您需要快速写入许多小表（最多约100万行）并在以后整体读取它们时，该类型的引擎是最有效的。

该类型的引擎：

TinyLog
StripeLog
Log

### 集成引擎

用于与其他的数据存储与处理系统集成的引擎。 该类型的引擎：

Kafka
MySQL
ODBC
JDBC
HDFS

### 用于其他特定功能的引擎

该类型的引擎：

Distributed
MaterializedView
Dictionary
Merge
File
Null
Set
Join
URL
View
Memory
Buffer

### 自定义分区键

MergeTree 系列的表（包括 可复制表 ）可以使用分区。基于 MergeTree 表的 物化视图 也支持分区。

分区是在一个表中通过指定的规则划分而成的逻辑数据集。可以按任意标准进行分区，如按月，按日或按事件类型。为了减少需要操作的数据，每个分区都是分开存储的。访问数据时，ClickHouse 尽量使用这些分区的最小子集。

分区是在 建表 时通过 PARTITION BY expr 子句指定的。分区键可以是表中列的任意表达式。例如，指定按月分区，表达式为 toYYYYMM(date_column)

### MergeTree

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

MergeTree 系列的引擎被设计用于插入极大量的数据到一张表当中。数据可以以数据片段的形式一个接着一个的快速写入，数据片段在后台按照一定的规则进行合并。相比在插入时不断修改（重写）已存储的数据，这种策略会高效很多。

主要特点:

存储的数据按主键排序。

这使得您能够创建一个小型的稀疏索引来加快数据检索。

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

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

支持数据副本。

ReplicatedMergeTree 系列的表提供了数据副本功能。更多信息，请参阅 数据副本 一节。

支持数据采样。

需要的话，您可以给表设置一个采样方法。

子句

ENGINE - 引擎名和参数。 ENGINE = MergeTree(). MergeTree 引擎没有参数。

ORDER BY — 排序键。

可以是一组列的元组或任意的表达式。 例如: ORDER BY (CounterID, EventDate) 。

如果没有使用 PRIMARY KEY 显式指定的主键，ClickHouse 会使用排序键作为主键。

如果不需要排序，可以使用 ORDER BY tuple(). 参考 选择主键

PARTITION BY — 分区键 ，可选项。

大多数情况下，不需要使用分区键。即使需要使用，也不需要使用比月更细粒度的分区键。分区不会加快查询（这与 ORDER BY 表达式不同）。永远也别使用过细粒度的分区键。不要使用客户端指定分区标识符或分区字段名称来对数据进行分区（而是将分区字段标识或名称作为 ORDER BY 表达式的第一列来指定分区）。

要按月分区，可以使用表达式 toYYYYMM(date_column) ，这里的 date_column 是一个 Date 类型的列。分区名的格式会是 "YYYYMM" 。

PRIMARY KEY - 如果要 选择与排序键不同的主键，在这里指定，可选项。

默认情况下主键跟排序键（由 ORDER BY 子句指定）相同。 因此，大部分情况下不需要再专门指定一个 PRIMARY KEY 子句。

SAMPLE BY - 用于抽样的表达式，可选项。

如果要用抽样表达式，主键中必须包含这个表达式。例如： SAMPLE BY intHash32(UserID) ORDER BY (CounterID, EventDate, intHash32(UserID)) 。

TTL - 指定行存储的持续时间并定义数据片段在硬盘和卷上的移动逻辑的规则列表，可选项。

表达式中必须存在至少一个 Date 或 DateTime 类型的列，比如：

TTL date + INTERVAl 1 DAY

规则的类型 DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'指定了当满足条件（到达指定时间）时所要执行的动作：移除过期的行，还是将数据片段（如果数据片段中的所有行都满足表达式的话）移动到指定的磁盘（TO DISK 'xxx') 或 卷（TO VOLUME 'xxx'）。默认的规则是移除（DELETE）。可以在列表中指定多个规则，但最多只能有一个DELETE的规则。



### 列和表的 TTL

TTL用于设置值的生命周期，它既可以为整张表设置，也可以为每个列字段单独设置。表级别的 TTL 还会指定数据在磁盘和卷上自动转移的逻辑。

TTL 表达式的计算结果必须是 日期 或 日期时间 类型的字段。


### ReplacingMergeTree


该引擎和 MergeTree 的不同之处在于它会删除排序键值相同的重复项。

数据的去重只会在数据合并期间进行。合并会在后台一个不确定的时间进行，因此你无法预先作出计划。有一些数据可能仍未被处理。尽管你可以调用 OPTIMIZE 语句发起计划外的合并，但请不要依靠它，因为 OPTIMIZE 语句会引发对数据的大量读写。

因此，ReplacingMergeTree 适用于在后台清除重复的数据以节省空间，但是它不保证没有重复的数据出现。


### 函数
#### 算术函数
```sql
CREATE TABLE IF NOT EXISTS users  
(  
    user_id UInt32,  
    name String,  
    age UInt8  
)  
ENGINE = MergeTree()  
ORDER BY user_id;

create database if not exists test;
desc users;
SELECT  * from users ;
ALTER table users  add column  ts DateTime64 ; -- 不加精度就是3 ，毫秒级别
ALTER table users  add column  daytime date ; -- 不加精度就是3 ，毫秒级别
ALTER table users  add column  str String ; 
ALTER table users  add column  str String ; 


--使用算术函数
--plus加函数
SELECT user_id ,name ,age , plus(age,100) as step100Age  from users;

-- 时间计算,dateTime 1000 表示毫秒值
SELECT user_id ,name ,age , plus(age,100) as step100Age, ts ,plus(ts,1000)as plus1000  from users; 

-- 时间计算,date 1000 表示天
SELECT user_id ,name ,age , plus(age,100) as step100Age, ts ,plus(ts,1000) as plus1000 ,daytime ,plus(daytime,1000)  from users; 

-- minus减函数
SELECT user_id ,name ,age,minus(age,1)  , daytime,minus(daytime,1), ts,minus(ts,1) from users; 

-- multiply 乘以,支持浮点
SELECT user_id ,name ,age,multiply(age,0.1)from users; 

-- divide 浮点除法

SELECT user_id ,name ,age,divide(age,0.1)from users; 

SELECT user_id ,name ,age,divide(age,10)from users; 

-- inidiv 整型除法,向下取整
SELECT user_id ,name ,age,intDiv(age,10)from users; 

-- modulo 取余数
SELECT user_id ,name ,age,modulo (age,10)from users; 

-- abs 绝度值,函数可以嵌套
SELECT user_id ,name ,age,abs(minus(age,100))from users; 

-- max2 最大值，计算两个值的大小，使用场景是什么呢？

-- min2 最小值 计算两个值的大小，使用场景是什么呢？


-- 比较函数 数字，字符串和日期
-- 等于
SELECT age=25  from  users ;

-- 大于
SELECT age>=25  from  users ;

-- 小于
SELECT age<=25  from  users ;


-- 逻辑函数
-- and or not xor 操作，目前用的不多
SELECT and(age,20) from users ;


-- 转换函数

-- 时间转换
-- 转成秒
SELECT ts,  toUnixTimestamp(now())  from users ;

-- 转成毫秒 新版本支持 ，老版本不支持 toUnixTimestamp64Nano ，from 可以从时间戳转成datetime
--SELECT ts,  toUnixTimestamp64Milli(now())  from users ;

-- 字符串转成数字
SELECT age ,toString(age) from users ;
-- 数字转字符串
SELECT str ,toUInt64(str) from users ;
 
-- in 操作
SELECT name in ('Bob') from users u ;

-- 条件函数 if（条件，then,other）

SELECT if(age>25,'old man','young man')  as person from users u ;
-- 等同于三元运算符号
SELECT  age>25 ? 'old man':'young man' as person from users u ;

-- 多条件筛选 case end 
SELECT 
case
when age>25 then 'old man'
WHEN age=25 then 'stronger'
WHEN age<25 then 'young man'
end
as person
from users ;

-- 时间函数
-- toDateTime

--  date_add 时间偏移 加三年
--second
--minute
--hour
--day
--week
--month
--quarter
--year
SELECT date_add(YEAR, 3,ts),date_add(YEAR, 3,daytime) from users u ;
-- 时间偏移 加一个月

SELECT date_add(MONTH  , 3,ts),date_add(MONTH, 3,daytime) from users u ;
-- 时间偏移 加小时
SELECT date_add(HOUR , 3,ts),date_add(HOUR, 3,daytime) from users u ;

-- 时间偏移 加分钟
SELECT date_add(MINUTE , 3,ts),date_add(MINUTE, 3,daytime) from users u ;

--  date_diff 时间偏移 间隔
--后面减去前面计算得到值
SELECT date_diff(SECOND , date_add(SECOND , 3,ts),ts) as internal  from users u ;

--  date_sub 时间偏移 减
SELECT date_sub(MINUTE , 3,ts),date_sub(MINUTE, 3,daytime) from users u ;

-- 时间格式化formatDateTime
--使用格式修饰符来指定结果字符串的样式。«Example» 列是对2018-01-02 22:33:44的格式化结果。
--
--修饰符	描述	示例
--%C	年除以100并截断为整数(00-99)	20
--%d	月中的一天，零填充（01-31)	02
--%D	短MM/DD/YY日期，相当于%m/%d/%y	01/02/2018
--%e	月中的一天，空格填充（ 1-31)	2
--%F	短YYYY-MM-DD日期，相当于%Y-%m-%d	2018-01-02
--%G	ISO周号的四位数年份格式， 从基于周的年份由ISO 8601定义 标准计算得出，通常仅对％V有用	2018
--%g	两位数的年份格式，与ISO 8601一致，四位数表示法的缩写	18
--%H	24小时格式（00-23)	22
--%I	12小时格式（01-12)	10
--%j	一年中的一天 (001-366)	002
--%m	月份为十进制数（01-12)	01
--%M	分钟(00-59)	33
--%n	换行符(")	
--%p	AM或PM指定	PM
--%Q	季度（1-4)	1
--%R	24小时HH:MM时间，相当于%H:%M	22:33
--%S	秒 (00-59)	44
--%t	水平制表符(’)	
--%T	ISO8601时间格式(HH:MM:SS)，相当于%H:%M:%S	22:33:44
--%u	ISO8601工作日为数字，星期一为1(1-7)	2
--%V	ISO8601周编号(01-53)	01
--%w	工作日为十进制数，周日为0(0-6)	2
--%y	年份，最后两位数字（00-99)	18
--%Y	年	2018
--%%	%符号	%

-- 调整时区是上海的时区
SELECT formatDateTime(now('Asia/Shanghai') ,'%F-%T') ;

-- 高阶函数
-- lamda 表达式，使用列表


-- json 函数 抽取字段，这个会通过索引查询吗
select JSONExtractString('{"a": "hello", "b": [-100, 200.0, 300]}', 'a') ='hello' 

-- 格式化读取的文件的字节大小，返回字符串
SELECT arrayJoin([1, 1024, 1024*1024, 192851925]) AS filesize_bytes,
    formatReadableSize(filesize_bytes) AS filesize

--bar 函数
--bar(x, min, max, width)

SELECT u.age,  
       bar(u.age, (SELECT MIN(age) FROM users ), (SELECT MAX (age) FROM users ), 5) AS bar_result  
FROM users u;


-- 聚合函数
    WITH histogram(5)(rand() % 100) AS hist
SELECT
    arrayJoin(hist).3 AS height,
    bar(height, 0, 6, 5) AS bar
FROM
(
    SELECT *
    FROM system.numbers
    LIMIT 20
);

SELECT  * from users u ;
-- histogram 直方图函数，第一个参数是显示几个，第二个参数是数据源
--会自动将数据拆分成想要的区间
 SELECT *
    FROM system.numbers
    LIMIT 20
   
    EXPLAIN indexes = 1
    SELECT histogram(5)(number + 1)
FROM (
    SELECT *
    FROM system.numbers
    LIMIT 20
)

-- 优化查询
-- 添加调表索引
--ALTER TABLE skip_table ADD INDEX vix my_value TYPE set(100) GRANULARITY 2;
-- 添加的索引只对新数据有效，
--对历史数据无效
-- 使用👇🏻来让历史数据生效
ALTER TABLE skip_table MATERIALIZE INDEX vix;
```