ALTER-TABLE-REPLACE
ALTER-TABLE-REPLACE
Name
ALTER TABLE REPLACE
Description
Atomic substitution of two tables. This operation applies only to OLAP tables.
ALTER TABLE [db.]tbl1 REPLACE WITH TABLE tbl2
[PROPERTIES('swap' = 'true')];
Replace table tbl1 with table tbl2.
If the swap
parameter is true
, the data in the table named tbl1
will be the data in the original table named tbl2
after the replacement. The data in the table named tbl2
is the data in the original tbl1
table. That is, two tables of data have been swapped.
If the swap
parameter is false
, the data in the tbl1
table will be the data in the tbl2
table after the replacement. The table named tbl2
is deleted.
Theory
The replace table function actually turns the following set of operations into an atomic operation.
If you want to replace table A with table B and swap
is true
, do the following:
- Rename table B as table A.
- Rename table A as table B.
If swap
is false
, do as follows:
- Delete table A.
- Rename table B as table A.
Notice
- The default
swap
parameter istrue
. That is, a table replacement operation is equivalent to an exchange of data between two tables. - If the
swap
parameter is set to false, the replaced table (table A) will be deleted and cannot be restored. - The replacement operation can only occur between two OLAP tables and does not check whether the table structure of the two tables is consistent.
- The original permission Settings are not changed. Because the permission check is based on the table name.
Example
- Swap
tbl1
withtbl2
without deleting thetbl1
table
ALTER TABLE tbl1 REPLACE WITH TABLE tbl2
[PROPERTIES('swap' = 'true')];
Keywords
ALTER, TABLE, REPLACE, ALTER TABLE
Best Practice
Atomic overlay write operations
In some cases, the user wants to be able to rewrite the data of a certain table, but if the data is deleted first and then imported, the data cannot be viewed for a period of time in between. At this time, the user can first use the
CREATE TABLE LIKE
statement to create a new table with the same structure, import the new data into the new table, and use the replacement operation to atomically replace the old table to achieve the goal. Atomic overwrite write operations at the partition level, see temp partition documentation.