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. 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 fullyqualified 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 CHARS CHARACTER CHARACTERS CHAR (lengthBytes) CHARS(lengthBytes) CHARACTER(lengthBytes) CHARACTERS(lengthBytes) |
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 CHARS VARYING CHARACTER VARYING CHARACTERS VARYING CHAR VARYING(lengthBytes) CHARS VARYING(lengthBytes) CHARACTER VARYING(lengthBytes) CHARACTERS VARYING(lengthBytes) |
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.
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 INT |
Optional keyword specifying a four-byte integer data 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. 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,
see SQL Data Types and Literals
(B035-1143).
|
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 the following is
true:
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. 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. 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 as
follows:
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. The “metadata(filename)” feature
has been improved to support all record types. Both binary and
text file formats are supported. See examples 6 and 7 in Examples of DEFINE SCHEMA Statements.
|
NUMBER NUMBER(*) NUMBER(*,scale) NUMBER(precision) NUMBER(precision,scale) |
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:
|
PERIOD(DATE) | Optional keyword specifying a column whose
values represent a duration of time, consisting of a beginning and
ending date value. 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, see SQL Data Types and Literals (B035-1143). |
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) WITH TIME ZONE) |
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. If there are no operators defined in the script, then the operatorName can be the name of an operator template specified in the APPLY-SELECT statement. |
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 PTgenerated schema. |
tableName | The name of a DBS table whose column definitions
Teradata PT uses as the basis for generating:
|
TIME TIME(precision) |
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. 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. |
USINGEXTENSION (‘usingExtension’) | Optional keyword specifying the USING clause
extension for this schema column. For data loading, the operator generates the USING clause based on the schema. The operator sends the USING clause to the Teradata Database. The USING clause identifies the layout of the data rows. The operator adds the UsingExtension value to the column when generating the USING clause. The operator does not validate the UsingExtension value. The UsingExtension value will be validated by the Teradata Database. The USINGEXTENSION option applies only to the Teradata PT Update operator when the job is using the extended MultiLoad protocol. The USINGEXTENSION option is ignored when the job is using the traditional MultiLoad protocol. Also, the USINGEXTENSION option is ignored for other Teradata PT operators. The USINGEXTENSION option can be used to load data into a temporal table using the Teradata PT Update operator. For more information on how to use the USINGEXTENSION option, see Temporal Tables. |
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” in the Teradata Parallel Transporter User Guide (B035-2445) 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. 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 Inserts. Other operators (Export, Load, Update and Stream) cannot process XML data. 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.
|