All Forums Tools
Harsha1187 1 post Joined 10/12
23 Oct 2012
Multi load through java application.

Hi,
 
I am trying to multi load a csv table. Can you please share a java code for the same. I dont want to use a script for this.
 
Thanks

feinholz 1234 posts Joined 05/08
31 Oct 2012

There is no Java/JDBC interface to the MultiLoad protocol.

--SteveF

tomnolan 594 posts Joined 01/08
31 Oct 2012

The MultiLoad protocol is not supported by the Teradata JDBC Driver at the present time; however, it is on our product roadmap.
As an alternative, we recommend using JDBC FastLoad CSV to load data into a staging table, and then using MERGE or INSERT...SELECT to transfer the data from the staging table into the destination table.
JDBC FastLoad CSV documentation is available here:
http://developer.teradata.com/doc/connectivity/jdbc/reference/current/jdbcug_chapter_2.html#BABFGFA2
JDBC FastLoad CSV sample programs are available here:
http://developer.teradata.com/doc/connectivity/jdbc/reference/current/samplePrograms.html

ekochne 15 posts Joined 09/13
23 Oct 2013

Hello,
Is there any news about adding MultiLoad support to the Teradata JDBC Driver?
Thanks,
Evgeny

tomnolan 594 posts Joined 01/08
23 Oct 2013

No change since my comment a year ago. The MultiLoad protocol is not supported by the Teradata JDBC Driver at the present time; however, it is still on our product roadmap.

ekochne 15 posts Joined 09/13
24 Oct 2013

 
Hi,
Tomnolan,
Thanks for quick answer.
I need to load about 36M records of parrent level and more for child tables.
Now I am using jdbc batching approach - 1000 records of batch for parent.
However our teradata guys are angry and told us that we are using "SINGLE ROW INSERTS" and do big impact to other process.
They suggest to use FastLoad or MultiLoad, but
- FastLoad has restruiction that table should be empty and I have big concern to load 36M at a time. Also we have foreign key constraints and some logic - I mean that there is a chance have error/exception during process data.
- MultiLoad is not supported by Teradata JDBC Driver now :(
They suggest do some "temporary" tables and use FastLoad. Is it possible to glue them together in the end?
Could you suggest something to do in this case? Maybe there is some another solution?
Thanks,
Evgeny
 
 

ekochne 15 posts Joined 09/13
24 Oct 2013

 
Also we think about install the Teradata Express 14.0 for VMware localy, do our export from mongodb, after do export and provide dump file(s) to teradata support team to upload it.
There is one question - what is better to use for export/dumping in this case? Teradata FastExport/Teradata FastLoad or some another tool?
Thanks,
Evgeny

ulrich 816 posts Joined 09/09
25 Oct 2013

read the terms for Teradata Express - it's a development license and is not allowed for production.
also read the inital comment from tomnolan JDBC fastload into empty table and merge can do what you want.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

tomnolan 594 posts Joined 01/08
25 Oct 2013

>>> FastLoad ... big concern to load 36M at a time. Also we have foreign key constraints and some logic - I mean that there is a chance have error/exception during process data
 
Yes, error handling is difficult with FastLoad. If your data is clean, then FastLoad can be a good choice. But if your data is low quality, then FastLoad may not be the best choice.
 
For your particular use case, I recommend JDBC PreparedStatement batch inserts using a regular SQL connection.
 
You said you that are "using jdbc batching approach", but your DBA said you were using single row inserts. That doesn't make sense to me.
1. If you are using a non-prepared Statement batch, then that is transmitted to the Teradata Database as a multi-statement request.
2. On the other hand, if you are using a PreparedStatement batch (what I recommended), then that is transmitted to the Teradata Database as an iterated request.
 
Neither kind of batch corresponds to single row inserts. So your DBA may be misinformed.

ekochne 15 posts Joined 09/13
25 Oct 2013

 
Thanks for detailed answer.
We are using PreparedStatement already.
One more question - if we will export our data as CSV file - can we use MultiLoad in this case to import this file in database?
Thanks,
Evgeny

ekochne 15 posts Joined 09/13
25 Oct 2013

 
Yes, I am asking about some tool which will support MultiLoad mode or what kind of format of data shoud be for MultiLoad mode?
For example - the Teradata Studio Express has Load/Teradata Load functionality then you do right click on table. Does it use FastLoad/MultiLoad mode?
Or I have to generate some special script for MultiLoad?
Thanks,
Evgeny
 
 

ekochne 15 posts Joined 09/13
25 Oct 2013

Also is there some another platform with MultiLoad support already.
For example: .NET Data Provider for Teradata - ?
Thanks,
Evgeny

feinholz 1234 posts Joined 05/08
28 Oct 2013

The standalone MultiLoad utility absolutely supports CSV files.
 

--SteveF

tomnolan 594 posts Joined 01/08
29 Oct 2013

>>> Teradata Studio Express has Load/Teradata Load functionality then you do right click on table.
 
Teradata Studio Express uses the Teradata JDBC Driver for its load and unload functionality.
 
>>> is there some another platform with MultiLoad support already. For example: .NET Data Provider for Teradata - ?
 
Neither the Teradata ODBC Driver or the .NET Data Provider support the MultiLoad wire protocol.
The standalone MultiLoad utility, TPT, and TPTAPI are the only products that currently support the MultiLoad wire protocol.

ekochne 15 posts Joined 09/13
31 Oct 2013

I successfully used the JDBC FastLoad CSV for load around 500.000 records from CSV file to new empty table and it was very fast (about 1-2 minutes).
I’d to  try to load my 36 million records from CSV file too – one question if I have duplication records – how I can handle it?
Where is some parameters for jdbc connection or table to skip duplication records?
 
Also, does it make sense to use BufferedInputStream for reading CSV file or jdbc driver does it itself?
dataStream = new BufferedInputStream(new FileInputStream(file), defaultBufferSize * 10000);
Thanks,
Evgeny

tomnolan 594 posts Joined 01/08
31 Oct 2013

>>> I’d to try to load my 36 million records from CSV file too – one question if I have duplication records – how I can handle it?
 
JDBC FastLoad CSV uses the Teradata FastLoad wire protocol, which has some quirky rules regarding duplicate records.
 
Here is what the Teradata FastLoad Reference says about duplicate rows:
FastLoad can load Primary Index (PI) tables defined as MULTISET, but all duplicate rows are discarded, the target table is treated as if it were a SET table.
NoPI tables are inherently MULTISET, since no duplicate row checking is possible (duplicate rows can be on different AMPs).
Therefore, when FastLoad targets a NoPI table, no check is made for duplicate rows, and duplicate rows are not discarded.
 
>>> Also, does it make sense to use BufferedInputStream for reading CSV file or jdbc driver does it itself?
 
JDBC FastLoad CSV directly uses the InputStream specified by the application in the PreparedStatement.setAsciiStream method, and JDBC FastLoad CSV does NOT wrap the specified InputStream.
 
It is the responsibility of your application to use a BufferedInputStream if needed. We recommend that you test the performance of your application with, and without, using a BufferedInputStream, and choose whether to use it based on actual performance data.
 

ekochne 15 posts Joined 09/13
31 Oct 2013

Thank you for detail explanation.
Can I change to MULTISET for exist table?

tomnolan 594 posts Joined 01/08
01 Nov 2013

I'm not sure what you are asking. Are you asking whether it is possible to change an existing SET table to be a MULTISET table?

ekochne 15 posts Joined 09/13
01 Nov 2013

Yes.

tomnolan 594 posts Joined 01/08
01 Nov 2013

I just examined the Teradata Database SQL Reference / Data Definition Language / ALTER TABLE command, and I do not see any syntax for changing a table from SET to MULTISET.
 
As an alternative, you can rename your table, create a new MULTISET table with the original table name, then INSERT...SELECT all the data from the old table into the new table.

alteves 2 posts Joined 06/12
13 Mar 2015

Hi,
Since it has been 15 months I will ask the same question. 
Is multiload supported yet? Is it still in the roadmap?
Thanks

sshekhard 7 posts Joined 11/14
16 Mar 2015

I am also looking for a MultiLoad Java API. Are there any updates if the support for MultiLoad will be added to Teradata JDBC Driver.

tomnolan 594 posts Joined 01/08
16 Mar 2015

No change since my last comment. The MultiLoad protocol is not supported by the Teradata JDBC Driver at the present time; however, it is still on our product roadmap.

You must sign in to leave a comment.