ALTER SHARE
Applies to: Databricks SQL
Databricks Runtime 10.3 and above
Unity Catalog only
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 HISTORY | WITHOUT HISTORY ] }
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
-
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_nameIdentifies 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.
-
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 presentADD 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 Runtime 12.2 and above.
-
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
orWITHOUT 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_nameRemove 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_nameApplies 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
principalTransfers 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;
— 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;
-- 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`