Skip to content
Tutorials Center
My learning

Topics

Lesson 13 of 36 ยท Spark and DataFrames

Spark SQL, Views and UDFs

Mix SQL and Python in Databricks: spark.sql(), temporary views, CTEs, built-in functions, higher-order functions, and when to write a Python or SQL UDF.

SQL and DataFrames in Spark are two faces of the same engine. Both compile to the same optimised plan, so a query written in SQL runs as fast as the equivalent DataFrame code. That freedom matters in Databricks: analysts use SQL, engineers use Python, and they can share the same tables and even the same notebook.

Running SQL from Python

Python
# read-only Databricks code
df = spark.sql("""
    SELECT city, count(*) AS orders, sum(amount) AS revenue
    FROM main.sales.orders
    WHERE order_date >= '2024-01-01'
    GROUP BY city
""")
display(df)

spark.sql(...) returns a DataFrame, which you can keep transforming in Python. Going the other way, a DataFrame can be exposed to SQL as a temporary view:

Python
orders.createOrReplaceTempView("orders_v")      # visible to this session only

spark.sql("SELECT city, sum(amount) FROM orders_v GROUP BY city")

In a SQL cell (%sql) you can then query orders_v directly. Temp views live only in your session, vanish when it ends and are not shared. For something others can use, create a view in the catalog:

SQL
CREATE OR REPLACE VIEW main.sales.big_orders AS
SELECT * FROM main.sales.orders WHERE amount > 100;

A view stores only the query, so it always shows current data. A materialized view stores the result and refreshes it, trading freshness for speed.

Useful SQL features

Common table expressions (CTEs) make long queries readable:

SQL
WITH recent AS (
  SELECT * FROM main.sales.orders WHERE order_date >= date_sub(current_date(), 30)
),
per_city AS (
  SELECT city, sum(amount) AS revenue FROM recent GROUP BY city
)
SELECT * FROM per_city ORDER BY revenue DESC;

Built-in functions cover most needs, with hundreds for strings, dates, math, JSON and arrays: concat_ws, regexp_extract, date_trunc, datediff, from_json, explode, array_contains, coalesce, try_cast. Reach for these before writing your own function, because the optimizer understands them and they run inside the JVM at full speed.

Higher-order functions work on arrays without exploding them:

SQL
SELECT transform(array(1, 2, 3), x -> x * 10) AS tens,
       filter(array(1, 2, 3, 4), x -> x % 2 = 0) AS evens;

Parameter markers make queries safe from injection when built from user input:

Python
spark.sql("SELECT * FROM main.sales.orders WHERE city = :c", args={"c": "Oslo"})

User-defined functions (UDFs)

A UDF is your own function used in a query. There are several kinds:

Kind Written in Speed Notes
SQL UDF SQL Fast Registered in Unity Catalog, shareable
Python UDF (row-at-a-time) Python Slow Rows travel between the JVM and Python
Pandas UDF (vectorised) Python Faster Works on batches via Arrow
Unity Catalog Python UDF Python Moderate Governed function in the catalog
Python
from pyspark.sql import functions as F
from pyspark.sql.types import StringType

@F.udf(returnType=StringType())
def size_label(amount):
    if amount is None:
        return None
    return "big" if amount >= 100 else "small"

orders.withColumn("size", size_label("amount"))

A SQL UDF in the catalog:

SQL
CREATE OR REPLACE FUNCTION main.sales.size_label(amount DOUBLE)
RETURNS STRING
RETURN CASE WHEN amount >= 100 THEN 'big' ELSE 'small' END;

Rule of thumb: use built-in functions first, a SQL expression such as CASE WHEN second, a pandas UDF third, and a plain Python UDF last. The optimizer cannot look inside a Python UDF, so it cannot push filters through it.

Stand-in: SQL with views and functions in SQLite

SQLite is a tiny SQL engine that runs in your browser inside Python. It is not Spark, and its function names differ, but it shows the SQL ideas: tables, views, CTEs and user-defined functions registered from Python.

Python Stand-in (SQLite): view and CTE

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The second stand-in registers a Python function as a SQL function, which is the idea behind a Python UDF, and compares it with a plain SQL CASE expression.

Python Stand-in (SQLite): a Python function used in SQL

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Python or SQL?

Pick the tool that makes the code clearest for your team:

  • SQL for filtering, joining, aggregating and reporting.
  • Python for loops over many tables, API calls, complex parsing, unit-tested functions and ML.
  • Mix them: do the heavy lifting in SQL and hand the DataFrame to Python for the rest.

Because both compile to one engine, you do not pay a speed penalty for choosing SQL.

Common mistakes

  • Registering a temp view and expecting other users or jobs to see it.
  • Writing a Python UDF for something a built-in function already does.
  • Building SQL strings by concatenating user input instead of using parameter markers.
  • Forgetting that a UDF returning the wrong type produces nulls silently.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does spark.sql("SELECT ...") return?

    spark.sql returns a DataFrame you can keep transforming or display.
  2. 2. Who can see a temporary view created with createOrReplaceTempView?

    Temp views are session-scoped. Create a catalog view to share.
  3. 3. Which is usually the slowest way to compute a derived column?

    Row-at-a-time Python UDFs move each row between the JVM and Python and block optimisations.
  4. 4. Why use parameter markers (:name) in spark.sql?

    Parameters are passed as values, not spliced into SQL text.

Summary

  • SQL and DataFrame code compile to the same plan, so mix them freely.
  • Temp views are per session; catalog views are shared; materialized views store results.
  • Prefer built-in functions and SQL expressions; use UDFs only when needed and prefer vectorised ones.
  • Use parameter markers for user-supplied values.

Next: Delta Lake Basics