Apache Spark™ Tutorial: Spark SQL

Spark SQL lets you write SQL against the same tables and the same engine as your DataFrame code. A query and a DataFrame chain results in the same logical plan, so you can pick whichever reads better and mix the two in one notebook without moving data. If you already know SQL, most of what you know carries over.

DataFrames showed the structured API in code. Spark SQL lets you use the same engine through SQL. Everything you selected, aggregated, and joined there, you can write as a query here, and you can switch freely between the two in one notebook.

Learning objectives

By the end of this module, you can:

• Explain why Spark SQL and the DataFrame API produce the same plan and the same result.
• Query a Unity Catalog table and move results between SQL and DataFrames.
• Read a query top to bottom with pipe syntax.
• Reuse a value with a session variable.
• Read semi-structured JSON with VARIANT, and say what ANSI mode and collations change.

Using a coding agent? Copy this prompt to get started quickly:

What Spark SQL is

Spark SQL is the SQL interface to the same engine as the DataFrame API. One parser, one optimizer, and one execution engine serve both, so a SQL statement and the equivalent DataFrame chain produce the same logical plan and the same result. In a Databricks notebook, spark.sql runs a query and returns a DataFrame, so SQL is a first-class interface to the session you already have, not a separate system to install or route around.

How SQL becomes a plan

A SQL statement is parsed into a logical plan, resolved against the catalog, and optimized by Catalyst exactly as a DataFrame chain is. The two results in the same physical plan, so they perform the same. ANSI SQL mode is on by default, so overflow and invalid casts raise an error instead of silently returning null.

Query a table

Result (top rows)

Filtering for fares over $10 changes the ranking you saw in Quick Start: airport-area ZIPs rise, because those trips cost more. 11371 covers LaGuardia, and 11422 sits near JFK.

Mix SQL and DataFrames

createOrReplaceTempView exposes a DataFrame to SQL under a session-scoped name, and spark.sql returns a new DataFrame. SQL is easier for joins and set logic; Python is easier for loops, functions, and control flow. Use whichever fits the step, in the same notebook.

Chain steps with pipe syntax

Pipe syntax chains steps from top to bottom, in the order the data flows, instead of nesting subqueries. This returns the same result as the first query. Each |> step takes the previous result as its input, so you read the query in the order it runs: start from the table, filter, aggregate, order, and limit. The grouping clause belongs to the AGGREGATE step.

Session variables and parameters

A session variable stores a typed value for the current session. Declaring min_fare once lets later queries reuse it with no string building, so the value lives in one place instead of being repeated as a literal. DECLARE OR REPLACE lets you rerun the cell without an "already exists" error.

Semi-structured data with VARIANT

Result

The VARIANT type stores data whose shape varies from row to row. parse_json builds a VARIANT from a JSON string, and variant_get reads a typed value from a path such as $.fare. VARIANT keeps the flexibility of JSON while letting the engine read individual fields without parsing the whole document each time.

ANSI mode and collations

ANSI mode makes invalid operations fail loudly instead of returning null. This raises an error:

Use try_cast when you want a null instead of an error:

Collations control how text is compared and sorted. COLLATE UTF8_LCASE gives case-insensitive matching:

Result

All three rows match, because the collation ignores the case. Without it, only the exact-case 'delta' would match.

Try it yourself

1. Rewrite the "mix SQL and DataFrames" query using pipe syntax.
2. Change min_fare to 20 and rerun. How does the ranking shift?
3. Parse a JSON string with two more fields and read them with variant_get.

Recap

• Spark SQL and the DataFrame API are two interfaces to one engine and one optimizer.
• spark.sql returns a DataFrame; createOrReplaceTempView exposes a DataFrame to SQL.
• Pipe syntax reads top to bottom, in data flow order.
• Session variables reuse a typed value without string building.
• VARIANT reads semi-structured JSON; ANSI mode fails loudly, and try_cast opts back into null.
• Collations control case and sort rules.

What's next

Structured Streaming applies these same queries to data that keeps arriving: the same DataFrame operations and SQL queries run against a stream, write to a sink, and track progress with checkpoints and watermarks.