Welcome
Welcome to Excel Jet Consult blog post. In this tutorial, we are going to talk about writing Inner Join SQL Query in Fabric Notebook using PySpark. Let’s get started
Video
What is PySpark?
PySpark is a Python API for Apache Spark which is a distributed computing framework that allows users to process large-scale data in parallel and with high performance1. Fabric notebooks are web-based interactive environments that support PySpark and other Spark languages, such as Scala, SQL, and R2.
Advantages of using PySpark in Fabric notebooks
As a Data Scientist or analyst, you can write and execute PySpark code in a familiar and intuitive way, with syntax highlighting, error checking, code completion, and rich visualizations. In addition, users can use multiple languages in the same notebook by using magic commands, such as %%pyspark, %%spark, %%sql, and %%spark. Users and data scientist can access and analyze data from your lakehouse, which is a unified data platform that combines the best of data lakes and data warehouses. The last but not the least, you can leverage the power and scalability of Spark to perform complex data transformations, machine learning, and streaming analytics.
Connecting to Data in the Lakehouse
In this blog, we are going to use the dimension tables and the fact table in a lakehouse as seen below
Spark SQL Module
Next, we created a DataFrame called df by using the spark.sql method whoch allows execution of SQL queries on data stored in my lakehouse and then we selected all the columns in the dbo_fTransaction in the WH_Data_Lakehouse and returns only the first 10 rows. Next, we used the display function to show the content of the df DataFrame as seen in the picture below:
Inner Join Query using Spark
Next, I inserted a new cell and executed this query:
TotalSalesByProductAndStore = """ SELECT p.Product, s.Store, SUM(f.SalesAmount) AS TotalSales FROM dbo_dimProduct p INNER JOIN dbo_fTransaction f ON p.ProductKey = f.ProductKey INNER JOIN dbo_dimStore s ON f.Storekey = s.StoreKey GROUP BY p.Product, s.Store ORDER BY p.Product """ df = spark.sql(TotalSalesByProductAndStore) display(df)
The above SQL query retrieves the total sales amount for each product and store by joining three tables: dbo_dimProduct (aliased as p), dbo_fTransaction (aliased as f), and dbo_dimStore (aliased as s). Then, I grouped the results by product and store, calculating the sum of sales amount for each group. Finally, the results are ordered by product.
The PySpark code uses the SQL query defined in the TotalSalesByProductAndStore string and executes it using Spark SQL. The result is stored in a DataFrame named df and the display(df) function is used to show the contents of the DataFrame in a visual format.
From the screenshot below, we can see TotalSales by Product and Store!
Display Result using Chart
One of the amazing thing about the Fabric notebook is that we can visualize the result using charts. In the screenshot below, I clicked on the Chart tab and the Notebook automatically created a Bar Chart Visual. Interesting, I can customer the chart by changing the Chart Types, Aggregation, Key and Values. To do all that, click on the Customer chart located at the top right corner of the output.
In conclusion, the possibility of writing complex SQL query using the Fabric Notebook PySpark is a game-changer for Data Scientists, Analysts and users. See you in the next tutorial