Issue: How to Load JSON File to Azure SQL Database Table in Azure Data Factory.
In this article, we are going to learn how to load .json file to Azure SQL database table in the Azure data factory, let's start our demonstration, first of all, we have to create a SQL database, to create SQL database open your Azure portal and click on SQL database at top of the dashboard, then click on + Create button, it will open a new window, as shown in the picture below, provide the required information, which is Azure subscription, Resource group, and database name, then select the server if you have already created if not, then create a new server to click create new under the server field, once you click on that it will open a window at the left side of the dashboard, provide the required information, which is the server name, server admin login, password and location and then click ok.
Fig-1: Create a new SQL database.
Once the Server is created click on the Configure database where you can find scalable compute and storage options, select the CPU's & Memory, etc as per your requirement, it will show you the total cost for that, and then click apply, then click Review + Create button and then click create. as the database is in the deployment process, meanwhile click on the server name which we have created recently, click on firewalls & virtual networks under the security tab, and then click on ''Allow azure service & resources to access this server'' make it ''Yes'' and click on save. as shown in the picture below.
Fig-2: Firewalls and virtual networks settings of the Server.
once our SQL database is created let's go to the SQL Server Management Studio, provide the Azure SQL server name and password and it will ask you for the sign-in and then click to connect with the recently created Azure SQL server, and create a new table as shown in the picture below.
Fig-3: Create a table in SQL server management studio.
Once the table is created go to the Azure data factory and create a new pipeline, go to the pipelines and click on New pipeline, name the pipeline and search and drag Copy Data activity in the pipeline, click on copy data activity and go to the source, then create a new Source dataset, click on + New, then select Azure Blob storage, then select the file format in my case it is .JSON, then click on continue, which is shown in the picture below.
Fig-4: Create a new Source Dataset.
Then create linked service, click on new then select the Azure subscription and Storage account name and click on create, then select the file path which is input, then select the import schema, in my case it is From connectin/store, then click ok.
Fig-5: Create a new linked service in our pipeline.
Once our source dataset is created, click on the Sink tab and create a new Sink dataset, select Azure SQL database as connection, then create a new linked service, Provide the name, Select your Azure Subscription, Database name, Authentication Type, user name & Password, as shown in the picture below, click ok and select the table and import schema and click ok,
Fig-6: Create a linked service for our sink dataset.
Once our Sink dataset is created, go to the mapping and click on import schemas, if you want to change any column name or something you can do it otherwise just click on Debug. once debug process is completed go to the SQL Server Management Studio and run the query '' Select * From dbo.cumstomer'' and it will show the records. as shown in the picture below.
No comments:
Post a Comment