蒙文网站建设的意义,网站点击软件排名,网络运维工程师项目经验,网站ps多大尺寸第 1 章 ClickHouse 入 门 ClickHouse 是俄罗斯的 Yandex 于 2016 年开源的列式存储数据库#xff08;DBMS#xff09;#xff0c;使用 C 语言编写#xff0c;主要用于在线分析处理查询#xff08;OLAP#xff09;#xff0c;能够使用 SQL 查询实时生成分析数据报告。
… 第 1 章 ClickHouse 入 门 ClickHouse 是俄罗斯的 Yandex 于 2016 年开源的列式存储数据库DBMS使用 C 语言编写主要用于在线分析处理查询OLAP能够使用 SQL 查询实时生成分析数据报告。
1.1 ClickHouse 的特点
1.1.1 列式存储 以下面的表为例 Id Name Age 1 张三 18 2 李四 22 3 王五 34
1采用行式存储时数据在磁盘上的组织结构为 好处是想查某个人所有的属性时可以通过一次磁盘查找加顺序读取就可以。但是当想查所有人的年龄时需要不停的查找或者全表扫描才行遍历的很多数据都是不需要的。
2采用列式存储时数据在磁盘上的组织结构为 这时想查所有人的年龄只需把年龄那一列拿出来就可以了。
3列式储存的好处
对于列的聚合计数求和等统计操作原因优于行式存储。
由于某一列的数据类型都是相同的针对于数据存储更容易进行数据压缩每一列
选择更优的数据压缩算法大大提高了数据的压缩比重。
由于数据压缩比更好一方面节省了磁盘空间另一方面对于 cache 也有了更大的发挥空间。
1.1.2 DBMS 的功能 几乎覆盖了标准SQL 的大部分语法包括 DDL 和 DML以及配套的各种函数用户管理及权限管理数据的备份与恢复。
1.1.3 多样化引擎 ClickHouse 和 MySQL 类似把表级的存储引擎插件化根据表的不同需求可以设定不同的存储引擎。目前包括合并树、日志、接口和其他四大类 20 多种引擎。
1.1.4 高吞吐写入能力 ClickHouse 采用类LSM Tree 的结构数据写入后定期在后台Compaction。通过类LSM tree 的结构ClickHouse 在数据导入时全部是顺序 append 写写入后数据段不可更改在后台compaction 时也是多个段merge sort 后顺序写回磁盘。顺序写的特性充分利用了磁盘的吞吐能力即便在 HDD 上也有着优异的写入性能。 官方公开 benchmark 测试显示能够达到 50MB-200MB/s 的写入吞吐能力按照每行100Byte 估算大约相当于 50W-200W 条/s 的写入速度。
1.1.5 数据分区与线程级并行 ClickHouse 将数据划分为多个 partition每个 partition 再进一步划分为多个 index granularity(索引粒度)然后通过多个CPU 核心分别处理其中的一部分来实现并行数据处理。在这种设计下单条 Query 就能利用整机所有CPU。极致的并行处理能力极大的降低了查询延时。 所以ClickHouse 即使对于大量数据的查询也能够化整为零平行处理。但是有一个弊端就是对于单条查询使用多 cpu就不利于同时并发多条查询。所以对于高 qps 的查询业务 ClickHouse 并不是强项。
1.1.6 性能对比
某网站精华帖中对几款数据库做了性能对比。
1单表查询 2关联查询 结论: ClickHouse 像很多 OLAP 数据库一样单表查询速度优于关联查询而且 ClickHouse的两者差距更为明显。 第 2 章 ClickHouse 的安装
2.1 准备工作
2.1.1 确定防火墙处于关闭状态
# 查看防火墙状态
systemctl status firewalld
# 关闭防火墙
systemctl stop firewalld提示生产环境不建议关闭防火墙 2.1.2 CentOS 取消打开文件数限制
1查看文件数限制
ulimit -a 2在服务器 vim etc/security/limits.conf 文件的末尾加入以下内容
# soft指当前数值hard指最大数值
* soft nofile 65536
* hard nofile 65536
* soft nproc 131072
* hard nproc 1310723在服务器 vim /etc/security/limits.d/20-nproc.conf 文件的末尾加入以下内容
* soft nproc 131072
* hard nproc 131072
4在服务器 vim /etc/security/limits.d/20-nofile.conf 文件的末尾加入以下内容
* soft nofile 65536
* hard nofile 65536
5重新登录即可生效
2.1.3 安装依赖
# 在联网或者有yum源的情况下安装依赖
sudo yum install -y libtool
sudo yum install -y *unixODBC*
2.1.4 CentOS 取消 SELINUX
1修改 vim /etc/selinux/config 中的 SELINUXdisabled
SELINUXdisabled
2临时关闭 SELINUX
# 查看SELINUX状态
getenforce
# 关闭SELINUX
setenforce 0
2.2 单机安装
官方网址https://clickhouse.com/
下载地址https://packages.clickhouse.com/rpm/stable/
2.2.1 创建 clickhouse 目录
在服务器 /opt/software 下创建 clickhouse 目录
cd /opt/software/
mkdir clickhouse
2.2.2 上传安装文件
将 ClickHouse 安装文件上传到 clickhouse 目录下 2.2.3 安装
# 安装
sudo rpm -ivh *.rpm
# 查看安装情况
sudo rpm -qa|grep clickhouse
2.2.4 修改配置文件
sudo vim /etc/clickhouse-server/config.xml
1把 listen_host::/listen_host 的注释打开这样的话才能让 ClickHouse 被除本机以外的服务器访问 2默认的路径配置
在这个文件中有 ClickHouse 的一些默认路径配置比较重要的比如数据文件存储路径、日志文件存储路径 数据文件路径path/var/lib/clickhouse//path 日志文件路径log/var/log/clickhouse-server/clickhouse-server.log/log 注意建议改成服务器磁盘比较充足的磁盘路径 3修改时区
timezoneAsia/Shanghai/timezone 4添加数据压缩算法
ClickHouse支持多种压缩算法包括LZ4、LZ4HC、ZSTD、Brotli等。其中LZ4是ClickHouse默认的压缩算法。
LZ4LZ4是一种无损压缩算法它的压缩速度非常快但压缩比相对较低。LZ4的压缩原理是将重复的数据块替换为一个标记和重复次数从而达到压缩的效果。LZ4HCLZ4HC是LZ4的一种高压缩比版本它的压缩速度相对较慢但压缩比相对较高。LZ4HC的压缩原理是通过哈希表来查找重复的数据块从而达到高压缩比的效果。ZSTDZSTD是一种高压缩比的压缩算法它的压缩速度相对较慢但压缩比相对较高。ZSTD的压缩原理是通过字典来查找重复的数据块从而达到高压缩比的效果。BrotliBrotli是一种高压缩比的压缩算法它的压缩速度相对较慢但压缩比相对较高。Brotli的压缩原理是通过有限状态机来查找重复的数据块从而达到高压缩比的效果。 compressioncasemin_part_size10000000000/min_part_sizemin_part_size_ratio0.01/min_part_size_ratiomethodlz4/method/case/compression2.2.5 启动
# 启动
sudo systemctl start clickhouse-server
# 查看启动的进程
ps -ef|grep clickhouse
2.2.6 设置开机自起
# 开机自起可选
sudo systemctl disable clickhouse-server
2.2.7 使用 client 连接 server
clickhouse-client -m [--password]-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] 使用场景 个数、数量、也可以存储型 id。 3.2 浮点型
Float32 - float
Float64 – double 注意建议尽可能以整数形式存储数据。例如将固定精度的数字转换为整数值如时间用毫秒为单位表示因为浮点型进行计算时可能引起四舍五入的误差。 使用场景一般数据值比较小不涉及大量的统计计算精度要求不高的时候。比如保存商品的重量。 3.3 布尔型
没有单独的类型来存储布尔值。可以使用 UInt8 类型取值限制为 0 或 1。
3.4 Decimal 型
有符号的浮点数可在加、减和乘法运算过程中保持精度。对于除法最低有效数字会被丢弃不舍入。
有三种声明
Decimal32(s)相当于 Decimal(9-s, s)有效位数为 1~9Decimal64(s)相当于 Decimal(18-s, s)有效位数为 1~18Decimal128(s)相当于 Decimal(38-s, s)有效位数为 1~38
s 标识小数位 使用场景 一般金额字段、汇率、利率等字段为了保证小数点精度都使用 Decimal 进行存储。 3.5 字符串
1String字符串可以任意长度的。它可以包含任意的字节集包含空字节。
2FixedString(N)固定长度 N 的字符串N 必须是严格的正自然数。当服务端读取长度小于 N 的字符串时候通过在字符串末尾添加空字节来达到 N 字节长度。 当服务端读取长度大于 N 的字符串时候将返回错误消息。与 String 相比极少会使用 FixedString因为使用起来不是很方便。 使用场景名称、文字描述、字符型编码。 固定长度的可以保存一些定长的内容比 如一些编码性别等但是考虑到一定的变化风险带来收益不够明显所以定长字符串使用意义有限。 3.6 枚举类型
包括 Enum8 和 Enum16 类型。Enum 保存 ‘string’ integer 的对应关系。
Enum8 用 ‘String’ Int8 对描述。
Enum16 用 ‘String’ Int16 对描述。
1用法演示
创建一个带有一个枚举 Enum8(‘hello’ 1, ‘world’ 2) 类型的列
CREATE TABLE t_enum
(x Enum8(hello 1, world 2)
)
ENGINE TinyLog;
2这个 x 列只能存储类型定义中列出的值’hello’或’world’
INSERT INTO t_enum VALUES (hello), (world), (hello);3如果尝试保存任何其他值ClickHouse 抛出异常
INSERT INTO t_enum VALUES(a); 4如果需要看到对应行的数值则必须将 Enum 值转换为整数类型
SELECT CAST(x, Int8) FROM t_enum;使用场景对一些状态、类型的字段算是一种空间优化也算是一种数据约束。但是实际使用中往往因为一些数据内容的变化增加一定的维护成本甚至是数据丢失问题。所以谨慎使用 3.7 时间类型
目前 ClickHouse 有三种时间类型
Date 接受年-月-日的字符串比如 ‘2019-12-16’Datetime 接受年-月-日 时:分:秒的字符串比如 ‘2019-12-16 20:50:10’Datetime64 接受年-月-日 时:分:秒.亚秒的字符串比如 ‘2019-12-16 20:50:10.66’
日期类型用两个字节存储表示从 1970-01-01 (无符号) 到当前的日期值。
3.8 数组
Array(T)由 T 类型元素组成的数组。
T 可以是任意类型包含数组类型。但不推荐使用多维数组ClickHouse 对多维数组的支持有限。例如不能在 MergeTree 表中存储多维数组。
1创建数组方式 1使用 array 函数
2创建数组方式 2使用方括号 第 4 章 表引擎
4.1 表引擎的使用
表引擎是 ClickHouse 的一大特色。可以说表引擎决定了如何存储表的数据。包括
数据的存储方式和位置写到哪里以及从哪里读取数据。支持哪些查询以及如何支持。并发数据访问。索引的使用如果存在。是否可以执行多线程请求。数据复制参数。
表引擎的使用方式就是必须显式在创建表时定义该表使用的引擎以及引擎使用的相关参数。 特别注意引擎的名称大小写敏感 4.2 TinyLog 以列文件的形式保存在磁盘上不支持索引没有并发控制。一般保存少量数据的小表生产环境上作用有限。
CREATE TABLE t_tinylog
(id String,name String
)
ENGINE TinyLog;
4.3 Memory 内存引擎数据以未压缩的原始形式直接保存在内存当中服务器重启数据就会消失。读写操作不会相互阻塞不支持索引。简单查询下有非常非常高的性能表现超过 10G/s。 一般用到它的地方不多除了用来测试就是在需要非常高的性能同时数据量又不太 大上限大概 1 亿行的场景。
4.4 MergeTree ClickHouse 中最强大的表引擎当属 MergeTree合并树引擎及该系列*MergeTree中的其他引擎支持索引和分区地位可以相当于 innodb 之于 Mysql。
1建表语句
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);
2插入数据
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);
MergeTree 其实还有很多参数(绝大多数用默认值即可)但是这三个参数是更加重要的也涉及了关于 MergeTree 的很多概念。
4.4.1 partition by 分区(可选)
1作用
分区的目的主要是降低扫描的范围优化查询速度
2如果不填
只会使用一个分区。
3分区目录
MergeTree 是以列文件索引文件表定义文件组成的但是如果设定了分区那么这些文件就会保存到不同的分区目录中。
分区目录文件命名规则PartitionId_MinBlockNum_MaxBlockNum_Level分区值_最小分区_最大分区块编号_合并层级
1PartitionId
数据分区 ID 生成规则数据分区规则由分区 ID 决定分区ID由 PARTITION BY 分区键决定。根据分区键字段类型ID生成规则可以分为 a.未定义分区键没有定义 PARTITION BY 默认生成一个目录名为 all 的数据分区所有数据均存放在 all 目录下。 b.整型分区键分区键为整型那么直接用该整型值的字符串形式作为分区ID。 c.日期类分区键分区键为日期类型或者可以转化成日期类型。 d.其他类型分区键String、Float 类型等通过128位的 Hash 算法取其 Hash 值作为分区ID。
2MinBlockNum最小分区块编号自增类型从1开始向上递增。每产生一个新的目录分区就向上递增一个数字。
3MaxBlockNum最大分区块编号新创建的分区 MinBlockNum 等于 MaxBlockNum 的编号。 4Level合并的层级被合并的次数。合并次数越多层级值越大。4并行
分区后面对涉及跨分区的查询统计ClickHouse 会以分区为单位并行处理。
5数据写入与分区合并
任何一个批次的数据写入都会产生一个临时分区不会纳入任何一个已有的分区。写入后的某个时刻大概 10-15 分钟后ClickHouse 会自动执行合并操作等不及也可以手动通过 optimize 执行把临时分区的数据合并到已有分区中。
OPTIMIZE TABLE xxxx FINAL;
# 只针对某一个分区做合并操作
OPTIMIZE TABLE xxxx PARTITION yyyy FINAL;
6例如
再次执行上面的插入操作
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),
查看数据并没有纳入任何分区 手动 optimize 之后optimize table t_order_mt final在查询。 4.4.2 primary key 主键(可选)
ClickHouse 中的主键和其他数据库不太一样它只提供了数据的一级索引但是却不是唯一约束。这就意味着是可以存在相同 primary key 的数据的。
主键的设定主要依据是查询语句中的 where 条件。
根据条件通过对主键进行某种形式的二分查找能够定位到对应的 index granularity避免了全表扫描。
index granularity直接翻译的话就是索引粒度指在稀疏索引中两个相邻索引对应数据的间隔。ClickHouse 中的 MergeTree 默认是 8192。官方不建议修改这个值除非该列存在大量重复值比如在一个分区中几万行才有一个不同数据。
稀疏索引 稀疏索引的好处就是可以用很少的索引数据定位更多的数据代价就是只能定位到索引粒度的第一行然后再进行进行一点扫描。
4.4.3 order by必选
order by 设定了分区内的数据按照哪些字段顺序进行有序保存。
order by 是 MergeTree 中唯一一个必填项甚至比 primary key 还重要因为当用户不设置主键的情况很多处理会依照 order by 的字段进行处理。
要求主键必须是 order by 字段的前缀字段。
比如order by 字段是 (id, sku_id) 那么主键必须是 id 或者(id, sku_id)
4.4.4 二级索引
目前在 ClickHouse 的官网上二级索引的功能在 v20.1.2.4 之前是被标注为实验性的在这个版本之后默认是开启的。
1老版本使用二级索引前需要增加设置
是否允许使用实验性的二级索引v20.1.2.4 开始这个参数已被删除默认开启
set allow_experimental_data_skipping_indices1;
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);
4对比效果
使用下面语句进行测试可以看出二级索引能够为非主键字段的查询发挥作用。
clickhouse-client [--password] --send_logs_leveltrace select * from t_order_mt2 where total_amount toDecimal32(900., 2);
4.4.5 数据 TTL
TTL 即 Time To LiveMergeTree 提供了可以管理数据表或者列的生命周期的功能。
1列级别 TTL
1创建测试表
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);
2插入数据
INSERT INTO t_order_mt3 VALUES
(106, sku_001, 1000.00, NOW()),
(107, sku_002, 2000.00, NOW()),
(110, sku_003, 600.00, NOW() INTERVAL 10 SECOND);
3手动合并查看效果到期后指定的字段数据将变为默认值 2表级 TTL
下面的这条语句是数据会在 create_time 之后 10 秒丢失。
ALTER TABLE t_order_mt3 MODIFY TTL create_time INTERVAL 10 SECOND;
涉及判断的字段必须是 Date 或者 Datetime 类型推荐使用分区的日期字段。
能够使用的时间周期
SECONDMINUTEHOURDAYWEEKMONTHQUARTERYEAR
4.5 ReplacingMergeTree ReplacingMergeTree 是 MergeTree 的一个变种它存储特性完全继承 MergeTree只是多了一个去重的功能。 尽管 MergeTree 可以设置主键但是 primary key 其实没有唯一约束的功能。如果你想处理掉重复的数据可以借助这个 ReplacingMergeTree。
4.5.1 去重时机
数据的去重只会在合并的过程中出现。合并会在未知的时间在后台进行所以你无法预先作出计划。有一些数据可能仍未被处理。
4.5.2 去重范围
如果表经过了分区去重只会在分区内部进行去重不能执行跨分区的去重。
所以 ReplacingMergeTree 能力有限 ReplacingMergeTree 适用于在后台清除重复的数据以节省空间但是它不保证没有重复的数据出现。
4.5.3 案例演示
1创建表
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() 填入的参数为版本字段重复数据保留版本字段值最大的。
如果不填版本字段默认按照插入顺序保留最后一条。
2向表中插入数据
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);
3重复再插入一次执行第一次查询
SELECT * FROM t_order_rmt; 4手动合并
OPTIMIZE TABLE t_order_rmt FINAL;
5再执行一次查询
SELECT * FROM t_order_rmt; 4.5.4 通过测试得到结论
实际上是使用 order by 字段作为唯一键去重不能跨分区只有同一批插入新版本或合并分区时才会进行去重认定重复的数据保留取版本字段值最大的如果版本字段相同则按插入顺序保留最后一笔
4.6 SummingMergeTree 对于不查询明细只关心以维度进行汇总聚合结果的场景。如果只使用普通的MergeTree的话无论是存储空间的开销还是查询时临时聚合的开销都比较大。
ClickHouse 为了这种场景提供了一种能够“预聚合”的引擎 SummingMergeTree
4.6.1 案例演示
1创建表
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);
2插入数据
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-02 12:00:00);
3重复再插入一次执行第一次查询
SELECT * FROM t_order_smt; 4手动合并
OPTIMIZE TABLE t_order_smt FINAL;
5在执行一次查询
select * from t_order_smt; 4.6.2 通过结果可以得到以下结论
以 SummingMergeTree中指定的列作为汇总数据列可以填写多列必须数字列如果不填以所有非维度列且为数字列的字段为汇总数据列以 order by 的列为准作为维度列其他的列按插入顺序保留第一行不在一个分区的数据不会被聚合只有在同一批次插入新版本或分片合并时才会进行聚合
4.6.3 开发建议
设计聚合表的话唯一键值、流水号可以去掉所有字段全部是维度、度量或者时间戳。
4.6.4 问题
能不能直接执行以下 SQL 得到汇总值
SELECT total_amount FROM XXX WHERE province_name AND create_date xxx
不行可能会包含一些还没来得及聚合的临时明细
如果要是获取汇总值还是需要使用 sum 进行聚合这样效率会有一定的提高但本身 ClickHouse 是列式存储的效率提升有限不会特别明显。
SELECT SUM(total_amount) FROM XXX WHERE province_name AND create_date xxx
第 5 章 SQL 操 作
基本上来说传统关系型数据库以 MySQL 为例的 SQL 语句ClickHouse 基本都支持这里只介绍 ClickHouse 与标准 SQLMySQL不一致的地方。
5.1 Insert 与标准 SQLMySQL基本一致
1标准
INSERT INTO [table_name] VALUES (...), (...)
2从表到表的插入
INSERT INTO [table_name] SELECT a,b,c FROM [table_name_2]5.2 Update 和 Delete
ClickHouse 提供了 Delete 和 Update 的能力这类操作被称为 Mutation 查询它可以看做 Alter 的一种。
虽然可以实现修改和删除但是和一般的 OLTP 数据库不一样Mutation 语句是一种很“重”的操作而且不支持事务。
“重”的原因主要是每次修改或者删除都会导致放弃目标数据的原有分区重建新分区。所以尽量做批量的变更不要进行频繁小数据的操作。
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;由于操作比较“重”所以 Mutation 语句分两步执行同步执行的部分其实只是进行新增数据新增分区和并把旧分区打上逻辑上的失效标记。直到触发分区合并的时候才会删除旧数据释放磁盘空间一般不会开放这样的功能给用户由管理员完成。
问题如何实现高性能 update 和 delete
回答在创建表时添加两个标记字段如下所示
CREATE TABLE A
(a xxx,b xxx,c xxx,_sign UInt8,_version UInt32
)
那么对于更新操作则相当于是插入一条新的数据此时 _version 1查询的时候加上一个过滤条件WHERE _version最大进行查询。
对于删除操作通过 _sign0表示未删除1表示已删除同时 _version 1查询的时候加上一个过滤条件WHERE _sign 0 AND _version最大进行查询。
那么如果时间久了数据膨胀了怎么办可以提供类似合并的机制定期把过期数据进行清除。
5.3 查询操作
ClickHouse 基本上与标准 SQL 差别不大但也有一些区别如果按照标准SQL写 不支持的ClickHouse 会在底层转换成
支持子查询支持 CTE(Common Table Expression 公用表表达式 with 子句)支持各种 JOIN但是 JOIN 操作无法使用缓存所以即使是两次相同的 JOIN 语句ClickHouse 也会视为两条新 SQL支持窗口函数支持自定义函数GROUP BY 操作增加了 with rollup\with cube\with total 用来计算小计和总计。
5.3.1 插入数据
-- 清空数据
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),
(106,sku_001,1000.00,2020-06-04 12:00:00),
(107,sku_002,2000.00,2020-06-04 12:00:00),
(108,sku_004,2500.00,2020-06-04 12:00:00),
(109,sku_002,2000.00,2020-06-04 12:00:00),
(110,sku_003,600.00,2020-06-01 12:00:00);
5.3.2 count 查询
select count() from t_order_mt
5.3.3 分组查询
select
sku_id,
any(id),
any(total_amount),
any(formatDateTime(toDateTime(create_time),%Y-%m-%d %R:%S)) AS create_time
from t_order_mt
group by sku_id
order by id 注意 如果分组后想返回其他字段必须用 any 函数意思是返回第一个遇到的值类似MySQL默认返回第一次遇到的值还有其他函数比如anyHeavy使用选择一个频繁出现的值anyLast选择遇到的最后一个值等。如果想格式化日期可以使用 formatDateTime(toDateTime(create_time),%Y-%m-%d %R:%S) 5.3.4 使用 with
1with rollup从右至左去掉维度进行小计
SELECT id, sku_id, sum(total_amount) FROM t_order_mt GROUP BY id, sku_id WITH ROLLUP;2with cube : 从右至左去掉维度进行小计再从左至右去掉维度进行小计
SELECT id, sku_id, sum(total_amount) FROM t_order_mt GROUP BY id, sku_id WITH CUBE;3with totals: 只计算合计
SELECT id, sku_id, sum(total_amount) FROM t_order_mt GROUP BY id, sku_id WITH TOTALS;5.4 alter 操作
同 MySQL 的修改字段基本一致
5.4.1 新增字段
ALTER TABLE tableName ADD COLUMN newcolname String AFTER col1;5.4.2 修改字段类型
ALTER TABLE tableName MODIFY COLUMN newcolname String;
5.4.3 删除字段
ALTER TABLE tableName DROP COLUMN newcolname;
5.5 导出数据
clickhouse-client --query select * from t_order_mt where create_time2020-06-01 12:00:00 --format CSVWithNames /opt/module/data/rs1.csv
更多支持格式参考https://clickhouse.com/docs/en/interfaces/formats/
第 6 章 副本
副本的目的主要是保障数据的高可用性即使一台 ClickHouse 节点宕机那么也可以从其他服务器获得相同的数据。
6.1 副本写入流程 6.2 配置步骤
1启动 zookeeper 集群
2在 hadoop102 的 /etc/clickhouse-server/config.d 目录下创建一个名为 metrika.xml 的配置文件内容如下
?xml version1.0?
yandexzookeeper-serversnode index1hosthadoop102/hostport2181/port/nodenode index2hosthadoop103/hostport2181/port/nodenode index3hosthadoop104/hostport2181/port/node/zookeeper-servers
/yandex 注也可以不创建外部文件直接在 config.xml 中指定zookeeper 提示这里用三台机器作为集群分别为hadoop102 、hadoop103 和 hadoop104 3同步到 hadoop103 和 hadoop104 上
4在 hadoop102 的/etc/clickhouse-server/config.xml 中增加
zookeeper inclzookeeper-servers optionaltrue /
include_from/etc/clickhouse-server/config.d/metrika.xml/include_from 5同步到 hadoop103 和 hadoop104 上然后分别在 hadoop102、hadoop103 和 hadoop104 上重启 ClickHouse 服务 注意因为修改了配置文件如果以前启动了服务需要重启 sudo clickhouse restart 注意我们演示副本操作只需要在 hadoop102 和 hadoop103 两台服务器即可上面的操作我们 hadoop104 可以你不用同步我们这里为了保证集群中资源的一致性做了同 步。 6在 hadoop102 和 hadoop103 上分别建表
副本只能同步数据不能同步表结构所以我们需要在每台机器上自己手动建表
① hadoop102
CREATE TABLE t_order_rep2
(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);
② hadoop103
CREATE TABLE t_order_rep2
(id UInt32,sku_id String,total_amount Decimal(16, 2),create_time Datetime
)
ENGINE ReplicatedMergeTree(/clickhouse/table/01/t_order_rep, rep_103)
PARTITION BY toYYYYMMDD(create_time)
PRIMARY KEY id
ORDER BY (id, sku_id);
③ hadoop104
CREATE TABLE t_order_rep2
(id UInt32,sku_id String,total_amount Decimal(16, 2),create_time Datetime
)
ENGINE ReplicatedMergeTree(/clickhouse/table/01/t_order_rep, rep_104)
PARTITION BY toYYYYMMDD(create_time)
PRIMARY KEY id
ORDER BY (id, sku_id);
参数解释
在 ReplicatedMergeTree 中
第一个参数是分片的 zk_path 一般按照/clickhouse/table/{shard}/{table_name} 的格式写如果只有一个分片就写 01 即可。
第二个参数是副本名称相同的分片副本名称不能相同。
7在 hadoop102 上执行 insert 语句
INSERT INTO t_order_rep2 VALUES
(101,sku_001,1000.00,2020-06-01 12:00:00),
(102,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);
8在 hadoop103 上执行 select可以查询出结果说明副本配置正确
SELECT * FROM t_order_rep2; 第 7 章 分片集群
副本虽然能够提高数据的可用性降低丢失风险但是每台服务器实际上必须容纳全量数据对数据的横向扩容没有解决。
要解决数据水平切分的问题需要引入分片的概念。通过分片把一份完整的数据进行切分不同的分片分布到不同的节点上再通过 Distributed 表引擎把数据拼接起来一同使用。
Distributed 表引擎本身不存储数据有点类似于 MyCat 之于 MySql成为一种中间件通过分布式逻辑表来写入、分发、路由来操作多台节点不同分片的分布式数据。 注意ClickHouse 的集群是表级别的实际企业中大部分做了高可用但是没有用分片避免降低查询性能以及操作集群的复杂性。 7.1 集群写入流程3 分片 2 副本共 6 个节点 7.2 集群读取流程3 分片 2 副本共 6 个节点 7.3 分片 2 副本共 6 个节点集群配置供参考
配置的位置还是在之前的/etc/clickhouse-server/config.d/metrika.xml内容如下
yandexremote_servers!-- 集群名称--gmall_cluster!--集群的第一个分片--shardinternal_replicationtrue/internal_replication!--该分片的第一个副本--replicahosthadoop101/hostport9000/port/replica!--该分片的第二个副本--replicahosthadoop102/hostport9000/port/replica/shard!--集群的第二个分片--shardinternal_replicationtrue/internal_replication!--该分片的第一个副本--replicahosthadoop103/hostport9000/port/replica!--该分片的第二个副本--replicahosthadoop104/hostport9000/port/replica/shard!--集群的第三个分片--shardinternal_replicationtrue/internal_replication!--该分片的第一个副本--replicahosthadoop105/hostport9000/port/replica!--该分片的第二个副本--replicahosthadoop106/hostport9000/port/replica/shard/gmall_cluster/remote_servers
/yandex 注意也可以不创建外部文件直接在 config.xml 的 remote_servers 中指定 7.4 配置三节点版本集群及副本
7.4.1 集群及副本规划2 个分片只有第一个分片有副本 hadoop102 hadoop103 hadoop104 macros shard01/shard replicarep_1_1/replica /macros macros shard01/shard replicarep_1_2/replica /macros macros shard02/shard replicarep_2_1/replica /macros
7.4.2 配置步骤
1在 hadoop102 的 /etc/clickhouse-server/config.d 目录下创建 metrika-shard.xml 文件
?xml version1.0?
yandexremote_servers!-- 集群名称--gmall_cluster!--集群的第一个分片--shardinternal_replicationtrue/internal_replication!--该分片的第一个副本--replicahosthadoop102/hostport9000/port/replica!--该分片的第二个副本--replicahosthadoop103/hostport9000/port/replica/shard!--集群的第二个分片--shardinternal_replicationtrue/internal_replication!--该分片的第一个副本--replicahosthadoop104/hostport9000/port/replica/shard/gmall_cluster/remote_serverszookeeper-serversnode index1hosthadoop102/hostport2181/port/nodenode index2hosthadoop103/hostport2181/port/nodenode index3hosthadoop104/hostport2181/port/node/zookeeper-serversmacros!--不同机器放的分片数不一样--shard01/shard!--不同机器放的副本数不一样--replicarep_1_1/replica/macros
/yandex 注意也可以不创建外部文件直接在 config.xml 的 remote_servers 中指定 2将 hadoop102 的 metrika-shard.xml 同步到 103 和 104
3修改 103 和 104 中 metrika-shard.xml 宏的配置
1103
sudo vim /etc/clickhouse-server/config.d/metrika-shard.xml 2104
sudo vim /etc/clickhouse-server/config.d/metrika-shard.xml4在 hadoop102 上修改 /etc/clickhouse-server/config.xml 5同步 /etc/clickhouse-server/config.xml 到 103 和 104
sudo vim /etc/clickhouse-server/config.xml6重启三台服务器上的 ClickHouse 服务
sudo clickhouse restart
ps -ef |grep click
7在 hadoop102 上执行建表语句
会自动同步到 hadoop103 和 hadoop104 上集群名字要和配置文件中的一致分片和副本名称从配置文件的宏定义中获取
CREATE TABLE st_order_mt ON CLUSTER gmall_cluster
(id UInt32,sku_id String,total_amount Decimal(16, 2),create_time Datetime
)
ENGINE ReplicatedMergeTree(/clickhouse/tables/{shard}/st_order_mt, {replica})
PARTITION BY toYYYYMMDD(create_time)
PRIMARY KEY id
ORDER BY (id, sku_id); 可以到 hadoop103 和 hadoop104 上查看表是否创建成功 8在 hadoop102 上创建 Distribute 分布式表
CREATE TABLE st_order_mt_all2 ON CLUSTER gmall_cluster
(id UInt32,sku_id String,total_amount Decimal(16, 2),create_time Datetime
)
ENGINE Distributed(gmall_cluster, default, st_order_mt, hiveHash(sku_id));
参数含义
Distributed集群名称库名本地表名分片键
分片键必须是整型数字所以用 hiveHash 函数转换也可以 rand() 9在 hadoop102 上插入测试数据
INSERT INTO st_order_mt_all2 VALUES
(201,sku_001,1000.00,2020-06-01 12:00:00) ,
(202,sku_002,2000.00,2020-06-01 12:00:00),
(203,sku_004,2500.00,2020-06-01 12:00:00),
(204,sku_002,2000.00,2020-06-01 12:00:00),
(205,sku_003,600.00,2020-06-02 12:00:00);
10通过查询分布式表和本地表观察输出结果 注意分布式表管理不同分片时需要知道用户名和密码如果 clickhouse 的用户设置了密码则需要在分片配置中添加上密码。 错误信息DB::Exception: Received from hadoop104:9000. DB::Exception: default: Authentication failed: password is incorrect or there is no user with such name. replicahosthadoop102/hostport9000/portuserdefault/userpasswordabc123/password
/replica
1分布式表 SELECT * FROM st_order_mt_all; 2本地表 select * from st_order_mt; 3观察数据的分布 st_order_mt_all hadoop102: st_order_mt hadoop103: st_order_mt hadoop104: st_order_mt
7.5 项目为了节省资源就使用单节点不用集群
