Introduction

In today’s world DATA is ubiquitous and critical to the business which eventually increases the need for integration across different platforms like Cloud, Web Service etc. When it comes to Data Integration, business needs effective communication between their software systems and ETL tool to fulfill their needs.

This blog post explains what a REST Web Service is, how you can create a Power Center workflow, use REST based method to access the web services via HTTP Transformation.

REST Overview

Web Service provides a common platform that allows two different applications on various platforms to communicate and exchange messages between them over HTTP protocol. Web Services can be accessed using different methods or styles. In Web Service world REpresentational State Transfer(REST) is a stateless client-server architecture in which Web Services are exposed as URLs. The typical method of accessing Web resource in a RESTful system is through various HTTP methods such as GET, POST, PUT and DELETE.

REST can use SOAP Web Services and any protocol such as HTTP, SOAP.

High-Level REST Architecture

Figure 1: High-Level REST Architecture

REST call using Informatica Power Center

Let us explain with the help of a sample web service which is used in a Retail Industry. In a Retail industry, customer can purchase various items from the retailer through online and offline and we need a centralized repository called a Data Warehouse where all the transactional data is fed into. The item details are then further normalized into Data Marts to form a common schema which will have facts and dimensions. On a large scale environment, this could be a cumbersome task in designing a data model.

To overcome this we have business needs to access the data over the network and the best way will be to use the web service. So the final order details, pricing, payment details, and shipping information are translated internally and saved in a third party system which is either in an XML or JSON format. The third-party system hosted over a secured network is accessed via a URL. Since this system is based on REST API calls, we use Informatica Power Center as a client to access the server which is a web service API.

In Power Center, you can use an HTTP transformation to send a request and receive a response from a REST Web service.

Pre-requisites to configure REST call using HTTP Transformation

Valid REST based URL supporting either XML or JSON

Valid SSL certificates to connect to REST URL outside of the network

Basic Authentication Mechanism(User Name and Password)

Step by Step Process

Creating a Source File

Create a source file which specifies the search parameters to be passed as input to the HTTP transformation.

Figure 2: Source File

Create HTTP Transformation

Create HTTP transformation with the following input ports:

Username

Password

OrderNumber

Figure 3: HTTP Transformation

Base URL

Set the Base URL to get the Order details based on the input parameters passed from the source file.

Base URL: https://test-swaggerUI/orders/search

Figure 4: Base URL Settings

Set the HTTP Header parameters

Header parameters in HTTP transformation contain header data for request and response in a specified format such as XML or JSON. Following are the parameters:

Default Value : Specify the acceptable values supported by the web service. By default, the response will be in JSON. If you need XML you need to specify as ‘text/xml’

: Specify the acceptable values supported by the web service. By default, the response will be in JSON. If you need XML you need to specify as ‘text/xml’ HTTP Name: For REST API the default value is “Accept”

Figure 5: Header Parameter Settings

HTTP Method Selection Type

Specify the type of HTTP method on the URL. In our example we are trying to get the order detail information, so specify the type as “GET”

Figure 6: HTTP Method Selection Type

HTTP Output

Pass the HTTP output to either a flat file or an XML parser to parse the information and load it into the target table. In this example, we write the output of the HTTP to a flat file.

This is how the final mapping will look like.

Figure 7: Final Mapping

Create a connection in Workflow manager and run the workflow

Final Mapping

Navigate to Workflow manager ->Connections-> Applications->HTTP Transformation

Figure 8: HTTP Application type

Name: Enter the name of the HTTP Transformation which is understandable

User Name: Enter the username of the REST API URL call

Password: Enter the password for the URL

Authentication Type: Select the Authentication type as “Basic”

Create a workflow for the mapping and run the workflow. Finally, view the output. In our example, for the Order Number “ORD-123”

Figure 9: Final Output

Issues faced while connecting to HTTP Transformation

While using HTTP Transformation the most common issue is “SSL Certificate Error”. So when you run the workflow which is using the final URL you will get the below errors in session logs

Root Cause

This error is caused because the connection to the web service URL or Rest API does not include the certificate data that is needed to authenticate the connection from the certificate provider’s end. The certificate bundle does not contain a certificate from a Certificate Authority that the Web service Provider uses.

Resolution

Download the SSL certificates for the URL and add to the certificate files located under $INFA_HOME/server/bin/ca-bundle.crt

Refer the below KB article to know how to add those certificates

HOW TO: Extract certificates from a HTTPS URL and add to ca-bundle.crt file

Conclusion

This concludes how to create a Power Center Workflow using REST based methods to access a web service using HTTP Transformation, invoke HTTP commands to get data from the web service resource.

About the Author

Sathish Kumar is a Senior Technical Consultant at Mastech Infotrellis and having around 6 plus years of experience in development and implementation of ETL and MDM solutions. He is having good knowledge in various Informatica products like Power Center, IDQ, Informatica Cloud and Informatica MDM MDE.