我们在 ClickHouse 中构建内部数据仓库：一年后的回顾

一年前，我的同事 Dmitry Pavlov 描述了 我们如何在 ClickHouse Cloud 上构建 ClickHouse 的内部数据仓库，代号为“DWH”。这个 DWH 通过收集来自各种源系统的数据，根据数据模型对其进行处理，并提供对数据的访问，为 ClickHouse, Inc. 提供内部业务分析和报告服务。最初的博文涵盖了构建 DWH 的几个方面，例如使用非规范化数据集市、实施安全功能、管理底层基础设施等等。

然而，随着 ClickHouse 的发展，其基于 ClickHouse Cloud 的 DWH 也随之发展壮大。在这篇博文中，我将分享 DWH 在过去一年中如何演变以支持更多样化的用户、数据源和访问点。特别是，我们持续使用 ClickHouse 并将 dbt 作为堆栈中的两个主要组件，这使得 DWH 能够支持将更多实时数据处理纳入常规批处理报告，这是其他传统数据仓库一直缺乏的功能。因此，我们能够从数据中做出更好的决策。

数据仓库是一个集中的存储库，用于存储从组织内不同来源收集的数据，以支持决策制定，通常是业务决策。以下是我们 DWH 架构的概述。

目前，我们已配置了 19 个原始数据源，并使用每小时或每日插入作业。每天写入 DWH 的数据未压缩时约有 60 亿行和 50 TB。然后 ClickHouse 会压缩并存储这些数据。目前，我们存储了在过去两年中收集的 470 TB（压缩后）数据，过去 30 天增加了 23%。在任何一周，我们通常会看到大约 70 位不同的用户生成至少一个 SELECT 查询。

DWH 是我们组织中一个业务关键系统，因为包括领导层、产品、工程、营销、销售和财务在内的各个业务部门都依赖 DWH 获取数据。此外，DWH 预生产环境已被用作金丝雀环境，用于在 ClickHouse Cloud 中执行更广泛的升级之前测试开源 ClickHouse 版本。

我们 DWH 的第一个版本是用作一个工具，用于从少量业务系统导入相对较小的数据量。导入这些原始数据后，会对其进行处理以创建批处理报告，例如“试验次数”或“转化次数”，这些是数据仓库的基础。批处理报告主要在 Airflow 中配置，我们完全没有使用 dbt。但是，随着我们添加数据源、定义更复杂的业务指标并服务越来越多的内部利益相关者，这种方法无法扩展。dbt 是数据工程师和分析师用来构建数据管道的一个开源工具，它允许团队编写和迭代用于转换数据的模块化 SQL 代码。将 dbt 引入堆栈很有帮助，因为它在一个位置集中了与批处理报告相关的转换逻辑。

例如，当我们添加新的数据源时，dbt 允许我们使用时间函数轻松地联接在不同时间提供的数据，并在准备好时触发后续流程。例如，考虑以下场景：来自不同数据源的数据在不同时间插入，例如晚上 7:26 和晚上 10:01，即使用于联接数据的逻辑时间戳是一个小时的增量，例如晚上 7:00。此逻辑可能高度定制化，并且会根据业务需求而发生变化。当有一个像 dbt 这样的工具来跟踪依赖项并自动生成谱系图作为文档时，对 19 个不同的数据源编码此逻辑要容易得多。

此外，我们定义的指标变得更加复杂。跨日期范围的简单 count() 和 sum() 让位于窗口函数和数据差异，这些函数根据存储在外部表中的条件进行过滤。dbt 与 ClickHouse 的 SQL 方言相结合是管理这种日益增长的复杂性的关键，因为 SQL 成为我们编码此业务逻辑的方式。dbt 成为在 DWH 中报告批处理业务指标更具可扩展性方式的基础。

能够将复杂的业务指标定义为批处理报告流程的一部分，是企业数据仓库的基本要求。ClickHouse 以其在 PB 级数据集上的性能而闻名，可以轻松处理我们输入的数据。但是，我们的 DWH 真正擅长的是处理不符合严格模式或关系的新型和独特的实时数据格式（对这些格式的支持正在不断 改进）。通常，这些数据源的数据量也大得多。

例如，在我们的 DWH 中，每天大约有 40 亿行插入到一张表中——整张表接近万亿行。再举一个例子，我们从 Google Analytics 导入事件，Google Analytics 将事件参数存储为嵌套数组，这些数组表示 clickhouse.com 上的网站活动。类似地，来自我们 NoSQL 控制平面数据库的卸载类似于具有灵活模式的嵌套 JSON 结构。

在我们的基础批处理报告到位后，我们开始将越来越多的实时数据源配置到我们的报告中。我们的用户反馈说，他们发现这种数据非常有价值且更直观，即使数据结构不太严格，并且在运行查询时需要一些轻量级的处理（例如，从 JSON 列中提取字段）。我们的营销团队能够更好地了解我们网站上哪些页面最受欢迎。我们的支持工程师能够通过深入了解他们最近的云活动日志来诊断客户查询的问题。我们的产品团队能够定义更多自定义逻辑来跟踪转化事件。

在数据仓库（DWH）中，我们将这些实时数据以其原始格式（导入时的格式）和转换后的格式（例如，实时事件的聚合）两种方式公开。 ClickHouse 丰富的函数库 和支持的数据格式使得探索实时数据的原始格式变得非常容易，这意味着用户能够使用 SQL 客户端进行即席分析，而无需数据工程师的帮助。创建实时数据的聚合也是可能的 - 我们使用 dbt 在实时数据到达 DWH 时定义聚合，然后将这些聚合存储在单独的表中。我们使用 dbt 是因为我们已经在其他地方使用 dbt，因此将其集中到现有工具中更容易，但是这些聚合也可以使用 ClickHouse 的功能（例如 物化视图）在本地配置。然后将这些聚合与现有的报表结合起来，以跟踪诸如“查询失败的客户数量”之类的指标。

这种设置使我们能够为实时数据配置批处理报表样式的管道，同时保留基础记录，以便在需要更深入调查时可以使用。在一个环境中，ClickHouse Cloud + dbt 允许我们的用户将批处理报表与实时数据流结合起来。

在 DWH 的第一个迭代中，我们将 Apache Superset 配置为 BI 工具，供用户访问数据。Superset 提供了许多关键功能，例如仪表板、SQL 客户端、警报和用户管理，这些功能在一个部署中提供，对维护 DWH 的团队很有吸引力。此外，它还是开源软件。但是，它也存在一些局限性。具体来说，我们发现 Superset 的 SQL 客户端存在 bug，这对用户体验产生了非常实际的影响。

为了解决这个问题，我们使用 ClickHouse Cloud 的原生 SQL 控制台开放了对 DWH 的访问。对于编写即席 SQL 查询以及探索各种数据库表和视图，SQL 控制台对用户来说要好得多。我们使用 ClickHouse Cloud API 管理用户对敏感数据的访问，并使用 Google Groups 为用户分配角色。一些 DWH 用户分享了使用 SQL 控制台的体验优于 Superset 的原生 SQL 客户端。

对于超出编写即席查询的更专业的用例，我们将与 Growthbook 的连接配置为使用 DWH 中的数据运行 A/B 测试的一种方式。Growthbook 和 ClickHouse Cloud 之间的连接设置只需几分钟，并为我们提供了一个开箱即用的工具来分析 A/B 测试的结果（这些结果是对 DWH 的 SELECT 查询）。Growthbook 直接查询 DWH ClickHouse Cloud 服务，这意味着使用非常原始的日志级数据运行实验非常简单。

最后，我们从 ClickHouse Cloud 到 Salesforce 设置了一个数据导出作业，以便我们的销售团队可以直接在我们的 CRM Salesforce 中使用来自 DWH 的数据。此连接的设置有点棘手，因为我们的 Salesforce 部署不支持来自静态 IP 地址的查询，这是一个限制，会降低连接的安全性。最终，我们最终将数据从 DWH 推送到 S3 存储桶中，然后让 Salesforce 查询此 S3 存储桶。

展望未来，我们很高兴通过配置其他数据源和吸纳更多用户来继续扩展 DWH。为了确保 DWH 的扩展，我们希望将工作负载分解为独立可扩展的计算组，我们相信这将带来性能提升和成本节约。

在 DWH 首次创建时，我们只有一个“每小时加载 DAG”查询，这是一个在 Airflow 中计划的 ETL 作业，负责将所有新数据整合到 DWH 中。选择这种架构是因为我们只有有限数量的数据源，并且数据模型很简单，仅包含两个实体。但是，随着我们添加了更多数据源和数据模型，我们很快意识到这种方法不可扩展：插入作业执行时间过长，并且由于不同数据模型之间的依赖关系，仅一个错误就会危及所有数据的插入。在引入 dbt 后，我们将此作业重构为九个不同的流程，并注意到服务可靠性有所提高。大多数这些作业仍然每小时运行一次，这意味着我们的 ClickHouse Cloud 服务必须 24x7 运行才能支持新数据的插入。

我们期待的一个即将推出的 ClickHouse Cloud 功能是 计算-计算分离。此功能使我们能够将 DWH 的计算资源分离到多个服务中，每个服务都可以根据需要独立扩展或缩减。这意味着我们可以为 BI 工具提供一个只读服务，为关键 ETL 作业提供一个 24x7 读写服务，以及为不太关键的 ETL 作业提供第二个读写服务，其中数据仅需每天刷新一次。然后可以在此第二个读写服务不活动时将其缩减，从而节省大量成本。通过这种方式，我们看到了分散计算资源并使不同的计算组彼此独立工作的潜力。

在人工智能时代，我也很高兴看到我们如何将人工智能功能整合到我们的 DWH 中。ClickHouse Cloud 已经支持一个 AI 查询构建器，可以帮助用户编写简单的 SQL 查询。但是，从长远来看，我对 AI 业务分析师的前景感到兴奋，它可以帮助不熟悉 SQL 的 DWH 用户。此外，许多 DWH 用户目前查询 DWH 以编写每周/每月/每季度的报表，这也是我想通过使用现有报表和查询提供上下文来委托给 AI 的另一个流程。

在 ClickHouse，我们每周都会举行一次领导力会议，以审查和讨论公司最重要的指标。许多这些指标都是直接从 DWH 中报告的。随着我们组织的规模扩大，以及 DWH 的规模扩大，我意识到数据仓库除了显示漂亮的仪表板和生成报表之外，还发挥着重要的作用；数据仓库是复杂而强大的机器，它们通过将整个组织的人员与单一的事实来源保持一致，促进讨论并激发行动。它们允许工程师、营销人员、销售人员、产品经理和高管使用相同的信息并据此做出决策。对于我们的数据仓库，ClickHouse 通过轻松地将批处理报表与实时数据丰富，扩展了可查询数据的范围。