Table properties and table options

Applies to: check marked yes Databricks SQL check marked yes 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 

-- Create table with user defined table properties
> 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      14
this.is.my.key2  false
...

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 

-- Alter the a table's table properties.
>  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 

-- Remove a table's table properties.
>  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 

-- Create table with user defined table option
-- The options appears with an `option.` prefix.
> 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.