Using Spark SQL · Topic 3 of 3
Persistent Tables and Temporary Views
Databricks Spark Developer: save DataFrames as persistent tables with partitioning, bucketing and sorting, and query temporary and global views with SQL.
- 4 min
- saveAsTable creates a metastore table. Dropping a managed table deletes its data. Dropping an external table keeps the files.
- partitionBy skips directories on filters. bucketBy helps joins and aggregations on the bucket column.
- bucketBy and sortBy work only with saveAsTable.
What it is
A persistent table is registered in the metastore and its data is stored, so it survives after your session ends. A temporary view is only a name for a query result, kept in the session and gone when the session ends.
Both let you query a DataFrame with SQL. Tables also let you choose the physical layout of the data, which speeds up later reads.
How it works
Saving persistent tables
(df.write
.mode("overwrite")
.partitionBy("country")
.saveAsTable("sales_by_country"))saveAsTable writes the data and registers the table. The format comes from spark.sql.sources.default unless you set .format(...). That default is Parquet in open source Spark, and Databricks creates Delta tables.
| Table type | How you get it | On DROP TABLE |
|---|---|---|
| Managed | saveAsTable without a path | Metadata and data are deleted |
| External | saveAsTable with .option("path", ...) or CREATE TABLE with LOCATION | Only metadata is deleted, files stay |
The default mode for saveAsTable is errorifexists, the same as other writes. Use append or overwrite to change it.
Layout for faster retrieval
| Technique | What it does | Helps when |
|---|---|---|
| partitionBy(col) | One directory per value | Queries filter on that column, so other directories are skipped |
| bucketBy(n, col) | Hashes rows into n files per partition by column | Joins or aggregations on that column, which can avoid a shuffle |
| sortBy(col) | Sorts rows inside each bucket | Used with bucketBy, speeds range filters and sort based joins |
(df.write
.bucketBy(8, "customer_id")
.sortBy("order_date")
.saveAsTable("orders_bucketed"))bucketBy and sortBy work only with saveAsTable. They fail with save() or a plain path write. The same layout in SQL looks like this:
CREATE TABLE orders_bucketed (customer_id INT, order_date DATE)
USING parquet
CLUSTERED BY (customer_id) SORTED BY (order_date) INTO 8 BUCKETS;Partition by low cardinality columns that queries filter on. Bucket by a high cardinality join key. Combining the two is allowed, for example partitioning by date and bucketing by customer.
Creating tables in SQL
SQL can create the same tables without a DataFrame. A table with a LOCATION clause is external, and one without it is managed.
CREATE TABLE sales_ext (id INT, amount DOUBLE)
USING parquet
LOCATION '/data/sales_ext';CREATE TABLE ... AS SELECT writes the query result into a new managed table. DROP TABLE follows the managed and external rules above.
Temporary and global temporary views
df.createOrReplaceTempView("recent_orders")
spark.sql("SELECT count(*) FROM recent_orders").show()
df.createGlobalTempView("shared_orders")
spark.sql("SELECT * FROM global_temp.shared_orders").show()| Call | Scope |
|---|---|
| createTempView | Session. Fails if the name already exists |
| createOrReplaceTempView | Session. Replaces an existing view of that name |
| createGlobalTempView | All sessions of the same application, in the global_temp database. Query it as global_temp.name |
A view stores no data. It holds the query plan, so the underlying DataFrame is recomputed each time the view is queried unless that DataFrame is cached. spark.sql(...) and spark.table(name) both return a DataFrame, so SQL and DataFrame code mix freely.
How the exam asks about this
- "Two notebooks in the same application need the same temporary result." A global temporary view, queried as
global_temp.name. - "Speed up repeated joins on
customer_idby avoiding a shuffle." bucketBy that column withsaveAsTable. - "Register a DataFrame so it can be queried with SQL in this session." createOrReplaceTempView.
Remove a temporary view with DROP VIEW or spark.catalog.dropTempView("name"). Dropping a view never touches data files.
What to remember for the exam
- saveAsTable creates a metastore table. Dropping a managed table deletes its data. Dropping an external table keeps the files.
- partitionBy skips directories on filters. bucketBy helps joins and aggregations on the bucket column.
- bucketBy and sortBy work only with saveAsTable.
- createOrReplaceTempView is session scoped. A global temporary view lives in global_temp and is shared across sessions of one application.
- createTempView raises an error if the name exists.
- Views hold a plan, not data.
Querying a global temporary view without the global_temp. prefix fails with a table or view not found error.
Common mistakes
Calling bucketBy with save(). Bucketing needs the table metadata that only saveAsTable records, so a path write raises an error.
Expecting a temporary view to outlive the session. It disappears when the session ends. Use a table for data you need later.
Assuming a view caches its result. Each query reruns the underlying plan. Cache the DataFrame if the view is queried many times.
Check yourself
What happens to the data when you drop a managed table?
Both the metastore entry and the data files are deleted.
Why is a global temporary view queried as
global_temp.name?Global temporary views live in the system database global_temp, and the prefix tells Spark to look there.
Which write call allows bucketBy?
saveAsTable, since bucketBy and sortBy do not work with save or a plain path write.
Topic 3 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.
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.