使用 ClickHouse 将实时分析添加到 Supabase 应用程序

在 ClickHouse，我们经常被问到 ClickHouse 与 Postgres 的比较以及 ClickHouse 应该用于哪些工作负载。随着我们与 Supabase 的朋友推出其 Postgres 产品的外键数据包装器 (FDW)，我们决定借此机会在上周初的网络研讨会上重新探讨这个话题。除了解释 OLTP 数据库（例如 Postgres）和 OLAP 数据库（例如 ClickHouse）之间的差异之外，我们还探讨了在应用程序中何时应该使用每个数据库。还有什么比使用两种功能的演示来表达这些想法更好的方法呢？

在这篇博文中，我们将展示如何使用 Supabase 的 FDW，通过 ClickHouse 增强了使用 Supabase 构建的应用程序的实时分析功能。这展示了用户如何从 Supabase 实例查询 ClickHouse，从而将实时分析集成到应用程序中，而无需离开 Supabase 生态系统及其熟悉的界面。我们提醒用户 OLAP 和 OLTP 之间的区别，以及在构建应用程序时何时选择最合适的选择。最后，我们将讨论一些 FDW 最佳实践以及在需要更新分析时将事务数据推送到 ClickHouse 的一些选项。

我们已将演示的代码提供。请原谅任何粗糙的地方，仅供参考。

注意：Supabase 的 Clickhouse 包装器目前处于 Alpha 阶段，本文中的一些功能可能无法在您的 Supabase 项目中使用。如果您需要访问权限，请联系[email protected]: Supabase 的 Clickhouse 包装器目前处于 Alpha 阶段，本文中的一些功能可能无法在您的 Supabase 项目中使用。如果您需要访问权限，请联系[email protected].

OLTP（在线事务处理）数据库旨在管理事务信息。

这些数据库的主要目标是确保工程师可以向数据库提交一批更新，并确保这些更新将全部成功或全部失败。

为了展示其用途，想象一下您是一家银行，您希望记录从一个帐户（来源）到另一个帐户（目的地）的 10 美元电汇。完成此操作需要两个步骤

1. 从来源帐户余额中扣除 10 美元
2. 向目的地帐户余额中添加 10 美元

这两个更新必须一起发生或完全不发生。否则，银行可能会处于 10 美元无法核对的状态！（如果步骤 1 成功，步骤 2 失败）。

这些类型的事务保证是 OLTP 数据库的主要关注点。

鉴于这些要求，OLTP 数据库在用于分析大型数据集的分析查询时通常会遇到性能限制。

OLAP（联机分析处理）数据库旨在满足这些需求——管理分析工作负载。

这些数据库的主要目标是确保工程师可以有效地查询和聚合庞大的数据集。像 ClickHouse 这样的实时 OLAP 系统允许在实时摄取数据时进行这种分析。

当查询旨在检索特定行集，可能访问更多列，并且数据经常需要实时更新时，OLTP 数据库表现出色。通过针对高速事务和并发性进行优化，OLTP 数据库允许在不发生冲突的情况下同时访问和修改数据。这些访问模式使 OLTP 数据库成为存储维护应用程序状态的数据并支持用户交互的理想选择。Postgres 在这些工作负载下即使在高查询并发的情况下也能快速响应的能力，使其成为应用程序的首选 OLTP 数据库，为从电子商务和金融交易平台到客户服务系统等各种用例提供支持。

相反，像 ClickHouse 这样的 OLAP 数据库在分析查询方面表现出色，这些查询访问大量行但访问的列很少。这些查询通常使用 GROUP BY 操作和分析函数对数十亿（如果不是万亿）行进行汇总，这些函数作用于多个非常大的表格上。这些工作负载通常与更大的数据集相关联，这些数据集需要不到 50 毫秒的查询时间。为了提供这种高性能，OLAP 数据库通常会牺牲某些功能，例如事务、对有限查询并发的支持、更新支持，并要求用户批量进行插入操作。

虽然 ClickHouse 可以归类为 OLAP 数据库，但它还支持实时分析工作负载，在这种工作负载中，查询并发通常要高得多。这使得 ClickHouse 非常适合将分析添加到您的应用程序中！

为了帮助用户理解这些原理，我们决定构建一个应用程序，分别利用每个数据库。对于任何好的演示，我们都需要一个与现实世界用例相关的dataset。英国房价dataset，尽管规模适中，大约有 3000 万行，但对在线列出其房产的房地产公司来说可能有用。此 dataset 包含自 1990 年以来英国出售的每一栋房子的行。如果我们可以使用此 dataset 作为生成待售房源的基础，但以分析的形式公开整个 dataset 以帮助用户在查看房产时做出决策，会怎么样？我们永远不会错过一个好双关语的机会，HouseClick 诞生了……“最快买房卖房的地方”，很明显。

房地产公司的用户数据访问模式与电子商务网站非常相似——它需要列出产品，提供丰富的搜索功能，以及能够通过 id 检索特定产品以进行详细查看。为此，OLTP 数据库是完美的。由于我们熟悉 Postgres 并且不想自己托管数据库，Supabase 是我们应用程序数据的完美解决方案——特别是我们当前待售的房产。

Supabase 提供了一个实时数据库，允许开发人员实时存储和同步跨多个设备的数据。简单来说，它是 OSS Firebase 的替代方案。它还提供各种后端服务，包括用于运行函数和托管静态资产的无服务器平台。

借助一系列丰富的客户端，这些客户端无需用户编写 SQL（解决了 SQL 注入问题），以及行级安全以限制匿名用户访问读取权限，这为存储我们当前大约 1000 套待售房产提供了完美的解决方案。

将历史数据加载到 ClickHouse Cloud 进行分析后，我们只需要选择一个 Web 框架。我对 React 有基本了解，不想花太多时间研究可能的技术栈，因此我采纳了 ClickHouse 中那些从事 Web 开发，而不是创建假房地产公司的人的建议——NextJS 结合 Tailwinds 似乎是普遍推荐的方案。考虑到分配了三天时间，我需要找到一些真正的待售房产……

虽然历史房价数据集提供了一些关于地址、价格和房屋出售日期的基本信息，但它缺乏我们构建一个丰富且引人入胜的房地产网站所需的信息——缺少标题、描述和图像。

SELECT *
FROM uk_price_paid
LIMIT 1
FORMAT Vertical

Row 1:
──────
price: 	1
date:  	1998-06-22 00:00:00
postcode1: CW11
postcode2: 1GS
type:  	detached
is_new:	0
duration:  leasehold
addr1: 	15
addr2:
street:	PENDA WAY
locality:
town:  	SANDBACH
district:  CHESHIRE EAST
county:	CHESHIRE EAST

1 row in set. Elapsed: 0.022 sec. Processed 57.34 thousand rows, 4.31 MB (2.64 million rows/s., 198.59 MB/s.)

我们选择了 1000 个随机的房产，并根据其原始出售日期和价格，使用其所在邮政编码的房产类型价格增长情况，预测了 2023 年的估值——并添加了一些偏差，以确保有些房屋看起来比其他房屋更划算。

使用该价格和房产面积，我们尝试预测房屋大小，即卧室数量，使用了一些 非常粗略的启发式算法——不得不承认，这太简单了，后来产生了一些有趣的结果，尤其是在与图像结合之后。

对于描述、标题和可能的房屋特征列表，我们转向了 OpenAI 的 文本完成 API 和 ChatGPT-3 text-davinci-003 模型。这对于所有 1000 个房产来说花费了大约 10 美元。

对我们的人工智能房地产经纪人感到满意后，我们只需要图像。虽然人工智能生成的图像可行，但使用 DALL-E 模型 倾向于生成相当不引人入胜的图像。

有了我们的标题和描述，Bing 图像 API 生成了更吸引人的图像，用于演示。再加上相当乐观的定价，这种相当粗略的方法不可否认地产生了一些有趣的结果。

我们不需要出售这些房产，并梦想着以 460,000 英镑的价格购买温莎的一栋 24 间卧室的房子，我们将以上技术结合到一个脚本中，生成了 1000 个房产，并将它们保存为 CSV 文件。

除了省去了部署和管理 Postgres 的麻烦之外，Supabase 还提供了一些功能，这些功能显著地加快了开发速度。

• 允许用户应用访问策略，只允许使用匿名令牌的用户进行读取访问。这意味着对于像搜索这样的客户端渲染页面，我们可以安全地从浏览器查询数据库——避免需要编写服务器端 API 端点。
• 一个简单的數據加載 API，它允许我们零代码加载我们的房源列表（我们也编写了一个 方便脚本，但它让我们快速开始了）。
• 使用 Postgres 索引 的全文搜索功能，避免了使用专门的搜索引擎，例如 Elasticsearch。
• 一个丰富的 JavaScript 客户端，使用方法链使构建 SQL 查询变得容易。这在将过滤器和排序组合到简单的搜索 UI 中时尤其有用，例如：

const { data, error } = await supabase.from(table).select('id,type,price,town,district,postcode1,postcode2,duration,urls,description,title,rooms,sold,date').order('date', {ascending: false}).limit(4)

与我们当前的房源列表不同，我们的历史价格数据是基于英国真实的房屋销售数据。将这些数据加载到 ClickHouse 只需要两条命令，如 我们的文档中所述。

加载完数据集后，我们需要选择一些对购房者有用的分析。在查看特定房产时，购房者可能对以下信息感兴趣：

• 该地区的房产历史价格，以及它们与全国平均价格的比较
• 在过去 30 年中，该地区的邮政编码价格增长了多少，相对于平均增长情况
• 该地区的房屋何时购买和出售？
• 该地区与英国其他地区相比如何，即在价格分布中位于哪个百分位数？

由于 ClickHouse SQL 支持 简化分析查询的函数，因此这些查询很容易构建。以下查询在过去 30 年中，按月查询了“SL4”邮政编码的平均房价，以及整个国家的平均房价。

SELECT
	toStartOfMonth(date) AS month,
	round(avgIf(price, postcode1 = 'SL4')) AS filter_price,
	round(avg(price)) AS avg
FROM uk_price_paid
GROUP BY month
ORDER BY month ASC
LIMIT 10

┌──────month─┬─filter_price─┬───avg─┐
│ 1995-01-01 │   	123855 │ 68381 │
│ 1995-02-01 │   	103594 │ 65646 │
│ 1995-03-01 │   	118140 │ 65750 │
│ 1995-04-01 │   	113352 │ 67835 │
│ 1995-05-01 │   	116996 │ 67079 │
│ 1995-06-01 │   	107411 │ 67990 │
│ 1995-07-01 │   	110651 │ 70312 │
│ 1995-08-01 │   	123354 │ 70601 │
│ 1995-09-01 │   	111195 │ 68368 │
│ 1995-10-01 │   	128282 │ 67573 │
└────────────┴──────────────┴───────┘

10 rows in set. Elapsed: 0.303 sec. Processed 28.11 million rows, 290.73 MB (92.90 million rows/s., 960.73 MB/s.)

在构建了几个这样的查询之后，我们需要一种可视化方法。由于 Clickhouse JavaScript 客户端 支持以 JSONEachRow 格式返回结果，Apache ECharts 似乎很理想，因为它的 配置利用了简单的 JSON 对象。使用一些简单的映射函数，我们能够在付出最少努力的情况下获得合理的结果。上面的查询转换为一个条形图和折线图。

将这些添加到特定房产的视图中，提供了一个显而易见的引入这些分析的方法。除了允许用户获得房产邮政编码的概述之外，我们还允许更改过滤器，以便从城镇和地区的角度汇总数据。

最初，我们通过使用 JS 客户端直接查询 ClickHouse 来将分析添加到我们的应用程序中，以提供上面所示的架构。这很好；它很快，而且有效。但是，假设我们希望通过一个接口进行通信，而不是让我们的开发人员维护多个连接，并学习两种库和语法。为了实现这一点，Supabase 提供了 外部数据包装器 (FDW)。它们允许 Postgres 连接到外部系统，例如 ClickHouse，在原位查询数据。

虽然其他类似技术可能旨在将数据集拉入查询引擎以执行过滤和聚合，但 FDW 强调依赖于仅提取方法，其中查询被向下推，并且聚合和过滤器在目标数据源中执行。然后，只有结果被返回到 Postgres 以供显示。这种方法在 ClickHouse 方面有一些明显的优势。对于 ClickHouse 中的数十亿甚至数万亿行数据，将源数据拉入 Postgres 是不可行的。通过向下推查询并允许 ClickHouse 做它最擅长的事情（快速聚合非常大的数据集），FDW 可以通过最大限度地减少传输的数据来扩展，同时仍然通过一致的接口在最终用户应用程序中暴露实时分析，在该接口中开发人员不需要学习新的工具或语言客户端。

这种方法的其他可能的优势包括：

• 提供类似于 GraphQL 引擎的接口聚合器，但通过所有开发人员都熟悉的语言——SQL。
• 卸载不适合 Postgres 的工作负载，例如将分析查询到专门的数据存储，例如 ClickHouse，甚至查询到 OpenAI 之类的 API。
• 通过不移动数据，它始终与底层数据存储保持同步。开发人员无需担心维护复杂的 ETL 管道。
• 事务性/操作数据可以与 ClickHouse 中的分析数据联接，暴露新的功能，例如在我们的 HouseClick 示例中。
• 减少需要管理的基础设施，以及在设置时间和带宽方面的节省。

向 Supabase 添加外部 ClickHouse 表很简单。首先，我们需要通过安装扩展并指定处理程序和验证程序来启用 ClickHouse 的外部数据包装器。

create extension if not exists wrappers;
create foreign data wrapper clickhouse_wrapper
 handler click_house_fdw_handler
 validator click_house_fdw_validator;

安装完成后，我们可以创建到 ClickHouse 的连接。请注意，以下示例以纯文本形式传递凭据。Supabase 支持使用 pgsodium 和 Vault 更安全地 存储凭据。

create server clickhouse_server
  foreign data wrapper clickhouse_wrapper
  options (
	conn_string 'tcp://default:<password>@<host>:9440/default?connection_timeout=30s&ping_before_query=false&secure=true'
  );

如果使用 ClickHouse Cloud，请注意集群创建时的密码和主机，并使用安全端口 9440。

create foreign table people (
  <schema>
)
  server clickhouse_server
  options (
	table '<clickhouse_table>'
  );

在为我们的 HouseClick 应用程序执行此操作之前，让我们为使用 FDW 与 ClickHouse 设定一些最佳实践。

在可能的情况下，Supabase 用户应该确保在使用 ClickHouse FDW 时发生查询向下推。这意味着 FDW 在 ClickHouse 上运行查询，而不是将数据集拉入 Postgres 并进行本地聚合。由于 ClickHouse 通常比 Postgres 更有效地执行查询——甚至传输数百亿行的数据通常是不可行的，并且需要数小时——所以这对于性能至关重要。向下推对于安全原因也很有用，因为 ClickHouse 可以强制执行访问控制。虽然 FDW 在撰写本文时支持有限的向下推支持，但用户可以通过在 ClickHouse 中创建参数化视图，并将这些视图作为外部表通过包装器暴露给 Postgres 来确保这一点。

参数化视图 类似于普通视图，但可以在查询时创建，这些视图不立即解析，而是等到查询时才解析。这些视图可以与表函数一起使用，表函数指定视图的名称作为函数名称，并将参数值作为参数。这里的主要概念是在 ClickHouse 端的视图内封装查询（和特定可视化）的复杂性，并确保任何大型复杂的处理都在源头完成，从而保证向下推。

假设我们有以下查询，它计算特定邮政编码（在本例中为 SL4）的永久业权和租赁权房产的比率。对于非英国读者，永久业权和租赁权只是 由对底层土地的所有权区分的两种不同的所有权类型。

SELECT
	duration AS name,
	count() AS value
FROM default.uk_price_paid
WHERE postcode1 = 'SL4'
GROUP BY duration

┌─name──────┬─value─┐
│ leasehold │  6469 │
│ freehold  │ 15683 │
└───────────┴───────┘

2 rows in set. Elapsed: 0.114 sec. Processed 28.11 million rows, 75.52 MB (247.22 million rows/s., 664.11 MB/s.)

我们使用此特定查询来驱动饼图可视化。

鉴于我们的界面支持按邮政编码、城镇和地区进行过滤，我们需要一种方法将这些传递给我们的视图和底层查询，以便每次只传递一个值。我们通过构建一个 OR 子句来实现这一点。在匹配一个值（例如邮政编码）时，我们为其他列传递无效的非匹配值。下面，我们创建参数化视图 sold_by_duration，并说明如何使用该视图获得与上面相同的结果。

CREATE VIEW sold_by_duration AS
SELECT
	duration AS name,
	count() AS value
FROM default.uk_price_paid
WHERE (postcode1 = {_postcode:String}) OR (district = {_district:String}) OR (town = {_town:String})
GROUP BY duration


SELECT *
FROM sold_by_duration(_postcode = 'SL4', _district = 'X', _town = 'X')

┌─name──────┬─value─┐
│ leasehold │  6469 │
│ freehold  │ 15683 │
└───────────┴───────┘

2 rows in set. Elapsed: 0.230 sec. Processed 28.11 million rows, 201.23 MB (122.00 million rows/s., 873.21 MB/s.)

请注意，我们在这里会遇到性能损失，因为城镇和地区字段也必须进行匹配。未来的改进可能允许我们更 简洁有效地使用这些视图。

将此视图连接到 Supabase 中的外部表需要对 Postgres 执行一些 DDL 命令。下面，我们创建了一个外部表 sold_by_duration，它使用之前创建的连接查询 ClickHouse 中的同名视图。请注意，该表允许指定参数 postcode、district 和 town。

create foreign table sold_by_duration (
  name text,
  value bigint,
  postcode text, -- parameter column, used for input parameter,
  district text,
  town text
)
server clickhouse_server
  options (
	table '(select * from sold_by_duration(_postcode=${postcode1}, _district=${district}, _town=${town}))',
	rowid_column 'name'
);

从 Postgres，我们现在可以使用 psql 客户端查询此表，使用标准 WHERE 子句应用过滤器。

postgres=> select name, value from sold_by_duration where postcode1='SL4' AND district='X' AND town='X';
   name	| value
-----------+-------
 leasehold |  6469
 freehold  | 15683
(2 rows)

使用来自 Supabase 客户端的外部表与使用任何其他表没有什么不同，它允许我们透明地查询 ClickHouse。在创建外部表之前，我们的饼图由以下函数使用 Clickhouse JS 客户端驱动。这里的 condition 就是要应用的过滤器，例如 postcode1=SL4.。

async function soldByDuration(condition) {
   const results = await clickhouse.query({
       query: `SELECT duration as name,count() as value FROM uk_price_paid WHERE ${condition} GROUP BY duration`,
       format: 'JSONEachRow',
   })
   return await results.json()
}

创建了外部表后，我们可以使用 Supabase JS 客户端 和 匿名公共令牌 来导入和配置它。我们的 soldbyDuration 函数被 Supabase 实现替换——并非没有 SQL 代码。

import { createClient } from '@supabase/supabase-js'

const supabase = createClient('https://sfzygnnbtbttbtpiczck.supabase.co', '<anon key>')

const filter_config = {
   'postcode': {
       column: 'postcode1'
   },
   'town': {
       column: 'town'
   },
   'district': {
       column: 'district'
   },
}

async function soldByDuration_fdw(filters) {
   const default_value = 'X'
   let query = supabase.from('sold_by_duration').select('name, value')
   for (let k in filter_config) {
       let filter = filters.filter(f => f.column == filter_config[k].column)
       query = filter.length > 0 ? query.eq(filter[0].column, filter[0].value): query.eq(filter_config[k].column, default_value)
   }
   const { error, data } = await query
   return data
}

我们的结果以相同的格式返回，因此我们的更改很小。filter_config 对象只是提供了过滤器名称与列之间的映射。

假设 HouseClick 出售了一处房产。使用 Postgres（我们的应用程序数据）的优势之一是能够以事务方式更新行。只需一个简单的更新操作，就可以将房产标记为已售出。

UPDATE uk_house_listings
SET sold = True,
   sold_date = '2023-05-01'
WHERE id = '99';

这里我们将 ID 为 99 的房产标记为在 5 月 1 日售出。

随着房产的售出，我们可能希望定期更新 ClickHouse 中的历史分析数据。我们可以通过多种方式实现这一点。

• ClickHouse 的 FDW 是双向的。我们可以通过简单的 insert into select 语句将行推送到 ClickHouse。我们可以使用 sold_date 来识别最近售出的房产。然后，可以使用 pg_cron 定期安排此查询，以确保我们的分析数据保持最新。请注意，这将要求我们的 ClickHouse 表具有一个标识 ID 列，因为 FDW 需要一个 rowid_column 才能 支持更新。
• 使用 ClickHouse 中的 postgresql 表函数，我们可以从 Supabase 实例中拉取行。下面我们将利用它来拉取售出日期大于或等于 5 月 1 日的已售房产，并将它们插入到 uk_price_paid 表中。这种方法要求我们定期安排导入操作，例如，使用 cron 作业。

INSERT INTO uk_price_paid SELECT
   price,
   sold_date AS date,
   postcode1,
   postcode2,
   type,
   is_new,
   duration,
   addr1,
   addr2,
   street,
   locality,
   town,
   district,
   county
FROM postgresql('db.sfzygnnbtbttbtpiczck.supabase.co', 'postgres', 'uk_house_listings', 'migration', 'clickhouse')
WHERE (sold = true) AND (sold_date >= '2023-05-01')

这通过在 ClickHouse 和 Supabase 之间的数据流中引入双向性，完成了我们的架构。

有关 postgresql 表函数的更多详细信息，请参见我们的 最新博文系列。

在这篇博文中，我们探讨了 OLTP 和 OLAP 数据库（具体而言是 Postgres 和 ClickHouse）之间的差异，以及前者如何被用来为应用程序的状态和事务功能提供支持。相比之下，后者可以用来提供实时分析。除了展示如何通过直接查询 ClickHouse 来公开这些功能之外，我们还利用了 Supabase 的外部数据包装器功能，通过一个熟悉的单个接口公开 ClickHouse 提供的分析功能，然后谈到了用户如何使用事务行子集更新其分析数据。