Skip to content
Tutorials Center
My learning

Topics

Lesson 17 of 48 ยท DataFrames and Spark SQL

Reading and Writing Data

Read and write CSV, JSON, Parquet, ORC, Avro and JDBC data in Spark, choose write modes and partitionBy, and know when to use an explicit schema.

A Spark program is only as useful as the data it can reach. Almost every job follows the same outline: read data from files or a database, transform it, and write the result somewhere else. Spark has one consistent API for this, spark.read and df.write, with many formats behind it.

Choosing the format and the options matters more than it seems. The same dataset can take seconds or hours to query depending on whether it is stored as one huge CSV or as partitioned Parquet files. This lesson shows the readers and writers, the four write modes, partitionBy, and the difference between letting Spark infer a schema and giving it one. It follows Grouping and Aggregations.

The real readers and writers need a Spark session and a file system, so the Spark code here is read-only. The runnable examples use pandas and in-memory "files" to show the same behavior.

The reader and writer pattern

Reading always starts with spark.read, then a format, options, and a path. Writing mirrors it with df.write.

Python
df = (spark.read
      .format("csv")
      .option("header", True)
      .option("inferSchema", True)
      .load("/data/orders/"))

# shortcuts for the common formats
df = spark.read.csv("/data/orders/", header=True, inferSchema=True)
df = spark.read.json("/data/events/")
df = spark.read.parquet("/data/warehouse/orders/")

(df.write
   .format("parquet")
   .mode("overwrite")
   .save("/data/warehouse/orders/"))

Two facts to remember. First, a path is a directory, not a single file. Spark reads every file inside it (and in sub-directories), and writes one file per partition, plus a _SUCCESS marker. A path can also be a glob such as /data/logs/2026-*/*.json or a list of paths. Second, reading is lazy: spark.read.csv(...) only looks at file listings and, when inferring, a sample of the data. The real scan happens when an action runs.

File formats

Format Kind Notes
CSV text, row-based Human readable, no types, slow. Needs header, sep, quoting options.
JSON text, row-based Expects JSON Lines (one object per line) by default. Use multiLine=True for a single big array.
Parquet binary, columnar The default and the best general choice: compressed, typed, column pruning and predicate pushdown.
ORC binary, columnar Similar to Parquet; common in Hive-centered setups.
Avro binary, row-based Good for streaming and schema evolution; needs the spark-avro package.
Text text One row per line in a single value column.
JDBC database Reads and writes relational tables over a connection.

Columnar formats store each column together. A query that needs 3 of 40 columns reads only those 3, and per-block min/max statistics let Spark skip whole blocks that cannot match a filter. This is why Catalyst Optimizer and Query Plans talks about column pruning and predicate pushdown. Row-based text formats cannot do either, so use them for exchange with other systems and use Parquet inside your pipelines.

Typical CSV and JSON options:

Python
spark.read.csv(
    "/data/orders/",
    header=True,           # first line holds column names
    sep=";",               # field separator
    quote='"',
    nullValue="NA",        # this text becomes null
    dateFormat="yyyy-MM-dd",
    mode="PERMISSIVE",     # keep bad rows (default), or DROPMALFORMED, or FAILFAST
)
spark.read.json("/data/events/", multiLine=False)
spark.read.parquet("/a/", "/b/")        # several paths

Parquet and ORC files carry their own schema, so you rarely configure anything. When many Parquet files have slightly different columns, option("mergeSchema", True) combines them (it costs an extra scan of footers).

Schema inference versus an explicit schema

With inferSchema=True, Spark reads the data an extra time to guess each column's type. That has three costs: the extra pass, a result that can change when the data changes (a column of numbers turns into strings the day someone types "missing"), and wrong guesses for things like IDs with leading zeros. Without it, CSV columns are all strings. JSON infers by default, and also scans the data.

For anything beyond exploration, declare the schema:

Python
from pyspark.sql.types import (StructType, StructField, StringType,
                               IntegerType, DoubleType, DateType)

schema = StructType([
    StructField("order_id", IntegerType(), nullable=False),
    StructField("customer", StringType()),
    StructField("amount", DoubleType()),
    StructField("order_date", DateType()),
])
orders = spark.read.schema(schema).csv("/data/orders/", header=True)

# a DDL string is shorter
orders = spark.read.schema("order_id INT, customer STRING, amount DOUBLE, order_date DATE") \
                   .csv("/data/orders/", header=True)

An explicit schema skips the inference pass, makes the job deterministic, and documents the data. Values that do not fit become null in PERMISSIVE mode; to find such rows, add a _corrupt_record string column to the schema. See Datasets, Schemas and Data Types for the type system in detail.

The first stand-in compares both approaches with pandas. Pandas guesses types too, and it makes the same kind of mistakes.

Python Inferred versus explicit schema

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Writing: modes, partitionBy and file layout

df.write.mode(...) decides what happens when the target already exists:

Mode Behavior if the path exists
error / errorifexists (default) Fail with an exception
overwrite Delete the existing data and write the new data
append Add new files next to the old ones
ignore Do nothing and silently skip the write

The default is the safe one, which is why a rerun of a job often fails with "path already exists". overwrite is convenient and dangerous: it is not atomic for plain file output, so a job that fails half-way can leave you with less data than before. For tables that need atomic updates, use a table format such as Delta Lake or Iceberg, covered briefly in Streaming with Kafka and Delta. Also never read from and overwrite the same path in one job.

partitionBy writes a directory tree by column value:

Python
(orders.write
    .mode("overwrite")
    .partitionBy("country", "year")
    .parquet("/data/warehouse/orders/"))

This creates paths like /data/warehouse/orders/country=DE/year=2026/part-00000-....parquet. The partition columns are stored in the directory names rather than inside the files, and Spark restores them as columns when it reads the directory. A later query with filter(country == "DE") reads only the matching directories (partition pruning).

Pick partition columns with low cardinality that queries filter on, usually dates or a region. Partitioning by a column such as customer_id creates millions of tiny directories and files, the infamous small-files problem (see Performance Tuning). Other useful write options are compression (snappy by default for Parquet, gzip, zstd), repartition(n) or coalesce(n) before the write to control the number of output files (see Partitions and Partitioning), and sortWithinPartitions to cluster the data inside the files.

flowchart TB
    W["orders/"] --> C1["country=DE/"]
    W --> C2["country=FR/"]
    C1 --> Y1["year=2025/<br/>part-0000.parquet"]
    C1 --> Y2["year=2026/<br/>part-0000.parquet"]
    C2 --> Y3["year=2026/<br/>part-0000.parquet"]

A directory tree written by partitionBy("country", "year"): the values live in the folder names.

The next stand-in imitates the write modes and the partitionBy layout with a dictionary acting as the file system.

Python Write modes and partitionBy on a fake file system

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Databases with JDBC

Spark can read a relational table through JDBC, provided the JDBC driver jar is on the classpath (--jars or --packages).

Python
people = (spark.read.format("jdbc")
    .option("url", "jdbc:postgresql://db.example.com:5432/shop")
    .option("dbtable", "public.orders")
    .option("user", "report")
    .option("password", password)        # never hard-code secrets
    .option("fetchsize", 10000)
    .load())

By default the whole table is read by one task over one connection, which is slow for large tables and hard on the database. To parallelize, give Spark a numeric (or date) column and its range:

Python
orders = (spark.read.format("jdbc")
    .option("url", url).option("dbtable", "orders")
    .option("partitionColumn", "order_id")
    .option("lowerBound", 1).option("upperBound", 5_000_000)
    .option("numPartitions", 8)
    .load())

Spark then issues eight queries, each with a WHERE order_id >= ... AND order_id < ... range. The bounds only split the work; rows outside them are still read, in the first and last partition. You can also push a subquery down as dbtable ("(SELECT ... ) AS t") so the database does the filtering. Writing back works with df.write.format("jdbc")..., where numPartitions is the maximum number of parallel connections. Keep that number small so you do not overwhelm the database.

Common mistakes

  • Using inferSchema in production pipelines and getting changed types after a bad file arrives.
  • Forgetting that the output path is a directory with many part files, not one file.
  • Expecting mode("overwrite") to be atomic.
  • Partitioning by a high-cardinality column and creating millions of small files.
  • Reading a huge table through JDBC without partitionColumn and numPartitions.
  • Reading a multi-line JSON array without multiLine=True, which gives only _corrupt_record.

Real versus stand-in. Real Spark reads and writes files in parallel (one task per file split on read, one file per partition on write), handles Parquet, ORC and Avro natively, and talks to databases over JDBC. The pandas and dictionary code only imitates the visible rules: type guessing, null markers, the four write modes and the partition folder layout. No Parquet or JDBC code can run in your browser.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What happens by default when you write a DataFrame to a path that already contains data?

    The default mode is errorifexists. Use overwrite, append or ignore to choose another behavior.
  2. 2. Why is an explicit schema better than inferSchema for a production job?

    Inference reads the data an extra time and can change when the data changes. A declared schema is deterministic and cheaper.
  3. 3. Which format allows column pruning and predicate pushdown?

    Parquet is columnar and stores per-block statistics, so Spark can read only needed columns and skip blocks.
  4. 4. partitionBy('country', 'year') writes the data how?

    partitionBy creates a directory tree. Queries filtering on the partition columns read only matching directories.
  5. 5. A JDBC read of a huge table is slow and uses a single connection. What fixes it?

    With a partition column and bounds Spark issues several range queries in parallel instead of one big query.

Summary

  • spark.read and df.write share one API for CSV, JSON, Parquet, ORC, Avro, text and JDBC; a path is a directory, and reading is lazy.
  • Prefer Parquet (columnar, typed, prunable) inside pipelines; use CSV and JSON for exchange.
  • Declare schemas for repeatable jobs; inference costs an extra pass and can guess wrongly.
  • Write modes are error (default), overwrite, append and ignore; plain overwrite is not atomic.
  • partitionBy creates a folder tree that enables partition pruning, but only works well on low-cardinality columns.
  • JDBC reads need partitionColumn, bounds and numPartitions to run in parallel.

Next: query the same data with SQL in Spark SQL and Temp Views.