Building high performance dashboards with SquaredUp and ClickHouse

ClickHouse is redefining the boundaries of analytical database performance. Trusted by hyperscalers like Netflix, OpenAI, and Disney, it delivers sub-second query responses on billions of rows and scales seamlessly to petabyte workloads.

The great news is that it is open source, so this power is available to everyone. You can spin up a local instance running in a Docker container in a matter of seconds. Even better, interacting with a ClickHouse backend does not require using any complex protocols or learning a new query language. You can do everything simply by passing SQL queries to an HTTP endpoint.

In this article, we explore how you can connect ClickHouse to SquaredUp using the Web API plugin and create smart dashboards in minutes.

Deploying ClickHouse with ClickStack on AKS

There are quite a few options for running ClickHouse. In this article, we are going to use it as part of the ClickStack observability stack. We will run our ClickStack instance on an Azure Kubernetes cluster. Before starting, you need to ensure your Kubernetes cluster has a local path StorageClass provisioner configured. In our setup, we used the Rancher Local Path provider, as described in this article.

Once you have created the StorageClass, all you need to do is install and run the ClickStack Helm chart. You can find full details on this page on the ClickHouse website.

helm repo add hyperdx https://hyperdxio.github.io/helm-charts 

helm repo update

helm install my-hyperdx hyperdx/hdx-oss-v2

This Helm chart will deploy four services. The app service is the ClickStack front end and the mongodb service is used for storing application state.

The clickhouse service consists of both the ClickHouse database engine and an HTTP interface. You’ll need to expose this via a public IP address or an ingress controller for external access.

Connecting ClickHouse to SquaredUp

To begin, create a new dashboard in SquaredUp and select the Web API data source. Creating the connection to the ClickHouse HTTP endpoint is very simple. Just enter a connection name and a URL. Authentication can be added if necessary.

That’s it! Now we can start running queries.

Visualizing system metrics

Next, we are going to walk through how you can create a dashboard like the one below to view metrics on the health and resource usage of our ClickHouse instance.

We just need to select the Web API data source and then select the HTTP Request data stream:

It is possible to interact with the ClickHouse API by sending either GET or POST requests. However, we need our response to be in JSON format and in this case, it is better to use a GET request and append the query as a parameter.

You must set the name of the parameter as 'query' and then enter your SQL as the parameter value. You don’t need to do any HTML encoding – SquaredUp takes care of that for you. To view average CPU usage per second, we will enter this query:

SELECT
toStartOfInterval(event_time, INTERVAL 60 SECOND) AS t,
avg(ProfileEvent_OSCPUVirtualTimeMicroseconds) / 1000000 AS avg_cpu_seconds
FROM system.metric_log
WHERE event_date >= toDate(now() - INTERVAL 1 HOUR)
AND event_time >= now() - INTERVAL 1 HOUR
GROUP BY t
ORDER BY t
WITH FILL STEP 60
LIMIT 60
FORMAT JSON

Since the SquaredUp Web API data source expects all data to be returned as JSON, you must append the FORMAT JSON directive to your SQL command.

Once the data stream is configured, visualize the results using SquaredUp’s line chart visualization.

To display disk space usage, we used a gauge visualization.

Note that ClickHouse reports free disk space rather than used space. To present the data more intuitively, we applied a transformation in SquaredUp to compute Used Space as a custom field. As you can see, this is achieved with a simple expression using mustache-like syntax.

If you want to create a full system monitoring dashboard like the one above, you can find more SQL queries on this page on the ClickHouse website. Also, quite fittingly for a SQL-centered product, ClickHouse has very conveniently made a full set of queries available in the system.dashboards table. To view them just run this query:

SELECT * FROM system.dashboards

Querying and visualizing data

Let’s move from infrastructure metrics to business data. For this example, we used the UK House Price dataset referenced in the ClickHouse docs to build the dashboard below:

The document provides full details on how you can create a new ClickHouse database and insert the data. Inserting a dataset of 33 million rows took under a minute on our AKS node (4 vCPUs, 16 GiB RAM). Once the query has completed you will have a database called 'uk'.

The process for creating the tiles in this dashboard follows the same procedure that we used for creating the system dashboard. All we are doing is querying a different database. If you want to explore this dataset, you can find a number of sample queries on this page on the ClickHouse website.

The visualization below shows average house prices for multiple districts over a number of years.

In this visualization, we have grouped our data by both Year and District, with each District being represented by a separate line on the graph. To achieve this, we just need to configure the District field as a series in our mapping:

A similar principle applies to the Most expensive neighbourhoods visualization below.

In this query, our data is grouped first by Town and then by District. You can see this in the raw data for the query below:

Again, to break the bar chart down into sub-groupings, we configure the District field as a Series in our visualization mapping:

Conclusion

ClickHouse brings unmatched analytical performance and, when paired with SquaredUp, delivers a streamlined dashboarding experience for engineering and operational teams.

In this guide, we've shown how to deploy ClickHouse, connect it to SquaredUp using the Web API plugin, and build dynamic dashboards from system metrics to rich business data.

Watch this space for our upcoming dedicated ClickHouse plugin, which will simplify integration even further.