May 28, 2024 — ML Functions Release Notes

Simpler SQL for storing results from ML functions

You can now call the Forecast and Detect Anomalies ML Functions directly in the FROM clause of a SELECT statement. You can call methods like <model_name>!DETECT_ANOMALIES, <model_name>!FORECAST, and <model_name>!SHOW_EVALUATION_METRICS in the FROM clause.

You can use this technique to simplify the SQL statements for saving results to a table. For example, rather than using the SQLID Snowflake Scripting variable with the RESULT_SCAN function to create a table containing these results:

BEGIN
  CALL model!FORECAST(FORECASTING_PERIODS => 7);
  LET x := SQLID;
  CREATE TABLE my_forecasts AS SELECT * FROM TABLE(RESULT_SCAN(:x));
END;
SELECT * FROM my_forecasts;
Copy

you can use a query that directly selects from the results of calling the methods:

CREATE TABLE my_forecasts AS
  SELECT * FROM TABLE(model!forecast(forecasting_periods => 7));
Copy

As shown in the example above, when calling the method, omit the CALL command. Instead, put the call in parentheses, preceded by the TABLE keyword.

For details, see Selecting columns from SQL class instance methods that return tabular data.

In addition, as announced earlier and shown in the example above, you can use the TABLE keyword (rather than calling SYSTEM$REFERENCE) to create a reference to pass in to the method.

Language: English