As examples two and three in the previous section show, Teradata PT accepts the name of a database table as a stand-in for a Teradata PT schema that it will generate from the column descriptions of the table. Such generated schemas can be a major convenience when the number of schema columns is large, reducing keyboarding time and keystroke errors and ultimately enabling job scripts to be simpler to write.
To generate a Teradata PT DEFINE SCHEMA statement from a database table, Teradata PT makes a HELP TABLE call to the database to get the descriptions of the table's columns, and constructs a DEFINE SCHEMA statement, with a generated schema name, for all script invocations of the template operator that specify their schema via this table name. Teradata PT then uses that generated name in the copy of the producer template definition that it imports into the script, for all references to this particular template that share the same schema.
Example 1
Suppose the database table Invoice_Counts has 4 columns of the four integer types. Its Teradata PT-constructed DEFINE SCHEMA statement would be as follows:
DEFINE SCHEMA $SCHEMA_GEN_TBL001 DESCRIPTION 'SOURCE INFORMATION SCHEMA' ( "IC1" BYTEINT, "IC2" SMALLINT, "IC4" INTEGER, "IC8" BIGINT );
and the copy of any producer template imported into the script that specified its schema via the table name 'Invoice_Counts' would have:
SCHEMA $SCHEMA_GEN_TBL001
as its schema specification. Notice that the generated schema name incorporates a sequence number ('001' in this example) that enumerates the number of schemas generated for any job script. Notice also that the generated schema column names are enclosed in double quotes to avoid any potential conflicts with Teradata PT reserved words.
If our example database table Invoice_Counts, whose generated schema is shown above, were to be the target table for a delimited data file source, then the job script would require the delimited-file-format version of the schema; it can be requested by the DELIMITED keyword, as shown in the third producer template reference example above, and again here:
... FROM OPERATOR( $FILE_READER( DELIMITED 'Invoice_Counts' ) ) ...
In this case, all columns in the producer’s generated schema are type VARCHAR to match the character format of the data, regardless of how the target columns are defined in the database target table. (The database performs all necessary data conversions from VARCHAR to non-character data type representations at loading time.) With the DELIMITED keyword present, as shown the above example, Teradata PT would generate the following delimited-file-format version of the schema for table Invoice_Counts:
DEFINE SCHEMA $SCHEMA_GEN_D_TBL003 DESCRIPTION 'SOURCE INFORMATION SCHEMA' ( "IC1" VARCHAR(4), "IC2" VARCHAR(6), "IC4" VARCHAR(11), "IC8" VARCHAR(20) );
Notice that the generated schema name contains an "_D" appended to it so as to distinguish it from the name of the non-delimited version of the schema, in case a job script might need both versions of the schema.
Example 2
Teradata PT generates a schema from a database table in one additional script context as illustrated by the following two DEFINE SCHEMA statements:
DEFINE SCHEMA TODAYS_TRANSACTIONS FROM TABLE 'Daily_Trans'; DEFINE SCHEMA INVOICE_COUNTS FROM TABLE DELIMITED 'Invoice_Counts';
Teradata PT generates a DEFINE SCHEMA statement based on the column descriptions of the identified database table and substitute it in the script for the original, abbreviated DEFINE SCHEMA statement.
The only difference from schemas generated from producer template references is that the generated schema has the name supplied in the original DEFINE SCHEMA statement rather than a generated name: TODAYS_TRANSACTIONS and INVOICE_COUNTS, respectively, in the above two examples. There is no functional difference between a schema defined using a database table and a schema defined by a fully-coded DEFINE SCHEMA statement.