topic Re: Dynamic Schema to save different column names in database tables in Talend Studio

Dynamic Schema to save different column names in database tables

Anonymous — Sat, 16 Nov 2024 11:57:35 GMT

Hi,
In my mysql database I have 4 tables.
1. inv_ppl1_flat -- (id, style, color, code, size)
2. inv_ppl2_flat -- (id, style, color, code, size)
3. inv_ppl1_lookup -- (id, ppl1_style, ppl1_color, ppl1_code, ppl1_size)
4. inv_ppl2_lookup -- (id, ppl1_style, ppl2_color, ppl2_code, ppl2_size)
In TOS, I created context variables with name "people" and values to "ppl1" and "ppl2"
I want to get data from ppl1/ppl2_flat table and insert to ppl1/ppl2_lookup table.
In the "tMySqlInput" I given Table name to "inv_"+context.people+"flat" and in "tMySqlOutput" I given the datatabse configurations. But I want to edit the schema. As you can see the column names in 2 lookup tables are different. the style, color, code and size column is preceded by the appropriate name(context variable value). So I cannot map to correct schema. I want to map
style to ppl1/ppl2_style
color to ppl1/ppl2_color
rest are similar

In the edit schema I cannot add context variables. So How can I perform mapping to different (but follows a naming pattern) column name and extract data from first table and insert to second?
Thanks

Re: Dynamic Schema to save different column names in database tables

_AnonymousUser — Mon, 29 Jul 2013 13:30:35 GMT

Database names have been modified for simplicity. I'm trying to get some dynamic sql in place to update static copies of some key production tables into another

Re: Dynamic Schema to save different column names in database tables

Anonymous — Mon, 29 Jul 2013 13:34:34 GMT

Where I can update the sql query?

Re: Dynamic Schema to save different column names in database tables

alevy — Tue, 30 Jul 2013 03:23:14 GMT

I think you can do this by using the "Additional columns" option in tMysqlOutput.
For example, if your tMysqlOutput schema is (id, style, color, code, size), set Name to context.people+"_style", SQL expression to "?", Position to "Replace" and Reference column to "style". Add settings for each column.
Otherwise, the only ways to do this using TOS would be:
1. Have a tMap with a separate output for each lookup table with the correct column names, with a filter expression using your context variable to control to which tMysqlOutput the data goes.
2. Use tMysqlRow to manually create the INSERT INTO statements for your lookup tables.
Using the Enterprise version of Talend, you could use a dynamic schema instead.

Re: Dynamic Schema to save different column names in database tables

Anonymous — Tue, 30 Jul 2013 05:12:00 GMT

In the edit schema I cannot add context.people+"_style". I don't want to map each table separately as I may need to add many more tables soon

Re: Dynamic Schema to save different column names in database tables

alevy — Tue, 30 Jul 2013 06:25:37 GMT

In the edit schema? The instructions I gave were for the "Additional columns" option.