Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-names below:

Names | Databricks Documentation

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.

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.

• connection_identifier: An identifier that uniquely identifies the connection.

SQL

> CREATE CONNECTION my_postgresql ...;

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

• catalog_identifier: An identifier that uniquely identifies the catalog.

SQL

> USE CATALOG hive_metastore;

> CREATE CATALOG mycatalog;


> CREATE CATALOG `cat-a-log`;


> USE `目录`;


> CREATE CATALOG `cat a log`;
ERROR

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

{ [ catalog_name . ] schema_identifier |
  IDENTIFIER clause }

• 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).

SQL

> USE SCHEMA default;

> CREATE SCHEMA my_sc;


> CREATE SCHEMA hive_metastore.`a-b`;
Error: INVALID_SCHEMA_OR_RELATION_NAME


> CREATE SCHEMA main.`a-b`;

> CREATE SCHEMA `a b`;
Error


> CREATE SCHEMA `数据库架构`;

A synonym for schema name.

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

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

{ [ schema_name . ] table_identifier |
  IDENTIFIER clause |
  { file_format | `file_format` } . `path_to_table` } [ temporal_spec ] [ options_spec ] }

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

options_spec
  WITH ( { option_key [ = ] option_val } [, ...] )

option_key
  { identifier [. ...] | string_literal }

• 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 contain current_date() or current_timestamp().
  • version: A Integer literal or String literal identifying the version of the Delta table.

• option_spec: When used defines directives to be passed to a data source such as a credential to access a storage location or 'write.split-size' to control INSERT behavior.

  • option_key

    The option key. The key can consist of one or more identifiers separated by a dot, or a string literal.

    Option keys must be unique and are case-sensitive.

  • option_val

    The value for the option. A constant expression of type BOOLEAN, STRING, INTEGER, or DECIMAL.

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.

SQL

> SELECT * FROM `Employees`;


> SELECT * FROM employees;


> SELECT * FROM hr.employees;


> SELECT * FROM `hr`.`employees`;


> SELECT * FROM hive_metastore.default.tab;


> SELECT * FROM system.information_schema.columns;


> SELECT * FROM delta.`somedir/delta_table`;

> SELECT * FROM `csv`.`spreadsheets/data.csv`;

> SELECT * FROM `csv`.`spreadsheets/data.csv` WITH (CREDENTIAL some_credential)

> INSERT INTO t WITH ('write.split-size' 10) SELECT * FROM s;


> CREATE TABLE hive_metastore.default.t1(c1 INT);

> CREATE TABLE hive_metastore.default.`表一`(c1 INT);
Error: INVALID_SCHEMA_OR_RELATION_NAME


> CREATE TABLE main.`瑞赛奇`.`表一`(c1 INT);

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

{ [ schema_name . ] view_identifier |
  IDENTIFIER clause }

• 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).

SQL

> SELECT * FROM `Employees`;


> SELECT * FROM employees;


> SELECT * FROM hr.employees;


> SELECT * FROM `hr`.`employees`;


> SELECT * FROM hive_metastore.default.tab;


> CREATE VIEW hive_metastore.default.v1(c1) AS SELECT 1;

> CREATE VIEW hive_metastore.default.`数据库视图一`(c1 INT);
Error: INVALID_SCHEMA_OR_RELATION_NAME


> CREATE VIEW main.`瑞赛奇`.`数据库视图一`(c1) AS SELECT 1;

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

{ table_name | view_name }

SQL

> SELECT * FROM `Employees`;


> SELECT * FROM employees;


> SELECT * FROM hr.employees;

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

{ [ relation_name . ] column_identifier |
  IDENTIFIER clause }

• 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 relation.

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.

SQL

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


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


> CREATE TABLE T(c1 INT);
> INSERT INTO T VALUES(1);
> SELECT T._metadata.file_size;
  574


> CREATE TABLE T(`sütun1`);

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

{ expr { . field_identifier [. ...] } |
  IDENTIFIER clause }

• 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.

SQL

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


> CREATE TABLE T(c1 struct<`атрибут1` INT, `атрибут2`>);
> SELECT c1.`атрибут1` FROM T;

Identifies a temporary (session) variable. The variable can be qualified with a schema name (system.session or session), or unqualified using a simple identifier.

{ [ schema_name . ] variable_identifier |
  IDENTIFIER clause }

• schema_name: system.session or session which contains all temporary variables.
• variable_identifier: An identifier that specifies the name of the variable.

SQL

> DECLARE session.myvar = 5;


> SET VAR myvar = 6


> DECLARE system.session.`圆周率` = 3.14;

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

{ [ schema_name . ] function_identifier |
  IDENTIFIER clause }

• 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.

SQL

> CREATE FUNCTION math.myplus(x INT, y INT) RETURN x + y;


> SELECT myplus()


> CREATE FUNCTION `圆周率`() RETURN 3.14;

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

{ [ schema_name . ] procedure_identifier |
  IDENTIFIER clause }

• schema_name: A qualified or unqualified schema name that contains the procedure.
• procedure_identifier: An identifier that specifies the name of the procedure.
• IDENTIFIER clause: A mapping of constant STRING to a procedure name.

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

[ routine_identifier . ] parameter_identifier

• routine_identifier: An identifier that specifies the name of a function or procedure.
• parameter_identifier: An identifier that specifies the name of a parameter.

SQL

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


> CREATE FUNCTION full_name(`prénom` STRING, `nom` STRING) RETURNS STRING
  RETURN `prénom` + ' ' + `nom`;

Identifies a label of a compound statement or looping statement.

• label_identifier: An identifier that specifies the name of a label.

SQL

> BEGIN
    label: LOOP
      LEAVE label;
    END LOOP label;
  END;

Identifies a system defined error class or a user defined condition defined in a compound statement.

• condition_identifier: An identifier that specifies the name of the condition.

SQL

BEGIN
  DECLARE a INT DEFAULT 1;
  DECLARE b INT DEFAULT 5;
  DECLARE EXIT HANDLER FOR DIVIDE_BY_ZERO
    BEGIN
      VALUES (15);
    END;
  SET a = 10;
  SET a = b / 0;
  VALUES (a);
END;
15

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

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

• 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.

SQL

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


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


> SELECT * FROM employee AS `직원`;

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.

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

• 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.

SQL

> SELECT 1 AS a;
 a
 1

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


> SELECT 3.14 AS `圆周率`;
 a
 1


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

Identifies a collation for a column or expression.

• collation_identifier: An identifier that specifies the name of the collation.

For a list of supported collations, see Supported collations. For details about collations see Collation.

SQL

> SELECT 'hello' COLLATE UNICODE_CI;
  hello

Identifies a credential to access storage at an external location or cloud services with provider SDKs.

• credential_identifier: An unqualified identifier that uniquely identifies the credential.

`my_storage_cred`

my_storage_cred

Identifies an external storage location.

• location_identifier: An unqualified identifier that uniquely identifies the location.

SQL

`s3-json-data`

s3_json_data

Identifies a share to access data shared by a provider.

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

SQL

`public info`

`public-info`

public_info

Identifies a Delta Sharing provider.

• provider_identifier

  An unqualified identifier that uniquely identifies the provider.

SQL

`Good Corp`

`Good-corp`

Good_Corp

Identifies a recipient for a share.

• recipient_identifier: An unqualified identifier that uniquely identifies the recipient.

SQL

`Good Corp`

`Good-corp`

Good_Corp

Identifies a clean room for a set of collaborators.

• clean_room_identifier: An unqualified identifier that uniquely specifies the clean room in the metastores of the collaborators.

Identifies a Unity Catalog volume. The volume can be qualified with a schema name or unqualified using a simple identifier.

[ schema_name .] volume_identifier

• 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.

SQL

`myVolume`

`my-volume`

myschema.myVolume

my_unity_catalog.my_schema.my_volume

• Identifiers
• IDENTIFIER clause
• File metadata column

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4