Search code examples
talend

Talend Open Studio : creating table in MySQL dynamically

I am trying to find out how to do dynamic creation of tables in MySQL using Talend. In other ETL tools such as Pentaho they have a specific component called "metadata" to do this. So my use case is the following: 1) Create database manually in MySQL 2) Use Talend to read CSV Header info, and use this as the fields of a table to be created in MySQL using Talend.

I have searched and could not find anything for this specific feature online. [Note : using Talend Open Studio for Big Data Version: 6.4.1]

UPDATE:

I have made progress on this, but running into issues trying to generate a primary key on the MySQL output using the NumericSequence function. My data does not have natively a primary key, but wish to include one: I have a screenshot of the tMap attached here:

enter image description here

Also I am getting a compile/build error. It seems it does not like the primary key generation it seems.

See attached image.

enter image description here

And here also is the tMySQLOutput settings for Primary Key:

enter image description here

More Work Done:

I have changed the name of the Primary Key in the tMySQL component to match the name of the tMap output area component, i get the same compile error. I will attach this error here:

enter image description here

Solution

  • To create dynamically MySQL tables in Talend, you can use one of the "Action on table" options in tMysqlOutput components (in your case, "Create table...").

    https://help.talend.com/reader/4I8tDQGtrOPDl5MXAS3Q~w/aDNKleHXlevILu9pnbCoNg

    Don't forget to define correctly PK fields for further inserts, updates, deletes...

    Then, if necessary, through your favorite Database Tool (MySQL Workbench, DBeaver or other), you can retrieve the DDL (and DML) script(s).

    I hope this answers to your problem/question.