ClickHouse建表语句分析

MergeTree引擎

Clickhouse 中最强大的表引擎当属 MergeTree （合并树）引擎及该系列（*MergeTree）中的其他引擎。

MergeTree 系列的引擎被设计用于插入极大量的数据到一张表当中。数据可以以数据片段的形式一个接着一个的快速写入，数据片段在后台按照一定的规则进行合并。相比在插入时不断修改（重写）已存储的数据，这种策略会高效很多。

主要特点:

• 存储的数据按主键排序。

  这使得您能够创建一个小型的稀疏索引来加快数据检索。

• 如果指定了分区键的话，可以使用分区。

  在相同数据集和相同结果集的情况下 ClickHouse 中某些带分区的操作会比普通操作更快。查询中指定了分区键时 ClickHouse 会自动截取分区数据。这也有效增加了查询性能。

• 支持数据副本。

  ReplicatedMergeTree 系列的表提供了数据副本功能。

• 支持数据采样。

  需要的话，您可以给表设置一个采样方法。

!!! "注意"：Merge引擎并不属于 *MergeTree 系列。

建表

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
        name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
        name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
        ...
        INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
        INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2
) ENGINE = MergeTree()
ORDER BY expr
[PARTITION BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[TTL expr [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'], ...]
[SETTINGS name=value, ...]

子句说明

• ENGINE - 引擎名和参数。 ENGINE = MergeTree()。 MergeTree 引擎没有参数。

• ORDER BY — 排序键。

  可以是一组列的元组或任意的表达式。 例如: ORDER BY (CounterID, EventDate) 。

  如果没有使用 PRIMARY KEY 显式指定的主键，ClickHouse 会使用排序键作为主键。

  如果不需要排序，可以使用 ORDER BY tuple()。

• PARTITION BY — 分区键，可选项。

  要按月分区，可以使用表达式 toYYYYMM(date_column) ，这里的 date_column 是一个 Date 类型的列。分区名的格式会是 "YYYYMM" 。

• PRIMARY KEY - 如果要选择与排序键不同的主键​，在这里指定，可选项。

  默认情况下主键跟排序键（由 ORDER BY 子句指定）相同。 因此，大部分情况下不需要再专门指定一个 PRIMARY KEY 子句。

• SAMPLE BY - 用于抽样的表达式，可选项。

  如果要用抽样表达式，主键中必须包含这个表达式。例如： SAMPLE BY intHash32(UserID) ORDER BY (CounterID, EventDate, intHash32(UserID)) 。

• TTL - 指定行存储的持续时间并定义数据片段在硬盘和卷上的移动逻辑的规则列表，可选项。

  表达式中必须存在至少一个 Date 或 DateTime 类型的列，比如：

  TTL date + INTERVAl 1 DAY

  规则的类型 DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'指定了当满足条件（到达指定时间）时所要执行的动作：移除过期的行，还是将数据片段（如果数据片段中的所有行都满足表达式的话）移动到指定的磁盘（TO DISK 'xxx') 或 卷（TO VOLUME 'xxx'）。默认的规则是移除（DELETE）。可以在列表中指定多个规则，但最多只能有一个DELETE的规则。

• SETTINGS — 控制 MergeTree 行为的额外参数，可选项：

  • index_granularity — 索引粒度。索引中相邻的『标记』间的数据行数。默认值8192 。
  • index_granularity_bytes — 索引粒度，以字节为单位，默认值: 10Mb。如果想要仅按数据行数限制索引粒度, 请设置为0(不建议)。
  • min_index_granularity_bytes - 允许的最小数据粒度，默认值：1024b。该选项用于防止误操作，添加了一个非常低索引粒度的表。
  • enable_mixed_granularity_parts — 是否启用通过 index_granularity_bytes 控制索引粒度的大小。在19.11版本之前, 只有 index_granularity 配置能够用于限制索引粒度的大小。当从具有很大的行（几十上百兆字节）的表中查询数据时候，index_granularity_bytes 配置能够提升ClickHouse的性能。如果您的表里有很大的行，可以开启这项配置来提升SELECT 查询的性能。
  • use_minimalistic_part_header_in_zookeeper — ZooKeeper中数据片段存储方式 。如果use_minimalistic_part_header_in_zookeeper=1 ，ZooKeeper 会存储更少的数据。
  • min_merge_bytes_to_use_direct_io — 使用直接 I/O 来操作磁盘的合并操作时要求的最小数据量。合并数据片段时，ClickHouse 会计算要被合并的所有数据的总存储空间。如果大小超过了 min_merge_bytes_to_use_direct_io 设置的字节数，则 ClickHouse 将使用直接 I/O 接口（O_DIRECT 选项）对磁盘读写。如果设置 min_merge_bytes_to_use_direct_io = 0 ，则会禁用直接 I/O。默认值：10 * 1024 * 1024 * 1024 字节。
  • merge_with_ttl_timeout — TTL合并频率的最小间隔时间，单位：秒。默认值: 86400 (1 天)。
  • write_final_mark — 是否启用在数据片段尾部写入最终索引标记。默认值: 1（不要关闭）。
  • merge_max_block_size — 在块中进行合并操作时的最大行数限制。默认值：8192
  • storage_policy — 存储策略。
  • min_bytes_for_wide_part,min_rows_for_wide_part 在数据片段中可以使用Wide格式进行存储的最小字节数/行数。您可以不设置、只设置一个，或全都设置。
  • max_parts_in_total - 所有分区中最大块的数量(意义不明)
  • max_compress_block_size - 在数据压缩写入表前，未压缩数据块的最大大小。您可以在全局设置中设置该值。建表时指定该值会覆盖全局设置。
  • min_compress_block_size - 在数据压缩写入表前，未压缩数据块的最小大小。您可以在全局设置中设置该值。建表时指定该值会覆盖全局设置。
  • max_partitions_to_read - 一次查询中可访问的分区最大数。您可以在全局设置中设置该值。

示例配置

ENGINE MergeTree() PARTITION BY toYYYYMM(EventDate) ORDER BY (CounterID, EventDate, intHash32(UserID)) SAMPLE BY intHash32(UserID) SETTINGS index_granularity=8192

在这个例子中，我们设置了按月进行分区。

同时我们设置了一个按用户 ID 哈希的抽样表达式。这使得您可以对该表中每个 CounterID 和 EventDate 的数据伪随机分布。如果您在查询时指定了​​SAMPLE​子句， ClickHouse会返回对于用户子集的一个均匀的伪随机数据采样。

index_granularity 可省略因为 8192 是默认设置 。