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.
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:
Figure 3: HTTP Transformation
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’
- 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
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
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
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.
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
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.