Controlling the DDL behind tMySqlOutput when creating a table

Overview

You can create a MySQL table with an auto_increment column on the tMysqlOutput component. You can create unique indexes too. This article explains how.

 

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 action Create table. The table creation statement generated by Talend Studio 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.

tMysqlOutput_schema.png

 

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 doesn't already exist, make sure the Create table action is selected.

create_table_action.png

 

Below is an example that adds two new external columns: Id and Name. Define the Id column as AUTO_INCREMENT and the Name column as UNIQUE.

Name Define the column name
Data type Define the column parameters
SQL expression The SQL expression used to insert column values for each row
Position Define the relative position of the reference column
Reference column Select a column as the reference of this new column

 

tMysqlOutput_advanced_settings.png

 

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

result.png

Version history
Revision #:
2 of 2
Last update:
‎06-21-2017 06:26 PM
Updated by:
 
Labels (1)
Tags (1)