Using Amazon Redshift with Lookout for Metrics

You can use Amazon Redshift as a datasource for an Amazon Lookout for Metrics detector. With Amazon Redshift, you can choose columns to monitor (measures) and columns that segment measure values (dimensions). The detector monitors the values in these columns to find anomalies in your data.

To use an Amazon Redshift data warehouse with Lookout for Metrics, the table must have a timestamp column that is defined as the sort key. Amazon Redshift uses the sort key to store data on disk and construct plans that exploit the way that the data is stored. For more information, see Working with sort keys in the Amazon Redshift Database Developer Guide.

You also need an AWS Secrets Manager secret for the detector. The secret must have the database password and have a name that starts with AmazonLookoutMetrics-.

The detector imports data at the end of each interval. You configure an offset to allow time after an interval ends for all data to be written. For example, if you choose an offset of 30 seconds, the detector waits 30 seconds after the end of each interval before reading data for that interval.

Before you configure the dataset, you need to know the following information.

To configure metrics in Lookout for Metrics, you choose columns to be measures and dimensions. Each measure is a column with a numerical value that you want to monitor for anomalies. Each dimension is a column with a string value that segments the measure(s). A metric in Lookout for Metrics is a combination of a measure value and a dimension value, aggregated within an interval. For example, average availability in Colorado, or maximum temperature in furnace 17.

The detector reads new data from Amazon Redshift periodically, by querying records with timestamps in the most recently completed interval. If it detects any anomalies in the metrics for the interval, it records an anomaly and sends anomaly alerts, if configured.

When you activate the detector, it uses data from several intervals to learn, before attempting to find anomalies. For a five minute interval, the training process takes approximately one day. Training time varies depending on the detector's interval.

For more information about Amazon Redshift, see Getting started with Amazon Redshift in the Amazon Redshift Getting Started Guide.

Sample IAM policies

The GitHub repository for this guide provides sample IAM policies that you can use as reference for developing service roles. You can use a single role that grants permission for both importing data and sending alerts by combining the applicable policies.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Action": [
                "redshift:DescribeClusters",
                "redshift:DescribeClusterSubnetGroups"
            ],
            "Resource": "*",
            "Effect": "Allow"
        },
        {
            "Action": [
                "secretsmanager:GetSecretValue"
            ],
            "Resource": [
                "arn:aws:secretsmanager:${Region}:${Account}:secret:${SecretId}"
            ],
            "Effect": "Allow",
            "Condition": {
                "ForAllValues:StringEquals": {
                    "secretsmanager:VersionStage": "AWSCURRENT"
                }
            }
        },
        ...

The second sample policy shows how to grant the detector permission to connect to a cluster across accounts. The account with the cluster (Account B) must be in the same organization and share its subnet with the account that contains the detector (AccountA).

        ...
        {
            "Action": [
                "ec2:CreateNetworkInterface"
            ],
            "Resource": [
                "arn:aws:ec2:${Region}:${AccountA}:network-interface/*",
                "arn:aws:ec2:${Region}:${AccountA}:security-group/*",
                "arn:aws:ec2:${Region}:${AccountB}:subnet/${SubnetId}"
            ],
            "Effect": "Allow"
        },
        ...

For more information, see Working with shared VPCs in the Amazon VPC User Guide.