Using Spark SQL · Topic 1 of 3

Reading and Writing JDBC and File Sources

Databricks Spark Developer: read JDBC databases and files into DataFrames, write them back, and use overwrite and partitionBy to control the output layout.

4 min

In a nutshell

spark.read loads and df.write saves, with format, options and a path or table.
Parquet and ORC carry schemas and support pruning. CSV and JSON need a schema or inference.
partitionBy writes one subdirectory per value, and filters on that column skip the other directories.

What it is

Spark reads data from many sources into DataFrames and writes DataFrames back out. The two groups the exam stresses are files (Parquet, ORC, JSON, CSV, text, Delta) and JDBC databases reached through a driver.

All sources use the same two entry points: spark.read to load and df.write to save. Both take a format, options, and a path or table.

How it works

Reading files

df = (spark.read.format("csv")
    .option("header", "true")
    .schema("id INT, name STRING, signup DATE")
    .load("/data/customers"))

Shortcuts exist for each format: spark.read.csv, .json, .parquet, .orc and .text. Without a format, Spark uses spark.sql.sources.default, which is Parquet in open source Spark.

Format	Notes
Parquet, ORC	Columnar, carry their own schema, support column pruning and predicate pushdown
JSON	Schema is inferred unless you give one. Inference scans the data
CSV	No types by default. Use header, inferSchema or an explicit schema
Text	One column named value, one row per line
Delta	Table format with transactions and time travel, read with format("delta")

Giving an explicit schema avoids the extra job that inference runs over the data, and it avoids wrong guesses.

Writing files, overwrite and partitionBy

(df.write
   .mode("overwrite")
   .partitionBy("country")
   .parquet("/out/customers"))

partitionBy("country") creates one subdirectory per value, such as country=US/, and drops that column from the data files because the directory name carries it. A later filter on country reads only the matching directories, which is called partition pruning.

mode("overwrite") replaces existing data at the path. By default it replaces the whole target, even if you only write a few partitions. Setting spark.sql.sources.partitionOverwriteMode to dynamic replaces only the partitions present in the new data.

Choose partition columns with low to moderate cardinality that appear in filters, such as date or country. A column like user ID creates millions of tiny directories and slows everything down.

JDBC sources

A JDBC read needs the database's driver jar on the classpath, a URL and either a table or a query.

df = (spark.read.format("jdbc")
    .option("url", "jdbc:postgresql://host:5432/shop")
    .option("dbtable", "public.orders")
    .option("user", user).option("password", pw)
    .option("partitionColumn", "id").option("lowerBound", "1")
    .option("upperBound", "1000000").option("numPartitions", "8")
    .load())

Without the partitioning options, Spark reads the table through one connection into one partition. With partitionColumn, lowerBound, upperBound and numPartitions together, it issues parallel range queries. The bounds set the stride only and do not filter rows. The column must be numeric, date or timestamp.

Use dbtable or query, not both. A write uses df.write.format("jdbc") with the same url and table options and a save mode. numPartitions also caps the number of parallel write connections. With overwrite, Spark drops and recreates the table unless you add truncate set to true, which keeps the table definition.

How the exam asks about this

"A large JDBC table is read slowly, through one task." Add partitionColumn, lowerBound, upperBound and numPartitions.
"Write output so queries filtering on year read less data." Use partitionBy("year").
"A CSV read is slow because Spark scans the file twice." Replace inferSchema with an explicit schema.

What to remember for the exam

spark.read loads and df.write saves, with format, options and a path or table.
Parquet and ORC carry schemas and support pruning. CSV and JSON need a schema or inference.
partitionBy writes one subdirectory per value, and filters on that column skip the other directories.
JDBC parallel reads need all four of partitionColumn, lowerBound, upperBound and numPartitions.
The bounds only set the stride. They do not filter rows.
Overwrite replaces the whole target by default. Dynamic partition overwrite replaces only the partitions written.

Watch out

Partitioning by a high cardinality column such as an ID creates a huge number of small directories and files, which hurts both writes and reads.

Common mistakes

Setting numPartitions without a partition column. On a JDBC read, numPartitions alone does not split the query. The range options and the column must all be given.

Reading the bounds as a filter. Rows outside lowerBound and upperBound are still read, in the first and last partitions.

Overwriting one partition and losing the rest. With the default static mode, overwrite with partitionBy can delete every existing partition. Switch to dynamic mode when only some partitions should change.

Check yourself

What does partitionBy("country") produce on disk?
One subdirectory per country value, such as country=US, which lets later filters on country skip other directories.
Why is a JDBC read without partitioning options slow on a large table?
Spark reads through a single connection into a single partition, so one task does all the work.
How do you avoid the inference job when reading CSV?
Supply an explicit schema instead of using inferSchema.

Topic 1 of 3 in Using Spark SQL

Test yourself on Using Spark SQL

The full Databricks Associate Developer for Apache Spark question bank has 600 questions, with timed practice exams and an explanation for every answer.

Get Full Access Try Sample Exam

This study guide is independent and is not affiliated with, endorsed by, or sponsored by Databricks. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.