Data Integration 005: Talend, Mapping with tMap

update-mobile-db

Talend job that matches data from 2 files and filter out specified records, using tMap.

In this example, I am creating a job that would read from two separate CSV files, one is a list of employee mobile phone numbers, the other is a list of employment status of all employees. After matching a record against both documents, if the employee is still active, the employee mobile phone number shall be updated in a MS SQL DB. (More details on how to connect to MS SQL Server here.)

Talend has provided an extremely convenient component to achieve my objective, which is the tMap component. This tMap component steps through the data records in my employee mobile number file (Main), and match the specified primary key against the reference file (Lookup), which contains employee status. When the status of a particular record meets my condition, it will be generated as an output from tMap, which can then be loaded into the DB by a separate DB input component.

tmap-settings

Mapping data inputs and outputs.

The tMap schema editor presents a simple drag and drop UI for you to relate the input and output data. To filter for certain conditions in the data, you will have to manipulate the data using expression (which appears to be in the Java language). More introduction on the tMap expression can be found here. In my example, I actually have two outputs. The second output catches all the data records rejected by the first output condition (Catch output reject = True), and presents the data to me in the console using tLogRow component.

tmap-settings-2

Schema of output to be loaded into DB.

I did run into a small problem when I run the job. The DB table allowed the field “isPrimary” to be nullable even though it holds the type Boolean. Naturally I have used the DB table schema to design the output of my Talend job, however Talend does not recognise Null value for the type Boolean. I will have to explicitly specify that the Boolean data field in my output is Nullable. (Which makes me wonder, why doesn’t Talend enforce this instead of allowing the user to specify otherwise and throwing up error?)

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s