Using the Amazon Redshift data source

IAM policies

Grafana needs permissions granted using IAM to be able to read Redshift metrics. You can attach these permissions to IAM roles and utilize Grafana's built-in support for assuming roles. The built-in Amazon Grafana Redshift access policy is defined in the AWS managed policy: AmazonGrafanaRedshiftAccess section.

Query Amazon Redshift data

Amazon Redshift data source provides a standard SQL query editor. Amazon Managed Grafana includes some macros to help with writing more complex timeseries queries.

Macros

Macro	Description	Output example
$__timeEpoch(column)	$__timeEpoch will be replaced by an expression to convert to a UNIX timestamp and rename the column to time	UNIX_TIMESTAMP(dateColumn) as "time"
$__timeFilter(column)	$__timeFiltercreates a conditional that filters the data (using column) based on the time range of the panel	time BETWEEN '2017-07-18T11:15:52Z' AND '2017-07-18T11:15:52Z'
$__timeFrom()	$__timeFrom outputs the current starting time of the range of the panel with quotes	'2017-07-18T11:15:52Z'
$__timeTo()	$__timeTo outputs the current ending time of the range of the panel with quotes	'2017-07-18T11:15:52Z'
$__timeGroup(column, '1m')	$__timeGroup groups timestamps so that there is only 1 point for every period on the graph	floor(extract(epoch from time)/60)*60 AS "time"
$__schema	$__schema uses the selected schema	public
$__table	$__table outputs a table from the given $__schema (it uses the public schema by default)	sales
$__column	$__column outputs a column from the current $__table	date
$__unixEpochFilter(column)	$__unixEpochFilter be replaced by a time range filter using the specified column name with times represented as Unix timestamp	column >= 1624406400 AND column <= 1624410000
$__unixEpochGroup(column)	$__unixEpochGroup is the same as $__timeGroup but for times stored as Unix timestamp	floor(time/60)*60 AS "time"

Visualization

Most queries in Redshift are best represented by a table visualization. Any query will display data in a table. If it can be queried, then it can be put in a table.

This example returns results for a table visualization:

SELECT {column_1}, {column_2} FROM {table};

Time series and graph visualizations

For time series and graph visualizations, there are a few requirements:

• A column with a date or a datetime type must be selected.

• The date column must be in ascending order (using ORDER BY column ASC).

• You must select a numeric column.

To make a more reasonable graph, be sure to use the $__timeFilter and $__timeGroup macros.

Example timeseries query:

SELECT
  avg(execution_time) AS average_execution_time,
  $__timeGroup(start_time, 'hour'),
  query_type
FROM
  account_usage.query_history
WHERE
  $__timeFilter(start_time)
group by
  query_type,start_time
order by
  start_time,query_type ASC;

Fill mode

Grafana also autocompletes frames without a value with some default. To configure this value, change the Fill Value in the query editor.

Inspecting the query

Because Grafana supports macros that Redshift does not, the fully rendered query, which can be copied and pasted directly into Redshift, is visible in the Query Inspector. To view the full interpolated query, choose the Query Inspector menu, and the full query is visible on the Query tab.

Templates and variables

For more information about how to add a new Redshift query varialble, see Adding a query variable. Use your Redshift data source as your data source for the available queries.

Any value queried from a Amazon Redshift table can be used as a variable. Be sure to avoid selecting too many values, as this can cause performance issues.

After creating a variable, you can use it in your Redshift queries by using Variable syntax. For more information about variables, see Templates and variables.

Annotations

Annotations allows you to overlay rich event information on top of graphs. You can add annotations by selecting the panel or by adding annotation queries using the Annotations view, opened from the Dashboard menu.

Example query to automatically add annotations:

SELECT
  time as time,
  environment as tags,
  humidity as text
FROM
  $__table
WHERE
  $__timeFilter(time) and humidity > 95

The following table represents the values of the columns taken into account to render annotations:

Name	Description
Time	The name of the date or time field. Could be a column with a native SQL date or time data type or epoch value.
Timeend	Optional name of the end date or time field. Could be a column with a native SQL dateor time data type or epoch value.
Text	Event description field.
Tags	Optional field name to use for event tags as a comma separated string.