Spark SQL is the structured data processing module within Apache Spark, providing a full SQL interface for querying and manipulating distributed datasets alongside DataFrame/Dataset APIs. It leverages the Catalyst optimizer and Tungsten execution engine to generate efficient physical plans from declarative SQL statements. Spark 4.0 introduced ANSI SQL compliance by default, the VARIANT semi-structured data type, PIPE syntax (|>) for readable query chaining, SQL Scripting with procedural control flow, and SQL user-defined functions—all running on JDK 17+ with Scala 2.13. Understanding how Spark SQL translates declarative queries into optimized distributed execution, manages data partitioning across clusters, and selects join strategies is essential for building scalable data pipelines that process terabytes efficiently.
What This Cheat Sheet Covers
This topic spans 20 focused tables and 295 indexed concepts, 186 flashcards. Below is a complete table-by-table outline of this topic, spanning foundational concepts through advanced details.
A jump-to index of every table row in this cheat sheet.
An interactive map of every table and concept in this topic.
Table 1: Core SQL Queries
The everyday building blocks of any query live here — projecting and filtering with SELECT and WHERE, sorting, deduplicating, and combining results with set operations. Spark also adds a few twists worth knowing, like the distinction between global ORDER BY and per-partition SORT BY, plus PIVOT, CTEs, and lateral subqueries for more expressive queries.
| Command | Example | Description | |
|---|---|---|---|
SELECT name, age FROM employees | Projects specific columns from a table or view. | ||
SELECT * FROM employees WHERE age > 21 | Filters rows based on a boolean condition. | ||
SELECT * FROM employees ORDER BY salary DESC | • Sorts the full result set globally • triggers a full shuffle across partitions. | ||
SELECT * FROM employees LIMIT 10 | Returns only the first N rows from the result. | ||
SELECT DISTINCT department FROM employees | Returns rows with duplicate values removed for the specified columns. | ||
SELECT salary * 12 AS annual_salary FROM employees | Assigns a readable name to a computed expression or column. | ||
SELECT CASE WHEN age < 18 THEN 'minor' ELSE 'adult' END AS category FROM t | Conditional expression — SQL equivalent of if-then-else logic. | ||
SELECT name FROM t1 UNION ALL SELECT name FROM t2 | • UNION ALL combines results keeping duplicates• UNION removes duplicates. | ||
SELECT id FROM t1 INTERSECT SELECT id FROM t2 | Returns rows present in both result sets. | ||
SELECT id FROM t1 EXCEPT SELECT id FROM t2 | Returns rows in the first result not present in the second. | ||
SELECT name, (SELECT MAX(salary) FROM employees) AS max_sal FROM employees | A query nested inside SELECT, WHERE, or FROM that returns a single value. | ||
SELECT * FROM orders o WHERE EXISTS (SELECT 1 FROM returns r WHERE r.order_id = o.id) | Tests whether a correlated subquery returns any rows. | ||
SELECT * FROM employees WHERE dept_id IN (1, 2, 3) | Filters rows where a column matches any value in a list or subquery. | ||
SELECT * FROM orders WHERE amount BETWEEN 100 AND 500 | Shorthand for amount >= 100 AND amount <= 500 — inclusive range filter. | ||
SELECT * FROM employees WHERE name LIKE 'J%' | • LIKE matches with wildcards (%, _)• RLIKE matches a regex pattern. | ||
SELECT * FROM employees TABLESAMPLE (10 PERCENT) | • Samples a table by percentage, row count, or bucket • useful for exploratory queries. | ||
SELECT * FROM t1, LATERAL (SELECT * FROM t2 WHERE t1.id = t2.id) | Subquery that can reference columns from the preceding FROM clause — like a correlated join. | ||
WITH active AS (SELECT * FROM users WHERE status = 'active') SELECT * FROM active | • Defines a named temporary result set for the query • improves readability for complex queries. | ||
SELECT * FROM sales PIVOT (SUM(revenue) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4')) | Transposes rows into columns — creates aggregated pivot tables. | ||
SELECT * FROM quarterly_sales UNPIVOT (revenue FOR quarter IN (Q1, Q2, Q3, Q4)) | Rotates columns into rows — inverse of PIVOT. | ||
SELECT * FROM employees SORT BY salary DESC | Sorts data within each partition — does not guarantee global order. | ||
SELECT * FROM events DISTRIBUTE BY user_id | Repartitions data by expression — co-locates rows with the same key without sorting. | ||
SELECT * FROM events CLUSTER BY user_id | • Equivalent to DISTRIBUTE BY + SORT BY on the same columns• repartitions and sorts within partitions. |