在 10 分钟内通过 ClickHouse Cloud 查询端点给应用程序增加数据分析功能

本文字数：10769；估计阅读时间：27 分钟

作者：Dale McDiarmid

本文在公众号【ClickHouseInc】首发

引言

ClickHouse 的开发速度常常令新用户感到惊喜，最近的发布周中，我们推出了许多新功能，使得使用 ClickHouse 变得更加简单。作为一个热衷于在 ClickHouse 上构建分析应用程序的人，这些新功能中，我特别关注了 API 端点。在尝试这一功能后，我发现许多演示代码可以显著简化，同时新功能的开发也变得更快速。

API 端点（目前处于测试阶段）允许您创建一个安全的 HTTP 端点，它可以接收参数并使用这些参数填充和执行预定义的 SQL 查询。

API 端点不仅简化了接口，还实现了关注点的分离。这意味着，更新应用程序查询时无需修改或重新部署代码，团队可以轻松暴露分析功能，而无需编写 SQL 或直接与其他团队管理的 ClickHouse 数据库交互。

为了演示这一点，我们更新了演示应用程序 ClickPy，在几分钟内添加了新的 GitHub 分析功能。我们希望这些经验能够帮助您更轻松地在自己的 ClickHouse 应用程序中添加新功能。

为配合本博客，我们还附上了一套示例代码，包含了博客中提到的可视化功能的独立代码。

什么是 ClickPy？

今年早些时候，我们推出了 ClickPy——一个简单的实时仪表板，用户可以查看 Python 包的下载统计信息。这个应用程序基于 PyPI 数据，每发生一次 Python 包下载就会在 ClickHouse 中生成一行记录！每次您运行 pip install，ClickHouse 就会收到一行数据！

目前，这个数据集已经超过了一万亿行，且每天增加约 12 亿行，完美展示了如何利用 ClickHouse 进行大数据分析。

ClickPy 应用程序本身相对简单，使用了 React、NextJs 和 Apache ECharts。我们在开源代码库中详细记录了它的秘诀：通过使用 ClickHouse 的物化视图在插入时计算聚合，确保查询能在毫秒级内响应，从而提供快速且流畅的用户体验。

添加新数据集

许多 Python 包是开源的，通常会有自己的 GitHub 仓库。PyPi 数据通过项目表中的 homepage 和 project_urls 列捕捉这些信息，例如 ClickHouse 的官方 Python 客户端 clickhouse-connect 和 boto3 库。

SELECT
  name,
  argMax(home_page, upload_time) AS home_page,
  argMax(project_urls, upload_time) AS project_urls
FROM pypi.projects
WHERE name IN ('clickhouse-connect', 'boto3')
GROUP BY name
FORMAT Vertical

Row 1:
──────
name:       boto3
home_page:  https://github.com/boto/boto3
project_urls: ['Documentation, https://boto3.amazonaws.com/v1/documentation/api/latest/index.html','Source, https://github.com/boto/boto3']

Row 2:
──────
name:       clickhouse-connect
home_page:  https://github.com/ClickHouse/clickhouse-connect
project_urls: []

2 rows in set. Elapsed: 0.018 sec. Processed 27.48 thousand rows, 2.94 MB (1.57 million rows/s., 167.52 MB/s.)
Peak memory usage: 26.51 MiB.

用户常用的另一个数据集是 GitHub 事件，它捕获了 GitHub 上的每个星标、问题、拉取请求、评论和分叉事件，截至 2024 年 6 月，约有 77.5 亿个事件。该数据集由 GitHub 提供，并每小时更新，成为我们 PyPi 数据集的完美补充。

CREATE TABLE github.github_events
(
  `file_time` DateTime,
  `event_type` Enum8('CommitCommentEvent' = 1, 'CreateEvent' = 2, 'DeleteEvent' = 3, 'ForkEvent' = 4, 'GollumEvent' = 5, 'IssueCommentEvent' = 6, 'IssuesEvent' = 7, 'MemberEvent' = 8, 'PublicEvent' = 9, 'PullRequestEvent' = 10, 'PullRequestReviewCommentEvent' = 11, 'PushEvent' = 12, 'ReleaseEvent' = 13, 'SponsorshipEvent' = 14, 'WatchEvent' = 15, 'GistEvent' = 16, 'FollowEvent' = 17, 'DownloadEvent' = 18, 'PullRequestReviewEvent' = 19, 'ForkApplyEvent' = 20, 'Event' = 21, 'TeamAddEvent' = 22),
  `actor_login` LowCardinality(String),
  `repo_name` LowCardinality(String),
  `repo_id` LowCardinality(String),
  `created_at` DateTime,
  `updated_at` DateTime,
  `action` Enum8('none' = 0, 'created' = 1, 'added' = 2, 'edited' = 3, 'deleted' = 4, 'opened' = 5, 'closed' = 6, 'reopened' = 7, 'assigned' = 8, 'unassigned' = 9, 'labeled' = 10, 'unlabeled' = 11, 'review_requested' = 12, 'review_request_removed' = 13, 'synchronize' = 14, 'started' = 15, 'published' = 16, 'update' = 17, 'create' = 18, 'fork' = 19, 'merged' = 20),
  `number` UInt32,
  … // columns omitted for brevity
)
ENGINE = MergeTree
ORDER BY (repo_id, event_type, created_at)

有关如何加载此数据集的完整架构和示例查询，可以在此处找到【https://ghe.clickhouse.tech/】。我们使用一个简单的脚本，每小时执行一次，以加载 GitHub 发布的新事件。我们的脚本与文档中的说明有所不同，它还提取了 repo.id，以满足我们计算统计信息的需要。我们的架构还调整了 ORDER BY 键，首先指定 repo_name，因为 ClickPy 要求将其作为过滤条件。

目标很简单：如果用户查看的包托管在 GitHub 上，则在我们的主要 ClickPy 分析页面中添加一些简单指标，比如星标、关注、问题和拉取请求的数量。

目前我们保持简单，未来会进一步丰富 ClickPy 的数据，并添加更具吸引力的可视化效果。敬请期待。

更清晰的方法

之前，ClickPy 中的每个可视化都是通过 SQL 查询生成的。大多数可视化都有类似以下的功能：

export async function getDownloadsOverTime({package_name, version, period, min_date, max_date, country_code, type}) {
  const columns = ['project', 'date']
  if (version) {  columns.push('version') }
  if (country_code) { columns.push('country_code') }
  if (type) { columns.push('type')}
  const table = findOptimalTable(columns)
  return query('getDownloadsOverTime',
       `SELECT
        toStartOf${period}(date)::Date32 AS x,
        sum(count) AS y
  FROM ${PYPI_DATABASE}.${table}
  WHERE (date >= {min_date:String}::Date32) AND (date < {max_date:String}::Date32) AND (project = {package_name:String})
  AND ${version ? `version={version:String}`: '1=1'} AND ${country_code ? `country_code={country_code:String}`: '1=1'} AND ${type ? `type={type:String}`: '1=1'} GROUP BY x
  ORDER BY x ASC`, 
       {
        package_name: package_name,
        version: version,
        min_date: min_date,
        max_date: max_date,
        country_code: country_code,
        type: type,
  })
}

这个功能驱动了每日下载量的折线图，并在服务器端执行。这样做有些繁琐，导致需要维护的大型查询文件。

理想情况下，这应该是一个简单的 HTTP 调用，仅传递参数，由独立的 API 层来维护所有的 SQL 逻辑。

进入 API 端点

在 ClickHouse Cloud 中，任何 SQL 查询都可以通过几个简单的步骤转换为 API 端点，系统会自动检测 SQL 参数并将其转换为 POST 参数。

假设我们将所有统计信息封装在一个端点中，查询代码如下，用于计算星标、问题、关注和拉取请求的数量：

SET param_min_date = '2011-01-01'
SET param_max_date = '2024-06-06'
SET param_project_name = 'clickhouse-connect'

WITH
   (
      SELECT regexpExtract(arrayFilter(l -> (l LIKE '%https://github.com/%'), arrayConcat(project_urls, [home_page]))[1], '.*https://github.com/(.*)')
      FROM pypi.projects
      WHERE name = {package_name:String} AND length(arrayFilter(l -> (l LIKE '%https://github.com/%'), arrayConcat(project_urls, [home_page]))) >= 1
      ORDER BY upload_time DESC
      LIMIT 1
   ) AS repo,
   id AS (
     SELECT repo_id
     FROM github.github_events
     WHERE (repo_name = repo) LIMIT 1
   )
SELECT
   uniqExactIf(actor_login, (event_type = 'WatchEvent') AND (action = 'started')) AS stars,
   uniqExactIf(number, event_type = 'IssuesEvent') AS issues,
   uniqExactIf(actor_login, event_type = 'ForkEvent') AS forks,
   uniqExactIf(number, event_type = 'PullRequestEvent') AS prs
FROM github.github_events
WHERE (repo_id IN id) AND (created_at > {min_date:Date32}) AND (created_at <= {max_date:Date32})

该查询接受 3 个参数，分别对应用户界面的过滤器：关注的仓库名称（字符串）以及最小和最大日期范围。第一个公共表表达式 (CTE) 用于检查 homepage 或 project_urls 是否包含以 https://github.com 为前缀的链接，以此判断项目是否与 GitHub 仓库相关联。通过 GitHub 项目路径构建仓库名称，并据此获取仓库 ID。

使用仓库 ID 对后续查询非常重要，因为仓库名称可能会变化。我们的统计信息是从主表 github.github_events 计算得出的，使用条件语句。

在 ClickHouse Cloud 中，这些参数会被自动检测，并以文本框形式呈现，用户可以填写：

要将此查询转换为 API 端点，我们只需点击共享” -> “API 端点，为查询命名并创建一个用于“查询端点”权限的 API 令牌。确保该端点仅使用只读权限：

请注意，我们将“播放角色”与端点关联。此角色确保端点只能用于所需表的查询，并对每个 IP 地址施加配额，从而限制单个用户的请求次数。希望从浏览器调用端点的用户，还可以配置允许的域的 CORS 头。默认的“只读”角色提供了一个更简单的入门方式。

这为我们提供了一个 HTTP 端点，我们可以使用 curl 执行，响应结果将以 JSON 格式返回：

curl -H "Content-Type: application/json" -X 'POST' -s --user '<key_id>:<key_secret>' 'https://console-api.clickhouse.cloud/.api/query-endpoints/9001b12a-88d0-4b14-acc3-37cc28d7e5f4/run?format=JSONEachRow' --data-raw '{"queryVariables":{"project_name":"boto3","min_date":"2011-01-01","max_date":"2024-06-06"}}'

{"stars":"47739","issues":"3009","forks":"11550","prs":"1657"}

机智的读者可能会注意到，我们传递了参数 "format":"JSONEachRow" 来控制输出格式。用户可以在此处选择 ClickHouse 支持的 70 多种输出格式，例如 CSVWithNames：

curl -H "Content-Type: application/json" -X 'POST' -s --user '<key_id>:<key_secret>' 'https://console-api.clickhouse.cloud/.api/query-endpoints/9001b12a-88d0-4b14-acc3-37cc28d7e5f4/run?format=CSVWithNames' --data-raw '{"queryVariables":{"project_name":"boto3","min_date":"2011-01-01","max_date":"2024-06-06"}}'

"stars","issues","forks","prs"
47739,3009,11550,1657

整合起来

以上步骤让我们只需构建可视化，并整合之前提到的 API 端点。

React 组件的代码相对简单，下面是一些关键的代码片段。如果您想深入了解，可以在这里找到完整代码。【https://github.com/ClickHouse/clickpy/blob/main/src/components/GithubStats.jsx】

// main panel containing stats
export default async function GithubStats({ repo_name, min_date, max_date }) {
  const stats = await getGithubStats(repo_name, min_date, max_date);
  return stats.length > 0 ? (
   <div className="flex h-full gap-4 flex-row flex-wrap xl:flex-nowrap">
    <div className="flex gap-4 w-full sm:flex-row flex-col">
      <SimpleStat value={stats[0]} subtitle={"# Github stars"} logo={"/stars.svg"} />
      <SimpleStat value={stats[1]} subtitle={"# Pull requests"} logo={"/prs.svg"} />
    </div>
    <div className="flex gap-4 w-full sm:flex-row flex-col">
      <SimpleStat value={stats[2]} subtitle={"# Issues"} logo={"/issues.svg"}/>
      <SimpleStat value={stats[3]} subtitle={"# Forks"} logo={"/fork.svg"} />
    </div>
   </div>
  ) : null;
}

// a single state component
export default function SimpleStat({ value, subtitle, logo }) {
 return (
   <div className="min-w-[250px] rounded-lg bg-slate-850 flex gap-4 p-4 h-24  w-full min-w-72 border border-slate-700">
     <div className="items-center flex grow">
       <Image
         width={16}
         height={16}
         className="h-16 w-16 min-w-16 min-h-16 bg-neutral-850 rounded-lg"
         src={logo}
         alt={subtitle}
      />
       <div className="ml-2 mr-4">
         <p className="text-xl mr-2 font-bold">{value}</p>
         <p className="text-slate-200">{subtitle}</p>
       </div>
     </div>
   </div>
 );
}

这段代码调用了 getGithubStats 函数，该函数又会调用通用的 runAPIEndpoint 函数，并传入相应的端点及参数：

export async function runAPIEndpoint(endpoint, params) {
  const data = {
      queryVariables: params,
      format: 'JSONEachRow'
    };    
    const response = await fetch(endpoint, {
      method: 'POST',
      headers: {
        'Content-Type': 'application/json',
        'Authorization': `Basic ${btoa(`${process.env.API_KEY_ID}:${process.env.API_KEY_SECRET}`)}`
      },
      body: JSON.stringify(data)
    })
    return response.json()
}

export async function getGithubStats(package_name, min_date, max_date) {
  return runAPIEndpoint(process.env.GITHUB_STATS_API, {
        package_name: package_name,
        min_date: min_date,
        max_date: max_date
  })
}

至此，我们完成了整个集成过程！

完整示例

虽然 ClickPy 的源代码可以在 GitHub 上找到，用户可能更希望尝试一个简单的示例。为此，我们准备了一个简化版应用，用户可以输入 Python 包并查看其 GitHub 统计数据。我们从 projects 表中查询所需的列：

SET param_package_name='boto3'

WITH
  (
        SELECT version
        FROM pypi.projects
        WHERE name = {package_name:String}
        ORDER BY arrayMap(x -> toUInt8OrDefault(x, 0), splitByChar('.', version)) DESC
        LIMIT 1
  ) AS max_version,
  project_details AS (
     SELECT
        name,
        max_version,
        summary,
        author,
        author_email,
        license,
        home_page,
        trim(TRAILING '/' FROM regexpExtract(arrayFilter(l -> (l LIKE '%https://github.com/%'), arrayConcat(project_urls, [home_page]))[1], '.*https://github.com/(.*)')) AS github
     FROM pypi.projects
     WHERE (name = {package_name:String})
     ORDER BY upload_time DESC
     LIMIT 1
  ),
  id AS (
      SELECT repo_id
      FROM github.repo_name_to_id
      WHERE repo_name IN (SELECT github FROM project_details) LIMIT 1
  ),
  stats AS (
     SELECT
        uniqExactIf(actor_login, (event_type = 'WatchEvent') AND (action = 'started')) AS stars,
        uniqExactIf(number, event_type = 'IssuesEvent') AS issues,
        uniqExactIf(actor_login, event_type = 'ForkEvent') AS forks,
        uniqExactIf(number, event_type = 'PullRequestEvent') AS prs
     FROM github.github_events_v2
     WHERE (repo_id IN id)
  )
 SELECT * FROM project_details, stats FORMAT Vertical

Row 1:
──────
name:       requests
max_version:  2.32.3
summary:    Python HTTP for Humans.
author:     Kenneth Reitz
author_email: me@kennethreitz.org
license:    Apache-2.0
home_page:  https://requests.readthedocs.io
github:     psf/requests
stars:      22032
issues:     1733
forks:      5150
prs:        1026

1 row in set. Elapsed: 0.472 sec. Processed 195.71 million rows, 394.59 MB (414.49 million rows/s., 835.71 MB/s.)
Peak memory usage: 723.12 MiB.

这些数据用以渲染一些基本统计信息。

curl -H "Content-Type: application/json" -X 'POST' -s --user 'MdhWYPEpXaqiwGMjbXWT:4b1dKbabyQTvuKUWOnI08oXVbUD4tkaxKKjEwz7ORG' 'https://console-api.clickhouse.cloud/.api/query-endpoints/297797b1-c5b0-4741-9f5b-3d6456a9860d/run?format=JSONEachRow' --data-raw '{"queryVariables":{"package_name":"requests"}}'

该应用的源代码可以在这里找到。【https://github.com/ClickHouse/gitstats_demo】

推荐用法

上述示例在服务器端执行 API 调用，保持了简单性。虽然用户可以在客户端公开 API 凭证，但需谨慎行事。具体建议如下：

1. 确保端点使用分配了“查询端点”权限的 API 令牌，以避免泄露其他不必要的权限。

2. 至少应分配只读角色。

   对于内部项目，这可能足够；

   对于外部项目，建议创建专用角色并分配配额，确保控制用户的查询频率。

   例如，我们演示应用的公共端点使用了“endpoint_role”及相关配额。

CREATE QUOTA endpoint_quota KEYED BY ip_address FOR INTERVAL 1 hour MAX queries = 100, result_rows = 1, read_rows = 3000000000000, execution_time = 6000 TO endpoint_role*

一个包含示例角色完整权限的代码在这里：https://pastila.nl/?002ccf51/917e9d67da452079cd08e9e8b65afe5f#ojEQ0evgldNjSzkT8jk9ZA==：

3. 创建端点时，请配置 CORS 的“允许域”，限制为托管您应用的域。

结论

虽然我们使用现有应用来演示端点的功能，添加新功能仅需几分钟，但用户也可以利用相同的特性快速原型和构建自己的应用。我们还提供了一个简单示例，供用户重建 GitHub 统计可视化应用。

征稿启示

面向社区长期正文，文章内容包括但不限于关于 ClickHouse 的技术研究、项目实践和创新做法等。建议行文风格干货输出&图文并茂。质量合格的文章将会发布在本公众号，优秀者也有机会推荐到 ClickHouse 官网。请将文章稿件的 WORD 版本发邮件至：Tracy.Wang@clickhouse.com