{"id":1371,"date":"2021-12-15T00:19:30","date_gmt":"2021-12-15T00:19:30","guid":{"rendered":"https:\/\/salarydistribution.com\/machine-learning\/2021\/12\/15\/process-amazon-redshift-data-and-schedule-a-training-pipeline-with-amazon-sagemaker-processing-and-amazon-sagemaker-pipelines\/"},"modified":"2021-12-15T00:19:30","modified_gmt":"2021-12-15T00:19:30","slug":"process-amazon-redshift-data-and-schedule-a-training-pipeline-with-amazon-sagemaker-processing-and-amazon-sagemaker-pipelines","status":"publish","type":"post","link":"https:\/\/salarydistribution.com\/machine-learning\/2021\/12\/15\/process-amazon-redshift-data-and-schedule-a-training-pipeline-with-amazon-sagemaker-processing-and-amazon-sagemaker-pipelines\/","title":{"rendered":"Process Amazon Redshift data and schedule a training pipeline with Amazon SageMaker Processing and Amazon SageMaker Pipelines"},"content":{"rendered":"
\n

Customers in many different domains tend to work with multiple sources for their data: object-based storage like Amazon Simple Storage Service<\/a> (Amazon S3), relational databases like Amazon Relational Database Service<\/a> (Amazon RDS), or data warehouses like Amazon Redshift<\/a>. Machine learning (ML) practitioners are often driven to work with objects and files instead of databases and tables from the different frameworks they work with. They also prefer local copies of such files in order to reduce the latency of accessing them.<\/p>\n

Nevertheless, ML engineers and data scientists might be required to directly extract data from data warehouses with SQL-like queries to obtain the datasets that they can use for training their models.<\/p>\n

In this post, we use the Amazon SageMaker<\/a> Processing API to run a query against an Amazon Redshift cluster, create CSV files, and perform distributed processing. As an extra step, we also train a simple model to predict the total sales for new events, and build a pipeline with Amazon SageMaker Pipelines<\/a> to schedule it.<\/p>\n

Prerequisites<\/h2>\n

This post uses the sample data that is available when creating a Free Tier cluster in Amazon Redshift<\/a>. As a prerequisite, you should create your cluster and attach to it an AWS Identity and Access Management<\/a> (IAM) role with the correct permissions. For instructions on creating the cluster with the sample dataset, see Using a sample dataset<\/a>. For instructions on associating the role with the cluster, see Authorizing access to the Amazon Redshift Data API<\/a>.<\/p>\n

You can then use your IDE of choice to open the notebooks. This content has been developed and tested using SageMaker Studio<\/a> on a ml.t3.medium instance. For more information about using Studio, refer to the following resources:<\/p>\n

Define the query<\/h2>\n

Now that your Amazon Redshift cluster is up and running, and loaded with the sample dataset, we can define the query to extract data from our cluster. According to the documentation for the sample database<\/a>, this application helps analysts track sales activity for the fictional TICKIT website, where users buy and sell tickets online for sporting events, shows, and concerts. In particular, analysts can identify ticket movement over time, success rates for sellers, and the best-selling events, venues, and seasons.<\/p>\n

Analysts may be tasked to solve a very common ML problem: predict the number of tickets sold given the characteristics of an event. Because we have two fact tables and five dimensions in our sample database, we have some data that we can work with. For the sake of this example, we try to use information from the venue in which the event takes place as well as its date. The SQL query looks like the following:<\/p>\n

\n
SELECT sum(s.qtysold) AS total_sold, e.venueid, e.catid, d.caldate, d.holiday\nfrom sales s, event e, date d\nWHERE s.eventid = e.eventid and e.dateid = d.dateid\nGROUP BY e.venueid, e.catid, d.caldate, d.holiday<\/code><\/pre>\n<\/p><\/div>\n
We can run this query in the query editor<\/a> to test the outcomes and change it to include additional information if needed.<\/p>\n
\"\"<\/a><\/p>\n
Extract the data from Amazon Redshift and process it with SageMaker Processing<\/h2>\n
Now that we\u2019re happy with our query, we need to make it part of our training pipeline.<\/p>\n
A typical training pipeline consists of three phases:<\/p>\n