[TALEND*] Using Contexts To Manage Multiple DB Environments

 

Using Contexts to Manage Multiple DB Environments

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 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”

Give your new database connection a name – “ProductsDB”

Click Next

 

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.

Next click on the “Export to context” button

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

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

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

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

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”

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

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:

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

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

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

Click “Yes” to propagate modifications to all jobs:

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”.

 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”

Locate the “tMysqlConnection” component in the Palette

Drag the component onto the job design  window

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

 

Click on the button that looks like this:

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

 

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

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

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.