如何执行混合查询？

本文字数：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

满足您所有的在线分析列式数据库管理需求