Embedded Files
This article will show you how to transfer data from REST API using GET and POST calls to snowflake using Informatica Cloud. As the REST API won't be open to the general public, we will create an access token that a user can use to retrieve data from the API and then send it to Snowflake.
Pre-requisites
Pre-requisites
We need to make sure that we have following things ready before proceeding any further:
Basic knowledge of Informatica cloud, runtime, connections etc.
Must have a Runtime Instance installed on local machine(having our own runtime gives us flexibility to change and modify settings according to our need also it is required to keep swagger files for API connection).
Existing snowflake account.
Informatica Configuration
Informatica Configuration
There are various steps that we need to follow:
Planning entire flow
Planning entire flow
Before moving on to the actual configuration, we need to know how the complete pipeline will flow. How data will be transferred from the API to Snowflake.
We will be calling API to get access token
Use that access token to get data.
Transfer data to Snowflake
Install Informatica Cloud Secure Agent(Runtime)
Install Informatica Cloud Secure Agent(Runtime)
Navigate to Administrator tab and then follow instructions
Navigate to Runtime environment from left navbbar and go to "Download Secure Agent"
Wait for runtime until all services are up and running
Make sure that all services are up and running
After successful installation of runtime, you can see your runtime in "Runtime Environments" in left navbar.
Creating Swagger file
Creating Swagger file
To explain our API call's overall structure, including the response fields, data types, and other elements, we must develop a Swagger file. The steps listed below should be followed to produce a Swagger file:
In Administration, navigate to Swagger Files and Click on New.
Give all the necessary details about API and Create.
We will be creating two swagger files. One will have details regarding getting access token and other will have details regarding getting data from API. After successful creation we can download our swagger file and we need to put these file on that system where our Secure Runtime is installed.
Creating API Connections
Creating API Connections
With these swagger files, we can now construct a RESTv2 connection. To establish a relationship, the location of our Swagger file in our secure runtime system must be selected. There are two swagger files hence there will be two connections as well.
Creating Snowflake Connection
Creating Snowflake Connection
We also need to create a connection to store data from API. We may not be able to find snowflake connection, we need to get this connector from "Add On Connections" menu from left navbar.
Creating Mapping
Creating Mapping
A mapping is made up of connectors, transformation objects, connectors, and source and target items. Target objects represent the systems that the data is fed into, whereas source objects represent the data sources from which the mapping retrieves data. Data transformation logic that is applied to the data as it goes from source to target is represented by transformation objects.
A drag-and-drop interface for building mappings is offered by Informatica Cloud. Developers can choose transformation objects, set data transformation rules, and visually design the data flow using this interface. The ability to save and reuse mappings for different data integration tasks.
In Data Integration, to go "New" and then click on "Mapping"
Configuring Source
Configuring Source
In Source Tab, select your access token RESTv2 connection
In Operation tab, select your operation ID that was given during swagger file generation.
In Query Options, go to Configure
Request Message Template shows us how should our "Request Message" should look like. Fill out "Request Message". These fields are required to get access token from API server.
NOTE : Request Message depends on your API call structure in each call, in above example API server needs "email" and "password" in request body. It might not be the case for every API Server.
"Field Mapping" contains those fields that will be retured after a successful API call. In our case we only need accessToken from response, so we select it
Don't forgot to change default precision for your access token becuase it was 10 then only first 10 characters will be received
Configuring Business Service
Configuring Business Service
We need to use API in midstream transformation, so we need to create a Business Service and use our get data API connection in this service.
Click on "New" tab in left top corner, Click on "Components" then click on "Business Services"
In Connection tab, select your get data connection then click on "Select Operation"
Click on "Select"
There you will find your operation id which was given during creation of swagger file.
Preparing Access Token
Preparing Access Token
In our example our access token be given as output by Source Transformation. Because we are using Bearer Authorization in our API call, we need to add string "Bearer " in front of our access token. We need to use Expression Transformation to achieve this.
From left toolbar, drag both Expression and Web Services Transformations
When we connect Source to Expression Transformation, we need to select root group that will contain our access token.
Connect Expression to Web Services Transformation as shown in figure.
Click on Expression and add a New Field.
Given Name and all necessary paramters
Click on Configure to configure this field
Concatenate 'Bearer ' in front of our access token. This depends on API server and type of authentication which is required. In some cases we may not need to append anything in from on access token.
Configuring Web Services Transformation
Configuring Web Services Transformation
Click on Web services Transformation, then in Business Service, select our get data service and then select operation. Operation will be our operation id from swagger file.
In Request Mapping, we can see Request Structure requires an Authorization parameter, we can map our o_access_token variable to that by drang and drop. This variable is nothing but output variable of Expression Transformation.
In Response mapping, we will mark those fields which we want to store.
NOTE: If JSON response is more complex and have arrays, then Informatica will automatically add Primary and Foreign keys to tables to aggregate data afterwards.
In our case Informatica is creating two groups in Output fields, we need to store both these groups.
We will take two targets to get data for both groups.
After connecting Web services to Target we will select both groups for each target.
Then we can Specify our target as Snowflake
We will configure snowflake as target for both Target Transformations
Running Mapping
Running Mapping
After Configuring everything, we can run our mapping to load data into snowflake.
After saving and validating our mapping we are ready to run it.
If everything is correct our mapping will run and also load data to snowflake.
We can also see loaded data in Snowsight UI.
Google Sites
Report abuse