Construct and deploy {custom} connectors for Amazon Redshift with Amazon Lookout for Metrics

0/5 No votes

Report this app



Amazon Lookout for Metrics detects outliers in your time collection knowledge, determines their root causes, and allows you to shortly take motion. Constructed from the identical expertise utilized by, Lookout for Metrics displays 20 years of experience in outlier detection and machine studying (ML). Learn our GitHub repo to study extra about how to consider your knowledge when establishing an anomaly detector.

On this publish, we talk about the way to construct and deploy {custom} connectors for Amazon Redshift utilizing Lookout for Metrics.

Introduction to time collection knowledge

You should use time collection knowledge to measure and monitor any values that shift from one time limit to a different. A easy instance is inventory costs over a given time interval or the variety of clients seen per day in a storage. You should use these values to identify developments and patterns and make higher selections about probably future occasions. Lookout for Metrics allows you to construction vital knowledge right into a tabular format (like a spreadsheet or database desk), to offer historic values to study from, and to offer steady values of knowledge.

Join your knowledge to Lookout for Metrics

Since launch, Lookout for Metrics has supported offering knowledge from the next AWS companies:

It additionally helps exterior knowledge sources reminiscent of Salesforce, Marketo, Dynatrace, ServiceNow, Google Analytics, and Amplitude, all through Amazon AppFlow.

These connectors all help steady supply of latest knowledge to Lookout for Metrics to study to construct a mannequin for anomaly detection.

Native connectors are an efficient choice to get began shortly with CloudWatch, Amazon S3, and through Amazon AppFlow for the exterior companies. Moreover, these work nice in your relational database administration system (RDBMS) knowledge you probably have saved your info in a singular desk, or you may create a process to populate and preserve that desk going ahead.

When to make use of a {custom} connector

In circumstances the place you need extra flexibility, you should use Lookout for Metrics {custom} connectors. In case your knowledge is in a state that requires an extract, remodel, and cargo (ETL) course of, reminiscent of becoming a member of from a number of tables, reworking a collection of values right into a composite, or performing any advanced postprocessing earlier than delivering the info to Lookout for Metrics, you should use {custom} connectors. Moreover, should you’re beginning with knowledge in an RDBMS and also you want to present a historic pattern for Lookout for Metrics to study from first, it is best to use a {custom} connector. This lets you feed in a big quantity of historical past first, bypassing the coldstart necessities and attaining a better high quality mannequin sooner.

For this publish, we use Amazon Redshift as our RDBMS, however you may modify this method for different methods.

It’s best to use {custom} connectors within the following conditions:

  • Your knowledge is unfold over a number of tables
  • It’s essential carry out extra advanced transformations or calculations earlier than it matches to a detector’s configuration
  • You wish to use all of your historic knowledge to coach your detector

For a faster begin, you should use built-in connectors within the following conditions:

  • Your knowledge exists in a singular desk that solely incorporates info utilized by your anomaly detector
  • You’re comfy utilizing your historic knowledge after which ready for the coldstart interval to elapse earlier than starting anomaly detection

Answer overview

All content material mentioned on this publish is hosted on the GitHub repo.

For this publish, we assume that you just’re storing your knowledge in Amazon Redshift over just a few tables and that you just want to join it Lookout for Metrics for anomaly detection.

The next diagram illustrates our answer structure.

Solution Architecture

At a excessive degree, we begin with an AWS CloudFormation template that deploys the next parts:

  • An Amazon SageMaker pocket book occasion that deploys the {custom} connector answer.
  • An AWS Step Capabilities workflow. Step one performs a historic crawl of your knowledge; the second configures your detector (the skilled mannequin and endpoint for Lookout for Metrics).
  • An S3 bucket to deal with all of your AWS Lambda capabilities as deployed (omitted from the structure diagram).
  • An S3 bucket to deal with all of your historic and steady knowledge.
  • A CloudFormation template and Lambda operate that begins crawling your knowledge on a schedule.

To switch this answer to suit your personal surroundings, replace the next:

  • A JSON configuration template that describes how your knowledge ought to look to Lookout for Metrics and the title of your AWS Secrets and techniques Supervisor location used to retrieve authentication credentials.
  • A SQL question that retrieves your historic knowledge.
  • A SQL question that retrieves your steady knowledge.

After you modify these parts, you may deploy the template and be up and operating inside an hour.

Deploy the answer

To make this answer explorable from finish to finish, we have now included a CloudFormation template that deploys a production-like Amazon Redshift cluster. It’s loaded with pattern knowledge for testing with Lookout for Metrics. It is a pattern ecommerce dataset that tasks roughly 2 years into the long run from the publication of this publish.

Create your Amazon Redshift cluster

Deploy the offered template to create the next sources in your account:

  • An Amazon Redshift cluster inside a VPC
  • Secrets and techniques Supervisor for authentication
  • A SageMaker pocket book occasion that runs all of the setup processes for the Amazon Redshift database and preliminary dataset loading
  • An S3 bucket that’s used to load knowledge into Amazon Redshift

The next diagram illustrates how these parts work collectively.

Production Redshift Setup

We offer Secrets and techniques Supervisor with credential info in your database, which is handed to a SageMaker pocket book’s lifecycle coverage that runs on boot. As soon as booted, the automation creates tables inside your Amazon Redshift cluster and masses knowledge from Amazon S3 into the cluster to be used with our {custom} connector.

To deploy these sources, full the next steps:

  1. Select Launch Stack:
  2. Select Subsequent.
    Setup step described by text
  3. Go away the stack particulars at their default and select Subsequent once more.Setup step described by text
  4. Go away the stack choices at their default and select Subsequent once more.Setup step described by text
  1. Choose I acknowledge that AWS CloudFormation may create IAM sources, then Select Create stack.Setup step described by text

The job takes a couple of minutes to finish. You’ll be able to monitor its progress on the AWS CloudFormation console.

CloudFormation Status

When the standing adjustments to CREATE_COMPLETE, you’re able to deploy the remainder of the answer.

Stack Complete

Information construction

We now have taken our commonplace ecommerce dataset and cut up it into three particular tables in order that we are able to be a part of them later through the {custom} connector. Most likely, your knowledge is unfold over varied tables and must be normalized in the same method.

The primary desk signifies the consumer’s platform, (what sort of machine customers are utilizing, reminiscent of cellphone or internet browser).

The following desk signifies our market (the place the customers are positioned).

Our ecommerce desk reveals the overall values for views and income right now.

ID TS Platform Market Views Income
1 01/10/2022 10:00:00 1 1 90 2458.90

Once we run queries later on this publish, they’re in opposition to a database with this construction.

Deploy a {custom} connector

After you deploy the earlier template, full the next steps to deploy a {custom} connector:

  1. On the AWS CloudFormation console, navigate to the Outputs tab of the template you deployed earlier.
    Outputs Link
  2. Observe the worth of RedshiftCluster and RedshiftSecret, then save them in a brief file to make use of later.
    Output Values
  3. Select Launch stack to deploy your sources with AWS CloudFormation:
  4. Select Subsequent.
    CloudFormation Setup
  5. Replace the worth for the RedshiftCluster and RedshiftSecret with the knowledge you copied earlier.
  6. Select Subsequent.CloudFormation Setup
  7. Go away the stack choices at their default and select Subsequent.Cloudformation Setup
  8. Choose I acknowledge that AWS CloudFormation may create IAM sources, then select Create stack.Cloudformation Setup

The method takes 30–40 minutes to finish, after which you might have a totally deployed answer with the demo surroundings.

View your anomaly detector

After you deploy the answer, you may find your detector and evaluation any discovered anomalies.

  1. Register to the Lookout for Metrics console in us-east-1.
  2. Within the navigation pane, select Detectors.Lookout for Metrics Detectors Link

The Detectors web page lists all of your energetic detectors.

  1. Select the detector l4m-custom-redshift-connector-detector.

Now you may view your detector’s configuration, configure alerts, and evaluation anomalies.

To view anomalies, both select Anomalies within the navigation web page or select View anomalies on the detector web page.
View Anomalies Link

After a time period, normally no quite a lot of days, it is best to see a listing of anomalies on this web page. You’ll be able to discover them in depth to view how the info offered appeared anomalous. In case you offered your personal dataset, the anomalies could solely present up after an uncommon occasion.

Anomalies List

Now that you’ve the answer deployed and operating, let’s talk about how this connector works in depth.

How a {custom} connector works

On this part, we talk about the connector’s core parts. We additionally display the way to construct a {custom} connector, authenticate to Amazon Redshift, modify queries, and modify the detector and dataset.

Core parts

You’ll be able to run the next parts and modify them to help your knowledge wants:

Whenever you deploy ai_ops/l4m-redshift-solution.yaml, it creates the next:

  • An S3 bucket for storing all Lambda capabilities.
  • A job for a SageMaker pocket book that has entry to switch all related sources.
  • A SageMaker pocket book lifecycle config that incorporates the startup script to clone all automation onto the pocket book and handle the params.json file. And runs the shell script (ai_ops/ to deploy the AWS SAM functions and additional replace the params.json file.

ai_ops/ begins by deploying ai_ops/template.yaml, which creates the next:

  • An S3 bucket for storing the params.json file and all enter knowledge for Lookout for Metrics.
  • An S3 bucket coverage to permit Lookout for Metrics to speak with Amazon S3.
  • A Lambda operate that’s invoked on the bucket when the params.json file is uploaded and begins the Step Capabilities state machine.
  • An AWS Identification and Entry Administration (IAM) function to run the state machine.
  • A shared Lambda layer of help capabilities.
  • A job for Lookout for Metrics to entry knowledge in Amazon S3.
  • A Lambda operate to crawl all historic knowledge.
  • A Lambda operate to create and activate a Lookout for Metrics detector.
  • A state machine that manages the move between creating that historic dataset and the detector.

After ai_ops/ creates the primary batch of things, it updates the params.json file with new related info from the detector and the IAM roles. It additionally modifies the Amazon Redshift cluster to permit the brand new function for Lookout for Metrics to speak with the cluster. After sleeping for 30 seconds to facilitate IAM propagation, the script copies the params.json file to the S3 bucket, which invokes the state machine deployed already.

Then the script deploys one other AWS SAM software outlined in l4m-redshift-continuous-crawl.yaml. This easy software defines and deploys an occasion set off to provoke the crawling of stay knowledge on a schedule (hourly for instance) and a Lambda operate that performs the crawl.

Each the historic crawled knowledge and the constantly crawled knowledge arrives in the identical S3 bucket. Lookout for Metrics makes use of the knowledge first for coaching, then as inference knowledge, the place it’s checked for anomalies because it arrives.

Every Lambda operate additionally incorporates a question.sql file that gives the bottom question that’s handed to Amazon Redshift. Later the capabilities append UNLOAD to every question and ship the info to Amazon S3 through CSV.

Construct a {custom} connector

Begin by forking this repository into your personal account or downloading a duplicate for personal improvement. When making substantial adjustments, be sure that the references to this specific repository within the following information are up to date and level to publicly accessible endpoints for Git:

  • – This file, particularly the Launch stack buttons, assumes you’re utilizing the stay model you see on this repository solely
  • ai_ops/l4m-redshift-solution.yaml – On this template, a Jupyter pocket book lifecycle configuration defines the repository to clone (deploys the {custom} connector)
  • sample_resources/redshift/l4m-redshift-sagemakernotebook.yaml – On this template, a Amazon SageMaker Pocket book lifecycle configuration defines the repository to clone (deploys the manufacturing Amazon Redshift instance).

Authenticate to Amazon Redshift

When exploring the way to lengthen this into your personal surroundings, the very first thing to contemplate is the authentication to your Amazon Redshift cluster. You’ll be able to accomplish this by utilizing the Amazon Redshift Information API and by storing the credentials inside AWS Secrets and techniques Handler.

In Secrets and techniques Supervisor, this answer appears to be like for the recognized secret title redshift-l4mintegration and incorporates a JSON construction like the next:

  "password": "DB_PASSWORD",
  "username": "DB_USERNAME",
  "dbClusterIdentifier": "REDSHIFT_CLUSTER_ID",
  "db": "DB_NAME",
  "host": "REDSHIFT_HOST",
  "port": 8192

If you wish to use a unique secret title than the one offered, you should replace the worth in ai_ops/l4m-redshift-solution.yaml. If you wish to change the opposite parameters’ names, you should seek for them within the repository and replace their references accordingly.

Modify queries to Amazon Redshift

This answer makes use of the Amazon Redshift Information API to permit for queries that may be run asynchronously from the consumer calling for them.

Particularly, it permits a Lambda operate to start out a question with the database after which let the DB engine handle the whole lot, together with the writing of the info in a desired format to Amazon S3. As a result of we let the DB engine deal with this, we simplify the operations of our Lambda capabilities and don’t have to fret about runtime limits. If you wish to carry out extra advanced transformations, you might wish to construct out extra Step Capabilities-based AWS SAM functions to deal with that work, even perhaps utilizing Docker containers over Lambda.

For many modifications, you may edit the question information saved within the two Lambda capabilities offered:

Take note of the continual crawl to be sure that the date ranges coincide along with your desired detection interval. For instance:

choose ecommerce.ts as timestamp, ecommerce.views, ecommerce.income, platform.title as platform, market.title as market
from ecommerce, platform, market
the place ecommerce.platform =
	and =
    and ecommerce.ts < DATEADD(hour, 0, getdate())
    and ecommerce.ts > DATEADD(hour, -1, getdate())

The previous code snippet is our demo steady crawl operate and makes use of the DATEADD operate to compute knowledge throughout the final hour. Coupled with the CloudWatch Occasions set off that schedules this operate for hourly, it permits us to stream knowledge to Lookout for Metrics reliably.

The work outlined within the question.sql information is barely a portion of the ultimate computed question. The total question is constructed by the respective Python information in every folder and appends the next:

  • IAM function for Amazon Redshift to make use of for the question
  • S3 bucket info for the place to put the information
  • CSV file export outlined

It appears to be like like the next code:

unload ('choose ecommerce.ts as timestamp, ecommerce.views, ecommerce.income, platform.title as platform, market.title as market
from ecommerce, platform, market
the place ecommerce.platform =
	and =
    and ecommerce.ts < DATEADD(hour, 0, getdate())
    and ecommerce.ts > DATEADD(hour, -1, getdate())') 
to 's3://BUCKET/ecommerce/stay/20220112/1800/' 
iam_role 'arn:aws:iam::ACCOUNT_ID:function/custom-rs-connector-LookoutForMetricsRole-' header CSV;

So long as your ready question will be encapsulated by the UNLOAD assertion, it ought to work with no points.

If you should change the frequency for the way usually the continual detector operate runs, replace the cron expression in ai_ops/l4m-redshift-continuous-crawl.yaml. It’s outlined within the final line as Schedule: cron(0 * * * ? *).

Modify the Lookout for Metrics detector and dataset

The ultimate parts give attention to Lookout for Metrics itself, primarily the detector and dataset configurations. They’re each outlined in ai_ops/params.json.

The included file appears to be like like the next code:

  "database_type": "redshift",  
  "detector_name": "l4m-custom-redshift-connector-detector",
    "detector_description": "A fast pattern config of the way to use L4M.",
    "detector_frequency": "PT1H",
    "timestamp_column": {
        "ColumnFormat": "yyyy-MM-dd HH:mm:ss",
        "ColumnName": "timestamp"
    "dimension_list": [
    "metrics_set": [
            "AggregationFunction": "SUM",
            "MetricName": "views"
            "AggregationFunction": "SUM",
            "MetricName": "revenue"
    "metric_source": {
        "S3SourceConfig": {
            "FileFormatDescriptor": {
                "CsvFormatDescriptor": {
                    "Charset": "UTF-8",
                    "ContainsHeader": true,
                    "Delimiter": ",",
                    "FileCompression": "NONE",
                    "QuoteSymbol": """
            "HistoricalDataPathList": [
            "RoleArn": "arn:aws:iam::ACCOUNT_ID:function/id-ml-ops2-LookoutForMetricsRole-IZ5PL6M7YKR1",
            "TemplatedPathList": [
    "s3_bucket": "",
    "alert_name": "alerter",
    "alert_threshold": 1,
    "alert_description": "Exports anomalies into s3 for visualization",
    "alert_lambda_arn": "",
    "offset": 300,
    "secret_name": "redshift-l4mintegration"

ai_ops/params.json manages the next parameters:

  • database_type
  • detector_name
  • detector_description
  • detector_frequency
  • timestamp_column and particulars
  • dimension_list
  • metrics_set
  • offset

Not each worth will be outlined statically forward of time; these are up to date by ai_ops/

  • HistoricalDataPathList
  • RoleArn
  • TemplatedPathList
  • s3_bucket

To switch any of those entities, replace the file chargeable for them and your detector is modified accordingly.

Clear up

Comply with the steps on this part to scrub up all sources created by this answer and be sure you’re not billed after evaluating or utilizing the answer.

  1. Empty all knowledge from the S3 buckets that had been created from their respective templates:
    1. ProductionRedshiftDemoS3ContentBucket
    2. CustomRedshiftConnectorS3LambdaBucket
    3. custom-rs-connectorInputBucket
  2. Delete your detector through the Lookout for Metrics console.
  3. Delete the CloudFormation stacks within the following order (look ahead to one to finish earlier than transferring onto the subsequent):
    1. custom-rs-connector-crawl
    2. custom-rs-connector
    3. CustomRedshiftConnector
    4. ProductionRedshiftDemo


You will have now seen the way to join an Amazon Redshift database to Lookout for Metrics utilizing the native Amazon Redshift Information APIs, CloudWatch Occasions, and Lambda capabilities. This method means that you can create related datasets based mostly in your info in Amazon Redshift to carry out anomaly detection in your time collection knowledge in only a few minutes. In case you can draft the SQL question to acquire the knowledge, you may allow ML-powered anomaly detection in your knowledge. From there, your anomalies ought to showcase anomalous occasions and allow you to perceive how one anomaly could also be brought on or impacted by others, thereby decreasing your time to understanding points vital to your online business or workload.

In regards to the Authors

Chris King is a Principal Options Architect in Utilized AI with AWS. He has a particular curiosity in launching AI companies and helped develop and construct Amazon Personalize and Amazon Forecast earlier than specializing in Amazon Lookout for Metrics. In his spare time he enjoys cooking, studying, boxing, and constructing fashions to foretell the result of fight sports activities.

Alex Kim is a Sr. Product Supervisor for Amazon Forecast. His mission is to ship AI/ML options to all clients who can profit from it. In his free time, he enjoys all varieties of sports activities and discovering new locations to eat.


Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.