Amazon Redshift ML allows data analysts, developers, and data scientists to train machine learning (ML) models using SQL. In previous posts, we demonstrated how customers can use the automatic model training capability of Amazon Redshift to train their classification and regression models. Redshift ML provides several capabilities for data scientists. It allows you to create a model using SQL and specify your algorithm as XGBoost. It also lets you bring your pre-trained XGBoost model into Amazon Redshift for local inference. You can let users remotely invoke any model deployed in Amazon SageMaker for inference with SQL.

In this post, we illustrate how data scientists can train models using the XGBoost algorithm. Specifically, we discuss how you can use Redshift ML to train ML models with the CREATE MODEL command by providing advanced parameters such as preprocessors, problem type, and hyperparameters.

In the post Create, train, and deploy machine learning models in Amazon Redshift using SQL with Amazon Redshift ML, we reviewed the benefits of Redshift ML and how it simplifies your ML pipeline without the complexity of exporting your data from the data warehouse for use with ML. You don’t have to worry about the governance of data that you export from your data warehouse.

Prerequisites

To get started, we need an Amazon Redshift cluster with the ML feature enabled. Redshift ML is generally available, and you can use it in a cluster with the latest maintenance version. For an introduction to 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.

Introducing XGBoost

The XGBoost algorithm (eXtreme Gradient Boosting) is an optimized open-source implementation of the gradient boosted trees algorithm. XGBoost is designed from the ground up to handle many data science problems in a highly efficient, flexible, portable, and accurate way. XGBoost can be used for regression, binary classification, multi-class classification, and ranking problems. For details on XGBoost and SageMaker, see Introducing the open-source Amazon SageMaker XGBoost algorithm container.

The Redshift ML CREATE MODEL with AUTO OFF option currently supports only XGBoost as the MODEL_TYPE. As a data scientist or ML expert, you can provide relevant information such as objective, preprocessors, and hyperparameters as part of the CREATE MODEL command based on your use case.

Use case

For this post, we use the banknote authentication dataset, which is a classic binary classification problem to predict whether a given banknote is genuine or forged. With analytical methods, you can use the Intaglio structures of circulating banknotes for authentication. Intaglio is a special printing technique that adds a unique structure to banknotes. Image recognition and the Wavelet Transform tool were used to obtain spectral features like variance, skewness, kurtosis, and entropy (of an image) from banknote specimens used as the input parameters for the model. Based on these measurements, you can employ ML methodologies to predict an original note from a forged one.

The following screenshot shows a sample dataset for this use case. The last column, class, is the one we’re going to predict. Zero (0) corresponds to a genuine banknote and one (1) is counterfeit.

2189 Table

Setup and data preparation

We split the dataset into two sets (80% and 20%) to use for training and testing purposes. To get arbitrariness in the dataset, we use a random number when we split the data. The test data is available in the public Amazon Simple Storage Service (Amazon S3) bucket s3://redshiftbucket-ml-sagemaker/banknote_authentication.

The following are the DDL and COPY commands for both training and test datasets. If you prefer, you can also keep the dataset in a single table and use a where clause in the SQL statement to split the training and test data. For simple illustration purposes, we divide the dataset to be used in two different tables. You can use the Amazon Redshift Query Editor or your preferred SQL editor to run these SQL statements.

For loading data, use the appropriate AWS Identity and Access Management (IAM) role for your Amazon Redshift cluster.

--train table CREATE TABLE banknoteauthentication_train(
variance FLOAT,
skewness FLOAT,
curtosis FLOAT,
entrophy FLOAT,
class INT); --Load COPY banknoteauthentication_train FROM 's3://redshiftbucket-ml-sagemaker/banknote_authentication/train_data/' IAM_ROLE '<<your-amazon-redshift-sagemaker-iam-role-arn>>' REGION 'us-west-2' IGNOREHEADER 1 CSV;
--test table CREATE TABLE banknoteauthentication_test(
variance FLOAT,
skewness FLOAT,
curtosis FLOAT,
entrophy FLOAT,
class INT); --Load COPY banknoteauthentication_test FROM 's3://redshiftbucket-ml-sagemaker/banknote_authentication/test_data/' IAM_ROLE '<<your-amazon-redshift-sagemaker-iam-role-arn>>' REGION 'us-west-2' IGNOREHEADER 1 CSV;

Create the ML Model

Now that we have set up the data , let’s create the model in Redshift ML with AUTO OFF and XGBoost as the model type using the CREATE MODEL command:

--Create a model CREATE MODEL model_banknoteauthentication_xgboost_binary FROM banknoteauthentication_train TARGET class FUNCTION func_model_banknoteauthentication_xgboost_binary IAM_ROLE 'arn:aws:iam::<replace-with-your-account-number>:role/Redshift-ML' AUTO OFF MODEL_TYPE xgboost OBJECTIVE 'binary:logistic' PREPROCESSORS 'none' HYPERPARAMETERS DEFAULT EXCEPT(NUM_ROUND '100') SETTINGS(S3_BUCKET '<< replace with your S3 bucket>>');

We specified the class column as the target (label) that we want to predict, and specified func_model_banknoteauthentication_xgboost_binary as the function.

Make the appropriate changes in the CREATE MODEL command to specify the IAM_ROLE and S3_BUCKET . Refer to the previous posts or the documentation on the requirements for the IAM role. The S3 bucket specified is used for exporting the training dataset and storing other ML-related artifacts.

We use the table banknoteauthentication_train for training data, and the MODEL_TYPE is set to xgboost with AUTO OFF.

The CREATE MODEL command is asynchronous, and the model creation happens in the background after you run the command. It may take 8–10 minutes for the model to be trained and ready; and you can monitor the progress using the SHOW MODEL model_name; command:

SHOW MODEL model_banknoteauthentication_xgboost_binary;

Objectives and hyperparameters

As a data scientist, you might want to specify training objectives and hyperparameters for tuning your model. Let’s discuss briefly how you can use OBJECTIVE and HYPERPARAMETERS options in the CREATE MODEL with AUTO OFF.

An objective in any ML problem is a measure of the quality of the solution. An objective of a problem aims to either minimize or maximize the function specified. For example, reg:squarederror is used as the objective for regression problems, and the smaller the value (closer to zero) this function is, the better the accuracy of the model.

Redshift ML currently supports the following OBJECTIVE options that go along with XGBoost: reg:squarederror, reg:squaredlogerror, reg:logistic, reg:pseudohubererror, reg:tweedie, binary:logistic, binary:hinge, and multi:softmax. For more information, see Learning Task Parameters in the XGBoost documentation.

Hyperparameters are problem- and algorithm-specific properties that govern and control the behavior of the training and can have a direct impact on the efficiency of the model. If no hyperparameters are specified, the defaults for XGBoost are used. The values should be enclosed in single quotes. For our hyperparameters, NUM_ROUND (the number of rounds to run the training) is set to 100. For a complete list of supported hyperparameters, see CREATE XGBoost models with AUTO OFF.

The following code is a representative output from show model after the create model command starts. It reflects the inputs presented in the create model command and more information like model state (TRAINING, READY, FAILED) and the max runtime to finish. Leaving the model to run to completion with the default time allows you to tune the model to yield better accuracy.

dev=# show model model_banknoteauthentication_xgboost_binary; Key | Value
--------------------------+---------------------------------------------------- Model Name | model_banknoteauthentication_xgboost_binary Schema Name | public Owner | rdsdb Creation Time | Wed, 03.03.2021 20:08:07 Model State | TRAINING | TRAINING DATA: | Query | SELECT * | FROM "BANKNOTEAUTHENTICATION_TRAIN" Target Column | CLASS | PARAMETERS: | Model Type | xgboost Function Name | func_model_banknoteauthentication_xgboost_binary Function Parameters | variance skewness curtosis entrophy Function Parameter Types | float8 float8 float8 float8 IAM Role | arn:aws:iam::XXXXXXXXX988:role/Redshift-ML S3 Bucket | redshift-ml-sagemaker Max Runtime | 5400 | HYPERPARAMETERS: | num_round | 100 objective | binary:logistic
(23 rows)

After the create model command is complete, the show model command output looks like the following code. The model state is now READY and has additional information like Estimated Cost and train:error. For models created with Amazon SageMaker Autopilot (AUTO ON), we see validation as one metric that gives the model’s accuracy, whereas AUTO OFF with XGBoost provides train:error, which is a measure of accuracy. In the following code, the value 0.000000 indicates the model is close to 100% accurate.

dev=# show model model_banknoteauthentication_xgboost_binary; Key | Value
-------------------------+--------------------------------------------
Model Name | model_banknoteauthentication_xgboost_binary Schema Name | public Owner | rdsdb Creation Time | Wed, 03.03.2021 20:08:07 Model State | READY train:error | 0.000000 Estimated Cost | 0.005455 |
TRAINING DATA: |
Query | SELECT * | FROM "BANKNOTEAUTHENTICATION_TRAIN" Target Column | CLASS |
PARAMETERS: |
Model Type | xgboost Function Name | func_model_banknoteauthentication_xgboost_binary Function Parameters | variance skewness curtosis entrophy Function Parameter Types | float8 float8 float8 float8 IAM Role | arn:aws:iam::XXXXXXXXX988:role/Redshift-ML S3 Bucket | redshift-ml-sagemaker Max Runtime | 5400 |
HYPERPARAMETERS: |
num_round | 100 objective | binary:logistic
(25 rows)

Inference

For binary and multi-class classification problems, we compute the accuracy as the model metric. Accuracy can be calculated based on the following:

accuracy = (sum (actual == predicted)/total) *100

Let’s apply the preceding code to our use case to find the accuracy of the model. We use the banknoteauthentication_test table to test the accuracy, and use the newly created function func_model_banknoteauthentication_xgboost_binary to predict and take the columns other than the target and label as the input.

-- check accuracy WITH infer_data AS (
SELECT class AS label,
func_model_banknoteauthentication_xgboost_binary (variance, skewness, curtosis, entrophy) AS predicted,
CASE WHEN label IS NULL THEN 0 ELSE label END AS actual,
CASE WHEN actual = predicted THEN 1::INT ELSE 0::INT END AS correct
FROM banknoteauthentication_test),
aggr_data AS (
SELECT SUM(correct) AS num_correct,
COUNT(*) AS total
FROM infer_data) SELECT (num_correct::FLOAT / total::FLOAT) AS accuracy FROM aggr_data; --output of the above query accuracy
------------------- 0.992351816443595
(1 row)

The inference query output (0.9923 *100 = 99.23 %) matches the output from the show model command.

Let’s run the prediction query on the banknoteauthentication_test to get the count of original vs. counterfeit banknotes:

--check the prediction WITH infer_data AS ( SELECT func_model_banknoteauthentication_xgboost_binary(variance, skewness, curtosis, entrophy) AS predicted FROM banknoteauthentication_test )SELECT CASE WHEN predicted = '0' THEN 'Original banknote' WHEN predicted = '1' THEN 'Counterfeit banknote' ELSE 'NA' END AS banknote_authentication ,COUNT(1) AS count FROM infer_data GROUP BY 1; --output of the above query banknote_authentication | count
-------------------------+------- Original banknote | 310 Counterfeit banknote | 213
(2 rows)

Troubleshooting

Please check the Redshift ML regression blog for troubleshooting tips.

Conclusion

Redshift ML provides an easy and seamless platform for database users to create, train, and tune models using the SQL interface. This post showed how data scientists can train an XGBoost model using Redshift ML with the  CREATE MODEL command and use the model for inference using SQL.


About the Authors

Satish Sathiya 100Satish Sathiya is a Senior Product Engineer at Amazon Redshift. He is an avid big data enthusiast who collaborates with customers around the globe to achieve success and meet their data warehousing and data lake architecture needs.

 

 

Debu PandaDebu Panda, 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.

 

 

 

Jiayuan Chen 100Jiayuan Chen is a Software Development Engineer at AWS. He is passionate about designing and building data-intensive applications, and has been working in the areas of data lake, query engine, ingestion, and analytics. He keeps up with latest technologies and innovates things that spark joy.

 

 

Lokesh Gupta 100Lokesh Gupta is a Software Development Manager at AWS and manages the Machine Learning Compiler Technologies team. He leads the effort to optimize ML models for best inference performance on the Cloud and Edge targets.