A UDF is similar to standard SQL functions such as SQRT, ABS, or TRIM and is invoked in the same manner.
- SQL
- C or C++
- Java
Teradata supports these types of UDFs:
| UDF Type | Description |
|---|---|
| Scalar | Takes input arguments and returns a single value result. |
| Aggregate | Takes grouped sets of relational data, makes a pass over each group, and returns one result for the group. |
| Table Function | Invoked in the FROM clause of an SQL SELECT statement. Returns a table a row at a time to the SELECT statement. Can produce the rows of a table from input arguments or by reading an external file or message queue. |
| Table Operator | Invoked in the FROM clause of an SQL SELECT statement. Accepts one or more tables or table expressions as input and generates a table as output. |
Scalar UDFs, aggregate UDFs, and table operators can run on the primary cluster or on compute clusters.
- C, C++ and Java UDFs can run on both the compute cluster and the primary cluster.
- Table functions only run on the primary cluster and are not supported on the compute clusters.
Creating and Managing SQL UDFs
Creating and Managing C, C++, or Java UDFs
Use the External Routine Installation Tool (tdextroutine) to create and manage C/C++ or Java UDFs.
If you have SQL scripts (BTEQ files) that use the CREATE/REPLACE FUNCTION syntax to create C/C++ or Java UDFs, use the External Routine Migration Tool (XRMigrationTool) to convert this syntax to tdextroutine calls for use with VantageCloud Lake.
For example, if you have existing SQL scripts that create C/C++ or Java UDFs for VantageCloud Enterprise or VantageCore, you must migrate the scripts to VantageCloud Lake using XRMigrationTool.