-1

my team has to do an Oracle database migration using Talend Open Studio Data Integration. There are many schema changes resulting in the target database and we have the new data model for the target database without data. All I have is the source database, empty target database and a heavy mapping document(MS Excel) as depicted in the diagram. There are direct copying over from source columns to target columns, simple mappings where transformation occur between values from source columns to target columns, complex mappings. For example, there are some scenarios where a value from the source column can be mapped to two columns of a target database table and the same value could be populated to a column in a different table. Do you have any idea to approach this problem?

Database mapping document

asthiwanka
  • 437
  • 2
  • 6
  • 17
  • 1
    This is a very broad question, and if you've not studied the Talend capabilities and made an attempt yourself then posting a question here is premature. You should make your best effort and then ask a _specific_ question when you run into a problem. There is no magic bullet for this problem, it's going to be very tedious and error-prone and will take a lot of time to get everything right. – Jim Garrison Jun 02 '14 at 03:44

2 Answers2

1

As mentioned in the comments, this question looks far too broad as it is.

However the crux of your problem is apparent and you just need to do some reasonably simple transformations.

For this you can use Talend's tMap component to cover all the scenarios you mentioned. It's a very powerful tool and simple to use thanks to the graphical interface. Simply pull in your source information and then define your target schema in the output and then drag and drop from the source to the target.

For example your job at a very simple level would look like this:

DB Migration Job Setup

And you could achieve your example mapping from the question with a tMap configured like so:

tMap config

Where you use some very simple Java snippets in your mapping to make the changes as you require. Here, I check for OLD_COL_TWO having a value of "E" and if so making the necessary transformation, otherwise make the other appropriate transformation using the ternary operator.

ydaetskcoR
  • 53,225
  • 8
  • 158
  • 177
  • Thanks for the answer. It is very useful and I will try to make a more generic solution using this approach. – asthiwanka Jun 05 '14 at 06:45
1

As you mention you have fixed data source and fixed destination, then it is better to follow below approach.

  1. create connection for source and retrive schema for all DB schema.
  2. do the same activity for destination databases and retrive schema for same.
  3. now create single job to load data using several subjob or create job for each table load.
  4. step by step add source table and respective ouput table and use tMap or other transformation components to mapp source with destination columns.

if you are looking for any dynamic loading then it is complicated in your scenario and anyway you have to do mapping to utilise dynamic feature of talend.

another option is JobScript which comes with Enterprise version of talend and it is also difficult to workout.

UmeshR
  • 803
  • 1
  • 9
  • 16