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. This way, you can restore data within the retention period to prevent data loss. Backup and restoration
The backup and restoration feature provides the following benefits:
  • Automatic enablement

    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.

Usage notes

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.

Commands

The following table describes the commands involved when you use the backup and restoration feature.
Scenario Command Description Remarks
Set a retention period for backup data setproject odps.timemachine.retention.days=days; This command is used to set a retention period for backup data. During the retention period, you can restore data of the current version 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 permissions to set a retention period for backup data.
setproject; This command is used to obtain project information. You can view the value of odps.timemachine.retention.days. odps.timemachine.retention.days=1 indicates that the retention period of the backup data for the current project is one day, or 24 hours.
View backup data show history for tables; This command is used to view information about tables in a project and tables in the backup state. You can view information such as the table names, table IDs, creation time, and deletion time. This command is different from the show tables; command. You must have the List permission on the project.

For more information, see 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 deleted, an error is returned after you run this command. You can run the show history for tables; command to obtain the information of a deleted table and information about the backup data versions.

For more information, see Permissions.

show history for table table_name ('id'='xxxx'); This command is used to view the backup data of a deleted table and obtain information about the data versions within the retention period.

You can run the show history for tables; command to obtain the name and ID of a deleted table.

You must have the List permission on the project.

For more information, see 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 deleted 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 ;.
Restore data restore table table_name ('id'='xxxxx'); This command is used to restore a deleted table.

You can run the show history for tables; command to obtain the name and ID of a deleted 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 Permissions.

    Note Clustered tables cannot be restored after the tables are deleted.
  • When you restore a partition, you can specify only one log sequence number (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 ; 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 deleted partition. You can restore multiple deleted 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.

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; command. Example: 

    odps@ test_restore>show history for tables;
    The following result is returned: 
    Name                   Id                                  Type                IsPartitioned   CreateTime          DropTime
partition_table_1      2815aee9cab74881975705789a01d7ae    MANAGED_TABLE       TRUE            2020-02-14 12:42:01
test_restore_part_y    1d42e7d9e0044d389776ccbd3a8ad7ae    MANAGED_TABLE       TRUE            2020-01-27 16:01:50 2020-01-27 18:16:49
  • View the backup data of a specified table

    Run the show history for table ; 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 deleted table

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

    ## Delete 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 deleted 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 deleted 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 as the table, change its name first. Example: 

    ## Query the backup data of the deleted table test_restore_x. 
odps@ test_restore>show history for table test_restore_x('id'='d6266b2c49b9418cb999dc65c10ad7ae');
## Create a table named test_restore_part_x. 
odps@ test_restore>Create Table test_restore_x(a string);
## Restore the test_restore_part_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_part_x table. 
odps@ test_restore>alter table test_restore_x rename to test_restore_x_rename;
## Restore the deleted table test_restore_part_x. 
odps@ test_restore>restore table test_restore_x('id'='d6266b2c49b9418cb999dc65c10ad7ae');
    OK is returned.
  • Restore a table to a specified LSN

    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_part_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. 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.

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

    You can run this command in the following three scenarios:
    • Restore a table to a specified LSN and rename the table. 
      ## Restore the deleted table test_restore_x to a specified LSN and rename the table. 
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 its data to an existing table that has a different name. 
      ## Restore the test_restore_x table to a specified LSN and save its data to an existing table that has a different name. 
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 its data to an existing table that has a different name and schema. The operation fails. The two tables must have the same schema. 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 its data 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. 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 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;
## Delete the test_restore_part_x table. 
odps@ test_restore>drop table test_restore_part_x;
## Confirm the deletion 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

    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;
## Delete 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 deletion 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

    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

    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|
+---+----+