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
4 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)

Re: Parameterize TPT extraction schema

Hello Dnoeth,

 

I've similar requirement to parameterize Define schema dynamic in the TPT. Is it possible to pass column list or 

SQLquery(select col1,col2, null col3, sysdate col4 from table) as shown below:

 

Scenario 1: Parameterize Column List:

 

DEFINE JOB UNLOAD_USER_DATA
(DEFINE SCHEMA COLUMN_LIST @Column_list

JobVars Includes:

 

 

,FileWriterFileName = 'myfilename.csv'
,Column_list='(col1 varchar(20), col2 varchar2(10), col3 varchar(10), col4 DATE)'
,Sqlquery ='select col1,col2, null col3, sysdate col4 from table'

 

 

Scenario 2 : Parameterize SQL query to Define Schema:

DEFINE JOB UNLOAD_USER_DATA
(DEFINE SCHEMA COLUMN_LIST FROM SELECT 
@Sqlquery;

 

JobVars Includes:

,FileWriterFileName = 'myfilename.csv'
,Sqlquery ='select col1,col2, null col3, sysdate col4 from table'

 

Please correct me, if my assumptions are wrong. I'm trying to extract data from Teradata and export to file pipe(|) delimiter parameterize filename,columnlist,SqlQuery, DB Credenetials.

 

Advance thanks for quick response.

 

thank you.

Re: Parameterize TPT extraction schema

Hello,

 

I've similar requirement to parameterize Define schema dynamic in the TPT. Is it possible to pass column list or 

SQLquery(select col1,col2, null col3, sysdate col4 from table) as shown below:

 

Scenario 1: Parameterize Column List:

 

DEFINE JOB UNLOAD_USER_DATA
(DEFINE SCHEMA COLUMN_LIST @Column_list

JobVars Includes:

 

 

,FileWriterFileName = 'myfilename.csv'
,Column_list='(col1 varchar(20), col2 varchar2(10), col3 varchar(10), col4 DATE)'
,Sqlquery ='select col1,col2, null col3, sysdate col4 from table'

 

 

Scenario 2 : Parameterize SQL query to Define Schema:

DEFINE JOB UNLOAD_USER_DATA
(DEFINE SCHEMA COLUMN_LIST FROM SELECT 
@Sqlquery;

 

JobVars Includes:

,FileWriterFileName = 'myfilename.csv'
,Sqlquery ='select col1,col2, null col3, sysdate col4 from table'

 

Please correct me, if my assumptions are wrong. I'm trying to extract data from Teradata and export to file pipe(|) delimiter parameterize filename,columnlist,SqlQuery, DB Credenetials.

 

Advance thanks for quick response.

 

thank you.