Scenario 2: Writing dynamic columns from a MySQL database to an output file - 6.1

Talend Components Reference Guide

EnrichVersion
6.1
EnrichProdName
Talend Big Data
Talend Big Data Platform
Talend Data Fabric
Talend Data Integration
Talend Data Management Platform
Talend Data Services Platform
Talend ESB
Talend MDM Platform
Talend Open Studio for Big Data
Talend Open Studio for Data Integration
Talend Open Studio for Data Quality
Talend Open Studio for ESB
Talend Open Studio for MDM
Talend Real-Time Big Data Platform
task
Data Governance
Data Quality and Preparation
Design and Development
EnrichPlatform
Talend Studio

Warning

This scenario makes use of the Dynamic Schema feature, which is only available to users who have subscribed to one of the Talend solutions.

In this scenario we will read dynamic columns from a MySQL database, map them and then write them to a table in a local output file. By defining a dynamic column alongside known column names, we can retrieve all of the columns from the database table, including the unknown columns.

Setting up the Job

  1. Drop a tAmazonMysqlInput, a tMap and a tFileOutputDelimited component onto the workspace.

  2. Link tAmazonMysqlInput to tMap using a Row > Main connection. Link tMap to tFileOutputDelimited using a Row > *New Output* (Main) connection.

Configuring the input component

  1. Double-click tAmazonMysqlInput to open its Basic settings view in the Component tab.

    Warning

    The dynamic schema feature is only supported in Built-In mode.

  2. Select Built-in as the Property Type. Select the DB Version from the corresponding list.

  3. Next to Host, enter the database server IP address. Next to Port, enter the listening port number of the database server.

  4. Enter your authentication data in the Username and Password fields. Set the Schema type as Built-in and click Edit schema to define the dynamic schema.

    The schema editor opens:

  5. Click the [+] button to add a row to the schema. Under Column, click in the fields to enter the corresponding column names.

  6. Click the field under Type to define the type of data. Click the arrow and select Dynamic from the list and click OK to close the schema editor.

    Warning

    Under Type, the dynamic column type must be set as Dynamic.

  7. Next to the Table Name field, click the [...] button to select the database table of interest.

    A dialog box displays a tree diagram of all the tables in the selected database:

  8. Click the table of interest and then click OK to close the dialog box. Set the Query Type as Built-In.

  9. In the Query box, enter the query required to retrieve all of the columns from the table.

    Warning

    In the SELECT statement it is necessary to use the * wildcard character, to retrieve all of the columns from the selected table.

Configuring the tMap component

  1. Click tMap to open its Basic Settings view in the Component tab. Click [...] next to Map Editor to map the column from the source file.

  2. Drop the column defined as dynamic from the input schema on the left onto the output schema on the right.

    The column dropped on the output schema retains its original values.

    Warning

    The dynamic column must be mapped on a one to one basis and cannot undergo any transformations. It cannot be used in a filter expression or in a variables section. It cannot be renamed in the output table and cannot be used as a join condition.

  3. Click OK to close the Map Editor.

Configuring the output component

  1. Double-click tFileOutputDelimited to set its Basic Settings in the Component tab.

  2. Next to the File Name field, click the [...] button to browse your directory to where you want to save the output file, then enter a name for the file.

  3. Select the Include Header check box to retrieve the column names as well as the data.

Job execution

Save the Job and press F6 to run it.

The output file is written with all the column names and corresponding data, retrieved from the database via the dynamic schema:

Note

The Job can also be run in the Traces Debug mode, which allows you to view the rows as they are written to the output file, in the workspace.

For further information about defining and mapping dynamic schemas, see Talend Studio User Guide.

For related scenarios, see: