Inner Join SQL Query in Fabric Notebook using Spark

In this video, I'm going to show you how to write in a join SQL query using the PySpark

in Fabric Notebook. So let's get started. Now the PySpark is one of the Python libraries

that provide an interface for programming Apache Spark job. So let's see how we can

use that in the Fabric Notebook. Now before we get started, let me just explain what we

have on the screen. In this case, I've got this data in the lake house. Of course, you

can see the dimension tables. Of course, I have the fact table. So you have so many dimension

table. In this cell, we define a df variable. And of course, we initialize the instance

of the Spark module and of course passing the select star from wh underscore data underscore

lake house dot dbo underscore f transaction. Now this is going to be the fact table. And

of course, we have the dimension tables. And of course, just limit the rows to just 10

rows and of course display the content of the data frame. Now I'm going to scroll down

and let's see what we intend to do. We want to write a SQL query using the inner join

to calculate the total sales by product and the stock. So I'm going to click on this add

cell. And then we can begin to write. The first thing I want to do is to define a variable that

I'm going to store the SQL code inside. I'm going to call it total sales by product and

store. Okay, and then just put in the equal to now to write the SQL in the Spark, I'm going to

open three double quotes. So three double quotes. And of course, don't forget three double quotes

And I'm going to come into line two, and then I can begin to write the SQL query. So actually

select the product column and then the store. And I'm going to scroll up now in the fact table

we do have the sales amount column that's one aggregate. So let's scroll down. Now I'm going

to use this sum function, open the brackets, and I want to call this sales amount. Of course

we want to alias as total sales. Okay, and then click Enter. Now, we'll select from the

DBO underscore dim products. So DBO underscore dim product dimension table. And of course

I'm going to alias the name of that table using the P. And I'm going to come back to this product

column and just type a P dot just to provide a proper name convention. And just click Enter

I can use the inner join. Now for the inner join, I want to reference the F transaction fact table

So if transaction, oh, excuse me, DBO underscore F transaction. And I'm going to alias this as F

And I'm going to come back to the sales amount and alias as F dot sales amount column. And I want

to use the on operation. Now, first and foremost, we want to access the product key, which is the

primary key in the dim product table. So I'm going to say P dot product key, which is the primary

key in the dim product table, equal to F dot product key in the fact table. So product key

okay, and then click Enter. So for the second part, we want to undo this term. So inner join

and I'm going to call the DBO underscore dim store dimension table. Now that's exactly what we have

a DBO underscore dim store. And of course, this is the DBO underscore dim product. So I'm going to

alias this as S. And I'm going to come back to this store column and alias as S. And let's continue

So I'm going to use the on operation. Now, in this case, I want to focus on the store key in the F

transaction fact table, which is the foreign key. And of course, the primary key in the store

dim store table. So I can say F dot store key, which is the foreign key equal to S dot store

key, which is the primary key. And then let's click Enter. And then we can group by the two

columns. So P dot product column comma and the S dot store column. And we can even order by

so this is store. Okay, click Enter. I'm going to order by and order by the sum of the F

dot sales amount column. Okay, and then let's order descending. All right, let's go through

the code to be sure everything is fine. First, we selected two tables, the product and of course

the store tables. And of course, we aggregate using the sum function, the sales amount

and of course, the alias as total sales. And then we select from the DB underscore dim product

dimension table, and of course, we alias that particular table with P. And then we use the

inner join. And of course, we specify the DB underscore F transfer the fact table. And of

course, we alias with F. And we use the on operation. And of course, we specify the product key

equal to the same rows of the product key in the F factor, which is the in this case

the foreign key, and this is the primary key. And then for the other part, the store we also

call the inner join DB underscore dim store, and then we alias as S. And we use the on keyword

again, F, the foreign key, the store key equal to the primary key in the store key that is the S

And then we group by the two columns that we specified here, P dot product and S dot store

And then we just order by the sum of sales amount. And that's going to be the code. Now

the next one to do is to initialize the Spark SQL module again. So I'm going to just type

df and equal to spark dot SQL module, and then open the brackets now inside the bracket

I'm just going to pass in the name of this variable total sales by product and store

I'm going to just paste here. And oops, control Z, okay, control V. Okay, let me just get rid of

these. Okay, and then let's get it done. Okay. And finally, we can display the content of the

df this data frame. And let's click on run. Let's scroll down. Absolutely cool. There we go. So we

can see the output, we can see the results. So we have the sales by product and store. And we can

easily tell that the largest total sales was achieved when we have products that equals to

furnitures and store that equal to EB. And that's returned 1,956,607. And the amazing part is that

I can click on this particular object, the charts and see the charts. There we go. So we can see

the clustered bar chart, which is quite amazing. I love this functionality. And of course, we can see

the list of the product. And of course, we can see the volume. And we can even click on this

customized charts, I can switch from the bar charts to clustered column charts. And I'm going

to scroll down and click on apply. And there we go. So we have the cluster column charts quite

amazing. I can even switch to the line chart, just scroll down, click on apply. And there we go. This

is super cool. So this is basically how we can use the Spark SQL to write SQL query in the

Fabric Notebook. I trust you enjoy this video. If you do, like, share and comment. Thank you and bye for now. Cheers