Integrating Grafana Cloud

Grafana is a popular open-source observability platform for monitoring applications and infrastructure. We’ll look at connecting Altinity.Cloud to Grafana Cloud, although connecting to any Grafana instance works basically the same, including self-hosted Grafana. And of course we’ll use the Altinity Grafana plugin for ClickHouse®. With over 16 million downloads, it’s the most popular ClickHouse plugin in the world.

Getting the connection details for your cluster

First of all, to connect your ClickHouse cluster to Grafana, you’ll need the connection details for your cluster. In the Clusters view, click the Connection Details link:

Figure 1 - A cluster panel

In addition to your username and password, you’ll need the host name and the HTTP port:

Figure 2 - Connection details for the ClickHouse cluster

Creating a new connection and installing the Altinity ClickHouse plugin

With those details, go ahead and log in to your Grafana Cloud account. Click the Grafana menu in the upper left corner, then select the Add new connection item in the Connections section:

Figure 3 - The Add new connection menu

Type altinity in the search box. You’ll see the tile for the Altinity plugin:

Figure 4 - Selecting the Altinity Grafana plugin for ClickHouse

Click on the plugin’s tile to go to the plugin’s overview page. If you haven’t used the Altinity plugin before, you’ll see something like this:

Figure 5 - The overview page for the Altinity Grafana plugin for ClickHouse

Make sure the version number is 3.1.0 or higher and click Install.

On the other hand, if you have used the Altinity plugin before, you’ll see a different set of buttons:

Figure 6 - Another version of the overview page for the Altinity Grafana plugin for ClickHouse

Make sure the version number is 3.1.0 or higher. If not, click Update to get the latest version of the plugin.

Creating a new data source

Once you’ve got the latest version of the plugin installed, click the Add new data source button. You’ll see this panel:

Figure 7 - Creating a new data source

At the top of the panel, give your new data source a name. In the URL field, enter the complete URL of your ClickHouse cluster, including https:// and the port number. Next click the Basic auth button; you’ll access ClickHouse with a username and password. Enter those in the Basic Auth Details section below.

NOTE: The user ID and password are the ID and password of your ClickHouse cluster, which are probably not the same as the ID and password of your Altinity.Cloud account. (admin is the default user ID.)

To complete the connection, scroll to the bottom of the page and click the Save & test button. You’ll see something like this:

Figure 8 - The Save & Test button with a successful connection

If anything goes wrong, you’ll get an error message:

Figure 9 - An unsuccessfully created data source

Exploring the plugin with your data source

Once you get the Data source is working message, you can click either the building a dashboard or the Explore view link. We’ll look at the Explore view now. When you create a dashboard later, you’ll add a visualization that goes through the same steps we’ll cover here.

The Explore view starts with an empty query:

Figure 10 - The initial data source explorer view

The data source you just created is displayed at the top of the panel. Click the –database– field to see the list of databases available in your ClickHouse cluster. Select one, then click the –table– field to see the list of all the tables in the database you selected. In Figure 11 we’ve selected the github_events table in the default database. Also, this is time-series data, so we need a timestamp column to order our data. We selected the merged_at column, which is of type DateTime:

Figure 11 - Selecting a database, table, and timestamp column

By the way, the database we’re using here is from the GH Archive project, containing details of more than 7.7 billion events from public GitHub projects.

The Altinity Grafana plugin lets you edit queries directly. Click the SQL Editor button and paste in this query:

SELECT 
    toStartOfHour(merged_at) AS event_time, 
    count(*) AS merged_pr_count 
FROM 
    github_events 
WHERE 
    $timeFilter
GROUP BY 
    event_time 
ORDER BY 
    event_time

We’re graphing the number of PRs merged per hour, so we use the toStartOfHour function to convert every timestamp to the start of that hour (08:37 becomes 08:00, 08:04 becomes 08:00, etc.) then count those PRs. In the WHERE clause we use the Grafana macro $timeFilter. This scopes the range of data returned by the query to the time range selected in the visualizer. (More on time ranges in a minute.)

The plugin will look like this:

Figure 12 - The query, ready to execute

Click the Run Query button. You’ll see a graph of the data. Click the Stacked Lines button above the graph and you’ll see something like this:

Figure 13 - A graph of PRs merged per hour over the last seven days

You can change the look of the graph by clicking the Lines, Bars, Points, and other buttons above the graph. Notice that below the graph is a table that lists the data behind the graph. (We truncated the table; you can scroll down and see all the data represented in the graph if you want.)

You can also change the range of data you’re visualizing. Click the Clock icon above the query and select Last 30 days from the list:

Figure 14 - Set the time range to “Last 30 days”

Thanks to the $timeFilter macro, the plugin reruns the query and updates the graph:

Figure 15 - A graph of PRs merged per hour over the last 30 days

This is a great example of the kinds of visualizations you can create with the Altinity Grafana plugin for ClickHouse. You can now use the plugin in your Grafana dashboards.