Parameterize TPT extraction schema

Database

Parameterize TPT extraction schema

I am writing a TPT Teradata to File extraction script on Teradata V15.

Objective - To export selected columns from any table(s) with minimum/no hardcoding.

Achieved - I have got most part parameterized - SelectStatement, Producer to Consumer statement.

Problem - Unable to parameterize DEFINE SCHEMA section.

Efforts - I had put job parameter inside define schema section. It gives RPARENT_ missing error.

Column list quoted in error log seems fine (as follows). Meaning what was passed as parameter has come to TPT as is.

Item_Id varchar(50), Root_Id varchar(50)

I cannot use the option to define schema automatically by table name. Because it will pour in entire definition of table as the schema. That I don't want.

Schema should be defined on basis of columns that are passed in dynamically as parameters to TPT.

Kindly help with your insight.

 

Thank you

V


Accepted Solutions
Junior Contributor

Re: Parameterize TPT extraction schema

 

You don't need to add a SCHEMA, it's done by TPT based on the SELECT. 

 

Based on a script from the TPT User Guide your script looks like this:

DEFINE JOB EXPORT_DELIMITED_FILE
DESCRIPTION 'Export rows from a Teradata table to a delimited file'
(
  APPLY TO OPERATOR ($FILE_WRITER() ATTR (Format = 'DELIMITED'))
  SELECT * FROM OPERATOR ($SELECTOR ATTR (SelectStmt = @ExportSelectStmt)
); 

And then your jobvars include:

,FileWriterFileName = 'myfilename.csv'
,ExportSelectStmt = 'select mycolumnlist from mytable;'

  

 

 

1 ACCEPTED SOLUTION
2 REPLIES
Junior Contributor

Re: Parameterize TPT extraction schema

 

You don't need to add a SCHEMA, it's done by TPT based on the SELECT. 

 

Based on a script from the TPT User Guide your script looks like this:

DEFINE JOB EXPORT_DELIMITED_FILE
DESCRIPTION 'Export rows from a Teradata table to a delimited file'
(
  APPLY TO OPERATOR ($FILE_WRITER() ATTR (Format = 'DELIMITED'))
  SELECT * FROM OPERATOR ($SELECTOR ATTR (SelectStmt = @ExportSelectStmt)
); 

And then your jobvars include:

,FileWriterFileName = 'myfilename.csv'
,ExportSelectStmt = 'select mycolumnlist from mytable;'

  

 

 

Re: Parameterize TPT extraction schema

Thank you so much Dieter.

I didn't know about these generation templates.

This makes schema generation fully automatic and parameterized :)

Thanks again

Tags (1)