HIFI 从 BigQuery 迁移到 ClickHouse

最新推荐文章于 2024-09-03 09:11:55 发布

ClickHouseDB

最新推荐文章于 2024-09-03 09:11:55 发布

阅读量147

点赞数

分类专栏：客户案例文章标签： clickhouse 数据库

本文链接：https://blog.csdn.net/ClickHouseDB/article/details/132304369

版权

客户案例专栏收录该内容

12 篇文章 0 订阅

订阅专栏

我们非常欢迎HIFI作为我们博客的嘉宾。请继续阅读，了解John Funge（CTO）和Akash Saha（主要全栈工程师）为何将数据聚合从BigQuery转换到ClickHouse以整合来自不同收入渠道的版税数据。

音乐版税通过许多不透明且碎片化的渠道流动，使得艺术家难以追踪和管理他们的收入。这种缺乏透明度为音乐创作者创造了一种混乱和失望的环境。HIFI正在构建创新技术，为其会员提供关键的财务和业务见解，帮助他们做出更明智的决策，推动他们的事业前进。

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

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

“ClickHouse的性能超越了所有其他列式数据库管理系统。它每秒可以处理数十亿行和数十GB的数据。”

从我们的观察来看，ClickHouse的确非常快！即使在缓存关闭的情况下，我们最大的数据集也可以在几秒钟内加载完成。我们的客户对用户体验非常满意，而且我们对能够随着增加更多客户和推出新产品而扩展的能力充满信心。

ClickHouse的速度在计算内部指标时也是巨大的优势，比如系统中的总版税以及过去一年内支付的总版税等指标。然而，如果您在传统关系型数据库管理系统方面有经验，ClickHouse的速度可能会带来一些意外之处！某些操作，比如连接（join），不一定按预期工作。如果您感觉适用于您，或者您对了解更多关于ClickHouse的知识感兴趣，那么这篇博文就是为您准备的。

在使用 ClickHouse 以前

在我们切换到ClickHouse之前，我们使用Google Cloud（GCP）的BigQuery（BQ）来存储版税数据。BQ的主要挑战在于其定价结构。这篇Quora帖子很好地解释了这个问题：

BigQuery会抑制数据的使用。与鼓励分析师以各种可能的方式查询数据库不同，您最终会担心需要限制他们并制定控制数据使用量的流程。作为一个数据驱动的公司，这种观念与我们公司的价值观相矛盾。

谷歌对这个问题的解决方案是提前购买BQ槽（专用资源池），这样您就不必担心BQ消费增加的成本。对于一个大型成熟的公司来说，具有明确定义和可预测的使用模式，这是有道理的。但对于初创公司来说，使用模式可能在每周内发生巨大变化。我们不想事先烦恼要购买多少BQ槽 - 真是让人头痛！

我们还考虑过将版税数据直接放入GCP的Cloud SQL for PostgreSQL（PG）中。但PG并非为此设计，根据我们的测试，这似乎不是可行的长期选项。我们也研究了最近的发展，例如为PostgreSQL设计的AlloyDB，它看起来很有趣，但我们不想受限于GCP的专有技术。

ClickHouse 中的 Join

ClickHouse是一种独特的技术，其工作方式与其他数据库不同。如果您来自传统的OLTP世界，您需要对这些差异有所了解，以获得最佳体验。我们首先遇到的挑战是，为了使联接（join）操作高效，您需要对ClickHouse的工作方式有更多了解。在深入了解之前，先了解一些背景情况。我们将艺术家版税结算数据存储在ClickHouse表中，名为 normalized 。每个结算行都作为表中的一行存储，其中 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特定的方法，我们能够更高效地更新记录。

联接ClickHouse和PostgreSQL数据

我们仍然使用PG来存储非版税数据，例如客户账户数据和元数据。幸运的是，ClickHouse相对容易连接到PG数据库。然而，如果你尝试从PG数据进行LEFT连接到一个大的ClickHouse表，如果你不仔细构造查询，可能会遇到内存限制异常错误。例如，我们在PG中有一个名为 vendor_job 的表，其中包含我们运行的数据供应商作业及其相关元数据的列表。ClickHouse中的 normalized 表有一个名为 jobId 的列，它引用PG中 vendor_job 表的id。要确定在ClickHouse表中没有对应记录的PG中的 vendor_jobs ，我们可以编写以下查询：

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

但是这个查询很慢，而且很容易得到内存限制超过的错误。这是由于默认的join算法hash将join的右侧（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客户端来连接ClickHouse，但很快发现即使传递给库的实际原始值是NULL，它却将空字符串插入到表中。而该库也提供了一个 query 方法，可以正常地插入NULL值。因此，这个问题只存在于库的 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（Google Cloud Platform），我们就会切换到ClickHouse Cloud的托管版本。最近，ClickHouse Cloud的定价结构简化为只收取计算和存储费用。这使得从我们目前的托管解决方案切换过来成为不言而喻的选择。使用ClickHouse Cloud，我们将不再担心扩展、备份、升级等运维方面的问题。