Java - table de copie de teradata à mysql - Stack Overflow ~ Stackoverflow Blog

i have searched for hours with no real solution.

I want to setup an ongoing task (everynight). I have a table in a Teradata database on server 1. Everynight i need to copy an entire table from this teradata instance to my development server (server 2) that has MySQL 5.6.

How do i copy an entire table form server 1 to server 2?

Things i have tried: 1)Select all data from table x into ResultSet from teradata server 1. Insert into mysql via preparedStatement. But this is crazy slow. Also i am not sure how to Drop the table and recreate it each night with the schema from the teradata server.

Any help please?

There are a few ways you can do this.

Note: these may be older methods just trying to get you to thinking about how you can do this in your current environment. Plus I am not familiar with your data sensitivity and permissions, etc.

One would be teradata to MySQL via CSV file see the examples and links below. (these could be older posts but the basic ideas are what you need).

Export from teradata:

CREATE EXTERNAL TABLE database_name.table_name (to be created) SAMEAS database_name.table_name (already existing, whose data is to be exported) USING (DATAOBJECT ('C:\Data\file_name.csv') DELIMITER '|' REMOTESOURCE 'ODBC');

Export From Teradata Table to CSV

Edit: If CREATE EXTERNAL TABLE doesn't fly then you may have to use java to extract first and then organize the data...Mimic the current method (however it works) at getting the data. Google-fu with this handy link https://www.google.com/search?q=external+csv+file+teradata&oq=external+csv+file+teradata&

(dnoeth) below recommends this: TPT Export in DELIMITED format (which looks like a hassle...but could be the only way) So here is a link that discusses it: http://developer.teradata.com/tools/articles/external-data-formats-supported-by-the-tpt-dataconnector-operator

Import to mysql (don't drop the table.just delete from table):

mysqlimport --ignore-lines=1 \ --fields-terminated-by=, \ --local -u root \ -p Database \ TableName.csv

http://chriseiffel.com/everything-linux/how-to-import-a-large-csv-file-to-mysql/

You would need to schedule this in both the environments and that could be a huge hassle.

Now I see you use Java and in Java you could create a simple scheduled task via (whatever you have available for scheduling tasks). It is possible that you will have to do trial and error runs and your data could be an issue depending on what it is. How it is delimited, if it has headers, etc.

Then you would call variants of the examples above. Through Java

here is a java example:

http://www.java-tips.org/other-api-tips/jdbc/import-data-from-txt-or-csv-files-into-mysql-database-t-3.html

and another:

How to uploading multiple csv file into mysql database using jsp and servlet?

another:

http://www.csvreader.com/java_csv_samples.php

So the basic idea is exporting your csv from teradata which should be simple.

Using Java to traverse the file server to get your file (you may need to FTP somewhere) you may need to consider this....

Consume your CSV file using Java and either a UDF or some package that can iterate over your CSV (stuff) and import into MySQL (write one yourself or find one on the "internet of things" as they now call it).

Edit: Here is info on scheduling tasks with java and links to review.

http://docs.oracle.com/javase/6/docs/api/java/util/concurrent/ScheduledExecutorService.html

and check this SO convo...

How to schedule a periodic task in Java?