TPT Inserter Operator

Pradeep

TPT Inserter Operator

TPT Selector is consumer operator which has features on available in Stream and Load operators, this can be used to import LOB columns as well.

TPT Inserter Characteristics:

'TYPE' of the operator should be defined as INSERTER.
Can be used to import low volumes of data to a table in the Teradata Database.
INSERTER Operator can use multiple teradata session. If multiple sessions are used and duplicates are present for primary index values then it could result in deadlock 2631 error.
Only TPT operator that can import LOB,JSON and XML data types from Teradata.
INSERTER operator can import rows to multiple tables.
Multiple instances are supported in INSERTER Operator. Each instance can utilize 1 or more session depending on the max sessions defined using MaxSessions attribute.
Five file formats are supported for import: Delimited, Text, Unformatted, Formatted and Binary. For detail of each file format, please click here

Example 1: Simple Inserter Operator example

DEFINE JOB Import_Employee_Data
Description 'Test Inserter script'
(
    DEFINE OPERATOR Inserter_operator
    TYPE INSERTER
    SCHEMA *
    ATTRIBUTES
    (
        VARCHAR TdPid='TDServer',
        VARCHAR UserName='tutorial_user',
        VARCHAR UserPassWord='tutorial_password',
        VARCHAR TargetTable='emp_unformatted',
        VARCHAR LogTable='dept_tpt_inserter_log',
        VARCHAR DateForm='AnsiDate',
        INTEGER MaxSessions=6,
        INTEGER MinSessions=3
    );
    
    DEFINE SCHEMA Define_Employee_Schema
    (
        emp_no VARCHAR(11),
        ename_name VARCHAR(50),
        hire_date VARCHAR(10)
    );

    DEFINE OPERATOR Producer_File_Detail
    TYPE DATACONNECTOR PRODUCER
    SCHEMA Define_Employee_Schema
    ATTRIBUTES
    (
        VARCHAR DirectoryPath='/Users/username/Desktop',
        VARCHAR FileName='emp_selector_delimited.out',
        VARCHAR TextDelimiter='|~|',
        VARCHAR Format='Delimited',
        VARCHAR OpenMode='Read',
        VARCHAR IndicatorMode='N',
        VARCHAR DateForm='AnsiDate'
    );
    
    APPLY
    (
       'INSERT INTO tutorial_db.emp_unformatted(emp_no,ename_name,hire_date) VALUES (:emp_no,:ename_name,:hire_date);'
    )
    TO OPERATOR(Inserter_operator)
    
    SELECT * FROM OPERATOR (Producer_File_Detail);
);

Example2: Export table with CLOB column.

DEFINE JOB Import_Employee_Data
Description 'Test Inserter script'
(
    DEFINE OPERATOR Inserter_operator
    TYPE INSERTER
    SCHEMA *
    ATTRIBUTES
    (
        VARCHAR TdPid='TDServer',
        VARCHAR UserName='tutorial_user',
        VARCHAR UserPassWord='tutorial_password',
        VARCHAR TargetTable='emp_unformatted',
        VARCHAR LogTable='dept_tpt_inserter_log',
        VARCHAR DateForm='AnsiDate',
        INTEGER MaxSessions=6,
        INTEGER MinSessions=3
    );
    
    DEFINE SCHEMA Define_Employee_Schema
    (
        emp_no VARCHAR(11),
        ename_name CLOB AS DEFERRED BY NAME,
        hire_date VARCHAR(10)
    );

    DEFINE OPERATOR Producer_File_Detail
    TYPE DATACONNECTOR PRODUCER
    SCHEMA Define_Employee_Schema
    ATTRIBUTES
    (
        VARCHAR DirectoryPath='/Users/username/Desktop',
        VARCHAR FileName='emp_selector_delimited_clob.out',
        VARCHAR TextDelimiter='|~|',
        VARCHAR Format='Delimited',
        VARCHAR OpenMode='Read',
        VARCHAR IndicatorMode='N',
        VARCHAR DateForm='AnsiDate'
    );
    
    APPLY
    (
       'INSERT INTO tutorial_db.emp_formatted_clob(emp_no,ename_name,hire_date) VALUES (:emp_no,:ename_name,:hire_date);'
    )
    TO OPERATOR(Inserter_operator)
    
    SELECT * FROM OPERATOR (Producer_File_Detail);
);

Points to be Noted:

In the 'example 2' above, 'Consumer_Table_Detail' is the place where Operator TPT INSERTER is defined.

Column datatype has to defined with 'CLOB/BLOB AS DEFERRED BY NAME' for LOB datatypes in schema definition. If not defined then it will fail with below error

TPT_INFRA: TPT02638: Error: Conflicting data length for column(2) - ename_name. Source column's data length (64000) Target column's data length (2097088000).

Teradata need files corresponding to each clob row.

Outfile should hold path of each of the file with LOB data at the specified schema position. Example, path for below outfile existed at 2nd position as in schema.

1000262|~|/Users/username/clob_data/clob_data_test_ename_name_pradeep.khatri-123_p1_r1.out|~|1981-06-09
1000294|~|/Users/username/clob_data/clob_data_test_ename_name_pradeep.khatri-123_p1_r2.out|~|1980-12-17

Executing TPT Inserter

Example 1: Executing simple TPT Inserter script
```
tbuild -f tpt_inserter_delimited.tpt
```
Example 2: Executing CLOB TPT Inserter script
```
tbuild -f tpt_inserter_delimited_clob.tpt
```

Download script files