Spark SQL vs. Spark SQL

Thank you Anonymous, 

Your reply is really helpful and clarifying! 😀

I have a further query:

I prefer the simplicity of using the %%sql magic.

I think it's easier to write Spark SQL code in a %%sql magic cell than in a PySpark cell (spark.sql-method).

(However I realized there are some ways of making it easier to write sql inside the spark.sql-function, see next reply from me).

There is one thing I am curious about in your reply, because it seems like an attractive option:

"Method B (%%sql Magic)

Purpose: This method is a magic command used within Fabric Notebooks to execute Spark SQL queries directly within the notebook cells. It's more concise and interactive.
DataFrames: While it doesn't directly create DataFrames, you can capture the output using the display() function or assign the query to a variable like any other Python expression."

How can I capture the output by using the display() function or assigning the query to a variable?

Let's say I have this %%sql magic code in a notebook cell, how can I capture the output of this cell for use in subsequent cells?

%%sql
SELECT 
    SalesOrderNumber,
    CustomerName,
    OrderDate,
    Item,
    Quantity,
    UnityPrice,
    Tax,
    Year,
    AVG(UnityPrice) OVER (PARTITION BY CustomerName) AS AvgUnitPricePerCustomer
FROM orders
ORDER BY OrderDate DESC;

One method I found, which seems works for me, is to use the %%sql magic cell to create temporary view, and then I can reference this temporary view in other notebook cells. However it requires me to add another cell with spark.sql if I want to use the temporary view in further transformations.

I can do something like this to create a temporary view:

%%sql
CREATE OR REPLACE TEMPORARY VIEW vw_temporary AS 
SELECT 
    SalesOrderNumber,
    CustomerName,
    OrderDate,
    Item,
    Quantity,
    UnityPrice,
    Tax,
    Year,
    AVG(UnityPrice) OVER (PARTITION BY CustomerName) AS AvgUnitPricePerCustomer
FROM orders
ORDER BY OrderDate DESC;

Then I can reference the temporary view inside a spark.sql() function in a PySpark cell, and easily assign it to a dataframe:

df = spark.sql("SELECT * FROM vw_temporary")
display(df)

Currently, this is the only way I have been able to use the outputs from %%sql magic code in subsequent steps. It works fine, but this way requires two notebook cells (one for the %%sql magic, and another one to assign it to a dataframe).

• I am curious how I can capture the output from the %%sql magic code by using the display() function or assigning the query to a variable.
  
  
• Also, I am curious if it is possible to combine %%sql magic code and PySpark code inside the same notebook cell?

Thank you 😀