SQL for Machine Learning (SQLML) is a MaxCompute feature that provides a SQL interface for machine learning. SQLML uses Platform for AI to perform operations such as model creation, prediction, and evaluation. This topic describes the features of SQLML, the supported machine learning models, and its prediction and evaluation functions.
Features
The MaxCompute SQLML feature requires both MaxCompute and PAI. You can use a client to develop SQLML jobs. These jobs use Platform for AI to train models on data in MaxCompute and make predictions to guide your business planning. The components work as follows:
MaxCompute: Provides SQLML, the SQL interface for machine learning.
Client: The platform for running SQL statements. You can choose DataWorks (recommended), MaxCompute SDK (Java SDK or Python SDK), MaxCompute client (odpscmd), or MaxCompute Studio.
Platform for AI: Provides machine learning models.
MaxCompute SQLML allows data engineers, analysts, and data scientists to use SQL to create, train, and apply machine learning models. SQL practitioners can use their existing skills to apply machine learning without migrating data.
Instructions
Follow these steps to use the MaxCompute SQLML feature:
Activate MaxCompute, DataWorks (Basic Edition), and Platform for AI services (pay-as-you-go for PAI-Studio, PAI-DSW, and PAI-EAS).
Dataset preparation
This data is used for model training and prediction.
Create or configure a DataWorks workspace. Set the computing engine service to MaxCompute and the machine learning service to PAI Studio.
Use DataWorks to create a table for the dataset and import the data.
Process the imported data based on the model's requirements. Then, create a training dataset and a test dataset. The training dataset is used for model training, and the test dataset is used for prediction.
Create a machine learning model and make predictions using the prediction model function provided by MaxCompute.
Evaluate the accuracy of the prediction results using the evaluation model function provided by MaxCompute.
For a quick start example, see Quick Start.
Supported machine learning models
MaxCompute SQLML currently supports the following machine learning models:
Binary logistic regression: The model name is logisticregression_binary. For more information, see Linear Support Vector Machine.
Multiclass logistic regression: The model name is logisticregression_multi. For more information, see PS-SMART for Multiclass Classification.
Linear regression: The model name is linearregression. For more information, see GBDT for Regression.
Supported prediction model function
MaxCompute SQLML currently supports the ml_predict prediction model function. The syntax is as follows:
ml_predict(model <model_name>, table <data_source>[, map<string, string> <parameters>])model_name: Required. Specifies the name of the new model.
data_source: Required. The data for prediction. This can be a table or a SELECT statement.
parameters: Optional. The parameters for prediction. These parameters are the same as those on the Platform for AI platform. For more information, see Linear Support Vector Machine, PS-SMART for Multiclass Classification, or GBDT for Regression.
Supported evaluation model functions
MaxCompute SQLML currently supports the following evaluation model functions to assess the accuracy of prediction results:
Binary classification evaluation: This evaluation is performed by the built-in function
ml_evaluate. It supports the calculation of metrics such as AUC, KS, and F1 score. The syntax is as follows:ml_evaluate(table <data_source>[, map<string, string> <parameters>])Multiclass classification evaluation: This evaluation is performed by the built-in function
ml_multiclass_evaluate. It evaluates the performance of a multiclass classification model based on the predicted and original results. The supported metrics include accuracy, kappa, and F1-score. The syntax is as follows:ml_multiclass_evaluate(table <data_source>[, map<string, string> <parameters>])Linear regression evaluation: This evaluation is performed by the built-in function
ml_regression_evaluate. It evaluates the performance of a regression algorithm model based on the predicted and original results. The output includes metrics and a histogram of residuals. The metrics include SST, SSE, SSR, R-squared, R, MSE, RMSE, MAE, MAD, MAPE, count, yMean, and predictMean. The syntax is as follows:ml_regression_evaluate(table <data_source>[, map<string, string> <parameters>])
In the preceding syntax:
data_source: Required. The data to be evaluated. It must contain the actual labels and the prediction results. This can be a table or a SELECT statement.
parameters: Optional. The parameters for evaluation. These parameters are the same as those on the Platform for AI platform. For more information, see Linear Support Vector Machine, PS-SMART for Multiclass Classification, or GBDT for Regression.