CryptoHouse: A Free Blockchain Analytics Service Powered by ClickHouse and Goldsky

We're excited to announce CryptoHouse, now accessible at crypto.clickhouse.com, a free blockchain analytics service powered by ClickHouse.

Key Features

• Real-Time Analysis: Unlike traditional services requiring scheduled async queries, ClickHouse enables instant responses for democratized data access.
• SQL-Based Queries: Users leverage SQL to explore blockchain data updated in real-time via Goldsky.
• Custom UI: Save/share queries, create basic charts, and explore curated examples to jumpstart analysis. Community contributions welcome via sample queries.

Supported Blockchains

Solana

Query the following datasets freely:

• Blocks
• Transactions
• Token Transfers
• Block Rewards
• Accounts
• Tokens

Similar datasets available for Ethereum, with more blockchains planned for future expansion.

Why Blockchain Analytics Matter

Blockchains process thousands of transactions/sec, creating complex ecosystems. Understanding their state and trends is critical for:

• Investors making data-driven decisions.
• Developers building smart contracts.

SQL is the natural language for analysis, but challenges include:

1. Converting blockchain entities to structured, row-oriented formats.
2. Finding databases capable of handling petabyte-scale data with low-latency queries.

Why ClickHouse Excels in Blockchain Analytics

As an open-source OLAP database, ClickHouse thrives with blockchain data due to:

• Columnar Design: Optimized for large-scale analytical queries.
• Parallel Execution: Processes terabytes of data rapidly.
• Industry Adoption: Used by leaders like Goldsky and Nansen for blockchain services.

Building CryptoHouse: Key Challenges

Data Engineering

Goldsky tackled Solana's high throughput (~3k-4k transactions/sec) by:

1. Real-Time Streaming: Using Mirror Dataflow Platform for low-latency ingestion.
2. Schema Optimization: Transforming raw data into query-friendly formats via ClickHouse-specific Materialized Views.

Example pipeline:

CREATE MATERIALIZED VIEW solana.stage_tokens_mv TO solana.tokens 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

ClickHouse Optimizations

1. Fair Use Quotas: Limits (10B rows/query, 60 queries/user/hour) ensure stability.
2. Materialized Views: Accelerate queries by preprocessing aggregates (e.g., daily fees).
3. Deduplication: ReplacingMergeTree engine handles rare duplicate events.
4. ClickHouse Cloud: Scales compute/storage independently via object storage.

User Interface

• Query Editor: Write, save, and share SQL.
• Visualization: Built-in charts via e-charts.
• Examples: Onboarding queries to explore datasets.

Pro Tips for Queries

• Use Materialized Views: Minimize scanned rows with pre-aggregated data.
• Apply Date Filters: Essential for large tables (e.g., transactions).
• Avoid Duplicate Scans: Leverage -Merge functions for aggregated results.

FAQ

What makes CryptoHouse different?

Unlike async-based services, CryptoHouse delivers real-time blockchain analytics via ClickHouse's high-speed queries.

How is data kept up-to-date?

Goldsky streams live blockchain data directly to ClickHouse, ensuring sub-second latency.

What if I need higher query limits?

For commercial-scale needs, contact Goldsky for dedicated ClickHouse instances.

Future Plans

• Expand supported blockchains.
• Automate view creation based on community feedback.
• Present at Solana Breakpoint 2024.

Get Involved

• Contribute queries: GitHub
• Report issues: Open GitHub tickets.

👉 Explore CryptoHouse Now

For more ClickHouse-powered solutions, check out our official blog.