Clickhouse基本使用方法详细讲解(包括详细步骤及相关操作截图)

超详细ClickHouse学习笔记

一、ClickHouse概述

ClickHouse是一个用于在线分析处理查询（OLAP）的列式数据库管理系统（DBMS）。它由Altinity公司开发，支持线性扩展和高性能的数据压缩。ClickHouse以其卓越的数据处理速度而闻名，特别适合于大规模数据集的实时查询和分析。

OLTP与OLAP对比

• OLTP（联机事务处理系统）：例如MySQL等关系型数据库，擅长处理小数据量的快速查询和频繁的增删改操作。
• OLAP（联机分析处理系统）：针对大量历史数据进行分析，增删改操作较少。OLAP数据库的特点包括：
  1. 绝大多数是读请求。
  2. 数据以大批次更新，而不是单行更新；或者根本没有更新。
  3. 已添加到数据库的数据不能修改。
  4. 对于读取，从数据库中提取大量行，但只提取列的一小部分。
  5. 宽表，即每个表包含大量列。
  6. 查询相对较少（通常每台服务器每秒查询数百次或更少）。
  7. 对于简单查询，允许延迟大约50毫秒。
  8. 列中的数据相对较小：数字和短字符串（例如，每个URL 60个字节）。
  9. 处理单个查询时需要高吞吐量（每台服务器每秒可达数十亿行）。
  10. 事务不是必须的。
  11. 对数据一致性要求低。
  12. 每个查询有一个大表。除了他以外，其他的都很小。
  13. 查询结果明显小于源数据。换句话说，数据经过过滤或聚合，因此结果适合于单个服务器的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家族的主要特点：

1. 数据片段的快速写入：

   • MergeTree家族的表引擎允许以数据片段（block）的形式快速写入数据。这些数据片段后续会在后台被合并，以优化存储和提高查询效率。

2. 后台合并：

   • ClickHouse会在后台自动合并这些数据片段，这个过程称为“merge”。合并的触发条件包括：合并后的文件数量达到阈值、合并后的文件大小超过一定大小时，或者通过OPTIMIZE TABLE语句手动触发。
     
     

3. 数据排序：

   • MergeTree表的数据是按照主键顺序排序存储的。这允许ClickHouse在查询时进行高效的范围扫描，并且可以创建一个小型的稀疏索引来加快数据检索。

4. 数据分区：

   • 支持数据分区（Partitioning），这是一种将数据分割成多个部分的方法，可以基于查询条件来跳过不相关的数据分区，从而提高查询性能。

5. 数据复制：

   • ReplicatedMergeTree系列的表提供了数据副本功能，允许跨多个物理服务器复制数据，以实现高可用性和数据冗余。

6. 数据采样：

   • 可以为表设置数据采样方法，这有助于在插入数据时减少数据的冗余和存储空间的使用。

7. 支持并发写入：

   • MergeTree家族的表引擎支持高并发的写入操作，这对于高吞吐量的数据插入非常有利。

8. 不支持实时更新：

   • 一旦数据写入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;