Spark SQL and temporary views

Many teams write Spark logic in SQL, and switching freely between SQL and DataFrames gives the clearest code.

Spark SQL and the DataFrame API compile to the same plans, so they perform identically. Choose whichever reads better for each step.

df.createOrReplaceTempView("orders") registers a DataFrame under a name for the current session. createOrReplaceGlobalTempView shares it across sessions as global_temp.orders.
spark.sql("SELECT ... FROM orders") returns a DataFrame you can keep transforming with the API.
spark.sql takes parameters safely: spark.sql("SELECT * FROM orders WHERE amount > :min", args={"min": 10}). Prefer that to building SQL with f-strings, which invites injection bugs.
You can also pass DataFrames directly: spark.sql("SELECT * FROM {t} WHERE x > 1", t=df).

Persistent tables live in a catalog (Hive metastore, Unity Catalog, AWS Glue): df.write.saveAsTable("sales.orders"), spark.table("sales.orders"), spark.catalog.listTables(). Table formats like Delta Lake and Iceberg add ACID transactions, MERGE INTO upserts and time travel.

Spark SQL supports standard joins, CTEs, window functions, CASE, set operations, LATERAL VIEW explode, plus Spark functions like date_trunc, array_contains and from_json. Spark 4 also adds SQL pipe syntax (FROM t |> WHERE x > 1 |> SELECT y) and SQL scripting.

Example

from pyspark.sql import SparkSession, functions as F
spark = SparkSession.builder.master("local[2]").getOrCreate()

orders = spark.createDataFrame([(1, 10.0), (1, 5.0), (2, 7.0), (3, 9.0)],
                               "uid INT, amount DOUBLE")
users = spark.createDataFrame([(1, "NO"), (2, "SE"), (3, "NO")], "uid INT, country STRING")
orders.createOrReplaceTempView("orders")
users.createOrReplaceTempView("users")

by_country = spark.sql("""
    SELECT u.country, SUM(o.amount) AS revenue
    FROM orders o JOIN users u USING (uid)
    WHERE o.amount > :min
    GROUP BY u.country
""", args={"min": 6})

result = by_country.withColumn("share", F.round(F.col("revenue") / 26.0, 2))
print([tuple(r) for r in result.orderBy("country").collect()])
# [('NO', 19.0, 0.73), ('SE', 7.0, 0.27)]
print(spark.sql("SELECT count(*) AS n FROM {t}", t=orders).first().n)   # 4

Remember

SQL and DataFrame code compile to the same optimized plans
Temp views name DataFrames for SQL within a session
Use args= parameters instead of string formatting

Watch out for

Building SQL with f-strings from user input.

Check yourself

Is spark.sql('SELECT ... GROUP BY ...') slower than the same df.groupBy().agg() code?

Official docs: spark.apache.org/docs/latest/api/python/user_guide/sql.html

Example

Remember

Watch out for

Check yourself

Builds on

Unlocks