ALTER SHARE

Applies to: Databricks SQL Databricks Runtime 10.3 and above Unity Catalog only

Adds, alters or removes schemas, tables, materialized views, or views to or from the share. Renames a share. Transfers the ownership of a share to a new principal.

Syntax

ALTER SHARE share_name
  { alter_add_materialized_view |
    REMOVE MATERIALIZED VIEW mat_view_name |
    alter_add_table |
    REMOVE TABLE table_name |
    alter_add_schema |
    REMOVE SCHEMA schema_name |
    alter_add_view |
    REMOVE VIEW view_name |
    RENAME TO to_share_name |
    [ SET ] OWNER TO principal }

alter_add_materialized_view
  { { ALTER | ADD } MATERIALIZED VIEW mat_view_name [ COMMENT comment ] [ AS mat_view_share_name ]

alter_add_table
  { { ALTER | ADD } [ TABLE ] table_name [ COMMENT comment ]
        [ PARTITION clause ] [ AS table_share_name ]
        [ WITH HISTORY | WITHOUT HISTORY ] }

alter_add_schema
  { { ALTER | ADD } SCHEMA schema_name [ COMMENT comment ]

alter_add_view
  { { ALTER | ADD } VIEW view_name [ COMMENT comment ] [ AS view_share_name ]

For Databricks SQL and Databricks Runtime between 11.1 and 12.0 you must specify WITH CHANGE DATA FEED [ START VERSION version ] ] instead of WITH HISTORY. This clause will be deprecated.

Parameters

share_name

The name of the share to be altered.
alter_add_materialized_view

Applies to: Databricks Runtime 13.3 and above

Adds a materialized view to the share or modifies an existing shared materialized view. To run this statement, you must be the owner of the share and have SELECT privileges on the materialized view.
- ADD MATERIALIZED VIEW mat_view_name
  
  Identifies the materialized view to be added. If the materialized view cannot be found, Databricks raises a TABLE_OR_VIEW_NOT_FOUND error.
- ALTER MATERIALIZED VIEW mat_view_name
  
  Identifies the materialized view to be altered. If the materialized view is not already part of the share, Databricks raises a an error.
- COMMENT comment
  
  An optional STRING literal attached to the materialized view share as a comment.
- AS mat_view_share_name
  
  Optionally exposes the materialized view under a different name. The name can be qualified with a schema name. If no mat_view_share_name is specified, the materialized view is known under its own name.
  
  If the shared name already exists, Databricks raises an error.
REMOVE MATERIALIZED VIEW mat_view_name

Applies to: Databricks Runtime 13.3 and above

Remove the materialized view identified by mat_view_name from the share. To run this statement, you must be the owner of the share.
alter_add_table

Adds a table or partitions of a table to the share or modifies an existing shared table. 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 Unity Catalog. If the table cannot be found Databricks raises a TABLE_OR_VIEW_NOT_FOUND error.
- ALTER [ TABLE ] table_name
  
  Identifies the table to be altered. If the table is not already part of the share Databricks raises a an 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.
  
  To partition by reference to a recipient properties, use the syntax:
```
PARTITION (column_name = CURRENT_RECPIENT().<property-key>)
```
  Partitioning by reference to recipient properties Applies to: Databricks SQL Databricks Runtime 12.2 and above.
- 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 HISTORY or WITHOUT HISTORY
  
  Applies to: Databricks SQL Databricks Runtime 12.1 and above
  
  When WITH HISTORY is specified, share the table with full history, allowing recipients to perform time travel queries. The shared table can then be referenced using VERSION AS OF and TIMESTAMP AS OF.
  
  A shared table can be referenced by the table_changes() function to view the history of changes to it, if “delta.enableChangeDataFeed” is set to “true” on the source table and history is shared.
  
  The default behavior is WITHOUT HISTORY.
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.
alter_add_schema

Applies to: Databricks SQL Databricks Runtime 13.2 and above

Adds a schema to the share or modifies an existing shared schema. To run this statement, you must be the owner of the share and the schema.
- ADD SCHEMA schema_name
  
  Identifies the schema to be added. If the schema cannot be found, Databricks raises a SCHEMA_NOT_FOUND error.
- ALTER SCHEMA schema_name
  
  Identifies the schema to be altered. If the schema is not already part of the share, Databricks raises a an error.
- COMMENT comment
  
  An optional STRING literal attached to the schema share as a comment.
REMOVE SCHEMA schema_name

Remove the schema identified by schema_name from the share. To run this statement, you must be the owner of the share.
alter_add_view

Applies to: Databricks SQL Databricks Runtime 13.2 and above

Adds a view to the share or modifies an existing shared view. To run this statement, you must be the owner of the share and have SELECT privilege on the view.
- ADD VIEW view_name
  
  Identifies the view to be added. If the view cannot be found Databricks raises a TABLE_OR_VIEW_NOT_FOUND error.
- ALTER VIEW view_name
  
  Identifies the view to be altered. If the view is not already part of the share Databricks raises a an error.
- COMMENT comment
  
  An optional STRING literal attached to the view share as a comment.
- AS view_share_name
  
  Optionally exposes the view under a different name. The name can be qualified with a schema name. If no view_share_name is specified, the view is known under its own name.
  
  If the shared name already exists, Databricks raises an error.
REMOVE VIEW view_name

Remove the view identified by view_name from the share. To run this statement, you must be the owner of the share.
RENAME TO to_share_name

Applies to: Databricks SQL 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: Databricks SQL SQL warehouse version 2022.35 or higher 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 schema to the share.
> ALTER SHARE some_share
     ADD SCHEMA some_schema
         COMMENT 'some comment';

-- Add a view to the share.
> ALTER SHARE some_share
     ADD VIEW my_schema.my_view
         COMMENT 'some comment'
         AS shared_schema.shared_view;

-- Add a materialized view to the share.
> ALTER SHARE some_share
     ADD MATERIALIZED VIEW my_schema.my_mat_view
         COMMENT 'some comment'
         AS shared_schema.shared_mat_view;

— Share a table with history
> ALTER SHARE share ADD TABLE table1 WITH HISTORY;
> ALTER SHARE share ADD TABLE table2 WITHOUT HISTORY;
> SHOW ALL IN SHARE share;
  Name    type   ... history_sharing  ...
  ------  ------ ... ----------------
  Table1  TABLE  ... ENABLED          ...
  Table2  TABLE  ... DISABLED         ...

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

-- Remove the schema again
> ALTER SHARE some_share
    REMOVE SCHEMA some_schema;

-- Remove a view again
> ALTER SHARE some_share
    REMOVE VIEW shared_schema.shared_view;

-- 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`

ALTER SHARE

Syntax

Parameters

Examples

Related articles