跟着chatgpt一起学|clickhouse入门（3）MergeTree

跟着chatgpt一起学|2.clickhouse入门（1）-CSDN博客

跟着chatgpt一起学|2.Clickhouse入门（2）-CSDN博客

chatgpt规划的学习路径如下：

3.MergeTree的分类和适用场景

`MergeTree` 引擎是 ClickHouse 中最为强大和多用途的引擎之一，适用于需要高性能读写、数据压缩和实时数据分析的场景。不同的 `MergeTree` 引擎变种针对不同的用例进行了优化。以下是 `MergeTree` 引擎的一些分类和它们的适用场景：

1. MergeTree： 
   - 适用场景：基础的分析型工作负载，需要高效的数据插入和查询性能。
   - 特点：需手动指定分区和排序规则。

2. ReplacingMergeTree：
   - 适用场景：需要自动去除重复数据的应用中，例如，有相同业务主键的最新数据记录。
   - 特点：可以根据某些列合并或替换数据中的重复项。

   - 工作流程：

   1). 数据插入：
      - 当数据被插入到 `ReplacingMergeTree` 表时，它们首先以小的数据块的形式存储在表中。这些插入操作通常很快，因为它们只追加到现有的数据分区中。

   2). 重复数据标识：
      - `ReplacingMergeTree` 引擎允许用户指定一个列（通常称为 `version` 列）来标识数据行的版本。如果没有指定版本列，引擎将使用所有主键列（定义在表的 `ORDER BY` 子句中）来识别重复的记录。

   3). 后台合并过程：
      - ClickHouse 定期在后台进行合并操作，将多个小数据块合并成更大的数据块。在合并过程中，`ReplacingMergeTree` 引擎会检查重复的数据行。
      - 如果两行具有相同的主键列值，但不同的 `version` 值，较老版本的行将被较新版本的行替换。如果未指定 `version` 列，则所有除最后插入一行之外的重复行都将被删除。

   4). 去重：
      - 通过合并操作，`ReplacingMergeTree` 引擎确保表中不会保留重复的数据。最终，每个主键值只对应一个数据行，通常是版本值最高的行。

   5). 查询时去重：
      - 即使在合并操作发生之前，查询也能返回去重后的数据。ClickHouse 的查询处理器能够在读取数据时动态地去除重复的行。

CREATE TABLE example (
    Date Date,
    UserID UInt32,
    CounterID UInt32,
    Version UInt32,
    Value UInt32
) ENGINE = ReplacingMergeTree(Version)
PARTITION BY toYYYYMM(Date)
ORDER BY (UserID, CounterID, Date);

在这个例子中，`Version` 列被用作版本控制，用于标识数据行的版本。在合并时，ClickHouse 将保留 `Version` 值最大的数据行，并去除其他重复的行。如果没有指定 `Version` 列，所有非最后插入的重复行都将在合并时被去除。

3. SummingMergeTree：
   - 适用场景：适合于那些需要自动聚合数值字段的场景，例如，当需要总结相同维度的指标时。
   - 特点：在查询时自动求和具有相同排序键的行

   - 工作流程：与ReplacingMergeTree类似，只不过把去重改为了累加和，如果没有指定响应的列，引擎将对所有数值列进行求和。

CREATE TABLE page_views (
    Date Date,
    Page String,
    Views UInt32,
    Clicks UInt32
) ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(Date)
ORDER BY (Date, Page);

如果仅想对个别列求和：ENGINE = SummingMergeTree(Value1)

CREATE TABLE example (
    Date Date,
    Category String,
    Value1 Int64,
    Value2 Int64,
    NonSummedValue Int64
) ENGINE = SummingMergeTree(Value1)
PARTITION BY toYYYYMM(Date)
ORDER BY (Date, Category)
SETTINGS index_granularity = 8192;

4. AggregatingMergeTree：
   - 适用场景：用于存储预聚合的数据，非常适合需要快速聚合查询的数据仓库。
   - 特点：能够存储和处理 ClickHouse 特有的聚合数据类型。

   - 工作流程： 和上面类似，但在插入数据时，你需要提供聚合数据而不是原始数据。这通常通过使用 `AggregateFunction` 类型的列和聚合函数（如 `sum()`, `count()`, `min()`, `max()`, `any()`, 等）来完成。在写入之前，数据在内存中预聚合。

CREATE TABLE agg_table (
    Date Date,
    Key String,
    Value AggregateFunction(sum, UInt64)
) ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(Date)
ORDER BY (Date, Key);

在这个例子中，`Value` 列被定义为类型 `AggregateFunction(sum, UInt64)`，表示它存储由 `sum` 聚合函数产生的数据。当数据合并时，同一日期 (`Date`) 和键 (`Key`) 的 `Value` 列会使用 `sumMerge` 函数来合并。

• 插入数据：

使用聚合函数相应的-State函数

INSERT INTO example (date, key, value)
VALUES ('2021-01-01', 1, sumState(100));

• 读取数据

使用聚合函数相应的-Merge函数

SELECT
    date,
    key,
    sumMerge(value) AS total
FROM example
GROUP BY date, key;

在使用 `AggregatingMergeTree` 时，一定要注意在插入数据前对其进行预聚合，并且在查询时使用相应的 `-Merge` 后缀的聚合函数来获取最终结果。这样，你就可以充分利用 `AggregatingMergeTree` 引擎的高效聚合能力。

5. CollapsingMergeTree：
   - 适用场景：当你需要动态地合并和折叠记录来进行数据去重时。
   - 特点：使用一个名为 `Sign` 的列来标记数据行的状态，用于在查询时动态折叠重复数据。

   - 工作流程：

        1） 创建表

创建一个 `CollapsingMergeTree` 类型的表，并指定一个 `Sign` 列，该列的类型必须为 `Int8`。`Sign` 列的值通常为 `1` 或 `-1`，分别代表增加或减少数据。

CREATE TABLE example (
    date Date,
    key UInt64,
    value UInt64,
    Sign Int8
) ENGINE = CollapsingMergeTree(Sign)
ORDER BY (date, key);

        2). 插入数据

当插入新数据时，如果数据是新增的，`Sign` 列的值应该为 `1`。如果数据是用于抵消之前的数据的，`Sign` 列的值应该为 `-1`。

-- 插入一条正常记录 
INSERT INTO example (date, key, value, Sign) VALUES ('2021-01-01', 1, 100, 1); 
-- 插入一条用于与先前记录抵消的记录 
INSERT INTO example (date, key, value, Sign) VALUES ('2021-01-01', 1, 100, -1);

        3). 合并数据：

`CollapsingMergeTree` 表的合并操作发生在后台，它会自动合并具有相同排序键的行，并且根据 `Sign` 列的值来增加或减少计数。合并过程会对具有相同排序键的所有行的 `Sign` 值求和，如果总和为零，则这些行将从表中删除。

         4). 查询数据：

在查询时，通常使用 `GROUP BY` 子句和聚合函数来获取最终结果。查询时一般会使用 `sum` 函数对 `Sign` 列求和，以确保抵消的记录不会影响最终的聚合结果。

SELECT
    date,
    key,
    sum(value * Sign) AS total_value
FROM example
GROUP BY date, key;

在上面的查询中，通过将 `value` 与 `Sign` 相乘，我们能够得到最终的总和 `total_value`，其中包括所有记录的累加和减去所有被抵消的记录。

`CollapsingMergeTree` 表引擎的一个关键特性是，它能够有效地处理和简化数据更新和删除操作，因为你只需要插入新的带有相应 `Sign` 值的记录来表示这些更改。在 ClickHouse 进行数据合并时，它会自动处理这些记录的增加和抵消操作。

6. VersionedCollapsingMergeTree：
   - 适用场景：适用于需要维护数据的历史版本的场景，例如，记录数据变更历史。
   - 特点：提供行的版本控制机制，可以保留数据的多个版本。

7. GraphiteMergeTree：
   - 适用场景：专门为 Graphite 监控系统设计，适用于处理 Graphite 时间序列数据。
   - 特点：对 Graphite 的数据保留和聚合规则提供了特殊的支持。

选择合适的 `MergeTree` 引擎时，需要考虑数据的特性、更新频率、查询模式以及是否需要特殊的数据去重或聚合逻辑。`MergeTree` 引擎的高效数据存储和查询能力使得 ClickHouse 在大数据分析领域表现出色。