209 Views
3 minute read
Categories
Custom Development

Import JSON Files into MySql Database Table Part – 2

Introduction:

In Part 1 of the blog series, we have learnt till how to test ADLA procedure while importing structured and semi-structured Json files into MySql Database table using Azure Data Factory & Data Analytics. In the second and last part of the series, we will learn how to create and assign permissions to service principal in a one-time setup.

Step 4: Create and assign permissions to service principal (one time setup)

We need a link service that will communicate with the Azure Data factory and the Azure Data Analytics. AS we will call the Azure Data Analytics U-SQL store procedure form Azure Data Factory.

Please go through the link for details.

After creating the service principle, we must add read and write permission so that it can read, write ADL job trough ADF.

Step 5: Create Azure Data factory component to generate CSV file from Json file

To perform the task, we have to create two linked services, two datasets, and one pipeline those will help us in future implementation.

Linked services: One link service needed to communicate Data Factory with Azure storage account. Another linked service needed to communicate Data factory with Azure Data Analytics account, in this case we need the service principal details.

Dataset: One dataset will relate to the row Json fie and another dataset will be connected to the storage accounts for output csv file.

Pipeline: We needed one pipeline that will content one U-SQL activity, that will call previously created U-SQL store procedure.

Import JSON Files into MySql Database Table

Once we click on “New” button in the ADLA Linked services option it will open a new window like below, where we have to put the service principal related information.

Import JSON Files into MySql Database Table

In the Script section we need another Linked service and dataset as below

Import JSON Files into MySql Database Table

We will call the ADA procedure like below

Import JSON Files into MySql Database Table

Now we have completed all the component needed to generate the csv file form Json file. To check it, we have start debugging the pipeline as below. Once the pipeline ran properly it will create a csv file in the output folder, that provided in the U-SQL procedure.

Step 6: Data Factory pipeline to import the converted csv formatted data to MySQL table

In this case we required two Linked service, one link will communicate with csv file, and another will be with MySql database (You may use any type of relational database). We required another pipeline that will content one “Copy Activity” and two dataset We can explain it by below screen shoot.

Configure source

We have to specify the source folder, that content the csv file. It will be connected with a linked service.

Import JSON Files into MySql Database Table
Configure Sink

In sink setting we will create a linked service that will connected to the MySql specific table where we need to insert the record from the csv file.

Import JSON Files into MySql Database Table
Configure mapping

In the mapping we will map the csv file column to corresponding MySql table column.

Import JSON Files into MySql Database Table

So, in this process we can store different structure Json file into a relational representation or any relational table like MySQL or MS SQL. Those record may be process to another application.

Debdatta Patra

About Debdatta Patra

Debdatta has around 10+yrs of experience working in Software Development using .Net, Azure services and SharePoint based applications. He has experience in analysis, design, development, implementation, maintenance, and support of applications in the Client Server, Intranet, and web enterprise application environments.

Prior to joining Netwoven, he was working with ICRA Analytics Limited, Kolkata. Debdatta did his MCA from JIS College of Engineering, West Bengal.

LinkedinTwitterFacebook

Leave a Reply

Your email address will not be published. Required fields are marked *




Enter Captcha Here :