Saturday, 16 November 2013

10.2 Teradata Parallel Transporter - SQL inserter example

Example: Load a table using SQL inserter 

DEFINE JOB LOAD_EMPLOYEE_TABLE_FROM_FILE
DESCRIPTION 'Load file using inserter operator'
(
/*****************************/
DEFINE SCHEMA EMPLOYEE_SCHEMA
DESCRIPTION 'SAMPLE EMPLOYEE SCHEMA'
(
EMP_ID   VARCHAR(10),
EMP_NAME VARCHAR(30)
);
/*****************************/


DEFINE OPERATOR FILE_READER()
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DATA CONNECTOR OPERATOR'
TYPE DATACONNECTOR PRODUCER
SCHEMA EMPLOYEE_SCHEMA
ATTRIBUTES
(
VARCHAR PrivateLogName    = 'file_reader_private.log',
VARCHAR FileName          = 'input_delimited_file.txt',
VARCHAR IndicatorMode     = 'N',
VARCHAR OpenMode          = 'Read',
VARCHAR Format            = 'Delimited'
);


/*****************************/
DEFINE OPERATOR TEST_INSERTER
DESCRIPTION 'Teradata PT INSERTER OPERATOR'
TYPE INSERTER
SCHEMA *
ATTRIBUTES
(
VARCHAR TdpId = 'Prd.Database.com',
VARCHAR UserName = sukul,
VARCHAR UserPassword = ‘mysillypwd’,
VARCHAR PrivateLogName = 'SQL_inserter_log'
);

/*****************************/

STEP export_to_file
(
APPLY ('INSERT INTO FINANCE.SOURCE_EMP_TABLE VALUES (:EMP_ID,:EMP_NAME);')
TO OPERATOR (TEST_INSERTER() )
SELECT EMP_ID,EMP_NAME FROM OPERATOR (FILE_READER());
);
);


No comments:

Post a comment