Integrating Grafana with ClickHouse

• 1: How to Install Grafana and ClickHouse Plugins
• 2: How to Connect Grafana to ClickHouse
• 3: Create Grafana Dashboards from ClickHouse Data
• 4:

1 - How to Install Grafana and ClickHouse Plugins

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:

1. 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:

   sudo apt-get clean  
   sudo apt-get update
   sudo apt-get install grafana
   

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

1. From a terminal, use an administrative account to install the ClickHouse plugins for Grafana with the following command:

   sudo grafana-cli plugins install vertamedia-clickhouse-datasource
   

2. Restart Grafana:

   sudo systemctl restart grafana-server
   

Deploy Grafana via Docker

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:

1. Start Docker and specify the ports, the container name, and the plugins to install with the following:

   docker run -d \
       -p 3000:3000 \
       --name=grafana \
       -e "GF_INSTALL_PLUGINS=vertamedia-clickhouse-datasource" \
       grafana/grafana
   

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

2 - How to Connect Grafana to ClickHouse

Once Grafana has been installed with the ClickHouse plugins, dashboards and charts can be created that draw off of data in the CLickHouse server.

Connecting Grafana to ClickHouse

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.

These instructions assume that you have installed Grafana with the ClickHouse plugins. For more information, see How to Install Grafana and ClickHouse Plugins.

Create Grafana Data Source with the ClickHouse Plugin

To create a new Grafana Data Source connected to a ClickHouse server:

1. Connect to your Grafana server from a browser.

2. From the left navigation panel, select Settings -> Data sources.

3. Select Add Data Source.

   

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

1. Enter the following:
   1. Name: The name for the connection. This does not need to be the name of the ClickHouse server or database.
   2. URL: For localhost, we need to specify both the protocol and the port. In this case, it will be http://localhost:8123.
   3. Auth: Only required if a ClickHouse username and password are specified. If there are no ClickHouse credentials to use, then leave this section disabled.
   4. Additional:
      1. Use Post method to send queries: True
      2. Default database: Select the database to be used without having to specify it in connections.
2. 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:

• Server: https://github.demo.trial.altinity.cloud
• Port: 8443
• Database: default
• Username: demo
• Password: demo

1. Enter the following:

   1. Name: The name for the connection. This does not need to be the name of the ClickHouse server or database.

   2. 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:

      1. connectiontype: HTTP or HTTPS
      2. url: The fully qualified domain name or IP address of the ClickHouse server.
      3. port: The port being used. By default, HTTP is port 8123, HTTPS is port 8443.

   3. Auth: Only required if a ClickHouse username and password are specified.

      1. Basic auth: True
      2. With Credentials: True

   4. Basic Auth Details: Only required if Basic auth is set to True.

      1. User: The ClickHouse username.
      2. Password: The ClickHouse user password.

   5. Additional:

      1. Use Post method to send queries: True
      2. Default database: Select the database to be used without having to specify it in connections.
2. Select Save & test.

If the Save & test is successful, then the data source is available for use.

3 - Create Grafana Dashboards from ClickHouse Data

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:

1. From the left navigation panel, select + -> Dashboard.
2. Select the Save icon from the upper right menu.
3. 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.

For more information on creating a Grafana Data Source with this sample Altinity.Cloud database, see How to Connect Grafana to ClickHouse

Create Big Stat Panel

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:

1. From Grafana, select the Dashboard to add the Stat panel to.
2. From the upper right hand menu select the Panel icon, then select Add Panel.
3. Select Add an Empty Panel.
4. Set the following:

   1. From the upper right hand corner, select the Visualization then select Stat.

   2. Set the Data source to your ClickHouse Data Source.

   3. In the Query field:

      1. Set the name by selecting the Name field and filling in whatever name you choose. In this case, it can be Total unique carriers.
      2. From
         1. 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.
         2. Set –table– to ontime.

   4. 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:

      select countDistinct(Carrier) FROM $table
      

   5. Set Format as Table.

5. 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:

1. From Grafana, select the Dashboard to add the Time Series panel to.

2. From the upper right hand menu select the Panel icon, then select Add Panel.

3. Select Add an Empty Panel.

4. Set the following:

   1. Set the Visualization in the upper right corner to Time Series.

   2. Set the Data source to your ClickHouse data source.

   3. In Query Options:

      1. Set Relative Time to 1M. This will accumulate one calendar month of data at a time.
      2. Time shift to 24M. This will have Grafana query 24 months of previous data from the current date.

   4. In the Query section:

      

      1. 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.
      2. From:
         1. 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.
         2. Set –table– to ontime.
      3. For the time series chart, set the following:
         1. Column:DateTime.
         2. In the –dateTime:col– field, set this to toDateTime(FlightDate). This informs Grafana of what field will be used for the time measurement.
         3. Set Column:Date to Column:Date.

   5. Select the query Edit icon and enter the following query:

      SELECT
          $timeSeries AS t,
          Carrier,
          count() Flights
      FROM $table
      
      WHERE $timeFilter
      
      GROUP BY
          t,
          Carrier
      ORDER BY
          t,
          Carrier
      

   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.

5. 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:

1. From Grafana, select the Dashboard to add the World Map panel to.

2. From the upper right hand menu select the Panel icon, then select Add Panel.

3. Select Add an Empty Panel.

4. Set the Visualization in the upper right corner to Worldmap Panel and set the following:

   

   1. Title: Create a title for the panel, such as Cancellations by Airport.

   2. Worldmap:

      1. Center: North America.
      2. Initial Zoom: 4
      3. Min Circle Size: 1
      4. Max Circle Size: 10

   3. Map Data Options:

      1. Location Data: table
      2. Aggregation: total

   4. Field Mapping:

      1. Table Query Format: coordinates
      2. Location Name Field: Origin
      3. Metric Field: Cancelled
      4. Latitude Field: Origin_Latitude
      5. Longitude Field: Origin_Longitude

   5. Threshold Options:

      

      1. Thresholds: 10000,50000
      2. Colors: Set the colors to Green, Orange and Red.

5. In the Data Source, select the ClickHouse data source created from How to Connect Grafana to ClickHouse.

6. In the Query section:

   1. Set the name by selecting the Name field and filling in whatever name you choose. In this case, it can be Flight Origins and Destinations.

   2. Select Edit icon and use the following query:

      SELECT Origin, 
          oa.Latitude as Origin_Latitude, oa.Longitude AS Origin_Longitude,  
          Flights, Cancelled, DepDelayed, ArrDelayed
      FROM (
      SELECT Origin, count() Flights,
          sum(Cancelled) Cancelled, 
          sum(DepDel15) DepDelayed,
          sum(ArrDel15) ArrDelayed
      FROM default.ontime
      GROUP BY Origin ORDER BY Cancelled DESC, Origin
      ) AS o 
      INNER JOIN default.airports AS oa ON toString(o.Origin) = oa.IATA
      LIMIT 100
      

   3. Set the Format as option as Table.

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

ACOS( (SIN (RADIANS (LOOKUP (AVG ( [dropoff_latitude] ), [IsJFK] ) ) ) * SIN (RADIANS (AVG ( [dropoff_latitude] ) ) ) + COS (RADIANS (LOOKUP (AVG ( [dropoff_latitude] ) ), [IsJFK] ) ) ) * COS (RADIANS (AVG ( [dropoff_latitude] ) ) ) ) * COS (RADIANS (AVG ( [dropoff_longitude] ) ) - RADIANS (LOOKUP (AVG ( [dropoff_longitude] ) , [IsJFK] ) ) ) ) * 3959