UNDROP

Applies to: Databricks SQL Databricks Runtime 12.2 LTS and above

The UNDROP command addresses the concern of managed or external relations (tables or materialized views) located in Unity Catalog being accidentally dropped or deleted. By default, this command undrops (recovers) the most recently dropped relation owned by the user of the given relation name. The parent schema and catalog must exist. This feature supports recovering dropped relations within a 7 day retention period.

If there are multiple dropped relations of the same name, you can use SHOW TABLES DROPPED to identify the table ID and use UNDROP TABLE WITH ID to recover a specific relation.

If there is a relation with the same name as the relation you wish to recover, use ALTER TABLE RENAME TO command to change the name of the existing relation.

Table metadata – such as table privileges, column spec, and properties – will be recovered. Primary and foreign key constraints are not recovered by the UNDROP command. Recreate them manually using ALTER TABLE ADD CONSTRAINT after the table has been recovered.

Syntax

UNDROP { MATERIALIZED VIEW | TABLE } { relation_name | WITH ID relation_id }

Parameter

• MATERIALIZED VIEW

  Applies to: Databricks SQL Databricks Runtime 16.2 and above

  Specifies that the relation relation_name to be restored is a materialized view.

• TABLE

  Specifies that the relation relation_name to be restored is a table.

• relation_name

  The name of the table or materialized view to be restored. The name must not include a temporal specification or options specification. If the relation or the type is not as specified cannot be found Azure Databricks raises WRONG_COMMAND_FOR_OBJECT_TYPE or TABLE_OR_VIEW_NOT_FOUND.

• relation_id

  A STRING literal in the form of a UUID of the relation as displayed by SHOW TABLES DROPPED.

Permissions

UNDROP requires one of the following base permissions:

• A user is the owner of the relation, has CREATE TABLE and USE SCHEMA on the schema, and USE CATALOG on the catalog.
• A user is the owner of the schema and has USE CATALOG on the catalog.
• A user is the owner of the catalog.
• A user is the owner of the metastore.
• A user has MANAGE on the table, CREATE TABLE and USE SCHEMA on the schema, and USE CATALOG on the catalog.

If a user is recovering a different type of table, additional permissions apply. For example, to undrop an external table, you must also have CREATE EXTERNAL TABLE on the external location or storage credential, which must exist.

After running this command, the ownership defaults to the previous relation owner. If required, the ownership can be changed using the ALTER TABLE or ALTER MATERIALIZED VIEW command.

Examples

-- UNDROP using the table name
> CREATE TABLE my_catalog.my_schema.my_table (id INT, name STRING);
> DROP TABLE my_catalog.my_schema.my_table;
> UNDROP TABLE my_catalog.my_schema.my_table;
  OK

-- UNDROP WITH ID
– Use SHOW TABLES DROPPED to find dropped tables
> SHOW TABLES DROPPED IN my_schema;
  catalogname schemaname tablename  tableid                              tabletype deletedat                     createdat                     updatedat                     createdby     owner         comment
  ----------- ---------- ---------- ------------------------------------ --------- ----------------------------- ----------------------------- ----------------------------- ------------- ------------- -------
  my_catalog  my_schema  my_table   6ca7be55-8f58-47a7-85ee-7a59082fd17a managed   2023-05-03 AD at 18:17:56 UTC 2023-05-03 AD at 18:17:00 UTC 2023-05-03 AD at 18:17:00 UTC alf@melmak.et alf@melmak.et
  my_catalog  my_schema  my_table   b819f397-c51f-4e60-8acc-05d4d4a7e084 managed   2023-05-04 AD at 10:20:00 UTC 2023-05-04 AD at 08:20:00 UTC 2023-05-04 AD at 08:20:00 UTC alf@melmak.et alf@melmak.et

–- Undrop a specific dropped table.
–- Here, we undrop my_table with table id '6ca7be55-8f58-47a7-85ee-7a59082fd17a'.
-- Note that the table id will be a string surrounded by single quotation marks.
> UNDROP TABLE WITH ID '6ca7be55-8f58-47a7-85ee-7a59082fd17a';
  OK

– Continuing from the example above, Now we want to undrop table with ID 'b819f397-c51f-4e60-8acc-05d4d4a7e084'.
- First, we rename the existing table
> ALTER TABLE my_table RENAME TO my_other_table
  OK
- Then we can undrop table with the name my_table
> UNDROP TABLE WITH ID 'b819f397-c51f-4e60-8acc-05d4d4a7e084'
  OK

—- Create some MVs within a DLT pipeline
> CREATE MATERIALIZED VIEW mv1 AS SELECT * FROM RANGE(5);
> CREATE MATERIALIZED VIEW mv2 AS SELECT * FROM mv1;

—- Drop the MVs
> DROP MATERIALIZED VIEW mv1;
> DROP MATERIALIZED VIEW mv2;

-- UNDROP using the table name
> UNDROP MATERIALIZED VIEW mv1;
  OK

-- UNDROP WITH ID
–- Use SHOW TABLES DROPPED to find the dropped mv2’s tableId
—- Assume it is 6ca7be55-8f58-47a7-85ee-7a59082fd17a
-- When undropping, note that the table id will be a string surrounded by single quotation marks.
> UNDROP MATERIALIZED VIEW WITH ID '6ca7be55-8f58-47a7-85ee-7a59082fd17a';
  OK

Related articles

• ALTER TABLE
• ALTER MATERIALIZED VIEW
• SHOW TABLES DROPPED