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.
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.
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.
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).
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).
No comments:
Post a Comment