Inserting a column and altering data using tMysqlOutput

This Java scenario is a three-component Job that aims at creating random data using a tRowGenerator, duplicating a column to be altered using the tMap component, and eventually altering the data to be inserted based on an SQL expression using the tMysqlOutput component.

For more technologies supported by Talend, see Talend components.

• Drop the following components from the Palette onto the design workspace: tRowGenerator, tMap and tMySQLOutput.

• Connect tRowGenerator, tMap, and tMysqlOutput using the Row Main link.

• In the design workspace, select tRowGenerator to display its Basic settings view.

• Click the [...] button next to Edit schema to define the data to pass on to the tMap component, two columns in this scenario, name and random_date.

• Click OK to close the dialog box.

• Click the [...] button next to RowGenerator Editor to open the editor and define the data to be generated.

• Click in the corresponding Functions fields and select a function for each of the two columns, getFirstName for the first column and getrandomDate for the second column.

• In the Number of Rows for Rowgenerator field, enter 10 to generate ten first name rows and click Ok to close the editor.

• Double-click the tMap component to open the Map editor. The Map editor opens displaying the input metadata of the tRowGenerator component.

• In the Schema editor panel of the Map editor, click the plus button of the output table to add two rows and define the first as random_date and the second as random_date1.

In this scenario, we want to duplicate the random_date column and adapt the schema in order to alter the data in the output component.

• In the Map editor, drag the random_date row from the input table to the random_date and random_date1 rows in the output table.

• Click OK to close the editor.

• In the design workspace, double-click the tMysqlOutput component to display its Basic settings view and set its parameters.

• Set Property Type to Repository and then click the [...] button to open the Repository content dialog box and select the correct DB connection. The connection details display automatically in the corresponding fields.

  Information noteNote:

  If you have not stored the DB connection details in the Metadata entry in the Repository, select Built-in on the property type list and set the connection detail manually.

• Click the [...] button next to the Table field and select the table to be altered, Dates in this scenario.

• On the Action on table list, select Drop table if exists and create, select Insert on the Action on data list.

• If needed, click Sync columns to synchronize with the columns coming from the tMap component.

• Click the Advanced settings tab to display the corresponding view and set the advanced parameters.

• In the Additional Columns area, set the alteration to be performed on columns.

  In this scenario, the One_month_later column replaces random_date_1. Also, the data itself gets altered using an SQL expression that adds one month to the randomly picked-up date of the random_date_1 column. ex: 2007-08-12 becomes 2007-09-12.

  -Enter One_Month_Later in the Name cell.

  -In the SQL expression cell, enter the relevant addition script to be performed, "adddate(Random_date, interval 1 month)" in this scenario.

  -Select Replace on the Position list.

  -Enter Random_date1 on the Reference column list.

• Save your Job and press F6 to execute it.

The new One_month_later column replaces the random_date1 column in the DB table and adds one month to each of the randomly generated dates.