MySQL HeatWave AutoML is simple to use, and the introduction of NL2ML (natural language to machine learning) makes it even easier to use. NL2ML provides step-by-step guidelines on how to use MySQL HeatWave AutoML for a particular business problem.

You can use NL2ML to do the following:

NL2ML uses pretrained foundational models supported by OCI Generative AI. MySQL HeatWave in-database LLMs are currently not supported. To review available OCI Generative AI models, see Pretrained Foundational Models in Generative AI.

For every major MySQL release, NL2ML creates a new vector store from the MySQL HeatWave User Guide documentation. After creating each new vector store, the old vector store gets deleted. Due to the creation of vector stores, some NL2ML calls may take longer to complete.

Review additional Syntax Examples.

To view the chat history and current Generative AI model, query the @nl2ml)options session variable. Optionally, use JSON_PRETTY to view the output in an easily readable format.

mysql> SELECT JSON_PRETTY(@nl2ml_options);

Use one of the following commands to reset the chat history:

mysql> SET @nl2ml_options = NULL;

mysql> SET @nl2ml_options = JSON_REMOVE(@nl2ml_options, '$.chat_history');

You have the option to change the current Generative AI model. To review available options for model_id, see Pretrained Foundational Models in Generative AI.

To change the current Generative AI model and maintain the current chat history, use the following command:

mysql> SET @nl2ml_options = JSON_SET(@nl2ml_options, '$.mode_id', "model_id");

For example:

mysql> SET @nl2ml_options = JSON_SET(@nl2ml_options, '$.mode_id', "meta.llama-3.1-405b-instruct");

To change the current Generative AI model and reset the chat history, use the following command:

mysql> SET @nl2ml_options = JSON_OBJECT("model_id", "model_id");

For example:

mysql> SET @nl2ml_options = JSON_OBJECT("model_id", "meta.llama-3.1-405b-instruct");

The following use case uses NL2ML to request AutoML queries to train, use, and evaluate a model for data related to a food delivery service. The examples show how you can use follow-up questions based on previous questions and responses.

Refer to the following questions and responses.

mysql> CALL sys.NL2ML("Give me a training query to predict how long it takes to deliver food", @output);
Query OK, 0 rows affected (6.8284 sec)

mysql> SELECT @output;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @output                                                                                                                                                         |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"text": "```sql\nCALL sys.ML_TRAIN('mlcorpus.food_delivery', 'delivery_duration', \n          JSON_OBJECT('task', 'regression'), @food_delivery_model);\n```"} |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.0003 sec)
mysql> CALL sys.NL2ML("how can I deploy this model?", @output);
Query OK, 0 rows affected (11.7221 sec)

mysql> SELECT @output;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @output                                                                                                                                                                                                                                                                        |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"text": "To deploy a trained machine learning model for predicting food delivery duration, you need to load the model into HeatWave AutoML using the ML_MODEL_LOAD routine. 
            \n\nFirst, you need to train the model using the ML_TRAIN routine and get the model handle.\n\n```mysql\nCALL sys.ML_TRAIN('mlcorpus.food_delivery', 'delivery_duration', NULL, @food_delivery_model);
            \n```\n\nThen, you can load the model into HeatWave AutoML using the ML_MODEL_LOAD routine.\n\n```mysql\nCALL sys.ML_MODEL_LOAD(@food_delivery_model, NULL);
            \n```\n\nAfter loading the model, you can use it to make predictions using the ML_PREDICT_TABLE or ML_PREDICT_ROW routines.
            \n\nFor example, to predict the delivery duration for a new food delivery order, you can use the ML_PREDICT_ROW routine.
            \n\n```mysql\nSELECT sys.ML_PREDICT_ROW(@new_order, @food_delivery_model);
            \n```\n\nReplace `@new_order` with the actual data for the new order.\n\nNote: Make sure to replace `@food_delivery_model` with the actual model handle returned by the ML_TRAIN routine. 
            \n\nAlso, ensure that the model is loaded into HeatWave AutoML before making predictions. You can check the active models using the ML_MODEL_ACTIVE routine. 
            \n\nIf you need to unload the model, you can use the ML_MODEL_UNLOAD routine.\n\n```mysql\nCALL sys.ML_MODEL_UNLOAD(@food_delivery_model);\n```"} |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.0002 sec)
mysql> CALL sys.NL2ML("how can I evaluate this model?", @output);
Query OK, 0 rows affected (16.4552 sec)

mysql> SELECT @output;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @output                                                                                                                                                                                                                                                                                                                                          |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"text": "To evaluate a trained machine learning model for predicting food delivery duration, you can use the ML_SCORE function provided by HeatWave AutoML. Since the task is regression (predicting delivery duration), you can use regression metrics such as neg_mean_absolute_error, neg_mean_squared_error, or r2.
            \n\nHere's an example SQL query to evaluate the model:\n\n```sql\nCALL sys.ML_SCORE('mlcorpus.food_delivery_validate', 'delivery_duration', @delivery_duration_model, 'neg_mean_absolute_error', @score, NULL);\nSELECT @score;
            \n```\n\nIn this query:\n\n- 'mlcorpus.food_delivery_validate' is the table used for validation, which should have the same columns as the training dataset but with different data.
            \n- 'delivery_duration' is the target column containing the actual delivery durations.\n- @delivery_duration_model is the model handle or a session variable containing the model handle.
            \n- 'neg_mean_absolute_error' is the metric used to evaluate the model.\n- @score is the user-defined variable name for the computed score.\n- NULL is used for the options parameter since it's not required for regression tasks.
            \n\nNote that you should replace '@delivery_duration_model' with the actual model handle or session variable containing the model handle. Also, make sure to create a validation table 'mlcorpus.food_delivery_validate' with the same columns as the training dataset but with different data."} |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.0002 sec)
mysql> CALL sys.NL2ML("How can I call predictions for a new row??", @output);
Query OK, 0 rows affected (16.8089 sec)

mysql> SELECT @output;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @output                                                                                                                                                                                                                                                                                                                                              |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"text": "To predict the delivery duration for a new food delivery order, you can use the ML_PREDICT_ROW function. First, ensure that the model you want to use is loaded. 
            \n\nAssuming that your model handle is stored in a session variable @delivery_duration_model, you can use the following SQL query to make a prediction for a new row:
            \n\n```sql\nSET @new_order = JSON_OBJECT(\n  \"order_id\", \"new_order_id\",\n  \"customer_id\", \"new_customer_id\",\n  \"restaurant_id\", \"new_restaurant_id\",\n  \"food_item\", \"new_food_item\",\n  \"order_time\", \"new_order_time\",\n  \"delivery_time\", NULL,\n  \"estimated_delivery_time\", \"new_estimated_delivery_time\",
            \n  \"delivery_distance\", \"new_delivery_distance\",\n  \"delivery_method\", \"new_delivery_method\",\n  \"traffic_conditions\", \"new_traffic_conditions\",\n  \"weather_conditions\", \"new_weather_conditions\",\n  \"order_priority\", \"new_order_priority\",\n  \"payment_method\", \"new_payment_method\"\n);
            \n\nSELECT sys.ML_PREDICT_ROW(@new_order, @delivery_duration_model, NULL);\n```\n\nReplace the values in the JSON_OBJECT with the actual values for the new order. The column names must match the feature column names in the training dataset. 
            \n\nNote that the delivery_time is set to NULL, assuming that it is the target variable that you are trying to predict. The result will be a JSON object containing the predicted delivery duration and the feature values used to make the prediction."} |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.0003 sec)
mysql> CALL sys.NL2ML("How can I generate explanation for a new table?", @output);
Query OK, 0 rows affected (8.7374 sec)

mysql> SELECT @output;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @output                                                                                                                                                                                                       |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"text": "```sql\nCALL sys.ML_EXPLAIN_TABLE('mlcorpus.new_food_delivery', @food_delivery_model, 'mlcorpus.food_delivery_explanations', JSON_OBJECT('prediction_explainer', 'permutation_importance'));\n```"} |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.0003 sec)