0

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?

codeNinja
  • 1,442
  • 3
  • 25
  • 61
  • 1
    Have you tried csv? Teradata can dump a csv file, and MySQL load data infile is pretty efficient. It should be straightforward, assuming you don't have any binary datatypes or other vendor-specific oddities. What OS are you on? You can run Teradata scripts and delete MySQL tables from the same cron job, if on linux. – John Powell Apr 02 '14 at 21:14
  • i use csv export right now but its a manual process. I need to automate it. One challenge i find is that the dates in the teradata table are `mm/dd/yyyy` and when i import it to mysql it becomes `0000-00-00`. not sure how to fix that. I am on a windows server. – codeNinja Apr 02 '14 at 21:25
  • I don't know so much about the equivalent of cron jobs on Windows. But you can use date_format on the date field on import to MySQL to specify the correct format. Something I did once with spatial data, which can't be dumped/restored cleanly between different database systems, was to set up a black hole database in MySQL, load csv into that, using load data infile and use a trigger to transform the data into a MySQL geometry type. This avoids having to load an intermediate table and then transform it (and all the wasted space that goes with that). Off the wall, maybe. :-) – John Powell Apr 02 '14 at 21:34
  • 1
    For exporting a CSV you should use a TPT Export using DELIMITED. And DATEs in Teradata don't have a format, this is just applied when casting it to a string. This can easily be modified using the FORMAT command. – dnoeth Apr 02 '14 at 21:45

1 Answers1

1

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?

Community
  • 1
  • 1
Frank Tudor
  • 4,226
  • 2
  • 23
  • 43
  • +1 A somewhat more complete version of my use csv suggestion. – John Powell Apr 02 '14 at 21:22
  • We all have to grunt through this kind of thing. I had two projects with doctor information using all kinds of feed pulls and scrubbers and import failures because of bad chars in text fields wrecking my import process. It can get nasty...its never perfect. The OP is lucky it is just one table :) – Frank Tudor Apr 02 '14 at 21:26
  • Btw, there's no CREATE EXTERNAL TABLE in Teradata :) – dnoeth Apr 02 '14 at 21:36
  • @dnoeth. What is there instead? – John Powell Apr 02 '14 at 21:42
  • 1
    For exporting CSV you can use a TPT Export in DELIMITED format. – dnoeth Apr 02 '14 at 21:50
  • @dnoeth I added this suggestion to the big list above and gave him a link to a teradata document that discusses it. + to you :) – Frank Tudor Apr 02 '14 at 21:57