RetroSearch Browse

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

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

Table properties and table options

Applies to: Databricks SQL Databricks Runtime

Defines user defined tags for tables and views.

table properties

A table property is a key-value pair which you can initialize when you perform a CREATE TABLE or a CREATE VIEW. You can UNSET existing or SET new or existing table properties using ALTER TABLE or ALTER VIEW.

You can use table properties to tag tables with information not tracked by SQL.
table options

The purpose of table options is to pass storage properties to the underlying storage, such as SERDE properties to Hive.

A table option is a key-value pair which you can initialize when you perform a CREATE TABLE. You cannot SET or UNSET a table option.

TBLPROPERTIESâ

Sets one or more table properties in a new table or view.

You can use table properties to tag tables with information not tracked by SQL.

Syntaxâ

TBLPROPERTIES ( { property_key [ = ] property_val } [, ...] )

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

Parametersâ

property_key

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

Property keys must be unique and are case sensitive.
property_val

The value for the property. The value must be a BOOLEAN, STRING, INTEGER, or DECIMAL literal.

Examplesâ

SQL


> CREATE TABLE T(c1 INT) TBLPROPERTIES('this.is.my.key' = 12, this.is.my.key2 = true);
> SHOW TBLPROPERTIES T;
key              value

...
this.is.my.key      12
this.is.my.key2   true
...

SET TBLPROPERTIESâ

Sets one or more table properties in an existing table or view.

Syntaxâ

SET TBLPROPERTIES ( { property_key [ = ] property_val } [, ...] )

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

Parametersâ

property_key

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

Property keys must be unique and are case sensitive.
property_val

The new value for the property. The value must be a BOOLEAN, STRING, INTEGER, or DECIMAL literal.

Examplesâ

SQL


>  ALTER TABLE T SET TBLPROPERTIES(this.is.my.key = 14, 'this.is.my.key2' = false);
> SHOW TBLPROPERTIES T;
key              value

...
this.is.my.key      14
this.is.my.key2  false
...

UNSET TBLPROPERTIESâ

Removes one or more table properties from a table or view.

Syntaxâ

UNSET TBLPROPERTIES [ IF EXISTS ] ( property_key [, ...] )

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

Parametersâ

IF EXISTS

An optional clause directing Databricks SQL not to raise an error if any of the property keys do not exist.
property_key

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

Property keys are case sensitive. If property_key doesn't exist and error is raised unless IF EXISTS has been specified.

Examplesâ

SQL


>  ALTER TABLE T UNSET TBLPROPERTIES(this.is.my.key, 'this.is.my.key2');
> SHOW TBLPROPERTIES T;
key                              value

... keys other that key and key2 ...

OPTIONSâ

Sets one or more table options in a new table.

The purpose of table options is to pass storage properties to the underlying storage, such as SERDE properties to Hive.

Specifying table options for Delta Lake tables will also echo these options as table properties.

Syntaxâ

OPTIONS ( { property_key [ = ] property_val } [, ...] )

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

Parametersâ

property_key

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

Property keys must be unique and are case-sensitive.
property_val

The value for the property. The value must be a BOOLEAN, STRING, INTEGER, or DECIMAL literal.

In Databricks SQL and Databricks Runtime 13.3 LTS and above property_val can be a constant expression.

Examplesâ

SQL



> CREATE TABLE T(c1 INT) OPTIONS(this.is.my.key = 'blue' || 'green');
> SHOW TBLPROPERTIES T;
key              value

...
option.this.is.my.key bluegreen
...

Reserved table property keysâ

Databricks reserves some property keys for its own use and raises an error if you attempt to use them:

external

Use CREATE EXTERNAL TABLE to create an external table.
location

Use the LOCATION clauses of ALTER TABLE and CREATE TABLE to set a table location.
owner

Use the [SET] OWNER TO clause of ALTER TABLE and ALTER VIEW to transfer ownership of a table or view. SET is allowed as an optional keyword in Databricks SQL.
provider

Use the USING clause of CREATE TABLE to set the data source of a table

You should not use property keys starting with the option identifier. This prefix identifier will be filtered out in SHOW TBLPROPERTIES. The option prefix is also used to display table options.

Common TBLPROPERTIES and OPTIONS keysâ

The following settings are commonly used with Delta Lake:

delta.appendOnly: Set to true to disable UPDATE and DELETE operations.
delta.dataSkippingNumIndexedCols: Set to the number of leading column for which to collect and consider statistics.
delta.deletedFileRetentionDuration: Set to an interval such as 'interval 7 days' to control when VACUUM is allowed to delete files.
delta.logRetentionDuration: Set to an interval such as 'interval 60 days' to control how long history is kept for time travel queries.