Nested Machine Learning Engine Analytic Functions | Teradata Vantage - Nested ML Engine Analytic Functions - Teradata Vantage

Machine Learning Engine User Guide

Product
Teradata Vantage
Release Number
9.02
9.2.4
9.2.3
2.3.2
1.3
Published
May 2022
Language
English (United States)
Last Update
2022-05-04
dita:mapPath
kaz1597341649653.ditamap
dita:ditavalPath
ehs1594682156756.ditaval
dita:id
B700-4004
lifecycle
previous
Product Category
Teradata Vantage™
ML Engine analytic function calls can be nested within a single SQL statement because the input (ON clause) of a function can be a SELECT statement that can contain another ML Engine analytic function call.
For example:
SELECT * FROM SentimentEvaluator (
  ON (
    SELECT * FROM SentimentExtractor (
      ON sentiment_extract_input as "input"
      USING
      TextColumn ('review')
      Accumulate ('category')
      "model" ('dictionary')
    ) AS dt1
  ) AS "input" PARTITION BY 1
  USING ObsColumn ('category')
  SentimentColumn ('out_polarity')
) AS dt;

You can construct queries with arbitrary levels of nested function calls by nesting function calls in the subqueries of the ON clauses. When nested queries are executed, each function runs independently. For each function, inputs are transferred from Advanced SQL Engine to ML Engine. The function runs and the results are transferred from ML Engine to Advanced SQL Engine.

Avoiding Back-and-Forth Data Transfer

Because the output of one function can be the input of another function, nesting functions can cause data transfer from ML Engine to Advanced SQL Engine and back to ML Engine.

You can write the query in a way that avoids some of the back-and-forth data transfer. For example, consider this query:
SELECT * FROM SentimentEvaluator (
  ON SentimentExtractor (
    ON sentiment_extractor_input as "input"
    USING
    TextColumn ('review')
    Accumulate ('category')
    "model" ('dictionary')
  ) AS "input" PARTITION BY 1
  USING
  ObsColumn ('category')
  SentimentColumn ('out_polarity')
) AS dt;
The preceding query does the following:
  • Avoids the back and forth transfer of the intermediate result.
  • Runs both functions in one request.
  • Transfers only the result of the outer function from ML Engine back to Advanced SQL Engine.
However, when nesting one ML Engine analytic function within another by specifying the inner function directly in the ON clause, these restrictions apply:
  • Only two levels of nesting are supported—the output of the inner function is used directly as the input to the outer function.
  • The outer function can have only one ON clause, which is the inner function.