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