This is a brief overview of the types of user-defined routines offered by Trafodion and a description on how to create them. The article also talks about operators and routines that are built into Trafodion.
Routines, Procedures and Functions
The most generic term used here is a Routine, which includes all of the following:
| Type | Subtype | Return Value(s) | Where in SQL syntax | Languages | Security | Description |
|---|---|---|---|---|---|---|
| Stored Procedure in Java (SPJ) | Output parameters Result sets | CALL statement | Java | trusted |
| |
Function (UDF) | Scalar UDF | Scalar value | In any scalar expression | C | trusted |
|
| Table-mapping UDF (TMUDF) | Table | FROM clause | Java / C++ | trusted |
|
Click the links in the table above for more information.
Known Issues
Unfortunately, the security features of Trafodion UDRs are not completely implemented at this time. Here is an outline of what we have today and of the design we are trying to follow:
| Security attribute of the UDR | TRUSTED | ISOLATED | Current Implementation |
|---|---|---|---|
| UDR code runs under Trafodion id, has full control over the database, can read and write any data, can corrupt system data structures. | yes | no | yes |
| UDR code runs in a separate process (tdm_udrserver) | no | yes | yes (except for TMUDF compiler interface) |
| UDR code runs under a special user id with low privileges (similar to a guest id) | no | yes | no |
| Scalar UDFs are converted into a relational operator | no | yes | yes |
| Ability to run SQL statements on a special connection that implements definer's rights or invoker's rights (the user id that created the UDR or that invoked the UDR, respectively) | yes | yes | no (SQL in SPJs runs as DB_ROOT) |
Levels of Integration with Trafodion
In some cases, we may want to include a new function in Trafodion, if it is generic and potentially useful to most Trafodion users. There are four levels of integration you can do, which we'll discuss here.
Regular UDRs
The least integrated option is of course to write your own UDFs and manage their source code and build process in your own environment. Those are not part of Trafodion at all. If you would like to share generic UDFs with the Trafodion community, consider posting a link to your github repository on the Trafodion Code Examples web page.
UDRs shipped with Trafodion that are created during INITIALIZE TRAFODION
Some UDRs come with Trafodion. Most of them are UDRs that are needed for manageability. If you would like to add such a UDR, take a look at file core/sql/sqlcomp/CmpSeabaseDDLroutine.cpp and at method CmpSeabaseDDL::createLibmgrProcs() to see an example how such procedures are created.
Predefined UDRs
If you have a TMUDF that just transforms data and has no need for grant/revoke security, then you can go one step further and make it a "predefined" TMUDF. This is a hybrid between a built-in and a user-defined function. Here is a comparison:
| Built-in function | Predefined UDF | TMUDF (not predefined) | |
|---|---|---|---|
| Language | C++ | C++, Java | C++, Java |
| Interface | Complex internal interface | TMUDF interface | TMUDF interface |
| Privileges | Anyone can execute built-in functions | Anyone can execute predefined UDFs | Grant/revoke |
| Name | One-part name | One-part name, can be eclipsed by a UDF of the same name in the default schema | 3-part name |
| Metadata | Hard-wired in source code | No metadata in metadata tables, uses TMUDF compiler interface | Metadata tables and optional TMUDF compiler interface |
| Syntax | Special syntax can be defined in Trafodion parser | UDF syntax or special syntax, defined in Trafodion parser | UDF syntax only |
| Execution | In the Trafodion engine (master executor, ESP) | In a separate tdm_udrserv process | In a separate tdm_udrserv process |
If you want to add a predefined TMUDF, you can follow the example of an existing one: https://github.com/apache/trafodion/blob/master/core/sql/sqludr/SqlUdrPredefTimeSeries.cpp. There are other predefined UDFs like event_log_reader and jdbc, but those should have been made regular UDRs (mea culpa), since not all system administrators may want to give access to those UDFs.
Built-in Functions
The tightest integration can of course be achieved by adding to one of the class hierarchies in Trafodion. Here are the main ones used to extend the code:
| Class | Description | Example |
|---|---|---|
| ItemExpr | Scalar expression, taking zero or more scalar inputs and producing one scalar result. Dave Birdsall put together a wiki page on how to add a scalar operator. | UPPER() |
| RelExpr | Relational expression, taking zero or more table-valued inputs and producing a table-valued result. | UNION |
| Rule | Optimizer rule. These could be transformation rules like the join commutativity rule (A join B => B join A) or implementation rules that specify runtime operators (see example). | A join B ==> A hash_join B |
| PartitioningFunction | Partitioning function. This describes how rows can be partitioned to multiple nodes in a cluster, based on partitioning key columns. | Hash partitioning function. |
Related articles
There is no content with the specified labels