HIFI 从 BigQuery 迁移到 ClickHouse

我们很高兴欢迎 HIFI 加入我们的博客。请继续阅读，听听来自 John Funge (首席技术官) 和 Akash Saha (首席全栈工程师) 的分享，了解他们为什么从 BigQuery 切换到 ClickHouse，以聚合来自不同收入来源的版税数据。

音乐版税流经数十个不透明且分散的来源，使艺术家难以追踪和管理他们的收入。这种缺乏透明度造成了音乐创作者的困惑和失望。HIFI 正在构建创新的技术，为其成员提供重要的财务和商业洞察力，帮助他们做出更好的决策，推动其职业生涯发展。

我们提供的一款产品是 HIFI Enterprise，它为处理大规模音乐版税数据的复杂企业带来了智能收入处理功能，包括收购音乐版权的公司和基金，以及与世界上最知名艺术家合作的商务经理。为了使这更具体，以下是在 HIFI Enterprise 中的版税仪表板截图。

在 HIFI 的幕后，我们处理大量的版税数据。例如，单个 HIFI Enterprise 帐户可以轻松拥有 500 MB 的关联版税数据，代表超过 2500 万行流媒体和其他交易数据。所有这些版税数据都需要在客户登录后尽快加载到 UI 中，并且显然可能有许多客户同时登录。过去，加载数据可能需要长达 30 秒的时间，有时甚至由于超时而根本无法加载。大约一年前，我们开始使用 ClickHouse 来帮助存储和组织版税数据。对于那些不了解 ClickHouse 的人来说，ClickHouse 是一种快速开源的列式数据库管理系统，它允许使用 SQL 查询实时生成分析数据报告。根据该网站

ClickHouse 的性能超过所有其他列式数据库管理系统。它每秒每台服务器处理数十亿行数据和数十 GB 的数据。

从我们所看到的，它确实非常快！现在，即使关闭缓存，我们最大的数据集也能在几秒钟内加载完成。我们的客户对用户体验感到非常满意，我们相信自己能够随着增加更多客户和推出新产品而扩展规模。

ClickHouse 的速度在计算内部指标时也是一个巨大的优势，例如系统中的总版税，以及过去一年的总版税支出等指标。但是，如果您有传统关系数据库管理系统的背景，那么 ClickHouse 的速度会带来一些惊喜！诸如联接之类的操作并不总是按预期工作。如果这听起来像您，或者您只是想了解有关 ClickHouse 的更多信息，那么这篇文章适合您。

在我们切换到 ClickHouse 之前，我们将版税数据存储在 Google Cloud (GCP) 中，使用 BigQuery (BQ)。BQ 的主要挑战是定价结构。这篇文章 Quora 很好地概括了这个问题

它 [BQ] 不鼓励数据使用。与其鼓励分析师以他们能想到的任何方式查询数据库，您最终会担心需要限制他们，并制定流程来控制使用的数据量。作为一家数据驱动型公司，这种想法与我们的公司价值观相矛盾。

Google 对这个问题的解决方案是在提前购买 BQ 槽位（专用池），这样您就不必担心 BQ 使用量增加导致的成本增加。对于拥有明确且可预测使用模式的大型成熟公司来说，这是有意义的。但对于初创公司来说，模式可能每周都会发生巨大变化。我们不想费心提前确定要购买多少个 BQ 槽位——这太麻烦了！

我们考虑的另一个选择是将版税数据直接放到 GCP Cloud SQL 中，用于 PostgreSQL (PG)。这并不是 PG 的设计初衷，从我们的测试来看，它似乎不是一个可行的长期选择。我们也查看了一些最新的发展，例如 AlloyDB for PostgreSQL，它看起来很有趣，但我们不想绑定到 GCP 的专有技术。

ClickHouse 是一种独特的技术，它的工作方式不同于其他数据库。如果您来自更传统的 OLTP 世界，那么您需要了解这些差异才能获得最佳体验。我们遇到的第一个挑战是，联接需要更多地了解 ClickHouse 的工作原理才能使其高效。在深入讨论之前，先介绍一些背景信息。我们将来自艺术家版税报表的音乐版税数据存储在一个名为 normalized 的 ClickHouse 表中。每个报表行都作为表中的一行存储，其中 timesStatement 列代表报表的日期，resourceFileName 代表报表的名称。有时特定报表的日期不正确，我们需要手动更正。

由于每个报表包含数千行（在某些情况下，数十万行），因此我们希望通过与另一个仅包含两列的表联接来简单地更新 timesStatement 列，这两列分别是 statementDate（正确日期）和 resourceFileName（应更新日期的文件名称）。在传统的关系数据库中，您只需将两个表联接，并使用表 2 中的数据更新表 1。虽然这在 ClickHouse 中是可能的，但我们发现使用联接引擎表可以获得更好的性能。以下是我们更新 normalized 表中 timesStatement 列的方式

1. 将具有新报表日期的数据导入到一个名为 temp_statementDates 的新表中
2. 创建联接引擎表

CREATE TABLE statementDates_join as temp_statementDates Engine = Join(ANY, LEFT, resourceFileName);

3. 填充联接引擎表

INSERT INTO statementDates_join 
SELECT *
from temp_statementDates;

4. 使用联接引擎表更新 normalized

ALTER TABLE normalized
UPDATE timesStatement = joinGet('statementDates_join', 'statementDate', resourceFileName)
WHERE jobId = '055c45fb-6251-4050-b699-4223efae5a14'

5. 清理临时表

DROP TABLE statementDates_join
DROP TABLE temp_statementDates

通过使用 ClickHouse 特定的方法，我们能够更有效地更新记录。

我们仍然使用 PG 用于非版税数据，例如客户帐户数据和元数据。幸运的是，ClickHouse 使得 连接到 PG 数据库变得相对容易。但是，如果您尝试从 PG 数据到大型 ClickHouse 表进行左联接，并且没有仔细构造查询，您可能会遇到内存限制异常错误。例如，我们在 PG 中有一个名为 vendor_job 的表，其中包含我们运行的数据供应商作业列表及其关联的元数据。ClickHouse 中的 normalized 表包含一个名为 jobId 的列，它引用了 PG 中 vendor_job 表的 ID。为了确定 PG 中哪些 vendor_jobs 在 ClickHouse 表中没有条目，我们可以编写如下查询

SELECT COUNT(vj.id) AS no_of_vendor_jobs
FROM hifi.vendor_job AS vj
LEFT JOIN normalized AS n ON toUUID(vj.id) = n.jobId
WHERE n.jobId IS NULL
SETTINGS join_use_nulls = 1

但查询速度很慢，很容易遇到内存限制超限错误。这是由于默认的连接算法哈希将连接的右侧流式传输到内存中导致的。在这种情况下，整个 ClickHouse 表都会被加载到内存中。如果没有对 ClickHouse 内部机制的理解，很容易写出像这样的简单查询

在不增加内存限制的情况下，以下是一种更高效地执行相同类型查询的方法

SELECT COUNT(vj.id) AS no_of_vendor_jobs
FROM hifi.vendor_job AS vj
LEFT JOIN
(
  SELECT
    jobId
  FROM normalized
  GROUP BY jobId
) AS n ON toUUID(vj.id) = n.jobId
WHERE n.jobId IS NULL
SETTINGS join_use_nulls = 1

通过将右侧限制为作业 ID 列表，避免使用大量内存，并在不到 1 秒内完成！

请注意，ClickHouse 最近发布了新的连接算法来帮助处理内存密集型连接。例如，最近添加的 Grace Hash 连接 有可能让原始查询在不耗尽内存的情况下完成。但是，优化后的查询仍然可能代表更有效的解决方案。

我们遇到的另一个挑战不是 ClickHouse 本身，而是各种编程语言中的库支持。在我们开发时，最近发布的 官方 Node JS 库 尚未发布。因此，我们使用了流行的 NodeJS ClickHouse 客户端，但很快发现它在向表中插入空字符串，即使实际传递给库的原始值为 NULL。该库还提供了一个 query 方法，该方法按预期插入 NULL。所以这个 bug 仅存在于库的 insert 方法中。

深入研究库的源代码，发现 bug 似乎存在于库的 insert 方法中。具体来说，我们成功地找到了库源代码中将 NULL 值替换为空字符串的具体行

通过将代码更新为如下所示（我们计划在将来的 pull 请求中提交回原始库），我们按预期将 NULL 存储在规范化表中

static mapRowAsObject(fieldList, row) {
    return fieldList
      .map(f => {
        return encodeValue(false, row[f] != null ? row[f] : null, 'TabSeparated'); 
      })
      .join('\t');

我们仍然面临着一些 ClickHouse 在我们使用的一些第三方工具中支持方面的小问题。例如，Appsmith 存在一个未解决的 请求 来添加原生 ClickHouse 支持，但解决方法是使用 ClickHouse HTTP 接口。

除此之外，我们还需要考虑当我们准备部署自己的 ClickHouse 集群 时该怎么做。运行一个集群似乎比我们想承担的 DevOps 工作量要大，因此我们决定，一旦 GCP 的支持添加完毕，我们将切换到 ClickHouse 云 托管版本。定价结构最近已简化为仅对计算和存储收费。这使得从我们的托管解决方案切换变得轻而易举，这样我们就不必再担心扩展、备份、升级等问题。

在全球顶尖风投和排行榜榜首创作者的支持下，HIFI 汇聚了一支由开拓性技术专家和领域专家组成的团队，他们正在打造音乐行业中的金融权利组织。