Names

Identifies different kinds of objects in Databricks Runtime.

Catalog name

Identifies a catalog. A catalog provides a grouping of objects which can be further subdivided into schemas.

Syntax

catalog_identifier

Parameters

Examples

> USE CATALOG hive_metastore;

> CREATE CATALOG mycatalog;

Schema name

Identifies a schema. A schema provides a grouping of objects in a catalog.

Syntax

[ catalog_name . ] schema_identifier

Parameters

Examples

> USE SCHEMA default;

> CREATE SCHEMA my_sc;

Database name

A synonym for schema name.

While usage of SCHEMA, and DATABASE is interchangeable, SCHEMA is preferred.

Table name

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

Syntax

{ [ schema_name . ] table_identifier [ temporal_spec ] |
  { file_format | `file_format` } . `path_to_table` [ temporal_spec ] [ credential_spec ] }

temporal_spec
{
  @ timestamp_encoding |
  @V version |
  [ FOR ] { SYSTEM_TIMESTAMP | TIMESTAMP } AS OF timestamp_expression |
  [ FOR ] { SYSTEM_VERSION | VERSION } AS OF version
}

credential_spec
  WITH ( CREDENTIAL credential_name )

Parameters

  • schema_name: A qualified or unqualified schema 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_spec: 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 Integer literal identifying the version of the Delta table.
    • timestamp_expression: A simple expression that evaluates to a TIMESTAMP. timestamp_expressiom must be a constant expression, but may contain current_date() or current_timestamp().
    • version: A Integer literal or String literal identifying the version of the Delta table.
  • credential_spec

    You can use an applicable credential to gain access to a path_to_table which is not embedded in an external location.

    • credential_name

      The name of the credential used to access the storage location.

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

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

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

Examples

`Employees`

employees

hr.employees

`hr`.`employees`

hive_metastore.default.tab

system.information_schema.columns

delta.`somedir/delta_table`

`csv`.`spreadsheets/data.csv`

`csv`.`spreadsheets/data.csv` WITH (CREDENTIAL some_credential)

View name

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

Syntax

[ schema_name . ] view_identifier

Parameters

  • schema_name: The qualified or unqualified name of the schema that 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.

Databricks Runtime supports a special _metadata column. This pseudo column of type struct is part of every table and can be used to retrieve metadata information about the rows in the table.

Examples

-- An unqualified column name
> SELECT c1 FROM VALUES(1) AS T(c1);
 c1
 1

-- A qualified column name
> SELECT T.c1 FROM VALUES(1) AS T(c1);
 c1
 1

-- Using _matadata to retrieve infromation about rows retrieved from T.
> CREATE TABLE T(c1 INT);
> INSERT INTO T VALUES(1);
> SELECT T._metadata.file_size;
  574

Field name

Identifies a field within a struct. The field must be qualified with the path up to the struct containing the field.

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 schema name, or unqualified using a simple identifier.

Syntax

[ schema_name . ] function_identifier

Parameters

  • schema_name: A qualified or unqualified schema name that contains the function.
  • function_identifier: An identifier that specifies the name of the function.

Examples

`math`.myplus

myplus

math.`myplus`

Parameter name

Identifies a parameter in the body of a SQL user-defined function (SQL UDF). The function can be qualified with a function identifier, or unqualified using a simple identifier.

Syntax

[ function_identifier . ] parameter_identifier

Parameters

Examples

CREATE FUNCTION area(x INT, y INT) RETURNS INT
RETURN area.x + y;

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

Credential name

Identifies a credential to access storage at an external location.

Syntax

credential_identifier

Parameters

Examples

Location name

Identifies an external storage location.

Syntax

location_identifier

Parameters

Examples

`s3-json-data`

s3_json_data

Share name

Identifies a share to access data shared by a provider.

Syntax

share_identifier

Parameters

  • share_identifier: An unqualified identifier that uniquely identifies the share.

Examples

`public info`

`public-info`

public_info

Recipient name

Identifies an recipient for a share.

Syntax

recipient_identifier

Parameters

Examples

`Good Corp`

`Good-corp`

Good_Corp