In Talend Open Studio, an output component like tMySqlOutput is based on a target schema. tMyqlOutput can be set to create a table on-the-fly if one doesn't exist: actions "Create table" and "Create table if not exists". The DDL generated by Talend to create the table is based on settings in the Schema window. Although the Schema window allows vendor-specific data types and NOT NULL settings, it doesn't allow for MySQL features like AUTO_INCREMENT and UNIQUE.
This schema is displayed when the "Edit schema" button is pressed on the Basic settings tab of tMySqlOutput. It was created from an input source. This window enables you to set the type (Java and MySQL), a NOT NULL flag, a DEFAULT value, and a COMMENT. However, there is no checkbox for AUTO_INCREMENT or UNIQUE.
 |
| tMySqlOutput Schema |
The "Advanced settings" tab of the component gives you this additional control. A full column type specification -- including modifiers like AUTO_INCREMENT -- can be added. To view the additional columns table, make sure that a DDL-creating action like "Create" is set.
 |
| Advanced Settings Tab |
Here is a screenshot of MySQL Workbench showing the result of running the TOS job.
 |
| MySQL Workbench Showing Result of Run |
UPDATE: When working with this technique, I noticed that syntax errors sometimes occur. This is shown in the error dialog that displays during an aborted run. To fix this, delete the special AUTO_INCREMENT or other column from the "Advanced settings" tab and re-create it (identically).
Thanks for the help. Worked for me
ReplyDeleteGlad you found it useful. I incorporated your suggestions about setting the Action into the post.
ReplyDeleteWhat version of Talend OS are you using? The "datatype" column does not appear in the tMySqlOutput advanced tab in version 4.2.3 for any of the "create" table actions.
ReplyDeleteHi Rob,
ReplyDeleteIt's there in 4.2.3. The Additional columns table interacts with the "Action on table" select box on a different tab and isn't immediately present. Also, the redrawing takes a switch between "Basic settings" and "Advanced settings" to display correctly.
Here's how to see the Data Type column with tMysqlOutput and tOracleOutput.
1. Drag a component on the canvas. Bring up the Component View / Basic settings tab.
2. Set "Action on table" to one of the Clear or Drop actions.
3. Switch to "Advanced settings".
4. Switch back to "Basic settings".
5. Return to "Advanced settings". Verify that there's a "Data type" field.
That worked like a charm. Thank you for the prompt response. I will be reading the rest of your blog posts now, I believe. :)
ReplyDeleteGood. The best place to start if you're looking for Talend info is to follow the link to "Bekwam Wiki/Talend" under More Talend.
ReplyDeleteCarl,
ReplyDeletedo you know how to check if the rows attributes already exists in a table, just don't insert the incoming one, instead fetch the ID of the already existing row from table?
Hi Ilyas,
DeleteAre you trying to update the table if there's a match? Maybe this post is what you're looking for: http://bekwam.blogspot.com/2011/10/updating-table-with-talend-open-studio.html.
do you have any tutorial on that? just like other tutorials :)
ReplyDeleteCan you use the same method to create auto-increment columns if you define a schema in TOS and then associate the schema with tMySqlOutput ?
ReplyDeleteSounds like it will work, but you may need to remove the ID column so that the value provided by you (with the auto increment spec) will be used.
Delete