{"id":1251,"date":"2021-11-23T08:29:42","date_gmt":"2021-11-23T08:29:42","guid":{"rendered":"https:\/\/salarydistribution.com\/machine-learning\/2021\/11\/23\/bring-your-amazon-sagemaker-model-into-amazon-redshift-for-remote-inference\/"},"modified":"2021-11-23T08:29:42","modified_gmt":"2021-11-23T08:29:42","slug":"bring-your-amazon-sagemaker-model-into-amazon-redshift-for-remote-inference","status":"publish","type":"post","link":"https:\/\/salarydistribution.com\/machine-learning\/2021\/11\/23\/bring-your-amazon-sagemaker-model-into-amazon-redshift-for-remote-inference\/","title":{"rendered":"Bring Your Amazon SageMaker model into Amazon Redshift for remote inference"},"content":{"rendered":"
\n

Amazon Redshift<\/a>, a fast, fully managed, widely used cloud data warehouse, natively integrates with Amazon SageMaker<\/a> for machine learning (ML). Tens of thousands of customers use Amazon Redshift to process exabytes of data every day to power their analytics workloads. Data analysts and database developers want to use this data to train ML models, which can then be used to generate insights for use cases such as forecasting revenue, predicting customer churn, and detecting anomalies.<\/p>\n

Amazon Redshift ML<\/a> makes it easy for SQL users to create, train, and deploy ML models using familiar SQL commands. In a previous post<\/a>, we covered how Amazon Redshift ML allows you to use your data in Amazon Redshift with SageMaker, a fully managed ML service, without requiring you to become an expert in ML. We also discussed how Amazon Redshift ML enables ML experts to create XGBoost or MLP models in an earlier post<\/a>. Additionally, Amazon Redshift ML allows data scientists to either import existing SageMaker models into Amazon Redshift for in-database inference or remotely invoke a SageMaker endpoint.<\/p>\n

This post shows how you can enable your data warehouse users to use SQL to invoke a remote SageMaker endpoint for prediction. We first train and deploy a Random Cut Forest model in SageMaker, and demonstrate how you can create a model with SQL to invoke that SageMaker predictions remotely. Then, we show how end users can invoke the model.<\/p>\n

Prerequisites<\/h2>\n

To get started, we need an Amazon Redshift cluster with the Amazon Redshift ML feature enabled. For an introduction to Amazon Redshift ML and instructions on setting it up, see Create, train, and deploy machine learning models in Amazon Redshift using SQL with Amazon Redshift ML<\/a>.<\/p>\n

You also have to make sure that the SageMaker model is deployed and you have the endpoint. You can use the following AWS CloudFormation<\/a> template<\/a> to provision all the required resources in your AWS accounts automatically.<\/p>\n

Solution overview<\/h2>\n

Amazon Redshift ML supports text and CSV inference formats. For more information about various SageMaker algorithms and their inference formats, see Random Cut Forest (RCF) Algorithm<\/a>.<\/p>\n

Amazon SageMaker Random Cut Forest (RCF) is an algorithm designed to detect anomalous data points within a dataset. Examples of anomalies that are important to detect include when website activity uncharacteristically spikes, when temperature data diverges from a periodic behavior, or when changes to public transit ridership reflect the occurrence of a special event.<\/p>\n

In this post, we use the SageMaker RCF algorithm to train an RCF model using the Notebook generated by the CloudFormation template on the Numenta Anomaly Benchmark (NAB) NYC Taxi dataset.<\/p>\n

We downloaded<\/a> the data and stored it in an Amazon Simple Storage Service<\/a> (Amazon S3) bucket. The data consists of the number of New York City taxi passengers over the course of 6 months aggregated into 30-minute buckets. We naturally expect to find anomalous events occurring during the NYC marathon, Thanksgiving, Christmas, New Year\u2019s Day, and on the day of a snowstorm.<\/p>\n

We then use this model to predict anomalous events by generating an anomaly score for each data point.<\/p>\n

The following figure illustrates how we use Amazon Redshift ML to create a model using the SageMaker endpoint.<\/p>\n

\"\"<\/a><\/p>\n

Deploy the model<\/h2>\n

To deploy the model, go to the SageMaker console and open the notebook that was created by the CloudFormation template.<\/p>\n

\"\"<\/a><\/p>\n

Then choose bring-your-own-model-remote-inference.ipynb<\/code>.<\/p>\n

\"\"<\/a><\/p>\n

Set up parameters as shown in the following screenshot and then run all cells.<\/p>\n

\"\"<\/a><\/p>\n

Get the SageMaker model endpoint<\/h2>\n

On the Amazon SageMaker console, under Inference <\/strong>in the navigation pane, choose Endpoints<\/strong> to find your model name. You use this when you create the remote inference model in Amazon Redshift.<\/p>\n

\"\"<\/a><\/p>\n

Prepare data to create a remote inference model using Amazon Redshift ML<\/h2>\n

Create the schema and load the data in Amazon Redshift using the following SQL:<\/p>\n

\n
\n
DROP TABLE IF EXISTS public.rcf_taxi_data CASCADE;\nCREATE TABLE public.rcf_taxi_data\n(\nride_timestamp timestamp,\nnbr_passengers int\n);\nCOPY public.rcf_taxi_data\nFROM 's3:\/\/sagemaker-sample-files\/datasets\/tabular\/anomaly_benchmark_taxi\/NAB_nyc_taxi.csv'\niam_role 'arn:aws:iam:::<accountid>:role\/RedshiftML' ignoreheader 1 csv delimiter ',';<\/code><\/pre>\n<\/p><\/div>\n<\/p><\/div>\n
Amazon Redshift now supports attaching the default IAM role. If you have enabled the default IAM role in your cluster, you can use the default IAM role as follows.<\/p>\n
\n
COPY public.rcf_taxi_data\nFROM 's3:\/\/sagemaker-sample-files\/datasets\/tabular\/anomaly_benchmark_taxi\/NAB_nyc_taxi.csv'\niam_role default ignoreheader 1 csv delimiter ',';<\/code><\/pre>\n<\/p><\/div>\n
You can use the Amazon Redshift query editor v2<\/a> to run these commands.<\/p>\n
\"\"<\/a><\/p>\n
Create a model<\/h2>\n
Create a model in Amazon Redshift ML using the SageMaker endpoint you previously captured:<\/p>\n
\n
CREATE MODEL public.remote_random_cut_forest\nFUNCTION remote_fn_rcf(int)\nRETURNS decimal(10,6)\nSAGEMAKER 'randomcutforest-xxxxxxxxx'\nIAM_ROLE 'arn:aws:iam::<accountid>:role\/RedshiftML';\n<\/code><\/pre>\n<\/p><\/div>\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n                    You can also use the default IAM role with your CREATE MODEL command as follows:\n                  <\/p>\n
\n
\n
\n
CREATE MODEL public.remote_random_cut_forest\nFUNCTION remote_fn_rcf(int)\nRETURNS decimal(10,6)\nSAGEMAKER 'randomcutforest-xxxxxxxxx'\nIAM_ROLE  default;<\/code><\/pre>\n<\/p><\/div>\n<\/p><\/div>\n<\/p><\/div>\n<\/p><\/div>\n<\/p><\/div>\n<\/p><\/div>\n<\/p><\/div>\n<\/p><\/div>\n<\/p><\/div>\n<\/p><\/div>\n<\/p><\/div>\n<\/p><\/div>\n<\/p><\/div>\n<\/p><\/div>\n
Check model status<\/h2>\n
You can use the show model<\/code> command to view the status of the model:<\/p>\n
\n
show model public.remote_random_cut_forest<\/code><\/pre>\n<\/p><\/div>\n
You get output like the following screenshot, which shows the endpoint and function name.<\/p>\n
\"\"<\/a><\/p>\n
Compute anomaly scores across the entire taxi dataset<\/h2>\n
Now, run the inference query using the function name from the create model<\/code> statement:<\/p>\n
\n
select ride_timestamp, nbr_passengers, public.remote_fn_rcf(nbr_passengers) as score\nfrom public.rcf_taxi_data;\n<\/code><\/pre>\n<\/p><\/div>\n
The following screenshot shows our results.<\/p>\n
\"\"<\/a><\/p>\n
Now that we have our anomaly scores, we need to check for higher-than-normal anomalies.<\/p>\n
Amazon Redshift ML has batching optimizations to minimize the communication cost with SageMaker and offers high-performance remote inference.<\/p>\n
Check for high anomalies<\/h2>\n
The following code runs a query for any data points with scores greater than three standard deviations (approximately 99.9th percentile) from the mean score:<\/p>\n
\n
with score_cutoff as\n(select stddev(public.remote_fn_rcf(nbr_passengers)) as std, avg(public.remote_fn_rcf(nbr_passengers)) as mean, ( mean + 3 * std ) as score_cutoff_value\nfrom public.rcf_taxi_data)\n\nselect ride_timestamp, nbr_passengers, public.remote_fn_rcf(nbr_passengers) as score\nfrom public.rcf_taxi_data\nwhere score > (select score_cutoff_value from score_cutoff)\norder by 2 desc;\n<\/code><\/pre>\n<\/p><\/div>\n
The data in the following screenshot shows that the biggest spike in ridership occurs on November 2, 2014, which was the annual NYC marathon. We also see spikes on Labor Day weekend, New Year\u2019s Day and the July 4th holiday weekend.<\/p>\n
\"\"<\/a><\/p>\n
Conclusion<\/h2>\n
In this post, we used SageMaker Random Cut Forest to detect anomalous data points in a taxi ridership dataset. In this data, the anomalies occurred when ridership was uncharacteristically high or low. However, the RCF algorithm is also capable of detecting when, for example, data breaks periodicity or uncharacteristically changes global behavior.<\/p>\n
We then used Amazon Redshift ML to demonstrate how you can make inferences on unsupervised algorithms (such as Random Cut Forest). This allows you to democratize ML by making predictions with Amazon Redshift SQL commands.<\/p>\n
For more information about building different models with Amazon Redshift ML see the\u00a0Amazon Redshift<\/a>\u00a0ML documentation.<\/p>\n
\n
About the Authors<\/h3>\n
\"\"Phil Bates\u00a0<\/b>is a Senior Analytics Specialist Solutions Architect at AWS with over 25 years of data warehouse experience.<\/p>\n
\"\"<\/strong>Debu Panda<\/strong>, a principal product manager at AWS, is an industry leader in analytics, application platform, and database technologies and has more than 25 years of experience in the IT world.<\/p>\n
\"\"<\/b>N<\/strong>ikos Koulouris\u00a0<\/strong><\/b>is a Software Development Engineer at AWS. He received his PhD from University of California, San Diego and he has been working in the areas of databases and analytics.<\/p>\n
\"\"Murali Narayanaswamy\u00a0<\/strong>is a principal machine learning scientist in AWS. He received his PhD from Carnegie Mellon University and works at the intersection of ML, AI, optimization, learning and inference to combat uncertainty in real-world applications including personalization, forecasting, supply chains and large scale systems.<\/p>\n
       \n      <\/div>\n","protected":false},"excerpt":{"rendered":"
https:\/\/aws.amazon.com\/blogs\/machine-learning\/bring-your-amazon-sagemaker-model-into-amazon-redshift-for-remote-inference\/<\/p>\n","protected":false},"author":0,"featured_media":1252,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[3],"tags":[],"_links":{"self":[{"href":"https:\/\/salarydistribution.com\/machine-learning\/wp-json\/wp\/v2\/posts\/1251"}],"collection":[{"href":"https:\/\/salarydistribution.com\/machine-learning\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/salarydistribution.com\/machine-learning\/wp-json\/wp\/v2\/types\/post"}],"replies":[{"embeddable":true,"href":"https:\/\/salarydistribution.com\/machine-learning\/wp-json\/wp\/v2\/comments?post=1251"}],"version-history":[{"count":0,"href":"https:\/\/salarydistribution.com\/machine-learning\/wp-json\/wp\/v2\/posts\/1251\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/salarydistribution.com\/machine-learning\/wp-json\/wp\/v2\/media\/1252"}],"wp:attachment":[{"href":"https:\/\/salarydistribution.com\/machine-learning\/wp-json\/wp\/v2\/media?parent=1251"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/salarydistribution.com\/machine-learning\/wp-json\/wp\/v2\/categories?post=1251"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/salarydistribution.com\/machine-learning\/wp-json\/wp\/v2\/tags?post=1251"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}