All Products
Search

This Product

    MaxCompute:Backup and restoration

    Document Center

    MaxCompute:Backup and restoration

    Last Updated:Nov 19, 2024

    This topic describes the backup and restoration feature of MaxCompute. It also provides related commands and examples.

    Overview

    MaxCompute provides the backup and restoration feature. This feature enables MaxCompute to automatically back up and retain original data for a specific period of time. Original data refers to data on which no operations, such as deletion or modification, are performed. You can restore data within the retention period to prevent data loss.备份与恢复

    The backup and restoration feature provides the following benefits:

    • Automatic enabling

      This feature is independent of external storage. By default, the retention period of data in all MaxCompute projects is 24 hours. Data backup and storage are free of charge.

    • Automatic and continuous backup

      MaxCompute automatically backs up modified data. If you modify data multiple times, MaxCompute backs up data after each modification. This feature helps you prevent data loss in a more efficient manner compared to a periodic data backup policy.

    • Quick and easy-to-use data restoration

      MaxCompute provides advanced capabilities to manage multiple versions of data and metadata. Backup and restoration operations do not consume additional computing resources. You can run related commands to restore different volumes of data.

    The backup and restoration feature is in public preview. During public preview, you are not charged for data backup and storage. As of October 15, 2021, you are charged for data that is backed up on MaxCompute on a pay-as-you-go basis.

    Precautions

    When you use the backup and restoration feature, take note of the following points:

    • If you want to view the backup information of a table on the MaxCompute client, we recommend that you download the latest version of the MaxCompute client. If you use the MaxCompute client of an earlier version, you may fail to view the backup and restoration parameters or the backup information of the table.

    • A backup table is generated each time you modify a table. No backup table is generated if you do not modify a table.

    • If the number of days for which a backup table is stored exceeds the backup data retention period that is configured for the project, MaxCompute deletes the backup table. The backup table that is deleted cannot be restored or queried.

    • After the purge command is run on a table, data in the table cannot be restored.

    • Dropped partitions or tables, including dropped Delta tables, cannot be directly restored to a specified log sequence number (LSN). You must first restore the partitions or tables and then restore the partitions or tables to a specified LSN.

    • The backup and restoration feature imposes the following limits on Delta tables:

      • You can completely restore a dropped partition or table. You cannot restore a dropped partition or table to a specific minor version. If you want to query data of a minor version, you can use the time travel feature.

      • After a dropped partition or table is restored, the process of performing time travel queries and incremental queries is almost the same as the process of performing the operations before the partition or table is dropped. However, you must take note of the following items:

        • If you perform a query, such as a time travel query, on a dropped table before the table is restored, an error is reported. If you perform a query on a dropped partition before the partition is restored, no query result is returned, which is the same as a normal query.

        • If the query time specified for a time travel query or incremental query on a dropped table that is restored is earlier than the time when the table is restored but is later than or equal to the time when the table is dropped, the system considers that the queried data is deleted, and no query result is returned.

        • If the query time specified for a time travel query or incremental query on a dropped table that is restored is earlier than the time when the table is dropped, the query result is the same as the query result returned if no dropping operation is performed.

        • If the query time specified for a time travel query or incremental query on a dropped table that is restored is later than or equal to the time when the table is restored, the query result is the same as the query result returned if no dropping and restoration operations are performed. The data that is restored is not considered incremental data.

      • After you perform dropping and restoration operations, the system generates commit time and versions. If you restore a partitioned table, all partitions in the table are separately restored. The system generates a version for each partition.

    Commands

    The following table describes the commands involved when you use the backup and restoration feature.

    Scenario

    Command

    Description

    Remarks

    Configure a retention period for backup data

    setproject odps.timemachine.retention.days=days;

    This command is used to configure a retention period for backup data. During the retention period, you can restore data of the version in use to the backup data of any version.

    The value of days ranges from 0 to 30. The default value is 1. The value 0 indicates that the backup feature is disabled.

    If you adjust the retention period, MaxCompute manages backup data based on the following rules:

    • If you prolong the retention period, MaxCompute retains the backup data based on the new period immediately after you specify it.

    • If you shorten the retention period, MaxCompute automatically deletes the backup data that exceeds the new retention period.

    Only Alibaba Cloud accounts or project administrators have the permission to configure a retention period for backup data.

    setproject;

    This command is run on the MaxCompute client to obtain information about project-level parameters. For more information about how to use the MaxCompute client, see MaxCompute client (odpscmd). You can view the value of odps.timemachine.retention.days. If the value of odps.timemachine.retention.days is 1, the retention period of the backup data for the project is one day, or 24 hours.

    View backup data

    show history for tables [like <table_name>];

    This command is used to view information about tables in a project. The tables include dropped tables and tables in the backup state. This command is different from the show tables; command.

    You must have the List permission on the project.

    For more information, see MaxCompute permissions.

    show history for table <table_name>;

    This command is used to view the backup data of a specified table and obtain information about the data versions within the retention period.

    • If the table exists, you must have the ShowHistory permission on the table.

    • If the table is dropped, an error is returned after you run this command. You can run the show history for tables [like <table_name>]; command to obtain the information of a dropped table and information about the backup data versions.

    For more information, see MaxCompute permissions.

    show history for table table_name ('id'='xxxx');

    This command is used to view the backup data of a dropped table and obtain information about the data versions within the retention period.

    You can run the show history for tables [like <table_name>]; command to obtain the name and ID of a dropped table.

    You must have the List permission on the project.

    For more information, see MaxCompute permissions.

    show history for table table_name partition_spec;

    This command is used to view the backup data of a specified partition and obtain information about the data versions within the retention period.

    None.

    show history for table table_name PARTITION('id'='xxxx');

    This command is used to view the backup data of a dropped partition and obtain information about the data versions within the retention period. You can obtain the value of id from the ObjectId field in the command output of show history for table <table_name>;.

    Restore data

    restore table table_name ('id'='xxxxx');

    This command is used to restore a dropped table.

    You can run the show history for tables [like <table_name>]; command to obtain the name and ID of a dropped table.

    • When you restore a table, take note of the following points:

      • If the table exists, you must have the Update permission on the table.

      • If the table does not exist, you must have the CreateTable permission on the project.

        For more information, see MaxCompute permissions.

      Note

      Clustered tables cannot be restored after the tables are dropped.

    • When you restore a partition, you can specify only one LSN. Each LSN corresponds to a data version.

      If you restore multiple partitions at a time, MaxCompute restores each partition to a specified LSN. If a partition does not have its specified LSN, MaxCompute restores the partition to the first LSN.

      For example, the pt1 partition has the LSNs of 100, 102, 104, and 106, and the pt2 partition has the LSNs of 101, 103, 104, and 105. To restore the partitions, run the following command: 

      restore table table_name PARTITION(pt='1') PARTITION(pt='2') to LSN '102';

      After you run this command, the pt1 partition is restored to LSN 102, and the pt2 partition is restored to LSN 101.

    restore table table_name to LSN 'xxxx';

    This command is used to restore a table to a specified LSN.

    You can run the show history for table <table_name>; command to obtain the LSNs of a table.

    restore table table_name to LSN 'xxxx' as new_table_name;

    This command is used to restore a table to a specified LSN and rename the table. You can also use this command to restore a table to a specified LSN and save its data to an existing table that has a different name.

    restore table table_name PARTITION('id'='xxxx')[PARTITION('id'='xxxx')];

    This command is used to restore a dropped partition. You can restore multiple dropped partitions at a time. In normal cases, this command is used to restore the partitions for which the DROP PARTITION statement is executed or to restore the partitions that are reclaimed after their lifecycle ends.

    restore table table_name partition_spec1[partition_spec2 ]to LSN 'xxxx';

    This command is used to restore a specified partition to a specified LSN. You can restore multiple partitions at a time. In normal cases, this command is used to restore the partitions for which the overwrite or merge operation is performed.

    restore table table_name partition_spec1[partition_spec2 ]to LSN 'xxxx' as new_table_name;

    This command is used to restore a specified partition to a specified LSN and rename the table to which the partition belongs.

    Operations for deleting data files

    MaxCompute retains historical data within the time range specified by the acid.data.retain.hours parameter for Delta tables. The retention generates additional storage costs. In other scenarios, MaxCompute automatically deletes historical data that is no longer required within one day after the commit time, which frees you from performing additional operations and helps reduce additional storage costs. The following types of historical data can be deleted:

    • Historical data that is generated at a point in time earlier than the time range specified by the acid.data.retain.hours parameter. If the time travel feature is disabled for the related table, all historical data can be automatically deleted.

    • Historical data that meets one of the following conditions: 1. The retention period exceeds the specified lifecycle. 2. A dropping operation is performed on the table to which the historical data belongs, and the retention period of the data backup generated for the dropping operation exceeds the backup protection threshold.

    Only the preceding types of historical data can be automatically deleted.

    Note

    Historical data refers to the data that is moved to the recycle bin. In normal cases, in addition to exceeding the specified lifecycle or performing a dropping operation, compaction or execution of the INSERT OVERWRITE statement also causes existing data files in the directory to which data is written to be moved to the recycle bin for automatic deletion.

    • Syntax for the command used to forcefully delete data files

      -- Manually and forcefully delete historical data files in the recycle bin.
purge table <table_name>;

    • Usage notes and limits

      • After you run the preceding command, MaxCompute immediately deletes all historical data in the recycle bin. As a result, no result is returned after you perform a time travel query to obtain historical data.

      • This command is often used in special scenarios for handling emergencies. For example, you can use this command if the read and write performance of a disk is unstable due to excessive files. You can also use this command if storage costs surge due to excessive historical data. In normal cases, MaxCompute automatically deletes historical data in the recycle bin. You do not need to perform additional operations.

    • Example

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

select * from mf_ttt version as of 2;
-- The following result is returned:
+------------+------------+
| pk         | val        |
+------------+------------+
| 1          | 1          |
| 2          | 2          |
+------------+------------+

-- Perform a compaction operation, run the purge command, and then perform a time travel query.
alter table mf_ttt compact major;
select * from mf_ttt version as of 2;
-- The following result is returned:
+------------+------------+
| pk         | val        |
+------------+------------+
| 1          | 1          |
| 2          | 2          |
+------------+------------+

purge table mf_ttt;
select * from mf_ttt version as of 2;
-- The following result is returned:
+------------+------------+
| pk         | val        |
+------------+------------+
| 1          | 1          |
| 3          | 3          |
| 2          | 20         |
+------------+------------+

    View backup data

    This section describes how to view the backup data of tables in the test_restore project.

    • View the backup data of all tables.

      Run the show history for tables [like <table_name>]; command. Example: 

      ## Create a table named mf_slot. 
odps@ test_restore>Create Table mf_slot(a string);
## View the backup data of the mf_slot table. 
odps@ test_restore>show history for tables like mf_slot;

      The following result is returned: 

      ID = 20230913031801784gyn8qbae3v8
Name                    Id                                      Type                    IsPartitioned   CreateTime              DropTime
mf_slot                 c75dccd60b224ff086171d39b1904eb4        MANAGED_TABLE           FALSE           2023-09-13 11:17:52

OK

    • View the backup data of a specified table.

      Run the show history for table <table_name>; command. Example: 

      ## Create a table named test_restore_x. 
odps@ test_restore>Create Table test_restore_x(a string);
## Update the data of the test_restore_x table. 
odps@ test_restore>INSERT OVERWRITE TABLE test_restore_x values("0");
odps@ test_restore>INSERT OVERWRITE TABLE test_restore_x values("1");
odps@ test_restore>INSERT OVERWRITE TABLE test_restore_x values("2");
odps@ test_restore>INSERT OVERWRITE TABLE test_restore_x values("3");
## View the backup data of the test_restore_x table. 
odps@ test_restore>show history for table test_restore_x;

      The following result is returned: 

      ObjectType  ObjectId                            ObjectName         LSN               Time                Operation
TABLE       d6266b2c49b9418cb999dc65c10ad7ae    test_restore_x     0000000000000001  2020-02-18 14:17:58 CREATE
TABLE       d6266b2c49b9418cb999dc65c10ad7ae    test_restore_x     0000000000000002  2020-02-18 14:22:26 OVERWRITE
TABLE       d6266b2c49b9418cb999dc65c10ad7ae    test_restore_x     0000000000000003  2020-02-18 14:23:32 OVERWRITE
TABLE       d6266b2c49b9418cb999dc65c10ad7ae    test_restore_x     0000000000000004  2020-02-18 14:24:37 OVERWRITE
TABLE       d6266b2c49b9418cb999dc65c10ad7ae    test_restore_x     0000000000000005  2020-02-18 14:25:44 OVERWRITE

    • View the backup data of a dropped table.

      Run the show history for table table_name ('id'='xxxx'); command. Example: 

      ## Drop the test_restore_x table. 
odps@ test_restore>drop table test_restore_x;
## Confirm the deletion of the test_restore_x table. 
Confirm to "drop table test_restore_x;" (yes/no)? yes
## View the backup data of the dropped table test_restore_x. 
odps@ test_restore>show history for table test_restore_x('id'='d6266b2c49b9418cb999dc65c10ad7ae');

      The following result is returned: 

      ObjectType  ObjectId                            ObjectName         LSN                 Time                Operation
TABLE       d6266b2c49b9418cb999dc65c10ad7ae    test_restore_x     0000000000000001    2020-02-18 14:17:58 CREATE
TABLE       d6266b2c49b9418cb999dc65c10ad7ae    test_restore_x     0000000000000002    2020-02-18 14:22:26 OVERWRITE
TABLE       d6266b2c49b9418cb999dc65c10ad7ae    test_restore_x     0000000000000003    2020-02-18 14:23:32 OVERWRITE
TABLE       d6266b2c49b9418cb999dc65c10ad7ae    test_restore_x     0000000000000004    2020-02-18 14:24:37 OVERWRITE
TABLE       d6266b2c49b9418cb999dc65c10ad7ae    test_restore_x     0000000000000005    2020-02-18 14:25:44 OVERWRITE
TABLE       d6266b2c49b9418cb999dc65c10ad7ae    test_restore_x     0000000000000006    2020-02-18 14:30:32 DROP

    • View the backup data of a partitioned table or partition.

      Run the show history for table table_name ('id'='xxxx'); command to view the backup data of a partitioned table. Run the show history for table table_name partition_spec; or show history for table table_name PARTITION('id'='xxxx'); command to view the backup data of a partition.

      View the backup data of a partitioned table. Example: 

      ## Create a table named test_restore_part_x. 
odps@ test_restore>Create Table test_restore_part_x(a string) PARTITIONED BY(ds string);
## Update the data of the test_restore_part_x table. 
odps@ test_restore>INSERT OVERWRITE TABLE test_restore_part_x partition(ds="20191201") values ("1");
odps@ test_restore>INSERT OVERWRITE TABLE test_restore_part_x partition(ds="20191202") values ("2");
odps@ test_restore>INSERT OVERWRITE TABLE test_restore_part_x partition(ds="20191203") values ("3");
odps@ test_restore>INSERT OVERWRITE TABLE test_restore_part_x partition(ds="20191204") values ("4");
odps@ test_restore>INSERT OVERWRITE TABLE test_restore_part_x partition(ds="20191205") values ("5");
odps@ test_restore>INSERT OVERWRITE TABLE test_restore_part_x partition(ds="20191205") values ("6");
odps@ test_restore>INSERT OVERWRITE TABLE test_restore_part_x partition(ds="20200101") values ("20200101");
odps@ test_restore>INSERT OVERWRITE TABLE test_restore_part_x partition(ds="20200102") values ("20200102");
## View the backup data of the test_restore_part_x table. 
odps@ test_restore>show history for table test_restore_part_x('id'='94d436523fe14ba39f33d2dee738c018');

      The following result is returned: 

      ObjectType  ObjectId                            ObjectName             LSN                 Time                Operation
TABLE       94d436523fe14ba39f33d2dee738c018    test_restore_part_x    0000000000000001    2020-02-18 17:29:35 CREATE
PARTITION   f4614a34620346aaa729761f082aae74    ds=20191201            0000000000000002    2020-02-18 17:32:56 CREATE
PARTITION   0698ed40169044c7bf66b14a3c3c2f35    ds=20191202            0000000000000003    2020-02-18 17:35:12 CREATE
PARTITION   19f26f7b1976438c94f8f53cfb5c6912    ds=20191203            0000000000000004    2020-02-18 17:35:22 CREATE
PARTITION   dc15ed7d5da441649a5f32c4929b2fb2    ds=20191204            0000000000000005    2020-02-18 17:35:57 CREATE
PARTITION   e01128f1183b44369b06dae1e73a8134    ds=20191205            0000000000000006    2020-02-18 17:37:21 CREATE
PARTITION   e01128f1183b44369b06dae1e73a8134    ds=20191205            0000000000000007    2020-02-18 17:37:48 OVERWRITE
PARTITION   189727214c0d4e8e92b52814211dd086    ds=20200101            0000000000000008    2020-02-18 17:37:59 CREATE
PARTITION   adbc79ade65d4b0dbea4a4dcbf0ce719    ds=20200102            0000000000000009    2020-02-18 17:38:09 CREATE

      View the backup data of a specified partition. Example: 

      ## View the backup data of a specified partition in the test_restore_part_x table. 
odps@ test_restore>show history for table test_restore_part_x('id'='94d436523fe14ba39f33d2dee738c018') partition(ds='20191201') partition(ds='20191202');

      The following result is returned: 

      ObjectType  ObjectId                            ObjectName           LSN                 Time                Operation
PARTITION   f4614a34620346aaa729761f082aae74    ds=20191201          0000000000000002    2020-02-18 17:32:56 CREATE
PARTITION   0698ed40169044c7bf66b14a3c3c2f35    ds=20191202          0000000000000003    2020-02-18 17:35:12 CREATE

    Restore a non-partitioned table

    This section describes how to restore a non-partitioned table in the test_restore project.

    • Restore a dropped table.

      Run the restore table table_name ('id'='xxxxx'); command. Make sure that the name of the table you want to restore is unique. If another table has the same name, change the name of the existing table first. Example: 

      ## Query the backup data of the dropped table test_restore_x. 
odps@ test_restore>show history for table test_restore_x('id'='d6266b2c49b9418cb999dc65c10ad7ae');
## Create a table named test_restore_x. 
odps@ test_restore>Create Table test_restore_x(a string);
## Restore the dropped test_restore_x table. However, an error is returned because an existing table has the same name. 
odps@ test_restore>restore table test_restore_x('id'='d6266b2c49b9418cb999dc65c10ad7ae');
## Rename the test_restore_x table that is created. 
odps@ test_restore>alter table test_restore_x rename to test_restore_x_rename;
## Restore the dropped table test_restore_x. 
odps@ test_restore>restore table test_restore_x('id'='d6266b2c49b9418cb999dc65c10ad7ae');

      OK is returned.

    • Restore a table to a specified LSN. If you want to restore a dropped table to a specified LSN, you must restore the table first.

      Run the restore table table_name to LSN 'xxxx'; command. Example: 

      ## Restore the test_restore_x table to a specified LSN. 
odps@ test_restore>restore table test_restore_x to LSN '0000000000000004';
## Query the data of the test_restore_x table. 
odps@ test_restore>select * from test_restore_x;

      The following result is returned: 

      Summary:
+---+
| a |
+---+
| 2 |
+---+

    • Restore a table to a specified LSN and rename the table, or restore a table to a specified LSN and save data in the table to an existing table that has a different name.

      Run the restore table table_name to LSN 'xxxx' as new_table_name; command.

      You may run this command in the following three scenarios:

      • Restore a table to a specified LSN and rename the table. 

        ## Restore the dropped table test_restore_x to a specified LSN and rename the table as test_restore_x_v5. 
odps@ test_restore>restore table test_restore_x to LSN '0000000000000005' as test_restore_x_v5;
## Query the data of the test_restore_x_v5 table. 
odps@ test_restore>select * from test_restore_x_v5;

        The following result is returned: 

        Summary:
+---+
| a |
+---+
| 3 |
+---+

      • Restore a table to a specified LSN and save data in the table to an existing table that has a different name. 

        ## Restore the test_restore_x table to a specified LSN and save data in the table to the existing table test_restore_x_v5. 
odps@ test_restore>restore table test_restore_x to LSN '0000000000000005' as test_restore_x_v5;
## Query the backup data of the test_restore_x_v5 table. 
odps@ test_restore>show history for table test_restore_x_v5;

        The following result is returned: 

        ObjectType  ObjectId                            ObjectName            LSN                 Time                Operation
TABLE       2e17a61561e4456db8ab66d0dd41e1b9    test_restore_x_v5     0000000000000001    2020-02-18 16:18:41 CREATE
TABLE       2e17a61561e4456db8ab66d0dd41e1b9    test_restore_x_v5     0000000000000003    2020-02-18 16:22:07 OVERWRITE

      • Restore a table to a specified LSN and save data in the table to an existing table that has a different name and a different schema. The operation fails. The two tables must have the same schema. The following code shows an example: 

        ## Create a table that has a different schema from the original table. 
odps@ test_restore>Create Table test_restore_2cols(a string, b string);
## Restore the test_restore_x table to a specified LSN and save data in the table to the test_restore_2cols table. 
odps@ test_restore>restore table test_restore_x to LSN '0000000000000005' as test_restore_2cols;

        The following result is returned: 

        FAILED: Catalog Service Failed, ErrorCode: 105, Error Message: ODPS-0110061: Failed to run ddltask - Restore table failed because: field schema not same, [{"comment":"","id":"","name":"a","type":"string"}] vs [{"comment":"","id":"","name":"a","type":"string"},{"comment":"","id":"","name":"b","type":"string"}]

    Restore a partitioned table or specific partitions

    This section describes how to restore a partitioned table or specific partitions in the test_restore project.

    • Restore a partitioned table.

      Run the restore table table_name ('id'='xxxxx'); command. 

      ## Create a table named test_restore_part_x. 
odps@ test_restore>Create Table test_restore_part_x(a string) PARTITIONED BY(ds string);
## Update the data of the test_restore_part_x table. 
odps@ test_restore>INSERT OVERWRITE TABLE test_restore_part_x partition(ds="20191201") values ("1");
odps@ test_restore>INSERT OVERWRITE TABLE test_restore_part_x partition(ds="20191202") values ("2");
odps@ test_restore>INSERT OVERWRITE TABLE test_restore_part_x partition(ds="20191203") values ("3");
odps@ test_restore>INSERT OVERWRITE TABLE test_restore_part_x partition(ds="20191204") values ("4");
odps@ test_restore>INSERT OVERWRITE TABLE test_restore_part_x partition(ds="20191205") values ("5");
odps@ test_restore>INSERT OVERWRITE TABLE test_restore_part_x partition(ds="20191205") values ("6");
odps@ test_restore>INSERT OVERWRITE TABLE test_restore_part_x partition(ds="20200101") values ("20200101");
odps@ test_restore>INSERT OVERWRITE TABLE test_restore_part_x partition(ds="20200102") values ("20200102");
## View the partitions in the test_restore_part_x table. 
odps@ test_restore>list partitions test_restore_part_x;
## View the data of the test_restore_part_x table. 
odps@ test_restore>select * from test_restore_part_x;
## Drop the test_restore_part_x table. 
odps@ test_restore>drop table test_restore_part_x;
## Confirm the dropping of the test_restore_part_x table. 
Confirm to "drop table test_restore_part_x;" (yes/no)? yes
## Restore the test_restore_part_x table. 
odps@ test_restore>restore table test_restore_part_x('id'='94d436523fe14ba39f33d2dee738c018');
## View the backup data of the test_restore_part_x table. 
odps@ test_restore>show history for table test_restore_part_x('id'='94d436523fe14ba39f33d2dee738c018');
## View the partitions in the test_restore_part_x table. 
odps@ test_restore>list partitions test_restore_part_x;

      The following result is returned: 

      ds=20191201
ds=20191202
ds=20191203
ds=20191204
ds=20191205
ds=20200101
ds=20200102

    • Restore specific partitions. If you want to restore specific partitions in a dropped table, you must restore the table first.

      Run the restore table table_name PARTITION('id'='xxxx')[PARTITION('id'='xxxx')]; command. Example: 

      ## Create a table named test_restore_part_y. 
odps@ test_restore>Create Table test_restore_part_y(a string) PARTITIONED BY(ds string);
## Update the data of the test_restore_part_y table. 
odps@ test_restore>INSERT OVERWRITE TABLE test_restore_part_y partition(ds="20191201") values ("1");
odps@ test_restore>INSERT OVERWRITE TABLE test_restore_part_y partition(ds="20191202") values ("2");
odps@ test_restore>INSERT OVERWRITE TABLE test_restore_part_y partition(ds="20191203") values ("3");
odps@ test_restore>INSERT OVERWRITE TABLE test_restore_part_y partition(ds="20191204") values ("4");
odps@ test_restore>INSERT OVERWRITE TABLE test_restore_part_y partition(ds="20191205") values ("5");
odps@ test_restore>INSERT OVERWRITE TABLE test_restore_part_y partition(ds="20191206") values ("6");
odps@ test_restore>INSERT OVERWRITE TABLE test_restore_part_y partition(ds="20200101") values ("20200101");
odps@ test_restore>INSERT OVERWRITE TABLE test_restore_part_y partition(ds="20200102") values ("20200102");
## View the partitions in the test_restore_part_y table. 
odps@ test_restore>list partitions test_restore_part_y;
## Drop specific partitions from the test_restore_part_y table. 
odps@ test_restore>alter table test_restore_part_y drop partition(ds='20191201'),partition(ds='20191202');
## Confirm the dropping of the partitions. 
Confirm to "alter table test_restore_part_y drop partition(ds='20191201'),partition(ds='20191202');" (yes/no)? yes
## View the partitions in the test_restore_part_y table. 
odps@ test_restore>list partitions test_restore_part_y;
## Restore the partitions in the test_restore_part_y table. 
odps@ test_restore>restore table test_restore_part_y partition('id'='e6647109adbe44b69068a4dd83a577ad') partition('id'='bc4aaf375ab94998b02dabb0fed0b5fe');
## View the partitions in the test_restore_part_y table. 
odps@ test_restore>list partitions test_restore_part_y;

      The following result is returned: 

      ds=20191201
ds=20191202
ds=20191203
ds=20191204
ds=20191205
ds=20191206
ds=20200101
ds=20200102

    • Restore specific partitions to a specified LSN. If you want to restore specific partitions in a dropped table to a specified LSN, you must restore the table first.

      Run the restore table table_name partition_spec1[partition_spec2] to LSN 'xxxx'; command. Example: 

      ## Update the data of the test_restore_part_y table. 
odps@ test_restore>INSERT OVERWRITE TABLE test_restore_part_y partition(ds="20200101") values ("20200101_v1");
odps@ test_restore>INSERT OVERWRITE TABLE test_restore_part_y partition(ds="20200102") values ("20200102_v1");
odps@ test_restore>INSERT OVERWRITE TABLE test_restore_part_y partition(ds="20200101") values ("20200101_v2");
odps@ test_restore>INSERT OVERWRITE TABLE test_restore_part_y partition(ds="20200102") values ("20200102_v2");
## View the data of the specified partitions in the test_restore_part_y table. 
odps@ test_restore>select * from test_restore_part_y where ds='20200101' or ds='20200102';
## Restore the partitions in the test_restore_part_y table to a specified LSN. 
odps@ test_restore>restore table test_restore_part_y partition(ds='20200101') partition(ds='20200102') to LSN '0000000000000010';
## View the data of the specified partitions in the test_restore_part_y table. 
odps@ test_restore>select * from test_restore_part_y where ds='20200101' or ds='20200102';

      The following result is returned: 

      +---+----+
| a | ds |
+---+----+
| 20200101| 20200101|
| 20200102| 20200102|
+---+----+

    • Restore specific partitions to a specified LSN and rename the table. If you want to restore specific partitions in a dropped table to a specified LSN and rename the table, you must restore the table first.

      Run the restore table table_name partition_spec1[partition_spec2] to LSN 'xxxx' as new_table_name; command. Example: 

      ## Restore the partitions to a specified LSN and rename the table as test_restore_part_y_v10. 
odps@ test_restore>restore table test_restore_part_y partition(ds='20200101') partition(ds='20200102') to LSN '0000000000000010' as test_restore_part_y_v10;
## View the data of the test_restore_part_y_v10 table. 
odps@ test_restore>select * from test_restore_part_y_v10;

      The following result is returned: 

      +---+----+
| a | ds |
+---+----+

| 20200101| 20200101|
| 20200102| 20200102|
+---+----+

    Example for Delta tables

    -- Create a Delta table, insert data into the table, and then query data from the 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 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';
-- The following result is returned:
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 3          | 30         | 01 | 01 |
| 4          | 4          | 01 | 01 |
| 5          | 5          | 01 | 01 |
| 2          | 2          | 01 | 01 |
+------------+------------+----+----+
-- Restore the Delta table. You can obtain the ID of the Delta table by running the desc extended table command.
desc extended mf_dt;
+------------------------------------------------------------------------------------+
| Owner:                    ALIYUN$mofan_***@test.aliyunid.com
| Project:                  mf_mc_bj                                                 |
| TableComment:                                                                      |
+------------------------------------------------------------------------------------+
| CreateTime:               2023-06-13 20:45:33                                      |
| LastDDLTime:              2023-06-13 20:45:33                                      |
| LastModifiedTime:         2023-06-13 20:45:41                                      |
+------------------------------------------------------------------------------------+
| InternalTable: YES      | Size: 8538                                               |
+------------------------------------------------------------------------------------+
| Native Columns:                                                                    |
+------------------------------------------------------------------------------------+
| Field    | Type   | Label | ExtendedLabel | Nullable | DefaultValue | Comment      |
+------------------------------------------------------------------------------------+
| pk       | bigint |       |               | false    | NULL         |              |
| val      | bigint |       |               | false    | NULL         |              |
+------------------------------------------------------------------------------------+
| Partition Columns:                                                                 |
+------------------------------------------------------------------------------------+
| dd              | string     |                                                     |
| hh              | string     |                                                     |
+------------------------------------------------------------------------------------+
| Extended Info:                                                                     |
+------------------------------------------------------------------------------------+
| TableID:                  a80ff25de1444ac3aa6a67a522d2110f                         |
| IsArchived:               false                                                    |
| PhysicalSize:             25614                                                    |
| FileNum:                  9                                                        |
| StoredAs:                 AliOrc                                                   |
| CompressionStrategy:      normal                                                   |
| Transactional:            true                                                     |
| IsolationMin:             NONSTRICT_SNAPSHOT_ISOLATION                             |
| odps.timemachine.retention.days: 1                                                        |
| encryption_enable:        false                                                    |
| ClusterType:              hash                                                     |
| BucketNum:                16                                                       |
| ClusterColumns:           [pk]                                                     |
| SortColumns:              [pk ASC]                                                 |
+------------------------------------------------------------------------------------+
drop table mf_dt;
restore table mf_dt ('id' = 'a80ff25de1444ac3aa6a67a522d2110f');
select * from mf_dt where dd='01' and hh='01';
-- The following result is returned:
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 3          | 30         | 01 | 01 |
| 4          | 4          | 01 | 01 |
| 5          | 5          | 01 | 01 |
| 2          | 2          | 01 | 01 |
+------------+------------+----+----+

-- Restore a partition in the Delta table. You can obtain the ID of the partition by running the show history for table command.
show history for table mf_dt;
ObjectType	ObjectId                        	ObjectName          	VERSION(LSN)    	Time               	Operation       
TABLE     	a80ff25de1444ac3aa6a67a522d2110f	mf_dt              	0000000000000001	2023-06-13 20:45:33	CREATE          
PARTITION 	90a22a7f485742f7b56c460ce1c246be	dd=01/hh=01         	0000000000000002	2023-06-13 20:45:37	CREATE          
PARTITION 	90a22a7f485742f7b56c460ce1c246be	dd=01/hh=01         	0000000000000003	2023-06-13 20:45:42	APPEND          
TABLE     	a80ff25de1444ac3aa6a67a522d2110f	mf_dt              	0000000000000004	2023-06-13 20:46:49	DROP            
TABLE     	a80ff25de1444ac3aa6a67a522d2110f	mf_dt              	0000000000000005	2023-06-13 20:46:51	RESTORE         
PARTITION 	90a22a7f485742f7b56c460ce1c246be	dd=01/hh=01         	0000000000000006	2023-06-13 20:46:52	RESTORE  

alter table mf_dt drop partition (dd = '01', hh = '01');
restore table mf_dt partition('id' = '90a22a7f485742f7b56c460ce1c246be');
select * from mf_dt where dd='01' and hh='01';
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 3          | 30         | 01 | 01 |
| 4          | 4          | 01 | 01 |
| 5          | 5          | 01 | 01 |
| 2          | 2          | 01 | 01 |
+------------+------------+----+----+

-- Perform a time travel query on the restored Delta table.
select * from mf_dt version as of 2 where dd = '01' and hh = '01';
-- The following result is returned:
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 3          | 3          | 01 | 01 |
| 2          | 2          | 01 | 01 |
+------------+------------+----+----+
select * from mf_dt version as of get_latest_version('mf_dt') 
         where dd = '01' and hh = '01';
-- The following result is returned:
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 3          | 30         | 01 | 01 |
| 4          | 4          | 01 | 01 |
| 5          | 5          | 01 | 01 |
| 2          | 2          | 01 | 01 |
+------------+------------+----+----+