Models

Query Service now supports the core processes of building and deploying a model. You can use SQL to train the model using your data, evaluate its accuracy, and then apply train model to make predictions on new data. You can then use the model to generalize from your past data to make informed decisions about real-world scenarios.

The three steps in the model lifecycle for generating actionable insights are:

Use the model SQL extension, added to the existing SQL grammar, to manage the model lifecycle according to your business needs. This document covers the SQL required to create or replace a model, train, evaluate, retrain when necessary, and predict insights.

Model creation and training create-and-train

Learn how to define, configure, and train a machine learning model using SQL commands. The SQL below demonstrates how to create a model, apply feature engineering transformations, and initiate the training process to ensure the model is configured correctly for future use. The following SQL commands, detail different options for model creation and management:

CREATE MODEL | CREATE MODEL IF NOT EXISTS | CREATE OR REPLACE MODEL}
model_alias
[TRANSFORM (select_list)]
[OPTIONS(model_option_list)]
[AS {select_query}]

model_option_list:
    MODEL_TYPE = { 'LINEAR_REG' |
                   'LOGISTIC_REG' |
                   'KMEANS' }
  [, MAX_ITER = int64_value ]
 [, LABEL = string_array ]
[, REG_PARAM = float64_value ]

Example

CREATE MODEL churn_model
TRANSFORM (vector_assembler(array(current_customers, previous_customers)) features)
OPTIONS(MODEL_TYPE='linear_reg', LABEL='churn_rate')
AS
SELECT *
FROM churn_with_rate
ORDER BY period;

To help you understand the key components and configurations in the model creation and training process, the following notes explain the purpose and function of each element in the SQL example above.

Use SQL to reference the dataset used for training.

Update a model update

Learn how to update an existing machine learning model by applying new feature engineering transformations and configuring options such as the algorithm type and label column. Each update creates a new version of the model, incremented from the last version. This ensures changes are tracked, and the model can be reused in future evaluation or prediction steps.

The following example demonstrates updating a model with new transformations and options:

UPDATE MODEL <model_alias> TRANSFORM (vector_assembler(array(current_customers, previous_customers)) features)  OPTIONS(MODEL_TYPE='logistic_reg', LABEL='churn_rate')  AS SELECT * FROM churn_with_rate ORDER BY period;

Example

To help you understand the versioning process, consider the following command:

UPDATE MODEL model_vdqbrja OPTIONS(MODEL_TYPE='logistic_reg', LABEL='Survived') AS SELECT * FROM titanic_e2e_dnd;

After this command is executed, the model has a new version, as shown in the table below:

The following notes explain the key components and options in the model update workflow.

Evaluate models evaluate-model

To ensure reliable results, assess the accuracy and effectiveness of the model before deploying it for predictions with the model_evaluate keyword. The SQL statement below specifies a test dataset, specific columns, and the model’s version to test the model by evaluating its performance.

SELECT *
FROM   model_evaluate(model-alias, version-number,SELECT col1,
       col2,
       label-COLUMN
FROM   test -dataset)

The model_evaluate function takes model-alias as its first argument and a flexible SELECT statement as its second argument. Query Service first executes the SELECT statement and maps the results to the model_evaluate Adobe Defined Function (ADF). The system expects the column names and data types in the SELECT statement’s result to match those used in the training step. These column names and data types are treated as test data and label data for evaluation.

Predict predict

Next, use the model_predict keyword to apply the specified model and version to a dataset and generate predictions for the selected columns. The SQL below demonstrates this process, showing how to forecast outcomes using the model’s alias and version.

SELECT *
FROM   model_predict(model-alias, version-number,SELECT col1,
       col2,
       label-COLUMN
FROM   dataset)

model_predict accepts the model alias as the first argument and a flexible SELECT statement as the second argument. Query Service first executes the SELECT statement and maps the results to the model_predict ADF. The system expects that the column names and data types in the SELECT statement’s result to match those from the training step. This data is then used for scoring and generating predictions.

Evaluate and manage your models

Use the SHOW MODELS command to list all the available models you have created. Use it to view the models that have been trained and are available for evaluation or prediction. When queried, the information is fetched from the model repository which is updated during model creation. The details returned are: model ID, model name, version, source dataset, algorithm details, options/parameters, created/updated time, and the user who created the model.

SHOW MODELS;

The results appear in a table similar to the one seen below:

Cleanup and maintain your models

Use the DROP MODELS command to delete the models you created from the model registry. You can use it to remove outdated, unused, or unwanted models. This frees up resources and ensuring that only relevant models are maintained. You can also include an optional model name for improved specificity. This only drops model with the provided model version.

DROP MODEL IF EXISTS modelName
DROP MODEL IF EXISTS modelName modelVersion ;

Next steps

After reading this document, you now understand the base SQL syntax required to create, train, and manage trusted models using Data Distiller. Next, explore the Implement advanced statistical models document to learn about the various trusted models available and how to implement them effectively within your SQL workflows. If you haven’t already, make sure to review the Feature Engineering document to ensure that your data is optimally prepared for model training.