Saturday 9 March 2013

Sharing Database connection with Child Jobs in Talend Open Studio

Today, I am going to write on sharing single Database connection with child sub jobs in Talend Open Studio /Jasper ETL.Typical ETL/Data transformation Talend job consists of various child jobs as well. Most of the child jobs may interact with the database to retrieve or update data. Child job makes a separate connection with database.

Let’s take a scenario where we have a Talend Job which have 3 sub jobs. Now every child job interacts with a database to retrieve some information. Hence when the Talend Job will execute then it will make 3 different database connections to retrieve the information.

Lets take another scenario where in Talend Job we are retrieving list of files using tFileList component and for every file retrieved, it is iterating a sub job which makes a database connection. In this case if we retrieve 20 files from tFileList then it may create 20 database connection. Hence it is necessary to create a single DB connection and make it available to the child job.

How to share a DB connection and share it across child sub jobs


Lets create a sample Talend Job to demonstrate the same.

1. Drag tMySQLConnection component from Palette pane onto Job designer pane.
 

2. Provide the Database credentials as per your requirements:


 



You can also provide these detals in the context variables. for more details on Context variable click here.

3. This is the most important step: Now to share this connection across the sub jobs select the checkbox “Use or register a shared DB connection”.

Once you check the box, provide any unique name to this shared connection in the “Shared DB Connection Name” text box.




4. Save this Job and create another Talend Job which will be used as a Subjob in this Job.

Create a Sub Job : This Subjob will retrieve data from a database table and display it in run console.

1. Drag tMySQLConnection component from Palette pane onto Job designer pane.

2. This time we are not going to provide the connection parameters details like Host, Username, Port,Database, Username and Password. We will simply use the same connection created in first Job.

To use the same connection, open the connection parameters of the tMySQLConnection component and click on the checkbox “Use or register a shared DB connection”.

Provide the same name ("MySQL_SHARED_DB")  to this shared connection in the “Shared DB Connection Name” text box.


 


3. Drag tMySQLInput component and tLogRow components onto Job designer.

Create a metadata schema of your Table and use it in tMysqlInput. Click here, For more information of creating schema of database table. In my case I am going to use the employee table with below schema.


Connection Parameters of tMySQLInput component:

Now, do not provide the connection parameters details like Host, Username, Port,Database, Username and Password. Just click on checkbox, Use existing connection.

This will make sure that you will use the database connection created in Step 2. of Child Subjob.


4 Right Click on tMySQLConnection component and Select On Subjob OK and connect it to tMySQLInput.

5. Similarly, Right Click tMySQLInput and Select Row → Main and connect it to tLogRow.


Save this Subjob and we will use it in Main Job.

Now open the Main job and drag the Job we have just created and click on tMySQLConnection and Select OnSubJobOk and connect it to Subjob that we have created.


This completes our main job. Its time to execute it and see how the database connection created in main job and use the same connection in subjob.


Summary:
1. To share the DB connection, provide the database credential and click on “Use or register a shared DB connection” in tMYSQLConnection component and provide the name to this shared connection in the “Shared DB Connection Name” text box.

2. Now, if you want to use the same connection in the child box, click on “Use or register a shared DB connection” in tMYSQLConnection component and provide the same name of the shared connection in the “Shared DB Connection Name” text box in the Child box.

You may also like to read..

5 comments:

  1. Nice post, Thanks. I am just starting out with Talend so the more information out there the better.

    ReplyDelete
  2. Very Nice post Vikram. Please add some Talend ESB related post.

    ReplyDelete
  3. Thanks Vikram for this post,
    I was able to use same parent job tmssqlconnection in child job

    Can you please check, as all images in ur post are not visaible/ hidden.

    Thanks for such a nice Post

    Sachin D

    ReplyDelete
  4. thank vikram your post helps alot

    ReplyDelete