How to dynammically map JSON data to an Azure SQL database

user_11153420 0
Viewed 204

I have to read JSON data in Azure Data Factory from a REST API like CRM Business Central and then, to store them in Azure SQL database. Since I have nested JSON data, I have to define the mapping. In a manual way, it looks like this:

enter image description here

One way to do this is to go the "{}" and then get the "translator" (which is the JSON schema of the table) and then save into a table or file. Once it's done, we can then use the "dynamic content" in the mapping tab of the Copy Activity from a Lookup Activity. Another way to do this is to create an destination table and then create a stored procedure for guessing the schema. Here is the link of the explanation. However, the process of saving JSON schema into a table/file as "metadata" is rather annoying because I have more than 100 tables to load into Azure SQL. Is there another way to infer JSON schema without creating a "destination table"? I would like to use the "auto-create table" in the Copy Activity? I was thinking to use a Python script inside ADF.

azure-data-factory dynamics-crm python json
1 Answers

However, the process of saving JSON schema into a table/file as "metadata" is rather annoying because I have more than 100 tables to load into Azure SQL. Is there another way to infer JSON schema without creating a "destination table"?

With all my research and knowledge, there is no such feature available in ADF which can allow you to load 100 tables without writing the schemas. You have to write schema for each and every table. The mapping becomes more critical when you have source as JSON. You need to properly map the records so that it would load without any exception in SQL Database.

Therefore, you need to manage this programmatically. Python is the best choice for this scenario.

Write a python script and run it using Custom activity in ADF.

You can create a Custom activity with your own data movement or transformation logic and use the activity in a pipeline. The custom activity runs your customized code logic on an Azure Batch pool of virtual machines.

To know more please refer Use custom activities in an Azure Data Factory.

user_159690410
Related