Databricks SQL names

Names identify different kinds of objects in Databricks SQL.

Database name

Identifies a database. A database provides a grouping of objects in the catalog.

Syntax

database_identifier

Parameters

Examples

> use default;

> create database my_db;

Schema name

A synonym for database name.

Table name

Identifies a table object. The table can be qualified with a database name or unqualified using a simple identifier.

Syntax

{ [ database_name . ] table_identifier |
  { file_format | `file_format` } . `path_to_table` }
  [temporal_specification]

temporal_specification
{
  @ timestamp_encoding |
  @V version |
  TIMESTAMP AS OF expr |
  VERSION AS OF version
}

Parameters

  • database_name: A qualified or unqualified database name that contains the table.

  • table_identifier: An identifier that specifies the name of the table or table_alias.

  • file_format: One of json, csv, avro, parquet, orc, binaryFile, text, delta (case insensitive).

  • path_to_table: The location of the table in the file system. You must have the ANY_FILE permission to use this syntax.

  • temporal_specification: When used references a Delta table at the specified point in time or version.

    You can use a temporal specification only within the context of a query or a MERGE USING.

    • @ timestamp_encoding: A positive BIGINT literal that encodes a timestamp in yyyyMMddHHmmssSSS format.
    • @V version: A positive INT literal identifying the version of the Delta table.
  • expr: A simple expression that evaluates to a TIMESTAMP. expr must be a constant expression, but may contain current_date() or current_timestamp().

  • timestamp_expression can be any one of:
    • '2018-10-18T22:15:12.013Z', that is, a string that can be cast to a timestamp
    • cast('2018-10-18 13:36:32 CEST' as timestamp)
    • '2018-10-18', that is, a date string
    • current_timestamp() - interval 12 hours
    • date_sub(current_date(), 1)
    • Any other expression that is or can be cast to a timestamp
  • version is a long value that can be obtained from the output of DESCRIBE HISTORY table_spec.

Neither timestamp_expression nor version can be subqueries.

If the name is unqualified and does not reference a known table alias, Databricks SQL first attempts to resolve the table as a table in the current database.

If the name is qualified with a database, Databricks SQL attempts to resolve the table in the current catalog.

Databricks SQL raises an error if you use a temporal_specification for a table that is not in Delta Lake format.

Examples

`Employees`

employees

hr.employees

`hr`.`employees`

delta.`somedir/delta_table`

`csv`.`spreadsheets/data.csv`

View name

Identifies a view. The view can be qualified with a database name or unqualified using a simple identifier.

Syntax

[ database_name . ] view_identifier

Parameters

  • database_name: A qualified or unqualified database name which contains the view.
  • view_identifier: An identifier that specifies the name of the view or the view identifier of a CTE.

Examples

`items`

items

hr.items

`hr`.`items`

Column name

Identifies a column within a table or view. The column can be qualified with a table or view name, or unqualified using a simple identifier.

Syntax

[ { table_name | view_name } . ] column_identifier

Parameters

  • table_name: A qualified or unqualified table name of the table containing the column.
  • view_name: A qualified or unqualified view name of the view containing the column.
  • column_identifier: An identifier that specifies the name of the column.

The identified column must exist within the table or view.

Examples

> SELECT c1 FROM VALUES(1) AS T(c1);
 c1
 1

Field name

Identifies a field within a struct. The column can be qualified with a table or view name, or unqualified using a simple identifier.

Syntax

expr { . field_identifier [. ...] }

Parameters

  • expr: An expression of type STRUCT.
  • field_identifier: An identifier that specifies the name of the field.

A deeply nested field can be referenced by specifying the field identifier along the path to the root struct.

Examples

> SELECT addr.address.name
    FROM VALUES (named_struct('address', named_struct('number', 5, 'name', 'Main St'),
                              'city', 'Springfield')) as t(addr);
  Main St

Function name

Identifies a function. The function can be qualified with a database name, or unqualified using a simple identifier.

Syntax

[ database_name . ] function_identifier

Parameters

Examples

`math`.myplus

myplus

math.`myplus`

Table alias

Labels a table reference, query, table function, or other form of a relation.

Syntax

[ AS ] table_identifier [ ( column_identifier1 [, ...] ) ]

Parameters

If you provide column identifiers, their number must match the number of columns in the matched relation.

If you don’t provide column identifiers, their names are inherited from the labeled relation.

Examples

> SELECT a, b FROM VALUES (1, 2) AS t(a, b);
 a  b
 1  2

> DELETE FROM emp AS e WHERE e.c1 = 5;

Column alias

Labels the result of an expression in a SELECT list for reference.

If the expression is a table valued generator function, the alias labels the list of columns produced.

Syntax

[AS] column_identifier
[AS] ( column_identifier [, ...] )

Parameters

While column aliases need not be unique within the select list, uniqueness is a requirement to reference an alias by name.

Examples

> SELECT 1 AS a;
 a
 1

> SELECT 1 a, 2 b;
 a b
 1 2

> SELECT 1 AS `a`;
 a
 1

> SELECT posexplode(array(2)) AS (i, a);
 i  a
 0  2

> SELECT a + a FROM (SELECT 1 AS a);
 a
 2