1.ClickHouse 入门
1.1 ClickHouse 的特点
列式储存的好处:
➢ 对于列的聚合,计数,求和等统计操作原因优于行式存储。
➢ 由于某一列的数据类型都是相同的,针对于数据存储更容易进行数据压缩,每一列选择更优的数据压缩算法,大大提高了数据的压缩比重。
➢ 由于数据压缩比更好,一方面节省了磁盘空间,另一方面对于
cache
也有了更大的发挥空间。
几乎覆盖了标准 SQL
的大部分语法,包括
DDL
和
DML
,以及配套的各种函数,用户管理及权限管理,数据的备份与恢复。
ClickHouse 和
MySQL
类似,把表级的存储引擎插件化,根据表的不同需求可以设定不同的存储引擎。目前包括合并树、日志、接口和其他等多种引擎。
ClickHouse 采用类
LSM Tree
的结构,数据写入后定期在后台
Compaction
。通过类
LSM tree
的结构,
ClickHouse
在数据导入时全部是顺序
append
写,写入后数据段不可更改,在后台 compaction 时也是多个段
merge sort
后顺序写回磁盘。顺序写的特性,充分利用了磁盘的吞吐能力,即便在 HDD
上也有着优异的写入性能。
官方公开 benchmark
测试显示能够达到
50MB-200MB/s
的写入吞吐能力,按照每行100Byte 估算,大约相当于
50W-200W
条
/s
的写入速度。
ClickHouse 将数据划分为多个
partition
,每个
partition
再进一步划分为多个
index granularity(索引粒度
)
,然后通过多个
CPU
核心分别处理其中的一部分来实现并行数据处理。 在这种设计下,
单条
Query
就能利用整机所有
CPU
。
极致的并行处理能力,极大的降低了查询延时。
所以,ClickHouse
即使对于大量数据的查询也能够化整为零平行处理。但是有一个弊端就是对于单条查询使用多 cpu
,就不利于同时并发多条查询。所以对于高
qps
的查询业务,ClickHouse 并不是强项。
2.ClickHouse 的安装
2.1安装前准备
CentOS 取消打开文件数限制
sudo vim /etc/security/limits.conf
sudo vim /etc/security/limits.d/20-nproc.conf
sudo /home/andre/bin/xsync /etc/security/limits.conf
sudo /home/andre/bin/xsync
注意:脚本中的 andre 替换为自己执行安装操作的非root用户
安装依赖
sudo yum install -y libtool
sudo yum install -y *unixODBC*
在其他两台节点执行相同操作
2.2 单机安装
官网:https://clickhouse.tech/
下载地址:http://repo.red-soft.biz/repos/clickhouse/stable/el7/
创建目录
mkdir -p /opt/software/clickhouse/
将 ClickHouse 相关安装资料上传到 /opt/software/clickhouse/ 目录下
将安装文件分发到另外两台节点
sudo rpm -qa|grep clickhouse 查看安装情况
修改配置文件
- 把 :: 的注释打开,这样的话才能让 ClickHouse 被除本机以外的服务器访问
- 分发配置文件
注意:
在这个文件中,有 ClickHouse
的一些默认路径配置,比较重要的
数据文件路径:/var/lib/clickhouse/
日志文件路径:/var/log/clickhouse-server/clickhouse-server.log
启动 Server
sudo systemctl start clickhouse-server
三台机器上关闭开机自启
sudo systemctl disable clickhouse-server
使用 client 连接 server
clickhouse-client -m
3.数据类型
3.1 整型
固定长度的整型,包括有符号整型或无符号整型。
整型范围(-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]
3.2 浮点型
Float32 - float
Float64 – double
3.3 布尔型
可以使用 UInt8 类型,取值限制为 0 或 1。
3.4 Decimal 型
有符号的浮点数,可在加、减和乘法运算过程中保持精度。对于除法,最低有效数字会被丢弃(不舍入)。
有三种声明:
➢
Decimal32(s)
,相当于
Decimal(9-s,s)
,有效位数为
1~9
➢
Decimal64(s)
,相当于
Decimal(18-s,s)
,有效位数为
1~18
➢
Decimal128(s)
,相当于
Decimal(38-s,s)
,有效位数为
1~38
3.5 字符串
1
)
String
字符串可以任意长度的。它可以包含任意的字节集,包含空字节。
2
)
FixedString(N)
固定长度 N
的字符串,
N
必须是严格的正自然数。当服务端读取长度小于
N
的字符串时候,通过在字符串末尾
添加空字节
来达到
N
字节长度。
当服务端读取长度大于 N
的字符串时候,将返回错误消息。
3.6 枚举类型
包括 Enum8
和
Enum16
类型。
Enum
保存
'string'= integer
的对应关系。
Enum8 用
'String'= Int8
对描述。
Enum16 用
'String'= Int16
对描述。
3.7 时间类型
目前 ClickHouse
有三种时间类型
➢
Date
接受
年
-
月
-
日
的字符串比如 ‘2022-2-16’
➢
Datetime
接受
年
-
月
-
日 时
:
分
:
秒
的字符串比如 ‘2022-2-16 18:56:18’
➢
Datetime64
接受
年
-
月
-
日 时
:
分
:
秒
.
亚秒的字符串比如‘2022-2-16 18:56:18.23’
日期类型,用两个字节存储,表示从 1970-01-01 (
无符号
)
到当前的日期值。
3.8 数组
Array(T)
:
由
T
类型元素组成的数组。
T 可以是任意类型,包含数组类型。 但不推荐使用多维数组,
ClickHouse
对多维数组的支持有限。例如,不能在 MergeTree
表中存储多维数组。
4.表引擎
4.1 表引擎的使用
表引擎是 ClickHouse
的一大特色。可以说, 表引擎决定了如何存储表的数据。包括:
➢ 数据的存储方式和位置,写到哪里以及从哪里读取数据。
➢ 支持哪些查询以及如何支持。
➢ 并发数据访问。
➢ 索引的使用(如果存在)。
➢ 是否可以执行多线程请求。
➢ 数据复制参数。
表引擎的使用方式就是必须显式在创建表时定义该表使用的引擎,以及引擎使用的相关参数。
特别注意:引擎的名称大小写敏感
4.2 TinyLog
以列文件的形式保存在磁盘上,不支持索引,没有并发控制。一般保存少量数据的小表,生产环境上作用有限。可以用于平时练习测试用。
4.3 Memory
内存引擎,数据以未压缩的原始形式直接保存在内存当中,服务器重启数据就会消失。读写操作不会相互阻塞,不支持索引。简单查询下有非常非常高的性能表现(
超过 10G/s
)。
一般用到它的地方不多,除了用来测试,就是在需要非常高的性能,同时数据量又不太大(上限大概 1
亿行)的场景。
4.4 MergeTree
ClickHouse 中
最强大的表引擎当属
MergeTree
(合并树)引擎及该系列(
*MergeTree
)
中的其他引擎,
支持索引和分区
,地位可以相当于
innodb
之于
Mysql
。
而且基于 MergeTree
,还衍生除了很多小弟,也是非常有特色的引擎。
4.5 ReplacingMergeTree
ReplacingMergeTree 是
MergeTree
的一个变种,它存储特性完全继承
MergeTree
,只是多了一个
去重
的功能。
尽管 MergeTree
可以设置主键,但是
primary key
其实没有唯一约束的功能。如果你想处理掉重复的数据,可以借助这个 ReplacingMergeTree
。
1)去重时机
数据的去重只会在合并的过程中出现。
合并会在未知的时间在后台进行,所以你无法预先作出计划。有一些数据可能仍未被处理。
2)去重范围
如果表经过了分区,去重只会在分区内部进行去重,不能执行跨分区的去重。
4.6 SummingMergeTree
对于不查询明细,只关心以维度进行汇总聚合结果的场景。如果只使用普通的MergeTree
的话,无论是存储空间的开销,还是查询时临时聚合的开销都比较大。
ClickHouse 为了这种场景,提供了一种能够“预聚合”的引擎
SummingMergeTree
5. SQL 操作
5.1 Insert
基本与标准
SQL
(
MySQL
)基本一致
(1)标准
insert into [table_name] values(…),(….)
(2)从表到表的插入
insert into [table_name] select a,b,c from [table_name_2]
5.2 Update 和 Delete
(1)删除操作
alter table t_order_smt delete where sku_id ='sku_001';
(2)修改操作
alter table t_order_smt update total_amount=toDecimal32(2000.00,2) where id =102;
5.3 查询操作
➢
支持子查询
➢
支持
CTE(Common Table Expression
公用表表达式
with
子句
)
➢
支持各种
JOIN
,但是 JOIN
操作无法使用缓存,所以即使是两次相同的
JOIN
语句, ClickHouse 也会视为两条新
SQL
➢ 窗口函数(官方正在测试中...)
➢
不支持自定义函数
➢
GROUP BY
操作增加了
with rollup\with cube\with total
用来计算小计和总计。
5.4 alter 操作
1
)新增字段
alter table tableName add column newcolname String after col1;
2
)修改字段类型
alter table tableName modify column newcolname String;
3
)删除字段
alter table tableName drop column newcolname;
5.5 导出数据
clickhouse-client --query "select * from t_order_mt where
create_time='2020-06-01 12:00:00'" --format CSVWithNames>