本文字数：9866；估计阅读时间：25 分钟
作者：Mark Needham
审校：庄晓东（魏庄）
本文在公众号【ClickHouseInc】首发
在我意识到：人们用ClickHouse的大部分时间里，我们理解它是一个带有传统客户端-服务器架构的分析数据库，旨在以低延迟为高并发查询提供服务。
直到最近几个月，我才被这样几个工具改变了我的理解：ClickHouse Local，它允许我们通过CLI运行ClickHouse前端的进程内版本，以及chDB，一个由ClickHouse驱动的嵌入式SQL OLAP引擎。
在为ClickHouse YouTube频道录制的大多数视频中，我发现自己在混合使用这些工具。
最值得注意的例外是那段视频，其中通过ClickPy解释了Materialized Views。ClickPy是一个托管在ClickHouse Cloud上的ClickHouse服务，包含有关从Python的PyPi包管理器下载软件包的数据。该数据集帮助我们了解长期以来最受欢迎的软件包，按国家、安装程序、版本和许多其他维度分组。
数据库还包含有关每个软件包的元数据，包括项目主页，通常是GitHub代码库。这让我想到，将这些库的GitHub指标（例如星标数或分叉数）与下载数进行比较可能会很有趣。
我询问了我的同事戴尔，是否可以将GitHub数据添加到ClickPy服务器上，他建议我首先查看ClickHouse的remote和remoteSecure表函数。这些函数允许您从另一个ClickHouse客户端动态查询远程ClickHouse服务器。我们还可以将远程查询的数据与本地ClickHouse中的数据进行连接（join），当与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.我想看到在GitHub上受欢迎的生成式AI应用程序中使用的许多库，这并不太令人惊讶。
在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来检查连接是否是远程完成的，这将返回以下内容：
┌─explain───────────────────────────────────┐
│ ReadFromRemote (Read from remote replica) │
└───────────────────────────────────────────┘如果JOIN是在本地执行的，我们将在查询计划中看到Join操作符。
将来自ClickHouse Cloud和ClickHouse Local的数据进行连接（Join）
接下来，让我们将这些数据与本地GitHub数据集进行连接（join）：
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秒，因为在执行与GitHub数据的连接之前，正在将projects和pypi_downloads表的连接结果流式传输到我的ClickHouse Local实例。我们可以看到下面的图表显示了这是如何工作的：
除了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上告诉我们。
联系我们
手机号：13910395701
邮箱：Tracy.Wang@clickhouse.com
满足您所有的在线分析列式数据库管理需求
38
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
