如何使用 RisingWave 和 Supabase 提高电商场景中的广告效果

作者：王炜皓，RisingWave Labs Cloud 开发工程师

Supabase 简介

Supabase 是一个开源的后端服务平台，它为开发人员提供了一组工具和基础设施，让他们能够高效地构建、扩展应用程序的后端部分。它利用 Postgres 作为底层 OLTP 数据库来处理事务型业务逻辑，并提供齐全的工具包以加速应用功能的交付。此外，Supabase Realtime 针对实时场景（例如在线聊天室和监控看板等）增强了开发工具包的功能，它为开发实时事件驱动应用提供了一个简便的框架。

RisingWave 如何增强流处理能力？

在很多情况下，直接使用 OLTP 数据库中的数据是无法满足应用场景需要的。以实时监控看板为例，我们无法直接从 OLTP 数据库中获取最近 5 分钟内修改了用户名的用户数量，因为这些数据是基于滑动时间窗口内原始数据汇总得出的，所以这些指标通常在 OLTP 数据库中并不容易获得。

为了实现这个功能且无需对业务系统进行修改，我们需要一个实时数据管道来监控用户表的变更数据捕获（CDC）流，以持续汇总过去 5 分钟内更新的用户名数量。通常，通常数据管道的计算结果还需要存储在某些数据服务当中，以便看板应用程序从中获取。

实时监控看板示例

看上去是不是相当繁琐？如果在流处理中也能有一款像 Supabase 这样的产品，我们就可以专注于业务逻辑了。这个产品需要能够根据我们描述所需结果的 SQL 语句自动构建数据管道。它需要具有弹性，这意味着它可以根据摄取的数据量来扩展计算资源。它还需要能支持用户直接访问计算结果，而无需外部数据服务。此外，它还需具有低成本、易于维护、可靠（符合SLA）和用户友好等特点。

隆重介绍 RisingWave。这是一款能满足以上所有需求的流式数据库。

RisingWave 由 RisingWave Labs 于 2021 年初构建，在与数家公司的数据堆栈集成并应用于多种场景后，它现在已经做好了普及流处理的一切准备。

在本文中，我们将探讨如何使用 Supabase 和 RisingWave Cloud 提高电子商务场景中的广告表现。本示例的灵感来自《Designing Data-Intensive Applications》，这是一本非常出色的数据工程指南。

使用 Supbase Tables 作为数据源（source）

让我们先在 Supabase 中创建一些表：products、customers、promotions 和 sale_events。前三个表是实体，模拟了电子商务业务逻辑中的参与者。第四个表 sale_events 被称为“事实表”，它记录了所有交易。这些关系可在 Supabase 模式可视化器（schema visualizer）中一目了然：

Supabase 是一个 OLTP 数据库，擅长处理客户订单和销售数据。现在，我们希望进行流处理，实时计算最优的广告投放方案，并动态调整广告权重。这就需要利用业务生成的数据，对其进行实时计算，然后将计算结果反馈给业务方。

与批处理不同的是，流处理会在接收到新数据时通过增量计算算法自动更新实时结果。以计算平均值为例。我们可以用增量方法更新结果，而不是将列表中的所有值相加再除以总数，时间复杂度仅为 O(1)：

其中 sum 和 count 的值也是以增量方式计算的。RisingWave 还能优化流处理中的前 N 项（top N）、滑动窗口（sliding window）和其他复杂计算。通过运行 SQL 语句 EXPLAIN CREATE MATERIALIZED VIEW ...，即可获得计算过程的全图。

为了简化操作，我们使用 RisingWave Cloud 建立了一个免费的 RisingWave 集群。RisingWave Cloud 可帮助我们管理集群，并提供在线 SQL 编辑器、数据管道可视化器、source/sink 管理界面、数据库用户管理和指标看板等实用功能。

建立 RisingWave 集群后，我们需要为 Supabase 中的 sale_events 表和 promotions 表创建数据库 replication。这些 replication 将作为 RisingWave 集群的数据源。然后，我们可以使用 RisingWave Cloud 中的 SQL 编辑器创建源表。接着我们使用 CREATE TABLE 语句连接到源表，这样就可以在 RisingWave 集群中持久化数据副本（不使用 CREATE SOURCE 语句的原因是它无法持久化数据）。

CREATE TABLE sale_events (
  -- 和 Supabase 中的 sale_events 表中的 column 一致
  id int8,
  created_at TIMESTAMPTZ, 
  customer_id string,
  product_id int8,
  promotion_id int8,
  quantity int8,
  net_price_cents int8,
  discount_price_cents int8,
  PRIMARY KEY (id)
) 
WITH (
  connector='postgres-cdc',
  hostname = 'db.xxxxxx.supabase.co',
  port = '5432',
  username = 'postgres',
  password = 'xxxxxx',
  database.name = 'postgres',
  schema.name = 'public',
  table.name = 'sale_events',
  publication.name = 'rw_publication' -- Supabase 中数据库 replication 的名称
);

CREATE TABLE promotions (
  id int8,
  created_at TIMESTAMPTZ, 
  ad_type string,
  product_id int8,
  weight_percent int8,
  PRIMARY KEY (id)
) 
WITH (
  ......
  table.name = 'promotions'
);

设计流处理管道

在编写计算部分之前，我们先来看看最终设计的数据流。首先，电子商务系统在 sale_events 表中记录销售事件（步骤 1）。表中的变化通过 CDC 流捕获并更新到 RisingWave 集群中。为了计算每次推广的投资回报率（ROI），promotions 表也会复制到 RisingWave 集群（步骤 2）。然后，数据管道会计算 ROI 和波动率，并将其作为中间结果存储在物化视图中。根据中间结果，可以使用 SQL 定义的策略计算新的推广权重（步骤 3）。最终结果存储在另一个物化视图中，其结构与 Supabase 中的 promotions 表相同。这样，RisingWave 集群就可以使用 JDBC 更新 Supabase 中权重列的值（步骤 4）。随后，Supabase 实时捕获 promotions 表中的更新事件，使应用程序能够立即调用 API 并调整电子商务系统中的权重（步骤 5 和 6）。

回到我们的用例。为了方便起见，我们有两个假设：

1. 产品供应商有固定的产品推广预算。这意味着我们可以将权重视为投资额，因此我们这里说的 “ROI” 实际上是一个相对的 ROI 指标。
2. 每种产品只有两种推广类型（这个我们下面会讲到）。

回报是销售总额，可以从 sale_events 表中计算出来。该指标的公式如下

其中，n 为时间窗口长度。

预处理数据以获取中间结果

我们使用时间窗口销售额的标准差作为波动率。有了收益率和波动率，我们就可以构建一个简单的优化组合，使收益率尽可能大，同时分散特异性风险。

sale_events表如下:

sale_events (table)
---------------------------------------------------------------------------
 product_id | promotiom_id     | created_at | discount_price | quantity
------------+------------------+------------+------------------------------
 headphones | Search Result    |      10:03 |           6600 |        1 
 headphones | Youtube Video Ad |      10:08 |           6600 |        2
 guitar     | Search Result    |      10:09 |          19900 |        1
 guitar     | Search Result    |      10:09 |          19900 |        1 
 headphones | Youtube Video Ad |      10:09 |           7299 |        2

我们用 5 分钟作为时间窗口长度，并使用 product_id 和 promotion_id 作为 key，在时间窗口中将行分组。

CREATE MATERIALIZED VIEW promotions_sequences_60mins AS(
  SELECT 
    product_id, 
    promotion_id, 
    sum(total_price) AS sales_amount,
    window_start,
    window_end
  FROM (
    SELECT 
      product_id, 
      promotion_id, 
      discount_price_cents * quantity as total_price,
      window_start,
      window_end
    FROM TUMBLE (sale_events, created_at, INTERVAL '5 MINUTES')
  )
  WHERE window_start > NOW() - INTERVAL '60 minutes'
  GROUP BY window_start, window_end, product_id, promotion_id
  ORDER BY window_start DESC
)

然后，我们就可以得到：

promotions_sequences_60mins (materialized view)
---------------------------------------------------------------------------
product_id | promotion_id      | sales_amount | creaed_at| window_start | wind_end 
------------+------------------+--------------+----------+--------------+--
 headphones | Search Result    |         6600 |    10:08 |        10:05 |      10:10
 headphones | Youtube Video Ad |        13200 |    10:08 |        10:05 |      10:10
     guitar | Search Result    |        19900 |    10:07 |        10:05 |      10:10
     guitar | Youtube Video Ad |        19900 |    10:05 |        10:05 |      10:10
===========================================================================
 headphones | Search Result    |         6600 |    10:04 |        10:00 |      10:05
 headphones | Youtube Video Ad |         6600 |    10:03 |        10:00 |      10:05
     guitar | Search Result    |        19900 |    10:03 |        10:00 |      10:05
     guitar | Youtube Video Ad |        19900 |    10:02 |        10:00 |      10:05
===========================================================================
        ... |              ... |          ... |      ... |          ... |        ...

计算波动率和 ROI 指标

有了这个表示时间序列的物化视图，我们就可以轻松计算出最新时间窗口的波动率和 ROI：

CREATE MATERIALIZED VIEW promotions_stat_60mins AS (
  SELECT
    promotions.product_id AS product_id,
    promotion_id,
    SUM(sales_amount) / SUM(promotions.weight_percent) AS roii,
    StDDEV_SAMP(sales_amount) as vol
  FROM promotions_sequences_60mins as seq
  JOIN promotions ON promotion_id = promotions.id AND seq.product_id = promotions.product_id
  GROUP BY product_id, promotion_id

新的物化视图如下：

promotions_stat_60mins (materialized view)
------------------------------------------------------------
 product_id | promotion_id     | roii         |         vol 
------------+------------------+--------------+-------------
 headphones | Search Result    |         89.3 |       0.532 
 headphones | Youtube Video Ad |         67.8 |       0.110 
     guitar | Search Result    |        125.7 |       0.239 
     guitar | Youtube Video Ad |        293.3 |       0.614

将新值传送到 Supabase

假设不同促销类型之间的相关性为零。那么，就可以通过构建一个夏普比率最大的投资组合来确定产品耳机的新权重。夏普比率最大化的数学表示为：

借助拉格朗日乘子法，我们可以知道权重为：

现在我们来计算新权重并使用 JDBC sink 将其更新到 Supabase：

CREATE SINK promotion_update AS (
  SELECT 
    product_id,
    promotion_id,
    o2.vol^2 * o1.roii / (o1.roii * o2.vol^2 + o2.roii * o1.vol^2) as weight
  FROM promotions_stat_60mins as o1
  JOIN promotions_stat_60mins as o2 ON o1.product_id = o2.product_id AND o1.promotion_id <> o2.promition_id
  GROUP BY product_id
) WITH (
  connector='jdbc',
  jdbc.url='jdbc:postgresql://xxxx.supabase.co:5432/postgres?user=postgres&password=xxx',
  table.name = 'promotions',
  type = 'upsert'
);

如有任何变化，该 sink 将使用 JDBC 更新 Supabase 中 promotions 表中的值。Supabase Realtime 会感知 promotions 表中的变化，并实时采取行动，从而完成动态广告系统的实时控制循环。

我们假设每个产品只有两个促销活动，因为基于夏普比率计算最佳投资组合对于纯 SQL 表达式来说有一定挑战性。不过，RisingWave 支持 Python、Java 和 Rust 用户自定义函数（UDF），可以轻松解决凸二次优化问题。此外，它还支持用户定义聚合函数（UDAF）和用户定义表生成函数（UTF），以适应各种需求。您可以在我们的文档网站上找到更多有关 UDF 的资源。

结论

本示例展示了 RisingWave 如何为 Supabase 开发人员在构建实时应用程序时提供无缝的开发体验。RisingWave 还与 PostgreSQL 兼容，这意味着经验丰富的 Supabase 开发人员无需在开发过程中频繁检查语法。RisingWave 还支持多种 source 和 sink，便于开发人员与现有的架构集成。

该示例突出了 RisingWave 的用户友好性，让用户更容易驾驭流数据管道。然而，实际情况往往比示例更复杂。幸运的是，RisingWave 支持诸如精确一次性语义（exactly-once semantics）、快照读取（snapshot read）、二级恢复和扩展（second-level recovery and scaling）、复杂连接操作（complicated join operations）等核心功能，以适应生产环境中的各种场景。

您可在 GitHub 上访问我们的源代码，也可在 RisingWave Cloud 中启动一个免费集群，使用 RisingWave 开始您的流处理之旅！

---

关于 RisingWave

RisingWave 是一款分布式 SQL 流处理数据库，旨在帮助用户降低实时应用的的开发成本。作为专为云上分布式流处理而设计的系统，RisingWave 为用户提供了与 PostgreSQL 类似的使用体验，并且具备比 Flink 高出 10 倍的性能以及更低的成本。

了解更多：

官网： risingwave.com

文档：risingwave.dev

GitHub：risingwave.com/github

Slack： risingwave.com/slack

B 站：RisingWave 中文开源社区

微信公众号：RisingWave中文开源社区

社区用户交流群：risingwave_assistant