Using Grafana variables

Grafana variables enable end-users of your dashboards to filter and customize visualizations.

Prerequisites

To complete this tutorial, you need a cursory knowledge of the Structured Query Language (SQL). The tutorial walks you through each SQL command, but it is helpful if you’ve seen SQL before.

Once your installation of TimescaleDB and Grafana are complete, ingest the data found in the NYC Taxi Cab tutorial and configure Grafana to connect to that database. Be sure to follow the full tutorial if you’re interested in background on how to use TimescaleDB.

Creating a variable

Our goal here is to create a variable which controls the type of ride displayed in the visual, based on the payment type used for the ride.

There are several types of payments, which we can see in the payment_types table:

  2. payment_type | description
  4. --------------+-------------
  6.             1 | credit card
  8.             2 | cash
  10.             3 | no charge
  12.             4 | dispute
  14.             5 | unknown
  16.             6 | voided trip
  18. (6 rows)

Grafana includes many types of variables, and variables in Grafana function just like variables in programming languages. We define a variable, and then we can reference it in our queries.

Define a new Grafana variable

To create a new variable, go to your Grafana dashboard settings, navigate to the ‘Variable’ option in the side-menu, and then click the ‘Add variable’ button.

In this case, we use the ‘Query’ type, where your variable is defined as the results of SQL query.

Under the ‘General’ section, we’ll name our variable payment_type and give it a type of Query. Then, we’ll assign it the label of “Payment Type,” which is how it appears in a drop-down menu.

Select your data source and supply the query:

  2. SELECT payment_type FROM payment_types;

Turn on ‘Multi-value’ and ‘Include All option’. This enables users of your dashboard to select more than one payment type. Our configuration should look like this:

Using a variable to filter the results in a Grafana visualization

Click ‘Add’ to save your variable.

Use the variable in a Grafana panel

Let’s edit the WorldMap panel we created in the Grafana geo-spatial queries tutorial. The first thing you’ll notice is that now that we’ve defined a variable for this dashboard, there’s now a drop-down for that variable in the upper left hand corner of the panel.

We can use this variable to filter the results of our query using the WHERE clause in SQL. Check and see if rides.payment_type is in the array of the variable, which we’ve named $payment_type.

Let’s modify our earlier query like so:

  2. SELECT time_bucket('5m', rides.pickup_datetime) AS time,
  4.        rides.trip_distance AS value,
  6.        rides.pickup_latitude AS latitude,
  8.        rides.pickup_longitude AS longitude
  10. FROM rides
  12. WHERE $__timeFilter(rides.pickup_datetime) AND
  14.   ST_Distance(pickup_geom,
  16.               ST_Transform(ST_SetSRID(ST_MakePoint(-73.9851,40.7589),4326),2163)
  18.   ) < 2000 AND
  20.   rides.payment_type IN ($payment_type)
  22. GROUP BY time,
  24.          rides.trip_distance,
  26.          rides.pickup_latitude,
  28.          rides.pickup_longitude
  30. ORDER BY time
  32. LIMIT 500;

Now we can use the drop-down to filter our rides based on the type of payment used:

Visualizing time series data in PostgreSQL using the Grafana Worldmap and filtering using a variable

Building dynamic panels using Grafana variables

We’ve seen how you can use a Grafana variable in a query. You can also use a Grafana variable to build dynamic panels. In our case, we’ve enabled people to select data based on the payment_type used for a taxi ride. We can also automatically create a graph panel for each of the payment types selected so that we can see those queries side-by-side.

Let’s first create a new graph panel that uses the $payment_type variable. This is your query:

  2. SELECT
  4.   --1--
  6.   time_bucket('5m', pickup_datetime) AS time,
  8.   --2--
  10.   COUNT(*)
  12. FROM rides
  14. WHERE $__timeFilter(pickup_datetime)
  16. AND rides.payment_type IN ($payment_type)
  18. GROUP BY time
  20. ORDER BY time

Now, let’s make this panel dynamic so that we have a separate panel for each variable that is checked in the drop-down. Start by changing the title of the panel to include the variable name. Go to the panel’s ‘General’ tab and change the ‘Title’ to the following:

  2. {$payment_type} Taxi Rides

In the ‘Repeating’ section, select the variable you want to generate dynamic panels based on. In this case, payment_type. You can have your dynamic panels generate vertically or horizontally. In this case, opt for repeating panels, 2 per row, horizontally:

Create a dynamic panel in Grafana

Save and refresh your dashboard. Select some payment types using the drop-down. Your dashboard should look something like this:

Dynamic panels in Grafana

Improving the Grafana filter to be human readable

But our filter isn’t very attractive. We can’t tell what ‘1’ means. Fortunately, when we set up our NYC Taxi Cab dataset, we created a payment_types table (which we queried earlier). The payment_types.description field has a more readable explanation of what each payment code means, for example, ‘credit card’, ‘cash’, and so on. Those readable descriptions are what we want in our drop-down.

Click ‘Dashboard settings’ (the “gear” icon in the upper-right of your Grafana visualizations). Select the ‘Variables’ tab on the left, and click the $payment_types variable. Modify your query to retrieve the description and store it in the __text field and retrieve the payment_type and store it in the __value field, like so:

  2. SELECT description AS "__text", payment_type AS "__value" FROM payment_types

Your configuration should look like this now:

Modify our grafana variable to be more human readable

There’s no need to alter the query for the WorldMap visualization itself. Whatever database column is assigned as __text is used whenever the variable is displayed and whatever is assigned to __value is used as the actual value when Grafana makes a query.

As you can see, a variable can be used in a query in much the same way you’d use a variable in any programming language.

Building dynamic panels using Grafana variables

We’ve seen how you can use a Grafana variable in a query. You can also use a Grafana variable to build dynamic panels, where panels are created automatically based on the values selected for a variable. In our case, we’ve enabled people to select data based on the payment_type used for a taxi ride. We want to automatically create a graph panel for each of the payment types selected so that we can see those queries side-by-side.

Let’s first create a new graph panel that uses the $payment_type variable. This is your query:

  2. SELECT
  4.   --1--
  6.   time_bucket('5m', pickup_datetime) AS time,
  8.   --2--
  10.   COUNT(*)
  12. FROM rides
  14. WHERE $__timeFilter(pickup_datetime)
  16. AND rides.payment_type IN ($payment_type)
  18. GROUP BY time
  20. ORDER BY time

Now, let’s make this panel dynamic so that we have a separate panel for each variable that is checked in the drop-down. Start by changing the title of the panel to include the variable name. Go to the panel’s ‘General’ tab and change the ‘Title’ to the following:

  2. {$payment_type} Taxi Rides

In the ‘Repeating’ section, select the variable you want to generate dynamic panels based on. In this case, payment_type. You can have your dynamic panels generate vertically or horizontally. In this case, opt for repeating panels, 2 per row, horizontally:

Create a dynamic panel in Grafana

Save and refresh your dashboard. Select some payment types using the drop-down. Your dashboard should look something like this:

Dynamic panels in Grafana

Summary

Complete your Grafana knowledge by following all the TimescaleDB + Grafana tutorials.