Teradata PT employs a single scripting language for extracting and loading data Teradata also provides:
- A simplified version of its scripting language, called “Simplicity,” that uses pre-defined reusable operator templates easy to maintain. Using pre-defined operator templates vastly reduces the number of lines of code you have to write. Job schemas are generated dynamically based on the source or target table. For information on simplified Teradata PT scripts, see Simplifying Scripts with Operator Templates and Generated Schemas.
- A utility called Easy Loader that allows you to run a Teradata PT load job using a command line interface. This makes it unnecessary to write a job script. For information on Easy Loader, see Teradata PT Easy Loader.
Using the Simplicity Job Script
Simplicity features include operator templates that are imported into the job script when operators are referenced in an APPLY/SELECT statement, as shown in the following code excerpt:
APPLY 'INSERT INTO TABLE_X (:col1, :col2);' TO OPERATOR ($LOAD) SELECT * FROM OPERATOR ($FILE_READER);
By referencing the $LOAD and $FILE_READER operator template names, Teradata PT knows the Load template to import into a job script. If reducing the amount of code for loading or exporting and simplifying job maintenance is important to you, use the simplified job script method.
The following two examples illustrate loading a delimited file into a Teradata table:
Example 1: Job Script with Simplified Syntax
DEFINE JOB PLOAD_JOB DESCRIPTION 'PLOAD JOB' ( /* Use the schema of the TargetTable for TPT_SCHEMA */ DEFINE SCHEMA TPT_SCHEMA FROM TABLE DELIMITED @LoadTargetTable; APPLY $INSERT @LoadTargetTable TO OPERATOR ($LOAD [@LoadInstances]) SELECT * FROM OPERATOR ($FILE_READER(TPT_SCHEMA) [@ReaderInstances]); );
Example 2: Job Script without Simplified Syntax
DEFINE JOB PLOAD_JOB DESCRIPTION 'PLOAD JOB' ( DEFINE SCHEMA TPT_SCHEMA DESCRIPTION 'TPT SCHEMA' ( COL001 VARCHAR(100), COL002 VARCHAR(100), COL003 VARCHAR(100) ); DEFINE OPERATOR LOAD_OPERATOR DESCRIPTION 'TPT Load Operator' TYPE LOAD SCHEMA * ATTRIBUTES ( VARCHAR TdpId = @LoadTdpId, VARCHAR UserName = @LoadUserName, VARCHAR UserPassword = @LoadUserPassword, VARCHAR TargetTable = @LoadTargetTable, VARCHAR LogTable = @LoadLogTable, VARCHAR ErrorTable1 = @LoadErrorTable1, VARCHAR ErrorTable2 = @LoadErrorTable2, VARCHAR PrivateLogName = @LoadPrivateLogName ); DEFINE OPERATOR FILE_READER_OPERATOR DESCRIPTION 'TPT DataConnector Producer Operator' TYPE DATACONNECTOR PRODUCER SCHEMA TPT_SCHEMA ATTRIBUTES ( VARCHAR FileName = @FileReaderFileName, VARCHAR Format = @FileReaderFormat, VARCHAR OpenMode = @FileReaderOpenMode, VARCHAR TextDelimiter = @FileReaderTextDelimiter, VARCHAR MultipleReaders = @FileReaderMultipleReaders, VARCHAR PrivateLogName = @FileReaderPrivateLogName ); APPLY ( 'INS ' || @LoadTargetTable || ' ( :COL001, :COL002, :COL003 );' TO OPERATOR (LOAD_OPERATOR [@LoadInstances]) SELECT * FROM OPERATOR ( FILE_READER_OPERATOR [@ReaderInstances]); );
As Example 1 above shows (Job Script with Simplified Syntax), using templates eliminates the need to write DEFINE OPERATOR statements for operators in the job script itself. The reference to the job variable @LoadTargetTable in the DEFINE SCHEMA statement allows Teradata PT to generate the job schema at runtime based on the value provided for the job variable.
The Value of Using a Job Variables File
Using job variables defined in a job variables file allows the job script compiler to assign values to job variables at script execution time rather than having to have you code values as constants in every script. Each job variable in a script should be prefixed with an @ sign so that the script compiler replaces each variable with the corresponding value when the Teradata PT job executes.
Teradata PT allows unlimited variable substitution in a script, maximizing the reusability of scripts across systems. Moreover, using a job variables file allows multiple jobs to share a common set of variable values.
For information on a job variables file, see Setting Up the Job Variables Files.
The Advantages of a Simplicity Script
- Smaller and simpler job scripts
- Less job script maintenance with the use of operator templates
- Scripts that can be customized by using user-defined templates
- Generated schema objects
- Generated SQL insert statements
- Standardized job variable names corresponding to operator attributes