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
DEFINE OPERATOR SQL_SELECTOR
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;',
DEFINE OPERATOR FILE_WRITER
TYPE DATACONNECTOR CONSUMER
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) ;
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), this will create 16 files each like file1,file2,file3,...
And you need to add the -C option to the tbuild call.