HIFI 从 BigQuery 迁移到 ClickHouse

我们很高兴欢迎 HIFI 作为我们的博客嘉宾。继续阅读，了解来自 John Funge (首席技术官) 和 Akash Saha (首席全栈工程师) 的观点，他们解释了为什么他们从 BigQuery 迁移到 ClickHouse 来聚合来自不同收入来源的版税数据。

音乐版税流经数十个不透明且支离破碎的来源，使艺术家难以跟踪和管理他们的收入。这种缺乏透明度为音乐创作者营造了一个混乱和失望的环境。HIFI 正在构建创新的技术，为其成员提供关键的财务和业务洞察力，帮助他们做出更好的决策，推动他们的职业发展。

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

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

ClickHouse 的性能超过所有其他列式数据库管理系统。它每秒处理数十亿行和数十吉字节的数据。

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

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

在切换到 ClickHouse 之前，我们在 Google Cloud (GCP) 中使用 BigQuery (BQ) 存储版税数据。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 列，以使用正确的日期。在传统的关系数据库中，您只需连接两个表并使用表 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 最近发布了新的 JOIN 算法，以帮助处理内存密集型联接。例如，最近添加的 Grace Hash 联接 可能允许原始查询在不耗尽内存的情况下完成。但是，优化后的查询仍然可能代表更有效的解决方案。

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

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

通过按如下所示更新代码（我们计划将其提交到原始库的 pull request 中），我们按预期将 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 是一支由开拓性技术专家和领域专家组成的团队，致力于为音乐行业打造金融权利组织。