Talend Tutorials

Managing Multiple Db Env

Managing Multiple Db Env

When connecting to a database in Talend Open Studio it is very important to utilize standard best practices that will enable you to access this connection during job design and to manage multiple database environments such as Dev, QA and Prod.

This tutorial will walk you through the steps required to maintain these best practices using the example of a MySQL 3 server environment – dev, qa, prod.

Step 1: Create a db connection

In the repository window to the left of screen, right click on “Db Connections” under “Metadata” and select “create Connection”

image 1

Give your new database connection a name – “ProductsDB”

Click Next

image 2

On the following screen, complete all the necessary fields to connect to your “Test” MySQL database instance.

If you are using any other database system, you can configure it here instead of MySQL.

Once you have completed the connection settings, click the “Check” button – you should receive a message notifying you that the connection was successful. If you get a failure message, contact your Database Administrator and ensure you have the connection parameters.

image 3

Next click on the “Export to context” button

In the following screen, enter any descriptive information if you like, then press “Next”

image 4

On the following screen, select the “Values as table” tab

image 5

Near the top right, click on the button that looks like this:
image 6

Now we have the ability to edit the contexts (dev/qa/prod) for our database connection. image 7

First, select the “Default” context and click “Edit”

Rename the “Default” context to “dev” and click ok

Next click the “New” button and create a new context called “qa”

image 8

Repeat this step to create a final context called “prod”

image 9

Click “OK”

Now you will see that the database connection parameters have been duplicated for all 3 contexts. Edit these in this screen so that each context is utilizing the correct connection parameters for its assigned environment. See my example below:

image 10

Once you have completed the details for each context, click “Finish”

Now on the following screen click “Finish” to finalize your connection setup.

image 11

If you receive this notification then just select “dev” as your default:

image 12

Click “Yes” to propagate modifications to all jobs:

image 13

Now if you click on “Contexts in the “Repository” panel you will notice there is a “ProductsDB” item listed. You can edit this to make any changes to the global contexts settings for the database at a future date.

You will notice there is also a “Db Connection” repository item called “ProductsDB”.

image 14

Step 2: Utilize connection parameters as contexts

Next we are going to walk through the basic ways to utilize your db connection during your job design.

Right click “Job Designs” and create a new job called “connect_db_repository”

image 15

Locate the “tMysqlConnection” component in the Palette

image 16

Drag the component onto the job design window

Next select the “Contexts” tab near the bottom middle of the screen.

image 17

Click on the button that looks like this: image 18

Select the “ProductsDb” context group then click “OK”

image 19

On the following screen, click the check box to ensure that all contexts are imported into the job, then click “OK”.

image 20

This is a very simple job but now you are ready to see how we can deploy this job to connect to either of our 3 database environments upon execution.

Step 3: Run job using different contexts

Select the “Run” tab near the bottom middle of the screen

Near the top right of the “Run” tab you will see a drop down that lists “dev” and the context parameters.

You can select which ever context you would like to use from this list when running the job in Talend Open Studio.

Once you have selected your context, then click the “Run” button

image 21

Nothing much will happen in this example because all we are doing is connecting to a database, but now you have the knowledge of how to use context groups and maintain this as a best practice for all your database connections in Talend.

Stay tuned for our next tutorial which will show you how to export your jobs from Talend and to specify the contexts from the command line.

Looking For More Talend Resources and Help?
Ask An Expert