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.