在我了解 ClickHouse 的大部分时间里，我的理解是它是一个具有传统客户端-服务器架构的分析数据库，旨在以低延迟服务于大量并发查询。
直到最近几个月，我才意识到一些工具改变了我的理解：ClickHouse Local，它让我们通过 CLI 运行 ClickHouse 前端的进程内版本；以及 chDB，一个由 ClickHouse 驱动的嵌入式 SQL OLAP 引擎。
我发现自己在为 ClickHouse YouTube 频道 创建的大部分视频中都混合使用了这些工具。
最值得注意的例外是一个视频，解释了物化视图，并得到了 ClickPy 的帮助。ClickPy 是托管在 ClickHouse Cloud 上的 ClickHouse 服务，其中包含来自 Python 的 PyPi 包管理器的数据包下载数据。该数据集帮助我们了解随时间推移下载次数最多的软件包，按国家/地区、安装程序、版本和许多其他维度分组。
该数据库还包含关于每个软件包的元数据，包括项目主页，通常是 GitHub 仓库。这让我开始思考，将这些库的 GitHub 指标（例如，星数或 fork 数）与下载数量进行比较会非常有趣。
我询问了我的同事 Dale，我们是否可以将 GitHub 数据添加到 ClickPy 服务器上，他建议我首先看看 ClickHouse 的 remote 和 remoteSecure 表函数。这些函数允许您从另一个 ClickHouse 客户端动态查询远程 ClickHouse 服务器。我们还可以将来自远程查询的数据与本地 ClickHouse 中的数据连接起来，当与 ClickHouse Local 一起使用时，这意味着我们可以实现一种混合查询执行。
我应该指出，这不是 ClickHouse 当前优化的用例，但我认为这将是一个有趣的实验，所以让我们开始吧！
使用 ClickHouse Local 查询 GitHub 指标
我编写了一个小的 Python 脚本，从 GitHub API 下载尽可能多的项目的数据，并将每个项目存储在我机器上的独立 JSON 文件中。例如，下面是 Langchain 项目的数据子集
{
"id": 552661142,
"node_id": "R_kgDOIPDwlg",
"name": "langchain",
"full_name": "langchain-ai/langchain",
...
"topics": [],
"visibility": "public",
"forks": 10190,
"open_issues": 2109,
"watchers": 69585,
"default_branch": "master",
...
"subscribers_count": 606
}
我们将使用 ClickHouse Local 探索这些文件，所以让我们在我们的机器上启动它
./clickhouse local -m
我们可以运行以下查询来查找根据 GitHub 星数最受欢迎的 PyPi 包
FROM file('data/*.json', JSONEachRow)
SELECT full_name, stargazers_count AS stars, forks
ORDER BY stargazers_count DESC
LIMIT 10;
┌─full_name────────────────┬──stars─┬─forks─┐
│ huggingface/transformers │ 116073 │ 23147 │
│ langchain-ai/langchain │ 69585 │ 10190 │
│ tiangolo/fastapi │ 65210 │ 5519 │
│ yt-dlp/yt-dlp │ 60914 │ 4994 │
│ keras-team/keras │ 59836 │ 19477 │
│ ansible/ansible │ 59352 │ 23867 │
│ openai/whisper │ 51217 │ 5828 │
│ localstack/localstack │ 50301 │ 3822 │
│ Textualize/rich │ 45582 │ 1686 │
│ psf/black │ 35545 │ 2339 │
└──────────────────────────┴────────┴───────┘
10 rows in set. Elapsed: 0.140 sec. Processed 2.08 thousand rows, 14.97 MB (14.91 thousand rows/s., 107.28 MB/s.)
Peak memory usage: 48.50 KiB.
我认为看到许多在生成式 AI 应用程序中使用的库在 GitHub 上非常受欢迎并不令人意外。
在 ClickHouse Cloud 上查询流行的 PyPi 项目
现在我们需要找出 ClickPy 数据库中哪些项目以 GitHub 仓库作为其项目主页。让我们首先使用只读play用户连接到 ClickPy 数据库
./clickhouse client -m \
-h clickpy-clickhouse.clickhouse.com \
--user play --secure
现在让我们编写一个查询，查找具有 GitHub 仓库的最受欢迎的 PyPi 项目。我们将通过连接 pypi_downloads 和 projects 表来完成此操作。我们在服务器上直接运行以下命令
SELECT name,
replaceOne(home_page, 'https://github.com/', '') AS repository,
sum(count) AS count
FROM pypi.pypi_downloads AS downloads
INNER JOIN (
SELECT name, argMax(home_page, version) AS home_page
FROM pypi.projects
GROUP BY name
) AS projects ON projects.name = downloads.project
WHERE projects.home_page LIKE '%github%'
GROUP BY ALL
ORDER BY count DESC
LIMIT 10;
┌─name───────────────┬─repository─────────────────┬───────count─┐
│ boto3 │ boto/boto3 │ 16031894410 │
│ botocore │ boto/botocore │ 11033306159 │
│ certifi │ certifi/python-certifi │ 8606959885 │
│ s3transfer │ boto/s3transfer │ 8575775398 │
│ python-dateutil │ dateutil/dateutil │ 8144178765
│ charset-normalizer │ Ousret/charset_normalizer │ 5891178066 │
│ jmespath │ jmespath/jmespath.py │ 5405618311 │
│ pyasn1 │ pyasn1/pyasn1 │ 5378303214 │
│ google-api-core │ googleapis/python-api-core │ 5022394699 │
│ importlib-metadata │ python/importlib_metadata │ 4353215364 │
└────────────────────┴────────────────────────────┴─────────────┘
10 rows in set. Elapsed: 0.260 sec. Processed 12.28 million rows, 935.69 MB (47.16 million rows/s., 3.59 GB/s.)
Peak memory usage: 1.02 GiB.
让我们看一个图表，显示不同位数据所在的位置。
配置远程查询的权限
接下来我想做的是将查找 PyPi 项目的查询与返回 GitHub 指标的查询结合起来。主要的挑战是 PyPi 数据在 Clickhouse Cloud 上，而 GitHub 指标在我的机器上。
我不想用我的 GitHub 数据污染 ClickHouse Cloud 实例，所以相反，我将使用 remoteSecure 表函数从我的机器查询 ClickHouse Cloud。为了使用此函数连接 projects 和 pypi_downloads 表，我们需要创建一个具有以下权限的用户
GRANT CREATE TEMPORARY TABLE, REMOTE ON *.* TO <user>
一旦我在 ClickPy 服务器上创建了一个名为 _mark_ 且具有此权限的用户，我们就可以返回到我们的 ClickHouse Local 会话并将密码定义为参数
set param_password = 'my-password';
从 ClickHouse Local 查询 ClickHouse Cloud
现在我们将运行上述查询的一个版本，该版本使用 remoteSecure 函数查找最受欢迎的 PyPi 项目。
SELECT name,
replaceOne(home_page, 'https://github.com/', '') AS repository,
sum(count) AS count
FROM remoteSecure(
'clickpy-clickhouse.clickhouse.com',
'pypi.pypi_downloads',
'mark', {password:String}
) AS pypi_downloads
INNER JOIN
(
SELECT name, argMax(home_page, version) AS home_page
FROM remoteSecure(
'clickpy-clickhouse.clickhouse.com',
'pypi.projects',
'mark', {password:String}
)
GROUP BY name
) AS projects ON projects.name = pypi_downloads.project
WHERE projects.home_page LIKE '%github%'
GROUP BY ALL
ORDER BY count DESC
LIMIT 10;
┌─name───────────────┬─repository─────────────────┬───────count─┐
│ boto3 │ boto/boto3 │ 16031894410 │
│ botocore │ boto/botocore │ 11033306159 │
│ certifi │ certifi/python-certifi │ 8606959885 │
│ s3transfer │ boto/s3transfer │ 8575775398 │
│ python-dateutil │ dateutil/dateutil │ 8144178765 │
│ charset-normalizer │ Ousret/charset_normalizer │ 5891178066 │
│ jmespath │ jmespath/jmespath.py │ 5405618311 │
│ pyasn1 │ pyasn1/pyasn1 │ 5378303214 │
│ google-api-core │ googleapis/python-api-core │ 5022394699 │
│ importlib-metadata │ python/importlib_metadata │ 4353215364 │
└────────────────────┴────────────────────────────┴─────────────┘
10 rows in set. Elapsed: 1.703 sec.
正如我们所期望的那样，我们得到了与之前相同的结果。此查询运行时间稍长，因为虽然 JOIN 是在 ClickPy 服务器上完成的，但每次运行查询时我们都会初始化到 ClickPy 服务器的新连接。我们可以通过在查询前加上 EXPLAIN PLAN 来检查 join 是否在远程完成，这将返回以下内容
┌─explain───────────────────────────────────┐
│ ReadFromRemote (Read from remote replica) │
└───────────────────────────────────────────┘
如果 JOIN 是在本地完成的，我们将在查询计划中看到 Join 运算符。
将来自 ClickHouse Cloud 的数据与 ClickHouse Local 连接
接下来，让我们将此数据与本地 GitHub 数据集连接起来
SELECT
projects.name,
replaceOne(home_page, 'https://github.com/', '') AS repository,
sum(count) AS count,
gh.stargazers_count AS stars
FROM remoteSecure(
'clickpy-clickhouse.clickhouse.com',
'pypi.pypi_downloads',
'mark', {password:String}
) AS pypi_downloads
INNER JOIN
(
SELECT name, argMax(home_page, version) AS home_page
FROM remoteSecure(
'clickpy-clickhouse.clickhouse.com',
'pypi.projects',
'mark', {password:String}
)
GROUP BY name
) AS projects ON projects.name = pypi_downloads.project
INNER JOIN
(
SELECT *
FROM file('data/*.json', JSONEachRow)
) AS gh ON gh.svn_url = projects.home_page
GROUP BY ALL
ORDER BY stars DESC
LIMIT 10;
这将产生以下输出
┌─projects.name────────────┬─repository───────────────┬─────count─┬──stars─┐
│ in-transformers │ huggingface/transformers │ 881 │ 116073 │
│ richads-transformers │ huggingface/transformers │ 1323 │ 116073 │
│ transformers-machinify │ huggingface/transformers │ 999 │ 116073 │
│ transformers-phobert │ huggingface/transformers │ 4550 │ 116073 │
│ transformers │ huggingface/transformers │ 302008339 │ 116073 │
│ langchain │ langchain-ai/langchain │ 35657607 │ 69585 │
│ langchain-by-johnsnowlabs│ langchain-ai/langchain │ 565 │ 69585 │
│ langchain-core │ langchain-ai/langchain │ 2440921 │ 69585 │
│ gigachain-core │ langchain-ai/langchain │ 4181 │ 69585 │
│ langchain-community │ langchain-ai/langchain │ 1438159 │ 69585 │
│ gigachain-community │ langchain-ai/langchain │ 1914 │ 69585 │
│ yt-dlp-custom │ yt-dlp/yt-dlp │ 948 │ 60914 │
│ yt-dlp │ yt-dlp/yt-dlp │ 86175495 │ 60914 │
│ keras │ keras-team/keras │ 374424308 │ 59836 │
│ keras-nightly │ keras-team/keras │ 20349029 │ 59836 │
│ symai-whisper │ openai/whisper │ 790 │ 51217 │
│ test10101010101 │ openai/whisper │ 46 │ 51217 │
│ whisper-openai │ openai/whisper │ 11486 │ 51217 │
│ openai-whisper │ openai/whisper │ 2029106 │ 51217 │
│ localstack │ localstack/localstack │ 3998353 │ 50301 │
└──────────────────────────┴──────────────────────────┴───────────┴────────┘
20 rows in set. Elapsed: 3.704 sec. Processed 12.28 million rows, 950.66 MB (3.31 million rows/s., 256.66 MB/s.)
Peak memory usage: 339.80 MiB.
huggingface/transformers、langchain-ai/langchain 和 openai/whisper 重复了好几次。这是因为有不同的 PyPi 项目使用相同的 GitHub 仓库作为其主页。其中一些看起来确实是不同的项目，但另一些似乎是主项目的废弃分支。
此查询需要将近 4 秒才能运行，因为 projects 和 pypi_downloads 表的连接结果正在流式传输到我的 ClickHouse Local 实例，然后才完成与 GitHub 数据的连接。我们可以在下面的图表中看到它是如何工作的
除了 keras 和 transformers 之外，大多数下载量大的包都没有出现在我们的列表中。我们可以通过更改 ORDER BY 子句来按下载量而不是星数排序，从而找出这些包有多少星数。我们需要更改以下行：\
ORDER BY stars DESC
改为
ORDER BY count DESC
如果我们运行带有该更改的查询，我们将看到以下输出
┌─projects.name──────┬─repository─────────────────┬───────count─┬─stars─┐
│ boto3 │ boto/boto3 │ 16031894410 │ 8440 │
│ botocore │ boto/botocore │ 11033306159 │ 1352 │
│ certifi │ certifi/python-certifi │ 8606959885 │ 707 │
│ s3transfer │ boto/s3transfer │ 8575775398 │ 189 │
│ python-dateutil │ dateutil/dateutil │ 8144178765 │ 2164 │
│ charset-normalizer │ Ousret/charset_normalizer │ 5891178066 │ 448 │
│ jmespath │ jmespath/jmespath.py │ 5405618311 │ 1975 │
│ pyasn1 │ pyasn1/pyasn1 │ 5378303214 │ 18 │
│ google-api-core │ googleapis/python-api-core │ 5022394699 │ 98 │
│ importlib-metadata │ python/importlib_metadata │ 4353215364 │ 101 │
└────────────────────┴────────────────────────────┴─────────────┴───────┘
10 rows in set. Elapsed: 3.957 sec. Processed 11.96 million rows, 941.07 MB (3.02 million rows/s., 237.81 MB/s.)
Peak memory usage: 336.19 MiB.
GitHub 上对这些项目中的大多数项目并没有太多的喜爱！该查询仍然需要 4 秒钟，但使用这个查询我们可以加速它，因为我们正在按远程表上的字段排序。这意味着我们可以限制远程连接返回的记录数，如下面的图表所示
让我们将记录数限制为 1,000，如下面的查询所示
WITH pypiProjects AS (
SELECT home_page, projects.name, sum(count) AS count
FROM remoteSecure(
'clickpy-clickhouse.clickhouse.com',
'pypi.pypi_downloads',
'mark', {password:String}
) AS pypi_downloads
INNER JOIN
(
SELECT name, argMax(home_page, version) AS home_page
FROM remoteSecure(
'clickpy-clickhouse.clickhouse.com',
'pypi.projects',
'mark', {password:String}
)
GROUP BY name
) AS projects ON projects.name = pypi_downloads.project
GROUP BY ALL
ORDER BY count DESC
LIMIT 1000
)
SELECT
name,
replaceOne(home_page, 'https://github.com/', '') AS repository,
count,
gh.stargazers_count AS stars
FROM pypiProjects
INNER JOIN
(
SELECT *
FROM file('data/*.json', JSONEachRow)
) AS gh ON gh.svn_url = pypiProjects.home_page
GROUP BY ALL
ORDER BY count DESC
LIMIT 10;
┌─name───────────────┬─repository─────────────────┬───────count─┬─stars─┐
│ boto3 │ boto/boto3 │ 16031894410 │ 8440 │
│ botocore │ boto/botocore │ 11033306159 │ 1352 │
│ certifi │ certifi/python-certifi │ 8606959885 │ 707 │
│ s3transfer │ boto/s3transfer │ 8575775398 │ 189 │
│ python-dateutil │ dateutil/dateutil │ 8144178765 │ 2164 │
│ charset-normalizer │ Ousret/charset_normalizer │ 5891178066 │ 448 │
│ jmespath │ jmespath/jmespath.py │ 5405618311 │ 1975 │
│ pyasn1 │ pyasn1/pyasn1 │ 5378303214 │ 18 │
│ google-api-core │ googleapis/python-api-core │ 5022394699 │ 98 │
│ importlib-metadata │ python/importlib_metadata │ 4353215364 │ 101 │
└────────────────────┴────────────────────────────┴─────────────┴───────┘
10 rows in set. Elapsed: 1.758 sec. Processed 2.08 thousand rows, 14.97 MB (1.18 thousand rows/s., 8.51 MB/s.)
Peak memory usage: 448.22 MiB.
这次只用了不到 2 秒，因为我们在与 GitHub 数据连接之前没有将那么多记录流式传输到 ClickHouse Local。然而，这不是一个完美的解决方案，因为如果我们的 1,000 条记录中有超过 990 条在 GitHub 数据集中没有匹配项，我们最终可能会得到少于 10 条记录。
总结
这就是目前为止的全部内容。我很想知道大家的想法？您是否看到了此功能的实际用例？如果是这样，请在评论或 ClickHouse Slack 上告知我们。
