DataFrame API
Databricks · Apache Spark on Databricks
Theory
A Spark DataFrame is a distributed table with named, typed columns, like a SQL table spread across the cluster. You create one with spark.read.table("main.sales.orders"), spark.read.format("csv").load(path) or spark.createDataFrame(rows). You then chain transformations: select picks columns, filter (or where) keeps rows, withColumn adds or replaces a column, groupBy().agg() aggregates, join combines DataFrames and orderBy sorts. Functions come from pyspark.sql.functions, usually imported as F.
Every transformation returns a new DataFrame; the original is never changed. For example, orders.filter(F.col("amount") >= 900).groupBy("region").agg(F.sum("amount").alias("total")) returns region totals for big orders only. Spark's Catalyst optimizer turns the chain into an efficient plan, so DataFrame code and the same query in SQL run at the same speed.
Why we use it
The DataFrame API lets you build transformations in Python with variables, functions, loops and tests, while Spark still optimizes and distributes the work. It is the main way data engineers write ETL in PySpark.
When to use it
Use the DataFrame API for ETL logic, reusable functions and dynamic pipelines. Use SQL when the logic is a simple query or the audience is SQL users. Both compile to the same plan, so choose by readability.
How it works
- Read a table into a DataFrame
- Filter rows with filter / where
- Group with groupBy and aggregate with agg
- Sort with orderBy
- Need to keep the result? Yes: Write with saveAsTable. No: display() it for analysis.
- Catalyst runs the optimized plan
Syntax
from pyspark.sql import functions as F
df.select(cols) | df.filter(cond) | df.withColumn(name, expr)
df.groupBy(cols).agg(F.sum(c).alias(n), F.count("*"))
df.join(other, on, how) | df.orderBy(F.desc(c)) | df.dropDuplicates([cols])
df.write.mode("overwrite").saveAsTable(name)Example
from pyspark.sql import functions as F
orders = spark.read.table("main.sales.orders")
big_by_region = (orders
.filter(F.col("amount") >= 900)
.groupBy("region")
.agg(F.sum("amount").alias("total"),
F.count("*").alias("orders"))
.orderBy(F.desc("total")))
display(big_by_region)
big_by_region.write.mode("overwrite").saveAsTable("main.sales.big_orders_by_region")Key points
- DataFrames are immutable: each transformation returns a new DataFrame
- Use F.col("x") or "x" for columns; import functions as F to avoid name clashes
- filter and where are the same; so are groupBy().agg() and SQL GROUP BY
- Use .alias() to name aggregate columns instead of sum(amount)
- DataFrame code and SQL compile to the same optimized plan