Vgbhfive's Blog

ClickHouse-基础

Posted on Edited on

简介

大家都说 MySQL 和 ClickHouse 很像，如果你不了解 ClickHouse，那我觉得你是对的，但若是你了解，那你更需要往下看看。

数据定义

数据类型

作为一款分析性数据库，ClickHouse 提供了很多数据类型，这里面主要可以分为基础类型、复合类型和特殊类型。

基础类型

基础类型主要包含有数值、字符串和时间三种类型，没有 Boolean 类型，但是可以使用整形的 0 和 1 来代替。

  1. 数值类型
    数值类型分为整数、浮点数和定点数三类。

    • Int： ClickHouse 使用 Int8、 Int16、 Int32、 Int64 指代四种大小的 Int 类型，其末尾的数字正好表明占用字节的大小。
    • Float：与整数类似，ClickHouse 直接使用 Float32、 Float64 代表单精度浮点数及双精度浮点数。
    • Decimal：如果需要更高精度的数值运算，则需要使用定点数，ClickHouse 提供了 Decimal32、 Decimal64、 Decimal128 三种精度的定点数。可以通过两种形式声明定点：简写方式有 Decimal32(S)、 Decimal64(S)、 Decimal128(S) 三种；原生方式为 Decimal(P, S)，其中 P 代笔精度，决定总位数（整数部份+小数部分），S 代表规模，决定小数位数。

  2. 字符串类型
    字符串类型可以细分为 String、 FixedString、 UUID 三类。

    • String：字符串由 String 定义，长度不限。
    • FixedString：定长字符串，在使用时需表明字符串的长度。
    • UUID：UUID 是一种数据库常见的主键类型，在 ClickHouse 中被当作了一种数据类型，UUID 共有 32 位，格式为 8-4-4-4-12，如果一个 UUID 类型的字段在写入时没有被赋值则会被依照格式使用 0 填充。

  3. 时间类型
    时间类型分为 DataTime、 DateTime64、 Date 三类，ClickHouse 目前没有时间戳类型，时间类型最高精度是秒，也就是说如果需要处理毫秒、微秒等大于秒分辨率的时间，则只能借助 UInt 类型实现。

    • DateTime：包含时、分、秒信息，精确到秒，支持使用字符串形式写入。
    • DateTime64：可以记录亚秒，在 DateTime 之上增加了精度的设置。
    • Date：不包含具体的时间信息，只精确到天，同样也支持字符串形式写入。
复合类型

除了基础类型之外，ClickHouse 还提供了数组、元组、枚举和嵌套四类复合类型。

  • Array：数组有两种定义方式，常规方式为 array(T)，或者简写方式 [T]。通过上述发现在查询时并不需要主动声明数组的元素类型，因为 ClickHouse 的数组拥有类型推断的能力，而推断依据则是以最小存储代价为原则，即使用最小可表达的数据类型。
  • Tuple：元组类型由 1~n 个元素组成，每个元素之间允许设置不同的数据类型，且彼此之间不要求兼容。元组同样支持类型推断，其推断仍然以最小的存储代价为原则。与数组类似元组也有两种方式定义，常规方式为 tuple(T)，或者简写方式 (T)。
  • Enum：ClickHouse 支持枚举类型，提供了 Enum8 和 Enum16 两种枚举类型，他们除了取值范围不同之外，别无二致。枚举固定使用 (String:Int) Key/Value 键值对的形式定义数据，所以 Enum8 和 Enum16 分别对应 (String:Int8) 和 (String:Int16)。
    Key/Value 是不允许重复的，要保证唯一性。另外也不允许为 Null，但是 Key 允许是空字符串。
  • Nested：嵌套类型，顾名思义就是一种嵌套表结构。一张表结构，可以定义任意多个嵌套类型字段，但每个字段的嵌套层级只支持一级，即嵌套表内不能继续使用嵌套类型。对于简单场景的层级关系或关联关系，使用嵌套类型也是不错的选择。
特殊类型

ClickHouse 还有一类不同的数据类型，那就是特殊类型。

  • Nullable：准确说这并不是一种独立的数据类型，更像是一种辅助的修饰符，需要与基础数据一起搭配使用，表示某个基础数据可以是 Null 值。
  • Domain：域名类型分为 IPv4 和 IPv6 两类，本质上就是对整形和字符串的进一步封装。但是在使用时不可当作字符串使用，因为其并不支持隐式的自动类型转换，如果需要返回 IP 的字符串形式，需要显式调用 IPv4NumToString 或 IPv6NumToString 函数进行转换。

数据表

之前说的都是数据类型，接下来就是 DDL 操作及定义数据的方法。

数据库
1
CREATE DATABASE IF NOT EXISTS db_name [ENGINE = engine]

数据库目前一共支持五种默认引擎：

  • Ordinary：默认引擎，大多数情况下使用的引擎，使用时无需声明。
  • Dictionary：字典引擎，此类数据库会为所有数据字典创建他们的数据表。
  • Memory：内存引擎，用于存放临时数据。
  • Lazy： 日志引擎，只能使用 Log 系列的表引擎。
  • MySQL：会自动拉取远端 MySQL 中的数据，并为他们创建 MySQL 表引擎的数据表。
数据表
1
2
3
4
CREATE TABLE [IF NOT EXISTS] [db_name.]table_name (
    name1 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
    name2 [type] [DEFAULT|MATERIALIZED|ALIAS expr]
) ENGINE = engine

上面是常见的创建表方式，下面则是通过复制其他表结构来创建表

1
CREATE TABLE [IF NOT EXISTS] [db_name1.]table_name1 AS [db_name2.]table_name2 [ENGINE = engine]
默认值表达式

表字段支持三种默认值表达式的定义方法，分别是 DEFAULT、 MATERIALIZED、 ALIAS，无论使用哪种形式，表字段一旦被定义了默认值，便不再强制要求定义数据类型，因为 ClickHouse 会根据默认值进行类型推断。若是对表字段定义了数据类型和默认值表达式，则以明确定义的数据类型为主。

默认值表达式的三种定义方法之间也存在着不同之处：

  • 在数据写入时，只有 DEFAULT 类型的字段可以出现在 INSERT 语句中，而 MATERIALIZED 和 ALIAS 不能被显式赋值，只能通过计算取值。
  • 在数据查询时，只有 DEFAULT 类型的字段可以出现在 SELECT 语句中，而 MATERIALIZED 和 ALIAS 不能出现在返回结果集中。
  • 在数据存储时，只有 DEFAULT 和 MATERIALIZED 类型的字段才支持持久化，而 ALIAS 类型的字段不能被持久化，它的取值依赖于计算。
临时表
1
2
3
4
CREATE TEMPORARY TABLE [IF NOT EXISTS] [db_name.]table_name (
    name1 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
    name2 [type] [DEFAULT|MATERIALIZED|ALIAS expr]
)

相比普通表而言，临时表由以下两点特殊之处：

  • 临时表的生命周期与会话绑定，所以他仅支持 Memory 表引擎，如果会话结束，数据表就会被销毁。
  • 临时表不属于任何数据库，所以在建表语句中，没有数据库参数和表引擎参数。

临时表的优先级大于普通表，当两张表名称相同时，会优先读取临时表的数据。

分区表

数据分区（partition）和数据分片（shard）是完全不同的两个概念，数据分区是针对本地数据而言的，是数据的一种纵向切分，而数据分片是数据的横向切分。但是目前只有合并树（MergeTree）家族系列的引擎支持数据分区。

1
2
3
4
5
6
CREATE TABLE [IF NOT EXISTS] [db_name.]table_name (
    ID String,
    Time Date
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(Time)
ORDER BY ID
视图

ClickHouse 拥有普通和物化两种视图，其中物化视图拥有独立的存储，而普通视图只是一层简单的查询代理。
普通视图不会存储任何数据，只是一层简单的 SELECT 查询映射，起着简化查询、明晰语义的作用，对查询性能不会有影响。而物化视图支持表引擎，数据保存形式由他的表引擎决定，物化视图创建好之后，如果源表被写入数据，那么物化视图也会同步更新。
POPULATE 修饰符决定了物化视图的初始策略，若使用 POPULATE 则在创建视图时会将源表的数据一并导入，反之不使用 POPULATE 则物化视图初始是没有数据的，只会在同步之后写入源表的数据。另外物化视图不支持删除数据，源表中的数据被删除，物化视图中的数据不会被删除。

1
2
3
4
# 创建普通视图的语法
CREATE VIEW [IF NOT EXISTS] [db_name.]view_name AS SELECT ...
# 创建物化视图的语法
CREATE [MATERIALIZED] VIEW [IF NOT EXISTS] [db.]table_name [TO[db.]name] [ENGINE = engine] [POPULATE] AS SELECT ...

数据表操作

基本操作

  1. 追加新字段

    1
    ALTER TABLE tb_name ADD COLUMN [IF NOT EXISTS] name [type] [default_expr] [AFTER name_after]

  2. 修改数据类型

    1
    ALTER TABLE tb_name MODIFY COLUMN [IF EXISTS] name [type] [default_expr]

  3. 修改备注

    1
    ALTER TABLE tb_name COMMENT COLUMN [IF EXISTS] name 'some comment'

  4. 删除已有字段

    1
    ALTER TABLE tb_name DROP COLUMN [IF EXISTS] name

  5. 移动数据表

    1
    RENAME TABLE [db_name1.]tb_name1 TO [db_name2.]tb_name2

    其原理与 Linux 系统中的 mv 命令异曲同工。

  6. 清空数据表

    1
    TRUNCATE TABLE [IF EXISTS] [db_name.]tb_name

  7. 查询分区信息
    ClickHouse 内置了许多 system 系统表，用于查询自身的状态信息，其中的 parts 系统表专门用于查询数据表的分区信息。

    1
    SELECT partition_id, name, table, database FROM system.parts WHERE table = 'partition_v2'

  8. 删除指定分区

    1
    ALTER TABLE tb_name DROP PARTITION partition_expr

  9. 复制分区数据
    ClickHouse 支持将 A 表的分区数据复制到 B 区，可以用于快速数据写入、多表间数据同步和备份等场景。当然此项特性支持是有前提的：

  • 两张表需要拥有相同的分区键。
  • 他们的表结构完全相同。
    1
    ALTER TABLE B REPLACE PARTITION partition_expr FROM A

  1. 重置分区数据
    如果数据表的某一列数据有误，需要将其重置为初始值，如果声明了默认值表达式，则以表达式为准，否则以数据类型的默认值为准。

    1
    ALTER TABLE tb_name CLEAR COLUMN column_name IN PARTITION partition_expr

  2. 卸载装载分区
    表分区可以通过 DETACH 语句卸载，分区被卸载后他的物理数据会被转移到当前数据表目录的 detached 子目录下，而装载分区则是反向操作，可以将 detached 子目录下的某个分区重新装载回去。

    1
    2
    3
    4
    # 卸载分区语法
    ALTER TABLE tb_name DETACH PARTITION partition_expr
    # 装载分区语法
    ALTER TABLE tb_name ATTACH PARTITION partition_expr

  3. 备份还原分区
    分区在被备份之后，会被统一保存到 ClickHouse 根路径 /shadow/N 子目录下，其中 N 是一个自增长的整数，它的含义是备份的次数，具体次数由 shadow 子目录下的 increment.txt 文件记录。而分区备份实质上是对原始目录文件进行硬链接，所以不会有额外的存储空间。整个备份目录会一直向上追溯到 data 根路径的整个链路。

    1
    /data/[database]/[table]/[partition_folder]

    FETCH 的工作原理与 ReplicatedMergeTree 同步数据的原理类似，FETCH 通过指定的 zk_path 找到 ReplicatedMergeTree 的所有副本实例，然后从中选择一个最合适的副本，并下载相应的分区数据。

    1
    2
    3
    4
    # 备份分区语法
    ALTER TABLE tb_name FREEZE PARTITION partition_expr
    # 还原分区语法
    ALTER TABLE tb_name FETCH PARTITION partition_expr FROM zk_path

    FETCH 只支持 ReplicatedMergeTree 系列的表引擎。

分布式的 DDL 操作

ClickHouse 支持集群模式，一个集群拥有一个到多个节点。CREATE、 ALTER、 DROP、 RENAME、 TRUNCATE 这些 DDL 语句，都支持分布式执行。这意味着在集群中任意一个节点上执行 DDL 语句，那么集群中的每个节点都会以相同的顺序执行相同的语句。
将一条普通的 DDL 语句转化为分布式执行十分简单，只需要加上 ON CLUSTER cluster_name 声明即可。

1
2
3
4
5
6
CREATE TABLE partition_v3 ON CLUSTER ch_cluster(
    ID String,
    Time Date
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(Time)
ORDRE BY ID
数据操作

  1. 写入
    INSERT 语句支持三种语法范式，三种范式各有不同，可以根据写入的需求灵活变化。

    • 使用 VALUES 格式的常规用法： 
      1
      INSERT INTO [db.]tb_name [(c1, c2, c3)] VALUES (v11, v12, v13), (v21, v22, v23)
    • 使用指定格式语法： 
      1
      INSERT INTO [db.]tb_name [(c1, c2, c3)] FORMAT format_name data_set
    • 使用 SELECT 子句形式语法： 
      1
      INSERT INTO [db.]tb_name [(c1, c2, c3)] SELECT ...

  2. 修改和删除
    ClickHouse 提供 DELETE 和 UPDATE 的能力，这类操作被称为 Mutation 查询，可以看作是 ALTER 语句的变种，虽然能实现同样的功能，但是与通常意义上的 DELETE 和 UPDATE 不一样：

    • Mutation 语句其实是很重的操作，更适用于批量数据的修改和删除。
    • 不支持事务，一旦语句被提交，就会立刻对数据产生影响且无法回滚。
    • Mutation 语句的执行是一个异步的后台过程，语句提交之后会立即返回。但是立即返回并不代表具体逻辑已经执行完毕，具体执行进度需要通过 system.mutations 系统表查询。

DELETE 和 UPDATE 语句语法如下：

1
2
ALTER TABLE [db_name.]tb_name DELETE WHERE filter_expr
ALTER TABLE [db_name.]tb_name UPDATE column1 = expr1, ... WHERE filter_expr

数据查询

WITH 子句

ClickHouse 支持 CTE （Common Table Expression，公共表表达式）以增强查询语句的表达。

  1. 定义变量
    可以定义变量，这些变量能够在后续的查询子句中被直接访问。

  2. 调用函数
    可以访问 SELECT 子句中的列字段，并调用函数做进一步的加工处理。

  3. 定义子查询
    可以定义子查询。
    在 WITH 中使用子查询时有一些需要特别注意，该查询语句只能返回同一行数据，如果结果集的数据大于一行则会抛出异常。

  4. 在子查询中重复使用 WITH
    在子查询中可以嵌套使用 WITH 子句。

FROM 子句

FROM 子句表示从何处读取数据。

  1. 从数据表中读取数据。

  2. 从子查询中取数。

  3. 从表函数中取数。

FROM 关键字可以省略，此时会从虚拟表中取数。在 ClickHouse 中，并没有数据库中常见的 DUAL 虚拟表，取而代之的就是 system.one。

SAMPLE 子句

SAMPLE 子句能够实现数据采样的功能，使查询仅返回采样数据而不是全部数据，从而减少查询负载。
SAMPLE 子句的采样设计是一种幂等设计，也就是说在数据不发生变化的情况下，使用相同的采样规则总是返回相同的数据。
SAMPLE 子句只能用于 MergeTree 系列引擎的数据表，并且要求在 CREATE TABLE 时声明 SAMPLE BY 抽样表达式。

  1. SAMPLE factor
    SAMPLE factor 表示按因子系数采样，其中 factor 表示采样因子，它的取值支持 0~1 之间的小数。如果 factor 设置为 0 或者 1，则效果等同于不进行数据采样。

  2. SAMPLE rows
    SAMPLE rows 表示按样本数量采样，其中 rows 表示至少采样多少行数据，它的取值必须是大于 1 的整数。如果 rows 的取值大于表内数据的总行数，则效果等于 rows=1（即不使用采样）。

  3. SAMPLE factor OFFSET n
    SAMPLE factor OFFSET n 表示按因子系数和偏移量采样，其中 factor 表示采样因子，n 表示偏移多少数据后才开始采样，他们两个的取值都是 0~1 之间的小数。

ARRAY JOIN 子句

ARRAY JOIN 子句允许在数据表内部，与数组或嵌套类型的字段进行 JOIN 操作，从而将一行数据展开为多行。
在一条 SELECT 语句中，只能存在一个 ARRAY JOIN（使用子查询除外）。目前仅支持 INNER 和 LEFT 两种策略。

  1. INNER ARRAY JOIN
    ARRAY JOIN 在默认情况下使用的是 INNER JOIN 策略。

    1
    2
    3
    SELECT title, value FROM query_v1 ARRAY JOIN value
    # food 1
    # food 2

  2. LEFT ARRAY JOIN
    ARRAY JOIN 子句支持 LEFT 连接策略。

    1
    2
    3
    SELECT title, value, v FROM query_v1 LEFT ARRAY JOIN value AS v
    # food [1, 2, 3] 1
    # food [1, 2, 3] 2

JOIN 子句

JOIN 子句可以对左右两张表的数据进行连接，这是最常用的查询子句之一。它的语法包含连接精度和连接类型两部分。连接精度可以分为 ALL、 ANY 和 ASOF 三种，而连接类型也可以分为外连接（外集合）、内连接（交集）和交叉连接（并集）三种。
在进行多表连接时，会转化为两两连接的形式。

注意事项
  • 优化 JOIN 性能，首先应遵循左大右小的原则。
  • JOIN 查询目前没有缓存的支持。
  • 如果是在大量维度属性补全的查询场景中，建议使用字典代替 JOIN 查询。
  • 连接查询的空值是由默认值填充的，连接查询的空值策略是通过 join_use_nulls 参数指定的，默认是 0。

WHERE 和 PREWHERE 子句

WHERE 子句基于条件表达式来实现数据过滤。另外还提供了全新的 PREWHERE 子句，PREWHERE 目前只能作用于 MergeTree 系列的表引擎，可以看作是 WHERE 的一种优化，其作用与 WHERE 相同，均用来过滤数据，不过 PREWHERE 只会读取指定的列字段数据，用于数据过滤的条件判断，待数据过滤之后再补齐 SELECT 声明的列字段以补全其余属性。

GROUP BY 子句

GROUP BY 又称聚合查询，在 GROUP BY 后声明的表达式，通常被称为聚合键或者 Key，数据会按照聚合键进行聚合。

  1. WITH ROLLUP
    ROLLUP 能够按照聚合键从右向左上卷数据，基于聚合函数依次生成分组小计和总计。

  2. WITH CUBE
    CUBE 会像立方体模型一样，基于聚合键之间所有的组合生成小计信息。如果聚合键的个数为 n，则最终小计组合的个数为 2^n。

  3. WITH TOTALS
    使用 TOTALS 修饰符后，会基于聚合函数对所有数据进行总计。

HAVING 子句

HAVING 子句需要与 GROUP BY 子句同时出现，不能单独使用。它能够在聚合计算之后实现二次过滤数据。
执行优先级： WHERE > GROUP BY > HAVING

ORDER BY 子句

ORDER BY 子句通过声明排序键来指定查询数据返回时的顺序。可以使用多个排序键，每个排序键后需紧跟 ASC 或者 DESC来确定排列顺序，默认 ASC。
对于 NULL 值的排序，目前拥有 NULLS LAST 和 NULLS FIRST 两种排序方式，含义跟其修饰符有关。另外就是 NaN，总是紧跟在 NULL 身边。

LIMIT BY 子句

LIMIT BY 子句和常见的 LIMIT 所有不同，它运行于 ORDER BY 之后和 LIMIT 之前，能够按照指定分组，最多返回前 n 行数据（如果数据少于 n 行，则按照实际数量返回），常用于 TOP N 的查询场景。另外也支持跳过 OFFSET 偏移量获取数据。

1
LIMIT n BY express

LIMIT 子句

LIMIT 子句用于返回指定的前 n 行数据，常用于分页场景。

1
2
3
LIMIT n
LIMIT n OFFSET m
LIMIT m, n

SELECT 子句

SELECT 子句决定了一次查询语句最终返回哪些字段或者表达式。

DISTINCT 子句

DISTINCT 子句能够去除重复数据，使用场景广泛。

UNION ALL 子句

UNIUN ALL 子句能够联合左右两边的两组子查询，将结果一并返回。在一次查询中可以声明多个 UNION ALL 以便联合多组查询，但 UNION ALL 不能直接使用其他子句，这些子句只能在其联合的子查询中使用。

1
2
3
select a1, b1 from tabl1
UNION ALL
select a2, b2 from table2

上述查询中，UNION ALL 两侧的子查询中能够得到节点信息，首先两侧列字段数量必须相同，其次列字段的数据类型必须相同或兼容，最后列字段的名字可以不同但会以左侧的子查询为准。

SQL 执行计划

ClickHouse 目前并没有直接提供 EXPLAIN 查询，但是借助后台的服务日志，能变相实现该功能。

  • 通过将 ClickHouse 服务日志设置到 DEBUG 或者 TRACE 日志级别，可以变相实现 EXPLAIN 查询，以分析 SQL 的执行日志。
  • 需要真正执行 SQL 查询，CH 才能打印计划日志，所以如果表的数据量很大，最好借助 LIMIT 子句以减少查询返回的数据量。
  • 在日志中，分区过滤信息部分 Selected xxx parts by date,，其中 by date 部分是固定的，无论我们的分区键是什么字段，这里都不会变。这是由于在早期版本中，MergeTree 分区键只支持日期字段。
  • 不要使用 select * 全字段查询。
  • 尽可能利用各种索引（分区索引、一级索引、二级索引），这样可以避免全表扫描。

实用工具

clickhouse-local

clickhouse-local 可以独立运行大部分 SQL 查询，不需要依靠任何 ClickHouse 的服务端程序，可以理解成是 ClickHouse 服务的单机版微内核，是一个轻量级的应用程序。
clickhouse-local 只能够使用 File 表引擎，他的数据与同机运行的 ClickHouse 服务也是完全隔离的，相互之间并不能访问。同时是非交互式运行的，每次执行都需要指定数据来源。

核心参数如下：

  • -S/--structure：表结构的简写方式。
  • -N/--table：表名称，默认值是 table。
  • -if/--input-format：输入数据的格式，默认值是 TSV。
  • -f/--file：输入数据的地址，默认值是 stdin 标准输入。
  • -q/--query：待执行的 SQL 语句，多条语句之间以分号间隔。

clickhouse-benchmark

clickhouse-benchmark 是基准测试的小工具，可以自动运行 SQL 查询，并生成相应的运行指标报告。

核心参数如下：

  • -i/--iterations：SQL 查询执行的次数，默认值为 0。
  • -c/--concurrency：同时执行查询的并发数，默认值是 1。
  • -r/--randomize：在执行多条 SQL 语句的时候，按照随机顺序执行。
  • -h/--host：服务端地址，默认值是 localhost。
  • --confidence：设置对比测试中置信区间的范围，默认值是 5(99.5%)。取值范围有 0(80%), 1(90%), 2(95%), 3(98%), 4(99%) 和 5(99.5%)。

clickhouse-mysql

clickhouse-mysql 用于将 MySQL 的数据或者 CSV 文件同步到 ClickHouse的工具。

其他信息参考另一篇博客，有更详细的说明

引用

个人备注

此博客内容均为作者学习所做笔记，侵删！
若转作其他用途，请注明来源！