Names
Applies to: Databricks SQL Databricks Runtime
Identifies different kinds of objects in Databricks.
The following limitations apply for all object names in Unity Catalog:
Object names cannot exceed 255 characters.
The following special characters are not allowed:
Period (
.
)Space (
Forward slash (
/
)All ASCII control characters (00-1F hex)
The DELETE character (7F hex)
Unity Catalog stores all object names as lowercase.
When referencing UC names in SQL, you must use backticks to escape names that contain special characters such as hyphens (
-
).
Note
Column names can use special characters, but the name must be escaped with backticks in all SQL statements if special characters are used. Unity Catalog preserves column name casing, but queries against Unity Catalog tables are case-insensitive.
Connection name
Identifies a foreign connection.
A foreign connection serves as a link to a foreign system, such as PostgreSQL
and can then be used to reference its catalogs, schemas, and tables.
Parameters
connection_identifier: An identifier that uniquely identifies the connection.
Catalog name
Identifies a catalog. A catalog provides a grouping of objects which can be further subdivided into schemas.
Parameters
catalog_identifier: An identifier that uniquely identifies the catalog.
Examples
> USE CATALOG hive_metastore;
> CREATE CATALOG mycatalog;
-- Creating a catalog with a special character requires back ticks
> CREATE CATALOG `cat-a-log`;
-- Creating a catalog with non ASCII characters requires back ticks
> USE `目录`;
-- space (' '), '/', and '.' are not allowed in catalog names, even with back ticks.
> CREATE CATALOG `cat a log`;
ERROR
Schema name
Identifies a schema. A schema provides a grouping of objects in a catalog.
Parameters
catalog_name: The name of an existing catalog.
schema_identifier: An identifier that uniquely identifies the schema.
IDENTIFIER clause: A mapping of constant
STRING
to a schema name.
Schemas created in hive_metastore
can only contain alphanumeric ASCII characters and underscores (INVALID_SCHEMA_OR_RELATION_NAME).
Examples
> USE SCHEMA default;
> CREATE SCHEMA my_sc;
-- In Hive Metastore, schema names must only consist of ASCII letters, digits and '_'
> CREATE SCHEMA hive_metastore.`a-b`;
Error: INVALID_SCHEMA_OR_RELATION_NAME
-- In Unity Catalog only space (' '), '/', and '.' are not allowed
> CREATE SCHEMA main.`a-b`;
> CREATE SCHEMA `a b`;
Error
-- Use back-ticks to reference or create schemas in Unity Catalog with non-ASCII characters
> CREATE SCHEMA `数据库架构`;
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 ] |
IDENTIFIER clause [ 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.IDENTIFIER clause: A mapping of constant
STRING
to a table name.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 containcurrent_date()
orcurrent_timestamp()
.version: A Integer literal or String literal identifying the version of the Delta table.
-
The name of the credential used to access the storage location.
Tables created in hive_metastore
can only contain alphanumeric ASCII characters and underscores (INVALID_SCHEMA_OR_RELATION_NAME).
If the name is unqualified and does not reference a known table alias, Databricks first attempts to resolve the table in the current schema.
If the name is qualified with a schema, Databricks attempts to resolve the table in the current catalog.
See Table and view resolution for more information on name resolution.
Databricks raises an error if you use a temporal_spec
for a table that is not in Delta Lake format.
Examples
-- A back quoted table name
> SELECT * FROM `Employees`;
-- A table name without back quotes
> SELECT * FROM employees;
-- A schema qualified table name
> SELECT * FROM hr.employees;
-- A schema qualified table name with back quotes for schema and table
> SELECT * FROM `hr`.`employees`;
-- A fully qualified table name
> SELECT * FROM hive_metastore.default.tab;
-- A reference to an information schema table.
> SELECT * FROM system.information_schema.columns;
-- Referencing a path as a table requires back ticks
> SELECT * FROM delta.`somedir/delta_table`;
> SELECT * FROM `csv`.`spreadsheets/data.csv`;
> SELECT * FROM `csv`.`spreadsheets/data.csv` WITH (CREDENTIAL some_credential)
-- Tables in `hive_metastore` can only contain alphanumeric ASCII characters and underscores
> CREATE TABLE hive_metastore.default.t1(c1 INT);
> CREATE TABLE hive_metastore.default.`表一`(c1 INT);
Error: INVALID_SCHEMA_OR_RELATION_NAME
-- Use back-ticks to reference or create tables in Unity Catalog with non ASCII characters
> CREATE TABLE main.`瑞赛奇`.`表一`(c1 INT);
View name
Identifies a view. The view can be qualified with a schema name or unqualified using a simple 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.
IDENTIFIER clause: A mapping of constant
STRING
to a view name.
Views created in hive_metastore
can only contain alphanumeric ASCII characters and underscores (INVALID_SCHEMA_OR_RELATION_NAME).
Examples
-- A back quoted view name
> SELECT * FROM `Employees`;
-- A view name without back quotes
> SELECT * FROM employees;
-- A schema qualified view name
> SELECT * FROM hr.employees;
-- A schema qualified view name with back quotes for schema and table
> SELECT * FROM `hr`.`employees`;
-- A fully qualified view name
> SELECT * FROM hive_metastore.default.tab;
-- Views in `hive_metastore` can only contain alphanumeric ASCII characters and underscores
> CREATE VIEW hive_metastore.default.v1(c1) AS SELECT 1;
> CREATE VIEW hive_metastore.default.`数据库视图一`(c1 INT);
Error: INVALID_SCHEMA_OR_RELATION_NAME
-- Use back-ticks to reference or create tables in Unity Catalog with non ASCII characters
> CREATE VIEW main.`瑞赛奇`.`数据库视图一`(c1) AS SELECT 1;
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.
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.
IDENTIFIER clause: A mapping of constant
STRING
to a column name.
The identified column must exist within the table or view.
Databricks 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.
Warning
If the table schema contains a column named _metadata
, queries will return the column from the data source, and not the file metadata. The _metadata
pseudo column will not be accessible.
Column names in Delta Lake tables without column mapping property ('delta.columnMapping.mode' = 'name'
) must not contain the characters ' '
(space), ','
, ';'
, '{'
, '}'
, '('
, ')'
. '\n'
, '\t'
, and '='
.
Column name in AVRO
tables must start with '_'
or a Unicode letter (including non-ASCII letters) and be followed by a combination of '_'
, Unicode letters and digits.
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 _metadata to retrieve information about rows retrieved from T.
> CREATE TABLE T(c1 INT);
> INSERT INTO T VALUES(1);
> SELECT T._metadata.file_size;
574
-- A delimited column name
> CREATE TABLE T(`sütun1`);
Field name
Identifies a field within a struct. The field must be qualified with the path up to the struct containing the field.
Parameters
expr: An expression of type STRUCT.
field_identifier: An identifier that specifies the name of the field.
IDENTIFIER clause: A mapping of constant
STRING
to a field name.
A deeply nested field can be referenced by specifying the field identifier along the path to the root struct.
Field names in Delta Lake tables without column mapping property ('delta.columnMapping.mode' = 'name'
) must not contain the characters ' '
(space), ','
, ';'
, '{'
, '}'
, '('
, ')'
. '\n'
, '\t'
, and '='
.
Field name in AVRO
tables must start with '_'
or a Unicode letter (including non-ASCII letters) and be followed by a combination of '_'
, Unicode letters and digits.
Variable name
Identifies a temporary (session) variable.
The variable can be qualified with a schema name (system.session
or session
), or unqualified using a simple identifier.
Parameters
schema_name:
system.session
orsession
which contains all temporary variables.variable_identifier: An identifier that specifies the name of the variable.
Function name
Identifies a function. The function can be qualified with a schema name, or unqualified using a simple 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.
IDENTIFIER clause: A mapping of constant
STRING
to a function name.
Functions created in hive_metastore
can only contain alphanumeric ASCII characters and underscores.
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.
Parameters
function_identifier: An identifier that specifies the name of a function.
parameter_identifier: An identifier that specifies the name of a parameter.
Examples
-- Create a function with undelimited parameters and reference them as qualified and nonqualified.
> CREATE FUNCTION area(x INT, y INT) RETURNS INT
RETURN area.x + y;
-- Create a function with non-ASCII character parameters
> CREATE FUNCTION full_name(`prénom` STRING, `nom` STRING) RETURNS STRING
RETURN `prénom` + ' ' + `nom`;
Table alias
Labels a table reference, query, table function, or other form of a relation.
Parameters
table_identifier: An identifier that specifies the name of the table.
column_identifierN: An optional identifier that specifies the name of the column.
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.
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.
Parameters
column_identifier: An identifier that specifies the name of the column.
While column aliases need not be unique within the select list, uniqueness is a requirement to reference an alias by name.
Credential name
Identifies a credential to access storage at an external location.
Parameters
credential_identifier: An unqualified identifier that uniquely identifies the credential.
Location name
Identifies an external storage location.
Parameters
location_identifier: An unqualified identifier that uniquely identifies the location.
Provider name
Identifies a Delta Sharing provider.
Recipient name
Identifies a recipient for a share.
Parameters
recipient_identifier: An unqualified identifier that uniquely identifies the recipient.
Clean Room name
Identifies a clean room for a set of collaborators.
Parameters
clean_room_identifier: An unqualified identifier that uniquely specifies the clean room in the metastores of the collaborators.
Volume name
Identifies a Unity Catalog volume. The volume can be qualified with a schema name or unqualified using a simple identifier.
Parameters
schema_name: A qualified or unqualified schema name that contains the volume.
volume_identifier: An unqualified identifier that uniquely identifies the volume within the schema.