Big Data
Intermediate 16 min read Big Data

Spark SQL and DataFrames

A typed, optimized table API

DataFrames give Spark a schema-aware, Catalyst-optimizable API that behaves like SQL with the type safety of a table. You write once, and it runs on notebooks, in ETL and in production jobs identically.

Spark SQLDataFramecatalystschemaSQLstructured API
Prerequisites: Spark Architecture
Before we start

What you will learn

  • Distinguish DataFrame from RDD and explain why Spark prefers DataFrames
  • Write equivalent DataFrame and Spark SQL queries
  • Know when to register temp views vs use the DataFrame API
Introduction

What is this?

Spark SQL and DataFrames are Spark's structured API. A DataFrame is a distributed collection of rows with a schema; operations on it go through Catalyst, Spark's query optimizer, so they can be pushed into file formats, fused and vectorized. Spark SQL is the exact same engine exposed as SQL statements against registered tables and views.

Motivation

Why do you need it?

  • Schema-awareness lets Catalyst and Tungsten optimize like a database, not a generic map-reduce.
  • The same code runs on Parquet, S3, JDBC and streaming sources with uniform execution.
  • Analysts can write the SQL they know; engineers get a typed API with a common engine.
  • SQL/DataFrame interfaces cut boilerplate vs hand-rolled RDD logic by an order of magnitude.
Think about it

Mental model

Imagine

A spreadsheet with typed columns that also speaks SQL

Think of

Spark SQL/DataFrames

It queries like Excel's pivot tables feel familiar, but the engine is a real query planner: you declare what you want (group by country) and the engine decides how to get it fast.

Mechanics

How it works

  • A DataFrame captures a logical plan over data with a known schema (columns + types).
  • Queries are converted to SQL-style logical plans, then optimized by Catalyst.
  • The optimizer produces a physical plan of operators (scan, filter, exchange, aggregate, join).
  • Tungsten executes it with compiled code and vectorized memory, writing results to Parquet or tables.
  • Temporary views register DataFrames so SQL and DataFrame APIs can be mixed in one program.

DataFrame/SQL execution path.

Sources
DataFrame/SQL
Catalyst
Physical plan
Result
Hands-on

Examples

DataFrame vs Spark SQL, same result

DataFrame vs Spark SQL, same result
-- Spark SQL
CREATE OR REPLACE TEMP VIEW sales_v AS SELECT * FROM parquet.'sales.parquet';
SELECT cat_id, SUM(amount) FROM sales_v GROUP BY cat_id;
 
# DataFrame API
df.groupBy("cat_id").agg(F.sum("amount")).show()

Both expressions compile to the same optimized plan. SQL is convenient for analysts and ad-hoc checks; the DataFrame API is convenient inside code where you want type inference and refactoring.

Real world

In production

  • A nightly job reads partitioned Parquet, filters to yesterday, writes a summarized table — all via DataFrame API with a consistent schema.
  • An analyst runs exploratory SQL against a temp view, then the same query as a view is promoted into the pipeline.
  • A streaming job uses micro-batch SQL windows, because Spark SQL on streaming sees the same optimizer as batch.
Guidelines

Best practices

  • Define schemas explicitly for JSON/CSV reads — inference costs a full extra pass and guesses wrong on nulls.
  • Partition output on the dominant filter key and write a fixed small number of partition columns.
  • Prefer columnar output (Parquet) so future pushes the cost of your queries downstream.
  • Register frequently used DataFrames as temp views so SQL and DataFrame styles compose.
Watch out

Common mistakes

  • Omitting schema on CSV reads, letting null-inferred types silently drop or corrupt values.
  • Read-write loops that re-read the source table instead of caching in memory once.
  • Mixing pandas and Spark DataFrames via .toPandas() on big data — driver OOM.
  • Using UDFs where Spark SQL expressions (F.when, F.coalesce) would be faster and safer.
Root cause analysis

Common issues

Error
CSV with 1M nulls infers an integer column as string
Root cause

Schema inference on a sample misjudged types

Fix

Provide an explicit schema so types and nulls are handled deterministically.

Error
Slow read: scanning all partitions before a WHERE
Root cause

No filter pushdown — predicate file at scan wasn't used

Fix

Use parquet partitioning/bucketing and ensure the filter is on a partition column.

Error
toPandas() OOMs the driver
Root cause

The whole result is collected to one machine

Fix

Aggregate first, sample, or write the result to Parquet and read it back locally.

When things break

Debugging tips

  • Print explain(true) to see if the plan honors your filter (pushdown) or fetches everything.
  • If counts look wrong, check whether you forgot to cast (schema inference) or dropped duplicates.
  • Row-count diffs between source and target often mean an unchecked filter or join mode.
  • Use spark.read and the DataFrame method to avoid parsing bugs with delimiter inference.
Interviews

Interview checkpoint

Practice

Hands-on lab

Analyze Sales DataFrames with SQL and the DataFrame API
30 min · intermediate
In progress
Objectives
  • Read Parquet with an explicit schema and register a temp view
  • Answer the same question in Spark SQL and DataFrame API
  • See filter pushdown and a plan consequence of each query

Analyze Sales DataFrames with SQL and the DataFrame API

30 min · intermediate

0/4 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/2
1

Reading a CSV without an explicit schema causes...

2

Spark SQL and the DataFrame API run through the same optimizer.

Go deeper

Official documentation

Spark SQL guide
docs
Spark PySpark DataFrame API
docs