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

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

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

我们已将代码提供给我们的演示。请谅解任何粗糙之处，仅供参考。

注意：Supabase 的 Clickhouse Wrapper 目前处于 Alpha 阶段，本文中的某些功能可能不适用于您的 Supabase 项目。如果您需要访问权限，请联系 [email protected]：Supabase 的 Clickhouse Wrapper 目前处于 Alpha 阶段，本文中的某些功能可能不适用于您的 Supabase 项目。如果您需要访问权限，请联系 [email protected]。

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

这些数据库的主要目标是确保工程师可以向数据库提交更新块，并确保它将——完全地——要么成功，要么失败。

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

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

重要的是，这两个更新必须同时发生，或者完全不发生。否则，银行可能会处于 10 美元下落不明的状态！（在 #1 成功，而 #2 失败的情况下）。

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

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

OLAP 或在线分析处理数据库旨在满足这些需求——管理分析工作负载。

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

当查询旨在检索一组特定的行，可能访问更多列，并且当数据需要频繁更新且必须实时执行时，OLTP 数据库非常出色。通过优化高速事务和并发性，OLTP 数据库允许同时访问和修改数据，而不会发生冲突。这些访问模式使 OLTP 数据库非常适合存储维护应用程序状态的数据并启用用户交互。Postgres 在这些工作负载下，尽管查询并发性很高，但仍能快速响应的能力，使其成为应用程序首选的 OLTP 数据库，为从电子商务和金融交易平台到客户服务系统的各种用例提供支持。

相反，OLAP 数据库（如 ClickHouse）擅长分析查询，这些查询访问许多行，但访问的列很少。这些查询通常使用 GROUP BY 操作和分析函数，对几个非常大的表中的数十亿（甚至数万亿）行进行汇总。这些工作负载通常与更大的数据集相关联，在这些数据集中，仍然需要小于 50 毫秒的查询时间。为了提供这种高性能，OLAP 数据库通常会牺牲某些功能，例如事务、对有限查询并发性的支持、更新支持，并要求用户批量执行插入。

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

为了帮助用户理解这些原则，我们决定构建一个应用程序，分别适当地使用每种数据库。对于任何好的演示，我们需要一个与真实世界用例相关的数据集。英国房价数据集虽然规模适中，约为 3000 万行，但可能对在线列出房产的房地产代理业务很有用。此数据集包含自 1990 年以来英国售出的每栋房屋的行。如果我们能否使用此数据集作为生成待售房屋的基础，但以分析的形式公开完整数据集，以帮助用户在查看房产时做出决策？永远不想错过双关语的机会，HouseClick 诞生了……“显然，购买和出售房屋的最快场所”。

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

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

凭借不需要 用户编写 SQL（已解决 SQL 注入问题）的丰富客户端集，以及将匿名用户限制为读取访问权限的行级安全性，这为存储我们当前约 1000 个待售房产列表提供了完美的解决方案。

我们的历史数据已加载到 ClickHouse Cloud 中进行分析，接下来我们只需要选择一个 Web 框架。由于对 React 有基本的熟悉，并且不想花费大量时间研究可能的堆栈，我听从了那些比创建虚假房地产代理业务更了解 Web 开发的 ClickHouse 人员的建议 - 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 年的估值 - 并添加了一些差异，以确保某些房屋看起来比其他房屋更划算。

使用此价格和房产面积，我们尝试预测房屋大小，即卧室数量，使用一些 非常粗略的启发式方法 - 诚然，这太简单了，后来产生了一些有趣的結果，特别是与图像结合使用时。

对于描述、标题和可能的房屋特征列表，我们求助于 Open AI 的 文本完成 API 和 ChatGPT-3 text-davinci-003 模型。所有 1000 个房产的成本约为 10 美元。

对我们基于 AI 的房地产经纪人感到满意，我们只需要图像。虽然 AI 生成的图像是可行的，使用 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 实例中拉取行。下面我们利用它来拉取 sold_date 大于或等于 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 驱动的分析，然后再讨论用户如何使用事务行的子集来更新其分析数据。