设计模式

这是从 PostgreSQL 迁移到 ClickHouse 的指南的 **第 2 部分**。此内容可被视为入门级内容，旨在帮助用户部署符合 ClickHouse 最佳实践的初始功能系统。它避免了复杂主题，不会导致完全优化的模式；相反，它为用户提供了构建生产系统和进行学习的坚实基础。

Stack Overflow 数据集包含多个相关表。我们建议迁移首先专注于迁移它们的主要表。这可能不一定是最大的表，而是您期望接收最多分析查询的表。这将使您熟悉主要 ClickHouse 概念，特别是如果您来自以 OLTP 为主的背景，这一点非常重要。此表可能需要重新建模，因为添加了其他表以充分利用 ClickHouse 功能并获得最佳性能。我们在我们的数据建模文档中探讨了此建模过程。

建立初始模式

遵循此原则，我们专注于主要的 posts 表。以下是此表的 Postgres 模式

CREATE TABLE posts (
   Id int,
   PostTypeId int,
   AcceptedAnswerId text,
   CreationDate timestamp,
   Score int,
   ViewCount int,
   Body text,
   OwnerUserId int,
   OwnerDisplayName text,
   LastEditorUserId text,
   LastEditorDisplayName text,
   LastEditDate timestamp,
   LastActivityDate timestamp,
   Title text,
   Tags text,
   AnswerCount int,
   CommentCount int,
   FavoriteCount int,
   ContentLicense text,
   ParentId text,
   CommunityOwnedDate timestamp,
   ClosedDate timestamp,
   PRIMARY KEY (Id),
   FOREIGN KEY (OwnerUserId) REFERENCES users(Id)
)

要为上述每个列建立等效类型，我们可以使用 DESCRIBE 命令以及 Postgres 表函数。将以下命令修改为您的 Postgres 实例

DESCRIBE TABLE postgresql('<host>:<port>', 'postgres', 'posts', '<username>', '<password>')
SETTINGS describe_compact_output = 1

┌─name──────────────────┬─type────────────────────┐
│ id                 │ Int32                   │
│ posttypeid         │ Nullable(Int32)         │
│ acceptedanswerid   │ Nullable(String)        │
│ creationdate       │ Nullable(DateTime64(6)) │
│ score              │ Nullable(Int32)         │
│ viewcount          │ Nullable(Int32)         │
│ body               │ Nullable(String)        │
│ owneruserid        │ Nullable(Int32)         │
│ ownerdisplayname   │ Nullable(String)        │
│ lasteditoruserid   │ Nullable(String)        │
│ lasteditordisplayname │ Nullable(String)         │
│ lasteditdate       │ Nullable(DateTime64(6)) │
│ lastactivitydate   │ Nullable(DateTime64(6)) │
│ title              │ Nullable(String)        │
│ tags               │ Nullable(String)        │
│ answercount        │ Nullable(Int32)         │
│ commentcount       │ Nullable(Int32)         │
│ favoritecount      │ Nullable(Int32)         │
│ contentlicense     │ Nullable(String)        │
│ parentid           │ Nullable(String)        │
│ communityowneddate    │ Nullable(DateTime64(6)) │
│ closeddate         │ Nullable(DateTime64(6)) │
└───────────────────────┴─────────────────────────┘

22 rows in set. Elapsed: 0.478 sec.

这为我们提供了最初的非优化模式。

没有 NOT NULL Constraint，Postgres 列可以包含 Null 值。在不检查行值的情况下，ClickHouse 会将其映射到等效的可空类型。请注意，主键不为 Null，这是 Postgres 中的要求。

我们可以使用这些类型使用简单的 CREATE AS EMPTY SELECT 命令创建 ClickHouse 表。

CREATE TABLE posts
ENGINE = MergeTree
ORDER BY () EMPTY AS
SELECT * FROM postgresql('<host>:<port>', 'postgres', 'posts', '<username>', '<password>')

相同的方法可用于从 s3 中以其他格式加载数据。请参阅此处，了解从 Parquet 格式加载此数据的等效示例。

初始加载

表创建后，我们可以使用 Postgres 表函数将 Postgres 中的行插入 ClickHouse。

INSERT INTO posts SELECT *
FROM postgresql('<host>:<port>', 'postgres', 'posts', '<username>', '<password>')

0 rows in set. Elapsed: 1136.841 sec. Processed 58.89 million rows, 80.85 GB (51.80 thousand rows/s., 71.12 MB/s.)
Peak memory usage: 2.51 GiB.

此操作可能会对 Postgres 造成相当大的负载。用户可能希望使用其他操作来进行回填，以避免影响生产工作负载，例如导出 SQL 脚本。此操作的性能将取决于您的 Postgres 和 ClickHouse 集群大小以及它们的网络互连。

从 ClickHouse 到 Postgres 的每个 SELECT 使用单个连接。此连接来自服务器端连接池，其大小由设置 postgresql_connection_pool_size（默认值为 16）确定。

如果使用完整的数据集，示例应加载 5900 万个帖子。使用 ClickHouse 中的简单计数进行确认

SELECT count()
FROM posts

┌──count()─┐
│ 58889566 │
└──────────┘

优化类型

优化此模式类型的步骤与从其他来源（例如 S3 上的 Parquet）加载数据时的步骤相同。应用本使用 Parquet 的备用指南中描述的过程将得出以下模式

CREATE TABLE posts_v2
(
   `Id` Int32,
   `PostTypeId` Enum('Question' = 1, 'Answer' = 2, 'Wiki' = 3, 'TagWikiExcerpt' = 4, 'TagWiki' = 5, 'ModeratorNomination' = 6, 'WikiPlaceholder' = 7, 'PrivilegeWiki' = 8),
   `AcceptedAnswerId` UInt32,
   `CreationDate` DateTime,
   `Score` Int32,
   `ViewCount` UInt32,
   `Body` String,
   `OwnerUserId` Int32,
   `OwnerDisplayName` String,
   `LastEditorUserId` Int32,
   `LastEditorDisplayName` String,
   `LastEditDate` DateTime,
   `LastActivityDate` DateTime,
   `Title` String,
   `Tags` String,
   `AnswerCount` UInt16,
   `CommentCount` UInt8,
   `FavoriteCount` UInt8,
   `ContentLicense`LowCardinality(String),
   `ParentId` String,
   `CommunityOwnedDate` DateTime,
   `ClosedDate` DateTime
)
ENGINE = MergeTree
ORDER BY tuple()
COMMENT 'Optimized types'

我们可以使用简单的 INSERT INTO SELECT 来填充它，从我们之前的表中读取数据并插入到此表中

INSERT INTO posts_v2 SELECT * FROM posts

0 rows in set. Elapsed: 146.471 sec. Processed 59.82 million rows, 83.82 GB (408.40 thousand rows/s., 572.25 MB/s.)

我们在新模式中不保留任何空值。以上插入会将这些空值隐式转换为各自类型的默认值 - 整数为 0，字符串为空值。ClickHouse 还会自动将任何数字转换为其目标精度。

ClickHouse 中的主键（排序键）

来自 OLTP 数据库的用户经常会在 ClickHouse 中寻找等效的概念。注意到 ClickHouse 支持 PRIMARY KEY 语法后，用户可能会尝试使用与其源 OLTP 数据库相同的键来定义其表模式。这是不合适的。

ClickHouse 主键有何不同？

要了解为什么在 ClickHouse 中使用 OLTP 主键不合适，用户应了解 ClickHouse 索引的基础知识。我们以 Postgres 为例，但这些通用概念适用于其他 OLTP 数据库。

根据定义，Postgres 主键每行唯一。使用 B 树结构允许通过此键高效地查找单个行。虽然 ClickHouse 可以针对单个行值的查找进行优化，但分析工作负载通常需要读取少量列，但要读取大量行。过滤器更经常需要识别要对其执行聚合的 行子集。
内存和磁盘效率对于 ClickHouse 通常使用的规模至关重要。数据以称为部分的块写入 ClickHouse 表，并应用规则在后台合并部分。在 ClickHouse 中，每个部分都有自己的主键索引。当部分合并时，合并部分的主键索引也会合并。与 Postgres 不同，这些索引不是为每行构建的。相反，部分的主键索引每个行组有一个索引项 - 这种技术称为 稀疏索引。
稀疏索引 是可能的，因为 ClickHouse 将部分的行按指定键的顺序存储在磁盘上。稀疏主键索引不会直接定位单个行（如基于 B 树的索引），而是允许它快速（通过索引项上的二进制搜索）识别可能与查询匹配的行组。然后，以并行方式将位于的可能匹配的行组流式传输到 ClickHouse 引擎中，以找到匹配项。这种索引设计允许主键索引很小（完全适合主内存），同时仍然显着加快查询执行时间，特别是对于数据分析用例中常见的范围查询。有关更多详细信息，我们建议您阅读此深入指南。

ClickHouse 中选择的键不仅决定索引，还决定数据在磁盘上的写入顺序。因此，它会极大地影响压缩级别，进而影响查询性能。导致大多数列的值按连续顺序写入的排序键将允许所选压缩算法（和编解码器）更有效地压缩数据。

表中的所有列将根据指定排序键的值进行排序，无论它们本身是否包含在键中。例如，如果使用 CreationDate 作为键，则所有其他列的值顺序将对应于 CreationDate 列的值顺序。可以指定多个排序键 - 这将按与 SELECT 查询中的 ORDER BY 子句相同的语义进行排序。

选择排序键

有关选择排序键的注意事项和步骤，请以帖子表为例，请参阅此处。

压缩

ClickHouse 的面向列的存储意味着与 Postgres 相比，压缩通常会好很多。以下是在比较两个数据库中所有 Stack Overflow 表的存储要求时进行了说明

--Postgres
SELECT
    schemaname,
    tablename,
    pg_total_relation_size(schemaname || '.' || tablename) AS total_size_bytes,
    pg_total_relation_size(schemaname || '.' || tablename) / (1024 * 1024 * 1024) AS total_size_gb
FROM
    pg_tables s
WHERE
    schemaname = 'public';
 schemaname |   tablename   | total_size_bytes | total_size_gb |
------------+-----------------+------------------+---------------+
 public     | users         |       4288405504 |            3 |
 public     | posts         |   68606214144 |           63 |
 public     | votes         |   20525654016 |           19 |
 public     | comments      |   22888538112 |           21 |
 public     | posthistory   |   125899735040 |          117 |
 public     | postlinks     |       579387392 |             0 |
 public     | badges        |       4989747200 |            4 |
(7 rows)

--ClickHouse
SELECT
    `table`,
    formatReadableSize(sum(data_compressed_bytes)) AS compressed_size
FROM system.parts
WHERE (database = 'stackoverflow') AND active
GROUP BY `table`

┌─table───────┬─compressed_size─┐
│ posts        │ 25.17 GiB       │
│ users        │ 846.57 MiB      │
│ badges       │ 513.13 MiB      │
│ comments      │ 7.11 GiB       │
│ votes        │ 1.28 GiB        │
│ posthistory │ 40.44 GiB    │
│ postlinks   │ 79.22 MiB    │
└─────────────┴─────────────────┘

有关优化和衡量压缩的更多详细信息，请参阅此处。

单击此处查看第 3 部分.

建立初始模式​

初始加载​

优化类型​

ClickHouse 中的主键（排序键）​

ClickHouse 主键有何不同？​

选择排序键​

压缩​