#BIGQUERY JSON EXTRACT EXAMPLE UPDATE#
Set up Azure Blob Storage dataset to Blob storage “ DS_Source_Location”.Set up API linked service, and create a REST dataset to API.This additional step to Blob ensures the ADF dataset can be configured to traverse the nested JSON object/array.īelow is a step-by-step guide to extracting complex JSON data in your Azure platform using Azure Data Factory (ADF). The process involves using ADF to extract data to Blob (.json) first, then copying data from Blob to Azure SQL Server.
It seems that there is a bug with ADF (v2) when it comes to directly extract a nested JSON to Azure SQL Server using the REST dataset and Copy data task. As integration tools evolve, greater effort is placed on enabling the tools to extract data from various source systems in different formats, thus enabling Data Engineers to create integration patterns in the most efficient way possible.īy using the ADF “ REST dataset” I was able to successfully create a “ copy data” task to directly map the JSON output to an Azure SQL server table, however, after inspecting the data I noticed NULL data returned from a nested JSON object while non-nested JSON object returned the right data.Įven though the ADF REST dataset can read the API metadata, the values in these nested JSON objects/arrays are returned as NULL when you use a Copy data task to directly load JSON to Azure SQL Server. Using an API to extract data in a JSON format has become a common method for extracting data into Azure SQL Database or Azure Blob. This Blog is a step-by-step guide to build the integration pattern used to extract JSON data from an API using Azure Data Factory. By Mik Panchal, Managing Consultant- Altis Sydney
0 kommentar(er)
