HIFI 从 BigQuery 迁移到 ClickHouse

欢迎 HIFI 作为嘉宾来到我们的博客。请继续阅读，了解 John Funge (CTO) 和 Akash Saha (首席全栈工程师) 讲述他们为什么从 BigQuery 切换到 ClickHouse，以聚合来自不同收入来源的版税数据。

音乐版税通过数十个不透明且分散的来源流动，这使得艺术家难以追踪和管理他们的收入。这种缺乏透明度的情况为音乐创作者创造了一个困惑和失望的环境。HIFI 正在构建创新技术，为其会员提供关键的财务和业务见解，以帮助他们做出更好的决策，从而推动他们的事业发展。

我们提供的产品之一是 HIFI Enterprise，它为处理大规模音乐版税数据的成熟企业带来智能收入处理，包括收购音乐版权的公司和基金，以及与世界上最著名的艺术家合作的业务经理。为了更具体地说明这一点，这里是 HIFI Enterprise 中版税仪表板的屏幕截图

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

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

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

在计算系统中的版税总额以及去年支付的版税总额等指标时，ClickHouse 的速度也是一个巨大的优势。但是，如果您具有传统关系数据库管理系统的背景，那么 ClickHouse 的速度会带来一些惊喜！像连接这样的操作不一定像预期的那样工作。如果这听起来像您，或者您只是对了解更多关于 ClickHouse 的信息感兴趣，那么这篇博文适合您。

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

它 [BQ] 不鼓励数据使用。它不是鼓励分析师以他们能想象到的任何和所有方式查询数据库，而是最终让您担心需要限制他们，并提出控制数据使用量的流程。作为一家数据驱动型公司，这种观念与我们的公司价值观相悖。

谷歌解决这个问题的方法是提前购买 BQ 插槽（专用池），这样您就不必担心 BQ 消耗的成本增加。对于一家大型成熟的公司，具有明确和可预测的使用模式，这是有道理的。但对于一家初创公司来说，模式可能会每周发生巨大变化。我们只是不想麻烦地提前弄清楚要购买多少 BQ 插槽 - 真让人头疼！

我们考虑的另一个选择只是将版税数据放入 GCP Cloud SQL for PostgreSQL (PG) 中。但这并不是 PG 的真正设计目的，从我们的测试来看，这似乎不是一个可行的长期选择。我们也关注了最近的发展，例如 AlloyDB for PostgreSQL，它看起来很有趣，但我们不想受制于 GCP 的专有技术。

ClickHouse 是一项独特的技术，其工作方式与其他数据库不同。如果您来自更传统的 OLTP 世界，您需要注意这些差异，以获得最佳体验。我们遇到的第一个挑战是，连接需要更多地了解 ClickHouse 的工作原理才能使其性能良好。在详细介绍之前，首先介绍一些背景知识。我们将来自艺术家版税报表的音乐版税数据存储在名为 normalized 的 ClickHouse 表中。每个报表行都作为表中的一行存储，其中 timesStatement 列表示报表的日期，resourceFileName 表示报表文件的名称。有时，特定报表的日期不正确，我们希望手动修复它们。

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

1. 将包含新报表日期的数据导入到新表 temp_statementDates 中
2. 创建了 JOIN 引擎表

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

3. 填充了 JOIN 引擎表

INSERT INTO statementDates_join 
SELECT *
from temp_statementDates;

4. 使用 JOIN 引擎表更新了 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 数据 LEFT JOIN 到一个大型 ClickHouse 表，如果您没有仔细构建查询，您可能会遇到内存限制异常错误。例如，我们在 PG 中有一个名为 vendor_job 的表，其中包含我们运行的数据供应商作业列表及其相关元数据。ClickHouse 中的 normalized 表有一个名为 jobId 的列，该列引用 PG 中 vendor_job 表的 id。为了确定 PG 中哪些 vendor_job 在 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 最近发布了更新的 JOIN 算法，以帮助处理内存密集型连接。例如，最近添加的 Grace Hash join 可能会允许原始查询在不耗尽内存的情况下完成。但是，优化的查询仍然可能代表更有效的解决方案。

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

深入研究库的源代码，该错误似乎存在于库的 insert 方法中。具体来说，我们设法查明了库源代码中将 NULL 值替换为空字符串的确切行

通过如下更新代码（我们计划在拉取请求中提交回原始库），我们将 NULL 按预期存储在我们的 normalized 表中

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 Cloud 托管版本。定价结构最近已简化为仅收取计算和存储费用。这使得从我们的托管解决方案切换成为一件轻而易举的事情，这样我们就无需再担心扩展、备份、升级等问题。

在全球顶级风险投资公司和排行榜首创作者的支持下，HIFI 是一支由开拓性技术专家和领域专家组成的团队，为音乐行业构建金融权利组织。