Kettle PDI, Named Parameters, and Generic Database Connections

Kettle / PDI Generic SQL Server JTDS database with parameters and a Generic SQL Server Database Connection

The purpose of using named parameters is to abstract out what parts of a database connection change, from one environment to another, and make that part of a configuration that can be maintained elsewhere.  For example,  for a given ETL to extract Appointment data, it might need to connect to different database servers, databases, and use different credentials in Development, QA, and Production.

This example makes the database connection more generic than necessary for that purpose, just as a learning exercise.

First… Define a database connection and use replacement variables for all of the parameters. In this example, the following parameters are used.

  • DB_HOST
  • DB_DBNAME
  • DB_PORT
  • DB_USER
  • DB_PW

For demonstration purposes, the generic database connection is named “GENERIC_JTDS” and uses the Microsoft SQL Server JTDS database driver.

2013-12-17_0935-generic-jtds-conn

 

In the job’s settings, define the same parameters it needs to connect to the database.

2013-12-16_1343-db-var-test-job-named-parameters

As an example, I have a created a job with a “Check Database Connection” component. “Check Db Connections direct VAR” uses the  “GENERIC JTDS”  database connection.

2013-12-16_1343-db-var-test-job

 

Here are the settings for the step “Check Db connections direct VAR”
2013-12-16_1344-db-conn-test-use-conn

 

Now…. I can pass in all the attributes / parameters for any SQL Server connection, into the test Job “var-conn-generic.kjb”,  into Kitchen.  Or, I can specify the default parameter values when I run the job from Spoon.

To run the job from the Spoon GUI, run it and specify the values for the parameters. 2013-12-17_0917-execute-job

 

Command Line, using Kitchen.sh

./kitchen.sh -file=/example/data-integration/var-conn-generic-test.kjb
 -param:DB_HOST="localhost"
 -param:DB_PORT="1433"
 -param:DB_DBNAME="EXAMPLE_DB"
 -param:DB_USER="EXAMPLE_USER"
 -param:DB_PW="Encrypted PW_ENCRYPTED_WITH_encr.sh"

Example Output

2013/12/16 13:50:45 - Kitchen - Start of run.
2013/12/16 13:50:47 - var-conn-generic-test - Start of job execution
2013/12/16 13:50:47 - var-conn-generic-test - Starting entry [Check Db connections direct VAR]
2013/12/16 13:50:47 - var-conn-generic-test - Starting entry [Write To Log direct VAR]
2013/12/16 13:50:47 - Params Received - DB_HOST ${DB_HOST}
2013/12/16 13:50:47 - Params Received - DB_USER ${DB_USER}
2013/12/16 13:50:47 - Params Received - DB_PW ${DB_PW}
2013/12/16 13:50:47 - var-conn-generic-test - Finished job entry [Write To Log direct VAR] (result=[true])
2013/12/16 13:50:47 - var-conn-generic-test - Finished job entry [Check Db connections direct VAR] (result=[true])
2013/12/16 13:50:47 - var-conn-generic-test - Job execution finished
2013/12/16 13:50:47 - Kitchen - Finished!
2013/12/16 13:50:47 - Kitchen - Start=2013/12/16 13:50:45.938, Stop=2013/12/16 13:50:47.546
2013/12/16 13:50:47 - Kitchen - Processing ended after 1 seconds.

Leave a Reply

Your email address will not be published. Required fields are marked *