One Star ead
One Star

How to get DATA from multiple tables at onece in ETL ???

Hi
I have 3 tables I need to get data out
lets call them pets, store, food (just an example)
I just want to get all the data from those tables, but with a join, mean that if pets have "petID" and so does store, than it will bring it and not show "petID" twice (you get my point, a usually join)
When I use the "tMysqlInput" I can choose more than one table (even 10 or more) at the Query design.
So there it works ok.
BUT
I need to choose table name for it, and there is more than one table.
Also,
I need to define "schema" for it, and need to id manually (not that good) and when I pass it to something like a "logger"
It doesn't work
Can someone please assist me here ?
Thank you.
15 REPLIES
One Star

Re: How to get DATA from multiple tables at onece in ETL ???

If you don't use the Guess Query button you don't have to specify the right table name.
To create the schema you can try to use the guess schema button the if it doesn't work you can create it manually
cheers
One Star ead
One Star

Re: How to get DATA from multiple tables at onece in ETL ???

amm I have tried, but getting problems
Seem like the data isn't right
and why can't I just AUTO create the schema? just like when selecting one table and let it guess the sql for it ??
Thank you!!
One Star ead
One Star

Re: How to get DATA from multiple tables at onece in ETL ???

Anyone ???
One Star

Re: How to get DATA from multiple tables at onece in ETL ???

hi,
i'm not sure to correctly understand,
just put all your tables you need and connect them by a TMAP. One tmysqlinput by table.
You can use db connection in metadata an retrieve the schema for you tables.
After you can drag your table in your job, qry and schema are auto define.
One Star ead
One Star

Re: How to get DATA from multiple tables at onece in ETL ???

Can you explain more about it please?
I have my tables, and defined the scehma for them (auto one)
Than, how can I use them all? means, I don't want just to get the data, but do some kind of join
Here is a example
If I have 3 tables
cars, date, people
people has both the car key and the date key
so I want to get all the cars to a someone on the date
Something like a join, but with more than two tables.
Just getting all the data, but having it on each row with the needed data that is sutible for the keys
One Star

Re: How to get DATA from multiple tables at onece in ETL ???

hi,
did you have read the user guide?
The component TMAP is for your need
One Star ead
One Star

Re: How to get DATA from multiple tables at onece in ETL ???

I have read it!
Still don't understand
Lets say I want to get data out of 5 different tables
But, I don't just want to "take" all the data, but do somekind of JOIN, means that if table A and B has a common key like ID, it will use it.
How can this be done please?
One Star ead
One Star

Re: How to get DATA from multiple tables at onece in ETL ???

anyone?
One Star

Re: How to get DATA from multiple tables at onece in ETL ???

link your 5 tables to the tmap.
In the tmap, link your table on their key.
just drop the field you want on the right side of the TMAP
One Star ead
One Star

Re: How to get DATA from multiple tables at onece in ETL ???

Ok, so I will make 5 SQL input compents for reading the tables
Right?
And than to the TMAP
But, on the tamp, I have one as the main, and the others as "lookup" tables
And I want to get all the fiels, but with some kind of join
Like if table 1 and 2 and 3 have ID, than it will make sure that each row is a join of those ID's
How can it be done?
One Star

Re: How to get DATA from multiple tables at onece in ETL ???

you join table1 to table2, table2 to table3...
One Star ead
One Star

Re: How to get DATA from multiple tables at onece in ETL ???

And than?
I want to join them all, means all the 5 to one table
One Star

Re: How to get DATA from multiple tables at onece in ETL ???

Yes you must do this


jeremie
One Star ead
One Star

Re: How to get DATA from multiple tables at onece in ETL ???

Do what?
5 joins?!
Isn't there a simpler way?
how will you do it?
Can you please upload a photo or something please ?
One Star

Re: How to get DATA from multiple tables at onece in ETL ???

Hello Everone,
I need a advise on this issue.
I am building a data conversion job, where i need to look up using different built in queries from multiple tables(4)(using oracle input components - toracleInput) and validate that data on data in flat file, which i could do on the tmap component using relevant join operations, but my issue is the lookups run before the main flow from CSV componet starts, can any one help me on how i can make the lookups run for each iteration/row of the data from the main flow (flat file - tFileInputDelimited component)
my thanks in advance
Madhu