DoubleCloud 即将停止运营。使用限时免费迁移服务迁移到 ClickHouse。立即联系我们。 ->->

博客 / 工程

使用 ClickHouse Cloud 查询端点在 10 分钟内为应用程序添加分析

Dale McDiarmid

2024 年 9 月 24 日

ClickHouse 的开发速度经常让新人感到惊喜，我们的发布周宣布了一系列新功能，使 ClickHouse 的使用比以往更加容易。作为一位热衷于在 ClickHouse 之上构建分析应用程序的人，这些新功能中的一项，API 端点，尤其引起了我的注意。在尝试使用该功能后，我意识到许多演示代码现在可以变得更加简单，同时也可以加快新功能的开发。

API 端点，已宣布进入测试版，允许您公开一个安全的 HTTP 端点，该端点会使用参数来填充和执行预定义的 SQL 查询。

API 端点不仅仅使界面更简单，它们还增加了关注点分离。除了使更新应用程序查询变得更简单（无需修改或重新部署代码），这还允许团队轻松公开分析，而无需编写 SQL 或直接与不同团队拥有的 ClickHouse 数据库进行交互。

为了演示这一点，我们更新了其中一个演示应用程序，ClickPy，在几分钟内添加了新的 GitHub 分析。我们希望这里学到的知识可以应用到您自己的 ClickHouse 应用程序中，使添加新功能变得更加容易。

为了配合这篇博客文章，我们还包含了一个小型食谱，其中包含了本文中添加的可视化效果的独立代码。

今年早些时候，我们宣布了 ClickPy - 一个简单的实时仪表板，允许用户查看 Python 包的下载统计信息。此应用程序由 PyPI 数据提供支持，其中包含每一行都代表一个 Python 包下载，它记录了曾经发生的每一次下载！每次运行 pip install, 我们都会在 ClickHouse 中获得一行数据！

该数据集现在超过万亿行，每天添加约 12 亿行，是使用 ClickHouse 执行大数据分析的完美示例。

该应用程序本身非常简单，基于 React、NextJs 和 Apache ECharts。如我们在开源存储库中所记录，其秘诀在于使用 ClickHouse 物化视图在插入时计算聚合，从而确保查询在毫秒内响应，用户获得快速且响应灵敏的体验。

许多 Python 包都是开源的，因此通常有它们自己的 GitHub 存储库。PyPi 数据通过 projects 表中的 homepage 和 project_urls 列捕获了这一点，例如，对于 clickhouse-connect（官方 ClickHouse Python 客户端）和 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.

用户经常用来尝试 ClickHouse 的另一个流行数据集是 GitHub 事件。它记录了 GitHub 上发生的每一项星标、问题、拉取请求、评论和 fork 事件，截至 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)

完整的模式以及有关加载此数据集和一些示例查询的详细信息，请参阅此处。我们使用一个简单的脚本每小时执行一次，以加载 GitHub 发布的新事件。我们的脚本与记录的说明不同，因为它还提取了 repo.id，这是我们希望计算的统计信息所必需的。我们的模式还通过首先指定 repo_name 来修改 ORDER BY 键，因为 ClickPy 将其作为过滤器强制执行。

这里的目标非常简单：如果用户正在查看的包托管在 GitHub 上，就在我们的 ClickPy 主分析页面中添加一些简单的指标。更具体地说，是星标、观看、问题和 PR 的数量。

目前我们保持简单。我们计划使用这些数据进一步丰富 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 逻辑。

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

假设我们将所有统计信息封装在一个端点中。用于计算星标、问题、观看和 PR 数量的查询

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 个参数，它们映射到 UI 过滤器：目标存储库名称（以字符串形式）、最小日期范围和最大日期范围。第一个 CTE 识别 homepage 或 project_urls 是否包含前缀为 https://github.com 的链接，以及该项目是否具有关联的 GitHub 存储库。使用 GitHub 项目路径构造一个存储库名称，并用它来标识存储库 ID。

使用存储库 ID 对于后续查询非常重要，因为存储库名称可能会发生变化。我们的统计信息是从主表 github.github_events 中计算得出的，使用以下条件

在 ClickHouse Cloud 中，这些参数会自动检测并公开为用户可以填充的文本框

要将此查询转换为端点，我们只需点击 共享 -> API 端点，保存查询并使用名称和 API 令牌创建 API 端点，该令牌具有“查询端点”权限。确保端点仅使用只读权限

请注意我们如何将“Play role”与端点关联。这是一个角色，确保此端点只能用于响应所需表的查询，并施加以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"}

细心的读者可能会注意到，我们传递了 url 参数"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 代码非常简单，下面列出了最相关的片段。更多好奇的读者可以在这里找到代码。

// 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 统计信息。为了呈现有关包的一些详细信息以及 Github 统计信息，我们的查询从项目表中返回列。

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"}}'

此应用程序的源代码可以在这里找到。

上面的示例在服务器端执行端点调用，以保持示例简单。虽然用户可以在客户端安全地公开 API 凭据，但应谨慎操作。具体来说

确保端点使用分配了“查询端点”权限的 API 令牌，以避免泄露具有更广泛权限（例如，创建服务）的凭据，而这些凭据超出了所需权限。
至少，确保分配了只读角色。如果将端点用于内部项目，这可能就足够了。对于外部项目，我们建议创建一个专用角色，并确保分配了配额，就像我们之前示例中所做的那样。这些配额可以以 IP 为键，因此管理员可以限制用户在单位时间内可以执行的查询数量，有效地创建速率限制。例如，我们用于演示应用程序的公共端点使用“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*

具有完整权限的示例角色可以在这里找到。

在创建端点时配置 CORs 的“允许域”，将其限制为托管应用程序的域。

虽然我们使用现有的应用程序来演示端点，并在几分钟内添加了新功能，但用户可以使用相同的特性快速原型化并构建自己的应用程序。我们还提供了一个简单的示例，供用户重新创建 Github 统计信息可视化效果作为应用程序。

立即开始使用 ClickHouse Cloud，并获得 300 美元的积分。在 30 天试用期结束后，继续使用按需付费计划，或者联系我们，详细了解我们的基于容量的折扣。访问我们的价格页面以了解更多信息。

分享这篇文章

订阅我们的时事通讯

随时了解功能发布、产品路线图、支持和云产品！

正在加载表单...

博客 / 工程

使用 ClickHouse Cloud 查询端点在 10 分钟内为应用程序添加分析

简介

什么是 ClickPy？

添加新的数据集

更简洁的方法

进入 API 端点

将它们组合在一起

完整示例

推荐用法

结论

订阅我们的时事通讯

最近的文章