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β
- Atomic swap
tbl1
withtbl2
without dropping any tables(Note: if you delete it, you actually delete tbl1 and rename tbl2 to tbl1.)
ALTER TABLE tbl1 REPLACE WITH TABLE tbl2;
or
ALTER TABLE tbl1 REPLACE WITH TABLE tbl2 PROPERTIES('swap' = 'true');
- Atomic swap
tbl1
withtbl2
and deleting thetbl2
table(Keeptbl1
and the data of the originaltbl2
)
ALTER TABLE tbl1 REPLACE WITH TABLE tbl2 PROPERTIES('swap' = 'false');
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.