Using Spark SQL · Topic 2 of 3

Querying Files Directly and Save Modes

Databricks Spark Developer: run SQL on ORC, JSON, CSV, text and Delta files with format.`path`, and pick the right save mode when writing data out.

  • 4 min
In a nutshell
  • The syntax is `SELECT ... FROM format./path `, with backticks around the path.
  • It works for ORC, JSON, CSV, text, Parquet and Delta files.
  • CSV queried this way gives _c0, _c1 columns. Text gives a single value column.

What it is

Spark SQL can query a file without first creating a table or a DataFrame. You put the format name in front of the path in the FROM clause, and Spark reads the files as a table.

When you write data out, the save mode decides what happens if something already exists at the target. The exam asks which mode fits a requirement and what happens by default.

SQL textSELECT from format.`path`spark.sqlDataFrameschema from the fileswrite with a save modeTargetappend, overwrite, error, ignore

How it works

SQL directly on files

SELECT * FROM parquet.`/data/sales`;
SELECT * FROM json.`/data/events`;
SELECT * FROM delta.`/data/orders`;

The format name goes before a dot, and the path sits between backticks, not quotes. The same form works for orc, csv, text, json, parquet and delta. Run it in a notebook SQL cell or from Python:

df = spark.sql("SELECT id, total FROM orc.`/data/sales` WHERE total > 100")
df.show(5)
FormatWhat you get
Parquet, ORC, DeltaColumns and types from the file metadata
JSONColumns inferred from the data, so Spark scans it
CSVColumns named _c0, _c1 and so on, with no header handling and every column a string
TextOne column named value, one row per line

The direct syntax takes no options. If you need a header, a delimiter or a schema for CSV, create a view instead:

CREATE TEMPORARY VIEW sales_csv
USING csv
OPTIONS (path '/data/sales.csv', header 'true', inferSchema 'true');

Delta files carry a transaction log, so a direct query returns the current version. The log also keeps older versions for time travel until VACUUM removes the files they need.

Partitioned output and direct queries

If a folder was written with partitionBy("country"), a direct query on the folder path finds the country=... subdirectories and returns country as a column. Filtering on it reads only the matching directories.

You can wrap a direct query in a view or a table, but the view stores only the query. The files stay where they are and are read again at each query.

Save modes

ModeIf data already exists at the target
error or errorifexists (default)Fails with an error
appendAdds the new rows to the existing data
overwriteReplaces the existing data with the new data
ignoreSkips the write and leaves the existing data unchanged
df.write.mode("append").parquet("/out/sales")
df.write.mode("ignore").parquet("/out/sales")
df.write.parquet("/out/sales")

The third line uses the default. If /out/sales already holds data, it raises an AnalysisException saying the path exists. The first adds rows, and the second does nothing silently, with no error.

In SQL, the equivalents are INSERT INTO to append and INSERT OVERWRITE to replace. A Delta table overwritten with overwrite keeps the old files for time travel until VACUUM, so storage does not shrink at once.

Append twice and you hold the data twice. Append is not an upsert, and it does not check for duplicates. Use a merge on a Delta table when you need updates.

How the exam asks about this

  • "Query a folder of Parquet files with SQL and no table." `SELECT * FROM parquet./path `.
  • "A nightly job must fail loudly if the output already exists." Keep the default errorifexists.
  • "A job should write once and skip silently on rerun." Use ignore.

What to remember for the exam

  • The syntax is `SELECT ... FROM format./path `, with backticks around the path.
  • It works for ORC, JSON, CSV, text, Parquet and Delta files.
  • CSV queried this way gives _c0, _c1 columns. Text gives a single value column.
  • Use a temporary view with USING and OPTIONS when CSV needs a header or schema.
  • Default save mode is errorifexists. Append adds, overwrite replaces, ignore skips.
  • A Delta overwrite keeps old versions for time travel until VACUUM runs.
Watch out

ignore does not raise an error and does not write. A job that reruns with ignore can look successful while the target still holds the old data.

Common mistakes

Using quotes around the path. FROM parquet.'/data/sales' is a syntax error. The path needs backticks.

Expecting header names from CSV in a direct query. The columns are _c0 onward. Define a view with header 'true' or read with the DataFrame API.

Mixing up ignore and append. Ignore writes nothing when data exists, while append writes the new rows next to the old ones.

Check yourself

  • What does df.write.parquet(path) do when the path already has data?

    It fails with an error, because the default save mode is errorifexists.

  • How do you read a CSV with a header row using SQL?

    Create a temporary view with USING csv and OPTIONS that set path and header, then query the view.

  • Which save mode leaves existing data untouched without raising an error?

    ignore.

Topic 2 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 AccessTry 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.