ML Engine Analytic Function Examples | Teradata Vantage - Examples - 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ā„¢

Function with Multiple ON Clauses

This function, Attribution_MLE, has two partitioned inputs and five dimensional inputs. When this SQL statement runs, all seven input tables are transferred to ML Engine. The Attribution_MLE function is then called using the transferred tables as inputs and with the function arguments in the SQL statement. Finally, the result of the Attribution_MLE function is returned to Advanced SQL Engine for use in processing the rest of the SQL statement.

SELECT * FROM Attribution_MLE (
  ON attribution_sample_table1 AS input1 PARTITION BY user_id 
                                         ORDER BY time_stamp
  ON attribution_sample_table2 AS input2 PARTITION BY user_id 
                                         ORDER BY time_stamp
  ON conversion_event_table AS conversion DIMENSION
  ON excluding_event_table AS excluding DIMENSION
  ON optional_event_table AS optional DIMENSION
  ON model1_table AS model1 DIMENSION
  ON model2_table AS model2 DIMENSION
  USING EventColumn ('event')
        TimestampColumn ('time_stamp')
        WindowSize ('rows:10&seconds:20')
) AS attrtb ORDER BY user_id, time_stamp;

Function with OUT TABLE Clauses

This function, KMeans, also has multiple ON clauses, but no ON clause has a PARTITION BY or DIMENSION clause. OUT TABLE clauses specify additional (secondary) output tables (those other than the primary output table). When this SQL statement runs, the results of the ON clauses are transferred to ML Engine. The KMeans function is then called using the transferred tables as inputs and with the function arguments provided in the SQL statement. The secondary outputs of the KMeans function are returned to Advanced SQL Engine in the kmeanssample_output and kmeanssample_clusteredoutput tables as specified in the function arguments. Finally, the result of the KMeans function is returned to Advanced SQL Engine for use in processing the rest of the SQL statement.

SELECT * FROM KMeans (
  ON computers_train1 AS InputTable
  ON kmeanssample_centroid AS CentroidsTable
  OUT TABLE OutputTable (kmeanssample_output)
  OUT TABLE ClusteredOutput (kmeanssample_clusteredoutput)
  USING
  MaxIterNum ('10')
) AS kmouttb2;

Nested Functions

This example calls nested functions. The output of SentimentExtractor is used as the input to SentimentEvaluator. When this SQL statement is executed, the input table for SentimentExtractor is transferred to ML Engine. The SentimentExtractor function is then called using the transferred table as input and with the function arguments provided in the SQL statement. The result of the SentimentExtractor function is then returned to Advanced SQL Engine. This intermediate result is transferred to ML Engine. Then the SentimentEvaluator function is called using the transferred intermediate result as input and with the function arguments provided in the SQL statement. Finally, the result of the SentimentEvaluator function is returned to Advanced SQL Engine for use in processing the rest of the SQL statement. See Nested ML Engine Analytic Functions. SQL identifiers that are reserved keywords must be enclosed in double quotation marks.

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;