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

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

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

Delimited Identifier

  1. `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., ` ).

Examples

  1. -- This CREATE TABLE fails with ParseException because of the illegal identifier name a.b
  2. CREATE TABLE test (a.b int);
  3. Error in query:
  4. [PARSE_SYNTAX_ERROR] Syntax error at or near '.': extra input '.'(line 1, pos 20)
  6. == SQL ==
  7. CREATE TABLE test (a.b int)
  8. --------------------^^^
  10. -- This CREATE TABLE works
  11. CREATE TABLE test (`a.b` int);
  13. -- This CREATE TABLE fails with ParseException because special character ` is not escaped
  14. CREATE TABLE test1 (`a`b` int);
  15. Error in query:
  16. [PARSE_SYNTAX_ERROR] Syntax error at or near '`'(line 1, pos 24)
  18. == SQL ==
  19. CREATE TABLE test1 (`a`b` int)
  20. ------------------------^^^
  22. -- This CREATE TABLE works
  23. CREATE TABLE test (`a``b` int);