Spark SQL vs. Spark SQL

Hi frithjof_v ,

Thanks for using Fabric Community.
I'd be glad to explain the differences between Method A (spark.sql) and Method B (%%sql magic) in Spark SQL for Fabric Notebooks:

Method A (spark.sql)

• Purpose: This method allows you to directly execute Spark SQL queries within your Python code using the spark.sql function.
• DataFrames: You can create DataFrames as the result of your queries and store them in variables for further manipulation.
• Use Cases: Ideal for scenarios where you need to:
  • Write complex Spark SQL queries programmatically.
  • Chain and combine multiple SQL queries with DataFrame operations.
  • Store query results in DataFrames for subsequent transformations.
    
    

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.
• Use Cases: Well-suited for:
  • Running ad-hoc queries for quick exploration and analysis.
  • Debugging and inspecting intermediate results.
  • Creating lakehouse tables, dropping tables, and other DDL (Data Definition Language) operations.

Performance:

There are generally minimal performance differences between the two methods. Both translate your queries into optimized distributed Spark operations. However, complex logic within Method A code might have a slight overhead compared to simpler %%sql queries.

Naming and Terminology:

• Spark SQL: Both methods leverage Spark SQL's capabilities behind the scenes.
• Separate Names: Yes, they have distinct names for clarity:
  • spark.sql: Refers to the Python function for programmatic execution.
  • %%sql: Denotes the magic command specific to Fabric Notebooks.

In Summary:

Choose Method A (spark.sql) when you need to:

• Integrate SQL queries with Python code for complex data processing workflows.
• Store query results as DataFrames for further transformations.

Use Method B (%%sql) for:

• Quick ad-hoc queries and exploration within notebooks.
• DDL operations like creating or dropping tables.

Hope this is helpful. Please let me know incase of further queries.

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 😀