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

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

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

Introduction to time sequence information

You should utilize time sequence information 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 prospects seen per day in a storage. You should utilize these values to identify traits and patterns and make higher selections about doubtless future occasions. Lookout for Metrics lets you construction vital information right into a tabular format (like a spreadsheet or database desk), to offer historic values to be taught from, and to offer steady values of information.

Join your information to Lookout for Metrics

Since launch, Lookout for Metrics has supported offering information from the next AWS providers:

It additionally helps exterior information sources resembling Salesforce, Marketo, Dynatrace, ServiceNow, Google Analytics, and Amplitude, all through Amazon AppFlow.

These connectors all help steady supply of recent information to Lookout for Metrics to be taught 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 providers. Moreover, these work nice to your relational database administration system (RDBMS) information if in case you have saved your data in a singular desk, or you’ll be able to create a process to populate and keep 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 information is in a state that requires an extract, remodel, and cargo (ETL) course of, resembling becoming a member of from a number of tables, reworking a sequence 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 information in an RDBMS and also you want to present a historic pattern for Lookout for Metrics to be taught from first, you need to use a {custom} connector. This lets you feed in a big quantity of historical past first, bypassing the coldstart necessities and reaching a better high quality mannequin sooner.

For this publish, we use Amazon Redshift as our RDBMS, however you’ll be able to modify this method for different methods.

You need to use {custom} connectors within the following conditions:

• Your information is unfold over a number of tables
• You might want to carry out extra advanced transformations or calculations earlier than it suits to a detector’s configuration
• You wish to use all of your historic information to coach your detector

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

• Your information exists in a singular desk that solely incorporates data utilized by your anomaly detector
• You’re snug utilizing your historic information 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 simply’re storing your information in Amazon Redshift over a number of tables and that you simply want to join it Lookout for Metrics for anomaly detection.

The next diagram illustrates our resolution structure.

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

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

To change this resolution to suit your personal surroundings, replace the next:

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

After you modify these parts, you’ll be able to deploy the template and be up and operating inside an hour.

Deploy the answer

To make this resolution explorable from finish to finish, we’ve included a CloudFormation template that deploys a production-like Amazon Redshift cluster. It’s loaded with pattern information 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 assets 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 information into Amazon Redshift

The next diagram illustrates how these parts work collectively.

We offer Secrets and techniques Supervisor with credential data to 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 hundreds information from Amazon S3 into the cluster to be used with our {custom} connector.

To deploy these assets, full the next steps:

1. Select Launch Stack:
   
2. Select Subsequent.
   
3. Depart the stack particulars at their default and select Subsequent once more.
4. Depart the stack choices at their default and select Subsequent once more.

5. Choose I acknowledge that AWS CloudFormation would possibly create IAM assets, then Select Create stack.

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

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

Information construction

We’ve got taken our commonplace ecommerce dataset and cut up it into three particular tables in order that we will be part of them later through the {custom} connector. Most likely, your information is unfold over varied tables and must be normalized in an identical method.

The primary desk signifies the consumer’s platform, (what sort of gadget customers are utilizing, resembling telephone or net browser).

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

Our ecommerce desk reveals the entire values for views and income at the moment.

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

After 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.
   
2. Observe the worth of RedshiftCluster and RedshiftSecret, then save them in a short lived file to make use of later.
   
3. Select Launch stack to deploy your assets with AWS CloudFormation:
   
4. Select Subsequent.
   
5. Replace the worth for the RedshiftCluster and RedshiftSecret with the data you copied earlier.
6. Select Subsequent.
7. Depart the stack choices at their default and select Subsequent.
8. Choose I acknowledge that AWS CloudFormation would possibly create IAM assets, then select Create stack.

The method takes 30–40 minutes to finish, after which you could have a completely deployed resolution with the demo surroundings.

View your anomaly detector

After you deploy the answer, you’ll be able to find your detector and evaluate any discovered anomalies.

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

The Detectors web page lists all of your energetic detectors.

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

Now you’ll be able to view your detector’s configuration, configure alerts, and evaluate anomalies.

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

After a time period, normally no quite a lot of days, you need to see an inventory 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 might solely present up after an uncommon occasion.

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

How a {custom} connector works

On this part, we focus on 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 information wants:

If 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 assets.
• 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/deploy_custom_connector.sh) to deploy the AWS SAM functions and additional replace the params.json file.

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

• An S3 bucket for storing the params.json file and all enter information for Lookout for Metrics.
• An S3 bucket coverage to permit Lookout for Metrics to speak with Amazon S3.
• A Lambda perform that’s invoked on the bucket when the params.json file is uploaded and begins the Step Capabilities state machine.
• An AWS Id 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 information in Amazon S3.
• A Lambda perform to crawl all historic information.
• A Lambda perform to create and activate a Lookout for Metrics detector.
• A state machine that manages the movement between creating that historic dataset and the detector.

After ai_ops/deploy_custom_connector.sh creates the primary batch of things, it updates the params.json file with new related data 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 dwell information on a schedule (hourly for instance) and a Lambda perform that performs the crawl.

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

Every Lambda perform 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 growth. When making substantial adjustments, guarantee that the references to this explicit repository within the following information are up to date and level to publicly accessible endpoints for Git:

• README.md – This file, particularly the Launch stack buttons, assumes you’re utilizing the dwell 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 think about 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 resolution seems to be for the identified secret identify 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 identify than the one offered, it is advisable replace the worth in ai_ops/l4m-redshift-solution.yaml. If you wish to change the opposite parameters’ names, it is advisable seek for them within the repository and replace their references accordingly.

Modify queries to Amazon Redshift

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

Particularly, it permits a Lambda perform to begin a question with the database after which let the DB engine handle every little thing, 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, it’s possible you’ll wish to construct out extra Step Capabilities-based AWS SAM functions to deal with that work, maybe even utilizing Docker containers over Lambda.

For many modifications, you’ll be able to edit the question information saved within the two Lambda capabilities offered:

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

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

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

The work outlined within the question.sql information is just a portion of the ultimate computed question. The complete 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 data for the place to position the information
• CSV file export outlined

It seems to be like the next code:

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

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

If it is advisable change the frequency for the way typically the continual detector perform 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 deal with Lookout for Metrics itself, primarily the detector and dataset configurations. They’re each outlined in ai_ops/params.json.

The included file seems to be 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": [
        "platform",
        "marketplace"
    ],
    "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": [
                "s3://id-ml-ops2-inputbucket-18vaudty8qtec/ecommerce/backtest/"
            ],
            "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 might be outlined statically forward of time; these are up to date by ai_ops/params_builder.py:

• HistoricalDataPathList
• RoleArn
• TemplatedPathList
• s3_bucket

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

Clear up

Observe the steps on this part to wash up all assets created by this resolution and be sure to’re not billed after evaluating or utilizing the answer.

1. Empty all information from the S3 buckets that had been created from their respective templates:
   1. ProductionRedshiftDemo – S3ContentBucket
   2. CustomRedshiftConnector – S3LambdaBucket
   3. custom-rs-connector – InputBucket
2. Delete your detector through the Lookout for Metrics console.
3. Delete the CloudFormation stacks within the following order (await one to finish earlier than transferring onto the following):
   1. custom-rs-connector-crawl
   2. custom-rs-connector
   3. CustomRedshiftConnector
   4. ProductionRedshiftDemo

Conclusion

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 permits you to create related datasets based mostly in your data in Amazon Redshift to carry out anomaly detection in your time sequence information in just some minutes. In case you can draft the SQL question to acquire the data, you’ll be able to allow ML-powered anomaly detection in your information. From there, your anomalies ought to showcase anomalous occasions and enable you perceive how one anomaly could also be brought about or impacted by others, thereby decreasing your time to understanding points important to what you are promoting or workload.

---

Concerning the Authors

Chris King is a Principal Options Architect in Utilized AI with AWS. He has a particular curiosity in launching AI providers 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 prospects who can profit from it. In his free time, he enjoys all kinds of sports activities and discovering new locations to eat.