ALTER-TABLE-REPLACE

Name

ALTER TABLE REPLACE

Description

Atomic substitution of two tables. This operation applies only to OLAP tables.

  1. ALTER TABLE [db.]tbl1 REPLACE WITH TABLE tbl2
  2. [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:

  1. Rename table B as table A.
  2. Rename table A as table B.

If swap is false, do as follows:

  1. Delete table A.
  2. Rename table B as table A.

Notice

  1. The default swap parameter is true. That is, a table replacement operation is equivalent to an exchange of data between two tables.
  2. If the swap parameter is set to false, the replaced table (table A) will be deleted and cannot be restored.
  3. The replacement operation can only occur between two OLAP tables and does not check whether the table structure of the two tables is consistent.
  4. The original permission Settings are not changed. Because the permission check is based on the table name.

Example

  1. Swap tbl1 with tbl2 without deleting the tbl1 table
  1. ALTER TABLE tbl1 REPLACE WITH TABLE tbl2
  2.     [PROPERTIES('swap' = 'true')];

Keywords

  1. ALTER, TABLE, REPLACE, ALTER TABLE

Best Practice

  1. Atomic overlay write operations

    In some cases, the user wants to be able to rewrite the data of a table, but if the deletion and then import method is used, the data cannot be viewed for a period of time. In this case, you can use the CREATE TABLE LIKE statement to CREATE a new TABLE with the same structure. After importing the new data into the new TABLE, you can replace the old TABLE atomic to achieve the purpose.