Breakpoint 2024: Product Keynote: Clickhouse & Goldsky: CryptoHouse (Derek Chia)

Solana developers and analysts, get ready for a game-changing analytics tool! Derek Chia from Clickhouse unveils CryptoHouse, a free blockchain analytics service that promises to revolutionize how we interact with Solana data.

Summary

Derek Chia, representing Clickhouse, introduces CryptoHouse, a free blockchain analytics service powered by Clickhouse and Goldsky. Clickhouse is an open-source analytical database known for its high performance and efficiency in handling large-scale data.

The presentation highlights Clickhouse's capabilities, including its SQL compatibility, high write speeds, distributed architecture, and blazing-fast query performance. These features make Clickhouse particularly well-suited for blockchain analytics, where processing vast amounts of data in real-time is crucial.

CryptoHouse, available at crypto.clickhouse.com, offers users the ability to write and execute real-time queries against Solana blockchain data, including blocks, transactions, and tokens. The service provides instant results and simple visualizations, making it accessible to users with varying levels of technical expertise.

Key Points:

Introduction to Clickhouse

Clickhouse is an open-source analytical database developed since 2009, boasting over 36,000 GitHub stars. It's column-oriented and excels at aggregations, making it ideal for analytical use cases. Users can deploy Clickhouse as a single binary or in a distributed fashion with support for replication and sharding across regions. For those seeking a managed solution, Clickhouse Cloud is available as a service.

Clickhouse's architecture is designed to handle massive amounts of data efficiently. It supports several million writes per second per server, far exceeding the transaction throughput of most blockchain networks, including Solana's impressive 4,000 transactions per second. The largest known Clickhouse cluster consists of over 4,000 servers, demonstrating its scalability for enterprise-level applications.

Clickhouse for Blockchain Analytics

Clickhouse offers several features that make it particularly suitable for blockchain analytics:

1. SQL compatibility: This allows seamless integration with existing SQL-compatible UI applications and dashboards, including popular services like Dune.

2. High write speeds: Clickhouse can handle millions of writes per second, easily accommodating the transaction volumes of even the fastest blockchain networks.

3. Distributed architecture: Users can set up Clickhouse in a distributed fashion with replications for high availability and sharding for improved performance.

4. Efficiency: Clickhouse offers various encoding and compression options, resulting in significant storage savings. For example, uncompressed CSV files can achieve over 20 times compression when stored in Clickhouse.

5. Fast query performance: Clickhouse can scan and process billions of rows per second, enabling real-time analytics on blockchain data.

6. OLAP modeling support: Clickhouse's ability to handle joins makes it suitable for in-depth analysis of structured blockchain data.

CryptoHouse: A Free Blockchain Analytics Service

CryptoHouse is a collaborative effort between Clickhouse and Goldsky, leveraging Clickhouse's powerful database capabilities and Goldsky's real-time data pipeline. The service is accessible at crypto.clickhouse.com and offers users the ability to write and execute queries against various blockchain data tables, including blocks, transactions, and tokens.

Key features of CryptoHouse include:

1. Real-time query results: All computations are performed in real-time, with no batching or waiting periods.
2. Simple visualizations: Users can easily consume and interpret data through built-in visualization tools.
3. Fair usage quotas: Database-level quotas ensure equitable access for all users.
4. Community contributions: Users are encouraged to contribute queries via the project's GitHub repository.

During the demonstration, Derek showcased the platform's capabilities by running various queries, including a summary of transactions (processing over 300 billion rows), viewing sample blocks, and identifying the most active account within a day (processing 1.2 billion rows in just 7 seconds).

Facts + Figures

• Clickhouse has over 36,000 GitHub stars
• Clickhouse can handle several million writes per second per server
• Solana's transaction throughput is around 4,000 transactions per second
• The largest known Clickhouse cluster consists of over 4,000 servers
• Clickhouse can achieve over 20 times compression rate compared to uncompressed CSV files
• CryptoHouse processes over 300 billion rows of Solana blockchain data
• A complex query processing 1.2 billion rows was executed in just 7 seconds on CryptoHouse
• CryptoHouse is available for free at crypto.clickhouse.com

Top quotes

1. "Clickhouse is significantly outperformed other databases. We tested and delivered 40 times because savings. And Clickhouse is a clear winner." - Aaron Chu, co-founder of Coin Hall
2. "If you have any analytical use case, then Clickhouse is the best database for you."
3. "We scan and process billions of rows per second, and you will need this in order to get real-time analytics coming out from blockchain data."

Questions Answered

What is CryptoHouse?

CryptoHouse is a free blockchain analytics service powered by Clickhouse and Goldsky. It provides users with the ability to write and execute real-time queries against Solana blockchain data, including blocks, transactions, and tokens. The service offers instant results and simple visualizations, making it accessible to users with varying levels of technical expertise.

Why is Clickhouse well-suited for blockchain analytics?

Clickhouse is ideal for blockchain analytics due to its SQL compatibility, high write speeds, distributed architecture, and blazing-fast query performance. It can handle millions of writes per second, process billions of rows in seconds, and offers efficient compression options. These features allow for real-time analysis of large-scale blockchain data, which is crucial for understanding network activity and trends.

How does CryptoHouse compare to other blockchain analytics tools?

CryptoHouse stands out by offering free, real-time access to Solana blockchain data with the power of Clickhouse's high-performance database. Unlike some other tools that may rely on batched or delayed data, CryptoHouse provides instant results for queries. It also offers a user-friendly interface with built-in visualizations and the ability to share and save queries, making it accessible to both technical and non-technical users.

What kind of data can I query using CryptoHouse?

With CryptoHouse, you can query a wide range of Solana blockchain data, including blocks, transactions, tokens, and more. The service provides access to over 300 billion rows of data, allowing for comprehensive analysis of network activity, account behavior, and token movements. Users can write custom SQL queries to extract specific insights or use pre-made queries for common analytics tasks.

Is CryptoHouse suitable for both beginners and advanced users?

Yes, CryptoHouse caters to users with varying levels of expertise. For beginners, the platform offers pre-made queries and simple visualizations to help them get started with blockchain analytics. Advanced users can write complex custom queries to perform in-depth analysis. The SQL-based interface makes it accessible to anyone familiar with database queries, while the real-time performance allows for iterative exploration of data.