clickhouse创建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,
    ...
    PROJECTION projection_name_1 (SELECT <COLUMN LIST EXPR> [GROUP BY] [ORDER BY]),
    PROJECTION projection_name_2 (SELECT <COLUMN LIST EXPR> [GROUP BY] [ORDER BY])
) ENGINE = MergeTree()
ORDER BY expr
[PARTITION BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[TTL expr
    [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx' [, ...] ]
    [WHERE conditions]
    [GROUP BY key_expr [SET v1 = aggr_func(v1) [, v2 = aggr_func(v2) ...]] ] ]
[SETTINGS name=value, ...]

 

• ENGINE— 引擎的名称和参数。ENGINE = MergeTree(). MergeTree引擎没有参数。

• ORDER BY— 排序键。

  列名或任意表达式的元组。示例：ORDER BY (CounterID, EventDate)。

  如果子句没有明确定义主键，ClickHouse 使用排序键作为主键PRIMARY KEY。

  ORDER BY tuple()如果不需要排序，请使用语法。请参阅选择主键。

• PARTITION BY—分区键。可选的。在大多数情况下，您不需要分区键，而在大多数其他情况下，您不需要比几个月更细的分区键。分区不会加速查询（与 ORDER BY 表达式相反）。你永远不应该使用太细化的分区。不要按客户端标识符或名称对数据进行分区（而是将客户端标识符或名称作为 ORDER 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'|GROUP BY指定在满足表达式（达到当前时间）时对部件执行的操作：删除过期行，将部件（如果部件中的所有行都满足表达式）移动到指定磁盘（TO DISK 'xxx'）或到卷 ( TO VOLUME 'xxx')，或聚合过期行中的值。规则的默认类型是删除 ( DELETE)。可以指定多个规则的列表，但不能超过一个DELETE。

  有关更多详细信息，请参阅列和表的 TTL

• SETTINGSMergeTree— 控制（可选）行为的附加参数：

  • index_granularity— 索引标记之间的最大数据行数。默认值：8192。请参阅数据存储。
  • index_granularity_bytes— 数据颗粒的最大大小（以字节为单位）。默认值：10Mb。要仅通过行数限制粒度大小，请设置为 0（不推荐）。请参阅数据存储。
  • min_index_granularity_bytes— 允许的最小数据颗粒大小（以字节为单位）。默认值：1024b。防止意外创建 index_granularity_bytes 非常低的表。请参阅数据存储。
  • 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 会计算所有待合并数据的总存储量。如果卷超过字节，ClickHouse 使用直接 I/O 接口（选项）min_merge_bytes_to_use_direct_io读取和写入数据到存储磁盘。O_DIRECT如果min_merge_bytes_to_use_direct_io = 0，则禁用直接 I/O。默认值：10 * 1024 * 1024 * 1024字节。
  • merge_with_ttl_timeout— 使用删除 TTL 重复合并之前的最小延迟（以秒为单位）。默认值：14400秒（4 小时）。
  • merge_with_recompression_ttl_timeout— 使用重新压缩 TTL 重复合并之前的最小延迟（以秒为单位）。默认值：14400秒（4 小时）。
  • try_fetch_recompressed_part_timeout— 开始合并重新压缩之前的超时（以秒为单位）。在此期间，ClickHouse 尝试从分配此合并重新压缩的副本中获取重新压缩的部分。默认值：7200秒（2 小时）。
  • 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— 压缩以写入表之前未压缩数据块的最大大小。您还可以在全局设置中指定此设置（请参阅max_compress_block_size设置）。创建表时指定的值会覆盖此设置的全局值。
  • min_compress_block_size— 写入下一个标记时压缩所需的未压缩数据块的最小大小。您还可以在全局设置中指定此设置（请参阅min_compress_block_size设置）。创建表时指定的值会覆盖此设置的全局值。
  • max_partitions_to_read— 限制一个查询中可以访问的最大分区数。您还可以在全局设置中指定设置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 是默认值。

 

 

以下为不推荐使用的创建表的方法

！！！注意 “注意” 不要在新项目中使用此方法。如果可能，将旧项目切换到上述方法。

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE [=] MergeTree(date-column [, sampling_expression], (primary, key), index_granularity)

 

MergeTree() 参数

• date-column— Date类型的列的名称。ClickHouse 根据该列自动按月创建分区。分区名称采用"YYYYMM"格式。
• sampling_expression— 抽样表达式。
• (primary, key)- 首要的关键。类型：元组（）
• index_granularity— 索引的粒度。索引“标记”之间的数据行数。值 8192 适用于大多数任务。

　　例子

MergeTree(EventDate, intHash32(UserID), (CounterID, EventDate, intHash32(UserID)), 8192)

　　引擎的MergeTree配置方式与上面示例中的主引擎配置方法相同。