Identifiers

Description

An identifier is a string used to identify a database object such as a table, view, schema, column, etc. Spark SQL has regular identifiers and delimited identifiers, which are enclosed within backticks. Both regular identifiers and delimited identifiers are case-insensitive.

Syntax

Regular Identifier

{ letter | digit | '_' } [ , ... ]

Note: If spark.sql.ansi.enforceReservedKeywords is set to true, ANSI SQL reserved keywords cannot be used as identifiers. For more details, please refer to ANSI Compliance.

Delimited Identifier

`c [ ... ]`

Parameters

letter

Any letter from A-Z or a-z.
digit

Any numeral from 0 to 9.
c

Any character from the character set. Use ` to escape special characters (e.g., `).

Reserved system names

system, session, and builtin have special meaning and should not be used as user-defined catalog or schema names.

Name	Position	Notes
`system`	catalog	Virtual catalog hosting `system.builtin` and `system.session`. Spark does not load `system` through the v2 catalog API; setting `spark.sql.catalog.system = ...` is unsupported and produces undefined results. The current catalog cannot be `system`.
`builtin`	schema	A persistent schema named `builtin` is allowed but discouraged because it collides with `system.builtin`.
`session`	schema	A persistent schema named `session` is allowed but discouraged because it collides with `system.session`.

A partially qualified 2-part reference like builtin.x or session.x walks a small mini-path to choose the implicit catalog: by default it resolves to system.builtin.x / system.session.x if such an object exists, and otherwise falls back to the same name in the current catalog. So an object in a persistent builtin or session schema is shadowed only when an object of the same name exists in the corresponding system namespace. The shadowed object stays reachable via its fully qualified 3-part name (for example spark_catalog.session.x). Set spark.sql.legacy.persistentCatalogFirst to true to reverse the preference: the current catalog is tried first and the system namespace becomes the fallback.

The system.builtin and system.session namespaces are described in SET PATH. Temporary objects in system.session are documented under CREATE VIEW and CREATE FUNCTION (SQL).

Examples

-- This CREATE TABLE fails with ParseException because of the illegal identifier name a.b
CREATE TABLE test (a.b int);
Error in query:
[PARSE_SYNTAX_ERROR] Syntax error at or near '.': extra input '.'(line 1, pos 20)

== SQL ==
CREATE TABLE test (a.b int)
--------------------^^^

-- This CREATE TABLE works
CREATE TABLE test (`a.b` int);

-- This CREATE TABLE fails with ParseException because special character ` is not escaped
CREATE TABLE test1 (`a`b` int);
Error in query:
[PARSE_SYNTAX_ERROR] Syntax error at or near '`'(line 1, pos 24)

== SQL ==
CREATE TABLE test1 (`a`b` int)
------------------------^^^

-- This CREATE TABLE works
CREATE TABLE test (`a``b` int);

Spark SQL Guide