August 17, 2023

tAmazonMysqlConnection – Docs for ESB 5.x

  2. Talend for ESB Docs
  3. Docs 5.x
  4. tAmazonMysqlConnection – Docs for ESB 5.x

tAmazonMysqlConnection

tAmazonMysqlConnection_icon32_white.png

tAmazonMysqlConnection Properties

Component family

Cloud/AmazonRDS/MySQL

  

Function

Opens a connection to the database for a current
transaction.

Purpose

This component is used to open a connection to the specified database that can then be reused in the subsequent subjob or subjobs.

Basic settings

Property type

Either Built-in or Repository.

Since version 5.6, both the Built-In mode and the Repository mode are
available in any of the Talend solutions.

 

 

Built-in: No property data stored
centrally.

 

 

Repository: Select the repository
file in which the properties are stored. The fields that follow are
completed automatically using the data retrieved.

 

DB Version

MySQL 5 is available.

 

Host

Database server IP address.

 

Port

Listening port number of DB server.

 

Database

Name of the database.

 

Additional JDBC parameters

Specify additional connection properties for the DB connection you
are creating.

 

Username and
Password

DB user authentication data.

To enter the password, click the […] button next to the
password field, and then in the pop-up dialog box enter the password between double quotes
and click OK to save the settings.

 

Use or register a shared DB Connection

Select this check box to share your connection or fetch a
connection shared by a parent or child Job. This allows you to share
one single DB connection among several DB connection components from
different Job levels that can be either parent or child.

Warning

This option is incompatible with the Use dynamic job
and Use an independent process to run subjob options of
the tRunJob component. Using a shared connection
together with a tRunJob component with either of these
two options enabled will cause your Job to fail.

Shared DB Connection Name: set or
type in the shared connection name.

Advanced settings

Auto Commit

Select this check box to commit any changes to the database automatically upon the
transaction.

With this check box selected, you cannot use the corresponding commit component to commit
changes to the database; likewise, when using the corresponding commit component, this check
box has to be cleared. By default, the auto commit function is disabled and changes must be
committed explicitly using the corresponding commit component.

Note that the auto commit function commits each SQL statement as a single transaction
immediately after the statement is executed while the commit component does not commit only
until all of the statements are executed. For this reason, if you need more room to manage
your transactions in a Job, it is recommended to use the commit component.

 

tStatCatcher Statistics

Select this check box to collect log data at the component
level.

Global Variables

ERROR_MESSAGE: the error message generated by the
component when an error occurs. This is an After variable and it returns a string. This
variable functions only if the Die on error check box is
cleared, if the component has this check box.

A Flow variable functions during the execution of a component while an After variable
functions after the execution of the component.

To fill up a field or expression with a variable, press Ctrl +
Space to access the variable list and choose the variable to use from it.

For further information about variables, see Talend Studio
User Guide.

Usage

This component is more commonly used with other tAmazonMysql*
components, especially with the tAmazonMysqlCommit and tAmazonMysqlRollback components.

Log4j

The activity of this component can be logged using the log4j feature. For more information on this feature, see Talend Studio User
Guide.

For more information on the log4j logging levels, see the Apache documentation at http://logging.apache.org/log4j/1.2/apidocs/org/apache/log4j/Level.html.

Limitation

n/a

Scenario: Inserting data in mother/daughter tables

The following Job is dedicated to advanced database users, who want to carry out
multiple table insertions using a parent table id to feed a child table. As a
prerequisite to this Job, follow the steps described below to create the relevant tables
using an engine such as innodb.

Setting up the Job

  1. In a command line editor, connect to your Mysql server. Once connected to
    the relevant database, type in the following command to create the parent
    table: create table f1090_mum(id int not null auto_increment, name
    varchar(10), primary key(id)) engine=innodb.

  2. Then create the second table: create table baby (id_baby int not null,
    years int) engine=innodb.

    Back into Talend Studio, the
    Job requires seven components including tAmazonMysqlConnection and tAmazonMysqlCommit.

    Use_Case_tAmazonMysqlConnection1.png

  3. Drag and drop the following components from the Palette: tFileList,
    tFileInputDelimited, tMap, tAmazonMysqlOutput (x2).

  4. Connect the tFileList component to the
    input file component using an Iterate link
    as the name of the file to be processed will be dynamically filled in from
    the tFileList directory using a global
    variable.

  5. Connect the tFileInputDelimited
    component to the tMap and dispatch the flow
    between the two output AmazonMysql DB components. Use a Row link for each for these connections
    representing the main data flow.

  6. Set the tFileList component properties,
    such as the directory name where files will be fetched from.

  7. Add a tAmazonMysqlConnection component
    and connect it to the starter component of this job, in this example, the
    tFileList component using an OnComponentOk link to define the execution
    order.

Setting up the DB connection

In the tAmazonMysqlConnection Component view,
set the connection details manually or fetch them from the Repository if you
centrally stored them as a Metadata DB connection entry. For more information
about Metadata, see Talend Studio User
Guide.

Configuring the input component

  1. On the tFileInputDelimited component’s
    Basic settings panel, press Ctrl+Space bar to access the variable list. Set
    the File Name field to the global variable:
    tFileList_1.CURRENT_FILEPATH

    Use_Case_tMysqlConnection2.png

  2. Set the rest of the fields as usual, defining the row and field
    separators according to your file structure. Then set the schema manually
    through the Edit schema feature or select
    the schema from the Repository. Make sure the data type is correctly set, in
    accordance with the nature of the data processed.

Configuring the tMap component

  1. In the tMap Output area, add two output
    tables, one called mum for the parent table, the second called baby, for the
    child table.

  2. Drag the Name column from the Input area, and drop it to the mum table. Drag the
    Years column from the Input area and drop it to the baby table.

    Use_Case_tMysqlConnection3.png

    Make sure the mum table is on the top of the baby table as the order is
    determining for the flow sequence hence the DB insert to perform
    correctly.

  3. Then connect the output row link to distribute correctly the flow to the
    relevant DB output component.

Configuring the output component

  1. In each of the tAmazonMysqlOutput
    components’ Basic settings panel, select
    the Use an existing connection check box to
    retrieve the tAmazonMysqlConnection
    details.

    Use_Case_tAmazonMysqlConnection4.png

  2. Set the Table name making sure it
    corresponds to the correct table, in this example either
    f1090_mum or
    f1090_baby.

    There is no action on the table as they are already created.

  3. Select Insert as Action on data for both output components. Click on
    Sync columns to retrieve the schema set
    in the tMap.

  4. Go to the Advanced settings panel of each
    of the tAmazonMysqlOutput components.
    Notice that the Commit every field will get
    overridden by the tAmazonMysqlCommit.

    Use_Case_tAmazonMysqlConnection2.png

  5. In the Additional columns area of the DB
    output component corresponding to the child table
    (f1090_baby), set the id_baby column so that it
    reuses the id from the parent table. In the SQL
    expression     field type in: ‘(Select Last_Insert_id())’.

    The position is Before and the Reference column is years.

Configuring the tAmazonMysqlCommit component

  1. Add the tAmazonMysqlCommit component to
    the design workspace and connect it from the tFileList component using a OnComponentOk connection in order for the Job to terminate
    with the transaction commit.

  2. On the tAmazonMysqlCommit
    Component view, select in the list the
    connection to be used.

Job execution

Save your Job and press F6 to execute it.

Use_Case_tMysqlConnection5.png

The parent table id has been reused to feed the
id_baby column.

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