Querying InfluxDB IOx Using the New Flight SQL Plugin for Grafana
Tags: grafana, monitoring, sql, arrow
Published: 2023-02-20 12:33:57

## A quick start guide to installation, configuration, and usage

[Grafana](https://www.influxdata.com/grafana/) has been a staple visualization tool used alongside InfluxDB since its inception. With the [release of InfluxDB Cloud powered by IOx](https://www.influxdata.com/blog/announcing-general-availability-new-database-engine), there is now a new way to integrate InfluxDB and Grafana: **[Flight SQL](https://arrow.apache.org/blog/2022/02/16/introducing-arrow-flight-sql/)**. 

Two of our engineers, Brett and Helen, have been working hard to create a new Grafana plugin called Flight SQL. This open-source plugin allows users to perform [SQL](https://www.influxdata.com/products/sql/) queries directly against InfluxDB IOx and other storage engines compatible with [Apache DataFusion](https://www.influxdata.com/glossary/apache-datafusion/). This blog post provides a quick start guide to installing, configuring, and utilizing the data source plugin. 


![dashboard](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sk6vbhhwwxe3kmiekc84.png)



**Note: If you are looking for an InfluxDB OSS tutorial with either Flux or InfluxQL, check out the following [blog](https://www.influxdata.com/blog/getting-started-influxdb-grafana/).**


## Installing the plugin

We are currently releasing the Flight SQL plugin for Grafana as an experimental package. To install it, check out the most up-to-date instructions [here](https://docs.influxdata.com/influxdb/cloud-iox/visualize-data/grafana/). Since the package is currently experimental, it is unsigned and requires you to explicitly approve the installation of the plugin (The instructions will help you through this).


## Configuring the plugin

To configure the FlightSQL plugin check out the following [documentation](https://docs.influxdata.com/influxdb/cloud-iox/visualize-data/grafana/?t=Docker#configure-the-flight-sql-datasource). My top tip here is to make sure you specify your host URL in the following manner: 
```
us-east-1-1.aws.cloud2.influxdata.com:443
```

**You will find that if you include any protocols or trailing paths after the port, the connection will timeout.**

Once you complete the steps in the above documentation, you will receive the following connection success.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4n5geczphz9o23ro2cxc.png)


## Quick Start examples

If you would like to skip some of the manual setups and are familiar with Docker, I also created a repository called [InfluxDB-IOx-Quick-Starts](https://github.com/InfluxCommunity/InfluxDB-IOx-Quick-Starts). This repository contains a series of Telegraf and Grafana examples deployed using Docker-Compose. To utilize this repository, run the following instructions: 

1. Clone the repository:

    ```bash
    clone https://github.com/InfluxCommunity/InfluxDB-IOx-Quick-Starts.git 
    ```

2. Create an environment file within the repository

    ```bash

    cd InfluxDB-IOx-Quick-Starts && touch .env 

    ```

3. Add the following environment variables to the file:

    ```
    export INFLUX_HOST=<INSERT_HOST> 

    export INFLUX_TOKEN=<INSERT_TOKEN>

    export INFLUX_ORG=<INSERT_ORG>

    export INFLUX_BUCKET=<INSERT_BUCKET_NAME>

    ```


    **Note: make sure to remember to only include the host:port like so:**


    ```
    us-east-1-1.aws.cloud2.influxdata.com:443
    ```


    **Save Changes.**

4. Source your newly created environment file:

    ```bash
    source .env
    ```

5. Now you can deploy both Telegraf and Grafana using docker-compose:

	```bash
	docker-compose -f system-monitoring/docker-compose.yml up -d
	```

Once the quick start is running, you can access Grafana at [http://localhost:3000](http://localhost:3000). You can log in with the default **username** and **password**: **admin** and **admin**. 


## Dashboard overview

Now that we installed and configured the plugin, let's take a tour of an example Grafana dashboard using the Flight SQL data source. Linux System is a beloved dashboard with the InfluxDB Community, so we will use this dashboard as an example of how to convert Flux to SQL.


### Global variables for Flight SQL

Before we move on to the query conversions, it is worth mentioning that there are a series of global Grafana variables you can use to make your queries dynamic and streamlined. Here is a non-exhaustive list:


<table>
  <tr>
   <td><strong>Variable</strong>
   </td>
   <td><strong>Description</strong>
   </td>
  </tr>
  <tr>
   <td>$__timeRange(time)
   </td>
   <td>This variable allows you to dynamically set the query time range. For instance, if you selected to see the last 15 minutes of data from the drop-down you would see an equivalent example to the conversion column.
<p>
<strong>Example Conversion:</strong> 
’2023-01 01T00:00:00Z’ and time &lt;= ’2023-01-01T01:00:00Z’
   </td>
  </tr>
  <tr>
   <td>$__dateBin(time)
   </td>
   <td>This variable is a dynamic short form for creating an interval window, and is useful for aggregation. The interval is set based on the dashboard specification.  

<p>
<strong>Example Conversion:</strong> 
date_bin(interval ’30 second’, time, interval ’1970-01-01T00:00:00Z’)
   </td>
  </tr>
  <tr>
   <td>$__timeGroup(time, hour)
   </td>
   <td>As we know ```dateBin``` is effectively a window period. This gives us a way to reduce the amount of data returned via aggregations or selectors. ```timeGroup``` differs by producing projections that can be grouped on.
<p>
<strong>Example Conversion:</strong> 
datepart(’minute’, time),datepart(’hour’, time),datepart(’day’, time),datepart(’month’, time),datepart(’year’, time);
   </td>
  </tr>
</table>


You can find a full variable list by clicking **Show Query Help** while constructing your query: 

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cwlq0utvj7z6h9z5x6cb.png)


### Get the list of hosts

The InfluxDB dashboard introduced a dashboard variable that allowed you to filter based on the hostname tag. Let's take a look at the conversion.


<table>
  <tr>
   <td><strong>Flux</strong>
   </td>
   <td><strong>FlightSQL</strong>
   </td>
  </tr>
  <tr>
   <td>

import "influxdata/influxdb/v1"
v1.measurementTagValues(bucket: v.bucket, measurement: "cpu", tag: "host")

   </td>
   <td>

SELECT distinct (host) FROM cpu 
WHERE $__timeRange(time)

   </td>
  </tr>
</table>



### Aggregation Window (Average)

One of the most commonly used functions within Flux is the aggregate window. This allows us to window our time series data by a specific interval (e.g. 30 seconds, 2 minutes, 1 year) and then perform an aggregator (mean, mode, median, etc.) or a selector (max, min, first, last, etc.) function on the windowed data points.


<table>
  <tr>
   <td><strong>Flux</strong>
   </td>
   <td><strong>Flight SQL</strong>
   </td>
  </tr>
  <tr>
   <td>

from(bucket: v.bucket)
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r._measurement == "cpu")
  |> filter(fn: (r) => r._field == "usage_user" or r._field == "usage_system" or r._field == "usage_idle")
  |> filter(fn: (r) => r.cpu == "cpu-total")
  |> filter(fn: (r) => r.host == v.linux_host)
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)


   </td>
   <td>

SELECT
  $__dateBin(time) ,
  avg(usage_user) AS 'usage_user',
  avg(usage_system) AS 'usage_system',
FROM cpu
WHERE host='${linux_host}' AND cpu='cpu-total' AND  $__timeRange(time)
GROUP BY time

   </td>
  </tr>
</table>



### Calculating the derivative

Now let’s not beat around the bush here: not all time-based queries are simple within SQL. The derivative of a value with respect to time, also known as the rate of change of the value, is calculated by using the rules of differentiation. The derivative of a function gives us the rate of change of the function at a given point. This requires us to know the previous value as well as the current. Let's look at an example: 



<table>
  <tr>
   <td><strong>Flux</strong>
   </td>
   <td><strong>Flight SQL</strong>
   </td>
  </tr>
  <tr>
   <td>
from(bucket: v.bucket)
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r._measurement == "diskio")
  |> filter(fn: (r) => r._field == "read_bytes" or r._field == "write_bytes")
  |> filter(fn: (r) => r.host == v.linux_host)
  |> derivative(unit: v.windowPeriod, nonNegative: false)
   </td>
   <td>
SELECT time, (read_bytes_delta_v / delta_t_ns) * 1000000000 as read_bytes, (write_bytes_delta_v / delta_t_ns) * 1000000000 as write_bytes
FROM
(
SELECT
  (lag(read_bytes, 1) OVER (ORDER BY time))  - read_bytes  as read_bytes_delta_v,
   (lag(write_bytes, 1) OVER (ORDER BY time))  - write_bytes  as write_bytes_delta_v,
  (lag(cast(time as bigint), 1) OVER (ORDER BY time)) - cast (time as bigint) as delta_t_ns,
  time
FROM
diskio
WHERE host='${linux_host}' AND $__timeRange(time)
) as sq
   </td>
  </tr>
</table>


For these types of examples, we plan to provide workarounds like this where possible and slowly develop a series of custom SQL functions to handle these complex time series calculations. 


## Conclusion

I hope this blog post provides enough insight into the new Flight SQL plugin for Grafana to start trialing it. My call to action for you is to start testing the plugin and leaving your feedback within the plugin [repository](https://github.com/influxdata/grafana-flightsql-datasource). This will help us improve the overall useability and feature set of the plugin. I would also like to take the time to thank Brett and Helen for their efforts in making an open-source plugin for Flight SQL.

Come join us on [Slack](https://www.influxdata.com/slack) and the [forums](https://community.influxdata.com/). Share your thoughts — I look forward to seeing you there!