Nested Teradata ML Engine Analytic Functions

Nested Teradata ML Engine Analytic Functions - Teradata Vantage

Teradata® Vantage User Guide

Product

Teradata Vantage

Release Number

1.0

Published

January 2019

Language

English (United States)

Last Update

2020-03-11

dita:mapPath

hfp1506029122470.ditamap

dita:ditavalPath

hfp1506029122470.ditaval

dita:id

B700-4002

lifecycle

Product Category

Teradata Vantage

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.