Tables & the catalog: managed vs external tables
Apache Spark · Spark SQL
Theory
A temp view only lives in your session, but a table is registered in a catalog (metastore) so any session or tool can find it by name. The catalog stores its name, schema, format and location. df.write.saveAsTable('sales.orders') creates a managed table: Spark owns both the metadata and the files, which it places under the warehouse directory (spark.sql.warehouse.dir), and DROP TABLE deletes the data too. Adding .option('path', ...) or CREATE TABLE ... LOCATION creates an external (unmanaged) table: the catalog only points at files you own, and DROP TABLE removes the metadata but keeps the files.
spark.catalog lets you browse and manage this from Python: listDatabases(), listTables(), listColumns(), tableExists(), currentCatalog(), and spark.table() reads a table by name. Plain local PySpark uses an in-memory catalog that disappears when the session ends; enableHiveSupport() uses a persistent Hive metastore, and Databricks uses Unity Catalog with three-level names catalog.schema.table.
Why we use it
Teams share data as named tables, not file paths, and the managed vs external difference decides whether DROP TABLE wipes your data. It is a common interview question and a common production accident.
When to use it
Use managed tables when the platform should own the data lifecycle. Use external tables for data shared with other tools or that must survive a drop. Use temp views for intermediate, session-only results. Prefer catalog names over hard-coded paths in jobs.
How it works
- Save a DataFrame as a table
- Path / LOCATION given? Yes: External: catalog stores the path. No: Managed: under warehouse dir.
- Catalog records name, schema, format and location
- Any session reads it with spark.table('db.name') or SQL
- DROP TABLE on a managed table? Yes: Metadata and files deleted. No: Metadata only; files stay.
- Catalog updated
Syntax
df.write.mode("overwrite").saveAsTable("sales.orders") # managed
df.write.option("path", "/data/orders").saveAsTable("sales.orders_ext") # external
spark.sql("CREATE TABLE t (id INT) USING parquet LOCATION '/data/t'")
spark.catalog.listTables("sales"); spark.catalog.listColumns("sales.orders")
spark.catalog.tableExists("sales.orders"); spark.table("sales.orders")
DESCRIBE TABLE EXTENDED sales.ordersExample
import os, tempfile
from pyspark.sql import SparkSession
wh = tempfile.mkdtemp()
spark = (SparkSession.builder.master("local[2]")
.config("spark.ui.enabled", "false")
.config("spark.sql.warehouse.dir", wh) # where MANAGED tables live
.getOrCreate())
orders = spark.createDataFrame([(101, "North", 55000.0), (102, "West", 30000.0)],
"order_id INT, region STRING, amount DOUBLE")
spark.sql("CREATE DATABASE IF NOT EXISTS sales")
# 1) Managed table: Spark owns metadata AND files (under the warehouse dir)
orders.write.mode("overwrite").saveAsTable("sales.orders_managed")
# 2) External (unmanaged) table: you own the files; the catalog only points to them
ext_path = os.path.join(tempfile.mkdtemp(), "orders_ext")
orders.write.mode("overwrite").option("path", ext_path).saveAsTable("sales.orders_ext")
# 3) Temp view: session-only, no files, not in any database
orders.createOrReplaceTempView("orders_tmp")
# 4) Browse the catalog
print("current catalog:", spark.catalog.currentCatalog())
print([db.name for db in spark.catalog.listDatabases()])
for t in sorted(spark.catalog.listTables("sales"), key=lambda t: t.name):
print(t.name, t.tableType, t.isTemporary)
print([c.name + ":" + c.dataType for c in spark.catalog.listColumns("sales.orders_ext")])
print("table exists:", spark.catalog.tableExists("sales.orders_managed"))
# 5) Read a table back by name
print(spark.table("sales.orders_managed").count(), "rows")
# 6) DROP: managed deletes the data, external keeps the files
managed_dir = os.path.join(wh, "sales.db", "orders_managed")
spark.sql("DROP TABLE sales.orders_managed")
spark.sql("DROP TABLE sales.orders_ext")
print("managed files still there:", os.path.exists(managed_dir))
print("external files still there:", os.path.exists(ext_path))
print("re-read external path:", spark.read.parquet(ext_path).count(), "rows")
# Output:
# current catalog: spark_catalog
# ['default', 'sales']
# orders_ext EXTERNAL False
# orders_managed MANAGED False
# orders_tmp TEMPORARY True
# ['order_id:int', 'region:string', 'amount:double']
# table exists: True
# 2 rows
# managed files still there: False
# external files still there: True
# re-read external path: 2 rows
Key points
- Managed: Spark owns metadata + files; DROP deletes the data
- External: catalog points at your path; DROP keeps the files
- Temp views are session-only and have no files
- Local PySpark uses an in-memory catalog; Hive metastore / Unity Catalog persist
- DESCRIBE TABLE EXTENDED shows Type (MANAGED/EXTERNAL) and Location
- Unity Catalog names are catalog.schema.table