Skip to content

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?

  1. AThree rows in columns _c0 and _c1, the first row holding the words sku and price
  2. BTwo rows in columns sku and price, because the first line is used as the header
  3. CNothing: the query fails, because a CSV file can be queried with SQL only after a schema is given
  4. 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.

Question 2 · choose 1

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?

  1. ASELECT * FROM events USING orc OPTIONS (path '/data/events')
  2. BSELECT * FROM '/data/events' AS orc
  3. CSELECT * FROM orc.`/data/events`
  4. 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.

Question 3 · choose 1

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?

  1. AThe previous complete data, because the overwrite becomes visible only when the whole write succeeds
  2. BAn error, because the path is locked for the duration of the overwrite
  3. CThe new complete data, because readers wait for the write to finish
  4. 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.

Question 4 · choose 1

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?

  1. AINSERT OVERWRITE DIRECTORY '/exports/daily' USING csv OPTIONS (header 'true') SELECT ...
  2. BINSERT INTO DIRECTORY '/exports/daily' USING csv SELECT ...
  3. CCREATE TEMPORARY VIEW daily USING csv OPTIONS (path '/exports/daily', header 'true') AS SELECT ...
  4. DSELECT ... INTO '/exports/daily' FORMAT csv
Show the answer and why
  • AINSERT OVERWRITE DIRECTORY '/exports/daily' USING csv OPTIONS (header 'true') SELECT ...

    Correct

    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.

Question 5 · choose 1

In INSERT OVERWRITE LOCAL DIRECTORY '/tmp/out' USING parquet SELECT * FROM t, what does the LOCAL keyword mean?

  1. AThe output is written only for the current session and removed when it ends
  2. BThe rows are written by the driver alone, without executors
  3. CThe directory is on the local file system
  4. 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.

Question 6 · choose 1

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?

  1. AINSERT OVERWRITE TABLE regions VALUES (101, 'north'), (102, 'south')
  2. BCREATE TABLE regions AS VALUES (101, 'north'), (102, 'south')
  3. CSELECT 101, 'north' INTO regions
  4. 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.

Practice domain 2 →Practice all domains →