3.1 SparkSession, Catalogs, Databases, and Namespaces

Apache Spark SQL provides a relational programming abstraction over distributed datasets. To execute structured queries, define tables, or manipulate metadata, applications interact with the driver through foundational entry points and catalog interfaces.

---

1. SparkSession: The Unified Driver Entry Point

Prior to Apache Spark 2.0, applications required separate entry points depending on the intended workload: SparkContext for core RDD operations, SQLContext for standard Spark SQL, HiveContext for querying metadata from an external Apache Hive metastore, and StreamingContext for real-time DStreams. This fragmented design led to configuration synchronization issues and boilerplate code.

Spark 2.0 introduced SparkSession, consolidating these disparate contexts into a single unified driver interface. In modern PySpark and Databricks environments, SparkSession is the primary entry point for reading structured files, executing SQL queries, configuring engine parameters, and interacting with catalog metadata.

The Builder Pattern

SparkSession instances are constructed using the builder pattern via SparkSession.builder. The .getOrCreate() method retrieves an existing active SparkSession within the driver JVM or instantiates a new one if none exists.

from pyspark.sql import SparkSession

# Configure and instantiate a SparkSession
spark = SparkSession.builder \
    .appName("DatabricksCatalogManagement") \
    .config("spark.sql.shuffle.partitions", "16") \
    .config("spark.sql.adaptive.enabled", "true") \
    .enableHiveSupport() \
    .getOrCreate()

Core Properties and Encapsulated Sub-Contexts

Once instantiated, the spark object provides direct access to lower-level subsystems and helper utilities:

• spark.sparkContext: The underlying low-level SparkContext responsible for task scheduling, cluster manager coordination, RDD creation, and hardware metric collection.
• spark.conf: The runtime configuration interface (RuntimeConfig), used to inspect and set dynamic SQL parameters (e.g., spark.conf.set("spark.sql.autoBroadcastJoinThreshold", "10485760")).
• spark.catalog: The catalog interface (Catalog) exposing programmatic metadata operations over databases, tables, functions, and columns.
• spark.read and spark.readStream: The DataFrameReader and DataStreamReader entry points for loading static and streaming datasets.
• spark.udf: The registration interface for SQL-callable user-defined functions; grouped pandas APIs have their own interfaces.

Isolated Sessions: spark.newSession()

A critical exam concept is session isolation. Calling spark.newSession() instantiates a new SparkSession sharing the exact same underlying SparkContext, executor resources, and cluster manager allocation. However, the new session maintains:

1. An isolated SQL configuration environment (independent spark.conf settings).
2. An isolated catalog of standard temporary views.
3. An isolated default database/schema pointer.

# Create an isolated child session
session_analytics = spark.newSession()

# Changes to configuration in session_analytics do NOT alter the parent session
session_analytics.conf.set("spark.sql.shuffle.partitions", "64")
print(spark.conf.get("spark.sql.shuffle.partitions"))            # Outputs: 16
print(session_analytics.conf.get("spark.sql.shuffle.partitions")) # Outputs: 64

Cached tables and physical data cached in memory/disk by the parent session remain accessible across both sessions because they reside in the shared executor memory managed by the single underlying SparkContext.

---

2. Programmatic Catalog Management: spark.catalog

The spark.catalog object exposes metadata management methods without requiring SQL string compilation. In PySpark, list methods return local Python lists of metadata objects, not DataFrames or Scala Datasets.

Metadata Inspection Methods

• spark.catalog.listDatabases(): Returns a Python list of Database objects containing name, catalog, description, and locationUri.
• spark.catalog.listTables(dbName=None): Returns a Python list of Table objects containing name, catalog, namespace, description, tableType, and isTemporary. If dbName is omitted, it lists tables and temporary views in the current database.
• spark.catalog.listColumns(tableName, dbName=None): Returns column metadata including name, description, dataType, nullable, isPartition, and isBucket.
• spark.catalog.listFunctions(dbName=None): Returns registered SQL functions (built-in, UDFs, and external metastore functions) with name, className, and isTemporary.

# Inspect databases currently available in the metastore
databases = spark.catalog.listDatabases()
for db in databases:
    print(f"Database: {db.name} | Location: {db.locationUri}")

# Inspect tables in the current active database
tables = spark.catalog.listTables()
for tbl in tables:
    print(f"Table: {tbl.name} | Type: {tbl.tableType} | Temporary: {tbl.isTemporary}")

Active Context Getters and Setters

To inspect or change the active database programmatically:

# Retrieve current database name
current_db = spark.catalog.currentDatabase()

# Change current database context programmatically
spark.catalog.setCurrentDatabase("finance_analytics")

Cache Administration

spark.catalog provides targeted cache control over specific registered entities:

• spark.catalog.cacheTable("table_name"): Lazy or eager caching of a relational table or view into executor memory.
• spark.catalog.uncacheTable("table_name"): Evicts the specified table from executor memory and storage.
• spark.catalog.isCached("table_name"): Returns True if the table is currently cached.
• spark.catalog.clearCache(): Removes cached table data in the shared cache manager; it does not delete table definitions or all query plans.

Metadata Synchronization

When external systems (such as Hive, Presto, or an external ETL pipeline) write new files or drop partitions directly in object storage, Spark's in-memory catalog cache becomes stale. spark.catalog provides programmatic invalidation:

• spark.catalog.refreshTable("table_name"): Invalidates cached metadata, file block locations, and physical schema definitions for the specified table.
• spark.catalog.refreshByPath("/path/to/data"): Invalidates cached metadata for any table or relation backed by the designated storage path.
• spark.catalog.recoverPartitions("table_name"): Scans the underlying directory structure of a partitioned table and registers any newly detected partition directories into the catalog metastore (equivalent to SQL MSCK REPAIR TABLE).

---

3. Declarative SQL Namespace and Database Commands

In addition to the programmatic Python API, Spark SQL supports ANSI-compliant Data Definition Language (DDL) statements executed through spark.sql("...").

-- Retrieve the active catalog and database
SELECT current_catalog(), current_database();

-- List available databases matching a prefix pattern
SHOW DATABASES LIKE 'prod_*';
SHOW SCHEMAS;

-- Create a new database with explicit location and metadata properties
CREATE DATABASE IF NOT EXISTS retail_analytics
COMMENT 'Centralized retail transactions and customer analytics'
LOCATION 's3://company-lakehouse-warehouse/retail_analytics.db'
WITH DBPROPERTIES ('environment' = 'production', 'tier' = 'gold');

-- Switch active database
USE retail_analytics;

-- Inspect extended metadata, parameters, and storage location
DESCRIBE DATABASE EXTENDED retail_analytics;

Dropping Databases: RESTRICT vs CASCADE

When dropping a database, Spark SQL defaults to RESTRICT mode:

• DROP DATABASE IF EXISTS retail_analytics RESTRICT;: Throws an AnalysisException if the database contains any tables, views, or functions.
• DROP DATABASE IF EXISTS retail_analytics CASCADE;: Recursively drops all child tables and views contained within the database before dropping the database entity itself. For managed tables, physical storage directories are permanently deleted.

---

4. Modern Multi-Catalog Architecture & Three-Tier Namespaces

Traditional Spark deployments relied on a single two-tier namespace: <database/schema>.<table>. Spark 3.0 introduced pluggable catalog provider interfaces (org.apache.spark.sql.connector.catalog.CatalogPlugin), establishing a hierarchical three-tier namespace:

$$\text{catalog} \;\longrightarrow\; \text{schema (database)} \;\longrightarrow\; \text{table / view / volume}$$

Databricks Unity Catalog Architecture

In modern Databricks environments powered by Unity Catalog, governance is organized into three distinct tiers:

1. Catalog (Top Tier): The highest organizational boundary for data assets. Organizations frequently separate catalogs by environment (dev, staging, prod) or business functional units (finance, marketing, supply_chain).
2. Schema / Database (Middle Tier): Logical groupings of related tables, views, volumes, and functions within a catalog.
3. Table / View (Bottom Tier): The actual structured data entities or saved SQL queries.

Identifier Resolution Mechanics

When a query executes, Spark resolves object references based on the number of specified namespace tokens:

• Three-part identifier (prod_catalog.finance.transactions): Fully qualified. Spark directs the query directly to prod_catalog, navigates to finance, and resolves transactions.
• Two-part identifier (finance.transactions): Partially qualified. Spark resolves the schema finance within the currently active catalog returned by current_catalog().
• One-part identifier (transactions): Unqualified. Spark resolves the entity within the currently active catalog and active database returned by current_database().

-- Databricks Unity Catalog example (commands are platform-specific)
-- View registered catalogs
SHOW CATALOGS;

-- Switch active catalog context
USE CATALOG prod_catalog;

-- Switch active schema within the active catalog
USE SCHEMA finance;

-- This unqualified query resolves to prod_catalog.finance.transactions
SELECT * FROM transactions;

---

5. Catalog API vs. Spark SQL Command Reference

The following reference table summarizes the programmatic spark.catalog methods alongside their exact Spark SQL declarative equivalents:

Session lifecycle is not application duplication

getOrCreate() can reuse an existing session rather than start another application. newSession() creates isolated SQL configuration and temporary-view state while sharing the SparkContext and cache manager. In classic Spark, SparkSession.stop() stops that underlying context, so stopping one shared-context session can end work for the others; it is not a way to close just one isolated SQL namespace while leaving the application untouched. Let managed notebook/platform lifecycle rules control shared compute. Spark Connect has separate remote-session semantics, so do not generalize classic context access to a thin client.