SET PATH
Applies to: 
Databricks SQL Databricks Runtime 19 and above
The path is the ordered list of schemas that Databricks searches when a query or DML statement references a table, view, or function without a catalog and schema qualifier. The unqualified subject of a DDL statement is current_catalog.current_schema, not a path lookup, so CREATE TABLE t creates t in the current schema. For how the path applies to queries in DDL statements and to persistent views, see Table and view resolution. For routine bodies, see Function and procedure resolution.
Syntax
SET PATH = path_element [, ...]
path_element
{ DEFAULT_PATH |
SYSTEM_PATH |
CURRENT_SCHEMA |
PATH |
schema_name }
Parameters
-
DEFAULT_PATH
A keyword that expands to the PATH configuration parameter, which is the workspace default that new sessions inherit. If the parameter has not been overridden, the expansion is
system.session, system.builtin, system.ai, CURRENT_SCHEMA. -
SYSTEM_PATH
A keyword that expands to the system-provided schemas:
system.builtin, system.ai. -
CURRENT_SCHEMA
A virtual schema reference that resolves at use time to the current catalog and current schema (
current_catalog().current_schema()). The expansion follows changes made by USE CATALOG and USE SCHEMA during the session. -
PATH
A keyword that expands to the current session path. Use it to extend the path without restating its contents.
-
A two-part name (
catalog.schema) is required. Names with more than two parts are allowed. A single-part name raises INVALID_SQL_PATH_SCHEMA_REFERENCE.The catalog and schema do not need to exist at the time you run
SET PATH. Name resolution skips missing entries and continues with the next path element.
Description
The following rules govern which path elements SET PATH accepts and how the resulting path affects name resolution.
- Duplicates: Duplicate path elements are not allowed. After keyword expansion, duplicates raise DUPLICATE_SQL_PATH_ENTRY.
- System schemas:
system.builtinandsystem.aihold built-in scalar, table, and AI functions.system.sessionholds session-scoped objects such as temporary variables, temporary functions, and temporary views. Leavingsystem.builtinoff the path is generally not advised. - Omitted schemas: Unqualified names resolve only against schemas on the path. If you omit
CURRENT_SCHEMA, qualify persistent objects in the current schema. If you omitsystem.builtinorsystem.ai, qualify those functions, for examplesystem.builtin.abs. If you omitsystem.session, unqualified temporary views, temporary functions, and session variables are not found. - Views and routines: Persistent views, SQL user-defined functions, and
SQL SECURITY DEFINERprocedures capture the path atCREATEand resolve unqualified names in their bodies against that captured path, not the invoker's session path. ASQL SECURITY DEFINERprocedure omitssystem.sessionfrom the captured path. ASQL SECURITY INVOKERprocedure uses the caller's live path. See Captured path. - Inspecting the path:
SET PATH;does not print the path. Use current_path.
Examples
Run the following statements in order, because later statements build on the path that earlier ones set.
-- Restore the workspace default.
> SET PATH = DEFAULT_PATH;
> SELECT current_path();
system.session,system.builtin,system.ai,main.default
-- Restrict the PATH to only the system-provided schemas.
> SET PATH = SYSTEM_PATH;
> SELECT current_path();
system.builtin,system.ai
-- Append a schema to the existing PATH without restating it.
> SET PATH = PATH, libraries.pgsql;
> SELECT current_path();
system.builtin,system.ai,libraries.pgsql
-- Put the current schema ahead of the system schemas so that user-defined
-- functions shadow built-in functions of the same name.
> SET PATH = CURRENT_SCHEMA, PATH;
> SELECT current_path();
main.default,system.builtin,system.ai,libraries.pgsql
The following statements fail and do not change the path.
-- A single-part schema name is rejected.
> SET PATH = my_schema;
[INVALID_SQL_PATH_SCHEMA_REFERENCE]
-- A duplicate path element raises an error after expansion.
> SET PATH = SYSTEM_PATH, system.builtin;
[DUPLICATE_SQL_PATH_ENTRY]