ALTER SHARE
Applies to: Databricks SQL Databricks Runtime 10.4 LTS 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.
Permissions required:
- To update the share owner, you must be one of the following: a metastore admin, the owner of the share object, or a user with both the
USE SHARE
andSET SHARE PERMISSION
privileges. - To update the share name, you must be a metastore admin (or user with the
CREATE_SHARE
privilege) and share owner. - To add tables or views, you must be the owner of the share object, have
USE SCHEMA
on the schema that contains the table or view, and theSELECT
privilege on the table or view. You must maintain theSELECT
privilege for as long as you want to share the table or view. - To update any other share properties, you must be the owner.
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 |
alter_add_model |
REMOVE MODEL model_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 ]
alter_add_model
{ { ALTER | ADD } VIEW model_name [ COMMENT comment ] [ AS model_share_name ]
Note
WITH HISTORY | WITHOUT HISTORY
is supported in Databricks Runtime 12.2 LTS and above. Databricks Runtime versions 11.1 through 12.0 require that you specify WITH CHANGE DATA FEED [ START VERSION version ] ]
instead of WITH HISTORY
.
WITH CHANGE DATA FEED
is deprecated.
Note also that if, in addition to doing time travel queries and streaming reads, you want your customers to be able to query a table's change data feed (CDF) using the table_changes() function, you must enable CDF on the table before you share it WITH HISTORY
(or WITH CHANGE DATA FEED
).
Parameters
-
The name of the share to be altered.
alter_add_materialized_view
Applies to: Databricks SQL Databricks Runtime 13.3 LTS and above
Important
This feature is in Private Preview. To try it, reach out to your Azure Databricks contact.
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_nameIdentifies the materialized view to be added. If the materialized view cannot be found, Azure Databricks raises a TABLE_OR_VIEW_NOT_FOUND error.
ALTER MATERIALIZED VIEW
mat_view_nameIdentifies the materialized view to be altered. If the materialized view is not already part of the share, Azure Databricks raises an error.
COMMENT comment
An optional
STRING
literal attached to the materialized view share as a comment.-
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, Azure Databricks raises an error.
REMOVE MATERIALIZED VIEW
mat_view_nameApplies to: Databricks SQL Databricks Runtime 13.3 LTS 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_nameIdentifies the table to be added. The table must not reside in Unity Catalog. If the table cannot be found Azure Databricks raises a TABLE_OR_VIEW_NOT_FOUND error.
ALTER [ TABLE ]
table_nameIdentifies the table to be altered. If the table is not already part of the share Azure Databricks raises an 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_RECIPIENT().<property-key>)
Partitioning by reference to recipient properties Applies to: Databricks SQL Databricks Runtime 12.2 LTS 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.2 LTS 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.If, in addition to doing time travel queries and streaming reads, you want your customers to be able to query a table's change data feed (CDF) using the table_changes() function, you must enable CDF on the table before you share it
WITH HISTORY
.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.alter_add_schema
Applies to: Databricks SQL Databricks Runtime 13.3 LTS 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_nameIdentifies the schema to be added. If the schema cannot be found, Azure Databricks raises a SCHEMA_NOT_FOUND error.
ALTER SCHEMA
schema_nameIdentifies the schema to be altered. If the schema is not already part of the share, Azure Databricks raises an error.
COMMENT comment
An optional
STRING
literal attached to the schema share as a comment.
REMOVE SCHEMA
schema_nameRemove 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.3 LTS 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_nameIdentifies the view to be added. If the view cannot be found Azure Databricks raises a TABLE_OR_VIEW_NOT_FOUND error.
ALTER VIEW
view_nameIdentifies the view to be altered. If the view is not already part of the share Azure Databricks raises an error.
COMMENT comment
An optional
STRING
literal attached to the view share as a comment.-
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, Azure Databricks raises an error.
REMOVE VIEW
view_nameRemove the view identified by
view_name
from the share. To run this statement, you must be the owner of the share.alter_add_model
Applies to: Databricks SQL Databricks Runtime 14.0 and above
Adds a model to the share or modifies an existing shared model. To run this statement, you must be the owner of the share and have
EXECUTE
privilege on the model.ADD MODEL
model_nameIdentifies the model to be added. If the model cannot be found Azure Databricks raises a RESOURCE_DOES_NOT_EXIST error.
ALTER MODEL
model_nameIdentifies the model to be altered. If the model is not already part of the share Azure Databricks raises an error.
COMMENT comment
An optional
STRING
literal attached to the model share as a comment.-
Optionally exposes the model under a different name. The name can be qualified with a schema name. If no
model_share_name
is specified, the model is known under its own name.If the shared name already exists, Azure Databricks raises an error.
REMOVE MODEL
model_nameRemove the model identified by
model_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.3 LTS 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 Databricks Runtime 11.3 LTS 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`