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

  1. Save a DataFrame as a table
  2. Path / LOCATION given? Yes: External: catalog stores the path. No: Managed: under warehouse dir.
  3. Catalog records name, schema, format and location
  4. Any session reads it with spark.table('db.name') or SQL
  5. DROP TABLE on a managed table? Yes: Metadata and files deleted. No: Metadata only; files stay.
  6. 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.orders

Example

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

Apache Spark topics

Spark Fundamentals

Spark Architecture

RDDs

DataFrames

Aggregations & Joins

Spark SQL

Window Functions

Cleaning & Complex Types

UDFs

Performance Tuning

Structured Streaming

MLlib & Ecosystem

Interview Prep

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Master Apache Spark

PySpark and Spark SQL from the ground up: DataFrames, joins, window functions, partitions and performance tuning, Structured Streaming and more. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice PySpark Write real PySpark — DataFrames, joins, window functions and Spark SQL — in a live editor with hidden tests and instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Spark  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Try the same API on Practice PySpark Online right in your browser, or run it locally after pip install pyspark (needs Java 17+), or paste it into a Databricks Free Edition notebook.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic