重写 PostgreSQL 查询
这是将 PostgreSQL 迁移到 ClickHouse 的指南的 第二部分。通过一个实际的示例，它演示了如何使用实时复制 (CDC) 方法高效地完成迁移。许多涵盖的概念也适用于从 PostgreSQL 到 ClickHouse 的手动批量数据传输。
您的 PostgreSQL 设置中的大多数 SQL 查询在 ClickHouse 中应该可以无修改地运行，并且执行速度更快。
使用 CDC 进行去重
在使用 CDC 进行实时复制时，请注意，更新和删除可能导致重复行。为了解决这个问题，您可以使用涉及视图和可刷新的物化视图的技术。
请参考本 指南，了解如何在使用实时复制和 CDC 进行迁移时，将您的应用程序从 PostgreSQL 迁移到 ClickHouse，降低摩擦。
在 ClickHouse 中优化查询
虽然可以在最小查询重写的情况下进行迁移，但建议利用 ClickHouse 的特性显著简化查询并进一步改善查询性能。
这里的示例涵盖了常见的查询模式，并展示了如何在 ClickHouse 中优化它们。它们使用完整的 Stack Overflow 数据集（截至 2024 年 4 月）在 PostgreSQL 和 ClickHouse 中的等效资源（8 核心，32GiB 内存）。
为了简单起见，下面的查询省略了使用数据去重的技术。
此处的计数会略有不同，因为 PostgreSQL 数据仅包含满足外键引用完整性的行。ClickHouse 不强加这样的约束，因此拥有完整的数据集，例如，包括匿名用户。
用户（提问超过 10 个）中获得最多浏览量的用户：
-- ClickHouse
SELECT OwnerDisplayName, sum(ViewCount) AS total_views
FROM stackoverflow.posts
WHERE (PostTypeId = 'Question') AND (OwnerDisplayName != '')
GROUP BY OwnerDisplayName
HAVING count() > 10
ORDER BY total_views DESC
LIMIT 5
┌─OwnerDisplayName────────┬─total_views─┐
│ Joan Venge │ 25520387 │
│ Ray Vega │ 21576470 │
│ anon │ 19814224 │
│ Tim │ 19028260 │
│ John │ 17638812 │
└─────────────────────────┴─────────────┘
5 rows in set. Elapsed: 0.360 sec. Processed 24.37 million rows, 140.45 MB (67.73 million rows/s., 390.38 MB/s.)
Peak memory usage: 510.71 MiB.
--Postgres
SELECT OwnerDisplayName, SUM(ViewCount) AS total_views
FROM public.posts
WHERE (PostTypeId = 1) AND (OwnerDisplayName != '')
GROUP BY OwnerDisplayName
HAVING COUNT(*) > 10
ORDER BY total_views DESC
LIMIT 5;
ownerdisplayname | total_views
-------------------------+-------------
Joan Venge | 25520387
Ray Vega | 21576470
Tim | 18283579
J. Pablo Fernández | 12446818
Matt | 12298764
Time: 107620.508 ms (01:47.621)
哪些 tags 收到的 views 最多：
--ClickHouse
SELECT arrayJoin(arrayFilter(t -> (t != ''), splitByChar('|', Tags))) AS tags,
sum(ViewCount) AS views
FROM posts
GROUP BY tags
ORDER BY views DESC
LIMIT 5
┌─tags───────┬──────views─┐
│ javascript │ 8190916894 │
│ python │ 8175132834 │
│ java │ 7258379211 │
│ c# │ 5476932513 │
│ android │ 4258320338 │
└────────────┴────────────┘
5 rows in set. Elapsed: 0.908 sec. Processed 59.82 million rows, 1.45 GB (65.87 million rows/s., 1.59 GB/s.)
--Postgres
WITH tags_exploded AS (
SELECT
unnest(string_to_array(Tags, '|')) AS tag,
ViewCount
FROM public.posts
),
filtered_tags AS (
SELECT
tag,
ViewCount
FROM tags_exploded
WHERE tag <> ''
)
SELECT tag AS tags,
SUM(ViewCount) AS views
FROM filtered_tags
GROUP BY tag
ORDER BY views DESC
LIMIT 5;
tags | views
------------+------------
javascript | 7974880378
python | 7972340763
java | 7064073461
c# | 5308656277
android | 4186216900
(5 rows)
Time: 112508.083 ms (01:52.508)
聚合函数
在可能的情况下，用户应利用 ClickHouse 的聚合函数。下面我们展示了使用 argMax 函数来计算每年最受欢迎的问题。
--ClickHouse
SELECT toYear(CreationDate) AS Year,
argMax(Title, ViewCount) AS MostViewedQuestionTitle,
max(ViewCount) AS MaxViewCount
FROM stackoverflow.posts
WHERE PostTypeId = 'Question'
GROUP BY Year
ORDER BY Year ASC
FORMAT Vertical
Row 1:
──────
Year: 2008
MostViewedQuestionTitle: How to find the index for a given item in a list?
MaxViewCount: 6316987
Row 2:
──────
Year: 2009
MostViewedQuestionTitle: How do I undo the most recent local commits in Git?
MaxViewCount: 13962748
...
Row 16:
───────
Year: 2023
MostViewedQuestionTitle: How do I solve "error: externally-managed-environment" every time I use pip 3?
MaxViewCount: 506822
Row 17:
───────
Year: 2024
MostViewedQuestionTitle: Warning "Third-party cookie will be blocked. Learn more in the Issues tab"
MaxViewCount: 66975
17 rows in set. Elapsed: 0.677 sec. Processed 24.37 million rows, 1.86 GB (36.01 million rows/s., 2.75 GB/s.)
Peak memory usage: 554.31 MiB.
这比等效的 PostgreSQL 查询显著更简单（且更快）：
--Postgres
WITH yearly_views AS (
SELECT
EXTRACT(YEAR FROM CreationDate) AS Year,
Title,
ViewCount,
ROW_NUMBER() OVER (PARTITION BY EXTRACT(YEAR FROM CreationDate) ORDER BY ViewCount DESC) AS rn
FROM public.posts
WHERE PostTypeId = 1
)
SELECT
Year,
Title AS MostViewedQuestionTitle,
ViewCount AS MaxViewCount
FROM yearly_views
WHERE rn = 1
ORDER BY Year;
year | mostviewedquestiontitle | maxviewcount
------+-----------------------------------------------------------------------------------------------------------------------+--------------
2008 | How to find the index for a given item in a list? | 6316987
2009 | How do I undo the most recent local commits in Git? | 13962748
...
2023 | How do I solve "error: externally-managed-environment" every time I use pip 3? | 506822
2024 | Warning "Third-party cookie will be blocked. Learn more in the Issues tab" | 66975
(17 rows)
Time: 125822.015 ms (02:05.822)
条件和数组
条件和数组函数使查询简单得多。以下查询计算了 2022 年到 2023 年之间出现超过 10000 次的标签，其百分比增长最大。请注意，以下的 ClickHouse 查询因条件、数组函数以及在 HAVING 和 SELECT 子句中重用别名的能力而变得简明扼要。
--ClickHouse
SELECT arrayJoin(arrayFilter(t -> (t != ''), splitByChar('|', Tags))) AS tag,
countIf(toYear(CreationDate) = 2023) AS count_2023,
countIf(toYear(CreationDate) = 2022) AS count_2022,
((count_2023 - count_2022) / count_2022) * 100 AS percent_change
FROM stackoverflow.posts
WHERE toYear(CreationDate) IN (2022, 2023)
GROUP BY tag
HAVING (count_2022 > 10000) AND (count_2023 > 10000)
ORDER BY percent_change DESC
LIMIT 5
┌─tag─────────┬─count_2023─┬─count_2022─┬──────percent_change─┐
│ next.js │ 13788 │ 10520 │ 31.06463878326996 │
│ spring-boot │ 16573 │ 17721 │ -6.478189718413183 │
│ .net │ 11458 │ 12968 │ -11.644046884639112 │
│ azure │ 11996 │ 14049 │ -14.613139725247349 │
│ docker │ 13885 │ 16877 │ -17.72826924216389 │
└─────────────┴────────────┴────────────┴─────────────────────┘
5 rows in set. Elapsed: 0.247 sec. Processed 5.08 million rows, 155.73 MB (20.58 million rows/s., 630.61 MB/s.)
Peak memory usage: 403.04 MiB.
--Postgres
SELECT
tag,
SUM(CASE WHEN year = 2023 THEN count ELSE 0 END) AS count_2023,
SUM(CASE WHEN year = 2022 THEN count ELSE 0 END) AS count_2022,
((SUM(CASE WHEN year = 2023 THEN count ELSE 0 END) - SUM(CASE WHEN year = 2022 THEN count ELSE 0 END))
/ SUM(CASE WHEN year = 2022 THEN count ELSE 0 END)::float) * 100 AS percent_change
FROM (
SELECT
unnest(string_to_array(Tags, '|')) AS tag,
EXTRACT(YEAR FROM CreationDate) AS year,
COUNT(*) AS count
FROM public.posts
WHERE EXTRACT(YEAR FROM CreationDate) IN (2022, 2023)
AND Tags <> ''
GROUP BY tag, year
) AS yearly_counts
GROUP BY tag
HAVING SUM(CASE WHEN year = 2022 THEN count ELSE 0 END) > 10000
AND SUM(CASE WHEN year = 2023 THEN count ELSE 0 END) > 10000
ORDER BY percent_change DESC
LIMIT 5;
tag | count_2023 | count_2022 | percent_change
-------------+------------+------------+---------------------
next.js | 13712 | 10370 | 32.22757955641273
spring-boot | 16482 | 17474 | -5.677005837243905
.net | 11376 | 12750 | -10.776470588235295
azure | 11938 | 13966 | -14.520979521695546
docker | 13832 | 16701 | -17.178612059158134
(5 rows)
Time: 116750.131 ms (01:56.750)
