Teams of developers large and small are trying to do more with less, deliver results faster, all while maintaining lightning-fast, secure applications that scale.

Combining Hasura’s ability to instantly generate GraphQL APIs and ClickHouse’s powerful real-time analytics capabilities allows developers to rapidly innovate and deliver blazing-fast applications that include real-time analytics.

What is ClickHouse?

ClickHouse is an open source column-oriented database management system that’s designed for blazing-fast real-time analytics. It is primarily used to store and analyze large volumes of data, leveraging its column-oriented nature to efficiently run fast query processing over large datasets.

The ability to retrieve data quickly from huge volumes of data in real time, makes ClickHouse an ideal data store for GenAI and analytics applications.

What is Hasura

Hasura is a cutting-edge platform that allows users to seamlessly build and deploy REST- and GraphQL-powered applications. By providing instant GraphQL APIs over existing databases, Hasura enables rapid development without compromising on performance or security. Its intuitive interface and powerful features, such as caching and role-based access controls, empower developers and businesses alike to build scalable, secure, and efficient applications.

Whether you're a seasoned developer or just getting started, Hasura promises to simplify and accelerate your application development journey.

Challenges

Teams across organizations of all sizes need access to specific data and leverage that data to build applications that serve internal and external users. APIs and microservices are a natural choice for connecting data to consumers in a fast, secure, scalable, and decoupled way. But the traditional API development process is time consuming.

Manual API integration is time-consuming, requiring developer teams to create boilerplate CRUD code, figure out authorization (input / output validation, etc.. And then even more work to get the API production-ready – putting in place the right security controls, wiring it up to be observable for monitoring, performance tuning, etc.

Limited flexibility means REST enforces a fixed set of endpoints with specific data structures, causing developers to manage multiple endpoints and make multiple requests for related data.

Traditional OLTP (Online Transaction Processing) and Cloud data warehouses are not optimized for building interactive, data-driven applications. These applications demand low latency queries, to provide responsive user experiences, even when exposed to high levels of concurrency. As a result, organizations turn to real-time analytical databases, such as ClickHouse.

Benefits of Hasura + ClickHouse

Hasura's ClickHouse integration changes the game, especially when we consider the inclusion of GraphQL.

Hasura's enterprise-ready API management and RBAC features simplify the adoption of new databases such as ClickHouse. Users can enjoy ClickHouse's ability to perform analytical queries over billions of rows in milliseconds, without needing a deep understanding of the data structure and schema. Application development can be accelerated with development teams working with a familiar API interface, and avoiding the initial cost of writing and refining SQL queries.

Existing ClickHouse users can exploit Hasura's ability to provide a data agnostic query layer in front of ClickHouse – simplifying the adoption and allowing rapid prototyping through automatically generated JSON APIs.

• GraphQL's flexibility: As previously emphasized, GraphQL’s capability allows developers to request exactly the data they need, no more and no less. This precision helps in reducing data transfer loads and enhancing application performance.
• Automatic API generation: With Hasura, as soon as the schema is set, it auto-generates a live, real-time GraphQL API, reducing the manual labor of setting up APIs.
• Data federations: Hasura GraphQL Engine provides a uniform interface to the data consumer across multiple data domains. This reduces the need for manual data integration and API orchestration, by integrating data across multiple diverse sources providing rich capabilities of retrieval, so that the data consumer does not have to incur the cost of data integration or API orchestration.
• Developer-friendly JSON format: GraphQL’s innate use of JSON for both queries and responses is an advantage. The readability and simplicity of JSON are much loved by developers, and its universality makes data parsing effortless across different programming languages.
• Rapid prototyping: Hasura accelerates the development process by offering instant real-time APIs, which in turn aids in quick prototyping and validation of ideas.

ClickHouse addresses the challenge of efficiently storing, managing, and querying massive volumes of data in real time. Traditional relational databases can struggle with performance issues when handling large-scale data analytics.

ClickHouse, being a column-oriented database management system, is designed to perform real-time analytical queries on large datasets with high speed. Its architecture supports distributed processing, enabling it to crunch millions, or even billions, of rows in a fraction of the time that conventional databases might take. This makes it particularly suited for scenarios that require swift analysis of big data, such as telemetry data analysis, real-time analytics dashboards, and high-speed logging.

In essence, ClickHouse offers a solution to the growing need for rapid, real-time data analytics on voluminous datasets.

What can I build with Hasura and ClickHouse?

Building a GenAI-powered SRE assistant with ClickHouse and Hasura

Before: Traditional systems struggled with real-time data analysis, often bottlenecking SRE operations and making it challenging to harness the full potential of GenAI for system reliability enhancement.

After: With ClickHouse integrated into Hasura, SRE teams now experience blazing-fast data analytics, unlocking the true capabilities of Gen-AI to power a sophisticated SRE assistant, ensuring enhanced system reliability and efficiency.

Before: Earlier, building real-time analytics dashboards required developers to write intricate SQL queries, entailing considerable time and expertise to manage and optimize data retrieval processes.

After: Now, by utilizing GraphQL, developers can effortlessly construct dynamic dashboards that provide real-time updates, showcasing the most current data insights and fostering data-driven decision-making.

Before: In the past, e-commerce platforms had to rely on multiple endpoints for fetching various data like user details, product information, and order histories, which often resulted in slower load times and a complex backend architecture.

After: With Hasura GraphQL engine, e-commerce applications streamline data fetching through a single API endpoint, thereby enhancing performance, reducing load times, and offering a more refined user experience.

Before: Aggregating content from various sources traditionally required an intricate network of API calls, making the process less efficient and harder to manage.

After: Now, Hasura facilitates a unified data access layer, allowing content aggregation platforms to fetch data from disparate sources with ease, presenting a consolidated view of information through a simplified query structure.

Getting started connecting Hasura to ClickHouse

You can connect Hasura to an open source or cloud-hosted version of ClickHouse. This example will cover connecting a Hasura Cloud project to a ClickHouse Cloud deployment.

First, create a free ClickHouse Cloud trial. You will be prompted with several questions regarding what type of deployment and cloud provider you would like to use. You can connect Hasura to any type of ClickHouse deployment. For this demo, we recommend you select “Anywhere” to simplify things.

Note: It is recommended that you host your Hasura and ClickHouse deployments in the same cloud provider and region, if possible, to reduce latency.

Once you have created your ClickHouse Cloud deployment, you can add some sample data.

Luckily, ClickHouse makes it easy to get started with a number of sample data sets. From the ClickHouse dashboard click the Actions dropdown and select add data. This will launch the console for the ClickHouse service you created.

You will be presented with several options to add data to ClickHouse. You can choose any of these options to get started. For this example, we will choose one of the predefined sample data sets ClickHouse provides.

You can see the predefined sample data offered by ClickHouse as of writing this post below. You can use any data to connect to Hasura. For this example, we will use the New York Taxi Data.

After you import the data into your ClickHouse service, you should see the following table and data in the ClickHouse console.

To get started, head over to cloud.hasura.io. Create a project and then click Launch Console.

Next, go to the Data tab to connect ClickHouse with Hasura.

Click on Data Connector Agents and then click Add Agent.

You are presented with a form that requires you to add your connector endpoint.

Prerequisite for the next step requires installation of the Hasura CLI. If you need to install and configure the Hasura CLI you can find more information in the ClickhHouse Getting Started - Cloud documentation.

Follow the below commands to deploy the ClickHouse connector and fetch the endpoint.

# Create connector endpoint using Hasura CLI
hasura connector create <connector name> --github-repo-url 
https://github.com/hasura/clickhouse_gdc_v2/tree/main/

# Review logs
hasura connector logs <connector name>

# Review hosted connectors and fetch endpoint
hasura connector list

Take note of the URL for the endpoint displayed in the console. You will need it for the next step.

Once you have the ClickHouse connector deployed you can fill and submit the form to Connect a Data Connector Agent.

Name - name to identify the connector
URL - endpoint of your deployed connector

Now, click Connect Database at the top of the page and select ClickHouse to connect the existing database.

Find the connection string for your ClickHouse project by clicking the Connect dropdown and selecting View connection string.

Navigate to the HTTPS section of the dialog. Within the information provided you can find the connection.

You are now ready to name your Hasura database connection and enter in the required information: connection URL, username, and password.

Now that we have connected ClickHouse to Hasura, we can track the nyc_taxi table we added to ClickHouse in Hasura to expose it over Hasura’s auto-generated GraphQL API.

While in the Data tab, click on the ClickHouse database you just connected. Select the nyc_taxi table and click Track Selected.

Congratulations! 🎉 You have now successfully connected Hasura to ClickHouse and exposed a table over the GraphQL API.

The Hasura Console

Your ClickHouse data is now available over a GraphQL API. Using the Hasura GraphiQL console you can explore the API making it easy for developers to explore and transform data to suit their needs. This is especially useful for use cases involving analytics. Using Hasura’s GraphQL engine you now have a secure API that leverages the capabilities of ClickHouse for real-time analytics.

Let’s try some examples.

Note that all of the queries have a caching directive added to them with a total time to live (ttl) of 3600 seconds or 1 hour. This means all queries will be cached in Hasura cloud for 1 hour, making for faster response times and less load on ClickHouse.

Simple query with filter

The query below will return trips with a specific id.

query MyQuery @cached(ttl: 3600) {
  nyc_taxi(where: {trip_id: {_eq: 1199999883}}) {
    trip_id
    total_amount
    trip_distance
    tip_amount
    passenger_count
    pickup_datetime
  }
}

Filter results based on condition

The query below will return all trips that were longer than 30 miles. Note that the schema or fields being returned has changed.

query MyQuery2 @cached(ttl: 3600) {
  nyc_taxi(where: {trip_distance: {_gt:30}}) {
    fare_amount
    trip_id
    trip_distance
    total_amount
  }
}

Aggregation

The following query will return the average and max values for fares, tips, and distance. Note: This is what ClickHouse is more efficient at than traditional databases.

query MyQuery3 @cached(ttl: 3600) {
  nyc_taxi_aggregate {
    aggregate {
      avg {
        fare_amount
        tip_amount
        trip_distance
      }
      max {
        fare_amount
        passenger_count
        tip_amount
        trip_distance
      }
    }
  }
}

Next steps

These are just the first steps to fully leveraging the capabilities of Hasura and ClickHouse. To build out your API further you can:

• Add sample data that includes relationships that will also be reflected in the GraphQL API. For more information see this demo.
• Add caching directives.
• Manage access to your data and APIs by defining roles and permissions.
• Create a REST endpoint.

Read more about a use case we found for using ClickHouse here at Hasuara: Building a GenAI-powered SRE assistant with ClickHouse and Hasura

By using Hasura to expose ClickHouse data over a GraphQL API, developers can significantly reduce the time and complexity involved when creating and iterating on real-time analytics applications. The user-friendly features and tools provided by Hasura streamline the development process, allowing developers to focus more on building powerful applications and less on wrestling with complex data retrieval processes.

This synergy brings unparalleled efficiency and performance to development, setting a new standard for data management and analytics in the modern business landscape.

To help you get started, we've prepared detailed documentation, guides, and examples:

• Hasura Clickhouse documentation
• Building a GenAI-powered SRE assistant with ClickHouse and Hasura
• Build an SRE app powered by AI
• Hasura on Clickhouse

We can't wait to see the amazing applications you'll build using Hasura and Native Queries. Get started today by signing up for Hasura Cloud and connecting to one of the supported databases.

If you have any questions or need assistance, feel free to reach out to our team on Discord or GitHub.