Unlocking Blockchain Analysis with CryptoHouse

·

Blockchain technology generates vast amounts of complex data, making real-time analysis essential for developers, investors, and enthusiasts. CryptoHouse offers a powerful solution for exploring this data through instant SQL queries, providing free and accessible insights into blockchain networks like Solana and Ethereum.

What Is CryptoHouse?

CryptoHouse is a free, real-time blockchain analytics service powered by ClickHouse and supported by Goldsky. Unlike traditional services that rely on scheduled, asynchronous queries, CryptoHouse enables users to interact with live data through a user-friendly SQL interface. This platform allows you to explore blocks, transactions, token transfers, and more without delays or costs.

Key features include:

👉 Explore real-time blockchain tools

The Growing Need for Blockchain Analytics

Blockchains process thousands of transactions and smart contract executions every second. Understanding these activities is critical for making informed decisions, whether you're an investor tracking market trends or a developer building decentralized applications.

SQL is the natural language for this type of analysis, but it comes with challenges:

  1. Converting blockchain entities into structured, row-based formats.
  2. Finding a database capable of handling high-throughput data and user queries simultaneously.

Why ClickHouse Is Ideal for Blockchain Data

ClickHouse is an open-source OLAP database designed for high-performance analytics. Its columnar storage format and parallel processing engine allow it to query terabytes of data in seconds. This makes it perfectly suited for blockchain datasets, which can grow to petabyte scales.

Companies like Goldsky and Nansen already use ClickHouse as the backbone of their blockchain analytics services, highlighting its reliability and efficiency.

How CryptoHouse Was Built

The Role of Goldsky

While ClickHouse provides the database power, Goldsky delivers the real-time data infrastructure. Goldsky specializes in streaming blockchain events from networks like Solana into structured formats, ready for analysis. Their platform processes data directly from nodes, transforms it, and loads it into ClickHouse with minimal latency.

This partnership allowed CryptoHouse to overcome the data engineering hurdles of blockchain ETL (extract, transform, load), ensuring fresh and accurate data.

Technical Architecture

Goldsky’s pipeline ingests Solana data at a rate of 3,000–4,000 transactions per second. The data is transformed and mirrored into ClickHouse using configurations like the one below:

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

To handle JSON-to-tuple conversions, the team used ClickHouse’s Null table engine combined with materialized views. This approach achieved ingestion speeds of nearly 500,000 rows per second during backfills.

Overcoming Challenges

Ensuring Fair Usage

To prevent any single user from monopolizing resources, CryptoHouse enforces usage quotas:

These limits ensure stability and fair access for all users.

Accelerating Queries with Materialized Views

Some analyses require aggregating billions of rows. To optimize performance, CryptoHouse uses materialized views that precompute results during data ingestion. For example, a query calculating daily fees over the past month:

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

This query processes 2.12 billion rows in under two seconds. Using a materialized view reduces the execution time to just 0.007 seconds.

Deduplication with ReplacingMergeTree

Goldsky provides at-least-once delivery semantics, meaning duplicate events can occur. To address this, CryptoHouse uses the ReplacingMergeTree engine, which deduplicates data based on sorting keys like block_timestamp and slot. This ensures eventual consistency without sacrificing performance.

Scalability with ClickHouse Cloud

CryptoHouse runs on ClickHouse Cloud, which separates storage and compute. This allows the service to scale independently based on demand, using object storage for cost-effective, unlimited expansion. The platform also leverages ClickHouse’s query caching for faster repeated queries.

Using the CryptoHouse Interface

The web interface at crypto.clickhouse.com lets users write, save, and share queries. It includes:

Tips for Effective Querying

To maximize your experience and avoid quota limits:

  1. Use materialized views for aggregated data to reduce row scans.
  2. Apply date filters when querying base tables to limit the scope.
  3. Leverage -Merge functions when querying aggregated data types.

👉 Get advanced query methods

Future Developments

CryptoHouse currently supports Solana and Ethereum, with plans to add more blockchains soon. The team also aims to automate the process for creating new materialized views based on community feedback.

Frequently Asked Questions

What is CryptoHouse?
CryptoHouse is a free analytics service that provides real-time SQL access to blockchain data. It is powered by ClickHouse and supported by Goldsky’s data infrastructure.

Which blockchains are supported?
Currently, Solana and Ethereum are fully supported. The team plans to add more networks in the future.

Is there a query limit?
Yes. Users can scan up to 10 billion rows per query, run queries for up to 60 seconds, and execute 60 queries per hour.

Can I visualize the query results?
Yes. The interface includes built-in charting tools powered by e-charts for basic visualizations.

How current is the data?
Data is updated in real-time thanks to Goldsky’s streaming pipelines.

What if I need higher quotas?
For commercial use cases or higher quotas, consider contacting Goldsky for dedicated ClickHouse instances tailored to your needs.

Conclusion

CryptoHouse democratizes access to blockchain analytics by offering free, real-time SQL queries on live data. Its technical foundation—ClickHouse for performance and Goldsky for data engineering—makes it a powerful tool for developers, analysts, and enthusiasts.

The service will continue evolving with more blockchains, optimized views, and community-driven features. For those interested in the technical deep dive, the team will present at Solana Breakpoint in September.

To get started, visit crypto.clickhouse.com and explore the possibilities of on-chain data analysis.