16.20 - Function Mapping for Native Functions - Teradata Vantage NewSQL Engine

Teradata Vantage™ NewSQL Engine Release Summary

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
created_date
March 2019
category
Release Notes
featnum
B035-1098-162K

You can use a function mapping to specify a simple name for executing a table operator within a database or user, in addition to executing a table operator on an external server. The function mapping defines input tables, output tables, and other parameters to use during processing. Previously, users would have to specify the location of a table operator such as, kmeans@coprocessor. For example:

  SELECT * FROM kmeans@coprocessor (
    ON computers_train1 AS InputTable 
    ON kmeanssample_centroid AS CentroidsTable
       USING OutputTable(kmeanssample_output)
       ClusterAssignmentTable(kmeanssample_clusteredoutput)
    ) AS dt ORDER BY clusterid ; 

After using the CREATE FUNCTION MAPPING SQL statement to define the function mapping, user queries can simply specify a function mapping name such as, kmeans. For example:

   SELECT * FROM 
    KMeans(
           ON computers_train1 AS InputTable
           ON kmeanssample_centroid AS CentroidsTable
           OUT TABLE OutputTable(kmeanssample_output)
           OUT TABLE ClusterAssignmentTable(kmeanssample_clusteredoutput)
           ) AS dt ORDER BY clusterid ;

Benefits

  • Use the CREATE FUNCTION MAPPING statement without the SERVER clause to map table operators within the database.
  • Eases the user experience by hiding the @coprocessor syntax, which can also enhance security. Users do not have to specify @coprocessor in queries.
  • Enables descriptive naming for flexibility.
  • The function mapping definition simplifies table specification.
  • Enables changes to query operation, such as function arguments, without impacting production queries.
  • You can specify multiple ANY IN TABLE clauses.

Considerations

For table operators stored within the database, if you do not specify a database or user, the location defaults to the following, in order of precedence:
  • Current default database or user for the session
  • SYSLIB

SQL Changes

For CREATE FUNCTION MAPPING and REPLACE FUNCTION MAPPING:
  • The SERVER clause is now optional, that is, not specified for a table operator within a database or user.
  • You can now specify multiple ANY IN TABLE clauses.

Additional Information

For more information about creating foreign tables, see Teradata Vantage™ SQL Data Definition Language Syntax and Examples, B035-1144.

For information about table operator processing, see "Table Operator" in Teradata Vantage™ SQL Data Manipulation Language , B035-1146 .