12 Apr 2023 OpenAPI Integration with IICS

Informatica Intelligent Cloud Services (IICS) provides a long list of connectivity options for your data integration tasks, enabling you to load data from most major clouds, APIs, and web services too.

If you are new to the ClearPeaks blog, we recommend you visit the previous articles in this IICS series:

1. IICS architecture and benefits
2. Loading data from semi-structured JSON to the relational table
3. Implementation of SCD Type 2 using prebuilt templates in IICS

In this blog post, we’ll focus on the integration of data sourced from a web service. We’ll be fetching semi-structured data from an OpenAPI and loading it into a database in tabular format.

A brief Introduction to APIs and Web Services

Before moving forward, let’s review some keywords and their definitions:

An API is a set of definitions and protocols to build and integrate application software. API stands for Application Programming Interface, a software intermediary provided by an application to another application that allows two applications to talk to each other.

REST is a set of architectural constraints, not a protocol or a standard. API developers can implement REST in a variety of ways. REST stands for Representational State Transfer, a term coined by computer scientist Roy Fielding at the beginning of the century.

A Web Service facilitates interaction between two machines over a network. These are essentially the services (APIs) that are accessible over the web.

A REST API (also known as a RESTful API) is an application programming interface (API or web API) that conforms to the constraints of the REST architectural style and allows for interaction with RESTful web services.

An OpenAPI, also called a public API, is an application programming interface made publicly available to software developers. OpenAPIs are published on the internet and shared freely, allowing the owner of a network-accessible service to give universal access to consumers. Originally known as the Swagger Specification, the OpenAPI Specification (OAS) is a format that can be used to describe, produce, consume, and visualise RESTful web services.

A Swagger File is a specification for documenting REST APIs; it specifies the format (URL, method, and representation) to describe REST web services. A Swagger file is a JSON file, and you need to have one to configure the IICS REST V2 connection to integrate with any REST API. The Swagger file describes the HTTP method, the parameters required, and the expected response fields for that API.

The Informatica Cloud REST V2 Connector helps to interact with web service applications built on REST architecture. You can use it in a Source transformation, Target transformation, or midstream in a Web Services transformation.

Implementation of API data integration in IICS

For the purposes of this blog post, we’re using data from an OpenAPI at this link. This API source returns data about astronauts currently in space. Effectively, for this blog post, we are using OpenAPI data as our source and Snowflake as our target database.

To create a Swagger file, you need to go to Administrator, select Swagger Files from the left pane, and then you can click on Create, as shown below:

*Operation Id is mandatory and it is simply a unique number to identify the file.

Once the Swagger file has been created, download it to your local machine:

For reference purposes, below you can see an example Swagger file:

{ 
  "swagger" : "2.0" 
  "info" : { 
    "description" : null, 
    "version" : "1.0.0", 
    "title" : null, 
    "termsOfService" : null, 
    "contact" : null, 
    "license" : null 
  }, 
  "host" : "api.open-notify.org", 
  "basePath" : "/", 
  "schemes" : [ "http" ], 
  "paths" : { 
    "/astros.json" : { 
      "get" : { 
        "tags" : [ "5377616767657244" ], 
        "summary" : null, 
        "description" : null, 
        "operationId" : "5377616767657244", 
        "produces" : [ "application/json" ], 
        "consumes" : [ "application/json" ], 
        "parameters" : [ ], 
        "responses" : { 
          "200" : { 
            "description" : "successful operation", 
            "schema" : { 
              "$ref" : "#/definitions/5377616767657244"            
  }          }        }      }    }  }, 
  "definitions" : { 
    "5377616767657244##people" : { 
      "properties" : { 
        "craft" : { 
          "type" : "string" 
        }, 
        "name" : { 
          "type" : "string" 
        }      }    }, 
    "5377616767657244" : { 
      "properties" : { 
        "number" : { 
          "type" : "number", 
          "format" : "int32" 
        }, 
        "message" : { 
          "type" : "string" 
        }, 
        "people" : { 
          "type" : "array", 
          "items" : { 
            "$ref" : "#/definitions/5377616767657244##people" 
          }        }      }    }  }}  

**You can copy this sample code from this blog post and save it as 5377616767657244.JSON.

Now you need to save your API Response in JSON format, so copy this data and save it as a JSON file:

For reference purposes, here is the code:

{"number": 14, "message": "success", "people": [{"craft": "ISS", "name": "Kjell Lindgren"},
{"craft": "ISS", "name": "Bob Hines"}, {"craft": "ISS", "name": "Samantha Cristoforetti"}, 
{"craft": "ISS", "name": "Jessica Watkins"}, {"craft": "Tiangong", "name": "Cai Xuzhe"}, 
{"craft": "Tiangong", "name": "Chen Dong"}, {"craft": "Tiangong", "name": "Liu Yang"}, 
{"craft": "ISS", "name": "Sergey Prokopyev"}, {"craft": "ISS", "name": "Dmitry Petelin"}, 
{"craft": "ISS", "name": "Frank Rubio"}, {"craft": "Endurance", "name": "Nicole Mann"}, 
{"craft": "Endurance", "name": "Josh Cassada"}, {"craft": "Endurance", "name": "Koichi Wakata"}, 
{"craft": "Endurance", "name": "Anna Kikina"}]} 

**You can copy this sample code and save it as response.JSON.

Now create a REST V2 connection on the Connections page in the Administrator console. If you can’t see REST v2 as a Connection, you can add it by going to the Add-On Connectors tab:

Make sure you enter the correct path of your Swagger file; the other settings can be left with their default values.

Now we are ready to create the Mapping in the Data Integration Tab.

Define your created connection here:

It is very important to define the Request Options; click on Configure and you can specify the parameter of the query as per your API. We wanted to fetch all available records, so we used the following:

Then you can specify which Fields of your API you want to load in the Target. We have selected these two:

As we can see, IICS has automatically gone to its child node and allowed us to fetch that column’s data into the relational format – this is the tool’s key advantage. You could refer to our blog post on JSON & Snowflake integration for detailed information on how semi-structured data is converted to tabular format.

Now we have just used an expression to hold these two columns, and we want to create the Target Table at Run Time, so we have selected this in the Target Table Configuration:

You can do a final review of your mapping; ours looks like this:

Create a Task for this mapping:

Define the details and select your mapping:

Finish the creation of the task, run the task, and check the data.

As this is an API, the data may change at any time. At the moment of writing this article, we are getting these 14 records shown below as an output:

We can see that the Target table has been created dynamically by IICS, and the data in the Target is loaded after a successful job run:

We can see the expected output, the same rows we found in the source, now parsed and stored in a structured way in a table in an RDBMS.

Conclusion

That’s the end of this blog post about implementing OpenAPI data into a relational table with IICS. We have seen the following highlights:

• The use of the Rest V2 Connector in IICS and how it can fetch any API data.
• As data is available in JSON format, which is semi-structured, IICS automatically allows you to select the level of information you are interested in and to fetch those in relational format with the option of creating a target table dynamically.
• We have also seen how to create and use Swagger files.

API management in Informatica Intelligent Cloud Services allows organisations to easily deploy, manage, and control the use of APIs for enterprise services and processes. If you would like to know more about the ETL/ELT Solutions we offer, simply contact us!