16.20 - R FNC Functions - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
April 2020
Content Type
Programming Reference
Publication ID
B035-1147-162K
Language
English (United States)

An R table operator typically accesses and modifies data stored in the database to perform an assigned task.

The following sections describe FNC functions available to R table operator developers. These functions provide an interface for R scripts to access and update data and metadata from the database.

R reads integer values as double. Therefore, you must cast the value to integer using as.integer() or other method. Otherwise you may get a data type mismatch error.

The following categories of functions are provided.

Functions for Data Manipulation

For table operators, the rows in the input and output streams are accessed following an iterator model such as the following:

  1. Open the input and output streams.
  2. Sequentially read the rows in the input stream.
  3. Directly access the columns in the current input row.
  4. Update the columns in the current output row.
  5. Sequentially write the output rows.
  6. Close the streams.

The following functions support the iterator model and are used to manipulate the input and output streams. These functions allow you to access and modify data in the database. Functions are also available to set and retrieve the format specification of input and output streams.

As with C protected mode table operators, a buffer is used for reading and writing rows. When a read row is issued, the system reads an entire buffer of rows so that the next time a read row is issued, it retrieves the row from the buffer. A new buffer is read when the end of the buffer is reached. Similarly, a buffer is used for writes. The size of the buffer is configurable and can be set in the cufconfig GDO(CServerMemSize).

Function Description
tdr.Open Opens an input or output stream.
tdr.Close Closes an input or output stream.
tdr.Read Reads a row from an input stream.
tdr.Write Writes a row to the output stream.
tdr.TblRead Reads an input stream into an R data frame and returns the data frame.
tdr.TblWrite Writes an R data frame to the output stream.
tdr.GetAttributeByNdx Retrieves an individual attribute from the current input stream row.
tdr.SetAttributeByNdx Sets the value of an individual attribute in the current output stream row.
tdr.GetStreamCount Retrieves the number of input and output streams.
tdr.GetFormat Retrieves information about the format of a specified stream.
tdr.SetFormat Sets an attribute of the format for a specified stream.
tdr.DisableCoGroup Disables the cogroup functionality for table operators that handle multiple input streams.

Functions for Metadata Manipulation

For table operators, some metadata passed during query invocation can be accessed from the contract function and the operator. This metadata can be set in the contract function if it was not specified as part of the query. In addition, a contract function can set a context that can be accessed during execution of the operator.

The following functions are used to access and set this metadata and the contract context.

Function Description
tdr.GetColCount Retrieves the number of columns in a stream.
tdr.GetColDef Retrieves the schema of the input or output stream.
tdr.SetOutputColDef Sets the schema of the output stream.
tdr.GetCountHashBy Retrieves the number of columns in the HASH BY clause for a specified input stream.
tdr.GetHashByDef Retrieves the columns in the HASH BY clause for a specified input stream.
tdr.SetHashByDef Sets the HASH BY specification for a specified input stream.
tdr.GetCountLocalOrderBy Retrieves the number of columns in the LOCAL ORDER BY clause for a specified input stream.
tdr.GetLocalOrderByDef Retrieves the columns in the LOCAL ORDER BY clause of a specified input stream, including the column ordering and a NULLS FIRST/NULLS LAST indicator.
tdr.SetLocalOrderByDef Sets the ordering specification for a specified input stream.
tdr.GetContractLength Retrieves the length of the contract context.
tdr.GetContractDef Retrieves the contract context.
tdr.SetContractDef Sets the contract context.
tdr.GetAsClauseName Retrieves the alias name (AS name) and associated length for a specified stream.
tdr.IsDimension Checks whether a stream is a DIMENSION input stream.
tdr.SetError Sets an error code and message to be displayed on the console when the query invoking the table operator is executed and an error is encountered.

Functions to Access USING Clause Parameters

You can use USING clauses to pass parameters as key-value pairs to the contract function and table operator. The following functions are used to access these pairs in the contract function and table operator.

Function Description
tdr.GetCustomKeyCount Retrieves the number of keys in the USING clauses.
tdr.GetCustomValuesOf Retrieves the values associated with a specified key.
tdr.GetCustomValuesAt Retrieves the values associated with a key at the specified index.
tdr.GetCustomKeyAt Retrieves the key at the specified index.

Functions for LOB Manipulation

The following functions are used to access and modify LOBs from R table operators. Using these functions, R table operators can read LOBs from input streams and create LOBs for output streams.

Function Description
tdr.LobOpen_CL Opens a LOB for reading.
tdr.LobClose Closes an input LOB.
tdr.GetLobLength Retrieves the length of an input LOB.
tdr.LobRead Reads a LOB.
tdr.LobCol2Loc Computes the output LOB locator needed to append data to a LOB in the current output row.
tdr.LobAppend Appends raw data to the end of the output LOB associated with a specified output locator.

Functions For Setting Column Definitions

The following functions are used to generate lists for each of the supported data types. You can then pass these lists to the tdr.SetOutputColDef function. This allows you to define the lists in a quick and efficient manner so that you will not have to explicitly define each and every list used in calls to tdr.SetOutputColDef.

Some of the functions simply return the default attributes required for a specific data type. Other functions indicate certain properties about the data type, such as the length or character type, based on the parameters passed to the function.

Character and CLOB Data Types
Function Description
tdr.Char Retrieves the column definition for the CHAR_DT data type.
tdr.VarChar Retrieves the column definition for the VARCHAR_DT data type.
tdr.Clob Retrieves the column definition for the CLOB_REFERENCE_DT data type.
Byte and BLOB Data Types
Function Description
tdr.Byte Retrieves the column definition for the BYTE_DT data type.
tdr.VarByte Retrieves the column definition for the VARBYTE_DT data type.
tdr.Blob Retrieves the column definition for the BLOB_REFERENCE_DT data type.
Numeric Data Types
Function Description
tdr.ByteInt Retrieves the column definition for the BYTEINT_DT data type.
tdr.SmallInt Retrieves the column definition for the SMALLINT_DT data type.
tdr.Integer Retrieves the column definition for the INTEGER_DT data type.
tdr.BigInt Retrieves the column definition for the BIGINT_DT data type.
tdr.Decimal1 Retrieves the column definition for the DECIMAL1_DT data type.
tdr.Decimal2 Retrieves the column definition for the DECIMAL2_DT data type.
tdr.Decimal4 Retrieves the column definition for the DECIMAL4_DT data type.
tdr.Decimal8 Retrieves the column definition for the DECIMAL8_DT data type.
tdr.Decimal16 Retrieves the column definition for the DECIMAL16_DT data type.
tdr.Float Retrieves the column definition for the REAL_DT data type.
tdr.Real Retrieves the column definition for the REAL_DT data type.
DateTime and Interval Data Types
Function Description
tdr.Date Retrieves the column definition for the DATE_DT data type.
tdr.Time Retrieves the column definition for the TIME_DT data type.
tdr.TimeWTZ Retrieves the column definition for the TIME_WTZ_DT data type.
tdr.Timestamp Retrieves the column definition for the TIMESTAMP_DT data type.
tdr.TimestampWTZ Retrieves the column definition for the TIMESTAMP_WTZ_DT data type.
tdr.IntervalYear Retrieves the column definition for the INTERVAL_YEAR_DT data type.
tdr.IntervalYTM Retrieves the column definition for the INTERVAL_YTM_DT data type.
tdr.IntervalMonth Retrieves the column definition for the INTERVAL_MONTH_DT data type.
tdr.IntervalDay Retrieves the column definition for the INTERVAL_DAY_DT data type.
tdr.IntervalDTH Retrieves the column definition for the INTERVAL_DTH_DT data type.
tdr.IntervalDTM Retrieves the column definition for the INTERVAL_DTM_DT data type.
tdr.IntervalDTS Retrieves the column definition for the INTERVAL_DTS_DT data type.
tdr.IntervalHour Retrieves the column definition for the INTERVAL_HOUR_DT data type.
tdr.IntervalHTM Retrieves the column definition for the INTERVAL_HTM_DT data type.
tdr.IntervalHTS Retrieves the column definition for the INTERVAL_HTS_DT data type.
tdr.IntervalMinute Retrieves the column definition for the INTERVAL_MINUTE_DT data type.
tdr.IntervalMTS Retrieves the column definition for the INTERVAL_MTS_DT data type.
tdr.IntervalSecond Retrieves the column definition for the INTERVAL_SECOND_DT data type.

Additional Functions for Developing R Table Operators

The following additional functions may also be useful when developing R table operators.

Function Description
tdr.ampinfo Returns information related to the AMP and node where the table operator is running.
tdr.dbsinfo Returns information related to the current running table operator or contract function, such as user account and user name.
tdr.getnodedata Returns node IDs and AMP IDs for all online AMP vprocs, allowing table operators to configure themselves to run on specific AMPs.
tdr.tracestring Returns the current setting of the trace string that was set up with the SET SESSION FUNCTION TRACE statement.
tdr.tracewrite Writes trace output into a temporary trace table defined by a CREATE GLOBAL TEMPORARY TRACE TABLE statement.

Prerequisite

Before calling these functions, the TDR package must be loaded by including the following statement in the R script.

library(tdr)

Terminology

R types do not include scalar types. Scalar types such as integers are typically represented as a unit vector of the given type (integer). In the following sections, unit vectors of a type are specified by giving only the type. For example, integer and character denote integer and character unit vectors, respectively. Vectors with more than one element are specified as type vector, for example, integer vector.