# Connect to an Amazon Redshift data source
<a name="AWS-Redshift"></a>

**Note**  
In workspaces that support version 9 or newer, this data source might require you to install the appropriate plugin. For more information, see [Extend your workspace with plugins](grafana-plugins.md).

**Note**  
 This guide assumes that users are familiar with the Amazon Redshift service before using the Amazon Redshift data source. 

With Amazon Managed Grafana, you can add Amazon Redshift as a data source by using the AWS data source configuration option in the Grafana workspace console. This feature simplifies adding Amazon Redshift as a data source by discovering your existing Amazon Redshift accounts and manages the configuration of the authentication credentials that are required to access Amazon Redshift. You can use this method to set up authentication and add Amazon Redshift as a data source, or you can manually set up the data source and the necessary authentication credentials using the same method that you would on a self-managed Grafana server.

There are prerequisites for Amazon Redshift to be accessible by Amazon Managed Grafana. For prerequisites associated with using the Amazon Redshift data source, see [Prerequisites](Redshift-prereq.md).

# Prerequisites
<a name="Redshift-prereq"></a>

To use the AWS managed policies for Amazon Managed Grafana, complete the following tasks before you configure the Amazon Redshift data source:
+ Tag your Amazon Redshift cluster with `GrafanaDataSource: true`. Otherwise, it won't be accessible.
+ Create the database credentials in one of the following mutually exclusive ways:
  + If you want to use the default mechanism (the temporary credentials options) to authenticate against the Redshift database, you must create a database user named `redshift_data_api_user`.
  + If you want to use the credentials from Secrets Manager, you must tag the secret with `RedshiftQueryOwner: true`. For more information, see [Identity-based policy examples in Amazon Managed Grafana](https://docs.aws.amazon.com/grafana/latest/userguide/security_iam_id-based-policy-examples.html)in this guide.

# Use AWS data source configuration to add Amazon Redshift as a data source
<a name="Redshift-configure"></a>

# To use AWS data source configuration to add Amazon Redshift as a data source


1.  Ensure that your user role is admin or editor.

1.  Select the workspace that you want to work on from the Amazon Managed Grafana console at [https://console.aws.amazon.com/grafana/](https://console.aws.amazon.com/grafana/home/).

1. If you didn't choose to use service-managed permissions for this workspace when you created it, then change from using customer-managed permissions to use service-managed permissions to ensure that the proper IAM roles and policies are enabled for using the AWS data source configuration option in the Grafana workspace console. To do so, choose the edit icon by **IAM role** and then choose **Service managed**, **Save changes**. For more information, see [Amazon Managed Grafana permissions and policies for AWS data sources](AMG-manage-permissions.md). 

1. Choose the **Data sources** tab. Then select the check box for **Amazon Redshift**, and choose **Actions**, **Enable service-managed policy**.

1. Choose the **Data sources** tab again, and then choose **Configure in Grafana** in the **Amazon Redshift** row.

1. Sign into the Grafana workspace console using IAM Identity Center if necessary.

1. In the left navigation bar in the Grafana workspace console, choose the lower AWS icon (there are two) and then choose **Redshift**.

1. Select the default region that you want the Amazon Redshift data source to query from, and then select the accounts that you want, and then choose **Add data source**.

1.  Follow the steps to configure **Connection Details** in [**Connection details** settings](Redshift-config.md#Redshift-connection-details).

# Manually adding the Amazon Redshift data source
<a name="Redshift-add-the-data-source"></a>

## Prerequisites
<a name="Redshift-prerequisites"></a>
+  You have access to **Amazon Redshift** from your account.

**To add the Amazon Redshift data source:**

1. Attach the [AmazonRedshiftAccessPolicy](security-iam-awsmanpol.md#security-iam-awsmanpol-AmazonGrafanaRedshiftAccess) to your workspace user role.

1. Ensure your user role is admin or editor.

1.  Select the workspace you want to work on from the Amazon Managed Grafana console at [https://console.aws.amazon.com/grafana/](https://console.aws.amazon.com/grafana/home/).

1.  In the Grafana console side menu, pause on the **Configuration** (gear) icon, then choose **Data Sources**.

1. Choose **Add data source**.

1. Choose the **AWS Redshift** data source. If necessary, you can start typing **Redshift** in the search box to help you find it.

1. This opens the **Connection Details** page. Follow the steps in configuring the [**Connection details** settings](Redshift-config.md#Redshift-connection-details). 

# Configuring Amazon Redshift
<a name="Redshift-config"></a>

 After adding your Amazon Redshift data source to your workspace, configure Amazon Redshift settings as the following:

## Prerequisites
<a name="Redshift-prerequisites"></a>
+  You have access to **Amazon Redshift** from your account.

## **Connection details** settings
<a name="Redshift-connection-details"></a>

**Configure Connection details settings**

1.  In the **Connection Details** menu, select the authentication provider (recommended: **Workspace IAM Role**). 

1.  Choose the **Default Region** you want to query. 

## **Authentication** settings
<a name="Redshift-Authentication"></a>

**Configure **Authentication** settings**

1.  In the **Authentication** menu, choose either the **Temporary Credentials** or **AWS Secrets Manager** tab as your access credentials provider. For details on Temporary Credentials and AWS Secrets Manager, refer to [AWS managed policy: AmazonGrafanaRedshiftAccess](security-iam-awsmanpol.md#security-iam-awsmanpol-AmazonGrafanaRedshiftAccess)

1.  If you choose **Temporary credentials** which is by default, follow the steps below. If you choose **AWS Secrets Manager**, enter your **AWS Secrets Manager** credentials in the input fields. 

1.  Choose the **Cluster Identifier** of the cluster you created in Amazon Redshift.

   For more information about the Redshift cluster, see [ Redshift connections](https://docs.aws.amazon.com/redshift/latest/gsg/connection.html).

1.  Choose your targeted Redshift database.

1.  Select the database user you created for the above cluster. 

1.  Choose **Save & Test**. 

The following is an example of the ** Temporary Credentials** settings.

![\[Temporary Credentials example\]](http://docs.aws.amazon.com/grafana/latest/userguide/images/redshift.png)


The following is an example of the **AWS Secrets Manager** menu.

![\[Secrets Manager example\]](http://docs.aws.amazon.com/grafana/latest/userguide/images/secretsmanager.png)


# Using the Amazon Redshift data source
<a name="Redshift-using-the-data-source"></a>

## IAM policies
<a name="Redshift-policies"></a>

 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](security-iam-awsmanpol.md#security-iam-awsmanpol-AmazonGrafanaRedshiftAccess) section. 

## Query Amazon Redshift data
<a name="Redshift-query"></a>

 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  | 
| --- | --- | --- | 
|  \$1\$1\$1timeEpoch(column)  |  \$1\$1\$1timeEpoch will be replaced by an expression to convert to a UNIX timestamp and rename the column to time  |  UNIX\$1TIMESTAMP(dateColumn) as "time"  | 
|  \$1\$1\$1timeFilter(column)  |  \$1\$1\$1timeFiltercreates 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'  | 
|  \$1\$1\$1timeFrom()  |  \$1\$1\$1timeFrom outputs the current starting time of the range of the panel with quotes  | '2017-07-18T11:15:52Z' | 
|  \$1\$1\$1timeTo()  |  \$1\$1\$1timeTo outputs the current ending time of the range of the panel with quotes  | '2017-07-18T11:15:52Z' | 
|  \$1\$1\$1timeGroup(column, '1m')  |  \$1\$1\$1timeGroup groups timestamps so that there is only 1 point for every period on the graph  | floor(extract(epoch from time)/60)\$160 AS "time" | 
|  \$1\$1\$1schema  |  \$1\$1\$1schema  uses the selected schema  | public | 
|  \$1\$1\$1table  |   \$1\$1\$1table outputs a table from the given \$1\$1\$1schema (it uses the public schema by default)  | sales | 
|  \$1\$1\$1column  |  \$1\$1\$1column outputs a column from the current \$1\$1\$1table  | date  | 
|  \$1\$1\$1unixEpochFilter(column)  |  \$1\$1\$1unixEpochFilter be replaced by a time range filter using the specified column name with times represented as Unix timestamp  |   column >= 1624406400 AND column <= 1624410000  | 
|  \$1\$1\$1unixEpochGroup(column)  |  \$1\$1\$1unixEpochGroup is the same as \$1\$1\$1timeGroup but for times stored as Unix timestamp  | floor(time/60)\$160 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
<a name="using-redshift-templates-variables"></a>

For more information about how to add a new Redshift query varialble, see [Adding a query variable](variables-types.md#add-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](templates-and-variables.md#variable-syntax). For more information about variables, see [Templates and variables](templates-and-variables.md).

## Annotations
<a name="using-redshift-annotations"></a>

[Annotations](dashboard-annotations.md) 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.  |