All Forums Tools
brockappleby 3 posts Joined 11/13
21 Oct 2014
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) ;
);

dnoeth 4628 posts Joined 11/04
22 Oct 2014

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.

Dieter

You must sign in to leave a comment.