I need to export a large data set from Teradata into CSV (pipe-delimited) files. A few tables are fairly large (600GB - 1TB). The tables all have a date column, which means I could use the date column to split them up into multiple files. But the export speed using ODBC/OLEDB connection is not great.
Does anyone have suggestions on what is the best way to do it? I am thinking TPT (Export operator)/Fexp. I'd appreciate it if someone could provide insight.
Btw, I prefer not to write OUTMOD modules to handle formatting.
Teradata Database 14.0 has a built-in function to format data as CSV for export.
With TD 13.10 and earlier versions, you can format the data yourself in the select statement, e.g.
select column1 || '|' || column2 || '|' || column3 from mytable
You can use CAST functions and FORMAT clauses to control how particular columns are formatted.
Send an email to Bob Hahn. He has developed an axsmod called pfast which allows you to easily do what you describe. It works great. We are using it with 13.10.
You might also be interested in a tool we developed in house, which we have now opensourced which allows you to use BTEQ or FastExport to write directly to a csv file... as easy as:
dwhget --fexp Output.CSV 'select * from mydb.mytable'
Any questions please let me know.
Please refrain from using the term "in house" when responding on this forum.
People might get the idea that it is a Teradata product, which it is not.
Teradata Parallel Transporter can output delimited format. If the query resultset includes non-character data, the Data Connector Operator does client side conversion to character. See example pts00016 described in the Teradata Parallel Transporter User Guide and included with the distribution.
TPT will do it easily.
Just use the Export operator and the DataConnector operator (file writer).
If you specify an instance count for the DC operator, the instance count will be the number of files generated with the output from the Export operator.
Additionally, if you use the -C command line argument (highly recommended for file writing) then the data blocks will be written out in a round-robin fashion, meaning the files will be pretty close in size to each other.
Furthermore, you can also use multiple Export operators (and tie them together with the UNION ALL syntax) and each Export operator could export a subset of the data blocks by using a WHERE clause in the SELECT statement.