Using Spark SQL

What this chapter covers

Using Spark SQL is 20% of the Databricks Associate Developer for Apache Spark exam, tied with Apache Spark Architecture and Components as the second largest chapter. It covers moving data in and out of Spark and making DataFrames queryable with SQL.

The chapter has three parts: reading and writing JDBC and file sources, including overwrite and partitionBy, running SQL directly on files and choosing a save mode, and saving persistent tables with partitioning, bucketing and sorting or registering temporary views.

Questions are single-answer multiple choice. Many show a short PySpark snippet or SQL statement in the stem or the options and ask what it returns, what happens when the target already exists, which line fixes it, or which call meets a requirement. There are no exhibits.

How the topics fit together

You load data from a database or from files, query it with SQL either directly on the files or through a registered view, and write the result out. A persistent table keeps the data and its layout for later sessions.

• Source questions ask how to read a JDBC table in parallel or which format suits a requirement.
• File query questions ask for the syntax that runs SQL straight on a folder of files.
• Save mode questions ask what append, overwrite, ignore and the default do when data exists.
• Table questions ask which layout speeds retrieval and what happens on DROP TABLE.
• View questions ask which scope a temporary or global view has.

How to use these pages

Each topic page has the same shape, so you can skim for what you need.

• What it is: the idea in plain words.
• How it works: the mechanism, with tables and examples.
• What to remember for the exam: the facts to memorize and how questions are phrased.
• Common mistakes: the classic traps.
• Check yourself: three questions to test your understanding.