数据反规范化

数据反规范化是 ClickHouse 中的一种技术，使用扁平化表格来帮助通过避免连接来最小化查询延迟。

比较规范化与反规范化模式

数据反规范化涉及有意地逆转规范化过程以优化数据库性能以适应特定的查询模式。在规范化数据库中，数据被拆分为多个相关表以最大限度地减少冗余并确保数据完整性。反规范化通过组合表格、复制数据并将计算字段合并到单个表格或更少表格中来重新引入冗余，有效地将查询中的任何连接从查询时间移动到插入时间。

此过程减少了查询时对复杂连接的需求，可以显着加快读取操作速度，使其非常适合具有大量读取需求和复杂查询的应用程序。但是，它会增加写入操作和维护的复杂性，因为对复制数据的任何更改都必须在所有实例中传播以保持一致性。

NoSQL 解决方案普及的一种常见技术是在没有 JOIN 支持的情况下对数据进行反规范化，有效地将所有统计信息或相关行作为列和嵌套对象存储在父行上。例如，在一个博客的示例模式中，我们可以将所有 Comments 作为它们各自帖子上的对象 Array 存储。

何时使用反规范化

一般来说，我们建议在以下情况下进行反规范化

对那些变化不频繁的表或数据可以容忍在可用于分析查询之前延迟的表进行反规范化，例如数据可以以批处理形式完全重新加载。
避免对多对多关系进行反规范化。如果一个源行发生更改，这可能会导致需要更新许多行。
避免对高基数关系进行反规范化。如果一张表中的每一行在另一张表中都有数千个相关条目，那么这些条目需要作为 Array 表示 - 既可以是基本类型，也可以是元组。通常，包含超过 1000 个元组的数组不建议使用。
与其将所有列反规范化为嵌套对象，不如考虑使用物化视图仅反规范化一个统计信息（见下文）。

并非所有信息都需要反规范化 - 仅反规范化需要频繁访问的关键信息。

反规范化工作可以在 ClickHouse 或上游进行处理，例如使用 Apache Flink。

避免对频繁更新的数据进行反规范化

对于 ClickHouse，反规范化是用户可以用来优化查询性能的几种选项之一，但应谨慎使用。如果数据频繁更新并且需要近乎实时更新，则应避免使用这种方法。如果主表基本上是追加的，或者可以定期作为批处理重新加载（例如每天），则可以使用此方法。

作为一种方法，它存在一个主要挑战 - 写入性能和更新数据。更具体地说，反规范化有效地将数据连接的责任从查询时间转移到摄取时间。虽然这可以显着提高查询性能，但它使摄取变得复杂，意味着如果用于组合它的任何行发生更改，数据管道需要将行重新插入 ClickHouse。这可能意味着源行中的一个更改可能意味着 ClickHouse 中的许多行需要更新。在复杂的模式中，如果行是通过复杂的连接组合起来的，那么连接嵌套组件中一个源行的更改可能会意味着数百万行需要更新。

由于以下两个挑战，实时实现这一点往往是不现实的，并且需要大量的工程工作

在表行更改时触发正确的连接语句。理想情况下，这不会导致连接的所有对象都被更新 - 而是只更新那些受到影响的对象。修改连接以有效地过滤掉正确的行，并在高吞吐量下实现这一点，需要外部工具或工程工作。
ClickHouse 中的行更新需要仔细管理，这会增加额外的复杂性。

因此，批处理更新过程更为常见，其中所有反规范化对象都会定期重新加载。

反规范化的实际案例

让我们考虑一些反规范化可能合理的实际示例，以及其他一些更适合使用替代方法的示例。

考虑一个已经反规范化了统计信息的 Posts 表，例如 AnswerCount 和 CommentCount - 源数据以这种形式提供。实际上，我们可能希望实际规范化这些信息，因为它很可能会经常更改。这些列中的许多也可以通过其他表格获得，例如，帖子的评论可以通过 PostId 列和 Comments 表获得。出于示例目的，我们假设帖子是通过批处理过程重新加载的。

我们也只考虑将其他表格反规范化到 Posts 上，因为我们认为这是我们进行分析的主要表格。在另一个方向上的反规范化对于某些查询也是合适的，并且同样适用上述注意事项。

对于以下每个示例，假设存在一个需要使用两个表格进行连接的查询。

帖子和投票

帖子的投票以单独的表格表示。优化后的模式如下所示，以及加载数据的插入命令

CREATE TABLE votes
(
    `Id` UInt32,
    `PostId` Int32,
    `VoteTypeId` UInt8,
    `CreationDate` DateTime64(3, 'UTC'),
    `UserId` Int32,
    `BountyAmount` UInt8
)
ENGINE = MergeTree
ORDER BY (VoteTypeId, CreationDate, PostId)

INSERT INTO votes SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/votes/*.parquet')

0 rows in set. Elapsed: 26.272 sec. Processed 238.98 million rows, 2.13 GB (9.10 million rows/s., 80.97 MB/s.)

乍一看，这些可能是反规范化到帖子表格上的候选者。这种方法有一些挑战。

投票经常添加到帖子中。虽然这可能随着时间的推移而减少每篇帖子的投票次数，但以下查询表明每小时约有 40k 张投票在 30k 篇帖子中。

SELECT round(avg(c)) AS avg_votes_per_hr, round(avg(posts)) AS avg_posts_per_hr
FROM
(
    SELECT
        toStartOfHour(CreationDate) AS hr,
        count() AS c,
        uniq(PostId) AS posts
    FROM votes
    GROUP BY hr
)

┌─avg_votes_per_hr─┬─avg_posts_per_hr─┐
│           41759 │         33322 │
└──────────────────┴──────────────────┘

如果可以容忍延迟，这可以通过批处理来解决，但这仍然要求我们处理更新，除非我们定期重新加载所有帖子（不太可能令人满意）。

更令人头疼的是，有些帖子有大量的投票

SELECT PostId, concat('https://stackoverflow.com/questions/', PostId) AS url, count() AS c
FROM votes
GROUP BY PostId
ORDER BY c DESC
LIMIT 5

┌───PostId─┬─url──────────────────────────────────────────┬─────c─┐
│ 11227902 │ https://stackoverflow.com/questions/11227902 │ 35123 │
│   927386 │ https://stackoverflow.com/questions/927386   │ 29090 │
│ 11227809 │ https://stackoverflow.com/questions/11227809 │ 27475 │
│   927358 │ https://stackoverflow.com/questions/927358   │ 26409 │
│  2003515 │ https://stackoverflow.com/questions/2003515  │ 25899 │
└──────────┴──────────────────────────────────────────────┴───────┘

这里的主要观察结果是，每个帖子的汇总投票统计信息对于大多数分析来说已经足够了 - 我们不需要反规范化所有投票信息。例如，当前的 Score 列代表了这样一个统计信息，即向上投票总数减去向下投票总数。理想情况下，我们只需要能够通过简单的查找（参见字典）在查询时检索这些统计信息。

用户和徽章

现在让我们考虑一下我们的 Users 和 Badges

我们首先使用以下命令插入数据

CREATE TABLE users
(
    `Id` Int32,
    `Reputation` LowCardinality(String),
    `CreationDate` DateTime64(3, 'UTC') CODEC(Delta(8), ZSTD(1)),
    `DisplayName` String,
    `LastAccessDate` DateTime64(3, 'UTC'),
    `AboutMe` String,
    `Views` UInt32,
    `UpVotes` UInt32,
    `DownVotes` UInt32,
    `WebsiteUrl` String,
    `Location` LowCardinality(String),
    `AccountId` Int32
)
ENGINE = MergeTree
ORDER BY (Id, CreationDate)

CREATE TABLE badges
(
    `Id` UInt32,
    `UserId` Int32,
    `Name` LowCardinality(String),
    `Date` DateTime64(3, 'UTC'),
    `Class` Enum8('Gold' = 1, 'Silver' = 2, 'Bronze' = 3),
    `TagBased` Bool
)
ENGINE = MergeTree
ORDER BY UserId

INSERT INTO users SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/users.parquet')

0 rows in set. Elapsed: 26.229 sec. Processed 22.48 million rows, 1.36 GB (857.21 thousand rows/s., 51.99 MB/s.)

INSERT INTO badges SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/badges.parquet')

0 rows in set. Elapsed: 18.126 sec. Processed 51.29 million rows, 797.05 MB (2.83 million rows/s., 43.97 MB/s.)

虽然用户可能会频繁获得徽章，但这不太可能是一个需要每天更新一次的数据集。徽章和用户之间的关系是一对多。也许我们可以简单地将徽章作为元组列表反规范化到用户身上？虽然可能，但快速检查以确认每个用户获得的最高徽章数量表明这不是理想的选择

SELECT UserId, count() AS c FROM badges GROUP BY UserId ORDER BY c DESC LIMIT 5

┌─UserId─┬─────c─┐
│  22656 │ 19334 │
│   6309 │ 10516 │
│ 100297 │  7848 │
│ 157882 │  7574 │
│  29407 │  6512 │
└────────┴───────┘

将 19k 个对象反规范化到单行可能不太现实。这种关系最好保留为单独的表格或添加统计信息。

我们可能希望反规范化徽章中的统计信息到用户身上，例如徽章的数量。我们在使用字典将此数据集插入时考虑这样一个示例。

帖子和帖子链接

PostLinks 连接用户认为相关的或重复的 Posts。以下查询显示了模式和加载命令

CREATE TABLE postlinks
(
  `Id` UInt64,
  `CreationDate` DateTime64(3, 'UTC'),
  `PostId` Int32,
  `RelatedPostId` Int32,
  `LinkTypeId` Enum('Linked' = 1, 'Duplicate' = 3)
)
ENGINE = MergeTree
ORDER BY (PostId, RelatedPostId)

INSERT INTO postlinks SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/postlinks.parquet')

0 rows in set. Elapsed: 4.726 sec. Processed 6.55 million rows, 129.70 MB (1.39 million rows/s., 27.44 MB/s.)

我们可以确认没有帖子有大量的链接，从而阻止了反规范化

SELECT PostId, count() AS c
FROM postlinks
GROUP BY PostId
ORDER BY c DESC LIMIT 5

┌───PostId─┬───c─┐
│ 22937618 │ 125 │
│  9549780 │ 120 │
│  3737139 │ 109 │
│ 18050071 │ 103 │
│ 25889234 │  82 │
└──────────┴─────┘

同样，这些链接也不是过于频繁发生的事件

SELECT
  round(avg(c)) AS avg_votes_per_hr,
  round(avg(posts)) AS avg_posts_per_hr
FROM
(
  SELECT
  toStartOfHour(CreationDate) AS hr,
  count() AS c,
  uniq(PostId) AS posts
  FROM postlinks
  GROUP BY hr
)

┌─avg_votes_per_hr─┬─avg_posts_per_hr─┐
│            54 │            44 │
└──────────────────┴──────────────────┘

我们将其用作下面的反规范化示例。

简单统计示例

在大多数情况下，反规范化需要在父行上添加单个列或统计信息。例如，我们可能只是希望用重复帖子的数量来丰富我们的帖子，我们只需要添加一个列。

CREATE TABLE posts_with_duplicate_count
(
  `Id` Int32 CODEC(Delta(4), ZSTD(1)),
   ... -other columns
   `DuplicatePosts` UInt16
) ENGINE = MergeTree
ORDER BY (PostTypeId, toDate(CreationDate), CommentCount)

为了填充此表，我们使用 INSERT INTO SELECT 将我们的重复统计信息与我们的帖子连接起来。

INSERT INTO posts_with_duplicate_count SELECT
    posts.*,
    DuplicatePosts
FROM posts AS posts
LEFT JOIN
(
    SELECT PostId, countIf(LinkTypeId = 'Duplicate') AS DuplicatePosts
    FROM postlinks
    GROUP BY PostId
) AS postlinks ON posts.Id = postlinks.PostId

利用复杂类型来处理一对多关系

为了执行反规范化，我们通常需要利用复杂类型。如果正在反规范化的关系是一对一关系，并且列数较少，用户可以简单地将这些列添加为行，并使用其原始类型，如上所示。但是，对于较大的对象，这通常是不可取的，并且对于一对多关系也不可行。

在复杂对象或一对多关系的情况下，用户可以使用

命名元组 - 这些允许将相关结构表示为一组列。
数组（元组）或嵌套 - 命名元组的数组，也称为嵌套，其中每个条目代表一个对象。适用于一对多关系。

例如，我们将在下面演示将PostLinks反规范化到Posts上。

每个帖子可以包含许多指向其他帖子的链接，如前面PostLinks模式所示。作为嵌套类型，我们可以如下表示这些链接的帖子和重复的帖子

SET flatten_nested=0
CREATE TABLE posts_with_links
(
  `Id` Int32 CODEC(Delta(4), ZSTD(1)),
   ... -other columns
   `LinkedPosts` Nested(CreationDate DateTime64(3, 'UTC'), PostId Int32),
   `DuplicatePosts` Nested(CreationDate DateTime64(3, 'UTC'), PostId Int32),
) ENGINE = MergeTree
ORDER BY (PostTypeId, toDate(CreationDate), CommentCount)

请注意使用设置flatten_nested=0。我们建议禁用嵌套数据的扁平化。

我们可以使用INSERT INTO SELECT和OUTER JOIN查询来执行此反规范化

INSERT INTO posts_with_links
SELECT
    posts.*,
    arrayMap(p -> (p.1, p.2), arrayFilter(p -> p.3 = 'Linked' AND p.2 != 0, Related)) AS LinkedPosts,
    arrayMap(p -> (p.1, p.2), arrayFilter(p -> p.3 = 'Duplicate' AND p.2 != 0, Related)) AS DuplicatePosts
FROM posts
LEFT JOIN (
    SELECT
     PostId,
     groupArray((CreationDate, RelatedPostId, LinkTypeId)) AS Related
    FROM postlinks
    GROUP BY PostId
) AS postlinks ON posts.Id = postlinks.PostId

0 rows in set. Elapsed: 155.372 sec. Processed 66.37 million rows, 76.33 GB (427.18 thousand rows/s., 491.25 MB/s.)
Peak memory usage: 6.98 GiB.

注意这里的时间。我们已经成功地在约 2 分钟内对 6600 万行数据进行了反规范化。正如我们稍后将看到的那样，这是一个我们可以安排的操作。

注意groupArray函数的使用，用于将PostLinks折叠成每个PostId的数组，然后进行连接。然后将此数组过滤成两个子列表：LinkedPosts和DuplicatePosts，它们还排除了来自外部连接的任何空结果。

我们可以选择一些行来查看我们新的反规范化结构

SELECT LinkedPosts, DuplicatePosts
FROM posts_with_links
WHERE (length(LinkedPosts) > 2) AND (length(DuplicatePosts) > 0)
LIMIT 1
FORMAT Vertical

Row 1:
──────
LinkedPosts:    [('2017-04-11 11:53:09.583',3404508),('2017-04-11 11:49:07.680',3922739),('2017-04-11 11:48:33.353',33058004)]
DuplicatePosts: [('2017-04-11 12:18:37.260',3922739),('2017-04-11 12:18:37.260',33058004)]

编排和调度反规范化

批处理

利用反规范化需要一个转换过程，在该过程中可以执行和编排反规范化。

我们已经在上面展示了如何使用 ClickHouse 在数据通过INSERT INTO SELECT加载后执行此转换。这适用于周期性批处理转换。

用户有几个选择来在 ClickHouse 中编排此操作，假设周期性批处理加载过程是可以接受的

外部工具 - 使用诸如dbt和Airflow之类的工具定期安排转换。该ClickHouse 集成到 dbt可确保此操作以原子方式执行，使用目标表的最新版本创建，然后通过EXCHANGE命令原子方式与接收查询的版本交换。
可刷新物化视图（实验性） - 可刷新物化视图可用于定期安排查询，并将结果发送到目标表。在查询执行时，视图确保原子更新目标表。这为安排此工作提供了一种 ClickHouse 原生方法。

流式传输

用户可以选择在插入之前，使用诸如Apache Flink之类的流式传输技术，在 ClickHouse 之外执行此操作。或者，可以将增量物化视图用于在数据插入时执行此过程。

数据反规范化

比较规范化与反规范化模式​

何时使用反规范化​

避免对频繁更新的数据进行反规范化​

反规范化的实际案例​

帖子和投票​

用户和徽章​

帖子和帖子链接​

简单统计示例​

利用复杂类型来处理一对多关系​

编排和调度反规范化​

批处理​

流式传输​