Table properties and table options

一個pplies to:check marked yesDatabricks SQLcheck marked yesDatabricks Runtime

Defines user defined tags for tables and views.

  • table properties

    一個table property is a key-value pair which you can initialize when you perform aCREATE TABLEor aCREATE VIEW. You canUNSETexisting orSETnew or existing table properties using一個LTER TABLEor一個LTER 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.

    一個table option is a key-value pair which you can initialize when you perform aCREATE TABLE. You cannotSETorUNSETa 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 moreidentifiersseparated 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>CREATETABLET(c1INT)TBLPROPERTIES('this.is.my.key'=12,this.is.my.key2=true);>SHOWTBLPROPERTIEST;option.serialization.format1this.is.my.key12this.is.my.key2truetransient_lastDdlTime1649783569

SET TBLPROPERTIES

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

Syntax

SETTBLPROPERTIES(property_key[=]property_val[,...])property_key{identifier[....]|string_literal}

Parameters

  • property_key

    The property key. The key can consist of one or moreidentifiersseparated 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.>一個LTERTABLETSETTBLPROPERTIES(this.is.my.key=14,'this.is.my.key2'=false);>SHOWTBLPROPERTIEST;option.serialization.format1this.is.my.key14this.is.my.key2falsetransient_lastDdlTime1649783980

UNSET TBLPROPERTIES

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

Syntax

UNSETTBLPROPERTIES[IFEXISTS](property_key[,...])property_key{identifier[....]|string_literal}

Parameters

  • IF EXISTS

    一個n 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 moreidentifiersseparated by a dot, or a string literal.

    Property keys are case sensitive. Ifproperty_keydoesn’t exist and error is raised unlessIFEXISTShas been specified.

Examples

-- Remove a table's table properties.>一個LTERTABLETUNSETTBLPROPERTIES(this.is.my.key,'this.is.my.key2');>SHOWTBLPROPERTIEST;option.serialization.format1transient_lastDdlTime1649784415

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 moreidentifiersseparated 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 option-- The options appears with an `option.` prefix.>CREATETABLET(c1INT)OPTIONS(this.is.my.key='green');>SHOWTBLPROPERTIEST;option.this.is.my.keygreenoption.serialization.format2

Reserved table property keys

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

You should not use property keys starting with theoptionidentifier. This prefix identifier will be filtered out inSHOW TBLPROPERTIES. Theoptionprefix is also used to displaytable options.

Common TBLPROPERTIES and OPTIONS keys

The following settings are commonly used with Delta Lake:

  • delta.appendOnly: Set totrueto disableUPDATEandDELETEoperations.

  • delta.dataSkippingNumIndexedCols: Set to the number of leading column for which to collect and consider statistics.

  • delta.deletedFileRetentionDuration: Set to an interval such as'interval7days'to control whenVACUUMis allowed to delete files.

  • delta.logRetentionDuration: Set to an interval such as'interval60days'to control how long history is kept for time travel queries.