SQLML is an SQL portal provided by MaxCompute for you to use the capabilities of Machine Learning Platform for AI (PAI). At the underlying layer, MaxCompute SQLML relies on PAI to create, predict, and evaluate models. This topic describes SQLML features. It also describes the machine learning models, model prediction functions, and model evaluation functions supported by SQLML.


MaxCompute SQLML depends on Alibaba Cloud services: MaxCompute and PAI. You can develop MaxCompute SQLML jobs in DataWorks, learn MaxCompute data by using PAI, and use machine learning models to make predictions. Then, use these results to guide your business planning.
  • MaxCompute: provides SQLML, an SQL portal for you to use PAI.
  • Client: SQL operation platform. You can choose DataWorks (recommended), MaxCompute SDK (Java SDK or Python SDK), MaxCompute odpscmd, or MaxCompute Studio.
  • PAI: provides machine learning models.

MaxCompute SQLML helps data developers, analysts, and data scientists use SQL to create, train, and apply machine learning models. It also helps SQL practitioners use their SQL skills to implement PAI capabilities without the need to migrate data.

Usage notes

Procedure on how to use MaxCompute SQLML:
  1. Activate MaxCompute, DataWorks Basic, and Pay-As-You-Go (PAI Studio, DSW, and EAS).
  2. Prepare a dataset.

    The dataset is used to train and predict models.

  3. Create or configure a DataWorks workspace. Set Compute Engines to MaxCompute and Machine Learning Services to PAI Studio for the workspace.
  4. Use DataWorks to create a table and import the data in the dataset into the table.
  5. Process the imported data based on the requirements of the specific model and create training datasets and test datasets. Training datasets are used to train models. Test datasets are used to predict models.
  6. Create a machine learning model and make predictions by using the model prediction functions provided by MaxCompute.
  7. Evaluate the accuracy of the prediction results by using the model evaluation functions provided by MaxCompute.

For usage examples, see Quick start.

Supported machine learning models

MaxCompute SQLML supports the following machine learning models:

Supported model prediction functions

MaxCompute SQLML supports the ml_predict model prediction function. Syntax:
ml_predict(model <model_name>, table <data_source>[, map<string, string> <parameters>])

Supported model evaluation functions

MaxCompute SQLML supports the following model evaluation functions to evaluate the accuracy of the prediction results:
  • Binary classification evaluation: implemented by using the built-in function ml_evaluate. You can evaluate the model by using indexes such as area under curve (AUC), Kolmogorov-Smirnov (KS), and F1 score. Syntax:
    ml_evaluate(table <data_source>[, map<string, string> <parameters>])
  • Multiclass classification evaluation: implemented by using the built-in function ml_multiclass_evaluate. You can evaluate a multiclass classification model based on its prediction and actual results. The evaluation indexes include accuracy, kappa, and F1 score. Syntax:
    ml_multiclass_evaluate(table <data_source>[, map<string, string> <parameters>])
  • Linear regression evaluation: implemented by using the built-in function ml_regression_evaluate. You can evaluate a linear algorithm model based on its prediction and actual results such as the indexes and residual histogram. The evaluation indexes include SST, SSE, SSR, R2, R, MSE, RMSE, MAE, MAD, MAPE, count, yMean, and predictMean. Syntax:
    ml_regression_evaluate(table <data_source>[, map<string, string> <parameters>])
In the preceding syntax: