HIFI 从 BigQuery 迁移到 ClickHouse

我们很高兴欢迎 HIFI 作为嘉宾来到我们的博客。继续阅读，了解来自 John Funge（CTO）和 Akash Saha（首席全栈工程师）的信息，了解他们为何从 BigQuery 迁移到 ClickHouse 来聚合来自不同收入来源的版权费数据。

音乐版权费流经数十个不透明且碎片化的来源，这使得艺术家难以跟踪和管理他们的收入。这种缺乏透明度的情况给音乐创作者带来了困惑和失望。HIFI 正在构建创新的技术，为其成员提供关键的财务和业务洞察，帮助他们做出更好的决策，推动其事业向前发展。

我们提供的产品之一是 HIFI Enterprise，它为处理海量音乐版权费数据的复杂企业带来了智能收入处理功能，包括收购音乐版权的公司和基金，以及与全球最著名艺术家合作的商业经理。为了更具体地说明这一点，以下是 HIFI Enterprise 中的版权费仪表板截图

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

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

从我们观察到的情况来看，它确实非常快！现在，即使关闭缓存，我们最大的数据集也只需几秒钟即可加载。我们的客户对用户体验非常满意，我们相信自己能够随着客户数量的增加和新产品的推出而扩展规模。

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

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

它 [BQ] 不鼓励数据使用。您不会鼓励分析师以他们能想到的所有方式查询数据库，而是会担心需要限制他们，并想出控制数据使用量的流程。作为一家数据驱动的公司，这个想法与我们的公司价值观相矛盾。

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

我们考虑的另一个选择只是将版权费数据放入 GCP 的 Cloud SQL for PostgreSQL (PG) 中。这并不是 PG 的设计初衷，根据我们的测试，它似乎不是一个可行的长期选择。我们也研究了最近的发展，例如 AlloyDB for PostgreSQL，它看起来很有趣，但我们不想绑定到 GCP 的专有技术。

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

由于每个报表包含数千行（在某些情况下为数十万行），因此我们希望通过与仅包含两列 statementDate（正确日期）和 resourceFileName（应更新日期的文件名称）的单独表联接来简单地更新 timesStatement 列。在传统的关系型数据库中，您只需联接两个表，然后使用 table2 中的数据更新 table1。虽然这在 ClickHouse 中是可能的，但我们发现可以使用 JOIN 引擎表获得更好的性能。以下是如何更新 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 最近发布了更新的 JOIN 算法来帮助处理内存密集型连接。例如，最近添加的Grace Hash 连接可能会允许原始查询完成而不会耗尽内存。但是，优化的查询仍然可能代表更有效的解决方案。

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

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

通过按如下所示更新代码（我们计划将其提交到原始库的拉取请求中），我们按预期在规范化表中存储 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 Cloud托管版本。定价结构最近已简化为仅收取计算和存储费用。这使得从我们的托管解决方案切换变得轻而易举，因此我们不再需要担心扩展、备份、升级等问题。

在全球顶级风投和热门创作者的支持下，HIFI 是一支由开拓性技术专家和领域专家组成的团队，致力于为音乐行业构建金融权利组织。