Examples - Access Module

Teradata Tools and Utilities Access Modules User Guide

Product
Access Module
Release Number
15.10
Language
English (United States)
Last Update
2018-06-06
Product Category
Teradata Tools and Utilities

Export Scenario

Job Variable File

Note: The parameters inside <> brackets need to be replaced appropriately and <> brackets need to be removed.

/********************************************************/
/* TPT attributes - Common for all Samples              */
/********************************************************/
TargetTdpId = '<Teradata Database ID>'
,TargetUserName = '<TargetUserName>'
,TargetUserPassword = '<TargetUserPassword>'
,TargetErrorList = [ '3706','3803','3807' ]
,DDLPrivateLogName = 'DDL_OPERATOR_LOG'
 
/********************************************************/
/* TPT EXPORT Operator attributes                       */
/********************************************************/
 
,ExportPrivateLogName = 'EXPORT_OPERATOR_LOG'
,SourceTdpId = '<Teradata Database ID>'
,SourceUserName = '<SourceUserName>'
,SourceUserPassword = '<SourceUserPassword>'
 
/********************************************************/
/* TPT DataConnector Consumer Operator                  */
/********************************************************/
 
,FileWriterFormat = 'Formatted'
,FileWriterPrivateLogName = 'FILE_WRITER_LOG'
,FileWriterFileName = 'S3W001DT'
,FileWriterDirectoryPath = '.'
,FileWriterOpenMode = 'Write'
 
/********************************************************/
/* APPLY STATEMENT parameters */
/********************************************************/
,ExportInstances = 1
,FileWriterInstances = 1

Setup Job

DEFINE JOB CREATE_SOURCE_TABLE_IN_TERADATA
DESCRIPTION 'Create a source table in Teradata'
(
    STEP CREATE_AND_POPULATE_SOURCE_TABLE
    (
        APPLY
        ('DROP TABLE S3W001_src;'),
        ('CT S3W001_src
          (
              Associate_Id INTEGER,
              Associate_Name CHAR(25),
              Salary FLOAT,
              DOJ DATE,
              Designation VARCHAR(25),
              Loan_Amount DECIMAL(5,2),
              Martial_Status CHAR(1),
              No_Of_Dependents BYTEINT
          );
        '),
        (' Ins S3W001_src( 1,''Morgan Tremblay'',10.12,''99-03-20'',
                              ''Software Engineer'',110.12,''y'',1);
           Ins S3W001_src( 2,''Chalmers Clayton'',11.12,''99-03-21'',
                              ''Technical Architect'',111.12,''n'',2);
           Ins S3W001_src( 3,''Francis Cochran'',12.12,''99-03-22'',
                              ''Software Engineer'',112.12,''n'',2);
 
           Ins S3W001_src( 4,''Hector Nielsen'',13.12,''99-03-23'',
                              ''Software Engineer'',113.12,''y'',4);
           Ins S3W001_src( 5,''Sherlock Fisher'',14.12,''99-03-24'',
                              ''HR Manager'',114.12,''n'',3);
           Ins S3W001_src( 6,''Orson Sanchez'',15.12,''99-03-25'',
                              ''Software Engineer'',115.12,''y'',6);
           Ins S3W001_src( 7,''Gregory Benton'',16.12,''99-03-26'',
                              ''Department Manager'',116.12,''y'',1);
           Ins S3W001_src( 8,''Bryant McCabe'',17.12,''99-03-27'',
                              ''Software Engineer'',117.12,''y'',1);
           Ins S3W001_src( 9,''Delmar Halsey'',18.12,''99-03-28'',
                              ''Facility Manager'',118.12,''n'',4);
           Ins S3W001_src(10,''Aldrich Jones'',19.12,''99-03-29'',
                              ''Floor Manager'',119.12,''n'',5);
           Ins S3W001_src(11,''Unwin Russell'',20.12,''99-03-30'',
                              ''Software Tester II'',120.12,''n'',6);
           Ins S3W001_src(12,''Gabriel Cooper'',21.12,''99-03-19'',
                              ''Software Tester'',121.12,''n'',2);
           Ins S3W001_src(13,''Willis James'',22.12,''99-03-18'',
 
                              ''Build Engineer'',122.12,''y'',2);
           Ins S3W001_src(14,''Phineas Campbell'',23.12,''99-03-17'',
                              ''Software Engineer'',123.12,''y'',2);
           Ins S3W001_src(15,''Rupert Butler'',24.12,''99-03-16'',
                              ''Design Engineer'',124.12,''n'',3);
           Ins S3W001_src(16,''Toby Ortiz'',25.12,''99-03-15'',
                              ''Software Engineer'',125.12,''y'',4);
           Ins S3W001_src(17,''Hardy Peterson'',26.12,''99-03-14'',
                              ''Software Engineer III'',126.12,''n'',2);
           Ins S3W001_src(18,''Zane Morgan'',27.12,''99-03-13'',
                              ''Software Engineer'',127.12,''n'',3);
           Ins S3W001_src(19,''Igor Smith'',28.12,''99-03-12'',
                              ''Software Engineer'',128.12,''y'',2);
           Ins S3W001_src(20,''Edric Davidson'',29.12,''99-03-11'',
                              ''Software Engineer II'',129.12,''y'',4);
           Ins S3W001_src
               Sel Associate_Id + 20, Associate_Name, Salary, DOJ, Designation,
               Loan_Amount, Martial_Status, No_Of_Dependents from S3W001_src;
           Ins S3W001_src
               Sel Associate_Id + 40, Associate_Name, Salary, DOJ, Designation,
               Loan_Amount, Martial_Status, No_Of_Dependents from S3W001_src;
           Ins S3W001_src
               Sel Associate_Id + 80, Associate_Name, Salary, DOJ, Designation,
               Loan_Amount, Martial_Status, No_Of_Dependents from S3W001_src;
 
           Ins S3W001_src
               Sel Associate_Id + 160, Associate_Name, Salary, DOJ, Designation,
               Loan_Amount, Martial_Status, No_Of_Dependents from S3W001_src;
           Ins S3W001_src
               Sel Associate_Id + 320, Associate_Name, Salary, DOJ, Designation,
               Loan_Amount, Martial_Status, No_Of_Dependents from S3W001_src;
           Ins S3W001_src
               Sel Associate_Id + 640, Associate_Name, Salary, DOJ, Designation,
               Loan_Amount, Martial_Status, No_Of_Dependents from S3W001_src;
           Ins S3W001_src
               Sel Associate_Id + 1280, Associate_Name, Salary, DOJ, Designation,
               Loan_Amount, Martial_Status, No_Of_Dependents from S3W001_src;
        ')
         TO OPERATOR ($DDL);
    );
 
);

Export Job

Note: The parameters inside <> brackets need to be replaced appropriately and <> brackets need to be removed. The Teradata Access Module for Amazon S3 config file and credentials file need to be created and placed at $HOME/.aws.

DEFINE JOB EXPORT_FROM_TERADATA
DESCRIPTION 'Export data from Teradata to Amazon S3'
(
    STEP EXPORT_THE_DATA
    (
        APPLY TO OPERATOR ( $FILE_WRITER()
            ATTR
            (
                AccessModuleName = 'libs3axsmod.so',
                AccessModuleInitStr = 'S3Bucket=<S3BucketName> S3Prefix="<PrefixName>/" S3Object=testdata.raw S3SinglePartFile=True'
            )
        )
        SELECT * FROM OPERATOR ( $EXPORT
            ATTR
            (
                SelectStmt = 'SELECT * FROM S3W001_src;'
            )
        );
    );
);