使用ClickHouse, Kafka连接器和ClickPipes处理实时事件流

介绍

在最近的一篇文章中，我们探讨了使用官方的ClickHouse Kafka连接器部署的实时流处理用例，使用Confluent Cloud的自定义连接器功能。为此，我们使用了谷歌发布并在公共发布/订阅中提供的Ethereum区块链数据集。

在这篇文章中，我们使用ClickPipes简化了这个架构——这是一个最近发布的完全托管的ClickHouse Cloud数据摄取服务。这使我们能够减少架构复杂性并克服之前的一些限制。

ClickPipes

ClickPipes是ClickHouse Cloud的本地功能，允许用户连接到远程Kafka代理并立即开始将数据摄取到他们的ClickHouse服务中。这释放了ClickHouse Cloud的全部潜力，使用户能够利用近实时数据进行洞察和分析。目前仅支持Kafka，但我们计划扩展支持的数据源和系统列表，将ClickPipes打造成ClickHouse Cloud的完整连接平台。有关更多详细信息，请参阅我们的公告文章。

数据集

我们使用了谷歌提供的一个公共项目中的以太坊加密货币数据集作为我们的测试数据集。

阅读本博客文章不需要事先对加密货币有任何经验，但对于那些感兴趣的读者，了解以太坊的简介将提供一个有用的概述，同时谷歌的博客文章也详细说明了该数据集的构建过程。

作为一个快速提醒，我们的数据集包含4个表格。这是完整数据的一个子集，足以回答大多数常见问题：

• 区块 - 区块是带有链中前一个区块哈希的交易批次。约有1700万行数据，每天增长约7000行。 

• 交易 - 交易是来自账户的加密签名指令。一个账户将启动一笔交易来更新以太坊网络的状态，例如将ETH从一个账户转移到另一个账户。超过20亿行数据，每天增加约100万行。

• 追踪 - 内部交易，允许查询所有以太坊地址及其余额。超过70亿行数据，每天增加约500万行。

• 合约 - "智能合约"简单地说就是在以太坊区块链上运行的程序。超过6000万行数据，每天增加约50000行。

架构的演进

在之前的博客文章中，我们详细探讨了这个数据集，并比较了BigQuery和ClickHouse，我们提出了一种基于批处理的方法来保持ClickHouse中的数据集更新。这是通过定期从公共BigQuery表（通过计划的查询）导出数据到GCS，并通过一个简单的定期计划任务将数据导入ClickHouse来实现的。

在过去，这种方法对我们的需求已经足够了，并且可以使我们的公共 sql.clickhouse.com 环境始终保持最新状态，以供我们的加密爱好者用户使用。然而，这也引入了一个不令人满意的延迟（约30分钟），即从区块链和BigQuery获取数据，到在ClickHouse中可查询。

幸运的是，Google还将这个数据集提供在几个公共的Pub/Sub主题中，提供了可以消费的事件流。使用这个源，我们只需要4分钟的延迟，即可提供与BigQuery类似的服务。

为了连接这些公共的Pub/Sub主题到ClickHouse，我们需要一个稳定的流水线，并希望最大限度地减少初始工作和未来的维护开销，因此使用了一个托管在云端的解决方案，使用我们的Kafka Connect连接器，Confluent托管了基础架构。除了减少数据在ClickHouse中可用之前的延迟之外，Kafka还允许我们缓冲多达N天的数据，并为我们提供重播功能（如果需要的话）。

为了实现这个架构，我们还需要一种可靠的方式将消息从Pub/Sub发送到Kafka。Confluent提供了一个专用的源连接器，也可以在零代码的情况下部署。结合这些连接器，形成了以下简单的架构：

如需实现此架构的更多细节，请参阅我们之前的博客文章。

尽管这种架构对于我们的需求已经足够，但它要求用户在所有组件中使用Confluent Cloud。虽然对于我们特定的问题来说这不是问题，但对于使用自管理的Kafka或MSK与ClickHouse Cloud的用户来说，ClickPipes提供了更简单的解决方案。

借助ClickPipes，我们可以进一步简化这个架构。不必部署Kafka连接器来将数据发送到ClickHouse Cloud，我们可以直接从Kafka主题中拉取数据。如下图所示：

介绍 ClickPipes

我们的消息通过Pub/Sub Connectors以JSON格式传递到Kafka主题，每个数据类型对应一个主题。有关配置的更多详细信息可以在这里找到。

流水线回顾

对于我们的新架构，我们为每个Kafka主题创建一个ClickPipe，将数据发送到与以前相同的接收表中。作为提醒，假设数据以JSON字符串的形式在名为 MessageData 的列中传递。例如，对于Blocks数据：

CREATE TABLE default.block_messages
(
  `MessageData` String,
  `AttributesMap` Map(String, String)
)
ENGINE = MergeTree
ORDER BY tuple()

SELECT *
FROM default.block_messages
LIMIT 1
FORMAT PrettyJSONEachRow

{
    "MessageData": "{\"type\": \"block\", \"number\": 17635706, \"hash\": \"0x44886d4a33deea1b76564ac6068357ee7e167a4e2b625d47e0bd048e7592bdee\", \"parent_hash\": \"0xbabfb51d4d645081c6fb28eccebf27543de094e4bb8e31d1b884a72a0a948f9b\", \"nonce\": \"0x0000000000000000\", \"sha3_uncles\": \"0x1dcc4de8dec75d7aab85b567b6ccd41ad312451b948a7413f0a142fd40d49347\", \"logs_bloom\": \"0x3061030f6188c36a303ebbb18652922694812ca9ee4b60cd10d996afc69c6c6fca611f8dd95032c057023aaf6488090d4213d28ade017aa623368462192f28f6648ac12d6618d8a9488bc46fc00985a291d600c817686c4202a4ac65956b1e25b8606480873fd9032bdcd5a04a3c1cd5a0c14c5714c0d594390455f2087ab2152f06875646c21da32253c35031024227e319a7a3998080a8c424737fd097c06ebed1837b61a8a6725a190ac099a56e0215564c1876ea669bb96a8874228c2a34cb5e340ff9a896ce002a8e47983c12c680e1132a97e954112860b71388c6ac40c9ff369205b292680a6674f47334140906ab1a0ad9488e5620397883a3ac74a5\", \"transactions_root\": \"0xe61ff16a082b53be8893e64224b0840de8f4ba246b7f2e1021227496750ce37d\", \"state_root\": \"0xdcb4abc3f10a51bb1691f5aa6b94d841360d454e44c848e04040e00d492c7a93\", \"receipts_root\": \"0x6376166e9180aa437e67b27c7119ceef073a99bcdbbbca00e5322c92661d7f4f\", \"miner\": \"0x4675c7e5baafbffbca748158becba61ef3b0a263\", \"difficulty\": 0, \"total_difficulty\": 58750003716598352816469, \"size\": 84025, \"extra_data\": \"0x6265617665726275696c642e6f7267\", \"gas_limit\": 30000000, \"gas_used\": 14672410, \"timestamp\": 1688657471, \"transaction_count\": 168, \"base_fee_per_gas\": 36613178634, \"withdrawals_root\": \"0xf213df7058a0ee7055c61c1703db0d27cfbec98a930ec0b46ae60f228aec3f16\", \"withdrawals\": [{\"index\": 9612515, \"validator_index\": 147393, \"address\": \"0x5363aedb6dcd082c77642d5bf663eabe916031f7\", \"amount\": 14340397}, {\"index\": 9612516, \"validator_index\": 147394, \"address\": \"0xbf85eb89b26f48aed0b4c28cf1281381e72bdec1\", \"amount\": 14348823}, {\"index\": 9612517, \"validator_index\": 147395, \"address\": \"0x5363aedb6dcd082c77642d5bf663eabe916031f7\", \"amount\": 14370108}, {\"index\": 9612518, \"validator_index\": 147396, \"address\": \"0x5363aedb6dcd082c77642d5bf663eabe916031f7\", \"amount\": 14389895}, {\"index\": 9612519, \"validator_index\": 147397, \"address\": \"0x5363aedb6dcd082c77642d5bf663eabe916031f7\", \"amount\": 14290983}, {\"index\": 9612520, \"validator_index\": 147398, \"address\": \"0x5363aedb6dcd082c77642d5bf663eabe916031f7\", \"amount\": 14378411}, {\"index\": 9612521, \"validator_index\": 147399, \"address\": \"0x5363aedb6dcd082c77642d5bf663eabe916031f7\", \"amount\": 14297547}, {\"index\": 9612522, \"validator_index\": 147400, \"address\": \"0x5363aedb6dcd082c77642d5bf663eabe916031f7\", \"amount\": 14272411}, {\"index\": 9612523, \"validator_index\": 147401, \"address\": \"0x5363aedb6dcd082c77642d5bf663eabe916031f7\", \"amount\": 50164441}, {\"index\": 9612524, \"validator_index\": 147402, \"address\": \"0x5363aedb6dcd082c77642d5bf663eabe916031f7\", \"amount\": 14340502}, {\"index\": 9612525, \"validator_index\": 147403, \"address\": \"0x5363aedb6dcd082c77642d5bf663eabe916031f7\", \"amount\": 14330852}, {\"index\": 9612526, \"validator_index\": 147404, \"address\": \"0x5363aedb6dcd082c77642d5bf663eabe916031f7\", \"amount\": 14398952}, {\"index\": 9612527, \"validator_index\": 147405, \"address\": \"0x5363aedb6dcd082c77642d5bf663eabe916031f7\", \"amount\": 14297302}, {\"index\": 9612528, \"validator_index\": 147406, \"address\": \"0x5363aedb6dcd082c77642d5bf663eabe916031f7\", \"amount\": 14292279}, {\"index\": 9612529, \"validator_index\": 147407, \"address\": \"0xbf85eb89b26f48aed0b4c28cf1281381e72bdec1\", \"amount\": 14275314}, {\"index\": 9612530, \"validator_index\": 147409, \"address\": \"0x5363aedb6dcd082c77642d5bf663eabe916031f7\", \"amount\": 14297649}], \"item_id\": \"block_0x44886d4a33deea1b76564ac6068357ee7e167a4e2b625d47e0bd048e7592bdee\", \"item_timestamp\": \"2023-07-06T15:31:11Z\"}",
    "AttributesMap": {
        "item_id": "block_0x44886d4a33deea1b76564ac6068357ee7e167a4e2b625d47e0bd048e7592bdee",
        "item_timestamp": "2023-07-06T15:31:11Z"
    }
}

为了将这些数据提取到我们的最终表中，我们使用了一个利用JSONExtract函数族的物化视图。

CREATE TABLE blocks
(
  `number` UInt32 CODEC(Delta(4), ZSTD(1)),
  `hash` String,
  `parent_hash` String,
  `nonce` String,
  `sha3_uncles` String,
  `logs_bloom` String,
  `transactions_root` String,
  `state_root` String,
  `receipts_root` String,
  `miner` String,
  `difficulty` Decimal(38, 0),
  `total_difficulty` Decimal(38, 0),
  `size` UInt32 CODEC(Delta(4), ZSTD(1)),
  `extra_data` String,
  `gas_limit` UInt32 CODEC(Delta(4), ZSTD(1)),
  `gas_used` UInt32 CODEC(Delta(4), ZSTD(1)),
  `timestamp` DateTime CODEC(Delta(4), ZSTD(1)),
  `transaction_count` UInt16,
  `base_fee_per_gas` UInt64,
  `withdrawals_root` String,
  `withdrawals.index` Array(UInt64),
  `withdrawals.validator_index` Array(Int64),
  `withdrawals.address` Array(String),
  `withdrawals.amount` Array(UInt64)
)
ENGINE = MergeTree
ORDER BY timestamp

这个视图在行被插入到 block_messages 表时执行，将数据转换并将结果插入到最终的 blocks 表中。我们目标的blocks表架构如下：

CREATE TABLE blocks
(
  `number` UInt32 CODEC(Delta(4), ZSTD(1)),
  `hash` String,
  `parent_hash` String,
  `nonce` String,
  `sha3_uncles` String,
  `logs_bloom` String,
  `transactions_root` String,
  `state_root` String,
  `receipts_root` String,
  `miner` String,
  `difficulty` Decimal(38, 0),
  `total_difficulty` Decimal(38, 0),
  `size` UInt32 CODEC(Delta(4), ZSTD(1)),
  `extra_data` String,
  `gas_limit` UInt32 CODEC(Delta(4), ZSTD(1)),
  `gas_used` UInt32 CODEC(Delta(4), ZSTD(1)),
  `timestamp` DateTime CODEC(Delta(4), ZSTD(1)),
  `transaction_count` UInt16,
  `base_fee_per_gas` UInt64,
  `withdrawals_root` String,
  `withdrawals.index` Array(UInt64),
  `withdrawals.validator_index` Array(Int64),
  `withdrawals.address` Array(String),
  `withdrawals.amount` Array(UInt64)
)
ENGINE = MergeTree
ORDER BY timestamp

同样的过程也适用于我们的其他数据类型，如traces、transactions和contracts。

配置 ClickPipes

配置ClickPipes非常简单。首先，我们需要从Confluent Cluster获取API密钥和端点。请注意如下所示，我们记录了引导服务器地址。

现在，我们可以使用这些凭据和端点在Cloud控制台上创建ClickPipe。下面我们为blocks数据集创建一个ClickPipe，从Confluent Cloud的 block_messages  Kafka主题中消费消息，并插入到同名的表中。请注意在选择Confluent Cloud作为源时，请遵循以下提示，我们希望在未来的版本中进行完善：

• 使用API密钥和密钥作为登录和密码。

• 确保消费者组设置为唯一的字符串。在未来的版本中，我们可能会预先填充此设置。

• 使用之前复制的引导端点作为服务器地址。

正如所示，我们将Kafka主题映射到现有的表 - 后者在我们的情况下已经预先创建。如果需要，用户还可以创建一个新的表，将消息字段映射到不同名称的列。

这个架构现在被用于在sql.clickhouse.com上维护这个数据集，用户可以免费查询最新的以太坊区块链，仅有四分钟的延迟！

总结

在本篇博客文章中，我们演示了新的ClickHouse Cloud功能ClickPipes，并展示了如何使用它来简化之前博客文章中呈现的流处理架构。ClickPipes是ClickHouse Cloud的本地功能，目前处于私有预览阶段。有兴趣尝试ClickPipes的用户可以在此处加入等待列表。

您可以按照文档中的说明创建您的第一个ClickPipes。更多详细信息请参阅：

• ClickPipes website：

  https://clickhouse.com/cloud/clickpipes

• Video demonstration：

  https://www.youtube.com/watch?v=rSUHqyqdRuk

• Documentation：

  https://clickhouse.com/docs/en/integrations/clickpipes

• Announcement blog：

  https://clickhouse.com/blog/clickhouse-cloud-clickpipes-for-kafka-managed-ingestion-service

联系我们

手机号：13910395701

邮箱：Tracy.Wang@clickhouse.com

满足您所有的在线分析列式数据库管理需求