TL;DR: 我们很高兴宣布 CryptoHouse，它可以通过 crypto.clickhouse.com 访问，它是由 ClickHouse 提供支持的免费区块链分析服务。
现有的公共区块链分析服务需要计划性的异步查询，但 ClickHouse 提供实时分析，通过启用即时查询响应来实现民主化访问。用户可以使用 SQL 查询此数据，该数据由于 Goldsky 的实时更新，完全免费。我们的自定义 UI 允许保存和共享查询以及基本图表，并提供示例供用户入门。我们欢迎外部贡献者参与 示例查询，以帮助我们完成此项工作。
从今天开始，CryptoHouse 的用户可以免费查询 Solana 区块、交易、代币转账、区块奖励、账户 和 代币。类似的数据集适用于以太坊。我们计划在未来几个月内扩展可用数据并公开更多区块链！
如果您对我们构建此服务的理由和方法感兴趣，请继续阅读…
对区块链分析的需求
区块链是复杂的实体，每秒可以处理数千笔交易和智能合约执行。了解它们的变化和状态对于进行明智决策的投资者和构建这些合约的开发人员至关重要。
SQL 是执行这些分析的自然语言，但这带来了两大挑战：(1) 将区块链实体转换为结构化的行式格式，以及 (2) 找到能够处理高吞吐量和潜在的 PB 级数据，同时满足用户分析查询需求的数据库。
ClickHouse 是区块链分析的标准
作为开源 OLAP 数据库，ClickHouse 由于其面向列的设计和高度并行的执行引擎，非常适合存储区块链数据。这使得查询能够在 TB 级数据上运行，从而在整个数据集上提供快速分析。因此，我们看到 ClickHouse 越来越多地用于区块链分析，Goldsky 和 Nansen 等公司在其产品中使用 ClickHouse 作为核心。
构建公共服务
任何关注 ClickHouse 并了解我们公开演示的人都知道，我们喜欢处理大型数据集并围绕它们构建服务。今年早些时候，我们发布了 ClickPy，它提供有关 Python 包下载量的免费分析。最近，adsb.exposed 以一些关于飞行数据的惊人视觉效果让社区惊叹不已。
我们一直都知道区块链具有满足我们对大型复杂数据集渴望的潜力。在流行的区块链中，我们知道 Solana 网络既有规模又有复杂性。虽然现有的公共区块链分析服务解决方案存在，但用户通常必须计划查询并等待它们异步执行 - 持久化结果以便稍后检索。作为 ClickHouse 的维护者，我们知道我们可以更好地解决这个问题，以极低的价格提供区块链的实时分析，并通过允许用户实时编写查询和检索响应来实现对数据的民主化访问。
虽然我们对 ClickHouse 方面的工作感到很舒服，但我们承认自己不是加密专家。将 Solana 区块链转换为结构化的行式格式的努力看起来很复杂，需要一些领域专业知识作为先决条件。因此，这个“挑战”一直处于暂停状态，直到今年早些时候的一些幸运的会议。
Goldsky 的加入
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
这是非常高效的，给了我们很大的灵活性，这让我们能够以接近 500k 行/秒的速度回填数据。
在边缘，我们可以轻松优化，只需要一个具有 10 个工作程序的管道来处理所有边缘数据，这相当于每秒写入大约 6000 行。
对于对 ClickHouse 中增量物化视图工作方式感兴趣的用户，我们建议阅读 这些文档 或观看 这段视频。
在查询时，用户可能会注意到，一些 Solana 区块和交易的
timestamp值为1970-01-01，height为 0。虽然 Goldsky 提供新数据，但 2024 年 6 月之前的数据是从 BigQuery 回填的。这些数据对某些时间戳和高度值有 Null 项，在 ClickHouse 中，这些值会变成它们各自类型（日期和 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 个查询。其他与内存使用相关的限制旨在确保服务稳定性和公平使用。
使用物化视图加速查询
有些查询不可避免地比其他查询计算量更大。区块链查询也经常需要扫描大量数据，对数百亿行数据进行汇总统计。为了实现这些类型的查询，我们提供 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 基金会合作期间开发的，并在测试过程中进行了优化。如果用户发现一个查询达到了他们认为社区会从中受益的配额限制，只需在项目仓库这里提出一个 issue。我们可以创建该视图并根据需要回填数据。将来，我们希望自动化此过程并公开一个构建系统，允许用户以 PR 的形式简单地提出视图提案或示例查询。
对数据进行重复数据删除
为了高效地交付事件，Goldsky 提供至少一次语义。这意味着虽然我们保证会接收链上发生的所有数据，但在极少数情况下，我们可能会多次收到同一个事件。为了解决这个问题，我们的表使用 ReplacingMergeTree 引擎.
此引擎类型会对具有相同表排序键值的事件进行重复数据删除（在大多数情况下，它是 block_timestamp 和 slot）。此重复数据删除过程在后台异步进行，并最终一致。虽然如果插入了重复事件，结果在一段时间内可能略微不准确，但鉴于行数众多以及重复事件的比例很小，我们预计这很少会成为问题，大多数查询不需要行级准确性。有关 ReplacingMergeTree 工作原理的更多详细信息，请参见这里.
使用 ClickHouse 云
用于为服务提供支持的实例托管在 ClickHouse 云中。这带来了许多好处，其中最重要的是存储和计算的分离。由于数据仅存储在对象存储中，我们可以根据用户需求独立扩展 CPU 和内存。如果我们看到对该服务的更高用户需求，我们可以简单地添加更多节点——不需要重新分片或重新分配数据。除了简化操作之外，使用对象存储意味着我们可以无限扩展（有效地）并以经济高效的方式交付该服务。
最后，我们利用了ClickHouse 查询缓存，它在今年早些时候被添加到开源版本中。
构建一个 UI
在解决了数据工程和 ClickHouse 的挑战后，我们希望提供一个用户喜欢使用的服务，因此我们公开了一个简单的 UI，允许用户编写和共享查询。
考虑到用户经常需要可视化结果，此 UI 还支持简单的多维图表，由 echarts 提供支持。
请注意，用户可以保存他们的查询以及提供的示例。但是，这些查询不会在服务中持久化，仅存在于浏览器存储中。
查询技巧
为了避免达到配额限制，我们建议用户
- 使用物化视图。这些视图故意将计算转移到插入时间，最大限度地减少了用户查询需要读取的行数。其中许多视图使用 AggregateFunction 类型，这些类型存储来自聚合的中间结果。这需要在查询时使用 -Merge 函数，例如这里。
- 对主表使用日期过滤器 - 物化视图按天聚合。对于更细粒度的分析，请参考基础表，例如 transactions。这些表包含每个事件，因此包含数百亿行。在查询这些行时，始终应用日期过滤器，以避免超过一个月的跨度。
如果用户需要更多...
虽然我们已经尽力在配额方面尽可能宽容，但我们预计有些用户会想要运行需要比 CryptoHouse 提供的更多计算能力的查询。CryptoHouse 旨在供社区使用，而不是供寻求构建服务或商业产品的组织使用，因此不支持大量查询。
如果您需要更高的配额或需要发出更多查询来实现这些目的，我们建议您联系 Goldsky，他们可以在专用 ClickHouse 实例中提供数据。这也可以根据您的访问模式和要求进行调整，从而提供卓越的性能和更低的延迟查询。
结论
我们很高兴地宣布 CryptoHouse 现已可供我们的用户和加密社区使用。这篇博文介绍了一些技术细节。
对于有兴趣了解更多详情的读者，我们将与 Goldsky 在9 月的 Solana 断点上进行一个面向开发者的研讨会，介绍该服务的内部机制以及遇到的挑战。
我们欢迎用户在公共仓库中提出问题和讨论。
