Sunday, 10 November 2013

8.3 Teradata Parallel Transporter - SQL selector operator Example

SQL selector Operator Example:

Example:  This script export records from the employee table in field mode and writes it to a delimited file.
Script:

DEFINE JOB EXPORT_EMPLOYEE_TABLE_TO_FILE
DESCRIPTION 'EXPORT SAMPLE EMPLOYEE TABLE TO A FILE'
(

/*****************************/
DEFINE SCHEMA EMPLOYEE_SCHEMA
DESCRIPTION 'SAMPLE EMPLOYEE SCHEMA'
(
EMP_ID   VARCHAR(11),
EMP_NAME VARCHAR(30)
);
/*For delimited file all the columns in schema should be defined as varchar*/
/*****************************/

/*****************************/
DEFINE OPERATOR FILE_WRITER()
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DATA CONNECTOR OPERATOR'
TYPE DATACONNECTOR CONSUMER
SCHEMA *
ATTRIBUTES
(
VARCHAR PrivateLogName    = 'file_writer_privatelog',
VARCHAR FileName          = 'output.txt',
VARCHAR IndicatorMode     = 'N',
VARCHAR OpenMode          = 'Write',
VARCHAR Format            = 'DELIMITED'
);
/*****************************/

/*****************************/
DEFINE OPERATOR SELECTOR_OPERATOR()
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER SQL SELECTOR OPERATOR'
TYPE SELECTOR
SCHEMA EMPLOYEE_SCHEMA
ATTRIBUTES
(
VARCHAR PrivateLogName    = 'selector_privatelog',
VARCHAR DATAENCRYPTION = 'ON',
VARCHAR QueryBandSessInfo ='developer=sukul;',
VARCHAR WORKINGDATABASe= 'EDW_RESTORE_TABLES',
VARCHAR TdpId             = 'Prd.Database.com',
VARCHAR UserName          = 'sukul',
VARCHAR UserPassword      = 'mysilllypwd',
VARCHAR ReportModeOn = 'YES',
VARCHAR AccountId,
VARCHAR SelectStmt        = 'SELECT EMP_ID,EMP_NAME FROM SOURCE_EMP_TABLE;'
);
/*****************************/
/* we have used the reportmodeon. So when selecting the columns we really don’t need to cast columns to CHAR or VARCHAR*/

STEP export_to_file
(
APPLY TO OPERATOR (FILE_WRITER() )
SELECT * FROM OPERATOR (SELECTOR_OPERATOR());
);
);

Log:

Teradata Parallel Transporter Version 13.10.00.05
Job log: /opt/teradata/client/13.10/tbuild/logs/sukul-30.out
Job id is sukul-30, running on testunixbox
Teradata Parallel Transporter DataConnector Version 13.10.00.05
FILE_WRITER Instance 1 directing private log report to 'file_writer_privatelog-1'.
Teradata Parallel Transporter SQL Selector Operator Version 13.10.00.05
SELECTOR_OPERATOR: private log specified: selector_privatelog
FILE_WRITER: TPT19007 DataConnector Consumer operator Instances: 1
FILE_WRITER: TPT19003 ECI operator ID: FILE_WRITER-28234
FILE_WRITER: TPT19222 Operator instance 1 processing file 'output.txt'.
SELECTOR_OPERATOR: connecting sessions
SELECTOR_OPERATOR: sending SELECT request
SELECTOR_OPERATOR: retrieving data
SELECTOR_OPERATOR: Total Rows Exported:  3
SELECTOR_OPERATOR: finished retrieving data
FILE_WRITER: TPT19221 Total files processed: 1.
SELECTOR_OPERATOR: disconnecting sessions
SELECTOR_OPERATOR: Total processor time used = '0.14 Second(s)'
SELECTOR_OPERATOR: Start : Sun Nov 10 03:03:14 2013
SELECTOR_OPERATOR: End   : Sun Nov 10 03:03:19 2013
Job step export_to_file completed successfully
Job sukul completed successfully

Private Log:

 tlogview -l /opt/teradata/client/13.10/tbuild/logs/sukul-30.out -f selector_privatelog

     ===================================================================
     =                                                                 =
     =                  TERADATA PARALLEL TRANSPORTER                  =
     =                                                                 =
     =          SQL SELECTOR OPERATOR     VERSION 13.10.00.05          =
     =                                                                 =
     =          OPERATOR SUPPORT LIBRARY VERSION 13.10.00.04           =
     =                                                                 =
     = COPYRIGHT 2001-2010, TERADATA CORPORATION. ALL RIGHTS RESERVED. =
     =                                                                 =
     ===================================================================

**** 03:03:14 Processing starting at: Sun Nov 10 03:03:14 2013

     ===================================================================
     =                                                                 =
     =                      Module Identification                      =
     =                                                                 =
     ===================================================================

     SQL Selector Operator for HP-UX release B.11.31 on testunixbox
     SelMain    : 13.10.00.03
     SelCLI     : 13.00.00.01
     SelUtil    : 13.10.00.06
     PcomCLI    : 13.10.00.15
     PcomMBCS   : 13.00.00.02
     PcomMsgs   : 13.01.00.07
     PcomNtfy   : 13.00.00.03
     PcomPx     : 13.10.00.16
     PcomUtil   : 13.10.00.07
     PXICU      : 13.10.00.04
     TDICU      : 13.10.00.03
     CLIv2      : 13.10.00.04

     ===================================================================
     =                                                                 =
     =                   Control Session Connection                    =
     =                                                                 =
     ===================================================================

**** 03:03:14 Connecting to RDBMS:    'Prd.Database.com'
**** 03:03:14 Connecting with UserId: 'sukul'
**** 03:03:14 This job is submitting the following request:

     SET QUERY_BAND = 'developer=sukul;' FOR SESSION;


     ===================================================================
     =                                                                 =
     =                  Teradata Database Information                  =
     =                                                                 =
     ===================================================================

**** 03:03:14 Teradata Database Version:      '13.10.07.14                     '
**** 03:03:14 Teradata Database Release:      '13.10.07.02                   '
**** 03:03:14 Maximum request size supported: 1MB
**** 03:03:14 Session character set:          'ASCII'
**** 03:03:14 Data Encryption:                supported
**** 03:03:14 Date format:   INTEGERDATE
**** 03:03:14 Maximum response buffer length: 1048472 bytes
**** 03:03:15 Current working DATABASE set:   'EDW_RESTORE_TABLES'

     ===================================================================
     =                                                                 =
     =                         Select Request                          =
     =                                                                 =
     ===================================================================

**** 03:03:15 This job is submitting the following request:

     SELECT EMP_ID,EMP_NAME FROM SOURCE_EMP_TABLE;

**** 03:03:15 Getting information on data returned by SELECT request

     ===================================================================
     =                                                                 =
     =                     Column/Field Definition                     =
     =                                                                 =
     ===================================================================

     Column Name                    Offset Length Type
     ============================== ====== ====== ========================
     EMP_ID                              0     11 VARCHAR
     EMP_NAME                           13     30 VARCHAR
     ============================== ====== ====== ========================
     INDICATOR BYTES NEEDED: 1
     EXPECTED RECORD LENGTH: 46

     ===================================================================
     =                                                                 =
     =                         Data Retrieval                          =
     =                                                                 =
     ===================================================================

**** 03:03:16 3 row(s) returned
**** 03:03:16 Starting to retrieve data from the RDBMS

     ===================================================================
     =                                                                 =
     =                          Ending Phase                           =
     =                                                                 =
     ===================================================================

**** 03:03:16 Finished retrieving rows from the RDBMS

     Total Records Exported From RDBMS    =  3

     ===================================================================
     =                                                                 =
     =                        Logoff/Disconnect                        =
     =                                                                 =
     ===================================================================

**** 03:03:19 Logging off all sessions

              Instance      Cpu Time
              ========  ================
                   1        0.14 Seconds

**** 03:03:19 Total processor time used = '0.14 Second(s)'
     .        Start : Sun Nov 10 03:03:14 2013
     .        End   : Sun Nov 10 03:03:19 2013
     .        Highest return code encountered = '0'.
**** 03:03:19 This job terminated

Output File:

more output.txt
          2|uma
         33|bhanu
          4|chettri



No comments:

Post a comment