7.3 Teradata Parallel Transporter - Export Operator - 2 Examples

Example 1 :

Following Example uses Export Operator to Export data from table and write it in 'formatted' mode to an output file.

Job 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   CHAR(10), EMP_NAME CHAR(30) ); /*****************************/ /*****************************/ 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            = 'formatted' ); /*****************************/ /*****************************/ DEFINE OPERATOR EXPORT_OPERATOR() DESCRIPTION 'TERADATA PARALLEL TRANSPORTER EXPORT OPERATOR' TYPE EXPORT SCHEMA EMPLOYEE_SCHEMA ATTRIBUTES ( VARCHAR PrivateLogName    = 'export_privatelog', INTEGER MaxSessions       =  32, INTEGER MinSessions       =  1, VARCHAR TdpId             = 'Prd.Database.com', VARCHAR UserName          = 'sukul', VARCHAR UserPassword      = 'mysillypwd', VARCHAR AccountId, VARCHAR SelectStmt        = 'SELECT CAST(EMP_ID AS CHAR(10)),EMP_NAME(CHAR(30)) FROM EDW_RESTORE_TABLES.SOURCE_EMP_TABLE;' ); /*****************************/ STEP export_to_file ( APPLY TO OPERATOR (FILE_WRITER() ) SELECT * FROM OPERATOR (EXPORT_OPERATOR() [1] ); ); );

Public Log: Following is the log after running the command tbuild -f tpttest4 Teradata Parallel Transporter Version 13.10.00.05 Job log: /opt/teradata/client/13.10/tbuild/logs/sukul-17.out Job id is sukul-17, running on testunixbox Teradata Parallel Transporter Export Operator Version 13.10.00.04 EXPORT_OPERATOR: private log specified: export_privatelog Teradata Parallel Transporter DataConnector Version 13.10.00.05 FILE_WRITER Instance 1 directing private log report to 'file_writer_privatelog-1'. FILE_WRITER: TPT19007 DataConnector Consumer operator Instances: 1 FILE_WRITER: TPT19003 ECI operator ID: FILE_WRITER-16577 FILE_WRITER: TPT19222 Operator instance 1 processing file 'output.txt'. EXPORT_OPERATOR: connecting sessions EXPORT_OPERATOR: sending SELECT request EXPORT_OPERATOR: entering End Export Phase EXPORT_OPERATOR: Total Rows Exported:  3 FILE_WRITER: TPT19221 Total files processed: 1. EXPORT_OPERATOR: disconnecting sessions EXPORT_OPERATOR: Total processor time used = '1.51 Second(s)' EXPORT_OPERATOR: Start : Sat Nov  9 10:34:01 2013 EXPORT_OPERATOR: End   : Sat Nov  9 10:34:15 2013 Job step export_to_file completed successfully Job sukul completed successfully

Following is the result of the script :   more output.txt (2         uma (33        bhanu (4         chettri Note the ( before each line. This happens because with formatted mode a length is written before each line which gets represented as ( Ascii value of ( is 40. 40 is the length of each line. We can remove this ( by making use of 'text' mode for writing the file. But remember that for this all the column in the schema should be defined as CHAR

Example 2:

 

Following example is similar to the above one but we have added more attributes to the script.

Job 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   CHAR(10), EMP_NAME CHAR(30) ); /*****************************/ /*****************************/ 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            = 'text' ); /*****************************/ /*****************************/ DEFINE OPERATOR EXPORT_OPERATOR() DESCRIPTION 'TERADATA PARALLEL TRANSPORTER EXPORT OPERATOR' TYPE EXPORT SCHEMA EMPLOYEE_SCHEMA ATTRIBUTES ( VARCHAR PrivateLogName    = 'export_privatelog', INTEGER BLOCKSIZE = 300, VARCHAR DATAENCRYPTION = 'ON', /*Data encryption indicates that all the data and requests would be passed to the Db with encryption*/ INTEGER OUTLIMIT = 1, /* OUTLIMIT applies for each instance. If we use 2 instances of export operator we would actually export 2 rows */ VARCHAR QueryBandSessInfo ='developer=sukul;', /*We don’t write the actual query band statement, instead we just write the query banding expression in name and value pairs. Note that each pair should be separated by ; and should end with ;*/ VARCHAR SLEEPMODE = 'NOSPOOL', VARCHAR WORKINGDATABASe= 'EDW_RESTORE_TABLES', /* This is same as the DATABASE command to set the default database*/ INTEGER MaxSessions       =  32, INTEGER MinSessions       =  1, VARCHAR TdpId             = 'Prd.Database.com', VARCHAR UserName          = 'sukul', VARCHAR UserPassword      = 'mysillypwd', VARCHAR AccountId, VARCHAR SelectStmt        = 'SELECT CAST(EMP_ID AS CHAR(10)),EMP_NAME(CHAR(30)) FROM SOURCE_EMP_TABLE;' /* As we have used WORKINGDATABASE we have not qualified the table name */ ); /*****************************/ STEP export_to_file ( APPLY TO OPERATOR (FILE_WRITER() ) SELECT * FROM OPERATOR (EXPORT_OPERATOR() [2] ); /* We have used 2 instance of EXPORT operator*/ ); );

Log:   tbuild -f tpttest4 Teradata Parallel Transporter Version 13.10.00.05 Job log: /opt/teradata/client/13.10/tbuild/logs/sukul-25.out Job id is sukul-25, running on testunixbox Teradata Parallel Transporter Export Operator Version 13.10.00.04 EXPORT_OPERATOR: private log specified: export_privatelog Teradata Parallel Transporter DataConnector Version 13.10.00.05 FILE_WRITER Instance 1 directing private log report to 'file_writer_privatelog-1'. FILE_WRITER: TPT19007 DataConnector Consumer operator Instances: 1 FILE_WRITER: TPT19003 ECI operator ID: FILE_WRITER-677 FILE_WRITER: TPT19222 Operator instance 1 processing file 'output.txt'. EXPORT_OPERATOR: connecting sessions EXPORT_OPERATOR: sending SELECT request EXPORT_OPERATOR: entering End Export Phase EXPORT_OPERATOR: Total Rows Exported:  2  --> though we specified OUTLIMIT as 1 , 2 records were exported as we used two instances and OUTLIMIT applies to each instance. FILE_WRITER: TPT19221 Total files processed: 1. EXPORT_OPERATOR: disconnecting sessions EXPORT_OPERATOR: Total processor time used = '1.56 Second(s)' EXPORT_OPERATOR: Start : Sat Nov  9 11:12:59 2013 EXPORT_OPERATOR: End   : Sat Nov  9 11:13:09 2013 Job step export_to_file completed successfully Job sukul completed successfully

Private log for export Operator:   testunixbox:/home/isaacrdo/sukul> tlogview -l /opt/teradata/client/13.10/tbuild/logs/sukul-25.out -p PXCRM TWB_SRCTGT TWB_STATUS TWB_EVENTS export_privatelog file_writer_privatelog-1 testunixbox:/home/isaacrdo/sukul> tlogview -l /opt/teradata/client/13.10/tbuild/logs/sukul-25.out -f export_privatelog      ===================================================================      =                                                                 =      =                  TERADATA PARALLEL TRANSPORTER                  =      =                                                                 =      =             EXPORT OPERATOR     VERSION 13.10.00.04             =      =                                                                 =      =          OPERATOR SUPPORT LIBRARY VERSION 13.10.00.04           =      =                                                                 =      = COPYRIGHT 2001-2010, TERADATA CORPORATION. ALL RIGHTS RESERVED. =      =                                                                 =      =================================================================== **** 11:12:59 Processing starting at: Sat Nov  9 11:12:59 2013      ===================================================================      =                                                                 =      =                      Module Identification                      =      =                                                                 =      ===================================================================      Export Operator for HP-UX release B.11.31 on testunixbox      ExportMain : 13.10.00.10      ExportCLI  : 13.10.00.09      ExportUtil : 13.10.00.02      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      ===================================================================      =                                                                 =      =                      Attribute Definitions                      =      =                                                                 =      =================================================================== **** 11:12:59 Options in effect for this job:               OperatorType:  Producer               Instances:     2               Character set: Not specified; will use default               Checkpoint:    No checkpoint in effect               Notify:        Not enabled               Encryption:    Data encryption requested               Tenacity:      4 hour limit to successfully connect               Sleep:         6 minute(s) between connect retries               Date format:   INTEGERDATE               Blocksize:     300 bytes               OutLimit:      1 records (per instance)      ===================================================================      =                                                                 =      =                     Column/Field Definition                     =      =                                                                 =      ===================================================================      Column Name                    Offset Length Type      ============================== ====== ====== ========================      EMP_ID                              0     10 CHAR      EMP_NAME                           10     30 CHAR      ============================== ====== ====== ========================      INDICATOR BYTES NEEDED: 1      EXPECTED RECORD LENGTH: 41      ===================================================================      =                                                                 =      =                   Control Session Connection                    =      =                                                                 =      =================================================================== **** 11:12:59 Connecting to RDBMS:    'Prd.Database.com' **** 11:12:59 Connecting with UserId: 'sukul' **** 11:13:00 This job is submitting the following request:      SET QUERY_BAND = 'developer=sukul;' FOR SESSION;  --> note query band statement generated on its own **** 11:13:01 Current working DATABASE set:   'EDW_RESTORE_TABLES' --> Default database set to EDW_RESTORE_TABLES      ===================================================================      =                                                                 =      =                  Teradata Database Information                  =      =                                                                 =      =================================================================== **** 11:13:01 Teradata Database Version:      '13.10.07.14                     ' **** 11:13:01 Teradata Database Release:      '13.10.07.02                   ' **** 11:13:01 Maximum request size supported: 1MB **** 11:13:01 Session character set:          'ASCII' **** 11:13:01 Total AMPs available:           384 **** 11:13:01 Data Encryption:                supported **** 11:13:01 Blocksize for this job:         300 bytes      ===================================================================      =                                                                 =      =                   Special Session Connection                    =      =                                                                 =      =================================================================== **** 11:13:03 Number of sessions adjusted due to TASM:      16               Instance Assigned Connected Result               ======== ======== ========= ======================                   1         8        8    Successful                   2         8        8    Successful               ======== ======== ========= ======================                 Total      16       16    Successful      ===================================================================      =                                                                 =      =                         Select Request                          =      =                                                                 =      =================================================================== **** 11:13:04 This job is submitting the following request:      SELECT CAST(EMP_ID AS CHAR(10)),EMP_NAME(CHAR(30)) FROM SOURCE_EMP_TABLE; **** 11:13:05 Select execution completed. 3 data blocks generated. **** 11:13:05 OutLimit reached.      ===================================================================      =                                                                 =      =                        End Export Phase                         =      =                                                                 =      =================================================================== **** 11:13:05 END EXPORT SUBMITTED **** 11:13:05 END EXPORT COMPLETED      Total Records Exported From RDBMS    =  2         Records exported by instance 1    =  1         Records exported by instance 2    =  1      ===================================================================      =                                                                 =      =                        Logoff/Disconnect                        =      =                                                                 =      =================================================================== **** 11:13:08 Logging off all sessions               Instance      Cpu Time               ========  ================                    1        0.86 Seconds                    2        0.70 Seconds **** 11:13:09 Total processor time used = '1.56 Second(s)'      .        Start : Sat Nov  9 11:12:59 2013      .        End   : Sat Nov  9 11:13:09 2013      .        Highest return code encountered = '0'. **** 11:13:09 This job terminated