Requirement:
Our requirement is - to get data from REST API and transfer data to Azure SQL table, along with transferring data we have to transfer one extra column value to AZURE SQL instance.
To achieve this use case, we have to understand the data coming from REST Api call, so try to execute API call through Postman once if possible. In this tutorial I am going to explain about how we can call REST Api through ADF and captured/received data get stored in AZURE SQL database along with extra column.
For this tutorial we are using REST API call and AZURE SQL as target database.
Prerequisite :
- Create an Azure Data Factory instance in Azure.
- Create Azure SQL instance in Azure.
- Get an API key for Rest Call - if authorization is there.
- Postman is needed to check - Response of API call.
Set up Linked Services for REST API :
Linked service is like a connection string which define the connection information needed for service to connect other sources. To create linked service for source i.e. REST API - Go to ADF Workspace then click on "Manage" on from left side menu. Under linked services click on "New", then select REST and configure required connection details, refer below images.
Create Dataset for REST API
For creating Dataset for Rest API's, go to Author section from left panel, click on Dataset and click on "New Dataset." Select Data store as "REST" and click continue. Next is link the linked service created earlier or created in above step and then click ok.
Set up linked Services for Azure SQL DB:
To create linked service for Azure SQL DB. - Go to ADF workspace then click on "Manage" from left side menu. Under Linked service click on "New", then select Azure SQL Database and configure the connection. Add a name to lined service then select integration runtime as "AutoResolveIntegrationRuntime". Next use Azure Subscription where database is located. Then provide all details like Server Name, Username, Password, and database name to connect. Lastly click create.
*Create DataSet for Azure SQL DB: *
Similarly, Dataset created for REST API service in same way create data set for Azure SQL DB. To perform that activity; go to Author panel from left menu. Select DataSet section and click on "New Data Set". Select data store as "Azure SQL Database" and click on continue. Next link the linked service created above for Azure DataBase.
Create Pipeline and Add Copy Activity:
Now we are ready with Data Set for Azure SQL Database which is destination and Rest API service which act as source in our use case. To create activity please follow below steps.
Go to the "Author" section.
Click on "Pipelines" and then "New Pipeline".
Add copy activity to the pipeline.
Drag "Copy Data" activity from Activities pane to the pipeline canvas.
In the "Source tab", select "REST API" dataset.
In the "Sink" tab, select the Azure SQL Database dataset.
This complete activity of copy data from REST API to Azure SQL Database. Now here we need to check the response of API and based on that mapping need to consider in ADF pipeline. For that check executing API through Postman.
Add Extra column:
Now we need to add one extra column to the destination Azure SQL table. To achieve this we need to add addition column from source; put column name and value - IF it is custom then use Custom or else use as Parameter; if you are passing it from any other activity. Here we are creating Custom column. Update column Name and its value (we are passing static value here.) This completes our entire setup for process of getting data from REST API and Push it to Azure SQL Database.
Test and Run:
Run the pipeline in debug mode to test the data flow and ensure everything is working as expected.
Publish:
Once the pipeline is tested and validated, publish all the changes to make the pipeline available for scheduled runs or trigger based executions.
Conclusion:
By following all above steps, you should be able to create a robust ADF pipeline that fetches data from REST API and store it in Azure SQL Database with the necessary columns.
Top comments (0)