I85PREP
⌘ K
Databricks Spark Developer Associate study materials
Databricks Spark Developer Associate study resources

Databricks Spark Developer Associate: Using Spark SQL

Databricks Spark Developer Associate · Work through your exam, one topic at a time.

All guide sections

Using Spark SQL

Objects and namespaces

spark.sql("...") returns a DataFrame. You work with databases, managed vs external tables, and views:

  • Temp views are scoped to the SparkSession that created them. Another session/notebook on the same cluster cannot see them.
  • Global temp views live in the global_temp database and persist for the lifetime of the application; query them as global_temp.name.
  • CACHE TABLE caches eagerly by default in SQL; spark.catalog and df.cache() are the programmatic equivalents.

Common statements to recognise: CREATE TABLE ... USING DELTA, CREATE OR REPLACE TEMP VIEW, CTAS (CREATE TABLE t AS SELECT), DESCRIBE, DROP VIEW, REFRESH TABLE, MSCK REPAIR TABLE.

Query features

Spark SQL supports subqueries (scalar, IN, correlated), CTEs (WITH), HAVING, window functions (ROW_NUMBER, RANK, LAG, frame clauses), GROUP BY ... GROUPING SETS/ROLLUP/CUBE, and higher-order functions (transform, filter, aggregate). Column name matching is case-insensitive unless spark.sql.caseSensitive is enabled.

Traps

  • Assuming a temp view persists after the session ends - it does not.
  • Forgetting that SQL joins and aggregations still shuffle and are governed by spark.sql.shuffle.partitions.
  • Confusing CACHE TABLE (takes effect immediately) with DataFrame .cache() (lazy).
  • Using a global temp view without the global_temp. prefix.

Worked example

CREATE OR REPLACE TEMP VIEW high_value AS
SELECT customer_id, SUM(amount) AS total
FROM sales GROUP BY customer_id HAVING SUM(amount) > 1000;

spark.sql("SELECT * FROM high_value").count() triggers a shuffle; the view alone does not execute anything.