Syntax
where:
Syntax Element |
Description |
ANSIDATE |
Optional keyword specifying a date data type in standard 10-character, dash-separated format. For example, October 23, 2007 is expressed as: 2007-10-23 |
BIGINT |
Optional keyword specifying an 8-byte binary integer numeric data type. |
BLOB (lengthBytes) CLOB (lengthBytes) |
Optional keyword specifying an inline LOB (Large Object) column whose data is stored in the data rows defined by the schema just as non-LOB columns data is. An inline LOB is either of data type BLOB (Binary Large Object or CLOB (Character Large Object). lengthBytes specifies the maximum number of bytes of data that an inline LOB column value can contain in a data row and like a VARCHAR column, it can be of any size smaller than lengthBytes. The largest inline LOB supported by Teradata PT is 64, 000 bytes. The DataConnector operator reads or writes LOB data from or to an external file. The SQL Selector operator extracts LOB data from Teradata Database and puts it into data streams. The SQL Inserter operator reads data from the data stream and loads it into Teradata Database. Note: An 8-byte length indicator precedes the inline LOB data if data rows are coming from a file read by a DataConnector operator. Other operators (Export, Load, Update and Stream) cannot process LOB data. |
BLOB (lengthBytes) AS DEFERRED BY NAME CLOB (lengthBytes) AS DEFERRED BY NAME |
Optional keyword specifying a deferred LOB (Large Object) column whose data is contained in files rather than in the data rows defined by a Teradata PT schema. A deferred LOB is either of data type BLOB or CLOB. lengthBytes specifies the maximum number of bytes of data that a LOB column value can contain in Teradata Database and thus the maximum size of the files whose names are stored in the data rows. The maximum value of lengthBytes for deferred LOBs is 2, 000, 000, 000. The names of the files that contain deferred LOB data are stored in data rows exactly like VARCHAR column values, with a 2-byte length prefix. The row value is either a fully-qualified path name or the name of a file that Teradata PT looks for in the directory out of which it executes. The option AS DEFERRED BY NAME, which specifies a Teradata PT LOB file identifier, means that LOB data will be deferred and sent to the Teradata Database separately from non-LOB data. The presence of the option AS DEFERRED BY NAME indicates that LOB file identifiers are expected in the data stream instead of the LOB data itself. The omission of AS DEFERRED BY NAME in the BLOB or CLOB column definition directs Teradata PT to send BLOB or CLOB data to the Teradata Database in the data row, just like non-LOB data. SQL Selector operator is the only producer operator that can extract LOB data from Teradata Database. The SQL Inserter operator is the only consumer operator that can load deferred LOBs into Teradata Database. Other operators (Export, Load, Update and Stream) cannot process LOB data. |
BYTE BYTE (lengthBytes) |
Optional keyword specifying a fixed-length column containing binary data. lengthBytes specifies the column length in bytes. The maximum valid length is 64,000 bytes. If not specified, the default length is 1 byte. |
BYTEINT |
Optional keyword specifying a 1-byte integer, a whole number in the range ‑128 to +127. |
CHAR
|
Optional keyword that specifies a fixed-length column containing character data.
lengthBytes specifies the column length in bytes. The maximum valid length is 64, 000. If not specified, the default length is 1. |
CHAR VARYING
|
Optional keyword specifying a variable-length column containing character data.
lengthBytes specifies the column length in bytes. The maximum valid length is 64, 000. If not specified, the default length is 1. See description of VARCHAR. |
columnName |
Required user-supplied name of a column in the data source or data target described by the schema. Each specified column name must be unique within the schema. |
DECIMAL, DEC, or NUMERIC |
Optional keyword defining a decimal numeric data type. This data type also defines the length in digits and decimal precision for the data column. The lengthDigits specification is required if a precision is specified. If the lengthDigits and precision values are not specified, the default values are used. |
DEFINE SCHEMA |
Required keyword phrase specifying the beginning of the schema definition. |
DELIMITED |
Keyword indicating that Teradata PT is to generate the delimited-file format version of the Teradata PT schema based on one of the following: The DBS table name specified: |
DESCRIPTION 'descriptionString' |
Optional keyword phrase providing a descriptive comment about the defined schema. |
FLOAT |
Optional keyword defining the floating point data type. |
FROM SELECT |
Keyword phrase indicating that TPT is to generate a schema based on the columns in the result table of an SQL SELECT statement, specified either explicitly or indirectly via the SelectStmt attribute of operator operatorName. |
FROM TABLE |
Keyword phrase indicating that Teradata PT is to generate a schema based on the columns of the DBS table identified via the quoted string tableName. |
GRAPHIC GRAPHIC (lengthChars) |
Optional keyword specifying a fixed-length column containing 2-byte graphic characters. lengthChars specifies the column length in 2-byte graphic characters. The maximum valid length is 32, 000 characters. If not specified, the default length is 1 character. The byte length of a GRAPHIC column is twice the value of lengthChars. |
INTDATE |
Optional keyword specifying a column containing data values represented by 4-byte binary integers, calculated as follows: intdate value = (year - 1900) * 10000 + month * 100 + day For example, the INTDATE column value for october 23, 2007 is 1071023 |
INTEGER |
Optional keyword specifying a four-byte integer date type. |
INTERVAL DAY INTERVAL DAY(lengthDigits) |
Optional keyword specifying a time interval column in days, where lengthDigits is the number of digits used to express the interval, populated from right to left. Valid values are 1 through 4. The default value is 2. Note: External to Teradata Database, the values of INTERVAL columns are represented by character strings of various lengths and formats. Teradata PT, however, currently has no knowledge of these valid INTERVAL lengths and formats, and to Teradata PT, INTERVAL column values are indistinguishable from character strings in general. A job script can contain a character string that is intended to represent the value of an INTERVAL column, but Teradata PT cannot verify its validity. Assuming an INTERVAL column literal string is correctly formatted, it can reliably be compared to the corresponding type of INTERVAL column value in a data row only using either the "equals" (=) or the "not equals" (<>) comparison operators. For details on the valid lengths and formats of string representations of the values of INTERVAL column types SQL Data Types and Literals. |
INTERVAL DAY TO HOUR INTERVAL DAY(lengthDigits) TO HOUR |
Optional keyword specifying a time interval column in days and hours stated in the named column, where lengthDigits is the number of digits used to define the interval in days, populated from right to left. Valid values are 1 through 4. The default value is 2. See Note for INTERVAL DAY. |
INTERVAL DAY TO MINUTE INTERVAL DAY(lengthDigits) TO MINUTE |
Optional keyword specifying a time interval column in days, hours, and minutes for the named column, where lengthDigits is the number of digits used to express the interval in days, populated from right to left. Valid values are 1 through 4. The default value is 2. See Note for INTERVAL DAY. |
INTERVAL DAY TO SECOND INTERVAL DAY(lengthDigits) TO SECOND INTERVAL DAY TO SECOND(precision) INTERVAL DAY(lengthDigits) TO SECOND(precision) |
Optional keyword specifying a time interval column in days, hours, minutes, and seconds where: See Note for INTERVAL DAY. |
INTERVAL HOUR INTERVAL HOUR(lengthDigits) |
Optional keyword specifying a time interval column in hours, where lengthDigits is the number of digits used to express the hour interval, populated from right to left. Valid values are 1 through 4, with a default value of 2. See Note for INTERVAL DAY. |
INTERVAL HOUR TO MINUTE INTERVAL HOUR(lengthDigits) TO MINUTE |
Optional keyword specifying a time interval column in hours and minutes, where lengthDigits is the number of digits used to express the hour interval, populated from right to left. Valid values are 1 through 4, with a default value of 2. See Note for INTERVAL DAY. |
INTERVAL HOUR TO SECOND INTERVAL HOUR(lengthDigits) TO SECOND INTERVAL HOUR TO SECOND(precision) INTERVAL HOUR(lengthDigits) TO SECOND(precision) |
Optional keyword specifying a time interval column in hours, minutes, and seconds where: See Note for INTERVAL DAY. |
INTERVAL MINUTE INTERVAL MINUTE(lengthDigits) |
Optional keyword specifying a time interval column in minutes, where lengthDigits is the number of digits used to express the minute interval, populated from right to left. Valid values are 1 through 4, with a default value of 2. See Note for INTERVAL DAY. |
INTERVAL MINUTE TO SECOND INTERVAL MINUTE(lengthDigits) TO SECOND(precision) INTERVAL MINUTE(lengthDigits) TO SECOND INTERVAL MINUTE TO SECOND(precision) |
Optional keyword specifying a time interval column in minutes and seconds where: See Note for INTERVAL DAY. |
INTERVAL SECOND, INTERVAL SECOND(lengthDigits) INTERVAL SECOND(lengthDigits, precision) |
Optional keyword specifying a time interval column in seconds, optionally to the nearest fraction of a second, where: See Note for INTERVAL DAY. |
INTERVAL MONTH INTERVAL MONTH (lengthDigits) |
Optional keyword specifying a time interval in months, where lengthDigits is the number of digits used to express the months, populated from right to left. Valid values are 1 through 4 with a default value of 2. See Note for INTERVAL DAY. |
INTERVAL YEAR INTERVAL YEAR(lengthDigits) |
Optional keyword specifying a time interval column in years, where lengthDigits is the number of digits used to express the year, populated from right to left. Valid values are 1 through 4, with a default value of 2. See Note for INTERVAL DAY. |
INTERVAL YEAR TO MONTH INTERVAL YEAR(lengthDigits) TO MONTH |
Optional keyword specifying a time interval column in years and months, where lengthDigits is the number of digits used to express the year, populated from right to left. Valid values are 1 through 4, with a default value of 2. See Note for INTERVAL DAY. |
JSON (lengthBytes) JSON (lengthBytes) AS DEFERRED BY NAME |
Optional keyword specifying an inline JavaScript Object Notation (JSON) column whose data is stored in the data rows defined by the schema just as non-JSON column data is stored. lengthBytes specifies the maximum number of bytes of data that an in-line JSON column can contain in a data row and like a VARCHAR column, it can be any size smaller than lengthBytes. The largest in-line JSON column supported by Teradata PT is 64,000 bytes. The DataConnector operator reads and writes JSON data from and to an external file. The SQL Selector operator extracts JSON data from the Teradata Database and puts it into data streams. The SQL Inserter operator reads data from the data stream and loads it into the Teradata Database. Note: An 8-byte length indicator precedes the in-line JSON data if data rows are coming from a file read by the DataConnector operator. Other operators (Export, Load, Update and Stream) cannot process JSON data. AS DEFERRED BY NAME Optional keyword specifying a deferred JSON column whose data is contained in files rather than in the data rows defined by a Teradata PT schema. lengthBytes specifies the maximum number of bytes of data that an JSON column can contain in Teradata Database and thus the maximum size of the files whose names are stored in the data rows. The maximum value of lengthBytes for deferred JSON files is 2,000,000,000 bytes. The presence of the option AS DEFERRED BY NAME indicates that JSON file identifiers are expected in the data stream instead of the JSON data itself. The names of the files that contain deferred JSON data are stored in data rows exactly like VARCHAR column values, with a 2-byte length prefix. The row value is either a fully qualified path name or the name of a file that Teradata PT looks for in the directory out of which it executes. |
|
SQL Selector operator is the only producer operator that can extract JSON data from the Teradata Database. The SQL Inserter operator is the only consumer operator that can load deferred JSON files into the Teradata Database. Other operators (Export, Load, Update and Stream) cannot process JSON data. |
lengthBytes |
Length specification for nonnumeric data types. lengthBytes specification is required for variable-length data types. Note: When lengthBytes is specified for a CHAR or VARCHAR column encoded in UTF-16, it represents the number of bytes occupied by the column values, which is twice the character count that would be specified for length in an equivalent column description of a Teradata Database table schema. |
lengthChars |
Optional keyword specifying the length in 2-byte graphic characters of the values of a GRAPHIC or VARGRAPHIC column. The byte length of these values are twice the value of lengthChars. |
MACROCHARSET |
Optional clause that defines the Teradata Database server character set name for the character field. The MACROCHARSET is used to generate a CHARACTER SET <ServerCharsetName> clause for each field of character data type in the Stream Operator macro creation. If there is no MACROCHARSET clause for a field of character type, then the default will be one of the following: The MACROCHARSET clause applies only to: |
macroCharSetIdentifier |
Optional identifier that specifies the server storage character set name the Stream Operator uses to generate a CHARACTER SET clause for each character column when the Stream Operator creates the macros that are necessary for loading the data. The five possible valid values are: Values specified for this identifier are meaningful in a DEFINE SCHEMA statement only when the schema is employed by an instance of the Stream operator. |
METADATA (metadataType) |
Optional keyword specifying that values for this schema column are not included in the operator’s source data, but instead are metadata of type metadataType supplied by the operator itself. Note: Only the FileName metadata type is supported and only by the DataConnector producer operator. |
NUMBER |
Optional keyword specifying a column whose values represent numbers up to 38 digits of precision, optionally up to precision digits of scale with an optional exponent with the range of: Note: Columns defined as NUMBER in the TPT schema object cannot be referenced in boolean predicates or numeric value expressions in the TPT APPLY statement, unless the predicate or expression is solely made up of NUMBER column references. Predicates or expressions involving NUMBER column references and non-NUMBER column references are not supported. |
PERIOD(DATE) |
Optional keyword specifying a column whose values represent a duration of time, consisting of a beginning and ending date value. Note: The values of PERIOD columns are internally coded, both within Teradata Database and within Teradata PT. There is no character string representation of the value of any PERIOD column type in a job script, and two columns of the same PERIOD type cannot even be compared in a boolean predicate. For details on the internal structure of the values of PERIOD column types and how they are represented when retrieved through BTEQ SQL Data Types and Literals. |
PERIOD(TIME) PERIOD(TIME(precision)) |
Optional keyword specifying a column whose values represent a duration of time, consisting of a beginning and an ending time value. precision specifies the number of fractional seconds digits, with valued values of 0 through 6. The default value is 6. See Note for PERIOD(DATE). |
PERIOD (TIME WITH TIME ZONE) PERIOD(TIME(precision)WITH TIME ZONE) |
Optional keyword specifying a column whose values represent a duration of time, consisting of a beginning and an ending time-with-time-zone value. precision specifies the number of fractional seconds digits, with valid values of 0 through 6. The default value is 6. See Note for PERIOD(DATE). |
PERIOD(TIMESTAMP) PERIOD(TIMESTAMP(precision)) |
Optional keyword specifying a column whose values represent a duration of time, consisting of a beginning and an ending timestamp value. precision specifies the number of fractional seconds digits, with valid values of 0 through 6. The default value is 6. See Note for PERIOD(DATE). |
PERIOD(TIMESTAMP WITH TIME ZONE) PERIOD(TIMESTAMP(precision) |
Optional keyword specifying a column whose values represent a duration of time, consisting of a beginning and an ending timestamp-with-time-zone value. precision specifies the number of fractional seconds digits, with valid values of 0 through 6. The default value is 6. See Note for PERIOD(DATE). |
lengthDigits |
Optional length value in digits for data types that contain decimal numbers. The range and default value for lengthDigits vary according to the data type to which they are applied. For details see the individual data type descriptions in this table. |
OF OPERATOR |
Keyword phrase indicating that Teradata PT is to generate a schema based on the SQL SELECT statement assigned to the SelectStmt attribute of the script-defined operatorName. |
operatorName |
The name of an operator, defined in a DEFINE OPERATOR statement, whose SQL SELECT statement is the basis for a Teradata PT-generated schema. |
precision |
Optional value that defines the number of digits to the right of the decimal point. The range and default values for precision vary according to the data type to which they are applied. For details the individual data type descriptions in this table. |
schemaObjectName |
Required internal Teradata PT metadata name of the defined schema object. |
SMALLINT |
Optional keyword specifying a two-byte integer numeric data type. |
sqlSelectStatement |
An SQL SELECT statement whose result table columns are the basis for a Teradata PT-generated schema. |
tableName |
The name of a DBS table whose column definitions Teradata PT uses as the basis for generating: |
TIME |
Optional keyword specifying a column whose values are time values. precision represents the number of digits to the right of the decimal point, indicating a decimal fraction of a second. Valid values for precision are 0 through 6. The default value is 6. Note: In the 4 time-oriented column types - TIME, TIME WITH TIME ZONE, TIMESTAMP and TIMESTAMP WITH TIME ZONE - the default precision for the fractional seconds is 6, not zero. Thus TIME and TIME(6) represent identical columns, and you must specify TIME(0) if the time values will have no fractional seconds. External to Teradata Database, the values of these 4 time-oriented columns are represented by character strings of well-defined lengths and formats. Teradata PT, however, has no knowledge of these lengths or formats, and cannot verify the validity of job script character strings intended to represent values of these time-oriented columns. If a script literal string is a valid representation of the value of one of these column types, then all the comparison operators will correctly compare the value to row values of a column of exactly the same time-oriented type. Similarly, all comparison operators will correctly compare the row values of two columns of the same type. |
TIME WITH TIME ZONE TIME(precision) WITH TIME ZONE |
Optional keyword specifying a column containing time values that include a time zone offset. precision represents the number of digits to the right of the decimal point; a decimal fraction of a second. Valid values for precision are 0 through 6. The default value is 6. See Note for TIME. |
TIMESTAMP TIMESTAMP(precision) |
Optional keyword specifying a column containing timestamp values. The precision represents the number of digits to the right of the decimal point; a decimal fraction of a second. Valid values are 0 through 6. The default value is 6. See Note for TIME. |
TIMESTAMP WITH TIME ZONE TIMESTAMP(precision) WITH TIME ZONE |
Optional keyword specifying a column containing timestamp values that include a time zone offset. precision represents the number of digits to the right of the decimal point, a decimal fraction of a second. Valid values are 0 through 6. The default value is 6. See Note for TIME. |
VARBYTE VARBYTE(lengthBytes) |
Optional keyword specifying a variable-length column containing binary data. lengthBytes specifies the maximum column length in bytes. If not specified, the default column length is 64, 000 bytes, which is the maximum valid length. VARCHAR and VARBYTE data with a length less than or equal to the defined length in the DEFINE SCHEMA definition is allowed. |
VARCHAR VARCHAR(lengthBytes) |
Optional keyword specifying a variable-length column containing character data. lengthBytes specifies the maximum column length in bytes. If not specified, the default column length is 64, 000 bytes, which is the maximum valid length. |
VARDATE(lengthBytes) FORMATIN(formatString) FORMATOUT(formatString) |
Optional keyword specifying a varying length character-based column whose data is to be reformatted before being loaded into a Teradata DateTime column. The FORMATIN string matches the format of the incoming DateTime data. The FORMATOUT string matches the format accepted by the Teradata DateTime column into which data is being loaded. lengthBytes must be greater than or equal to the size of the larger format string. See “Using VARDATE Columns to Reformat DateTime Data” inTeradata Parallel Transporter User Guide for information on, and examples of, reformatting DateTime data using the VARDATE column data type. |
VARGRAPHIC VARGRAPHIC(lengthBytes) |
Optional keyword specifying a variable-length column containing 2-byte graphic characters. lengthBytes specifies the column length in 2-byte graphic characters. If not specified, the default length is 32, 000 characters, which is the maximum valid length. The byte length of VARGRAPHIC column is twice the value of lengthChars. |
XML XML AS DEFERRED BY NAME |
Optional keyword specifying an inline XML column whose data is stored in the data rows defined by the schema just as non-XML columns data is. Teradata PT defaults to size 64,000 bytes for Inline XML columns. The DataConnector operator reads or writes XML data from or to an external file. The SQL Selector operator extracts XML data from Teradata Database and puts it into data streams. The SQL Inserter operator reads data from the data stream and loads it into Teradata Database. Note: An 8-byte length indicator precedes the inline XML data if data rows are coming from a file read by a DataConnector operator. Other operators (Export, Load, Update and Stream) cannot process XML data. AS DEFERRED BY NAME Option specifying a deferred XML column whose data is contained in files rather than in the data rows defined by a Teradata PT schema. Teradata PT defaults to size 2, 000, 000, 000 bytes for deferred XML columns. The option AS DEFERRED BY NAME, which specifies a Teradata PT XML file identifier, means that XML data is deferred and sent to the Teradata Database separately from non-XML data. The names of the files that contain deferred XML data are stored in data rows exactly like VARCHAR column values, with a 2-byte length prefix. The row value is either a fully qualified path name or the name of a file that Teradata PT looks for in the directory out of which it executes. SQL Selector operator is the only producer operator that can extract XML data from Teradata Database. The SQL Inserter operator is the only consumer operator that can load deferred XML files into Teradata Database. Other operators (Export, Load, Update and Stream) cannot process XML data. Note: Teradata PT does not allow an XML column to be used in any predicate evaluation specified in the WHERE clause or in either type of CASE expression of an SQL request. |