Scenario 2: Sharing a database connection between a parent Job and child Job - 6.3

Talend Open Studio for Big Data Components Reference Guide

EnrichVersion
6.3
EnrichProdName
Talend Open Studio for Big Data
task
Data Governance
Data Quality and Preparation
Design and Development
EnrichPlatform
Talend Studio

This scenario shows how a database connection is shared between a parent Job and a child Job. The parent Job first calls the child Job to write some randomly generated data to a MySQL database, and then reads the data from the MySQL database and displays it on the console. A connection to the MySQL database is set up only once and used in both Jobs.

Setting up the child Job

Adding and linking components

  1. Add the following components by typing the component names on the design workspace or dropping them from the Palette:

    • a tMysqlConnection component, to open a connection to the MySQL database,

    • a tRowGenerator, to generate random input data,

    • a tMysqlOutput component, to write data to the MySQL database.

  2. Connect the tRowGenerator component to the tMysqlOutput component using a Row > Main connection.

  3. Connect the tMysqlConnection component to the tRowGenerator using a Trigger > OnSubjobOk connection.

Configuring the database connection

  1. Double-click the tMysqlConnection component to open its Basic settings view.

  2. With the Property Type set to Built-In, specify the connection details in the relevant fields, including:

    • the host name or IP address of your database server

    • the listening port number

    • the database name

    • the user name and password for your database authentication.

    If you have stored your connection details under the Metadata node in the Repository tree view, you can simply drop your centralized metadata item onto the tMysqlConnection component. For information on how to centralize a database connection, see the chapter on managing metadata of the Talend Studio User Guide.

  3. Select the Use or register a shared DB Connection check box so that the database connection open by this component can be shared across different Jobs. Then, enter a name for the shared connection between double quotes in the Shared DB Connection Name field, shared_mysql_connection in this example.

Configuring the input data

  1. Double-click the tRowGenerator component to open the Row Generator editor.

  2. Click the [+] button to add four columns and set their properties:

    • id, type Integer, 2 characters long.

    • firstName, type String, 15 characters long

    • lastName, type String, 15 characters long

    • city, type String, 15 characters long

  3. Define the function for each column:

    • For the id column, select Numeric.sequence from the Function list to generate sequence numbers.

    • For the firstName column, select TalendDataGenerator.getFirstName from the Function list to generate random first names.

    • For the lastName column, select TalendDataGenerator.getLastName from the Function list to generate random family names.

    • For the city column, select TalendDataGenerator.getUsCity from the Function list to generate random city names.

  4. In the Number of Rows for RowGenerator field, specify the number of data rows you want to generate, 10 in this example.

  5. Click the Preview button on the Preview tab to validate the generator settings.

  6. When done, click OK to close the editor and click Yes when prompted to propagate the schema to the next component.

Configuring the database output

  1. Double-click the tMysqlOutput component to open its Basic settings view.

  2. Select the Use an existing connection check box and, in case you have more than one connection component in the Job, select the connection component to be used from the Component List drop-down list.

  3. In the Table field, enter the name of the database table you are going to write data to, customers in this example.

  4. From the Action on table list, select the Drop table if exists and create option to ensure a clean table is created.

  5. From the Action on data list, select Insert.

  6. Click the [...] button next to Edit schema to check the output schema. If needed, click Sync columns to retrieve the schema from the preceding component.

  7. Press Ctrl+S to save the Job.

Setting up the parent Job

Adding and linking components

  1. Add the following components by typing the component names on the design workspace or dropping them from the Palette:

    • a tRunJob component, to call the child Job,

    • a tMysqlConnection component, to open the MySQL database connection,

    • a tMysqlInput component, to read the data written to the MySQL database by the child Job.

    • a tLogRow component to display the data on the console,

    • a tMysqlCommit component to commit data upon transation and close the database connection.

  2. Connect the tRunJob component to the tMysqlConnection compnent using a Trigger > OnSubjobOk connection.

  3. Connect the tMysqlConnection component to the tMysqlInput component using a Trigger > OnSubjobOk connection.

  4. Connect the tMysqlInput component to the tLogRow component using a Row > Main connection.

  5. Connect the tMysqlInput component to the tMysqlCommit component using a Trigger > OnSubjobOk connection.

Configuring the components

  1. Double-click the tRunJob component to open its Basic settings view.

    If a child Jod has been already specified in the component, you can open its Basic settings view by right-clicking it and selecting Settings from the contextual menu, or by clicking the component and then selecting the Component tab.

  2. Click the [...] button next to the Job field and select the child Job from the [Repository Content] dialog box. Leave all the other parameters as default.

  3. In the Basic settings view of the tMysqlConnection component, select the Use or register a shared DB Connection check box. Then, in the Shared DB Connection Name field, enter the name of the shared connection defined in the child Job, shared_mysql_connection in this example.

    Leave all the other parameters blank or as default.

  4. Double-click the tMysqlInput component to open its Basic settings view.