在 OLAP 数据库中建模机器学习数据

在本文中，我们将探讨 MLOps 的世界，以及如何对 OLAP 数据库中的数据进行建模和转换，使其能够充当训练 ML 模型的高效特征存储。虽然分享的经验适用于各种 OLAP 系统，但我们将以 ClickHouse 为例来演示这些技术——原因仅仅是我们非常熟悉它！

本博客中讨论的方法被 现有的 ClickHouse 用户 使用，我们感谢他们分享了他们的技术，以及开箱即用的特征存储。

我们专注于使用 ClickHouse 作为数据源、离线存储和转换引擎。特征存储的这些组件对于高效且正确地向模型训练交付数据至关重要。虽然大多数开箱即用的特征存储提供了抽象，但我们剥开层面，描述如何有效地建模数据以构建和服务特征。对于希望构建自己的特征存储或只是好奇现有存储使用哪些技术的用户，请继续阅读。

我们已经在 之前的博客文章 中探讨了什么是特征存储，并建议用户在深入阅读本文之前熟悉这个概念。简而言之，特征存储库是一个用于存储和管理将用于训练 ML 模型的数据的集中式存储库，旨在提高协作和可重用性，并缩短模型迭代时间。

作为实时数据仓库，ClickHouse 除了简单地提供数据源之外，还可以满足特征存储的两个主要组件。

1. 转换引擎：ClickHouse 利用 SQL 声明数据转换，并通过其分析和统计函数进行优化。它支持查询来自各种来源的数据，例如 Parquet、Postgres 和 MySQL，并对 PB 级数据执行聚合。物化视图允许在插入时进行数据转换。此外，ClickHouse 可以通过 chDB 在 Python 中用于转换大型数据帧。
2. 离线存储：ClickHouse 可以通过 INSERT INTO SELECT 语句持久化查询结果，自动生成表模式。它支持高效的数据迭代和扩展，特征通常以带有时间戳的表表示，用于时间点查询。ClickHouse 的稀疏索引和 ASOF LEFT JOIN 子句有助于快速过滤和特征选择，优化训练管道的数据准备。这项工作是并行化的，并在集群中执行，使离线存储能够扩展到 PB 级，同时保持特征存储的轻量级。

在这篇文章中，我们将展示如何在 ClickHouse 中建模和管理数据以执行这些角色。

当使用 ClickHouse 作为离线特征存储的基础时，我们认为训练模型的步骤包括以下内容

1. 探索 - 使用 SQL 查询熟悉 ClickHouse 中的源数据。

2. 识别数据子集和特征 - 识别可能的特征、它们各自的实体以及生成它们所需的数据子集。我们将此步骤中的子集称为“特征子集”。

3. 创建特征 - 创建生成特征所需的 SQL 查询。

4. 生成模型数据 - 适当地组合特征以生成一组特征向量，通常使用 ASOF JOIN 在公共键和时间戳邻近度上实现。

5. 生成测试集和训练集 - 将“特征子集”拆分为测试集和训练集（以及可能的验证集）。

6. 训练模型 - 使用训练数据训练模型，可能使用不同的算法。

7. 模型选择和调优 - 根据验证集评估模型，选择最佳模型，并微调超参数。

8. 模型评估 - 根据测试集评估最终模型。如果性能足够，则停止；否则，返回第 2 步。

我们关注步骤 (1) 到 (5)，因为这些是 ClickHouse 特有的。上述过程的关键属性之一是它具有高度迭代性。步骤 (3) 和 (4) 可以粗略地称为“特征工程”，并且通常比选择模型和改进超参数花费更多时间。因此，优化此过程并确保尽可能高效地使用 ClickHouse 可以节省大量时间和成本。

我们在下面探讨了这些步骤中的每一个，并提出了一种灵活的方法，该方法最佳地利用了 ClickHouse 功能，允许用户高效地迭代。

在我们的示例中，我们使用以下 Web 分析数据集，此处 描述。此数据集包含 1 亿行，其中一个事件表示对特定 URL 的请求。在 ClickHouse 中对 Web 分析数据训练机器学习模型是我们用户中常见的用例_[1][2]。

由于其大小，下表已截断为我们将使用的列。有关完整模式，请参见 此处。

CREATE TABLE default.web_events
(
   `EventTime` DateTime,
   `UserID` UInt64,
   `URL` String,
   `UserAgent` UInt8,
   `RefererCategoryID` UInt16,
   `URLCategoryID` UInt16,
   `FetchTiming` UInt32,
   `ClientIP` UInt32,
   `IsNotBounce` UInt8,
   -- many more columns...
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(EventDate)
ORDER BY (CounterID, EventDate, intHash32(UserID))

为了说明建模步骤，假设我们希望使用此数据集构建一个模型，该模型预测用户在请求到达时是否会跳出。如果我们考虑上面的源数据，这由列 IsNotBounce 表示。这表示我们的目标类或标签。

我们实际上不会构建此模型并提供 Python 代码，而是专注于数据建模过程。因此，我们对特征的选择仅用于说明目的。

探索和理解源数据需要用户熟悉 ClickHouse SQL。我们建议用户在此步骤中熟悉 ClickHouse 的各种 分析函数。熟悉数据后，我们可以开始识别模型的特征以及生成它们所需的数据子集。

为了预测访问是否会跳出，我们的模型将需要一个训练集，其中每个数据点都包含组合成特征向量的适当特征。通常，这些特征将基于数据的子集。

我们已经在其他博客中探讨了特征和特征向量的概念，以及它们如何分别与结果集的列和行松散地相关联。重要的是，它们需要在训练和请求时可用。

请注意，我们在上面强调了“结果集”。特征向量很少只是表中的一行，其中包含列子集的特征。通常，必须发出复杂的查询才能从聚合或转换中计算特征。

在识别特征之前，我们应该了解将影响我们建模过程的两个关键属性

• 与实体的关联 - 特征通常与它们关联或“键控”的 实体 相关联。对于我们的问题，我们认为可能有助于进行预测的特征可能是基于用户或域的混合。基于用户的特征将是特定于请求的，例如，用户的年龄、客户端 IP 或用户代理。域特征将与访问的页面相关联，例如 每年的访问次数。

  将特征与实体的实例关联需要实体具有键或标识符。在我们的例子中，我们需要用户和域值的标识符。这些可以从 UserID 和 URL 列中获得。可以使用 ClickHouse 中的域函数从 URL 中提取域，即 domain(URL)。

• 动态且复杂 - 虽然某些特征保持相对静态，例如用户的年龄，但其他特征（例如客户端 IP）会随着时间推移而发生变化。在这种情况下，我们需要访问特征在特定时间戳存在时的值。这对于创建时间点正确的训练集至关重要。

  虽然某些特征可能很简单，例如设备是否是移动设备或客户端 IP，但其他更复杂的特征需要聚合统计信息，这些统计信息会随着时间而变化 - 而这正是 ClickHouse 的优势所在！

例如，假设我们认为以下特征对于预测网站访问是否会跳出很有用。我们所有的特征都与时间戳相关联，因为它们是动态的并且随时间变化。

• 访问的用户代理 - 与用户实体关联，并在 UserAgent 列中可用。
• 引荐来源类别的类别 - （例如，搜索引擎、社交媒体、直接）。用户特征，可通过 RefererCategoryID 列获得。
• 每小时访问的域数 - 在用户发出请求时。需要 GROUP BY 来计算此用户特征。
• 每小时访问域的唯一 IP 数 - 域特征，需要 GROUP BY。
• 页面类别 - 用户特征，可通过 URLCategoryID 列获得。
• 每小时域的平均请求时间 - 需要 GROUP BY 才能从 FetchTiming 列中计算得出。

这些特征可能不是理想的，仅用于说明目的。将这些特征中的许多特征链接到用户实体有点过于简单。例如，某些特征更准确地与请求或会话实体相关联。

一旦我们了解了我们将使用的特征，我们就可以识别构建它们所需的数据子集。此步骤是可选的，因为有时用户希望使用整个数据，或者数据不够大和复杂以至于不需要此步骤。

应用时，我们经常看到用户为其模型数据创建表 - 我们将这些表称为“特征子集”。这些包括

• 每个特征向量的实体值。
• 事件的时间戳（如果存在）。
• 类标签。
• 生成计划特征所需的列。用户可能希望添加他们认为在未来迭代中有用的其他列。

这种方法提供了几个优点

• 允许对数据进行排序和优化，以便将来访问。通常，模型数据的读取和过滤方式与源数据不同。当生成最终训练集时，这些表还可以比源数据更快地生成特征。

• 模型可能需要数据的子集或转换集。识别、过滤和转换此子集可能是一个昂贵的查询。通过将数据插入到中间模型表中，此查询只需执行一次。因此，随后的特征生成和模型训练运行可以从该表中高效地获取数据。

  我们可以使用此步骤来删除任何重复数据。虽然原始数据可能不包含重复项，但如果我们提取列子集作为特征，则生成的子集可能会包含重复项。

假设对于我们的模型，我们创建一个中间表 predict_bounce_subset。此表需要 EventTime、标签 IsNotBounce 和实体键 Domain 和 UserID。此外，我们还包括简单的特征列 UserAgent、RefererCategoryID 和 URLCategoryID，以及生成我们的聚合特征所需的列 - FetchTiming 和 ClientIP。

CREATE TABLE predict_bounce_subset
(
  EventTime DateTime64,
  UserID UInt64,
  Domain String,
  UserAgent UInt8,
  RefererCategoryID UInt16,
  URLCategoryID UInt16,
  FetchTiming UInt32,
  ClientIP UInt32,
  IsNotBounce UInt8
)
ENGINE = ReplacingMergeTree
ORDER BY (EventTime, Domain, UserID, UserAgent, RefererCategoryID, URLCategoryID, FetchTiming, ClientIP, IsNotBounce)
PRIMARY KEY (EventTime, Domain, UserID)

我们使用 ReplacingMergeTree 作为我们的表引擎。此引擎删除在排序键列中具有相同值的行。这是 在后台在 MergeTree 中异步执行的，尽管我们可以使用 FINAL 修饰符 确保在查询时不接收重复项。在这种情况下，这种去重方法比替代方法（在下面的插入时使用我们希望去重数据的列的 GROUP BY）更节省资源。上面，我们假设应使用所有列来识别唯一行。可能会发生具有相同 EventTime、UserID、Domain 的事件，例如访问域时发出多个具有不同 FetchTiming 值的请求。

有关 ReplacingMergeTree 的更多详细信息，请参见此处。

作为优化，我们仅通过 PRIMARY KEY 子句将排序键的子集加载到主键（保存在内存中）。默认情况下，加载 ORDER BY 中的所有列。在这种情况下，我们可能只想按 EventTime、Domain 和 UserID 查询。

假设我们只想对与机器人无关的事件（由 Robotness=0 标识）训练我们的跳出预测模型。我们还需要 Domain 和 UserID 值。

我们可以使用 INSERT INTO SELECT 填充我们的 predict_bounce_subset 表，从 web_events 中读取行并应用过滤器，将我们的数据大小减少到 4200 万行。

INSERT INTO predict_bounce_subset SELECT
	EventTime,
	UserID,
	domain(URL) AS Domain,
	UserAgent,
	RefererCategoryID,
	URLCategoryID,
	FetchTiming,
	ClientIP,
	IsNotBounce
FROM web_events
WHERE Robotness = 0 AND Domain != '' AND UserID != 0

0 rows in set. Elapsed: 7.886 sec. Processed 99.98 million rows, 12.62 GB (12.68 million rows/s., 1.60 GB/s.)

SELECT formatReadableQuantity(count()) AS count
FROM predict_bounce_subset FINAL

┌─count─────────┐
│ 42.89 million │
└───────────────┘

1 row in set. Elapsed: 0.003 sec.

请注意上面使用了 FINAL 子句以确保我们仅计算唯一行。

虽然某些数据子集是静态的，但其他子集会随着新事件到达源表而发生变化。因此，用户通常需要保持子集为最新。虽然这可以通过计划查询（例如，使用 dbt）重建表来实现，但 ClickHouse（增量）物化视图可用于维护这些表。

物化视图允许用户将计算成本从查询时间转移到插入时间。ClickHouse 物化视图只是一个触发器，它在数据块插入到表（例如 web_events 表）时对数据块运行查询。然后，此查询的结果将插入到第二个“目标”表（在我们的例子中为子集表）。如果插入更多行，结果将再次发送到目标表。此合并结果等效于对所有原始数据运行查询。

下面显示了一个维护我们的 predict_bounce_subset 的物化视图

CREATE MATERIALIZED VIEW predict_bounce_subset_mv TO predict_bounce_subset AS
SELECT
   EventTime,
   UserID,
   domain(URL) AS Domain,
   UserAgent,
   RefererCategoryID,
   URLCategoryID,
   FetchTiming,
   ClientIP,
   IsNotBounce
FROM web_events
WHERE Robotness = 0 AND Domain != '' AND UserID != 0

这是一个简单的示例，我们建议用户在此处 了解有关物化视图功能的更多信息。对于后续步骤，我们假设我们将使用模型表 predict_bounce_subset。

对于模型训练，我们需要将我们计划的特征组装成一组特征向量，其中包含标签 IsNotBounce，即

请注意我们如何从多个实体的特征组装我们的特征向量。在许多情况下，我们都在寻找来自另一个实体的最接近时间点的特征值。

一位熟悉 ClickHouse 的经验丰富的 SQL 开发人员可能会制定一个生成上述特征向量的查询。虽然有可能，但这不仅将是一个复杂且计算成本高昂的查询——尤其是在数十亿行的大型数据集上。

此外，如前所述，我们的特征向量是动态的，并且可能由不同训练迭代期间不同特征的任意数量的组合组成。此外，理想情况下，不同的工程师和数据科学家将使用相同的特征定义，并尽可能优化地编写查询。

鉴于上述需求，将我们的特征物化到表中是有意义的，方法是使用 INSERT INTO SELECT 将查询结果写入表中。这实际上意味着我们执行一次特征生成，将结果写入表中，以便在迭代时可以高效地读取和重用它们。这也使我们能够声明一次特征查询，以最佳方式声明，并与其他工程师和科学家共享结果。

目前，我们省略了用户可能希望如何声明、版本控制和共享其特征定义。毕竟，SQL 是代码。解决此问题有多种解决方案，其中一些解决方案解决了不同的挑战 - 请参阅“构建或采用”。

特征表包含与实体关联的特征实例，以及可选的时间戳。我们已经看到我们的用户为特征采用了两种表模型 - 要么为每个特征创建一个表，要么为每个实体创建一个表。我们将在下面探讨每种方法的各自优缺点。但这两种方法都允许重用特征，其优点是它们确保数据得到良好压缩。

您无需为每个特征创建特征表。在某些情况下，可以从模型表中按原样使用某些特征，在这些情况下，它们由列值表示。

使用每个特征一个表，表名表示特征本身。这种方法的主要优点是它可能会简化以后的连接。此外，这意味着用户可以使用物化视图来维护这些表，因为源数据会发生变化 - 请参阅“更新”。

这种方法的缺点是它的扩展性不如“每个事件”方法。具有数千个特征的用户将拥有数千个表。虽然可能可以管理，但创建物化视图来维护每个表是不可行的。

例如，考虑负责存储域特征“每个域的唯一 IP 数”的表。

CREATE TABLE number_unique_ips_per_hour
(
  Domain String,
  EventTime DateTime64,
  Value Int64
)
ENGINE = MergeTree
ORDER BY (Domain, EventTime)

我们选择 ORDER BY 键来优化 压缩和未来的读取。我们可以使用简单的 INSERT INTO SELECT 和聚合来计算我们的特征，从而填充我们的特征表。

INSERT INTO number_unique_ips_per_hour SELECT
   Domain,
   toStartOfHour(EventTime) AS EventTime,
   uniqExact(ClientIP) AS Value
FROM predict_bounce_subset FINAL
GROUP BY
   Domain,
   EventTime

0 rows in set. Elapsed: 0.777 sec. Processed 43.80 million rows, 1.49 GB (56.39 million rows/s., 1.92 GB/s.)

SELECT count()
FROM number_unique_ips_per_hour

┌─count()─┐
│  613382 │
└─────────┘

我们选择使用 Domain 和 Value 作为实体和特征值的列名。这使我们未来的查询稍微简单一些。用户可能希望为所有特征使用通用表结构，其中包含 Entity 和 Value 列。

CREATE TABLE <feature_name>
(
  Entity Variant(UInt64, Int64, String),
  EventTime DateTime64,
  Value Variant(UInt64, Int64, Float64)
)
ENGINE = MergeTree
ORDER BY (Entity, EventTime)

这需要使用 Variant 类型。此类型允许列支持其他数据类型的联合，例如 Variant(String, Float64, Int64) 表示此类型的每一行都具有 String、Float64 Int64 类型的值或没有值（NULL 值）。

此功能目前是实验性的，对于上述方法是可选的，但对于下面描述的“每个实体”方法是必需的。

在每个实体方法中，我们对与同一实体关联的所有特征使用同一张表。在这种情况下，我们在下面使用列 FeatureId 来表示特征的名称。

这种方法的优点是其可扩展性。单张表可以轻松容纳数千个特征。

主要的缺点是目前此方法不支持物化视图。

请注意，在下面的域特征示例中，我们被迫使用新的 Variant 类型。虽然此表支持 UInt64、Int64 和 Float64 特征值，但它可能支持更多。

-- domain Features
SET allow_experimental_variant_type=1
CREATE TABLE domain_features
(
  Domain String,
  FeatureId String,
  EventTime DateTime,
  Value Variant(UInt64, Int64, Float64)
)
ENGINE = MergeTree
ORDER BY (FeatureId, Domain, EventTime)

此表的 ORDER BY 针对按特定 FeatureId 和 Domain 进行过滤进行了优化 - 这是我们稍后将看到的典型访问模式。

为了将我们的“每个域的唯一 IP 数”特征填充到此表中，我们需要使用与之前使用的查询类似的查询

INSERT INTO domain_features SELECT
   Domain,
   'number_unique_ips_per_hour' AS FeatureId,
   toStartOfHour(EventTime) AS EventTime,
   uniqExact(ClientIP) AS Value
FROM predict_bounce_subset FINAL
GROUP BY
   Domain,
   EventTime

0 rows in set. Elapsed: 0.573 sec. Processed 43.80 million rows, 1.49 GB (76.40 million rows/s., 2.60 GB/s.)

SELECT count()
FROM domain_features

┌─count()─┐
│  613382 │
└─────────┘

ClickHouse 新手可能会想知道，“每个特征”方法是否比“每个实体”方法允许更快地检索特征。由于使用了排序键和 ClickHouse 稀疏索引，因此此处应该没有区别。

虽然某些特征是静态的，但我们通常希望确保在源数据或子集发生更改时更新它们。正如“更新子集”中所述，我们可以使用物化视图来实现此目的。

在特征表的情况下，我们的物化视图通常更复杂，因为结果通常是聚合结果，而不仅仅是简单的转换和过滤。因此，物化视图执行的查询将生成部分聚合状态。这些部分聚合状态表示聚合的中间状态，目标特征表可以将这些状态合并在一起。这要求我们的特征表使用 AggregatingMergeTree 和适当的 AggregateFunction 类型。

我们在下面为“每个特征”表 number_unique_ips_per_hour 提供了一个示例。

CREATE TABLE number_unique_ips_per_hour
(
  Entity String,
  EventTime DateTime64,
  -- the AggregateFunction merges states produced by the view
  Value AggregateFunction(uniqExact, UInt32)
)
ENGINE = AggregatingMergeTree
ORDER BY (Entity, EventTime)

CREATE MATERIALIZED VIEW number_unique_ips_per_hour_mv TO number_unique_ips_per_hour AS
SELECT
   domain(URL) AS Entity,
   toStartOfHour(EventTime) AS EventTime,
   -- our view uses the -State suffix to generate intermediate states
   uniqExactState(ClientIP) AS Value
FROM predict_bounce_subset
GROUP BY
   Entity,
   EventTime

当新行插入到 predict_bounce_subset 表中时，我们的 number_unique_ips_per_hour 特征表将被更新。

在查询 number_unique_ips_per_hour 时，我们必须使用 FINAL 子句或 GROUP BY Entity, EventTime 来确保聚合状态与聚合函数的 -Merge 变体（在本例中为 uniqExact）一起合并。如下所示，这会更改用于获取实体的查询 - 有关更多详细信息，请参见 此处。

-- Select entities for a single domain
SELECT
   EventTime,
   Entity,
   uniqExactMerge(Value) AS Value
FROM number_unique_ips_per_hour
WHERE Entity = 'smeshariki.ru'
GROUP BY
   Entity,
   EventTime
ORDER BY EventTime DESC LIMIT 5

┌───────────────EventTime─┬─Entity────────┬─Value─┐
│ 2013-07-31 23:00:00.000 │ smeshariki.ru │  3810 │
│ 2013-07-31 22:00:00.000 │ smeshariki.ru │  3895 │
│ 2013-07-31 21:00:00.000 │ smeshariki.ru │  4053 │
│ 2013-07-31 20:00:00.000 │ smeshariki.ru │  3893 │
│ 2013-07-31 19:00:00.000 │ smeshariki.ru │  3926 │
└─────────────────────────┴───────────────┴───────┘

5 rows in set. Elapsed: 0.491 sec. Processed 8.19 thousand rows, 1.28 MB (16.67 thousand rows/s., 2.61 MB/s.)
Peak memory usage: 235.93 MiB.

虽然稍微复杂一些，但中间聚合状态允许我们使用上面的表来生成不同时间的特征。例如，我们可以使用 此查询 从上面的表中计算每个域每天的唯一 IP 数，这是我们使用原始特征表无法做到的。

用户可能会注意到，我们的子集表 predict_bounce_subset 已经通过物化视图进行了更新，而物化视图又附加了物化视图。如下所示，这意味着我们的物化视图实际上是“链接的”。有关链接物化视图的更多示例，请参见 此处。

上述“每个特征”建模方法意味着每个特征表有一个物化视图（一个物化视图只能将结果发送到一个表）。在较大的用例中，这成为扩展的约束 - 物化视图会产生插入时间开销，我们不建议在单个表上使用超过 10 个物化视图。

“每个实体”特征表模型减少了特征表的数量。为了封装不同特征的多个查询，我们需要将 Variant 类型与 AggregateFunction 类型一起使用。目前不支持这样做。

作为替代方案，用户可以使用可刷新物化视图（目前是实验性的）。与 ClickHouse 的增量物化视图不同，这些视图定期在整个数据集上执行视图查询，并将结果存储在目标表中，目标表的内容会被原子替换。

用户可以使用此功能按计划定期更新特征表。我们在下面提供了一个示例，其中“每个域的唯一 IP 数”每 10 分钟更新一次。

--enable experimental feature
SET allow_experimental_refreshable_materialized_view = 1

CREATE MATERIALIZED VIEW domain_features_mv REFRESH EVERY 10 MINUTES TO domain_features AS
SELECT
   Domain,
   'number_unique_ips_per_hour' AS FeatureId,
   toStartOfHour(EventTime) AS EventTime,
   uniqExact(ClientIP) AS Value
FROM predict_bounce_subset
GROUP BY
   Domain,
   EventTime

有关可刷新物化视图的更多详细信息，请参见 此处。

创建特征后，我们可以开始将这些特征组合到我们的模型数据中——这将构成我们的训练集、验证集和测试集的基础。

此表中的行将构成我们模型的基础，每一行对应一个特征向量。为了生成这个向量，我们需要连接我们的特征。其中一些特征将来自特征表，另一些则直接来自我们的“特征子集”表，即上面的 predict_bounce_subset。

由于特征子集表包含带有标签、时间戳和实体键的事件，因此将其作为连接的基础表（左侧）是有意义的。 这也可能是最大的表，使其成为 连接左侧的明智选择。

特征将基于以下两个标准连接到此表

• 每个特征的时间戳 (EventTime) 与特征子集表 (predict_bounce_subset) 中的行最接近的时间戳。
• 特征表对应的实体列，例如 UserID 或 Domain。

要基于此等值连接 *和* 最接近的时间进行连接，需要使用 ASOF JOIN。

我们将把此连接的结果发送到一个表中，使用 INSERT INTO，我们将其称为“模型表”。 这将用于生成未来的训练集、验证集和测试集。

在探索连接之前，我们声明我们的“模型表”为 predict_bounce。

CREATE TABLE predict_bounce_model (
   Row UInt64,
   EventTime DateTime64,
   UserID UInt64,
   Domain String,
   UserAgent UInt8,
   RefererCategoryID UInt16,
   URLCategoryID UInt16,
   DomainsVisitedPerHour UInt32 COMMENT 'Number of domains visited in last hour by the user',
   UniqueIPsPerHour UInt32 COMMENT 'Number of unique ips visiting the domain per hour',
   AverageRequestTime Float32 COMMENT 'Average request time for the domain per hour',
   IsNotBounce UInt8,
) ENGINE = MergeTree
ORDER BY (Row, EventTime)

此处的 Row 列将包含数据集中每行的唯一条目。 我们将其作为 ORDER BY 的一部分，并在稍后利用它来高效地生成训练集和测试集。

为了基于实体键和时间戳连接和对齐特征，我们可以使用 ASOF。 我们如何构建 JOIN 取决于几个因素，即我们是使用“每个特征”还是“每个实体”特征表。

假设我们正在使用“每个特征”表，并且有以下可用表

• number_unique_ips_per_hour - 包含每小时访问 **每个域名** 的唯一 IP 数量。 示例如上。
• domains_visited_per_hour - **每个用户** 在过去一小时内访问的域名数量。 使用 此处的 查询生成。
• average_request_time - **每个域名** 每小时的平均请求时间。 使用 此处的 查询生成。

我们这里的 JOIN 非常简单

INSERT INTO predict_bounce_model SELECT
   rand() AS Row,
   mt.EventTime AS EventTime,
   mt.UserID AS UserID,
   mt.Domain AS Domain,
   mt.UserAgent,
   mt.RefererCategoryID,
   mt.URLCategoryID,
   dv.Value AS DomainsVisitedPerHour,
   uips.Value AS UniqueIPsPerHour,
   art.Value AS AverageRequestTime,
   mt.IsNotBounce
FROM predict_bounce_subset AS mt FINAL
ASOF JOIN domains_visited_per_hour AS dv ON (mt.UserID = dv.UserID) AND (mt.EventTime >= dv.EventTime)
ASOF JOIN number_unique_ips_per_hour AS uips ON (mt.Domain = uips.Domain) AND (mt.EventTime >= uips.EventTime)
ASOF JOIN average_request_time AS art ON (mt.Domain = art.Domain) AND (mt.EventTime >= art.EventTime)

0 rows in set. Elapsed: 13.440 sec. Processed 89.38 million rows, 3.10 GB (6.65 million rows/s., 230.36 MB/s.)
Peak memory usage: 2.94 GiB.

SELECT * FROM predict_bounce_model LIMIT 1 FORMAT Vertical

Row 1:
──────
Row:               	57
EventTime:         	2013-07-10 06:11:39.000
UserID:            	1993141920794806602
Domain:            	smeshariki.ru
UserAgent:         	7
RefererCategoryID: 	16000
URLCategoryID:     	9911
DomainsVisitedPerHour: 1
UniqueIPsPerHour:  	16479
AverageRequestTime:	182.69382
IsNotBounce:       	0

虽然类似，但如果使用“每个实体”特征表，则此连接会变得稍微复杂（且成本更高）。 假设我们有特征表 domain_features 和 user_features，并使用 这些查询 填充。

INSERT INTO predict_bounce_model SELECT
   rand() AS Row,
   mt.EventTime AS EventTime,
   mt.UserID AS UserID,
   mt.Domain AS Domain,
   mt.UserAgent,
   mt.RefererCategoryID,
   mt.URLCategoryID,
   DomainsVisitedPerHour,
   UniqueIPsPerHour,
   AverageRequestTime,
   mt.IsNotBounce
FROM predict_bounce_subset AS mt FINAL
ASOF LEFT JOIN (
   SELECT Domain, EventTime, Value.UInt64 AS UniqueIPsPerHour
   FROM domain_features
   WHERE FeatureId = 'number_unique_ips_per_hour'
) AS df ON (mt.Domain = df.Domain) AND (mt.EventTime >= df.EventTime)
ASOF LEFT JOIN (
   SELECT Domain, EventTime, Value.Float64 AS AverageRequestTime
   FROM domain_features
   WHERE FeatureId = 'average_request_time'
) AS art ON (mt.Domain = art.Domain) AND (mt.EventTime >= art.EventTime)
ASOF LEFT JOIN (
   SELECT UserID, EventTime, Value.UInt64 AS DomainsVisitedPerHour
   FROM user_features
   WHERE FeatureId = 'domains_visited_per_hour'
) AS dv ON (mt.UserID = dv.UserID) AND (mt.EventTime >= dv.EventTime)

0 rows in set. Elapsed: 12.528 sec. Processed 58.65 million rows, 3.08 GB (4.68 million rows/s., 245.66 MB/s.)
Peak memory usage: 3.16 GiB.

以上连接使用哈希连接。 24.7 版本为 ASOF JOIN 添加了对 full_sorting_merge 算法的支持。 此算法可以利用被连接表的排序顺序，从而避免在连接之前进行排序阶段。 连接表可以在任何排序和合并操作之前通过彼此的连接键进行过滤，以最大限度地减少处理的数据量。 这将使上述查询在消耗更少资源的同时保持快速。

生成模型数据后，我们可以根据需要生成训练集、验证集和测试集。 每个集合将包含不同百分比的数据，例如 80%、10%、10%。 在查询执行过程中，这些需要是一致的结果 - 我们不能允许测试数据渗入训练数据，反之亦然。 此外，结果集需要是稳定的 - 某些算法可能会受到数据传递顺序的影响，从而产生不同的结果。

为了实现结果的一致性和稳定性，同时确保查询快速返回行，我们可以利用 Row 和 EventTime 列。

假设我们希望按 EventTime 顺序获取 80% 的数据用于训练。 这可以从我们的 predict_bounce_model 表中通过一个简单的查询获得，该查询对 Row 列执行 mod 100 操作

SELECT * EXCEPT Row
FROM predict_bounce_model
WHERE (Row % 100) < 80
ORDER BY EventTime, Row ASC

我们可以使用一些简单的查询来确认这些行提供稳定的结果

SELECT
   groupBitXor(sub) AS hash,
   count() AS count
FROM
(
   SELECT sipHash64(concat(*)) AS sub
   FROM predict_bounce_model
   WHERE (Row % 100) < 80
   ORDER BY
       EventTime ASC,
       Row ASC
)

┌─────────────────hash─┬────count─┐
│ 14452214628073740040 │ 34315802 │
└──────────────────────┴──────────┘

1 row in set. Elapsed: 8.346 sec. Processed 42.89 million rows, 2.74 GB (5.14 million rows/s., 328.29 MB/s.)
Peak memory usage: 10.29 GiB.

--repeat query, omitted for brevity

┌─────────────────hash─┬────count─┐
│ 14452214628073740040 │ 34315802 │
└──────────────────────┴──────────┘

类似地，可以使用以下方法获得训练集和验证集，每个集占数据的 10%

-- validation
SELECT * EXCEPT Row
FROM predict_bounce_model
WHERE (Row % 100) BETWEEN 80 AND 89
ORDER BY EventTime, Row ASC
-- test
SELECT * EXCEPT Row
FROM predict_bounce_model
WHERE (Row % 100) BETWEEN 90 AND 100
ORDER BY EventTime, Row ASC

此处用户使用的确切方法可能取决于他们希望如何分隔训练集和测试集。 对于我们的模型，最好使用固定时间点之前的所有数据进行训练，而测试集使用此之后的所有行。 上面，我们的训练和测试样本数据包含来自整个时期的数据。 这可能会导致两个集合之间泄漏一些信息，例如来自同一页面访问的事件。 为了示例的目的，我们忽略了这一点。 我们建议根据您打算如何使用行来调整模型表上的排序键 - 请参阅 此处 以获取有关选择排序键的建议。

所描述的过程涉及多个查询和复杂的过程。 在使用 ClickHouse 构建离线特征存储时，用户通常采用以下方法之一

1. 构建自己的特征存储： 这是最先进和最复杂的方法，允许用户针对其特定数据优化流程和模式。 它通常被那些特征存储对其业务至关重要且涉及大量数据量的公司采用，例如在广告技术中。

2. Dbt + Airflow： Dbt 在管理数据转换和处理复杂查询和数据建模方面很受欢迎。 当与强大的工作流编排工具 Airflow 结合使用时，用户可以自动化和调度上述流程。 这种方法提供模块化和可维护的工作流，在自定义解决方案与现有工具之间取得平衡，以处理大量数据和复杂查询，而无需完全自定义构建的特征存储或现有解决方案施加的工作流。

3. 采用带有 ClickHouse 的特征存储： 像 Featureform 这样的特征存储集成了 ClickHouse，用于数据存储、转换和提供离线特征。 这些存储管理流程、版本特征并执行治理和合规性规则，从而降低数据科学家的数据工程复杂性。 采用这些技术取决于抽象和工作流在多大程度上适合用例。

来源：Featureform 特征存储

这篇博文讨论了使用 ClickHouse 作为离线存储来生成模型训练的特征。 模型训练完成后，可以部署模型进行预测，这需要实时数据，如用户 ID 和域名。 预先计算的特征（如“过去一小时内访问的域名”）对于预测是必要的，但在推理期间计算成本太高。 这些特征需要根据最新的数据版本提供服务，特别是对于实时预测而言。

ClickHouse 作为实时分析数据库，由于其日志结构合并树，可以处理具有低延迟和高写入工作负载的高度并发查询。 这使其适用于在在线存储中提供特征。 来自离线存储的特征可以使用现有功能物化到同一 ClickHouse 集群或不同实例中的新表中。 有关此过程的更多详细信息将在后续文章中介绍。

这篇博客概述了使用 ClickHouse 作为离线特征存储和转换引擎的常见数据建模方法。 虽然并非详尽无遗，但这些方法提供了一个起点，并与 Featureform 等特征存储中使用的技术保持一致，Featureform 与 ClickHouse 集成。 我们欢迎贡献和改进的想法。 如果您正在使用 ClickHouse 作为特征存储，请告诉我们！

了解如何在 ClickHouse 中对机器学习数据进行建模，以加速您的管道并能够快速构建可能跨越数十亿行的特征。