Grafana dashboards provide dynamic, beautiful displays of account transactions, system performance, and other data that keep teams on top of what their data means.
When combined with the analytical power and speed of ClickHouse, organizations can monitor everything from financial transactions to network performance, security threats to shipping routes.
The following guides detail how to:
Install Grafana and the plugins for ClickHouse
Integrate your Grafana server with a ClickHouse cluster
Create sample dashboards and panels in Grafana with your ClickHouse data.
1 - How to Install Grafana and ClickHouse Plugins
Basic instructions on installing Grafana with support for ClickHouse
Grafana server supports multiple operating systems including:
Linux (DEB and RPM based distributions)
Microsoft Windows
Mac OS
Docker
The following instructions are based on installing Grafana for Ubuntu, or using the Docker image to provide services. For full details on installing Grafana, see the Grafana Labs Install Grafana site.
Install Grafana on Ubuntu
Prerequisites
Installing Grafana on Ubuntu has the following prerequisites:
255 MB or more storage space
A database to store information. By default, Grafana installs and uses SQLite as its default database. For production systems, MySQL or PostgreSQL are recommended.
Grafana Install
Grafana comes in both an Enterprise and an OSS (Open Source Software) versions. The instructions below are for the OSS version of Grafana.
The following steps require an administrative account. To install Grafana on Ubuntu 20.04 or similar Linux distributions:
From a terminal, install Grafana through apt-get. This will install Grafana from the current stable repository that’s available for all Ubuntu 20.04 users:
Start Grafana, and set it to start at system boot:
sudo systemctl daemon-reload
sudo systemctl start grafana-server
sudo systemctl enable grafana-server.service
sudo systemctl status grafana-server
● grafana-server.service - Grafana instance
Loaded: loaded (/lib/systemd/system/grafana-server.service; enabled; vendo>
Active: active (running) since Wed 2021-06-30 10:26:41 MDT; 11min ago
ClickHouse Plugin Install
Once Grafana has been installed, the plugins to access ClickHouse can be installed through the following process. For more information on the ClickHouse plugin for Grafana, see the ClickHouse datasource for Grafana page.
From a terminal, use an administrative account to install the ClickHouse plugins for Grafana with the following command:
For organizations that prefer to use Docker based services, the following process will set up a Grafana server with plugins for ClickHouse already installed.
To run the most recent version of Grafana with the ClickHouse plugins through Docker:
Start Docker and specify the ports, the container name, and the plugins to install with the following:
After running the first time, it can be restarted with:
docker start grafana
Configure Grafana
Once Grafana has been installed from either of the directions listed above, erify it is running by launching a browser and pointing it at the host name or IP address port 3000. For example: http://localhost:3000. The default username is admin and the default password is admin. Once logged in you will be prompted to create a new password.
Creating a Dashboard and Chart from ClickHouse data requires the creation of a Grafana Data Source. This is a configuration that specifies the ClickHouse server hostname and other settings Grafana will use to connect.
Create Grafana Data Source with the ClickHouse Plugin
To create a new Grafana Data Source connected to a ClickHouse server:
Connect to your Grafana server from a browser.
From the left navigation panel, select Settings -> Data sources.
Select Add Data Source.
From the list of data sources, select ClickHouse.
Localhost Sample Data Source
For organizations who want to test their Grafana ClickHouse plugin with their own internal ClickHouse server, the following settings would be used to connect to a ClickHouse server at host name localhost, with no credentials and no encryption.
Enter the following:
Name: The name for the connection. This does not need to be the name of the ClickHouse server or database.
URL: For localhost, we need to specify both the protocol and the port. In this case, it will be http://localhost:8123.
Auth: Only required if a ClickHouse username and password are specified. If there are no ClickHouse credentials to use, then leave this section disabled.
Additional:
Use Post method to send queries: True
Default database: Select the database to be used without having to specify it in connections.
Select Save & test.
If successful, then the Grafana server can connect to the ClickHouse server on localhost.
Altinity.Cloud Sample Data Source
The following samples are based on a publicly available ClickHouse sample database provided by Altinity.Cloud. This database provides access to US airline flight data and sample logs, and can be access through the following credentials:
Name: The name for the connection. This does not need to be the name of the ClickHouse server or database.
URL: The URL is in the format {HTTP/HTTPS}://{URL}:{port}. If using Altinity.Cloud, this is provided through the Cluster Access Point.
For example:
If the connection is on the specific IP 192.168.0.10 in your network on an unencrypted link then the URL would be http://192.168.0.10:8123.
If using the Altinity.Cloud example above then the URL would be https://github.demo.trial.altinity.cloud:8443. The URL used for Grafana breaks down as follows:
connectiontype: HTTP or HTTPS
url: The fully qualified domain name or IP address of the ClickHouse server.
port: The port being used. By default, HTTP is port 8123, HTTPS is port 8443.
Auth: Only required if a ClickHouse username and password are specified.
Basic auth: True
With Credentials: True
Basic Auth Details: Only required if Basic auth is set to True.
User: The ClickHouse username.
Password: The ClickHouse user password.
Additional:
Use Post method to send queries: True
Default database: Select the database to be used without having to specify it in connections.
Select Save & test.
If the Save & test is successful, then the data source is available for use.
3 - Create Grafana Dashboards from ClickHouse Data
How to turn ClickHouse queries into Grafana dashboards and panels.
A Grafana server that has the ClickHouse plugins and connected a Data Source to a ClickHouse server can use that connection to populate charts.
In Grafana, the visualizations are organized into Dashboards containing one or more Panels. Each Panel is represented by a query to the ClickHouse server, the data used to create a visual representation.
The following examples will demonstrate how to create a Grafana dashboard, and populate it with the following types of visualizations:
Stat: Used to display large statistical values.
Time Series: Separates data into time segments, useful for tracking changes or patterns over time.
World Map: Use latitude and longitude data to label information on a map.
Create a New Dashboard
To create a new Dashboard in Grafana:
From the left navigation panel, select + -> Dashboard.
Select the Save icon from the upper right menu.
Provide a name for the dashboard then select Save.
Once finished, new Grafana panels can be added by selecting Add Panel in the upper right hand corner.
Panel Examples
Sample Data
Sample data is provided by a ClickHouse database hosted by Altinity.Cloud. This database includes sample information for United States airline flights, http log file data, and other useful sets of data for testing ClickHouse and experimenting with querying data from it.
Big stat panels are used to show single or prominent values. For examples, they can track the total number of servers that are down, total of shipments currently being managed, or other useful benchmarks.
The following example creates a Grafana Stat panel that displays the number of Carriers found on the table ontime, which tracks flight data across the United States. To create the Grafana Stat panel from the sample Altinity.Cloud data:
From Grafana, select the Dashboard to add the Stat panel to.
From the upper right hand menu select the Panel icon, then select Add Panel.
Select Add an Empty Panel.
Set the following:
From the upper right hand corner, select the Visualization then select Stat.
Set the Data source to your ClickHouse Data Source.
In the Query field:
Set the name by selecting the Name field and filling in whatever name you choose. In this case, it can be Total unique carriers.
From
Set –database– to default. If your data source connected to the Altinity.Cloud sample data, then this will be available as a drop down choice.
Set –table– to ontime.
Select Go to Query and enter the following SQL statement. Note that the ending ; is not included in this query, and that the query is using a variable marked with the $ symbol:
selectcountDistinct(Carrier)FROM$table
Set Format asTable.
Save the panel by selecting Save from the upper right corner. Provide a name for your panel such as Total Unique Carriers.
Depending on the connection, the values displayed will update within moments and display a single number as the total number of unique carriers.
Create Time Series Panel
A Time Series panel can display information over time segments, such as over a week, a month, or other sets of time. This provides a quick visual representation of how some measurement is comparing against similar sections of time. Such as comparing financial performance from one quarter to the next.
These steps will use the Altinity.Cloud demonstration database to show a count of flights grouped by carrier in monthly time segments.
To create a Time Series demonstration chart with the sample data:
From Grafana, select the Dashboard to add the Time Series panel to.
From the upper right hand menu select the Panel icon, then select Add Panel.
Select Add an Empty Panel.
Set the following:
Set the Visualization in the upper right corner to Time Series.
Set the Data source to your ClickHouse data source.
In Query Options:
Set Relative Time to 1M. This will accumulate one calendar month of data at a time.
Time shift to 24M. This will have Grafana query 24 months of previous data from the current date.
In the Query section:
Set the name by selecting the Name field and filling in whatever name you choose. In this case, it can be Carrier Flights by Time Period.
From:
Set –database– to default. If your data source is connected to the Altinity.Cloud sample, then this will be available as a drop down choice.
Set –table– to ontime.
For the time series chart, set the following:
Column:DateTime.
In the –dateTime:col– field, set this to toDateTime(FlightDate). This informs Grafana of what field will be used for the time measurement.
Set Column:Date to Column:Date.
Select the query Edit icon and enter the following query:
Note that this query is using variables indicated by the $ symbol. This allows the table and time filter set in the previous steps to be automatically applied to the query without reconstructing the entire request.
Save the panel by selecting Save from the upper right corner. Provide a name for your panel such as Total Flights by Carriers Time Series.
For experimentation, change the Query Options to update the Time Shift and Relative Time to adjust the chart by different time metrics, such as 3M or 1Y for “three months” or “one year” respectively.
Create World Map Panel
One of the powerful features of Grafana is the ability to make use of Grafana plugins, some which are created by Grafana Labs, others by the community.
One custom panel is the Worldmap Panel, which allows latitude and longitude information to be charted on the image of a world map. Before beginning the steps below, complete the Worldmap Panel installation process for your Grafana server.
For this example, we will be combining the data from two tables from out Altinity.Cloud sample database:
ontime: This tracks flight information for US flights across the United States.
airports: This table stores information on airports, including their latitude and longitude.
These two tables will be combined through a JOIN query, and the information mapped onto the world panel map in Grafana showing the locations of airports on the map.
To create a world map panel with sample Altinity.Cloud airport data:
From Grafana, select the Dashboard to add the World Map panel to.
From the upper right hand menu select the Panel icon, then select Add Panel.
Select Add an Empty Panel.
Set the Visualization in the upper right corner to Worldmap Panel and set the following:
Title: Create a title for the panel, such as Cancellations by Airport.
Save the panel by selecting Save from the upper right corner. Provide a name for your panel such as Cancelled Flights by Airport.
The panel will now display a world map, focused on the United States with circles at each airport. The colors will indicate how many late flights there were for each airport.
4 -
Setup the connection to ClickHouse demo site.
select toString(rand64()) as rnd_id, *
from tripdata
limit 100000000