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.
Drop a tMysqlInput, a tMap and a tFileOutputDelimited component onto the workspace.
Link tMysqlInput to tMap using a Row > Main connection.
Link tMap to tFileOutputDelimited using a Row > *New Output* (Main) connection.
Data source and dynamic columns
Double-click tMysqlInput to open its Basic Settings view in the Component tab.
The dynamic schema feature is only supported in Built-In mode.
Select Built-in as the Property Type.
Select the DB Version from the corresponding list.
Next to Host, enter the database server IP address.
Next to Port, enter the listening port number of the database server.
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:
Click the [+] button to add a row to the schema.
Under Column and Db Column, click in the fields to enter the corresponding column names.
Click the field under Type to define the type of data.
Click the arrow and select Dynamic from the list.
Under Type, the dynamic column type must be set as Dynamic.
Click OK to close the schema editor.
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:
Click the table of interest and then click OK to close the dialog box.
Set the Query Type as Built-In.
In the Query box, enter the query required to retrieve all of the columns from the table.
In the SELECT statement it is necessary to use the * wildcard character, to retrieve all of the columns from the selected table.
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.
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.
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.
Click OK to close the Map Editor.
Double-click tFileOutputDelimited to set its Basic Settings in the Component tab.
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.
Select the Include Header check box to retrieve the column names as well as the data.
Save the Job.
The results below can be found after F6 is pressed to run the Job.
As shown above, the output file is written with all the column names and corresponding data, retrieved from the database via the dynamic schema:
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: