设计 Schema
这是从 PostgreSQL 迁移到 ClickHouse 指南的第二部分。此内容可以被认为是入门级的，旨在帮助用户部署一个符合 ClickHouse 最佳实践的初始功能系统。它避免了复杂的主题，不会产生完全优化的 schema；相反，它为用户构建生产系统和学习奠定了坚实的基础。
Stack Overflow 数据集包含许多相关的表。我们建议迁移首先关注迁移它们的主表。这可能不一定是最大的表，而是您期望接收最多分析查询的表。这将使您熟悉主要的 ClickHouse 概念，如果您主要来自 OLTP 背景，这尤其重要。随着添加更多表以充分利用 ClickHouse 功能并获得最佳性能，此表可能需要重新建模。我们在数据建模文档中探讨了此建模过程。
建立初始 schema
遵循此原则，我们专注于主要的 posts 表。下面显示了此表的 Postgres schema
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.
这为我们提供了一个初始的非优化 schema。
如果没有
NOT NULL Constraint，Postgres 列可以包含 Null 值。在不检查行值的情况下，ClickHouse 将这些值映射到等效的 Nullable 类型。请注意，主键不是 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 │
└──────────┘
优化类型
优化此 schema 类型的步骤与从其他来源加载数据（例如 S3 上的 Parquet）的情况相同。应用此使用 Parquet 的备用指南中描述的过程将得到以下 schema
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.)
我们在新的 schema 中不保留任何 null 值。上面的插入将这些值隐式转换为其各自类型的默认值 - 整数为 0，字符串为空值。ClickHouse 还会自动将任何数值转换为其目标精度。
ClickHouse 中的主键（排序键）
来自 OLTP 数据库的用户通常会在 ClickHouse 中寻找等效的概念。当注意到 ClickHouse 支持 PRIMARY KEY 语法时，用户可能会尝试使用与其源 OLTP 数据库相同的键来定义其表 schema。这是不合适的。
ClickHouse 主键有何不同？
要理解为什么在 ClickHouse 中使用 OLTP 主键是不合适的，用户应该理解 ClickHouse 索引的基础知识。我们以 Postgres 为例进行比较，但这些一般概念适用于其他 OLTP 数据库。
- 根据定义，Postgres 主键对于每行都是唯一的。 B 树结构的使用允许通过此键有效地查找单行。虽然可以针对单行值的查找优化 ClickHouse，但分析工作负载通常需要读取少量列但针对许多行。过滤器将更频繁地需要识别将在其上执行聚合的行子集。
- 内存和磁盘效率对于 ClickHouse 经常使用的规模至关重要。数据以称为 parts 的块写入 ClickHouse 表，并应用规则在后台合并 parts。在 ClickHouse 中，每个 part 都有自己的主索引。当 parts 合并时，合并后的 part 的主索引也会合并。与 Postgres 不同，这些索引不是为每行构建的。相反，part 的主索引每组行有一个索引条目 - 此技术称为稀疏索引。
- 稀疏索引之所以成为可能，是因为 ClickHouse 将 part 的行存储在磁盘上，并按指定的键排序。稀疏主索引不是直接定位单行（如基于 B 树的索引），而是允许它快速（通过对索引条目的二进制搜索）识别可能与查询匹配的行组。然后，将定位到的可能匹配的行组并行流式传输到 ClickHouse 引擎中，以查找匹配项。这种索引设计允许主索引很小（它完全适合主内存），同时仍然显着加快查询执行时间，特别是对于数据分析用例中典型的范围查询。有关更多详细信息，我们推荐这篇深入指南。
ClickHouse 中选择的键不仅决定索引，还决定数据写入磁盘的顺序。因此，它可能会显着影响压缩级别，进而影响查询性能。导致大多数列的值以连续顺序写入的排序键将允许所选的压缩算法（和编解码器）更有效地压缩数据。
表中的所有列都将根据指定的排序键的值进行排序，无论它们是否包含在键本身中。例如，如果
CreationDate用作键，则所有其他列中的值的顺序将对应于CreationDate列中的值的顺序。可以指定多个排序键 - 这将以与SELECT查询中的ORDER BY子句相同的语义进行排序。
选择排序键
有关选择排序键的注意事项和步骤，以 posts 表为例，请参阅此处。
压缩
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 │
└─────────────┴─────────────────┘
有关优化和衡量压缩的更多详细信息，请参见此处。