CryptoHouse：由 ClickHouse 和 Goldsky 支持的免费区块链分析服务(ClickHouse 博客)-数字星空

我们很高兴地宣布 CryptoHouse，在 crypto.clickhouse.com 上可访问，这是一个由 ClickHouse 提供支持的免费区块链分析服务。

https://crypto.clickhouse.com/

现有的公共区块链分析服务通常需要定时、异步查询，而 ClickHouse 提供实时分析，通过即时查询响应来普及访问权限。用户可以使用 SQL 查询这些数据，这些数据由于 Goldsky 的支持而得以实时更新，并且完全免费。我们定制的用户界面允许保存和分享查询以及基本图表制作，并提供了示例帮助用户开始使用。我们欢迎外部贡献者参与到示例查询中来协助这一努力。

从今天起，CryptoHouse 用户可以免费查询 Solana 的区块、交易、代币转账、区块奖励、账户和代币。对于 Ethereum 同样提供了类似的数据库。我们计划在未来几个月内扩展可用数据并公开更多的区块链！

如果您对为什么及如何构建这项服务感兴趣，请继续阅读……

对区块链分析的需求

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

SQL 是进行这些分析的自然语言，但这带来了两个重大挑战：(1) 将区块链实体转换为结构化的、行导向的格式；(2) 找到一个能够处理高吞吐量和潜在的数拍字节数据同时满足用户分析查询需求的数据库。

ClickHouse 是区块链分析的标准

作为开源的 OLAP 数据库，ClickHouse 非常适合存储区块链数据，因为它采用了列式设计和高度并行的执行引擎。这使得查询可以在数太字节的数据上运行，实现整个数据集上的快速分析。因此，我们看到越来越多的公司（如 Goldsky 和 Nansen）在他们的区块链分析服务中核心使用 ClickHouse。

构建公共服务

任何关注 ClickHouse 并了解我们的公共演示的人士都知道，我们喜欢处理大型数据集并围绕它们构建服务。今年早些时候，我们发布了 ClickPy，它提供了关于 Python 包下载的免费分析。最近，adsb.exposed 以其在飞行数据方面的惊人可视化震撼了社区。

我们早就知道，区块链具有满足我们对大型复杂数据集需求的潜力。在流行的区块链中，我们知道 Solana 网络既庞大又复杂。虽然已经存在公共区块链分析服务的解决方案，但用户通常需要安排查询并等待它们异步执行——将结果持久化以供后续检索。作为 ClickHouse 的维护者，我们知道我们可以更好地解决这个问题，以极低的成本提供实时的区块链分析，并通过让用户编写查询并在实时获取响应的方式普及数据访问。

虽然我们在 ClickHouse 方面的工作感到自信，但我们承认自己并不是加密货币专家。将 Solana 区块链转换为结构化的行导向格式看起来需要一定的领域专业知识。因此，“挑战”一直搁置，直到今年早些时候的一些幸运会面。

进入 Goldsky

Goldsky 是一款专注于加密货币数据基础设施的产品，为开发者提供工具，使他们能够利用来自 Solana 和其他区块链网络的数据构建出色的实时应用。他们的平台通过提供服务（如以结构化格式实时流传输区块链事件，并直接写入数据库）支持开发者构建可靠的数据驱动型 Web3 应用。

https://goldsky.com/

虽然 Goldsky 已经使用 ClickHouse 一段时间来满足自己的内部使用场景，但他们经常被要求向希望进行分析的客户的自有 ClickHouse 集群发送区块链数据。去年年末，在采访 Goldsky 的首席技术官 Jeff Ling 时，我们分享了构建 CryptoHouse 的想法。令我们惊讶的是，Jeff 很乐意参与进来并解决我们面临的数据工程问题！

数据工程挑战

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

https://goldsky.com/products/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 表引擎，结合 Materialized View，实现了从 JSON 字符串到元组的 ClickHouse 特定转换。例如，以下视图和 Null 表负责接收代币数据集的插入操作。Materialized View 的结果被发送到最终的 solana.tokens 表：

https://clickhouse.com/docs/en/engines/table-engines/special/null

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

这种方法非常高效，并为我们提供了极大的灵活性，使我们能够以接近 500k 行/秒的速度回填数据。

在边缘处，我们很容易优化为只有一个包含 10 个工作进程的管道来处理所有边缘数据，这相当于大约每秒写入 6000 行。

对于想了解更多有关 ClickHouse 中增量 Materialized View 如何工作的用户，我们推荐这些文档或这个视频。

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

ClickHouse 的挑战

确保公平使用

尽管 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 个查询。其他关于内存使用的限制旨在确保服务稳定性和公平使用。

https://clickhouse.com/docs/en/operations/quotas

通过 Materialized Views 加速查询

有些查询不可避免地比其他查询更耗费计算资源。区块链查询通常需要扫描大量数据，为数百亿行数据提供汇总统计信息。为了实现这类查询，我们提供了 ClickHouse Materialized Views，它将计算从查询时间转移到插入时间。这可以显著加速某些查询，并允许用户获得跨越整个数据集的统计数据。这些视图随着数据的插入而实时增量更新。举个例子，考虑以下查询，它计算过去一个月每天的费用：

https://crypto.clickhouse.com/?query=U0VMRUNUCiAgdG9TdGFydE9mSG91cihibG9ja190aW1lc3RhbXApIGFzIGhvdXIsCiAgYXZnKGZlZSAvIDFlOSkgQVMgYXZnX2ZlZV9zb2wsCiAgc3VtKGZlZSAvIDFlOSkgYXMgZmVlX3NvbApGUk9NCiAgc29sYW5hLnRyYW5zYWN0aW9uc19ub25fdm90aW5nCldIRVJFIGJsb2NrX3RpbWVzdGFtcCA-IHRvZGF5KCkgLSBJTlRFUlZBTCAxIE1PTlRICkdST1VQIEJZCiAgMQpPUkRFUiBCWQogIDEgQVND

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 秒内完成。用户可以通过使用其中一个示例查询来获得相同的结果，这些示例查询利用了 Materialized View：

https://crypto.clickhouse.com/?query=LS1EYWlseSBmZWVzLiBUaGlzIHVzZXMgYSBtYXRlcmlhbGl6ZWQgdmlldywgZ3JvdXBpbmcgYnkgZGF5LiBGb3IgbW9yZSBncmFudWxhciBwZXJpb2RzIHVzZSB0aGUgc29sYW5hLnRyYW5zYWN0aW9uc19ub25fdm90aW5nIHRhYmxlIGUuZy4gaHR0cHM6Ly9jcnlwdG8uY2xpY2tob3VzZS5jb20_cXVlcnk9VTBWTVJVTlVJSFJ2VTNSaGNuUlBaa2h2ZFhJb1miteHZZMnRmZEdsdFpYTjBZVzF3S1NCaGN5Qm9iM1Z5TENCaGRtY29abVZsTHpGbE9Ta2dRVk1nWVhablgyWmxaVjl6YjJ3c0lITjFiU2htWldVdk1XVTVLU0JoY3lCbVpXVmZjMjlzSUVaU1QwMGdjMjlzWVc1aExuUnlZVzV6WVdOMGFXOXVjMTl1YjI1ZmRtOTBhVzVuSUZkSVJWSkZJR0pzYjJOclgzUnBiV1Z6ZEdGdGNEbzZaR0YwWlNBOUlDQW5NakF5TkMwd055MHlOaWNnSUVkU1QxVlFJRUpaSURFZ1QxSkVSVklnUWxrZ01TQkJVME0KU0VMRUNUCiAgZGF5LAogIGF2Z01lcmdlKGF2Z19mZWVfc29sKSBhcyBhdmcsCiAgc3VtTWVyZ2UoZmVlX3NvbCkgYXMgZmVlX3NvbApGUk9NCiAgc29sYW5hLmRhaWx5X2ZlZXNfYnlfZGF5IFdIRVJFIGRheSA-IHRvZGF5KCkgLSBJTlRFUlZBTCAxIE1PTlRICkdST1VQIEJZCiAgZGF5Ck9SREVSIEJZCiAgZGF5IERFU0M

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 引擎。

https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/replacingmergetree

这种引擎类型会对具有相同表排序键值（在大多数情况下，这是 block_timestamp 和 slot）的事件进行去重。这个去重过程在后台异步进行，并最终达到一致性。如果插入了重复事件，则结果可能在一段时间内略有不准确，但由于行数庞大且重复事件的比例极小，我们预计这种情况很少出现，大多数查询不需要行级别的准确性。有关 ReplacingMergeTree 工作原理的更多详细信息，请参见这里。

https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/replacingmergetree

使用 ClickHouse Cloud

为提供服务而使用的实例托管在 ClickHouse Cloud 中。这提供了多种好处，其中最重要的是存储和计算的分离。仅需在对象存储中存储一份数据，我们就可以根据用户需求独立扩展 CPU 和内存。如果我们看到对该服务有更高的用户需求，我们只需简单地添加更多节点即可——无需重新分区或重新分布数据。除了简化操作外，使用对象存储意味着我们可以无限扩展（实际上）并以成本效益高的方式提供这项服务。

最后，我们利用了 ClickHouse 查询缓存，它在今年早些时候被加入到了开源版本中。

https://clickhouse.com/docs/en/operations/query-cache

构建用户界面

解决了数据工程和 ClickHouse 面临的挑战后，我们希望提供一项用户喜欢使用的服务，因此我们提供了一个简单的用户界面，允许用户编写和分享查询。

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

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

查询技巧

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

使用 Materialized Views。 这些视图故意将计算移到插入时间，从而最小化用户查询需要读取的行数。许多这些视图使用 AggregateFunction 类型，它们存储聚合的中间结果。在查询时，这需要使用 -Merge 函数，例如这里。
在主表上使用日期过滤器 - Materialized Views 按天聚合。对于更精细的分析，请参考基础表，如交易表。这些表包含每个事件，并因此拥有数百亿行。在查询这些行时，始终应用日期过滤器以避免超过一个月的时间跨度。