November 22nd, 2021

Amazon Redshift Integration for Model Scoring

RSS icon RSS Category: Data Science, H2O AI Cloud

We consistently work with our partners on innovative ways to use models in production here at, and we are excited to demonstrate our AWS Redshift integration for model scoring.

Amazon Redshift is a very popular data warehouse on AWS. We wanted to expand on the existing capacities of using data from Redshift to train a model on the H2O AI Cloud, which is a comprehensive automated machine learning platform. Now, once a model is trained, Redshift can use the model for inferencing (scoring) using standard SQL. 

Calling the model using SQL is a very convenient way to create inferences that can be stored in Redshift.  Because the model operates like an SQL function, it is easy to include the model in an SQL query that uses live data rather than the more common extract, score and upload approach.

Any application that uses Redshift to access data can now get real time predictions by leveraging current data at scoring time. Once a model is created using the H2O AI Hybrid Cloud, the following install steps will enable that model to be used in Redshift:


  1. Download the file (Download Scoring Pipeline > Download Mojo Scoring Pipeline)
  2. Unzip the, in the unzipped directory, and find the file pipeline.mojo. This is the model and is the only file required.
  3. Download and follow the AWS SageMaker integration steps outlined here
  4. Generate the RedShift SQL for this model. With all the files in the same directory, use the jar downloaded in Step 3 and specify the model to use and the type of artifact you want to generate (Redshift-SQL). This command will generate the Redshift function for this specific model and the SQL that can be used to call the model for inference.The result of the above command is a file (pipeline.mojo.Redshift-sql):

Notice how the function name (h2oscore_pipeline) contains the name of the model. This is because each model could have a different number of parameters and types attributed to it. If we specified a model called churn.mojo on the generation step, the function name would be h2oscore_churn.

    5. Now paste into an SQL tool and execute.

The SageMaker and IAM_ROLE need to be specified for your account. These would have been created in Step 3. 

The last part of the output shows an example SQL select statement for the specific model. This shows us how to call the model and the columns that will be passed from the specific table. The eye catcher <table-name> should be changed to the table in RedShift that you would like to use for inferencing.

Now you can execute the SQL with any SQL Editor that can connect to RedShift. In this example,  I used the AWS Query Editor.


One way to capture the results is to create a table with a select statement. This operation allows the original table to remain unchanged, and the results of the scoring to be written to a new table. Notice here that I used a key (customer id) so that I can use this within a join to reference the original row.

This new functionality enables scoring to be invoked from Redshift. This saves time for the operations team as data does not have to be selected and exported for scoring then reloaded, reducing the time it takes to operationalize the model.

As the model can be called using SQL, now any application that uses Redshift can get predictions, this further increases the value of the models output throughout the organization, as the predictions can use current data rather than predictions that were created days or weeks ago.

This new functionality for scoring opens the possibility of using Redshift for real time scoring! If you aren’t a current user, you can sign up to try the H2O AI Cloud for free today!




About the Author

Eric Gudgion
Eric Gudgion

Eric is a Senior Principal Solutions Architect, he is passionate about performance and scalability. Eric’s role enables him to help customers adopt h2o within their enterprises.

Leave a Reply

10 Consejos para Convertirte en un Científico de Datos Exitoso

En este mundo que no deja de cambiar y sorprendernos, como científicos de datos debemos

January 19, 2023 - by Favio Vázquez
Explaining models built in H2O-3 — Part 1

Machine Learning explainability refers to understanding and interpreting the decisions and predictions made by a

December 22, 2022 - by Parul Pandey
+ at NeurIPS 2022 is proud to participate in the 36th Conference on Neural Information Processing Systems (NeurIPS)

December 6, 2022 - by Marcos V. Conde
A Brief Overview of AI Governance for Responsible Machine Learning Systems

Our paper “A Brief Overview of AI Governance for Responsible Machine Learning Systems” was recently

November 30, 2022 - by Navdeep Gill, Abhishek Mathur and Marcos V. Conde
H2O World Dallas Customer Talks

After three long years of not having an #H2OWorld, we finally held our first one

November 24, 2022 - by Vinod Iyengar
New in Wave 0.24.0

Another Wave release has arrived with quite a few exciting new features. Let's quickly go

November 21, 2022 - by Martin Turoci

Request a Demo

Explore how to Make, Operate and Innovate with the H2O AI Cloud today

Learn More