Replace Table
Replace Table
In version 0.14, Doris supports atomic replacement of two tables. This operation only applies to OLAP tables.
For partition level replacement operations, please refer to Temporary Partition Document
Syntax
ALTER TABLE [db.]tbl1 REPLACE WITH tbl2
[PROPERTIES('swap' = 'true')];
Replace table tbl1
with table tbl2
.
If the swap
parameter is true
, after replacement, the data in the table named tbl1
is the data in the original tbl2
table. The data in the table named tbl2
is the data in the original table tbl1
. That is, the data of the two tables are interchanged.
If the swap
parameter is false
, after replacement, the data in the table named tbl1
is the data in the original tbl2
table. The table named tbl2
is dropped.
Principle
The replacement table function actually turns the following set of operations into an atomic operation.
Suppose you want to replace table A with table B, and swap
is true
, the operation is as follows:
- Rename table B to table A.
- Rename table A to table B.
If swap
is false
, the operation is as follows:
- Drop table A.
- Rename table B to table A.
Notice
- The
swap
parameter defaults totrue
. That is, the replacement table operation is equivalent to the exchange of two table data. - If the
swap
parameter is set tofalse
, the replaced table (table A) will be dropped and cannot be recovered. - The replacement operation can only occur between two OLAP tables, and the table structure of the two tables is not checked for consistency.
- The replacement operation will not change the original permission settings. Because the permission check is based on the table name.
Best Practices
Atomic Overwrite Operation
In some cases, the user wants to be able to rewrite the data of a certain table, but if it is dropped and then imported, there will be a period of time in which the data cannot be viewed. 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 replace the old table atomically through the replacement operation to achieve the goal. For partition level atomic overwrite operation, please refer to Temporary partition document