Loading data from API to Table using Informatica’s Intelligent Data Management Cloud (IDMC)-CDI

Objective:

Let’s talk about the situation where you want to periodically fetch data from a remote source and store it in a table for various purposes, such as analysis, reporting, or data backup. In that case, using API calls is quite useful. API, or Application Programming Interface, serves as a bridge that enables different software systems or applications to communicate and interact with each other. There are not many cloud applications that accomplish this. Here, we are detailing out on how this works on informatica cloud.

This blog helps to understand how to retrieve a remote data through API calls and store it in a table with the help of IDMC-CDI. We will guide you through the step-by-step process on how this works with screenshots and instructions for better understanding.

Why API?

• It offers high performance.
• Enables us to implement innovative automations.
• It fosters great employee productivity.
• Enhances the customer experience.

Background:

There are some prerequisites before entering this process, which is as follows,

• Privilege to access Administrator, Data Integration and Monitor environment of IDMC.
• A swagger file to capture the API calls.
• A REST API V2 connection with the help of swagger file and Table connection.
• A runtime environment under which a service called Data Integration Server (DIS) should be up and running for the successful completion of data integration tasks while using that environment.

(For the above-mentioned prerequisites, references have been given at the end of this blog.)

Source API Data:

Step 1:

Create a swagger file, REST V2 connection and Table Connection.

• Launch Informatica Data Management Console (IDMC) and navigate to the Administrator tool.

• Set up the connection details for the Table in Administrator.

• Create a swagger file to capture the API calls.

(A Swagger file, also known as an Open API Specification (OAS) file, is a machine-readable document that describes and defines the structure, endpoints, operations, and behaviour of a RESTful API. It serves as an API documentation which is essential for both developers and consumers))

• Create a REST V2 connection in Administrator by referencing the swagger file we created and place it in the secure agent path.

Step 2:

Create a business service:

• A business service should be created in IDMC-CDI which references the REST API connection we created above.

Step 3:

Create a mapping to carry out the process:

• A simple mapping is created with a Source, Web Service, and a Target transformation.
• Select the Table connection as source and point a text file with only port value in the file.

• Select the business service we created in the web service option under the web service transformation.

• Map the incoming field i.e., port from Source option to root from the request structure under the request mapping.
• Basically, it requests the structure of the incoming source data to break down hierarchy.

• In response mapping tab, we can see all the hierarchies are derived with respect to their cardinality and the output fields are derived.

• After completing the response mapping, we should map the web service transformation to the target transformation.
• While mapping to the target, it will ask about which part of the output hierarchy structure should be passed on to the target.
• We should map only the data we need in the target transformation.

Step 4:

Configure Target Transformation:

• Select the table connection we created already under the connection tab in target transformation.
• Create a new target table object at runtime under the object tab.

• Save and validate the mapping. If no validation error occurs our mapping is good to execute.
• Execute the mapping in the runtime environment under which our swagger file is placed.
• After successful execution of the mapping, the data will be stored in the table we created at target.

• The table has been created in the target database.

Conclusion:

By following these steps, we can achieve data consumption from an API to a Table using mapping from IDMC-CDI. It opens our gate to explore more possibilities of API integration in Informatica cloud.