1.1 - Nested ML Engine Analytic Functions - Teradata Vantage

Teradata Vantage™ User Guide

prodname
Teradata Vantage
vrm_release
1.1
created_date
May 2020
category
User Guide
featnum
B700-4002-079K
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.