发布 CryptoHouse：由 ClickHouse 和 Goldsky 驱动的免费区块链分析

概要：我们很高兴地宣布推出 CryptoHouse，可通过 crypto.clickhouse.com 访问，是由 ClickHouse 驱动的免费区块链分析服务。

现有的公共区块链分析服务需要预定的异步查询，但 ClickHouse 提供实时分析，通过支持即时查询响应来实现访问民主化。用户可以使用 SQL 查询这些数据，数据由 Goldsky 实时更新，且无需任何费用。我们的自定义 UI 允许保存和共享查询以及基本图表，并提供示例帮助用户入门。我们欢迎外部贡献 示例查询，以帮助推进这项工作。

截至今日，CryptoHouse 用户可以免费查询 Solana 的 区块、交易、token_transfers、block_rewards、账户和 代币。以太坊也提供类似的数据集。我们计划在未来几个月内扩展可用数据并公开更多区块链！

如果您对我们构建此服务的原因和方式感兴趣，请继续阅读…

区块链是复杂的实体，每秒可以处理数千笔交易和智能合约执行。了解它们的变化和状态对于投资者做出明智的决策以及开发人员构建这些合约至关重要。

SQL 是执行这些分析的自然语言，但这带来了两个重大挑战：（1）将区块链实体转换为结构化的、面向行的格式，以及（2）找到一个数据库，能够处理高吞吐量和潜在的 PB 级数据，同时服务于用户需要的分析查询。

作为开源 OLAP 数据库，ClickHouse 因其面向列的设计和高度并行的执行引擎而成为存储区块链数据的理想选择。这使得查询能够运行在 TB 级数据之上，从而在整个数据集上实现快速分析。因此，我们看到 ClickHouse 越来越多地用于区块链分析，Goldsky 和 Nansen 等公司在其产品的核心使用了 ClickHouse。

任何关注 ClickHouse 并了解我们公开演示的人都会知道，我们喜欢处理大型数据集并围绕它们构建服务。今年早些时候，我们发布了 ClickPy，它提供关于 Python 包下载的免费分析。最近，adsb.exposed 以一些关于飞行数据的惊人视觉效果让社区惊叹。

我们早就知道区块链有潜力满足我们对大型复杂数据集的渴望。在流行的区块链中，我们知道 Solana 网络既有规模又有复杂性。虽然现有的解决方案已经存在公共区块链分析服务，但用户通常必须安排查询并等待它们异步执行 - 持久化结果以便稍后检索。作为 ClickHouse 的维护者，我们知道我们可以更好地解决这个问题，以更低的成本在区块链上提供实时分析，并通过允许用户实时编写查询和检索响应来实现数据的民主化访问。

虽然我们对 ClickHouse 方面的工作感到满意，但我们承认我们不是加密货币专家。将 Solana 区块链转换为结构化、面向行的格式的工作看起来很复杂，并且需要一些领域专业知识的先决条件。因此，“挑战”一直处于暂停状态，直到今年早些时候的一些幸运的会议。

Goldsky 是一款专注于加密货币数据基础设施的产品，为开发人员提供工具，以使用来自 Solana 和其他区块链网络的数据构建出色的实时应用程序。他们的平台通过提供诸如结构化格式的区块链事件实时数据流等服务，直接交付到数据库中，来支持开发人员构建可靠的、数据驱动的 Web3 应用程序。

虽然 Goldsky 一直以来都是 ClickHouse 的用户，用于他们自己的内部用例，但他们经常被要求将区块链数据发送到他们客户自己的 ClickHouse 集群，这些客户希望执行分析。在去年年底采访 Goldsky 的 CTO Jeff Ling 的用户故事时，我们分享了我们构建 CryptoHouse 的想法。令我们惊讶的是，Jeff 非常渴望参与并解决我们问题的数据工程组件！

Solana 每秒产生 3000-4000 笔交易，数据需要直接从节点提取。最初，Goldsky 运营开源软件以提供 Solana 支持，这相当于抓取内置的区块链节点 API。这种方法导致了一种架构，其中新区块将被检测到并放入队列中，多个工作线程负责获取所有必需的交易，然后将它们放入 Goldsky Mirror 数据流平台，延迟最小。

在实践中，每笔交易也被提取到其他数据集，例如代币转移和账户更改。调整了摄取框架，以考虑所有需要的下游转换。

随着数据现在实时摄取到平台中，为我们想要支持的所有表创建了镜像管道配置。需要进行一些转换以使数据与表匹配，这些转换针对高效存储进行了优化，并旨在满足用户想要运行的最常见查询。

# Example pipeline for blocks - this was repeated for all tables
name: clickhouse-partnership-solana
sources:
  blocks:
    dataset_name: solana.edge_blocks
    type: dataset
    version: 1.0.0
transforms:
  blocks_transform:
    sql: >
      SELECT hash as block_hash, `timestamp` AS block_timestamp, height, leader, leader_reward, previous_block_hash, slot, transaction_count 
      FROM blocks 
    primary_key: block_timestamp, slot, block_hash
sinks:
  solana_blocks_sink:
    type: clickhouse
    table: blocks
    secret_name: CLICKHOUSE_PARTNERSHIP_SOLANA
    from: blocks_transform

最后，由于最终模式需要元组，因此我们在将数据集中的 JSON 转换为正确的格式时遇到了困难。为了解决这个问题，我们使用了 Null 表引擎，并结合物化视图，以执行从 JSON 字符串到元组的 ClickHouse 特定转换。例如，以下视图和 Null 表负责接收代币数据集的插入。物化视图的结果被发送到最终的 solana.tokens 表

CREATE TABLE solana.stage_tokens
(
	`block_slot` Int64,
	`block_hash` String,
	`block_timestamp` DateTime64(6),
	`tx_signature` String,
	`retrieval_timestamp` DateTime64(6),
	`is_nft` Bool,
	`mint` String,
	`update_authority` String,
	`name` String,
	`symbol` String,
	`uri` String,
	`seller_fee_basis_points` Decimal(38, 9),
	`creators` String,
	`primary_sale_happened` Bool,
	`is_mutable` Bool
)
ENGINE = Null

CREATE MATERIALIZED VIEW solana.stage_tokens_mv TO solana.tokens
(
	`block_slot` Int64,
	`block_hash` String,
	`block_timestamp` DateTime64(6),
	`tx_signature` String,
	`retrieval_timestamp` DateTime64(6),
	`is_nft` Bool,
	`mint` String,
	`update_authority` String,
	`name` String,
	`symbol` String,
	`uri` String,
	`seller_fee_basis_points` Decimal(38, 9),
	`creators` Array(Tuple(String, UInt8, Int64)),
	`primary_sale_happened` Bool,
	`is_mutable` Bool
)
AS SELECT block_slot, block_hash, block_timestamp, tx_signature, retrieval_timestamp, is_nft, mint, update_authority, name, symbol, uri, seller_fee_basis_points, arrayMap(x -> (x.1, (x.2) = 1, x.3), CAST(creators, 'Array(Tuple(String, Int8, Int64))')) AS creators,primary_sale_happened, is_mutable
FROM solana.stage_tokens

这非常高效，并为我们提供了很大的灵活性，使我们能够以接近 50 万行/秒的速度回填数据。

在边缘，我们可以轻松优化为仅使用一个包含 10 个工作线程的管道来处理所有边缘数据，这相当于每秒写入约 6000 行。

对于有兴趣了解有关 ClickHouse 中增量物化视图如何工作的更多详细信息的用户，我们推荐这些文档或此视频。

在查询时，用户可能会注意到，某些 Solana 区块和交易的 timestamp 值为 1970-01-01，height 为 0。虽然 Goldsky 提供新数据，但 2024 年 6 月之前的行已从 BigQuery 回填。这些数据的一些时间戳和高度值具有 Null 条目，在 ClickHouse 中，这些条目成为其各自类型的默认值 - Date 和 Int64。我们计划长期纠正这些数据质量问题。

虽然 Solana 区块链的数据量对于 ClickHouse 来说并不显眼，最大的表持有大约 500TiB 的交易（如下所示），但我们希望提供任何人都可以编写 SQL 查询的功能。这带来了关于跨所有用户公平管理资源以及确保单个查询不会消耗所有可用内存或 CPU 的问题。

SELECT
   `table`,
   formatReadableSize(sum(data_compressed_bytes)) AS compressed_size,
   formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed_size,
   round(sum(data_uncompressed_bytes) / sum(data_compressed_bytes), 2) AS ratio
FROM system.parts
WHERE (database = 'solana') AND active
GROUP BY `table`
ORDER BY sum(data_compressed_bytes) DESC

┌─table─────────────────────────┬─compressed_size─┬─uncompressed_size─┬─ratio─┐
│ transactions                  │ 79.34 TiB       │ 468.91 TiB        │  5.91 │
│ transactions_non_voting       │ 17.89 TiB       │ 162.20 TiB        │  9.07 │
│ token_transfers               │ 3.08 TiB        │ 18.84 TiB         │  6.11 │
│ block_rewards                 │ 1.31 TiB        │ 10.85 TiB         │  8.28 │
│ accounts                      │ 47.82 GiB       │ 217.88 GiB        │  4.56 │
│ blocks                        │ 41.17 GiB       │ 82.64 GiB         │  2.01 │
│ tokens                        │ 3.42 GiB        │ 10.10 GiB         │  2.96 │
└───────────────────────────────┴─────────────────┴───────────────────┴───────┘

10 rows in set. Elapsed: 0.009 sec. Processed 1.42 thousand rows, 78.31 KB (158.79 thousand rows/s., 8.74 MB/s.)
Peak memory usage: 99.91 KiB.

为了确保公平使用（和可预测的成本），我们实施了 ClickHouse 使用配额，限制用户查询可以扫描的行数为 100 亿行。查询还必须在 60 秒内完成（大多数查询都能完成，这要归功于 ClickHouse 的性能），并且每用户每小时限制 60 个查询。其他关于内存使用量的限制旨在确保服务的稳定性和公平使用。

有些查询总是比其他查询在计算上更昂贵。区块链查询通常还需要扫描大量数据，提供跨数千亿行的汇总统计信息。为了启用这些类型的查询，我们提供了 ClickHouse 物化视图，它将计算从查询时间转移到插入时间。这可以显著加速某些查询，并允许用户获得跨整个数据集计算的统计信息。这些视图在数据插入时实时增量更新。例如，考虑 以下查询，该查询计算过去一个月中每天的每日费用

SELECT
  toStartOfDay(block_timestamp) as day,
  avg(fee / 1e9) AS avg_fee_sol,
  sum(fee / 1e9) as fee_sol
FROM
  solana.transactions_non_voting
WHERE block_timestamp > today() - INTERVAL 1 MONTH
GROUP BY
  1
ORDER BY 1 DESC

31 rows in set. Elapsed: 1.783 sec. Processed 2.12 billion rows, 50.98 GB (1.19 billion rows/s., 28.58 GB/s.)
Peak memory usage: 454.44 MiB.

此查询扫描约 20 亿行，并在 2 秒内完成。用户可以使用利用物化视图的 示例查询之一获得相同的结果

SELECT day,
	avgMerge(avg_fee_sol) AS avg,
	sumMerge(fee_sol) AS fee_sol
FROM solana.daily_fees_by_day
WHERE day > today() - INTERVAL 1 MONTH
GROUP BY day
ORDER BY day DESC

31 rows in set. Elapsed: 0.007 sec. Processed 1.38 thousand rows, 60.54 KB (184.41 thousand rows/s., 8.11 MB/s.)
Peak memory usage: 4.88 MiB.

这在 0.007 秒内完成。请注意，该视图按天聚合，因此对于需要更精细统计信息的查询，例如，特定日期的按小时统计信息，我们建议使用源表 solana.transactions_non_voting。

当前的视图是与 Solana 基金会合作开发的，并在测试期间进行了优化。如果用户发现某个查询达到了配额限制，并且他们认为社区会从中受益，只需在此处在项目存储库中提出一个问题。我们可以创建视图并根据需要回填数据。未来，我们希望自动化此过程，并公开一个构建系统，允许用户简单地将视图提案或示例查询作为 PR 提出。

为了高效地交付事件，Goldsky 至少提供一次语义。这意味着，虽然我们保证接收链上发生的所有数据，但在极少数情况下，我们可能会多次接收到同一个事件。为了解决这个问题，我们的表使用了 ReplacingMergeTree 引擎。

这种引擎类型对表排序键值相同的事件进行去重（在大多数情况下，这是 block_timestamp 和 slot）。此去重过程在后台异步发生，并最终保持一致。虽然如果插入重复事件，结果在一段时间内可能会略有不准确，但考虑到行数众多且重复项的百分比很小，我们预计这很少会成为问题，大多数查询不需要行级精度。有关 ReplacingMergeTree 如何工作的更多详细信息，请参阅此处。

用于驱动该服务的实例托管在 ClickHouse Cloud 中。这提供了多项优势，尤其重要的是存储和计算的分离。由于只有一份数据存储在对象存储中，我们可以根据用户需求独立扩展 CPU 和内存。如果我们看到对这项服务的用户需求增加，我们可以简单地添加更多节点 - 无需重新分片或重新分发数据。除了简化操作之外，使用对象存储还意味着我们可以无限（有效地）扩展并经济高效地交付这项服务。

最后，我们利用了 ClickHouse 查询缓存，它是在今年早些时候添加到开源中的。

在解决了数据工程和 ClickHouse 的挑战之后，我们希望提供用户喜欢使用的服务，因此我们公开了一个简单的 UI，允许用户编写和共享查询。

考虑到用户经常需要可视化结果，此 UI 还支持由 e-charts 驱动的简单多维图表。

请注意，用户可以将他们的查询与提供的示例一起保存。但是，这些查询不会持久保存在服务中，而只存在于浏览器存储中。

为了避免达到配额限制，我们建议用户

• 使用物化视图。这些视图特意将计算转移到插入时间，从而最大限度地减少用户查询需要读取的行数。其中许多视图使用 AggregateFunction 类型，该类型存储来自聚合的中间结果。这需要在查询时使用 -Merge 函数，例如此处。
• 在主表上使用日期过滤器 - 物化视图按天聚合。对于更精细的分析，请参考基本表，例如 transactions。这些表包含每个事件，因此包含数千亿行。查询这些行时，始终应用日期过滤器以避免超过一个月的跨度。

虽然我们已尽力尽可能慷慨地设置配额，但我们预计有些用户希望运行需要比 CryptoHouse 提供的更多计算能力的查询。CryptoHouse 旨在供社区使用，而不是供希望构建服务或商业产品的组织使用，因此不支持更高的查询量。

如果您需要更高的配额或需要出于这些目的发出更多查询，我们建议联系 Goldsky，他们可以在专用的 ClickHouse 实例中提供数据。这也可以根据您的访问模式和要求进行调整，从而提供卓越的性能和更低的延迟查询。

我们很高兴地宣布 CryptoHouse 现在可供我们的用户和加密货币社区使用。这篇博文涵盖了一些技术细节。

对于有兴趣了解更多详情的读者，我们将在 9 月份的 Solana breakpoint 与 Goldsky 一起举办一次以开发人员为中心的会议，涵盖该服务的内部结构和遇到的挑战。

我们欢迎用户在公共存储库中提出问题和讨论。