C/C++ Table Operator Metadata Mapping | External Routine Programming | Vantage - C/C++ Table Operator Metadata Mapping - Advanced SQL Engine - Teradata Database

SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
qwr1571437338192.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1147
lifecycle
previous
Product Category
Teradata Vantage™

Data type metadata, including UDT and CDT metadata, can be passed to a table operator contract function.

Note the difference between the terms mapping and transformed type as follows:
Mapping Type
The External Type code that is used in the contract function to identify the data type of a column.
Transform Type
The predefined data type that a UDT or CDT value is converted to by invoking the transform function.

By default, all of the UDT or CDT data values are passed in their default transform form to the operator. This may correspond to either the SQL transform type of the UDT or another predefined type. The behavior is explicitly determined for each individual UDT or CDT type. For Java table operators, an option is provided to pass some UDTs and CDTs in an untransformed or atomized form.

Each of the UDT or CDT type is also mapped to an External Type code in the table operator contract function. Additional information about each type is passed in the UDT_BaseInfo_t structure for C/C++ table operators or the com.teradata.fnc.runtime.UDTBaseInfo class for Java table operators.

The following table shows the metadata mapping in the contract function from Teradata SQL type to the External Type code for UDTs and CDTs.
SQL UDT or Complex Data Type External Type Code
ARRAY/VARRAY ARRAY_DT
DATASET in the Avro storage format DATASET_AVRO_DT
DATASET in the CSV storage format DATASET_CSV_DT
Geospatial – MBB MBB_DT
Geospatial – MBR MBR_DT
Geospatial – ST_Geometry ST_GEOMETRY_DT
JSON JSON_DT
Period type PERIOD_DT
UDT (Distinct) UDT_DT
UDT (Structured) UDT_DT
XML XML_DT

Passing UDT and CDT Columns To and From a Table Operator

The contract function for a table operator describes the input and output columns to be passed and retrieved. The following sections provide detail on how UDT and CDT columns may be passed to and from a table operator.

Retrieving UDT and CDT Input Metadata

In the contract function, an External Type code referring to the UDT or CDT is passed in the parm_tx structure for the input column.

The contract function retrieves the input column metadata by calling the FNC_TblOpGetColDef function. This function returns the structure FNC_TblOpColumnDef_t which contains a field of type parm_tx for each input column. The datatype field of the parm_tx structure identifies a UDT or CDT column using an External Type code.

There are two ways to retrieve the UDT and CDT metadata:
  • FNC_TblOpColumnDef_t structures can be passed to the FNC_TblOpGetBaseInfo function, which fills out an array of UDT_BaseInfo_t structures corresponding to each input column. The FNC_TblOpGetBaseInfo function can only be invoked on the PE in the contract function.
  • You can use the FNC_TblOpGetUDTMetadata function which returns UDT_BaseInfo_t structures for one or more input or output columns. This function can be invoked on both the PE and AMP vprocs.

For structured UDTs, the fields of the UDT_BaseInfo_t structure may not be sufficient to provide all the metadata information. Because structured UDTs may have many attributes and may also contain an arbitrary level of nesting, metadata about the attributes of a structured UDT is retrieved using the FNC_TblOpGetStructuredAttributeInfo function. This function returns an array of attribute_info_t structures corresponding to all of the attributes in the structured UDT.

Setting UDT and CDT Output Metadata

The contract function calls the FNC_TblOpSetOutputColDef function to set the metadata for a group of output columns. The metadata is saved in the structure FNC_TblOpColumnDef_t. The data type encoding and udt name for the column can identify the UDT or CDT type output column. This is all the information that is needed to set the output metadata for a UDT or CDT column.