Nested Teradata ML Engine Analytic Functions

Teradata® Vantage User Guide

brand
Teradata Vantage
prodname
Teradata Vantage
vrm_release
1.0
category
User Guide
featnum
B700-4002-098K
Teradata 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 Teradata 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 sub-selects of the ON clauses. When nested queries are executed, each function runs independently. For each function, inputs are transferred from the Teradata SQL Engine to the Teradata ML Engine. The function runs and the results are transferred from the Teradata ML Engine to the Teradata 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 the Teradata ML Engine to the Teradata SQL Engine and back to the Teradata 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 the Teradata ML Engine back to Teradata SQL Engine.
However, when nesting one Teradata 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.