Search code examples
mysqletltalend

Can Talend use a dynamic table name for MySQL output?

(Talend OS for Data Integration)

Is it possible to use a dynamic table name? I've researched all of the resources within the user manual and, whilst none of the them seem to expressly rule it out, I can't seem to get it to work.

Scenario

Say you had a huge list of 1,000,000 names - Dave Smith, Dave Jones, Dave Bloggs etc. These are all stored in one table.

You need to split these into tables which reflect the first name, so you would have a DB with tables names such as 'Andy', 'Adrian', 'Adam' etc.

Where I'm at

What I can do, without issue, is batch export a full dataset into a table. I can also iterate through the field to create my individual tables using a tMysqlRow (this is much quicker on its own).

I then attempted to select and spit out the data into its respective fields, but came across these issues:

  • tMySqlRow can be used with variables, so I can insert the data into a relevant table, but it's so slow it is redundant. It operates at around 2000 rows/sec, and there are some 20 million to do.
  • tMySqlOutput is much quicker, and can do this quite well, but needs to have the table name clearly defined. Using a context variable as the table name doesn't work (reads as 'Null').

It was suggested to look at the ETL components, but their definitions clearly suggest that the table name has to be defined in quotation marks.

It seems really odd for Talend to provide so much use of context variables, but seemingly won't allow you to split one data set into multiple tables dynamically. Is it a product limitation, or just me?

Thanks in advance.

Edit - added screenshots

context variable available in selection The context is printing to the window in this screenshot ('AB' was the test context value used) to show that this value was relevant until it was declared in the 'Table' field, but that then returns a null value

context variable prints to console okay, but is ignored as the table value

Solution

  • Just to add to this, I have managed to get this to work.

    enter image description here

    1. tMysqlInput_1 retrieves the table names from information_schema that are created in a subjob

    2. These are then passes to tHashOutput_1.

    3. tHashInput_1 then feeds these to a tFlowtoIterate component, which iterates through my input job.

    4. I can then access each table name by calling (String)globalMap.get("row6.TABLE_NAME")), as my tHash schema has one column which I have named 'Table_Name'.

    I have a few actions which take place whereby I use the Table_Name in a filter to extract only the fields which match the table name, then passing it to my yMysqlOutput component, which looks like this:

    enter image description here

    I've tested it and it's all good. Hopefully this might save someone a LOT of time figuring out this in future (or 5 minutes to someone who is much better with Talend than I am!).

    Thanks!