Featured Post

Applying Email Validation to a JavaFX TextField Using Binding

This example uses the same controller as in a previous post but adds a use case to support email validation.  A Commons Validator object is ...

Thursday, October 13, 2011

Controlling the DDL Behind Talend Open Studio's tMySqlOutput

Did you know that you can create a MySQL table with an auto_increment column in Talend Open Studio's tMySqlOutput component?  You can create unique indexes too.

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
Advanced Settings

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
 The Advanced Settings Tab screenshot defines two columns: AI_Contact_Id, UNQ_Contact_Name.  Pay particular attention to the quotes surrounding Name and "Sql expression" and the lack of quotes used in "Data type".  I haven't tried it, but you can probably give a detailed storage specification with this technique (good for Clobs).

Here is a screenshot of MySQL Workbench showing the result of running the TOS job.

MySQL Workbench Showing Result of Run
If you're creating tables on-the-fly using Talend Open Studio's Create "Action on table", use the Advanced settings > Additional information table to pass vendor-specific column parameters to the tMySqlOutput component.

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).

11 comments:

  1. Glad you found it useful. I incorporated your suggestions about setting the Action into the post.

    ReplyDelete
  2. What 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.

    ReplyDelete
  3. Hi Rob,

    It'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.

    ReplyDelete
  4. That worked like a charm. Thank you for the prompt response. I will be reading the rest of your blog posts now, I believe. :)

    ReplyDelete
  5. Good. The best place to start if you're looking for Talend info is to follow the link to "Bekwam Wiki/Talend" under More Talend.

    ReplyDelete
  6. Carl,
    do 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?

    ReplyDelete
    Replies
    1. Hi Ilyas,

      Are 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.

      Delete
  7. do you have any tutorial on that? just like other tutorials :)

    ReplyDelete
  8. Can you use the same method to create auto-increment columns if you define a schema in TOS and then associate the schema with tMySqlOutput ?

    ReplyDelete
    Replies
    1. Sounds 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