Construct ML options at scale with Amazon SageMaker Characteristic Retailer utilizing information from Amazon Redshift

Amazon Redshift is the most well-liked cloud information warehouse that’s utilized by tens of 1000’s of consumers to investigate exabytes of knowledge daily. Many practitioners are extending these Redshift datasets at scale for machine studying (ML) utilizing Amazon SageMaker, a completely managed ML service, with necessities to develop options offline in a code means or low-code/no-code means, retailer featured information from Amazon Redshift, and make this occur at scale in a manufacturing surroundings.

On this publish, we present you three choices to arrange Redshift supply information at scale in SageMaker, together with loading information from Amazon Redshift, performing function engineering, and ingesting options into Amazon SageMaker Feature Store:

When you’re an AWS Glue person and want to do the method interactively, think about choice A. When you’re aware of SageMaker and writing Spark code, choice B could possibly be your selection. If you wish to do the method in a low-code/no-code means, you may observe choice C.

Amazon Redshift makes use of SQL to investigate structured and semi-structured information throughout information warehouses, operational databases, and information lakes, utilizing AWS-designed {hardware} and ML to ship the most effective price-performance at any scale.

SageMaker Studio is the primary totally built-in improvement surroundings (IDE) for ML. It offers a single web-based visible interface the place you may carry out all ML improvement steps, together with making ready information and constructing, coaching, and deploying fashions.

AWS Glue is a serverless information integration service that makes it straightforward to find, put together, and mix information for analytics, ML, and software improvement. AWS Glue lets you seamlessly acquire, rework, cleanse, and put together information for storage in your information lakes and information pipelines utilizing quite a lot of capabilities, together with built-in transforms.

Answer overview

The next diagram illustrates the answer structure for every choice.

Conditions

To proceed with the examples on this publish, you could create the required AWS assets. To do that, we offer an AWS CloudFormation template to create a stack that accommodates the assets. Whenever you create the stack, AWS creates a variety of assets in your account:

• A SageMaker area, which incorporates an related Amazon Elastic File System (Amazon EFS) quantity
• A listing of licensed customers and quite a lot of safety, software, coverage, and Amazon Virtual Private Cloud (Amazon VPC) configurations
• A Redshift cluster
• A Redshift secret
• An AWS Glue connection for Amazon Redshift
• An AWS Lambda operate to arrange required assets, execution roles and insurance policies

Just be sure you don’t have already two SageMaker Studio domains within the Area the place you’re operating the CloudFormation template. That is the utmost allowed variety of domains in every supported Area.

Deploy the CloudFormation template

Full the next steps to deploy the CloudFormation template:

1. Save the CloudFormation template sm-redshift-demo-vpc-cfn-v1.yaml regionally.
2. On the AWS CloudFormation console, select Create stack.
3. For Put together template, choose Template is prepared.
4. For Template supply, choose Add a template file.
5. Select Select File and navigate to the situation in your pc the place the CloudFormation template was downloaded and select the file.
6. Enter a stack title, reminiscent of Demo-Redshift.
7. On the Configure stack choices web page, go away every part as default and select Subsequent.
8. On the Assessment web page, choose I acknowledge that AWS CloudFormation would possibly create IAM assets with customized names and select Create stack.

You must see a brand new CloudFormation stack with the title Demo-Redshift being created. Look forward to the standing of the stack to be CREATE_COMPLETE (roughly 7 minutes) earlier than shifting on. You may navigate to the stack’s Sources tab to test what AWS assets had been created.

Launch SageMaker Studio

Full the next steps to launch your SageMaker Studio area:

1. On the SageMaker console, select Domains within the navigation pane.
2. Select the area you created as a part of the CloudFormation stack (SageMakerDemoDomain).
3. Select Launch and Studio.

This web page can take 1–2 minutes to load once you entry SageMaker Studio for the primary time, after which you’ll be redirected to a Residence tab.

Obtain the GitHub repository

Full the next steps to obtain the GitHub repo:

1. Within the SageMaker pocket book, on the File menu, select New and Terminal.
2. Within the terminal, enter the next command:

git clone https://github.com/aws-samples/amazon-sagemaker-featurestore-redshift-integration.git

Now you can see the amazon-sagemaker-featurestore-redshift-integration folder in navigation pane of SageMaker Studio.

Arrange batch ingestion with the Spark connector

Full the next steps to arrange batch ingestion:

1. In SageMaker Studio, open the pocket book 1-uploadJar.ipynb beneath amazon-sagemaker-featurestore-redshift-integration.
2. If you’re prompted to decide on a kernel, select Information Science because the picture and Python 3 because the kernel, then select Choose.
3. For the next notebooks, select the identical picture and kernel besides the AWS Glue Interactive Classes pocket book (4a).
4. Run the cells by urgent Shift+Enter in every of the cells.

Whereas the code runs, an asterisk (*) seems between the sq. brackets. When the code is completed operating, the * shall be changed with numbers. This motion can be workable for all different notebooks.

Arrange the schema and cargo information to Amazon Redshift

The following step is to arrange the schema and cargo information from Amazon Simple Storage Service (Amazon S3) to Amazon Redshift. To take action, run the pocket book 2-loadredshiftdata.ipynb.

Create function shops in SageMaker Characteristic Retailer

To create your function shops, run the pocket book 3-createFeatureStore.ipynb.

Carry out function engineering and ingest options into SageMaker Characteristic Retailer

On this part, we current the steps for all three choices to carry out function engineering and ingest processed options into SageMaker Characteristic Retailer.

Possibility A: Use SageMaker Studio with a serverless AWS Glue interactive session

Full the next steps for choice A:

1. In SageMaker Studio, open the pocket book 4a-glue-int-session.ipynb.
2. If you’re prompted to decide on a kernel, select SparkAnalytics 2.0 because the picture and Glue Python [PySpark and Ray] because the kernel, then select Choose.

The surroundings preparation course of could take a while to finish.

Possibility B: Use a SageMaker Processing job with Spark

On this choice, we use a SageMaker Processing job with a Spark script to load the unique dataset from Amazon Redshift, carry out function engineering, and ingest the info into SageMaker Characteristic Retailer. To take action, open the pocket book 4b-processing-rs-to-fs.ipynb in your SageMaker Studio surroundings.

Right here we use RedshiftDatasetDefinition to retrieve the dataset from the Redshift cluster. RedshiftDatasetDefinition is one kind of enter of the processing job, which offers a easy interface for practitioners to configure Redshift connection-related parameters reminiscent of identifier, database, desk, question string, and extra. You may simply set up your Redshift connection utilizing RedshiftDatasetDefinition with out sustaining a connection full time. We additionally use the SageMaker Feature Store Spark connector library within the processing job to connect with SageMaker Characteristic Retailer in a distributed surroundings. With this Spark connector, you may simply ingest information to the function group’s on-line and offline retailer from a Spark DataFrame. Additionally, this connector accommodates the performance to mechanically load function definitions to assist with creating function teams. Above all, this answer affords you a local Spark strategy to implement an end-to-end information pipeline from Amazon Redshift to SageMaker. You may carry out any function engineering in a Spark context and ingest closing options into SageMaker Characteristic Retailer in only one Spark venture.

To make use of the SageMaker Characteristic Retailer Spark connector, we prolong a pre-built SageMaker Spark container with sagemaker-feature-store-pyspark put in. Within the Spark script, use the system executable command to run pip set up, set up this library in your native surroundings, and get the native path of the JAR file dependency. Within the processing job API, present this path to the parameter of submit_jars to the node of the Spark cluster that the processing job creates.

Within the Spark script for the processing job, we first learn the unique dataset recordsdata from Amazon S3, which quickly shops the unloaded dataset from Amazon Redshift as a medium. Then we carry out function engineering in a Spark means and use feature_store_pyspark to ingest information into the offline function retailer.

For the processing job, we offer a ProcessingInput with a redshift_dataset_definition. Right here we construct a construction in accordance with the interface, offering Redshift connection-related configurations. You should use query_string to filter your dataset by SQL and unload it to Amazon S3. See the next code:

rdd_input = ProcessingInput(
            input_name="redshift_dataset_definition",
            app_managed=True,
            dataset_definition=DatasetDefinition(
                local_path="/decide/ml/processing/enter/rdd",
                data_distribution_type="FullyReplicated",
                input_mode="File",
                redshift_dataset_definition=RedshiftDatasetDefinition(
                    cluster_id=_cluster_id,
                    database=_dbname,
                    db_user=_username,
                    query_string=_query_string,
                    cluster_role_arn=_redshift_role_arn,
                    output_s3_uri=_s3_rdd_output,
                    output_format="PARQUET"
                ),
            ),
        )

It’s good to wait 6–7 minutes for every processing job together with USER, PLACE, and RATING datasets.

For extra particulars about SageMaker Processing jobs, consult with Process data.

For SageMaker native options for function processing from Amazon Redshift, you can too use Feature Processing in SageMaker Characteristic Retailer, which is for underlying infrastructure together with provisioning the compute environments and creating and sustaining SageMaker pipelines to load and ingest information. You may solely focus in your function processor definitions that embrace transformation features, the supply of Amazon Redshift, and the sink of SageMaker Characteristic Retailer. The scheduling, job administration, and different workloads in manufacturing are managed by SageMaker. Characteristic Processor pipelines are SageMaker pipelines, so the standard monitoring mechanisms and integrations can be found.

Possibility C: Use SageMaker Information Wrangler

SageMaker Information Wrangler means that you can import information from varied information sources together with Amazon Redshift for a low-code/no-code strategy to put together, rework, and featurize your information. After you end information preparation, you should utilize SageMaker Information Wrangler to export options to SageMaker Characteristic Retailer.

There are some AWS Identity and Access Management (IAM) settings that enable SageMaker Information Wrangler to connect with Amazon Redshift. First, create an IAM position (for instance, redshift-s3-dw-connect) that features an Amazon S3 entry coverage. For this publish, we connected the AmazonS3FullAccess coverage to the IAM position. When you’ve got restrictions of accessing a specified S3 bucket, you may outline it within the Amazon S3 entry coverage. We connected the IAM position to the Redshift cluster that we created earlier. Subsequent, create a coverage for SageMaker to entry Amazon Redshift by getting its cluster credentials, and fix the coverage to the SageMaker IAM position. The coverage seems to be like the next code:

{
    "Model": "2012-10-17",
    "Assertion": [
        {
            "Action": "redshift:getclustercredentials",
            "Effect": "Allow",
            "Resource": [
                "*"
            ]
        }
    ]
}

After this setup, SageMaker Information Wrangler means that you can question Amazon Redshift and output the outcomes into an S3 bucket. For directions to connect with a Redshift cluster and question and import information from Amazon Redshift to SageMaker Information Wrangler, consult with Import data from Amazon Redshift.

SageMaker Information Wrangler affords a number of over 300 pre-built information transformations for frequent use circumstances reminiscent of deleting duplicate rows, imputing lacking information, one-hot encoding, and dealing with time collection information. You can too add customized transformations in pandas or PySpark. In our instance, we utilized some transformations reminiscent of drop column, information kind enforcement, and ordinal encoding to the info.

When your information circulate is full, you may export it to SageMaker Characteristic Retailer. At this level, you could create a function group: give the function group a reputation, choose each on-line and offline storage, present the title of a S3 bucket to make use of for the offline retailer, and supply a job that has SageMaker Characteristic Retailer entry. Lastly, you may create a job, which creates a SageMaker Processing job that runs the SageMaker Information Wrangler circulate to ingest options from the Redshift information supply to your function group.

Right here is one end-to-end information circulate within the state of affairs of PLACE function engineering.

Use SageMaker Characteristic Retailer for mannequin coaching and prediction

To make use of SageMaker Characteristic retailer for mannequin coaching and prediction, open the pocket book 5-classification-using-feature-groups.ipynb.

After the Redshift information is remodeled into options and ingested into SageMaker Characteristic Retailer, the options can be found for search and discovery throughout groups of knowledge scientists accountable for many unbiased ML fashions and use circumstances. These groups can use the options for modeling with out having to rebuild or rerun function engineering pipelines. Characteristic teams are managed and scaled independently, and may be reused and joined collectively whatever the upstream information supply.

The following step is to construct ML fashions utilizing options chosen from one or a number of function teams. You resolve which function teams to make use of on your fashions. There are two choices to create an ML dataset from function teams, each using the SageMaker Python SDK:

• Use the SageMaker Characteristic Retailer DatasetBuilder API – The SageMaker Characteristic Retailer DatasetBuilder API permits information scientists create ML datasets from a number of function teams within the offline retailer. You should use the API to create a dataset from a single or a number of function teams, and output it as a CSV file or a pandas DataFrame. See the next instance code:

from sagemaker.feature_store.dataset_builder import DatasetBuilder

fact_rating_dataset = DatasetBuilder(
    sagemaker_session = sagemaker_session, 
    base = fact_rating_feature_group,
    output_path = f"s3://{s3_bucket_name}/{prefix}",
    record_identifier_feature_name="ratingid",
    event_time_identifier_feature_name="timestamp", 
).to_dataframe()[0]

• Run SQL queries utilizing the athena_query operate within the FeatureGroup API – Another choice is to make use of the auto-built AWS Glue Information Catalog for the FeatureGroup API. The FeatureGroup API contains an Athena_query operate that creates an AthenaQuery occasion to run user-defined SQL question strings. You then run the Athena question and arrange the question end result right into a pandas DataFrame. This selection means that you can specify extra sophisticated SQL queries to extract data from a function group. See the next instance code:

dim_user_query = dim_user_feature_group.athena_query()
dim_user_table = dim_user_query.table_name

dim_user_query_string = (
    'SELECT * FROM "'
    + dim_user_table
    + '"'
)

dim_user_query.run(
    query_string = dim_user_query_string,
    output_location = f"s3://{s3_bucket_name}/{prefix}",
)

dim_user_query.wait()
dim_user_dataset = dim_user_query.as_dataframe()

Subsequent, we will merge the queried information from completely different function teams into our closing dataset for mannequin coaching and testing. For this publish, we use batch transform for mannequin inference. Batch rework means that you can get mannequin inferene on a bulk of knowledge in Amazon S3, and its inference result’s saved in Amazon S3 as properly. For particulars on mannequin coaching and inference, consult with the pocket book 5-classification-using-feature-groups.ipynb.

Run a be part of question on prediction ends in Amazon Redshift

Lastly, we question the inference end result and be part of it with unique person profiles in Amazon Redshift. To do that, we use Amazon Redshift Spectrum to affix batch prediction ends in Amazon S3 with the unique Redshift information. For particulars, consult with the pocket book run 6-read-results-in-redshift.ipynb.

Clear up

On this part, we offer the steps to wash up the assets created as a part of this publish to keep away from ongoing prices.

Shut down SageMaker Apps

Full the next steps to close down your assets:

1. In SageMaker Studio, on the File menu, select Shut Down.
2. Within the Shutdown affirmation dialog, select Shutdown All to proceed.

3. After you get the “Server stopped” message, you may shut this tab.

Delete the apps

Full the next steps to delete your apps:

1. On the SageMaker console, within the navigation pane, select Domains.
2. On the Domains web page, select SageMakerDemoDomain.
3. On the area particulars web page, beneath Person profiles, select the person sagemakerdemouser.
4. Within the Apps part, within the Motion column, select Delete app for any energetic apps.
5. Be certain that the Standing column says Deleted for all of the apps.

Delete the EFS storage quantity related along with your SageMaker area

Find your EFS quantity on the SageMaker console and delete it. For directions, consult with Manage Your Amazon EFS Storage Volume in SageMaker Studio.

Delete default S3 buckets for SageMaker

Delete the default S3 buckets (sagemaker-<region-code>-<acct-id>) for SageMaker If you’re not utilizing SageMaker in that Area.

Delete the CloudFormation stack

Delete the CloudFormation stack in your AWS account in order to wash up all associated assets.

Conclusion

On this publish, we demonstrated an end-to-end information and ML circulate from a Redshift information warehouse to SageMaker. You may simply use AWS native integration of purpose-built engines to undergo the info journey seamlessly. Try the AWS Blog for extra practices about constructing ML options from a contemporary information warehouse.

In regards to the Authors

Akhilesh Dube, a Senior Analytics Options Architect at AWS, possesses greater than twenty years of experience in working with databases and analytics merchandise. His major position includes collaborating with enterprise purchasers to design strong information analytics options whereas providing complete technical steering on a variety of AWS Analytics and AI/ML providers.

Ren Guo is a Senior Information Specialist Options Architect within the domains of generative AI, analytics, and conventional AI/ML at AWS, Better China Area.

Sherry Ding is a Senior AI/ML Specialist Options Architect. She has intensive expertise in machine studying with a PhD diploma in Laptop Science. She primarily works with Public Sector prospects on varied AI/ML-related enterprise challenges, serving to them speed up their machine studying journey on the AWS Cloud. When not serving to prospects, she enjoys outside actions.

Mark Roy is a Principal Machine Studying Architect for AWS, serving to prospects design and construct AI/ML options. Mark’s work covers a variety of ML use circumstances, with a major curiosity in pc imaginative and prescient, deep studying, and scaling ML throughout the enterprise. He has helped firms in lots of industries, together with insurance coverage, monetary providers, media and leisure, healthcare, utilities, and manufacturing. Mark holds six AWS Certifications, together with the ML Specialty Certification. Previous to becoming a member of AWS, Mark was an architect, developer, and know-how chief for over 25 years, together with 19 years in monetary providers.