UNDROP TABLE

Use the UNDROP TABLE statement to restore deleted tables, dynamic tables, and materialized views. Therefore, this article uses UNDROP TABLE to restore deleted materialized views. Whether the deleted object can be restored depends on the data retention period. Data Retention Period: The ability to restore historical objects depends on the data retention period. The current preview version has a default data retention period of 7 days, which will be adjusted to 1 day in the future. You can adjust the retention period by executing the ALTER command. Please note that modifying the retention period may increase storage costs. Supported objects include tables (TABLE), dynamic tables (DYNAMIC TABLE), and materialized views.

Syntax

UNDROP TABLE tablename;

Parameter Description

• tablename: Specifies the name of the table to be deleted. Supports dynamic tables, internal tables, materialized views.

Example

Restore Materialized View

DROP      TABLE mv_base_a;

DROP MATERIALIZED VIEW mv1;

-- Create a base table
CREATE    TABLE mv_base_a (i int, j int);

INSERT    INTO mv_base_a
VALUES    (1, 10),
          (2, 20),
          (3, 30),
          (4, 40);

-- Use dynamic table for processing
CREATE MATERIALIZED VIEW mv1 (i, j) AS
SELECT    *
FROM      mv_base_a;

-- Refresh dynamic table
REFRESH   MATERIALIZED VIEW mv1;

-- Query data
SELECT    *
FROM      mv1;
+---+----+
| i | j  |
+---+----+
| 1 | 10 |
| 2 | 20 |
| 3 | 30 |
| 4 | 40 |
+---+----+
DROP materialized VIEW mv1;

SHOW      TABLES history
WHERE     table_name = 'mv1';
+-------------+------------+-------------------------+----------+------+-------+---------+----------------+-------------------------+
| schema_name | table_name |       create_time       | creator  | rows | bytes | comment | retention_time |       delete_time       |
+-------------+------------+-------------------------+----------+------+-------+---------+----------------+-------------------------+
| public      | mv1        | 2024-12-18 16:57:35.916 | UAT_TEST | 4    | 2467  |         | 1              | 2024-12-18 16:57:58.427 |
+-------------+------------+-------------------------+----------+------+-------+---------+----------------+-------------------------+
UNDROP TABLE mv1;

SELECT    *
FROM      mv1;
+---+----+
| i | j  |
+---+----+
| 1 | 10 |
| 2 | 20 |
| 3 | 30 |
| 4 | 40 |
+---+----+

Precautions

• Please ensure that the recovery operation is performed within the data retention period, otherwise the deleted table cannot be restored.
• Before restoring the table, make sure that there are no tables with the same name. If there are tables with the same name, please rename or delete the tables with the same name.