Controlling the DDL behind tMySqlOutput when creating a table

 

Overview

Did you know that you can create a MySQL table with an auto_increment column on tMysqlOutput component?  You can create unique indexes too. This article explains how to achieve it.

Environment

This procedure applies to all versions of Talend Studio.

Procedure

In Talend Studio, an output component like tMysqlOutput is based on a target schema.  tMysqlOutput can be set to create a table on-the-fly if it doesn't exist with the actions "Create table".  The table creation statement generated by Talend Studio to create the table  is based on the settings defined in the component Schema.  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.

Basic Settings

The Schema window is displayed when the "Edit schema" button is pressed on the Basic settings tab of tMysqlOutput. Generally, the schema columns are synchronized automatically with the input Schema if you have linked a row-type connector from an input component to tMysqlOutput.This window enables you to set the type (Java and MySQL), a NOT NULL flag, a PRECISION, 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 allows you control other DDL in the Additional columns table.  A full column type specification -- including modifiers like AUTO_INCREMENT can be added.  To create a table on-the-fly on tMysqlOutput if it does't not exist, make sure the 'Create table' action is selected.

Below is an example which adds two external new columns: Id and Name, define Id column as AUTO_INCREMENT and Name column as UNIQUE.

Name: define the column name.

Data type: define column parameters.

SQL expression: the SQL expression used to insert column value for each row.

Position: define the relative position of the reference column.

Reference column: select a column as reference of this new column.

Here is a screenshot showing the result of table creation after you run the Talend Job.

Version History
Revision #:
1 of 1
Last update:
‎04-17-2017 11:06 PM
Updated by:
 
Labels (1)
Contributors