Up next in 10
Data Pipeline to Copy Data from Azure SQL Database to Fabric Data Warehouse
10K views
Jan 29, 2024
In this new video, I demoed how to create data pipeline to copy data from Azure SQL Database to Fabric Data Warehouse. Also talked about the important of invoking compulsory firewall rules in Azure to successfully copy data to the Fabric Warehouse workload!
View Video Transcript
0:00
Hi. In this video
0:05
I'm going to show you how to create data pipeline to copy
0:08
data from Microsoft Azure database to Microsoft Fabric data warehouse workload. Let's get started. Now, this is my Azure SQL database
0:22
and of course, we have this transaction table that we intend to ingest into the data warehouse in Fabric
0:28
Let's see how we can easily do this without much stress. I'm going to come to my workspace
0:34
this particular data factory workspace. At the bottom, I'm going to switch from data warehouse to
0:39
the same data warehouse and then create a data warehouse. Click on it. For the new data we have
0:46
let's call it sales data, and then click on ''Create.'' There we go. The data warehouse is created
0:54
Next, we want to create a new data pipeline to perform that copy activity
1:00
Click on ''New Data Pipeline'' and let's just call it Azure Data and click ''Create.'
1:10
In the choose data source, I'm going to scroll down and search for Azure SQL database
1:18
Click on it and then click on ''Next.'' Of course, we need to establish connection
1:23
I can click on the ''Create new connection'' and specify your server name and optionally
1:28
the name of the particular database and of course, the connection credentials
1:33
Now, I do have some existing connections, so I'm going to click on ''Existing'' and then choose the connection
1:38
Now, I want to point to this particular fabric data.database.windows.net, which is semicolon and then we have a PLR
1:47
Click on it and click on ''Next.'' There we go. We can connect to the data source and we can see
1:58
the databaseowner.transaction so click on it to see the data preview. There we go. We can see the data
2:08
so everything is working fine. Click on ''Next'' and then we'll choose the data destination
2:14
that is the data warehouse that we intend to ingest the data into
2:19
This is going to be sales data, data warehouse, so click on ''Next.'
2:25
Then we can see that we can load to new table or an existing table
2:31
So let's go with the new table and this is going to be the name. This is fine, I can actually change it if I want, but this is fine
2:37
So we can see the column mapping, the source column and the data types and the destination columns and the data types
2:43
So click on ''Next'' and then in this next page, we can see the enable staging
2:49
So the data store type is going to be in the workspace, so that's fine
2:53
Just go ahead and click on ''Next'' and then we can see the review plus save
2:58
So this is the copy summary. The source is actually the Azure SQL database flowing into the data warehouse
3:07
So we can see the source and the connection name, the name of the table and then the destination
3:14
Of course, we can see the name of the connection and of course the name of the table, so click on ''Save'' and ''Run.'
3:21
Let's wait for the activity to start and then we should be able to see the data successfully ingested
3:30
So as we await that, you probably need to enable what is called firewall settings
3:37
So I have some firewalls that are actually enabled for the data to be successfully ingested into the data warehouse
3:45
I have a couple of IPs here. So if you're doing that
3:49
you just have to follow the prompts as you see on your screen. So let's go on
3:53
Now, there you can see we have failed, so something is wrong. So let me see the problem
3:58
Now, we can see that we need to specify the client with IP address
4:03
220.90.130.2 is not allowed to access the server. That's fine. So what I'm going to do is come back
4:11
to my Azure and then under the firewall settings, I'm going to change this to two
4:17
Let me change this to two and then let's change to two
4:21
and let's see what goes on. So two and then I'm going to click on ''Save'
4:27
so we can see successfully updated server firewall rules. So let's go back and click on ''Run'
4:35
and let's run that and let's see whether we're going to get the same error or not
4:40
There we go. So we can see activity status succeeded and we can see ''Run'' succeeded
4:49
So we have been able to successfully ingest the data from the Azure SQL database to the data warehouse in Fabric
4:57
So let's go to the workspace, Data Factory and investigate. So this is the data pipeline and of course
5:04
we can see the sales data warehouse we just created. So when I click on it, I have the transaction table
5:11
and of course, we can begin to query the datasets from the data warehouse
5:17
So this is basically how we can create data pipelines to copy data from Azure SQL database
5:22
to data warehouse in Microsoft Fabric. I hope you enjoyed this video
5:27
If you do, like, share with your friends and comment. Thank you and bye for now. Cheers
#Data Management