超详细ClickHouse学习笔记
一、ClickHouse概述
ClickHouse是一个用于在线分析处理查询（OLAP）的列式数据库管理系统（DBMS）。它由Altinity公司开发，支持线性扩展和高性能的数据压缩。ClickHouse以其卓越的数据处理速度而闻名，特别适合于大规模数据集的实时查询和分析。
OLTP与OLAP对比
- OLTP（联机事务处理系统）：例如MySQL等关系型数据库，擅长处理小数据量的快速查询和频繁的增删改操作。
- OLAP（联机分析处理系统）：针对大量历史数据进行分析，增删改操作较少。OLAP数据库的特点包括：
- 绝大多数是读请求。
- 数据以大批次更新，而不是单行更新；或者根本没有更新。
- 已添加到数据库的数据不能修改。
- 对于读取，从数据库中提取大量行，但只提取列的一小部分。
- 宽表，即每个表包含大量列。
- 查询相对较少（通常每台服务器每秒查询数百次或更少）。
- 对于简单查询，允许延迟大约50毫秒。
- 列中的数据相对较小：数字和短字符串（例如，每个URL 60个字节）。
- 处理单个查询时需要高吞吐量（每台服务器每秒可达数十亿行）。
- 事务不是必须的。
- 对数据一致性要求低。
- 每个查询有一个大表。除了他以外，其他的都很小。
- 查询结果明显小于源数据。换句话说，数据经过过滤或聚合，因此结果适合于单个服务器的RAM中。
数据类型
ClickHouse的数据类型丰富多样，支持各种数值、字符串、日期和复合类型等。了解这些数据类型对于高效地使用ClickHouse至关重要。
整数类型
ClickHouse支持多种整数类型，包括有符号和无符号整数。
有符号整数类型
- Int8：1字节，范围为-128到127。
- Int16：2字节，范围为-32768到32767。
- Int32：4字节，范围为-2147483648到2147483647。
- Int64：8字节，范围为-9223372036854775808到9223372036854775807。
- Int128：16字节，范围非常大，适用于特殊需求。
- Int256：32字节，范围更大，同样适用于特殊需求。
无符号整数类型
- UInt8：1字节，范围为0到255。
- UInt16：2字节，范围为0到65535。
- UInt32：4字节，范围为0到4294967295。
- UInt64：8字节，范围为0到18446744073709551615。
- UInt128：16字节，范围更大。
- UInt256：32字节，范围最大。
字符串类型
ClickHouse中的字符串类型用于存储文本数据。
- String：可变长字符串，类似于SQL中的VARCHAR。它允许存储任意长度的字符串，但在查询时可能会因为长度不同而影响性能。
- FixedString(N)：固定长度的字符串，其中N是字符串的字节长度。如果存储的字符串短于N字节，ClickHouse会用空格填充；如果长于N字节，会被截断。FixedString的性能通常优于String，因为它的大小是固定的，易于缓存和处理。
日期和时间类型
ClickHouse提供了多种日期和时间类型，用于存储日期和时间数据。
- Date：无时间日期，存储格式为年-月-日，占用3字节。
- Date32：与Date类似，但使用4字节存储，支持闰秒。
- DateTime：带有时间的日期，存储格式为年-月-日 时：分：秒，占用8字节。
- DateTime64：与DateTime类似，但支持更高的精度，占用12字节。
插入时间示例
-- 创建一个名为date_test的表
CREATE TABLE date_test (
-- 定义一个名为date1的列，数据类型为Date，用于存储无时间的日期，格式为年-月-日
date1 Date,
-- 定义一个名为date2的列，数据类型为Date32，与Date类似，但使用4字节存储，支持闰秒
date2 Date32,
-- 定义一个名为date3的列，数据类型为DateTime，用于存储带有时间的日期，格式为年-月-日 时：分：秒
date3 DateTime,
-- 定义一个名为date4的列，数据类型为DateTime64，与DateTime类似，但支持纳秒级精度
date4 DateTime64
-- 表使用TinyLog引擎，这是一种简单的日志引擎，适用于小规模数据的写入和读取
-- TinyLog引擎不支持并发写入，但适用于单线程环境，且每个写入操作都会生成一个新的数据文件
) ENGINE = TinyLog;
-- 向date_test表中插入一条记录
INSERT INTO date_test VALUES (
-- '2023-11-21'作为date1列的值，格式为'年-月-日'，适合Date类型的要求
'2023-11-21',
-- '2023-11-21'作为date2列的值，同样是日期格式，适用于Date32类型
'2023-11-21',
-- '2023-11-21'作为date3列的值，这里虽然没有指定时间部分，但DateTime类型会将时间默认为0时0分0秒
'2023-11-21',
-- '2023-11-21'作为date4列的值，DateTime64类型支持纳秒级精度，但这里只提供了日期部分，时间部分默认为0时0分0秒0毫秒
'2023-11-21'
);
这个建表语句定义了一个包含四个日期和时间字段的表，每个字段都有其特定的日期和时间类型。插入语句则向这个表中添加了一条记录，每个日期和时间字段都被赋予了相同的日期值。注意，对于包含时间的日期类型，如未指定时间，默认会使用0时0分0秒（对于DateTime64，是0时0分0秒0毫秒）。
插入时间戳示例
insert into bigdata.date_test
values (1729751474903, 1729751474903, 1729751474903, 1729751474903)
--Date和Date32是以天计算时间戳
--DateTime是以秒计算时间戳
--DateTime64是以毫秒计算时间戳
UUID类型
- UUID：用于存储UUID（通用唯一识别码），占用16字节。
可为空类型
ClickHouse支持可为空的任何数据类型，表示为Nullable(T)，其中T是任何ClickHouse支持的数据类型。
- Nullable(Int32)：可以存储整数值或NULL。
--建表语句
create table bigdata.student(
id Nullable(Int32),
name FixedString(12),
age Int16
)ENGINE = TinyLog;
--插入语句
insert into bigdata.student values(null,'bob','20');
数组类型
ClickHouse支持数组类型，用于存储同一类型的多个值。
- Array(T)：T可以是任何数据类型，Array(T)用于存储T类型的多个值。
CREATE TABLE IF NOT EXISTS bigdata.stu3
(
`id` Int32,
`name` String,
`age` Nullable(Int32),
`gender` FixedString(8),
`clazz` String,
`likes` Array(String)
) ENGINE = TinyLog;
insert into bigdata.stu3
values (1001, '老六', 18, '男', '数加32期', array('唱', '跳', 'rap'));
小数类型
ClickHouse支持定点数和小数类型，用于精确的数值计算。
- Decimal(P, S)：有符号的定点数，P是精度（总位数），S是尺度（小数位数）。
- Decimal32(S)：32位的小数类型，S是小数位数。
- Decimal64(S)：64位的小数类型，S是小数位数。
- Decimal128(S)：128位的小数类型，S是小数位数。
总结
ClickHouse的数据类型设计考虑了不同的使用场景，从简单的整数和字符串到复杂的日期时间和小数类型。选择合适的数据类型对于优化查询性能和存储效率至关重要。在实际应用中，应根据数据的特性和查询需求选择合适的数据类型。
三、表操作
建表语句
-- 创建一个名为user3的表
CREATE TABLE user3 (
-- 定义一个名为id的列，数据类型为Int64，这是一种有符号的64位整数类型，适用于存储大范围的整数
id Int64,
-- 定义一个名为name的列，数据类型为FixedString(12)，这是一种固定长度的字符串，长度为12字节
-- 如果存储的字符串短于12字节，ClickHouse会用空格填充；如果长于12字节，会被截断
name FixedString(12),
-- 定义一个名为gender的列，数据类型为Nullable(FixedString(3))，这是一种可以存储NULL值的固定长度字符串
-- 这种类型表示gender列可以存储长度为3字节的字符串或者一个NULL值，适用于存储如'男'或'女'这样的性别标识
gender Nullable(FixedString(3)),
-- 定义一个名为clazz的列，数据类型为String，这是一种可变长字符串，适用于存储长度不一的文本数据
-- String类型的字段会根据实际存储的字符串长度动态分配空间
clazz String
-- 表使用TinyLog引擎，这是一种简单的日志引擎，适用于小规模数据的写入和读取
-- TinyLog引擎不支持并发写入，但适用于单线程环境，且每个写入操作都会生成一个新的数据文件
) ENGINE = TinyLog;
这个建表语句定义了一个包含四个字段的用户表，每个字段都有其特定的数据类型，适用于存储不同类型的数据。表引擎选择了TinyLog，适合于写入次数不频繁且数据量不大的场景。
插入数据
INSERT INTO user3 VALUES (1001, '小刚子', '男', '特训营32期'), (1002, '大老虎', '男', '特训营32期');
四、引擎
ClickHouse支持多种表引擎，包括日志引擎和合并树家族引擎。
日志引擎篇
ClickHouse的日志引擎家族提供了几种轻量级的表引擎，它们特别适合于小规模数据的写入和读取。这些引擎由于其简单性，在某些特定场景下非常有用，例如临时数据存储、测试环境或者审计日志等。下面是对日志引擎的详细说明：
日志引擎概述
日志引擎家族的特点是：
- 简单性：这些引擎的实现简单，易于理解和维护。
- 无索引：数据文件不维护索引，导致查询通常需要全表扫描。
- 高写入性能：在写入小批量数据时，性能较高。
- 低读取性能：由于缺乏索引，读取操作可能较慢，特别是对于大数据量的表。
Log引擎
Log引擎将每个插入的数据块存储为一个文件，这些文件存储在表的数据目录中。- 它不支持并发写入，这意味着在写入数据时，多个查询可能会被阻塞。
- 读取操作会按文件的顺序进行，因此读取操作是线程安全的，可以在多个线程中并行执行。
- 由于没有索引，
SELECT查询通常会扫描整个表，这在数据量大时可能非常低效。
TinyLog引擎
TinyLog引擎是Log引擎的简化版，它将所有列存储在单个文件中，而不是每个插入的数据块一个文件。- 这种设计使得
TinyLog引擎在处理小表时非常高效，因为它减少了打开文件的数量。 - 与
Log引擎一样，TinyLog也不支持索引，因此对于大量数据的读取操作可能不够高效。 - 适用于数据量不大，且写入操作不频繁的场景。
StripeLog引擎
StripeLog引擎将所有列数据存储在一个文件中，每个插入的数据块作为文件中的一个条带（stripe）。- 这种存储方式使得
StripeLog引擎在写入数据时非常高效，因为它减少了文件系统的I/O操作。 - 读取操作可以并行执行，因为
StripeLog引擎使用标记文件来加速数据的读取。 - 但是，
StripeLog引擎不支持ALTER UPDATE和ALTER DELETE操作，这意味着一旦数据写入，就无法修改。
使用场景
日志引擎家族的表引擎适合以下场景：
- 临时数据存储：用于存储临时数据，例如会话信息或临时计算结果。
- 测试和开发：在测试环境中使用，因为它们简单且易于设置。
- 审计日志：存储审计日志信息，这些信息通常按时间顺序写入，且查询操作不频繁。
注意事项
- 数据量：日志引擎家族的表引擎适合数据量较小的表。对于大数据量的表，它们的性能可能不佳。
- 查询性能：由于缺乏索引，这些引擎的查询性能通常较低。如果需要频繁执行复杂查询，可能需要考虑使用
MergeTree家族的表引擎。 - 数据安全性：在生产环境中使用时，需要注意数据的安全性和备份，因为这些引擎不支持事务。
总的来说，ClickHouse的日志引擎家族为特定场景提供了简单而有效的解决方案。在选择表引擎时，应根据数据的特点和使用场景来决定是否使用这些轻量级的引擎。
MergeTree家族的主要特点：
-
数据片段的快速写入：
MergeTree家族的表引擎允许以数据片段（block）的形式快速写入数据。这些数据片段后续会在后台被合并，以优化存储和提高查询效率。
-
后台合并：
- ClickHouse会在后台自动合并这些数据片段，这个过程称为“merge”。合并的触发条件包括：合并后的文件数量达到阈值、合并后的文件大小超过一定大小时，或者通过
OPTIMIZE TABLE语句手动触发。
- ClickHouse会在后台自动合并这些数据片段，这个过程称为“merge”。合并的触发条件包括：合并后的文件数量达到阈值、合并后的文件大小超过一定大小时，或者通过
-
数据排序：
MergeTree表的数据是按照主键顺序排序存储的。这允许ClickHouse在查询时进行高效的范围扫描，并且可以创建一个小型的稀疏索引来加快数据检索。
-
数据分区：
- 支持数据分区（Partitioning），这是一种将数据分割成多个部分的方法，可以基于查询条件来跳过不相关的数据分区，从而提高查询性能。
-
数据复制：
ReplicatedMergeTree系列的表提供了数据副本功能，允许跨多个物理服务器复制数据，以实现高可用性和数据冗余。
-
数据采样：
- 可以为表设置数据采样方法，这有助于在插入数据时减少数据的冗余和存储空间的使用。
-
支持并发写入：
MergeTree家族的表引擎支持高并发的写入操作，这对于高吞吐量的数据插入非常有利。
-
不支持实时更新：
- 一旦数据写入
MergeTree表，就无法对其进行修改。如果需要更新或删除数据，可以使用ALTER TABLE ... UPDATE或ALTER TABLE ... DELETE语句，这些操作会生成新的数据版本。
- 一旦数据写入
create table bigdata.goods_orders
(
id String,
uname String,
goods_name String,
price Int64,
date Date32
) ENGINE = MergeTree() order by date PARTITION BY date;
insert into bigdata.goods_orders
values ('1001', '光头强', 'oppo手机', 7000, '2024-10-24'),
('1002', '熊大', '机械革命电脑', 10000, '2024-10-22'),
('1003', '熊二', 'iphone14', 5000, '2024-10-24'),
('1004', '翠花', 'AI吸尘器', 17000, '2024-10-22');
insert into bigdata.goods_orders
values ('1001', '老鳄', 'vivo x200 pro', 5299, '2024-10-24'),
('1002', '铁掌', '科睿显示屏', 10000, '2024-10-22'),
('1003', '拖拖', 'iphone16 pro max', 5000, '2024-10-24'),
('1004', '吉吉', '华为三折叠', 17000, '2024-10-22');
MergeTree家族的主要成员：
- MergeTree：基本的
MergeTree引擎，没有分区和复制功能。 - ReplicatedMergeTree：支持数据复制的
MergeTree，适用于多副本部署。 - CollapsingMergeTree：支持数据折叠，可以用于处理事件累积量（如点击次数）的场景。
- SummingMergeTree：在合并过程中自动对数值列进行求和。
- AggregatingMergeTree：在合并过程中自动对聚合列进行聚合操作。
- VersionedCollapsingMergeTree：支持版本控制的
CollapsingMergeTree，可以处理带有版本号的数据。 - GraphiteMergeTree：用于从Graphite服务导入时间序列数据。
使用MergeTree家族引擎的考虑因素：
- 写入性能：
MergeTree家族的表引擎非常适合高吞吐量的写入场景。 - 查询性能：由于数据是排序存储的，对于范围查询和聚合查询非常高效。
- 存储优化：后台合并过程有助于优化存储空间的使用，但可能会对I/O和CPU资源产生一定影响。
总的来说，MergeTree家族引擎是ClickHouse中处理大规模数据集的核心，它们通过高效的数据写入和后台合并机制，为OLAP查询提供了强大的支持。
五、常用函数
算术函数
plus(a, b)：计算数值的总和。minus(a, b)：计算数值之间的差。multiply(a, b)：计算数值的乘积。divide(a, b)：计算数值的商。intDiv(a, b)：计算数值的商，向下舍入取整。
比较函数
=,==：等于。!=,<>：不等于。<：少。>：大于。<=：小于等于。>=：大于等于。
数据类型转换
在进行数据类型转换时，需要注意可能的数据丢失，尤其是在将大的数据类型转换为小的数据类型时。
六、实践案例
案例1：创建表并插入数据
CREATE TABLE example_table (
id Int32,
name String,
birth_date Date
) ENGINE = MergeTree() ORDER BY id;
INSERT INTO example_table VALUES (1, 'John Doe', '1990-01-01'), (2, 'Jane Doe', '1992-05-15');
案例2：使用函数进行数据操作
SELECT
id,
name,
toYear(birth_date) AS birth_year,
plus(id, 100) AS new_id
FROM example_table;
案例3：数据类型转换
SELECT
toDecimal32(12345678901234567890, 2) AS decimal_value;