TPT Selector Operator

Pradeep

TPT Selector Operator

TPT Selector is producer operator which has features on available in Export operator, this can be used to export LOB columns as well.

TPT Selector Characteristics:

'TYPE' of the operator should be defined as SELECTOR.
Can be used to extract low volumes of data from a table on the Teradata Database.
SELECTOR Operator can use only single teradata session.
Only TPT operator that can export LOB,JSON and XML data types from Teradata.
Selector operator can export rows from multiple tables.
Multiple instances are not supported in SELECT Operator, if specified then it will fail with below error.
```
Producer_Query_Detail: TPT10327: Multiple instances are not supported
```
SELECTOR TPT jobs are not restartable if the data export has started and the data export has not completed.
Below attributes can be used to instruct Teradata where to create file corresponding to each clob row. If not specified, teradata will create in same directory as data file.
1. VARCHAR LobDirectoryPath='/Users/username/Desktop/tpt/clob_data'
2. VARCHAR LobFileBaseName='clob_data_test'
3. VARCHAR LobFileExtension='out'
Five file formats are supported for export: Delimited, Text, Unformatted, Formatted and Binary. For detail of each file format, please click here

Example 1: Simple Selector Operator example

 DEFINE JOB EXPORT_EMPLOYEE_DATA
  DESCRIPTION 'Exports employee data to a delimited file using Selector'
  (
      DEFINE OPERATOR Consumer_File_Detail
      DESCRIPTION 'Defining a consumer operator for storing retrieved data to a file'
      TYPE DATACONNECTOR CONSUMER
      SCHEMA *
      ATTRIBUTES
      (
          VARCHAR FileName='/Users/username/Desktop/emp_selector_delimited.out',
          VARCHAR FORMAT= 'DELIMITED',
          VARCHAR TextDelimiter= '|~|',
          VARCHAR OpenMode='Write'
      );
      
      DEFINE SCHEMA Define_Employee_Schema
      DESCRIPTION 'Defining a Schema to describe the structure of the output file'
      (
           emp_no INTEGER,
           ename_name VARCHAR(50),
           hire_date ANSIDATE
      );
      
      DEFINE OPERATOR Selector_operator
      TYPE SELECTOR
      SCHEMA Define_Employee_Schema
      ATTRIBUTES
      (
        VARCHAR TdPid='TDServer',
        VARCHAR UserName='tutorial_user',
        VARCHAR UserPassWord='tutorial_password',
        VARCHAR SelectStmt = 'SELECT emp_no,ename_name,hire_date FROM tutorial_db.emp_unformatted;',
        VARCHAR DateForm='AnsiDate'
      );
      
      APPLY 
      TO OPERATOR( Consumer_File_Detail )
      SELECT * FROM OPERATOR( Selector_operator[1] );
  );

Example2: Export table with CLOB column.

 DEFINE JOB EXPORT_EMPLOYEE_DATA
  DESCRIPTION 'Exports employee data to a formatted file using EXPORT'
  (
      DEFINE OPERATOR Consumer_File_Detail
      DESCRIPTION 'Defining a consumer operator for storing retrieved data to a file'
      TYPE DATACONNECTOR CONSUMER
      SCHEMA *
      ATTRIBUTES
      (
          VARCHAR FileName='/Users/username/Desktop/emp_selector_delimited_clob.out',
          VARCHAR FORMAT= 'DELIMITED',
          VARCHAR TextDelimiter= '|~|',
          VARCHAR OpenMode='Write'
      );
      
      DEFINE SCHEMA Define_Employee_Schema
      DESCRIPTION 'Defining a Schema to describe the structure of the output file'
      (
           emp_no INTEGER,
           ename_name CLOB AS DEFERRED BY NAME,
           hire_date ANSIDATE
      );
      
      DEFINE OPERATOR Selector_operator
      TYPE SELECTOR
      SCHEMA Define_Employee_Schema
      ATTRIBUTES
      (
        VARCHAR TdPid='TDServer',
        VARCHAR UserName='tutorial_user',
        VARCHAR UserPassWord='tutorial_password',
        VARCHAR SelectStmt = 'SELECT emp_no,CAST(ename_name AS CLOB),hire_date FROM tutorial_db.emp_formatted_clob;',
        INTEGER MaxSessions=6,
        INTEGER minsessions=3,
        VARCHAR DateForm='AnsiDate'
      );
      
      APPLY 
      TO OPERATOR( Consumer_File_Detail )
      SELECT * FROM OPERATOR( Selector_operator[1] );
  );

Points to be Noted:

In the 'example 2' above, 'Producer_Query_Detail' is the place where Operator TPT SELECTOR 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 create files corresponding to each clob row.

Outfile will hold path of each of the file generated with LOB data at the specified schema position. Example, path for 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 Selector

Example 1: Executing simple TPT Selector script
```
tbuild -f tpt_selector.tpt
```
Example 2: Executing CLOB TPT Selector script
```
tbuild -f tpt_selector_clob.tpt
```

Download script files