Simplify Real-Time Blockchain Analytics with ClickHouse

Last Updated on July 16, 2024 by Pinax Team

TL;DR: ClickHouse Sink revolutionizes blockchain data analytics by offering a fast, flexible, and user-friendly platform for efficient data extraction and processing.

Hey there! Let’s dive into the world of blockchain analytics and see how ClickHouse is changing the game. ClickHouse isn’t just any database; it’s a powerhouse for crunching big data in real time. And when it comes to blockchain, that’s exactly what we need.

What’s ClickHouse Sink all about?

ClickHouse Sink is Pinax’s latest project aimed at funneling blockchain data into ClickHouse. Why? Because we want to make querying this massive data fast and efficient. ClickHouse boasts of being one of the quickest databases out there, and we’re putting that to the test with blockchain analytics.

ClickHouse Pipeline

The workflow: simple and efficient

Imagine multiple streams of data flowing in parallel, all converging into one ClickHouse Sink. This sink then tidies up the data and gets it ready for whatever you need. The best part? It’s serverless, meaning it’s scalable and efficient.

Key features you’ll love

1. Data Processing: ClickHouse Sink is designed to exclusively handle entity changes. Every incoming request is expected to adhere to this specific format, ensuring consistency in data processing. The content of these requests can vary, but they must respect the predefined structure.
2. Smart Buffering: To keep up with ClickHouse’s pace, we buffer validated data. This means we can handle loads of data without breaking a sweat.
3. Customizable Database Structure: You get main tables and can add your own for specific needs. Plus, we automatically add some metadata columns for you.
4. Schema Flexibility: Whether you’re an SQL pro or a GraphQL fan, we’ve got you covered. And if you prefer no schema at all, that’s cool too.

Table Creation – In the video segment from 3:19 to 4:04, Julien explains the schema for the ClickHouse Sink project, focusing on how blockchain data is structured for efficient querying. He highlights the importance of defining a clear schema that maps blockchain events to database columns, ensuring data is organized and accessible for real-time analysis.

Table Creation-Automatic Fields – In the video segment from 3:19 to 4:04, Julien discusses the automatic creation of schema tables in the ClickHouse Sink project, emphasizing how fields like block number, transaction hash, and log index are automatically added to each table. This process ensures that essential blockchain data is consistently captured and available for querying and analysis.

Easy and safe

We’ve added some neat features to make ClickHouse Sink user-friendly and secure. Think protected endpoints, a read-only mode for safe querying, and built-in queries for keeping an eye on the process.

ClickHouse General Use

See it in action

In our demo, we walk you through setting up and using ClickHouse Sink. It’s a breeze – from authentication to database initialization and schema creation. You’ll see how seamlessly it integrates with your blockchain projects.

Database structure

See the latest version of the schema in the repo.

Database structure

Wrapping up

ClickHouse Sink is our answer to the complex world of blockchain data analytics. It’s fast, flexible, and user-friendly. Whether you’re a developer or an analyst, this tool is designed to make your life easier. For a deeper dive into the project, check out the Substreams ClickHouse Sink GitHub repository where you can explore the code, contribute, and see how ClickHouse Sink is evolving.

Want to know more?

Watch our detailed video guide on ClickHouse Sink above. It’s packed with information and practical tips for integrating ClickHouse into your blockchain projects.

Plus, check out Quickstart Guide: ClickHouse 101 and Substreams Sampler: How to Bulk-Load Blockchain Data into ClickHouse.