Updating data in a database table - 6.1

Talend Open Studio for Data Integration Getting Started Guide

EnrichVersion
6.1
EnrichProdName
Talend Open Studio for Data Integration
task
Design and Development
EnrichPlatform
Talend Studio

This example describes a two-component Job that updates data in a MySQL table according to that in a delimited file.

Dropping and link components

  1. Drop tFileInputDelimited and tMysqlOutput from the Palette onto the design workspace.

  2. Connect the two components together using a Row Main link.

Configuring the input component

  1. Double-click tFileInputDelimited to display its Basic settings view and define the component properties.

  2. From the Property Type list, select Repository if you have already stored the metadata of the delimited file in the Metadata node in the Repository tree view. Otherwise, select Built-In to define manually the metadata of the delimited file.

    For more information about storing metadata, see Talend Studio User Guide.

  3. In the File Name field, click the three-dot button and browse to the source delimited file that contains the modifications to propagate in the MySQL table.

    In this example, we use the customer_update file that holds four columns: id, CustomerName, CustomerAddress and idState. Some of the data in these four columns is different from that in the MySQL table.

    id;CustomerName;CustomerAdddress;idState
    858;Froggy's Gourmet Catering;1831 Beverly Place #9-11D;4
    859;Dependable Plumbing and Sewver;1550 Ridge Rd.;25
    860;Lickmen Restoration;1235 Easton Rd.;40
    861;Acturial Enterprises Ltd.;3148 Cottonwood Ct.;18
    862;Rythmics Ltd.;857 Woodbine Rd;30
    863;Acturial Enterprises Ltd.;1482 Concorde Circle;48
    864;Crosstracks Car Wash;218 Oakridge Ave.;39
    865;Meonits & Mogogni Inc.; 616 Cobblestone Cir.;17
    866;Foy Aviation;2220 Grant Blvd.;50
    867;Ebert Music Center;12 Broadview Lane;29
    868;janice Mann Accounting Service;1660 Park Ave.;9
    869;Johnson, Erico & Co CPA's;2922 Twin Oaks Drive;40
    870;Corbins;Rodriguez, & Savocchi;115 Pleasant Ave.;18
    871;Nina's Snow Plowing;3385 University Ave.;20
    872;Darcy Frame and Matting Servic;1101 Deerfield Place;47
    873;Marks, Kaplan and Jones Ltd.;1949 Cloverdale Rd.;9
  4. Define the row and field separators used in the source file in the corresponding fields.

  5. If needed, set Header, Footer and Limit.

    In this example, Header is set to 1 since the first row holds the names of columns, therefore it should be ignored. Also, the number of processed lines is limited to 2000.

  6. Click the [...] button next to Edit Schema to open a dialog box where you can describe the data structure of the source delimited file that you want to pass to the component that follows.

  7. Select the Key check box(es) next to the column name(s) you want to define as key column(s).

    Note

    It is necessary to define at least one column as a key column for the Job to be executed correctly. Otherwise, the Job is automatically interrupted and an error message displays on the console.

Configuring the output component

  1. In the design workspace, double-click tMysqlOutput to open its Basic settings view where you can define its properties.

  2. Click Sync columns to retrieve the schema of the preceding component. If needed, click the three-dot button next to Edit schema to open a dialog box where you can check the retrieved schema.

  3. From the Property Type list, select Repository if you have already stored the connection metadata in the Metadata node in the Repository tree view. Otherwise, select Built-In to define manually the connection information in the corresponding fields: Host, Port, Database, Username and Password.

    For more information about storing metadata, see Talend Studio User Guide.

  4. In the Table field, enter the name of the table to update.

  5. From the Action on table list, select the operation you want to perform, Default in this example since the table already exists.

  6. From the Action on data list, select the operation you want to perform on the data, Update in this example.

Saving and executing the Job

  1. Press Crtl+S to save your Job.

  2. Press F6 or click Run on the Run tab to execute the Job.

    The MySQL table customers has been modified according to the delimited file.