Spark SQL Guide

SET PATH

Description

SET PATH changes the SQL Path of the current session.

The SQL Path is an ordered list of catalog-qualified schema names that Spark walks when resolving unqualified references to functions, tables, views, and session variables in queries and DML (SELECT, INSERT, UPDATE, DELETE, MERGE). The first match wins. DDL (CREATE TABLE, CREATE VIEW, CREATE FUNCTION, DROP, ALTER, …) resolves unqualified object names against current_catalog.current_schema, not the path; so CREATE TABLE t always creates t in the current schema regardless of the path.

The path can include two virtual namespaces in the system catalog:

SET PATH is controlled by spark.sql.path.enabled. When it is false (the default), SET PATH raises UNSUPPORTED_FEATURE.SET_PATH_WHEN_DISABLED. Unqualified resolution and current_path() still use the default path.

The initial value of PATH in a session is DEFAULT_PATH. DEFAULT_PATH is either the value of spark.sql.defaultPath, or, when that configuration is empty, a built-in value composed of system.builtin, system.session, and the current schema. To override, set spark.sql.defaultPath. See the DEFAULT_PATH parameter for the exact derivation rules.

The effect of SET PATH is scoped to the current session and is lost when the session ends. To re-apply the current default path mid-session, run SET PATH = DEFAULT_PATH. (This stores a snapshot of DEFAULT_PATH at the moment of the statement; later changes to spark.sql.defaultPath are not picked up automatically.) Cloned sessions inherit the parent’s path at clone time; later changes in the child do not propagate back.

Persistent views and SQL UDFs capture the path at CREATE time into the object’s metadata. Each invocation resolves the body against that frozen path, not the invoker’s current path; current_schema() and current_path() inside the body still return the invoker’s context.

The leading names session and builtin have special meaning in 2-part references; see Reserved system names.

Syntax

SET PATH = path_element [ , ... ]

path_element
    { DEFAULT_PATH |
      SYSTEM_PATH |
      PATH |
      CURRENT_SCHEMA |
      CURRENT_DATABASE |
      catalog_name . namespace [ . namespace ... ] }

Parameters

Semantics

Error conditions

Condition Cause
UNSUPPORTED_FEATURE.SET_PATH_WHEN_DISABLED SET PATH was issued while spark.sql.path.enabled is false.
INVALID_SQL_PATH_SCHEMA_REFERENCE An entry with fewer than two parts was given.
DUPLICATE_SQL_PATH_ENTRY Two entries collapsed to the same concrete namespace after expansion.

Examples

-- Enable the feature first; the default is false.
> SET spark.sql.path.enabled = true;

-- Observe the default path.
> SELECT current_path();
 system.builtin,system.session,spark_catalog.default

-- Replace the path with explicit entries.
> SET PATH = spark_catalog.default, system.builtin;
> SELECT current_path();
 spark_catalog.default,system.builtin

-- Identifier case is preserved.
> SET PATH = Spark_Catalog.Default, System.Builtin;
> SELECT current_path();
 Spark_Catalog.Default,System.Builtin

-- Backtick-quoted parts that contain a dot round-trip with quoting.
> SET PATH = spark_catalog.`sch.b`, system.builtin;
> SELECT current_path();
 spark_catalog.`sch.b`,system.builtin

-- DEFAULT_PATH and SYSTEM_PATH shortcuts.
> SET PATH = DEFAULT_PATH;
> SELECT current_path();
 system.builtin,system.session,spark_catalog.default
> SET PATH = SYSTEM_PATH;
> SELECT current_path();
 system.builtin

-- SYSTEM_PATH composes naturally with the working schema.
> SET PATH = SYSTEM_PATH, CURRENT_SCHEMA;
> SELECT current_path();
 system.builtin,spark_catalog.default

-- Append an entry by referring to the current path.
> SET PATH = spark_catalog.default, system.builtin;
> SET PATH = PATH, spark_catalog.analytics;
> SELECT current_path();
 spark_catalog.default,system.builtin,spark_catalog.analytics

-- CURRENT_SCHEMA is re-evaluated each time; USE SCHEMA updates the effective path.
> SET PATH = CURRENT_SCHEMA, system.builtin;
> USE spark_catalog.finance;
> SELECT current_path();
 spark_catalog.finance,system.builtin
> USE spark_catalog.default;
> SELECT current_path();
 spark_catalog.default,system.builtin

-- DEFAULT_PATH can be customized via the conf.
> SET spark.sql.defaultPath = system.session, system.builtin, current_schema;
> SET PATH = DEFAULT_PATH;
> SELECT current_path();
 system.session,system.builtin,spark_catalog.default
> RESET spark.sql.defaultPath;

-- Append a schema of shared UDFs so callers do not have to qualify them.
> CREATE SCHEMA spark_catalog.shared_udfs;
> CREATE FUNCTION spark_catalog.shared_udfs.to_iso_date(d DATE) RETURNS STRING
    RETURN date_format(d, 'yyyy-MM-dd');
> SET PATH = PATH, spark_catalog.shared_udfs;
> SELECT to_iso_date(DATE'2026-05-22');
 2026-05-22

-- Drop system.session from the path to force temporary objects to be qualified explicitly.
> CREATE TEMPORARY FUNCTION revenue() RETURNS INT RETURN 42;
> SELECT revenue();                  -- resolves via the default path
 42
> SET PATH = system.builtin, current_schema;
> SELECT revenue();                  -- now must be qualified
 [UNRESOLVED_ROUTINE] `revenue` ...
> SELECT session.revenue();
 42

-- Error cases.
> SET PATH = spark_catalog.default, spark_catalog.default;
  [DUPLICATE_SQL_PATH_ENTRY]

> SET PATH = my_schema_no_catalog;
  [INVALID_SQL_PATH_SCHEMA_REFERENCE]

-- PATH is rejected as a value of the DEFAULT_PATH conf (would cycle).
> SET spark.sql.defaultPath = PATH, system.builtin;
  [Error: invalid value]

-- SET PATH is rejected when the feature is disabled.
> SET spark.sql.path.enabled = false;
> SET PATH = spark_catalog.default;
  [UNSUPPORTED_FEATURE.SET_PATH_WHEN_DISABLED]