Spark Tables and Catalog

A DataFrame lives only inside your program. A table gives the data a permanent name that other programs and users can query. The Spark catalog keeps a list of databases, tables, and views, so everyone finds data by name instead of by file path.

The Layman View

A library stores thousands of books. Without a catalog, a visitor must search every shelf. The catalog card says where each book sits. The Spark catalog works as that card system for your data.

Views Versus Tables

Temporary view     Name for a DataFrame; vanishes when the session ends
Global temp view   Name shared across sessions of one application
Table              Permanent entry in the catalog with data stored on disk

Temporary Views

df = spark.read.parquet("/data/sales")
df.createOrReplaceTempView("sales_view")

spark.sql("SELECT region, SUM(amount) FROM sales_view GROUP BY region").show()

A temporary view stores no data. It saves only the query plan.

Managed Tables

Spark owns the data of a managed table. It places files inside the warehouse folder. Dropping the table deletes the data.

df.write.mode("overwrite").saveAsTable("sales_managed")

spark.sql("SELECT COUNT(*) FROM sales_managed").show()

External Tables

An external table points to files that live elsewhere. Spark manages only the table definition. Dropping the table keeps the files safe.

df.write.mode("overwrite").option("path", "/data/lake/sales").saveAsTable("sales_external")

Managed table                       External table

Catalog entry                       Catalog entry
     |                                   |
Files in warehouse folder           Files in your own folder
DROP TABLE --> entry and files      DROP TABLE --> entry only, files stay
   deleted

Choose external tables for shared raw data that other tools also use.

Databases

Databases group tables like folders group files.

spark.sql("CREATE DATABASE IF NOT EXISTS retail")
spark.sql("USE retail")

df.write.saveAsTable("retail.sales")
spark.sql("SELECT * FROM retail.sales LIMIT 5").show()

Exploring the Catalog

spark.catalog.listDatabases()
spark.catalog.listTables("retail")
spark.catalog.listColumns("sales", "retail")
spark.catalog.tableExists("retail.sales")

spark.sql("DESCRIBE EXTENDED retail.sales").show(50, truncate=False)

DESCRIBE EXTENDED reveals the location, the table type, and the schema.

Where Table Definitions Live

Spark stores definitions in a metastore. A local session uses a small built-in store. A shared cluster connects to a Hive metastore so every application sees the same tables.

spark = (SparkSession.builder
         .enableHiveSupport()
         .getOrCreate())

Partitioned and Bucketed Tables

A partitioned table splits files into folders by column values. Queries that filter on the column read only matching folders.

df.write.partitionBy("year", "month").saveAsTable("sales_part")

/warehouse/sales_part/year=2026/month=1/part-0001.parquet
/warehouse/sales_part/year=2026/month=2/part-0001.parquet

Bucketing hashes a column into a fixed number of files. Joins between tables bucketed on the same key avoid a shuffle.

df.write.bucketBy(16, "customer_id").sortBy("customer_id").saveAsTable("orders_bucketed")

Refreshing Cached Information

Spark caches file listings. Run a refresh after another tool adds files to an external table.

spark.sql("REFRESH TABLE retail.sales")
spark.catalog.refreshTable("retail.sales")

Good Habits

  • Use clear database names such as raw, clean, and reporting.
  • Pick external tables for data that must survive a table drop.
  • Choose partition columns with moderate variety. A column with millions of values creates millions of folders.

Querying Tables with SQL and DataFrames

A saved table works in both styles. SQL users write queries, and Python users load the table as a DataFrame. Both paths reach the same data.

sales = spark.table("retail.sales")
sales.groupBy("region").count().show()

spark.sql("SELECT region, COUNT(*) FROM retail.sales GROUP BY region").show()

Dropping and Cleaning Up

spark.sql("DROP TABLE IF EXISTS retail.sales_old")
spark.sql("DROP VIEW IF EXISTS sales_view")
spark.sql("DROP DATABASE IF EXISTS scratch CASCADE")

Check the table type before a drop. A managed table loses its data, and an external table keeps it. The CASCADE keyword removes every table inside the database.

Previous lesson
Back to course
Next lesson