Restoring an Amazon Keyspaces table to a point in time

The following example demonstrates how to use the Amazon Keyspaces console to restore an existing table named mytable to a point in time.

1. Sign in to the Amazon Web Services Management Console, and open the Amazon Keyspaces console at https://console.amazonaws.cn/keyspaces/home.

2. In the navigation pane on the left side of the console, choose Tables.

3. In the list of tables, choose the mytable table.

4. On the Backups tab of the mytable table, in the Point-in-time recovery section, choose Restore.

5. For the new table name, enter mytable_restored.

6. To define the point in time for the restore operation, you can choose between two options:

   • Select the preconfigured Earliest time.

   • Select Specify date and time and enter the date and time you want to restore the new table to.

   Note

   You can restore to any point in time within Earliest time and the current time. Amazon Keyspaces restores your table data to the state based on the selected date and time (day:hour:minute:second).

7. Choose Restore to start the restore process.

   The table that is being restored is shown with the status Restoring. After the restore process is finished, the status of the mytable_restored table changes to Active.

   Important

   While a restore is in progress, don't modify or delete the Amazon Identity and Access Management (IAM) policies that grant the IAM entity (for example, user, group, or role) permission to perform the restore. Otherwise, unexpected behavior can result. For example, suppose that you removed write permissions for a table while that table was being restored. In this case, the underlying RestoreTableToPointInTime operation can't write any of the restored data to the table.

   You can modify or delete permissions only after the restore operation is completed.

1. In the first step, you create a simple table named myTable that has PITR enabled. The command has been broken up into separate lines for readability.

   aws keyspaces create-table --keyspace-name 'myKeyspace' --table-name 'myTable' 
               --schema-definition 'allColumns=[{name=id,type=int},{name=name,type=text},{name=date,type=timestamp}],partitionKeys=[{name=id}]' 
               --point-in-time-recovery 'status=ENABLED'

2. Confirm the properties of the new table and review the earliestRestorableTimestamp for PITR.

   aws keyspaces get-table --keyspace-name 'myKeyspace' --table-name 'myTable'

   The output of this command returns the following.

   {
       "keyspaceName": "myKeyspace",
       "tableName": "myTable",
       "resourceArn": "arn:aws:cassandra:us-east-1:111222333444:/keyspace/myKeyspace/table/myTable",
       "creationTimestamp": "2022-06-20T14:34:57.049000-07:00",
       "status": "ACTIVE",
       "schemaDefinition": {
           "allColumns": [
               {
                   "name": "id",
                   "type": "int"
               },
               {
                   "name": "date",
                   "type": "timestamp"
               },
               {
                   "name": "name",
                   "type": "text"
               }
           ],
           "partitionKeys": [
               {
                   "name": "id"
               }
           ],
           "clusteringKeys": [],
           "staticColumns": []
       },
       "capacitySpecification": {
           "throughputMode": "PAY_PER_REQUEST",
           "lastUpdateToPayPerRequestTimestamp": "2022-06-20T14:34:57.049000-07:00"
       },
       "encryptionSpecification": {
           "type": "AWS_OWNED_KMS_KEY"
       },
       "pointInTimeRecovery": {
           "status": "ENABLED",
           "earliestRestorableTimestamp": "2022-06-20T14:35:13.693000-07:00"
       },
       "defaultTimeToLive": 0,
       "comment": {
           "message": ""
       }
   }

   You can restore an active table to any point-in-time between the earliestRestorableTimestamp and the current time in one second intervals. Current time is the default.

3. To restore a table to a point in time, specify a restore_timestamp in ISO 8601 format. You can chose any point in time during the last 35 days in one second intervals. For example, the following command restores the table to the EarliestRestorableDateTime.

   aws keyspaces restore-table --source-keyspace-name 'myKeyspace' --source-table-name 'myTable' --target-keyspace-name 'myKeyspace' --target-table-name 'myTable_restored' --restore-timestamp "2022-06-20 21:35:14.693"

   The output of this command returns the ARN of the restored table.

   {
       "restoredTableARN": "arn:aws:cassandra:us-east-1:111222333444:/keyspace/myKeyspace/table/myTable_restored"
   }

   To restore the table to the current time, you can omit the restore-timestamp.

   aws keyspaces restore-table --source-keyspace-name 'myKeyspace' --source-table-name 'myTable' --target-keyspace-name 'myKeyspace' --target-table-name 'myTable_restored1'"

1. You can restore an active table to a point-in-time between earliest_restorable_timestamp and the current time. Current time is the default.

   To confirm that point-in-time recovery is enabled for the mytable table, query the system_schema_mcs.tables as follows.

   SELECT custom_properties
   FROM system_schema_mcs.tables
   WHERE keyspace_name = 'mykeyspace' AND table_name = 'mytable';

   Point-in-time recovery is enabled as shown in the following sample output.

   custom_properties
   -----------------
   {
     ...,
       "point_in_time_recovery": {
       "earliest_restorable_timestamp":"2020-06-30T19:19:21.175Z"
       "status":"enabled"
     }
   }

2. Restore the table to a point in time, specified by a restore_timestamp in ISO 8601 format. In this case, the mytable table is restored to the current time. You can omit the WITH restore_timestamp = ... clause. Without the clause, the current timestamp is used.

   RESTORE TABLE mykeyspace.mytable_restored
   FROM TABLE mykeyspace.mytable;

   You can also restore to a specific point in time. You can specify any point in time during the last 35 days. For example, the following command restores the table to the EarliestRestorableDateTime.

   RESTORE TABLE mykeyspace.mytable_restored
   FROM TABLE mykeyspace.mytable
   WITH restore_timestamp = '2020-06-30T19:19:21.175Z';

   For a full syntax description, see RESTORE TABLE in the language reference.

1. Delete the table that you created in the previous tutorial.

   aws keyspaces delete-table --keyspace-name 'myKeyspace' --table-name 'myTable'

2. Restore the deleted table to the time of deletion with the following command.

   aws keyspaces restore-table --source-keyspace-name 'myKeyspace' --source-table-name 'myTable' --target-keyspace-name 'myKeyspace' --target-table-name 'myTable_restored2'

   The output of this command returns the ARN of the restored table.

   {
       "restoredTableARN": "arn:aws:cassandra:us-east-1:111222333444:/keyspace/myKeyspace/table/myTable_restored2"
   }

1. To confirm that point-in-time recovery is enabled for a deleted table, query the system table. Only tables with point-in-time recovery enabled are shown.

   SELECT custom_properties
   FROM system_schema_mcs.tables_history 
   WHERE keyspace_name = 'mykeyspace' AND table_name = 'my_table';

   The query shows the following output.

   custom_properties
   ------------------
   {
       ...,
      "point_in_time_recovery":{
         "restorable_until_time":"2020-08-04T00:48:58.381Z",
         "status":"enabled"
      }
   }

2. Restore the table to the time of deletion with the following sample statement.

   RESTORE TABLE mykeyspace.mytable_restored
   FROM TABLE mykeyspace.mytable;