Overview
CryptoHouse is a free blockchain analytics platform powered by ClickHouse, enabling real-time SQL queries on blockchain data. It democratizes access to blockchain analytics, particularly for Solana and Ethereum, and encourages community contributions to enhance its capabilities.
What You'll Learn
1
How to perform real-time analytics on blockchain data using ClickHouse
2
Why using Materialized Views can optimize query performance in ClickHouse
3
How to utilize SQL for querying blockchain data effectively
Prerequisites & Requirements
- Understanding of SQL and blockchain concepts
- Familiarity with ClickHouse and Goldsky(optional)
Key Questions Answered
What is CryptoHouse and what features does it offer?
CryptoHouse is a free blockchain analytics service that allows users to perform real-time SQL queries on blockchain data. It supports querying Solana and Ethereum datasets, enabling users to access and analyze blockchain information instantly.
How does ClickHouse handle large volumes of blockchain data?
ClickHouse uses a column-oriented design and highly parallel execution engine, allowing it to efficiently store and analyze terabytes of blockchain data. This architecture enables fast analytics and supports high throughput for complex queries.
What challenges were faced in building CryptoHouse?
The main challenges included converting blockchain entities into a structured format, ensuring fair resource usage among users, and managing the high throughput of transactions from the Solana blockchain while maintaining performance.
How does Goldsky contribute to CryptoHouse?
Goldsky provides the cryptocurrency data infrastructure necessary for CryptoHouse, enabling real-time data streaming from the Solana blockchain. Their expertise in data engineering helped address the challenges of structuring and ingesting blockchain data efficiently.
Key Statistics & Figures
Transactions per second on Solana
3000-4000
This high transaction rate necessitates efficient data handling and real-time analytics capabilities.
Size of the largest table holding transactions
500 TiB
This highlights the significant data volume that ClickHouse can manage effectively.
Backfill speed for data ingestion
500k rows/second
This demonstrates the efficiency of the data ingestion framework implemented by Goldsky.
Technologies & Tools
Some links below are affiliate links. We may earn a commission if you make a purchase.
Database
Clickhouse
Used for real-time analytics and storage of blockchain data.
Data Infrastructure
Goldsky
Provides tools for real-time data streaming from blockchain networks.
Key Actionable Insights
1Utilize Materialized Views in ClickHouse to enhance query performance and reduce computation time.By shifting computation to insert time, Materialized Views can significantly speed up queries that aggregate large datasets, making them ideal for blockchain analytics.
2Apply date filters when querying large tables to avoid exceeding resource quotas.Given the vast amount of data in blockchain tables, filtering by date can help manage query complexity and ensure compliance with usage limits.
3Contribute to the CryptoHouse community by sharing example queries.Engaging with the community not only helps improve the platform but also allows you to learn from others' experiences and use cases.
Common Pitfalls
1
Failing to apply date filters when querying large datasets can lead to exceeding resource quotas.
This can result in query failures or delays, as the system is designed to limit the amount of data processed to ensure fair usage.
2
Neglecting to utilize Materialized Views for complex queries can result in slower performance.
Without leveraging Materialized Views, users may face longer query times, especially when aggregating large amounts of data.
Related Concepts
Blockchain Analytics
Real-time Data Processing
SQL Query Optimization
Data Engineering Challenges In Blockchain