MaxCompute:Local backup

SHOW HISTORY FOR tables;

SHOW HISTORY FOR tables LIKE test_restore_1;

Name                	Id                              	Type            	IsPartitioned 	CreateTime         	DropTime
test_restore_1      	a0b06367bd054d17a55505aa31601b89	MANAGED_TABLE   	FALSE         	2025-07-18 16:32:13

SHOW HISTORY FOR TABLE <table_name> [LIMIT <n>] [LSN '<lsn>' | OFFSET <offset>];

SHOW HISTORY FOR TABLE test_restore_1;

ObjectType	ObjectId                        	ObjectName          	VERSION(LSN)    	Time               	Operation
TABLE     	a0b06367bd054d17a55505aa31601b89	test_restore_1      	0000000000000001	2025-07-18 16:32:14	CREATE
TABLE     	a0b06367bd054d17a55505aa31601b89	test_restore_1      	0000000000000002	2025-07-18 16:52:08	OVERWRITE
TABLE     	a0b06367bd054d17a55505aa31601b89	test_restore_1      	0000000000000003	2025-07-18 16:52:12	OVERWRITE
TABLE     	a0b06367bd054d17a55505aa31601b89	test_restore_1      	0000000000000004	2025-07-18 16:52:14	OVERWRITE
TABLE     	a0b06367bd054d17a55505aa31601b89	test_restore_1      	0000000000000005	2025-07-18 16:52:17	OVERWRITE

SHOW HISTORY FOR TABLE test_restore_2 ('id'='437b29466ba948b392b2090ec0a60fc2');

-- By partition spec
SHOW HISTORY FOR TABLE test_restore_part_1 PARTITION(ds='20250701');

-- By partition ID (for dropped partitions)
SHOW HISTORY FOR TABLE test_restore_part_1 PARTITION('id'='271aebf3e17a4a8e9f6b35733bf63db4');

SHOW HISTORY FOR TABLE test_restore_part_1 ('id'='fbee66b56cf544d2a9999d5d0ce5d352')
  PARTITION(ds='20250701') PARTITION(ds='20250702');

RESTORE TABLE <table_name> ('id'='<table_id>');

-- Rename the conflicting table
ALTER TABLE test_restore_2 RENAME TO test_restore_2_rename;

-- Restore the dropped table
RESTORE TABLE test_restore_2 ('id'='437b29466ba948b392b2090ec0a60fc2');

RESTORE TABLE <table_name> TO LSN '<lsn>';

RESTORE TABLE test_restore_1 TO LSN '0000000000000004';

SELECT * FROM test_restore_1;
-- Returns:
-- +------------+
-- | colname    |
-- +------------+
-- | 2          |
-- +------------+

RESTORE TABLE <table_name> TO LSN '<lsn>' AS <new_table_name>;

RESTORE TABLE test_restore_2 TO LSN '0000000000000003' AS test_restore_new;

SELECT * FROM test_restore_new;
-- Returns:
-- +------------+
-- | colname    |
-- +------------+
-- | 1          |
-- +------------+

RESTORE TABLE <table_name> ('id'='<table_id>');

RESTORE TABLE <table_name> PARTITION('id'='<partition_id>') [PARTITION('id'='<partition_id>')];

-- View partition IDs
SHOW HISTORY FOR TABLE test_restore_part_y;

-- Restore two dropped partitions
RESTORE TABLE test_restore_part_y
  PARTITION('id'='a14d6cb4ab0c46378a6e284b257bbfaa')
  PARTITION('id'='8c85184ec0b44fba8198274401df2519');

RESTORE TABLE <table_name>
  PARTITION(<partition_spec>) [PARTITION(<partition_spec>)]
  TO LSN '<lsn>';

RESTORE TABLE test_restore_part_y
  PARTITION(ds='20250701') PARTITION(ds='20250702')
  TO LSN '0000000000000010';

RESTORE TABLE <table_name>
  PARTITION(<partition_spec>) [PARTITION(<partition_spec>)]
  TO LSN '<lsn>' AS <new_table_name>;

-- Create a Delta table
CREATE TABLE mf_dt (pk BIGINT NOT NULL PRIMARY KEY,
                    val BIGINT NOT NULL)
                    PARTITIONED BY (dd STRING, hh STRING)
                    TBLPROPERTIES ("transactional"="true");

-- Insert data
INSERT OVERWRITE TABLE mf_dt PARTITION(dd='01', hh='01')
  VALUES (1, 1), (2, 2), (3, 3);

INSERT INTO TABLE mf_dt PARTITION(dd='01', hh='01')
  VALUES (3, 30), (4, 4), (5, 5);

SELECT * FROM mf_dt WHERE dd='01' AND hh='01';
-- Returns:
-- +----+-----+----+----+
-- | pk | val | dd | hh |
-- +----+-----+----+----+
-- | 1  | 1   | 01 | 01 |
-- | 4  | 4   | 01 | 01 |
-- | 5  | 5   | 01 | 01 |
-- | 2  | 2   | 01 | 01 |
-- | 3  | 30  | 01 | 01 |
-- +----+-----+----+----+

TableID:  cd607cd938dc4ca6886dd12212995604

DROP TABLE mf_dt;

RESTORE TABLE mf_dt ('id' = 'cd607cd938dc4ca6886dd12212995604');

-- Verify the data
SELECT * FROM mf_dt WHERE dd='01' AND hh='01';
-- Returns the same data as before the drop.

-- Get partition IDs
SHOW HISTORY FOR TABLE mf_dt;

-- Drop the partition
ALTER TABLE mf_dt DROP PARTITION (dd = '01', hh = '01');

-- Restore the partition
RESTORE TABLE mf_dt PARTITION('id' = '51d38cc9ded344cf99188cd1a806e5d2');

-- Verify the data
SELECT * FROM mf_dt WHERE dd='01' AND hh='01';
-- Returns the same data as before the drop.

-- Query a specific version
SELECT * FROM mf_dt version AS OF 2 WHERE dd = '01' AND hh = '01';
-- Returns:
-- +----+-----+----+----+
-- | pk | val | dd | hh |
-- +----+-----+----+----+
-- | 1  | 1   | 01 | 01 |
-- | 3  | 3   | 01 | 01 |
-- | 2  | 2   | 01 | 01 |
-- +----+-----+----+----+

-- Query the latest version
SELECT * FROM mf_dt version AS OF get_latest_version('mf_dt')
  WHERE dd = '01' AND hh = '01';
-- Returns the current data.

PURGE TABLE <table_name>;

CREATE TABLE mf_ttt (pk BIGINT NOT NULL PRIMARY KEY,
                     val BIGINT NOT NULL)
                     TBLPROPERTIES ("transactional"="true");

INSERT INTO TABLE mf_ttt VALUES (1, 1), (2, 2);
INSERT INTO TABLE mf_ttt VALUES (2, 20), (3, 3);

-- Time travel returns version 2 data
SELECT * FROM mf_ttt version AS OF 2;
-- +----+-----+
-- | pk | val |
-- +----+-----+
-- | 1  | 1   |
-- | 2  | 2   |
-- +----+-----+

-- Run compaction, then time travel still works
ALTER TABLE mf_ttt compact major;
SELECT * FROM mf_ttt version AS OF 2;
-- Same result as above

-- After purge, time travel returns different data
PURGE TABLE mf_ttt;
SELECT * FROM mf_ttt version AS OF 2;
-- +----+-----+
-- | pk | val |
-- +----+-----+
-- | 1  | 1   |
-- | 3  | 3   |
-- | 2  | 20  |
-- +----+-----+