TPT export operator to create multiple output files based on different condition

Tools & Utilities
Enthusiast

TPT export operator to create multiple output files based on different condition

Hi,
We are trying to export data using the export operator in TPT 15.00
We need to export rows from a single table into 2 different output files based on 2 different filter conditions on the same table.I am able to achieve this in a single TPT script using 2 different file writers and using the where condition in the select statement of the file reader.As a result the same table is scanned twice in each of the apply statements.Is there a way around to do this using a single table scan?

Ex: Select * from sample where filter_condition=cond1. ----output file 1
Select * from sample where filter_condition=cond2 ---output file 2
11 REPLIES 11
Teradata Employee

Re: TPT export operator to create multiple output files based on different condition

Try this (I am writing shorthand, but hopefully you get the idea):

 

APPLY TO file_writer_1 WHERE <some-condition>,

APPLY TO file_writer_2 WHERE <some-other-condition>

SELECT * FROM OPERATOR ($EXPORT);

 

This will enable you to read the data once from the DBS, and write to 2 different files based on 2 different conditions.

 

-- SteveF
Enthusiast

Re: TPT export operator to create multiple output files based on different condition

Hi Steve,

 

I tried the approach as mentioned by you above but it is throwing an error:

 

Sample TPT script:

USING CHARACTER SET "UTF8"
DEFINE JOB TPT_DATA_EXTRACT_ID
DESCRIPTION 'Export JOB'
(DEFINE OPERATOR FILE_WRITER_5397_1
TYPE DATACONNECTOR CONSUMER
SCHEMA *
ATTRIBUTES
(
FileName = 'D:\Outputfile1.txt',
Format = 'DELIMITED',
OpenMode = 'Write',
IndicatorMode = 'N',
TextDelimiter = '^|^',
QuotedData = 'Yes',
OpenQuoteMarkHex = '0102',
CloseQuoteMarkHex = '0102' ,
EscapeQuoteDelimiter = '\'
);
DEFINE OPERATOR FILE_WRITER_5397_2
TYPE DATACONNECTOR CONSUMER
SCHEMA *
ATTRIBUTES
(
FileName = 'D:\Outputfile2.txt',
Format = 'DELIMITED',
OpenMode = 'Write',
IndicatorMode = 'N',
TextDelimiter = '^|^',
QuotedData = 'Yes',
OpenQuoteMarkHex = '0102',
CloseQuoteMarkHex = '0102' ,
EscapeQuoteDelimiter = '\'
);

DEFINE SCHEMA SCHEMA_FILE_READER_5397_1
(
EMP_ID INT,
FNAME VARCHAR(60),
LNAME VARCHAR(60),
TM TIMESTAMP(6)

);


DEFINE OPERATOR FILE_READER_5397_1
TYPE EXPORT
SCHEMA SCHEMA_FILE_READER_5397_1
ATTRIBUTES(
UserName = '***',
UserPassword = '***',
SelectStmt = 'SELECT empid, fname, lname, tm FROM samples.table ;',
TdpId = '***',
QuerybandSessInfo = 'UTILITYDATASIZE=LARGE;',
DATAENCRYPTION = 'ON'
);

 

APPLY TO OPERATOR
(FILE_WRITER_5397_1[8])
WHERE EMP_ID=42,
APPLY TO OPERATOR
(FILE_WRITER_5397_2[8])
WHERE EMP_ID=45,
SELECT * FROM OPERATOR

(FILE_READER_5397_1);

);

 

The error I am getting is as below 

Teradata Parallel Transporter Version 15.00.00.00
TPT_INFRA: Semantic error at or near line 74 of Job Script File 'DS1_MATL_T_tptC
mdFile_452903_505_5397.tpt':
TPT_INFRA: TPT03154: Operator 'FILE_WRITER_5397_1' is not of type 'Standalone'.
Operator is rejected as a target of APPLY operation.
Compilation failed due to errors. Execution Plan was not generated.
Job script compilation failed.
Job terminated with status 8.

 

Is there something missing or wrong with the syntax I am using?

Regards,

Indranil Roy

Teradata Employee

Re: TPT export operator to create multiple output files based on different condition

I am being told (by a developer) that we do not support the WHERE clause on the APPLY.

However, before we go down that road, try removing the comma in between the 2nd APPLY and the SELECT.

See if that helps.

-- SteveF
Enthusiast

Re: TPT export operator to create multiple output files based on different condition

Hi Steve,

I tried removing the comma as suggested by you but still getting the same error.Any other way to achieve this?

 

 

 

 

 

 

Regards,

Indranil Roy

Teradata Employee

Re: TPT export operator to create multiple output files based on different condition

We will have to look into it (nobody uses this syntax so maybe not thoroughly tested).

You can try a different syntax:

 

CASE WHEN <cond>

      APPLY TO <file writer 1>

ELSE WHEN <cond 2>

      APPLY TO <file writer 2>

END

SELECT * FROM OPERATOR , , , ,

-- SteveF
Fan

Re: TPT export operator to create multiple output files based on different condition

Hey Steve.

Do you know if any new development has enabled this functionality? 

I haven't had any success doing a conditional export to different files. 

I can't get any syntax in this thread to work nor can I find any documentation. 

In short I'm trying to run 1 sql and use the export operator to send specific values to specific files. 

ie

write all lines with value 1 > file1 

write all lines with value 2 > file2 

 

Thx!

 

Teradata Employee

Re: TPT export operator to create multiple output files based on different condition

I have not tried it, but the APPLY statement should support a WHERE clause.

So, you should be able to do:

APPLY TO OPERATOR ($FILE_WRITER) WHERE x > 100

 

We also support a feature (although it is not advertised and possibly difficult to find in the documentation) called "multiple APPLY".

This allows:

APPLY TO OPERATOR (<operator-1-name>),

APPLY TO OPERATOR (<operator-2-name>)

SELECT * FROM . . . . ;

 

Thus, you MIGHT be able to do this:

APPLY TO OPERATOR ( $FILE_WRITER[1] ATTR( ... ) ) WHERE x > 100,

APPLY TO OPERATOR ( $FILE_WRITER[1] ATTR( ... ) ) WHERE x < 100

SELECT * FROM OPERATOR ($EXPORT);

 

where you put a different file name in the attribute list for each copy of the file writer.

Again, I am not sure whether this will work, but it might be worth a try.

 

-- SteveF

Re: TPT export operator to create multiple output files based on different condition

@indra91 @feinholz 

 

i also have same requirement, but not able to acheive any solution. Could you please let me know if any solution or workaround is implemented for it.

Teradata Employee

Re: TPT export operator to create multiple output files based on different condition

Please be explicity for what you would like to achieve.

Did you try my suggestion above?

 

-- SteveF