Associate Developer for Apache Spark · Domain 2: Using Spark SQL · 20% of the exam
Task 2.2: Execute SQL queries directly on files, including ORC Files, JSON Files, CSV Files, Text Files, and Delta Files, and understand the different save modes for outputting data in Spark SQL
Querying a path with format.`path` syntax, what each file format returns, and the errorifexists, append, overwrite and ignore save modes.
Study it
SQL directly on files and the save modes
Lesson coming
Sample questions
Try each one before opening the answer. Every option is explained, with the Databricks documentation page that proves it.
Question 1 · choose 1
A Unity Catalog volume holds the file /Volumes/main/raw/landing/prices.csv
with these three lines:
sku,price
A-1,9.50
B-7,12.00
A developer runs spark.sql("SELECT * FROM
csv.`/Volumes/main/raw/landing/prices.csv`") on Databricks. What does the
result contain?
AThree rows in columns _c0 and _c1, the first row holding the words sku and price
BTwo rows in columns sku and price, because the first line is used as the header
CNothing: the query fails, because a CSV file can be queried with SQL only after a schema is given
DTwo rows in columns _c0 and _c1, because the header line is skipped but not used for names
Show the answer and why
AThree rows in columns _c0 and _c1, the first row holding the words sku and price
Correct
A direct SQL query on CSV takes no data source options, so the header option keeps its default, false, and the first line is read as data with generated column names.
BTwo rows in columns sku and price, because the first line is used as the header
Incorrect
The header option defaults to false, so the first line is not used for column names.
CNothing: the query fails, because a CSV file can be queried with SQL only after a schema is given
Incorrect
Files can be queried directly with SQL, and a direct CSV query cannot take a schema at all; it runs with the defaults.
DTwo rows in columns _c0 and _c1, because the header line is skipped but not used for names
Incorrect
With the header option at its default, false, no line is skipped: the first line is returned as a row.
Spark SQL can query a file directly with format.`path`. For CSV, Databricks notes that such a direct query cannot specify data source options or a schema, so every CSV option keeps its default. The header option defaults to false: the first line is not used for names and comes back as a data row, and the columns get generated names such as _c0 and _c1.
A folder /data/events holds ORC files. A developer wants to query them with Spark SQL directly, without creating a table or view first. Which statement does that?
ASELECT * FROM events USING orc OPTIONS (path '/data/events')
BSELECT * FROM '/data/events' AS orc
CSELECT * FROM orc.`/data/events`
DSELECT * FROM TABLE(orc, '/data/events')
Show the answer and why
ASELECT * FROM events USING orc OPTIONS (path '/data/events')
Incorrect
USING and OPTIONS belong to CREATE TABLE statements; a direct file query names the format and the path in the FROM clause.
BSELECT * FROM '/data/events' AS orc
Incorrect
A quoted string with an alias is not the file query syntax; the format must prefix the path.
CSELECT * FROM orc.`/data/events`
Correct
Spark SQL queries a file directly with the syntax file_format.`file_path`, for formats such as ORC, Parquet and text files.
DSELECT * FROM TABLE(orc, '/data/events')
Incorrect
This is not the file query syntax; the format prefixes the path, which is quoted with backticks.
Instead of loading a file into a DataFrame with the read API, you can query it directly with SQL. The syntax is file_format.`file_path`, for example SELECT * FROM orc.`examples/src/main/resources/users.orc`, where the format can be TEXTFILE, ORC, PARQUET and others.
In open-source Spark, a nightly job replaces a Parquet output directory with df.write.mode("overwrite").parquet(path). While the job runs, an analyst's query reads the same path. What can the analyst's query see?
AThe previous complete data, because the overwrite becomes visible only when the whole write succeeds
BAn error, because the path is locked for the duration of the overwrite
CThe new complete data, because readers wait for the write to finish
DMissing or partial data, because the overwrite deletes the old data first and is not atomic
Show the answer and why
AThe previous complete data, because the overwrite becomes visible only when the whole write succeeds
Incorrect
Save modes are not atomic, so there is no point at which the new data replaces the old data in one step.
BAn error, because the path is locked for the duration of the overwrite
Incorrect
Save modes do not use any locking, so the read is not blocked.
CThe new complete data, because readers wait for the write to finish
Incorrect
Nothing makes a reader wait: save modes use no locking.
DMissing or partial data, because the overwrite deletes the old data first and is not atomic
Correct
Save modes use no locking and are not atomic, and an overwrite deletes the existing data before writing the new data.
A save mode tells Spark how to handle existing data: error or errorifexists, append, overwrite or ignore. These modes do not use any locking and are not atomic, and an overwrite deletes the existing data before writing out the new data, so a reader can find the directory empty or half written while the job runs.
Working only in Spark SQL, an analyst must write the result of a query as CSV files with a header line into the directory /exports/daily, replacing what is there. Which statement does that?
INSERT OVERWRITE DIRECTORY overwrites the directory with the query result in the file format given by USING, with format options in OPTIONS.
BINSERT INTO DIRECTORY '/exports/daily' USING csv SELECT ...
Incorrect
Writing a query result to a directory uses INSERT OVERWRITE DIRECTORY; the statement has no INSERT INTO form.
CCREATE TEMPORARY VIEW daily USING csv OPTIONS (path '/exports/daily', header 'true') AS SELECT ...
Incorrect
A temporary view gives a query a name; it does not write files to the directory.
DSELECT ... INTO '/exports/daily' FORMAT csv
Incorrect
This is not Spark SQL syntax; directories are written with INSERT OVERWRITE DIRECTORY.
INSERT OVERWRITE [LOCAL] DIRECTORY directory_path USING file_format [OPTIONS (...)] query overwrites the existing data in the directory with the query's rows, using a Spark file format such as TEXT, CSV, JSON, PARQUET or ORC; OPTIONS passes options for that format, such as header for CSV.
In INSERT OVERWRITE LOCAL DIRECTORY '/tmp/out' USING parquet SELECT * FROM t, what does the LOCAL keyword mean?
AThe output is written only for the current session and removed when it ends
BThe rows are written by the driver alone, without executors
CThe directory is on the local file system
DExisting files in the directory are kept and new files added
Show the answer and why
AThe output is written only for the current session and removed when it ends
Incorrect
LOCAL is about the file system of the directory, not the lifetime of the output.
BThe rows are written by the driver alone, without executors
Incorrect
LOCAL does not change how the query runs; it says where the directory is.
CThe directory is on the local file system
Correct
The LOCAL keyword specifies that the destination directory is on the local file system.
DExisting files in the directory are kept and new files added
Incorrect
INSERT OVERWRITE DIRECTORY overwrites the existing data in the directory.
INSERT OVERWRITE [LOCAL] DIRECTORY overwrites the data in a directory with new values, in a Spark file format or a Hive SerDe. The LOCAL keyword specifies that the directory is on the local file system; without it, the path refers to the default file system, such as distributed storage.
An analyst needs to add two rows with literal values, (101, 'north') and (102, 'south'), to the existing table regions with Spark SQL, keeping the rows already there. Which statement does that?
AINSERT OVERWRITE TABLE regions VALUES (101, 'north'), (102, 'south')
BCREATE TABLE regions AS VALUES (101, 'north'), (102, 'south')
CSELECT 101, 'north' INTO regions
DINSERT INTO regions VALUES (101, 'north'), (102, 'south')
Show the answer and why
AINSERT OVERWRITE TABLE regions VALUES (101, 'north'), (102, 'south')
Incorrect
INSERT OVERWRITE replaces the existing data in the table.
BCREATE TABLE regions AS VALUES (101, 'north'), (102, 'south')
Incorrect
CREATE TABLE defines a new table; regions already exists.
CSELECT 101, 'north' INTO regions
Incorrect
This is not Spark SQL syntax for writing rows into a table.
DINSERT INTO regions VALUES (101, 'north'), (102, 'south')
Correct
INSERT INTO inserts new rows into the table, here specified by value expressions.
The INSERT statement inserts new rows into a table or overwrites the existing data in the table: INSERT INTO adds rows, INSERT OVERWRITE replaces the table's data. The rows can be given by value expressions, VALUES (...), (...), or by a query.