TPT export large table to smaller text files

Tools

TPT export large table to smaller text files

Hello good people

I have a table of ~77 million rows that I need to export to fixed width text files, however I need to impose a limit of 5 million rows per file.

I am currently achieving this with "SELECT * FROM MYTABLE QUALIFY ROW_NUMBER() OVER(ORDER BY COL1 ASC) BETWEEN 1 AND 5000000"

and then altering the statement e.g. "...BETWEEN 5000001 AND 10000000" etc until done.     

While this works, it means that I am altering and executing my TPT script 16 times and it is rather slow. I'm sure someone out there has a much more efficient and elegant solution? I have researched using Export operator instead of SQL_Selector and OUTMOD but I have had no success.   Open to suggestions! :) 

DEFINE JOB MYJOB

DESCRIPTION 'Export rows from a Teradata table to a fixed width flat file'

(

 DEFINE SCHEMA SOURCE_SCHEMA

 (

  col1   CHAR(12),

  col2    CHAR(30),

  col3   CHAR(15),

  col4  CHAR(30),

 ...

col(n) CHAR(x)

 );

 DEFINE OPERATOR SQL_SELECTOR

 TYPE SELECTOR

 SCHEMA SOURCE_SCHEMA

 ATTRIBUTES

 (

  VARCHAR TdpId = @TdpId,

  VARCHAR UserName = @User,

  VARCHAR UserPassword = @Password,

  VARCHAR LogonMech = 'LDAP',

  VARCHAR SelectStmt = 'SELECT * FROM MYTABLE QUALIFY ROW_NUMBER() OVER(ORDER BY COL1 ASC) BETWEEN 1 AND 5000000;',

  VARCHAR ReportModeOn

 );

 DEFINE OPERATOR FILE_WRITER

 TYPE DATACONNECTOR CONSUMER

 SCHEMA *

 ATTRIBUTES

 (

  VARCHAR DirectoryPath = '.\data\',

  VARCHAR FileName = 'EXTRACT_PART1.DAT',

  VARCHAR Format = 'Text',

  VARCHAR OpenMode = 'Write'

 );

 APPLY TO OPERATOR (FILE_WRITER)

 VIA OPERATOR (

 SELECT * FROM OPERATOR (SQL_SELECTOR) ;

);

Tags (3)
1 REPLY
Junior Contributor

Re: TPT export large table to smaller text files

If it doesn't have to be exactly 5,000,000 rows per file you might simply use multiple instances of the file writer APPLY TO OPERATOR (FILE_WRITER[16]), this will create 16 files each like file1,file2,file3,... 

And you need to add the -C option to the tbuild call.