Informatica SQL Transformation
In a matured data warehouse environment, you will see all sorts of data sources, like Mainframe, ERP, Web Services, Machine Logs, Message Queues, Hadoop etc. Informatica has provided a variety of connector to get data extracted from such data sources. Using Informatica HTTP transformation, you can make Web Service calls and get data from web servers. We will have this transformation explained in this article with a use case.

What is HTTP Transformation

The HTTP transformation enables you to connect to an HTTP server to use its services and applications. When you run a session with an HTTP transformation, the Integration Service connects to the HTTP server and issues a request to retrieve data from or update data on the HTTP server.

For example, you can get the currency conversion rate between USD and EUR by calling this web service call. http://rate-exchange.appspot.com/currency?from=USD&to=EUR Using HTTP Transformation you can :
  1. Read data from an HTTP server :- It retrieves data from the HTTP server and passes the data to a downstream transformation in the mapping.
  2. Update data on the HTTP server :- It posts data to the HTTP server and passes HTTP server responses to a downstream transformation in the mapping.

Developing HTTP Transformation

Like any other transformation, you can create HTTP transformations in the Transformation Developer or in the Mapping Designer. As shown in below image, all the configuration required for this transformation in on the HTTP tab.
Informatica HTTP Transformation

Read or Write data to HTTP server

As shown in the image, on the HTTP tab,  you can configure the transformation to read data or write data to the HTTP server. Select GET method to read data and POST or SIMPLE POST method to write data to an HTTP server.

Configuring Groups and Ports

Base on the type of the HTTP method, you choose and the port group and port in the transformation in the HTTP tab.. 
  • Output. Contains data from the HTTP response. Passes responses from the HTTP server to downstream transformations. 
  • Input. Used to construct the final URL for the GET method or the data for the POST request.
  • Header. Contains header data for the request and response.
In the above shown image, we have two input ports for the GET method and the response from the server as the output port

Configuring a URL

The web service will be accessed using a URL and the base URL of the web service need to be provided in the transformation. The Designer constructs the final URL for the GET method based on the base URL and port names in the input group.
In the above shown image, you can see the base url and the constructed URL, which includes the query parameters. This web service call is to get the currency conversion and we are passing two parameters to the base url, "from" and "to" currency.

Connecting to the HTTP Server

If the HTTP server requires authentication, you can create an HTTP connection object in the Workflow Manager. This connection can be used in the session configuration to connect the HTTP server.

HTTP Transformation Use Case

Lets consider an ETL job, which is used to integrate sales data from different global sales regions in to the enterprise data warehouse. Data in the warehouse needs to be standardized and all the sales figure need to be stored in US Dollars (USD).

Solution : Here in the ETL process lets us use a web service call to get the real time currency conversion rate and convert the foreign currency to USD. We will use HTTP Transformation to call the web service.

For the demo, we will concentrate only on the HTTP transformation. We will be using the web service from http://rate-exchange.appspot.com/ for the demonstration. This web service take two  parameters, "from currency" and "to currency" and returns a JSON document, with the exchange rate information.

http://rate-exchange.appspot.com/currency?from=USD&to=EUR

Step 1 :- Create the HTTP Transformation like any other transformation in the mapping designer. We need to configure the transformation for the GET HTTP method to access currency conversion data. Below shown is the configuration.
Informatica HTTP Transformation
Step 2 :- Create two input ports as shown in below image.  The ports need to be string data type and the port name should match with the url parameter name.
Informatica HTTP Transformation
Step 3 :- Now you can provide the base URL for the web service and the designer will construct the complete URL with the parameters included.
Informatica HTTP Transformation
Step 4 :-  The output from the HTTP transformation will look similar to what is given below.

{"to": "USD", "rate": 1.3522000000000001, "from": "EUR"}

Finally, you can plug in the transformation into the mapping as shown in below image. Parse the output from HTTP Transformation in an expression transformation and do the calculation to convert the currency to USD.
Informatica HTTP Transformation
Hope you enjoyed this tutorial, Please let us know if you have any difficulties in trying out HTTP transformation or share us if you use any different use cases you want to implement using HTTP transformation.

Share with your friends

Readers Comments