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:

  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.

Grafana Dashboard Save

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.
Grafana Stat Panel

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:

      Grafana Time Series Data Settings
      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.

Grafana Time Series Settings

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:

    Grafana World Map Visual Options
    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:

      Grafana World Map 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.