DoubleCloud 即将停止服务。迁移到 ClickHouse，享受限时免费迁移服务。立即联系我们 ->->
ClickHouse logo
登录

博客 / 工程

混合查询执行实验

author avatar
Mark Needham
2024年1月31日

在我了解 ClickHouse 的大部分时间里，我理解它是一个具有传统客户端-服务器架构的分析数据库，旨在以低延迟服务大量并发查询。

直到最近几个月，我才了解到几个改变了我理解的工具：ClickHouse Local，它允许我们通过 CLI 运行 ClickHouse 前端的进程内版本；以及 chDB，一个由 ClickHouse 提供支持的嵌入式 SQL OLAP 引擎。

我发现自己一直在为 ClickHouse YouTube 频道 创建的大多数视频中使用了这些工具的混合。

一个最值得注意的例外是 解释物化视图 的视频，该视频在 ClickPy 的帮助下完成。ClickPy 是一个托管在 ClickHouse Cloud 上的 ClickHouse 服务，其中包含 Python 的 PyPi 包管理器中包下载的数据。该数据集帮助我们了解随着时间推移，按国家/地区、安装程序、版本和许多其他维度分组的最常下载的包。

数据库还包含有关每个包的元数据，包括项目主页，通常是 GitHub 存储库。这让我想到，将这些库的 GitHub 指标（例如星数或分叉数）与下载次数进行比较会很有趣。

我问我的同事 Dale 我们是否可以在 ClickPy 服务器上添加 GitHub 数据，他建议我首先查看 ClickHouse 的 远程和远程安全表函数。这些函数允许您从另一个 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.

让我们看一下显示不同数据位所在位置的图表。

Hybrid Query Execution Diagram.png

配置远程查询权限

接下来我想做的是将查找 PyPi 项目的查询与返回 GitHub 指标的查询结合起来。主要挑战在于 PyPi 数据位于 Clickhouse Cloud 上，而 GitHub 指标位于我的机器上。

我不想用我的 GitHub 数据污染 ClickHouse Cloud 实例，因此我将使用 远程安全 表函数从我的机器查询 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

现在，我们将运行上述查询的一个版本，该版本使用 远程安全 函数查找最受欢迎的 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 表连接的结果在与 GitHub 数据连接之前被流式传输到我的 ClickHouse Local 实例。我们可以在下面的图表中看到它是如何工作的

Hybrid Query Execution Diagram (2).png

除了 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 秒，但通过这个项目，我们可以加快速度，因为我们正在根据远程表上的字段进行排序。这意味着我们可以限制远程联接返回的记录数量，如下面的图表所示。

Hybrid Query Execution Diagram (1).png

让我们将记录数量限制为 1000，如下面的查询所示。

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。然而，这不是一个完美的解决方案，因为如果我们 1000 条记录中的 990 多条在 GitHub 数据集中没有匹配项，那么我们最终可能得到的记录少于 10 条。

总结

暂时就这样了。我很想知道大家的想法？您能看到此功能的实际用例吗？如果是，请在评论中或在 ClickHouse Slack 上告诉我们。

分享此文章

订阅我们的时事通讯

随时了解功能发布、产品路线图、支持和云产品！
正在加载表单...

近期文章

2024 年 10 月时事通讯
社区
2024 年 10 月时事通讯
ClickHouse 团队 · 2024 年 10 月 17 日
使用 ClickHouse 和 HTTP 构建单页应用程序
工程
使用 ClickHouse 构建单页应用程序
Dale McDiarmid · 2024 年 10 月 10 日
ClickHouse 中的语义版本控制 UDF
工程
ClickHouse 中的语义版本控制 UDF
Juan S. Carrillo · 2024 年 10 月 9 日
关注我们
Twitter imageSlack imageGitHub image
Telegram imageMeetup imageRss image
产品
资源
公司
加入我们的社区
对比
ClickHouse logo
随时了解功能发布、产品路线图、支持和云产品！
正在加载表单...
©2024ClickHouse, Inc. 总部位于加州湾区和荷兰阿姆斯特丹。
商标隐私安全法律Cookie 政策