Simplify Real-Time Blockchain Analytics with ClickHouse

Substreams By Dec 05, 2023 3 Comments
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.

A diagram of the ClickHouse Pipeline, showing the workflow from Substreams to Webhook Sink to ClickHouse Sink to ClickHouse to External API.

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 – 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.

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.

A diagram of the ClickHouse general use, with a workflow showing the webhook sink output coming into ClickHouse Sink for format validation, internal buffering, query builder and then into to ClickHouse.

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.

A breakdown of the database structure, where the user dimension is generated by the user provided schema and is augmented by a few columns.

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?

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

Author

I am a dedicated member of the Graph Advocates DAO and proud to be a part of the Graphtronauts community. As a passionate crypto investor and enthusiast, I have delved into the world of decentralized technologies, with a strong focus on Graph Protocol. My journey includes writing insightful blogs for Graphtronauts and contributing to the development of Subgraph documentation for various projects within the Graph Protocol ecosystem. Most recently, I have taken on the role of a Pinax technical writer, further expanding my commitment to advancing the adoption and understanding of blockchain and graph-based technologies.

3 Comments

  1. louis says:

    I love analytics!!! Can’t wait to see more of this but also how on-chain analytics affects or correlates with off-chain analytics and ‘real-world’ data, not that blockchain isn’t real but you get what I mean. Great article!

  2. Sophie says:

    Great blog article! I’m considering integrating ClickHouse into my project now due to how cool and efficient it looks haha!

  3. Jérémie says:

    Great article! Very informative!

Leave a comment

Your email address will not be published. Required fields are marked *