August 15, 2023

Scenario: Writing dynamic columns from a source file to a database – Docs for ESB 6.x

Scenario: Writing dynamic columns from a source file to a database

This scenario applies only to a subscription-based Talend solution.

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

  • Drop a tFileInputDelimited, a tMap and a tMysqlOutput component onto the workspace.

dynamic_schema_connections.png
  • Link tFileInputDelimited to tMap using a Row >
    Main
    connection.

  • Link tMap to tMysqlOutput using a Row > *New Output*
    (Main)
    connection.

  • Double-click tFileInputDelimited to open its
    Basic Settings view in the Component tab.

dynamic_schema_tFileInputDelimited_BasicSettings.png
Warning:

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

  • Select Built-In from the Property Type list.

  • Click the […] button next to the File name/Stream field and browse to the input
    file.

  • Enter the characters you want to use as separators next to the Row Separator and Field
    Separator
    fields.

  • Click Edit Schema to define the source file
    schema.

    The [Edit Schema] dialog box opens.

dynamic_schema_tFileInputDelimited_Edit_Schema.png
  • Add as many rows as required or delete rows using the

    plus_button.png

    and

    delete_button.png

    buttons.

  • Modify the order of the columns using the

    downward_arrow.png

    and

    upward_arrow.png

    buttons.

  • Under Column, enter the names of each known
    column on separate rows.

  • In the last row, under Column, enter a name
    for the dynamic column.

  • Under Type, click the field to define the
    type of data in the corresponding column.

    Click the arrow to select the correct data type.

Warning:

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

Warning:

The dynamic column must be defined in the last row of the schema.

  • Click OK to close the dialog box when you
    have finished defining the source schema.

  • Click tMap to open its Basic Settings view in the Component tab.

dynamic_schema_tMap_Basic_Settings.png
  • Click […] next to Map Editor to map the columns from the source file.

dynamic_schema_tMap_Edit1.png
  • On the toolbar on top of the Output Panel on
    the top right of the window, click the

    plus_button.png

    button.

    The [Add an Output] schema dialog box
    appears.

dynamic_schema_Add_An_Output.png
  • Next to New output, enter a name for the
    output schema.

  • Click OK to close the dialog box.

  • Using the Ctrl + click technique, highlight
    all off the column names in the input schema on the left and drop them onto the
    output schema.

    The columns dropped on the output columns retain their original values and
    they are automatically mapped on a one to one basis.

dynamic_schema_tMap_Edit2.png
  • In the output schema, click the relevant row under Expression if you want to use the Expression Builder to set advanced parameters for the
    corresponding column in the output.

  • Click the […] button which appears to open
    the Expression Builder and set the parameters
    as required.

For further information about using the Expression
Builder
, see
Talend Studio

User Guide.

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.

  • Click OK to close the Map Editor.

  • Double click tMysqlOutput to set its
    Basic Settings in the Component tab.

dynamic_schema_tMysqlOutput_Basic_Properties.png
  • 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.

  • Next to Action on table, select the required
    action.

  • Next to Action on data, select the required
    action.

  • Set the Schema type as
    Built-in
    and click Edit schema
    to modify the schema if required.

  • Press F6 to run the Job.

    The table is written to the MySQL database along with the data and the column
    names of the previously unknown columns:

dynamic_schema_mysql_database.png
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 an example of how to write dynamic columns to an output file, see Scenario 5: Writing dynamic columns from a database to an output file.


Document get from Talend https://help.talend.com
Thank you for watching.
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x