Scenario: 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.
Drop the following components from the Palette onto the design workspace: tRowGenerator, tMap and
Connect tRowGenerator, tMap, and tMysqlOutput using
the Row Main link.
In the design workspace, select tRowGenerator to display its Basic
Click the Edit schema three-dot button to
define the data to pass on to the tMap
component, two columns in this scenario, name and
Click OK to close the dialog box.
Click the RowGenerator Editor three-dot
button 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,
the first column and
getrandomDatefor 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
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 three-dot button to open the
[Repository content] dialog box and select
the correct DB connection. The connection details display automatically in the
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 three-dot button next to the Table
field and select the table to be altered, Dates in this
On the Action on table list, select
Drop table if exists and create, select
Insert on the Action on
If needed, click Sync columns to synchronize
with the columns coming from the tMap
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
-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 1in this scenario.
-Select Replace on the Position list.
-Enter Random_date1 on the Reference column list.
For this job we duplicated the random_date_1 column in the DB
table before replacing one instance of it with the
One_Month_Later column. The aim of this workaround was to
be able to view upfront the modification performed.
Save your job and press F6 to execute
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.
See also Procedure of
the tDBOutput component.