PolarDB:Query or restore historical data from column-store snapshots

Prerequisites

The instance must be 5.4.20 or later, and the engine must be MySQL 5.7 or MySQL 8.0.

Precautions

• A column-store snapshot query returns results based on the latest table structure, regardless of whether the snapshot point is created before the table structure change. For example, after you add a column to a table and then query the column-store snapshot data before the column is added, the table structure after the column is added is obtained in the result.

• You can use the INSERT SELECT statement to restore data in a transaction only when the automatic commit of the database is enabled (autocommit=true).

• When you use the INSERT SELECT statements, we recommend that you do not perform complex queries in the SELECT clause because complex queries may cause execution inefficiency.

Query historical data from a snapshot point

To query historical data from a snapshot, append the AS OF TSO <TSO> clause after the table name in the SELECT statement. To query historical data from a column-store snapshot, you must also include the FORCE INDEX clause and specify the name of the column-store snapshot. Syntax:

SELECT * FROM <table_name> AS OF TSO <TSO> FORCE INDEX(<snapshot_name>);

Sample statement:

-- Place the FORCE INDEX clause after the AS OF TSO clause.
SELECT * FROM tb1 AS OF TSO 7206138458723582016 FORCE INDEX(cci);

You can also restore historical data from a column-store snapshot. Syntax:

INSERT INTO <target_table> SELECT * FROM <source_table> AS OF TSO <TSO> FORCE INDEX(<snapshot_name>);

Sample statement:

INSERT INTO tmp SELECT * FROM tb1 AS OF TSO 7206138458723582016 FORCE INDEX(cci);

Examples

1. Execute the following statements to create the database test_columnar_snapshot and create the table tb1 in the database.

   CREATE DATABASE test_columnar_snapshot MODE=AUTO;

   USE test_columnar_snapshot;
   CREATE TABLE tb1 (
     id INT PRIMARY KEY,
     a INT,
     gmt_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
     gmt_modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
   ) PARTITION BY KEY(id);

2. Create a column-store snapshot named cci for the tb1 table.

   CREATE CLUSTERED COLUMNAR INDEX cci ON tb1(a) 
   PARTITION BY KEY(id) 
   columnar_options='{
     "type":"snapshot",
     "snapshot_retention_days":"7",
     "auto_gen_columnar_snapshot_interval":"30"
   }';

3. Insert data into the table and generate the snapshot points.

   INSERT INTO tb1 (id, a) VALUES (0, 0);
   
   CALL polardbx.columnar_flush (test_columnar_snapshot, 'tb1', 'cci'); -- Generate a CCI-level snapshot point and record it as TSO1
   
   INSERT INTO tb1 (id, a) VALUES (1, 1);
   
   CALL polardbx.columnar_flush(); -- Generate an instance-level snapshot point and record it as TSO2.
   
   INSERT INTO tb1 (id, a) VALUES (2, 2);

4. Query column-store snapshots based on snapshot points.

   SELECT * FROM tb1 AS OF TSO {TSO1} FORCE INDEX(cci) ORDER BY id; -- Expected result: (0, 0).
   
   SELECT * FROM tb1 AS OF TSO {TSO2} FORCE INDEX(cci) ORDER BY id; -- Expected result: (0, 0), (1, 1).
   
   SELECT * FROM tb1 FORCE INDEX(cci) ORDER BY id; -- Expected result: (0, 0), (1, 1), (2, 2).

5. Use the INSERT SELECT statement to restore data.

   CREATE TABLE tb1_tmp (
     id INT PRIMARY KEY,
     a INT,
     gmt_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
     gmt_modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
   ) PARTITION BY KEY(id);
   
   -- Use the column-store snapshot to restore data.
   INSERT INTO tb1_tmp SELECT * FROM tb1 AS OF TSO {TSO1} FORCE INDEX(cci);
   
   SELECT * FROM tb1_tmp ORDER BY id; -- Expected result: (0, 0).