ALTER SHARE

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 10.3 and above

Adds or removes tables to or from the share. Renames a share. Transfers the ownership of a share to a new principal.

Syntax

ALTER SHARE share_name
  { alter_table |
    REMOVE TABLE clause |
    RENAME TO to_share_name |
    [ SET ] OWNER TO principal }

alter_table
  { ADD [ TABLE ] table_name [ COMMENT comment ]
        [ PARTITION clause ] [ AS table_share_name ]
        [ WITH CHANGE DATA FEED [ START VERSION version ] }

Parameters

  • share_name

    The name of the share to be altered.

  • alter_table

    Adds a table or partitions of a table to the share. To run this statement, you must be the owner of the share and have SELECT privilege on the table.

    • ADD [ TABLE ] table_name

      Identifies the table to be added. The table must not reside in the hive_metastore. If the table cannot be found Databricks raises a TABLE_OR_VIEW_NOT_FOUND error.

    • COMMENT comment

      An optional string literal attached to the table share as a comment.

    • PARTITION clause

      One or to more partitions of the table to be added. The partition keys must match the partitioning of the table and be associated with values. If no PARTITION clause is present ADD TABLE adds the entire table.

    • AS table_share_name

      Optionally exposes the table under a different name. The name can be qualified with a database (schema) name. If no table_share_name is specified the table will be known under its own name.

    • WITH CHANGE DATA FEED

      Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 11.1 and above

      Optionally specifies that the share gets access to the change data feed of the Delta Lake table. A shared table WITH CHANGE DATA FEED can be referenced by the table_changes() function to view the history of changes to the shared table. The shared table can also be referenced using VERSION AS OF temporal specification.

      • START VERSION version

        Applies to: check marked yes Databricks SQL SQL warehouse version 2022.35 or higher check marked yes Databricks Runtime 11.2 and above

        Optionally, the starting version of the table, from which the recipient can query the table’s change data feed. If no version is specified, the feed starts with the current version.

        version must be an INTEGER literal corresponding to a version returned by the DESCRIBE HISTORY table_name statement.

  • REMOVE [ TABLE ] table_name

    Remove the table identified by table_name from the share. To run this statement, you must be the owner of the share.

  • RENAME TO to_share_name

    Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 11.1 and above

    Renames the share. The name must be unique among all shares in the metastore. To run this statement, you must be the owner of the share and have CREATE SHARE privilege on the metastore.

  • [ SET ] OWNER TO principal

    Transfers ownership of the share to principal. To run this statement, you must be the owner of the share.

    Applies to: check marked yes Databricks SQL SQL warehouse version 2022.35 or higher check marked yes Databricks Runtime 11.2 and above

    SET is allowed as an optional keyword.

Examples

-- Creates a share named `some_share`.
> CREATE SHARE some_share;

-- Add a table to the share.
> ALTER SHARE some_share
     ADD TABLE my_schema.my_tab
         COMMENT 'some comment'
         PARTITION(c1_int = 5, c2_date LIKE '2021%')
         AS shared_schema.shared_tab;

-- Add a table with data change feed to the share.
> ALTER SHARE some_share
     ADD TABLE my_schema.my_tab1 WITH DATA CHANGE FEED START VERSION 3;

-- Remove the table again
> ALTER SHARE some_share
    REMOVE TABLE shared_schema.shared_tab;

-- Rename a share
> ALTER SHARE some_share
    RENAME TO new_share;

-- Change ownership of the share
> ALTER SHARE some_share
    OWNER TO `alf@melmak.et`