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

Prerequisites

Before you begin, ensure that you have:

• A PolarDB for Xscale instance running version 5.4.20 or later

• An instance using the MySQL 5.7 or MySQL 8.0 engine

Usage notes

• Schema changes affect historical queries. Column-store snapshot queries always return results using the current table structure, not the structure that existed when the snapshot was created. For example, if you add a column to a table and then query a snapshot taken before that column was added, the result includes the new column based on the post-change table structure.

• autocommit is required for transactional restores. The INSERT ... SELECT statement can restore data within a transaction only when autocommit=true is set on the database.

• Avoid complex queries in INSERT ... SELECT. Complex subqueries in the SELECT clause may degrade performance significantly.

Query historical data from a snapshot

To query data at a specific snapshot point, use the AS OF TSO clause with FORCE INDEX in a SELECT statement:

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

Example:

-- FORCE INDEX must come after AS OF TSO.
SELECT * FROM tb1 AS OF TSO 7206138458723582016 FORCE INDEX(cci);

Restore data from a snapshot

To copy historical data from a snapshot into another table, use INSERT ... SELECT with the same AS OF TSO and FORCE INDEX clauses:

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

Example:

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

End-to-end example

The following example walks through creating a table, generating snapshots at different points, and querying or restoring data from each snapshot.

Step 1: Create the database and table.

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);

Step 2: Create a column-store snapshot named cci on tb1.

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"
}';

Step 3: Insert data and generate snapshot points.

INSERT INTO tb1 (id, a) VALUES (0, 0);

CALL polardbx.columnar_flush (test_columnar_snapshot, 'tb1', 'cci'); -- CCI-level snapshot; record the returned TSO as TSO1

INSERT INTO tb1 (id, a) VALUES (1, 1);

CALL polardbx.columnar_flush(); -- Instance-level snapshot; record the returned TSO as TSO2

INSERT INTO tb1 (id, a) VALUES (2, 2);

Step 4: Query data at each snapshot point.

-- Query at TSO1 (after inserting row 0).
SELECT * FROM tb1 AS OF TSO {TSO1} FORCE INDEX(cci) ORDER BY id;

Expected output:

+----+------+
| id | a    |
+----+------+
|  0 |    0 |
+----+------+

-- Query at TSO2 (after inserting rows 0 and 1).
SELECT * FROM tb1 AS OF TSO {TSO2} FORCE INDEX(cci) ORDER BY id;

Expected output:

+----+------+
| id | a    |
+----+------+
|  0 |    0 |
|  1 |    1 |
+----+------+

-- Query the latest data (all three rows).
SELECT * FROM tb1 FORCE INDEX(cci) ORDER BY id;

Expected output:

+----+------+
| id | a    |
+----+------+
|  0 |    0 |
|  1 |    1 |
|  2 |    2 |
+----+------+

Step 5: Restore data from a snapshot.

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);

-- Restore data from the TSO1 snapshot into tb1_tmp.
INSERT INTO tb1_tmp SELECT * FROM tb1 AS OF TSO {TSO1} FORCE INDEX(cci);

SELECT * FROM tb1_tmp ORDER BY id;

Expected output:

+----+------+
| id | a    |
+----+------+
|  0 |    0 |
+----+------+

What's next

• Generate snapshot points

• View and update the version of an instance