Export large data set to CSV files

Tools
Enthusiast

Export large data set to CSV files

Hi, folks,

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.

9 REPLIES
Teradata Employee

Re: Export large data set to CSV files

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.

Teradata Employee

Re: Export large data set to CSV files

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.

Robert.Hahn@Teradata.com

-Fred

Enthusiast

Re: Export large data set to CSV files

Hi

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'

https://github.com/xlfe/dwhwrapper

Any questions please let me know.

Teradata Employee

Re: Export large data set to CSV files

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.

-- SteveF
Enthusiast

Re: Export large data set to CSV files

It is pretty clearly labelled as not being a teradata product, actually.

Teradata Employee

Re: Export large data set to CSV files

It is labeled when they go to the website.

I do not want people misled just by looking at the post.

-- SteveF
Enthusiast

Re: Export large data set to CSV files

But thanks for your constructive comments

Teradata Employee

Re: Export large data set to CSV files

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.  

Teradata Employee

Re: Export large data set to CSV files

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.

 

-- SteveF