Expert Consultancy from Yellow Pelican

Reusable Database Connection Job

A site about Talend

Reusable Database Connection Job

This tutorial explains how to create a reusable Job for establishing a shared connection to a MySQL Database. The principles employed here may be used for any database vendor, and many other types of data source. This tutorial is part of our series on writing reusable Jobs that will support you in your Job development and help you towards your goal of writing maintainable, reliable and reusable code that you write once and use many.

Download this Tutorial

You may Download this Tutorial and import it in to a Talend Project of your choice. This tutorial supports Talend 5.2.2 and above. This tutorial is work in progress. Please check back later for an updated tutorial. This tutorial was last updated 7th April 2014.

Download

Prerequisites

The reusable Job LibMySQLSharedConnection makes use of the Job LibContextReader. For more information on LibContextReader, read our tutorial Reusable Context Load Job. Everything that you need is included in the download.

You will need access to a MySQL Database. You may, of course, customise LibMySQLSharedConnection to work with a database vendor of your choice; however, you may find it helpful to get this tutorial running with MySQL, first.

The example Job ReadMySQLDatabase requires access to some specific tables, using the specified accounts and schemas. You may, of course, amend these to suit the database that is available to you. If you are able to create the accounts, schemas and tables that are used by this example, then this may be beneficial to you understanding this Job, quickly.

Example Job

The following screenshot shows the example Job, ReadMySQLDatabase. This is a simple Job to create, that establishes two separate, shared, MySQL Database Connections. From one of these connections, a MySQL catalogue table is read, with some simple mapping being performed. From the second connection, two custom tables are read. Data is retieved using tMysqlInput Components and all output is displayed using tLogRow Components.

If you download this tutorial, you should be able to run the Job; however, it is unlikely that you will have the correct database connections, schemas or tables. This tutorial will explain how to create these so that you can run this tutorial "as is", or you may choose to modify the tutorial to suit your own environment.

The key aspect to understanding this tutorial is that this sample Job employs the Job LibMySQLSharedConnection to stablish a shared MySQL Database Connection. This Job, in turn, uses LibContextReader to load the MySQL connection parameters. LibMySQLSharedConnection may be used to establish multiple MySQL connections within the same parent Job, and you may use the principles shown here, to connect to the database vendor of your choice.

Image 1

LibMySQLSharedConnection

The purpose of the Job LibMySQLSharedConnection Is to establish a connection to your database and to make this connection available as a shared connection. This means that you can establish the connection in a child Job and then make the connection available to its parent Job amd siblings. This dramatically reduces the about of programming that you have to perform across your task-specific Jobs. This Job makes use of the Job LibContextReader which makes your Job configuration as effortless as possible; especially when you're promoting your Jobs through Development, Test, and Production.

Image 2

Context Files

The example Job, LibMySQLSharedConnection, makes use of two Context Files. This is because it connects to two separate MySQL databases. If you want to run this tutorial, then you will need to create these these two files, now. You may modify these to suit your own environment. These should be created in your home directory, in a sub-directory, named talend/context. You may change the default location of these files or override the location used in this example. For more information on LibContextReader and how it uses these files, read the tutorial now.

Default.MySQL.cfg

mYSQLHost=localhost
mYSQLPort=3306
mYSQLDatabase=MYSQL
mYSQLAdditionalJDBCParameters="noDatetimeStringSync=true"
mYSQLUsername=root
mYSQLPassword=root

Default.MySQL_TBE.cfg

mYSQLHost=localhost
mYSQLPort=3306
mYSQLDatabase=TBE
mYSQLAdditionalJDBCParameters="noDatetimeStringSync=true"
mYSQLUsername=talend
mYSQLPassword=talend

Sample Data

To run this tutorial, you will need to establish the two database connections described by the two Context files shown above. The first connection allows data to be selected from the user table in the MySQL catalogue. The second connection is for selecting data from two custom tables defined in the database TBE, people and location. Create the database TBE and the tables now, or modify the example Job to suit your own environment.

Custom Table Definitions

To run this tutorial, these are the tables that will need to be created and populated, in the database TBE.

People

delimiter $$

CREATE TABLE `people` (
`Id` int(11) NOT NULL,
`Name` varchar(45) NOT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$

Location

delimiter $$

CREATE TABLE `location` (
`Id` int(11) NOT NULL,
`AddressLine1` varchar(45) NOT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$

Configuring LibMySQLSharedConnection

LibMySQLSharedConnection supports the following Context Variables.

Image 3

It is not normally expected that any of these should be overiden, except for contextStr, contextName. You may choose to override the location of contextDir for testing; however it is strongly recommended that you change the Job LibContextReader if you want to modify the default location on a permanent basis. The MySQL parameters cannot be overriden as they will be read from a Context file.

contextStr

This parameter is used to tell Library Jobs the Context of their parent. If you only use a single Context (Default), then there is no need to pass this parameter; however, if you use multiple Contexts, for example, Default, Test and Production, then this parameter should be set to contextStr, as shown in the screen shot below.

contextName

The default value of contextName, for a MySQL database is MySQL. This value is used by LibContextReader for constructing a path to a Context file. This makes the default file name for MySQL .../Default.MySQL.cfg. If you want to use multiple (and different) MySQL connections in the same Job, then you will need to override this value, as shown in the screenshot below.

Image 4




Expert Consultancy from Yellow Pelican
comments powered by Disqus

© www.TalendByExample.com