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

Amazon Lookout for Metrics detects outliers in your time sequence knowledge, determines their root causes, and lets you rapidly take motion. Constructed from the identical expertise utilized by Amazon.com, 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 organising an anomaly detector.

On this put up, we talk about tips on how to construct and deploy {custom} connectors for Amazon Redshift utilizing Lookout for Metrics.

Introduction to time sequence knowledge

You should use time sequence knowledge to measure and monitor any values that shift from one cut-off date 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 use these values to identify tendencies and patterns and make higher selections about probably future occasions. Lookout for Metrics lets you 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 assist steady supply of recent knowledge to Lookout for Metrics to study to construct a mannequin for anomaly detection.

Native connectors are an efficient choice to get began rapidly 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’ll be able to 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 need to 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, remodeling a sequence of values right into a composite, or performing any complicated postprocessing earlier than delivering the info to Lookout for Metrics, you need to use {custom} connectors. Moreover, in case 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, you must use a {custom} connector. This lets you feed in a big quantity of historical past first, bypassing the coldstart necessities and attaining the next high quality mannequin sooner.

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

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

• Your knowledge is unfold over a number of tables
• It is advisable to carry out extra complicated transformations or calculations earlier than it suits to a detector’s configuration
• You need to use all of your historic knowledge to coach your detector

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

• Your knowledge exists in a singular desk that solely comprises 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 put up is hosted on the GitHub repo.

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

The next diagram illustrates our resolution structure.

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

• An Amazon SageMaker pocket book occasion that deploys the {custom} connector resolution.
• An AWS Step Features 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 accommodate all of your AWS Lambda features as deployed (omitted from the structure diagram).
• An S3 bucket to accommodate all of your historic and steady knowledge.
• A CloudFormation template and Lambda perform that begins crawling your knowledge on a schedule.

To switch this resolution to suit your personal atmosphere, 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 elements, you’ll be able to deploy the template and be up and working inside an hour.

Deploy the answer

To make this resolution explorable from finish to finish, now we have 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 initiatives roughly 2 years into the longer term from the publication of this put up.

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 knowledge into Amazon Redshift

The next diagram illustrates how these elements work collectively.

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 hundreds knowledge 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. Go away the stack particulars at their default and select Subsequent once more.
4. Go away 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 may monitor its progress on the AWS CloudFormation console.

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

Knowledge construction

We’ve got taken our normal ecommerce dataset and break 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 numerous tables and must be normalized in an analogous method.

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

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

Our ecommerce desk exhibits the full values for views and income at the moment.

Once we run queries later on this put up, they’re towards 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. Be aware 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 knowledge you copied earlier.
6. Select Subsequent.
7. Go away 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 atmosphere.

View your anomaly detector

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

1. Sign up 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 lively detectors.

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

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

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

After a time frame, normally no quite a lot of days, you must see a listing of anomalies on this web page. You may discover them in depth to view how the info offered appeared anomalous. Should you offered your individual dataset, the anomalies might solely present up after an uncommon occasion.

Now that you’ve the answer deployed and working, 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 elements. We additionally reveal tips on how to construct a {custom} connector, authenticate to Amazon Redshift, modify queries, and modify the detector and dataset.

Core elements

You may run the next elements and modify them to assist your knowledge wants:

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

• An S3 bucket for storing all Lambda features.
• A job for a SageMaker pocket book that has entry to change all related assets.
• A SageMaker pocket book lifecycle config that comprises 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 purposes 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 knowledge 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 Features state machine.
• An AWS Id and Entry Administration (IAM) function to run the state machine.
• A shared Lambda layer of assist features.
• A job for Lookout for Metrics to entry knowledge in Amazon S3.
• A Lambda perform to crawl all historic knowledge.
• A Lambda perform 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/deploy_custom_connector.sh 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 utility outlined in l4m-redshift-continuous-crawl.yaml. This easy utility defines and deploys an occasion set off to provoke the crawling of stay knowledge on a schedule (hourly for instance) and a Lambda perform that performs the crawl.

Each the historic crawled knowledge and the repeatedly 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 perform additionally comprises a question.sql file that gives the bottom question that’s handed to Amazon Redshift. Later the features 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 individual account or downloading a replica for personal growth. When making substantial modifications, be sure 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 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 tips on how to prolong this into your individual atmosphere, the very first thing to think about is the authentication to your Amazon Redshift cluster. You may accomplish this by utilizing the Amazon Redshift Knowledge API and by storing the credentials inside AWS Secrets and techniques Handler.

In Secrets and techniques Supervisor, this resolution seems to be for the recognized secret title redshift-l4mintegration and comprises 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 special secret title than the one offered, it’s good to replace the worth in ai_ops/l4m-redshift-solution.yaml. If you wish to change the opposite parameters’ names, it’s good to seek for them within the repository and replace their references accordingly.

Modify queries to Amazon Redshift

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

Particularly, it permits a Lambda perform to begin a question with the database after which let the DB engine handle all the pieces, 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 features and don’t have to fret about runtime limits. If you wish to carry out extra complicated transformations, you might need to construct out extra Step Features-based AWS SAM purposes 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 features 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 = 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 knowledge inside the final hour. Coupled with the CloudWatch Occasions set off that schedules this perform for hourly, it permits us to stream knowledge to Lookout for Metrics reliably.

The work outlined within the question.sql information is just 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 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.title as platform, market.title 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/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 it’s good to change the frequency for a way usually 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 elements concentrate on 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 tips on how 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 will be outlined statically forward of time; these are up to date by ai_ops/params_builder.py:

• 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

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 knowledge 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 subsequent):
   1. custom-rs-connector-crawl
   2. custom-rs-connector
   3. CustomRedshiftConnector
   4. ProductionRedshiftDemo

Conclusion

You’ve got now seen tips on how to join an Amazon Redshift database to Lookout for Metrics utilizing the native Amazon Redshift Knowledge APIs, CloudWatch Occasions, and Lambda features. This strategy means that you can create related datasets based mostly in your info in Amazon Redshift to carry out anomaly detection in your time sequence knowledge in only a few minutes. Should you can draft the SQL question to acquire the knowledge, you’ll be able to allow ML-powered anomaly detection in your knowledge. From there, your anomalies ought to showcase anomalous occasions and assist you to perceive how one anomaly could also be brought about or impacted by others, thereby lowering your time to understanding points crucial to your small 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 prospects who can profit from it. In his free time, he enjoys all varieties of sports activities and discovering new locations to eat.