Monetise your data by exposing APIs using Talend ESB

Database(PostgreSQL) browser with Talend ESB

In this article, I’m going to describe a Talend ESB job created for a table lookup in PostgreSQL database over HTTP API.

Below is the table description of the service_details table, which is the table to be looked up. The main purpose of this article is to explain a technique to create HTTP API in Talend ESB that can be used for looking up single table in postgreSQL database. Therefore do not bother to interpret the actual meaning of the data in this table as those are meaningless without other surrounding table structure and application to which this table belongs to.

Part2 of this article will explain the creation of HTTP API in Talend ESB for looking up joined multiple tables in PostgreSQL database.

Fg1.1 service_details Table

Fg1.1 service_details Table

 

The Talend ESB job is created via Talend open studio to open HTTP based API to look up above table looks like below.Fg1.2 Talend ESB job

Fg1.2 Talend ESB job

 

Database connection for a PostgreSQL database is established at the beginning and then rest of the components are started.

On the receipt of the HTTP API request, table attribute called service_code is extracted from the query string. The table is looked up with received service_code parameter and the entry found condition is evaluated at tParseRecordSet_1 component. If the condition is met, execution flow continues on the lower path, and service details are returned. Otherwise continues on upper path and only the service_code received as the request parameter is returned.

 

Each component of the above depicted Talend job is configured as below.

tDBConnection_1 — This is configured to establish a connection with PostgreSQL database. And it triggers tRESTRequest_1 component after setting up the database connection.

tRESTRequest_1 — This starts with OnComponentOk trigger from

tDBConnection_1. The service_code parameter is defined as a query string parameter for the getServiceDetails output flow.

 

Fg2.1 tRESTRequest_1 Basic settings

 

Fg2.2 tRESTRequest_1 Basic settings query string parameter

 

tDBRow_1 — The existing connection tDBConnection_1 is used to connect database. An additional column with type Object is added to the schema to use for the propagating of recordset. Propagate QUERY’s record set option is enabled to insert the result of the query into a column of the current flow and the recordSet column is configured for that.

NOTE: Propagate QUERY’s record option allows the component to have a different schema from that of the preceding component. The column that holds the QUERY’s record set should be set to the type Object and this component is usually followed by tParseRecordSet. (Reference: Talend ESB studio Documentation).

 

Fg4.1. tDBRow_1 Basic settings

Fg5.2 tParseRecordSet_1 Basic settings

 

Fg4.3 tDBRow_1 Advanced settings

ParseRecordSet_1 — The column from the database that holds the recordset, i.e. recordSet is set as the Prev.Comp. Column list. The output schema is edited for the required column list to be processed and pass to the next component. In the attribute table settings, the position value of each column for single records from the recordset is set. I.e. if the same value is passed to the next component, the value parameter for each column is set to the string value of the same column name.

NOTE: Boolean type values is converted into String type values at the output of this component. Therefore as shown in Fg5.2 Boolean type values is set to String type at the output (Those are converted into Boolean again at a later stage before the final output)

Run-if trigger is used to start the branch which handles the flow when the number of rows processed is 0.

The Run-if condition check is as depicted in Fg5.3

Fg5.1 tParseRecordSet_1 Edit schema

Fg5.2 tParseRecordSet_1 Basic settings

Fg5.3 tParseRecordSet_1 Run if

 

tFixedFlowInput_1 — A column service_code is sent as a fixed input with value received as the service_code (global parameter) in HTTP request. The value for the fixed column is configured as depicted in Fg6.2.

Fg6.1 tFixedFlowInput_1 Edit schema

 

Fg6.2 tFixedFlowInput_1 Basic schema

 

tXMLMap_1 — Boolean output columns are mapped through expression builder which converts String input to Boolean output. The Fg7.2 depicts the expression used for the transformation. All the other input parameters are directly mapped to output without any intermediate processing.

 

tXMLMap_1 Boolean output columns are mapped through expression builder which converts String input to Boolean output. The Fg7.2 depicts the expression used for the transformation. All the other input parameters are directly mapped to output without any intermediate processing.

Fg7.1 tXMLMap_1 Map editor

Fg7.2 tXMLMap_1 Expression builder for a String type ‘managed’ variable to get Boolean output

 

tXMLMap_2 — Input parameter is directly mapped to output without any intermediate processing.

Fg8.1 tXMLMap_2 Map editor

 

tRESTResponse_1 — Default configuration is kept as depicted below figures.

Fg9.1 tRESTResponse_1 Return Body type

 

Fg9.2 tRESTResponse_1 Output body mapping

 

tRESTResponse_2 — Default configuration is kept as depicted below figures.

Fg10.1 tRESTResponse_2 Return Body type

Fg10.2 tRESTResponse_2 Output body mapping

 

The exposed HTTP endpoint after starting of the Talend job can be used to lookup the service_details table in PostgreSQL DB by service_code parameter.

JMeter application is used as the client program and the responses for data available and unavailable scenarios are depicted below.

 

Data available scenario: Response contains all the service details.

Fg11.1 Response for available data

 

Data unavailable scenario: Response contains only service code.

Fg12.1 Response for unavailable data

 

Resources: GitHub repository

Author:

Chananka Fernando, Wavenet

Chanaka Fernando

Associate Software Architect,

Global Wavenet

 

 

 

.

More from our blog

See all posts