All Products
Search
Document Center

Clear historical data

Last Updated: Nov 03, 2021

To prevent the accumulation of historical data from affecting the stability of an online environment, Data Management (DMS) provides the historical data cleaning feature for you. You can use this feature to regularly clear historical data.

Prerequisites

A MySQL database is used.

Procedure

  1. Log on to the DMS console.

  2. In the top navigation bar, click the Database Development tab. Then, choose Data Change > History Data Clean in the left-side navigation pane.

    Note

    If you are using the previous version of the DMS console, move the pointer over the More icon in the top navigation bar and choose Data Plans > Change > History Data Clean.

  3. On the Data Change Ticket Application page, set the parameters that are described in the following table and click Submit.

    Parameter

    Description

    Database

    The database on which you have the change permissions. You cannot submit a data change ticket if you have only the permissions to query databases or change data in tables. For more information, see View owned permissions.

    Reason Category

    The reason for the data change. This helps you find the ticket in subsequent operations.

    Business Background

    The purpose or objective of the data change. This reduces unnecessary communication.

    Deletion Settings

    The settings related to data deletion. Set the Table Name, Time Field, Time accuracy, Retention Period, and Filter Condition parameters. DMS generates an SQL script to clear historical data based on the preceding settings.

    Note

    To specify a logical table, you must enter its logical name rather than its physical name.

    For example, if the table name is api_call_record_11, the time field is gmt_create, and the retention period is seven days, DMS generates the following SQL statement: DELETE FROM `api_call_record_11` WHERE `gmt_create` < SUBDATE(CURDATE(),INTERVAL 7 DAY);.

    Schedule

    The time at which DMS begins to run the task. In DMS, if you use a scheduled task to clear data from a table, the task scans the full table and deletes data in different batches based on the primary key or non-null unique index of the table. We recommend that you set a point in time during off-peak hours to run the task and set a reasonable interval at which the scheduled task is run.

    Note

    The minimum time interval is 1 hour. By default, the task is run at 02:00 every day.

    Policy Configuration

    The policy for running the task. You can specify an end time for the task, so that the task is automatically suspended after this end time. You can use this policy to prevent the data change from affecting your business during peak hours.

    Change Stakeholder

    Optional. The stakeholders involved in the data change. All specified stakeholders can view the ticket details and assist in the approval process. Irrelevant users other than DMS administrators and database administrators (DBAs) are not allowed to view the ticket details.

    Attachments

    The images or files that are uploaded to add more information about this data change.

  4. After you submit the ticket, wait until DMS verifies that your configurations are valid. After the ticket passes the precheck, you can submit the ticket for approval. If the ticket fails the precheck, modify the ticket as prompted. Then, submit the modified ticket for precheck again.

    Note

    Before you submit the ticket for approval, you can modify the settings and scheduling policy that you have configured. You cannot modify these configurations after you submit the ticket for approval.

  5. After the ticket is approved, DMS automatically generates the scheduled task and sends an email to the ticket owner. You can click View Scheduled Tasks to view the details of the scheduled task. You can also perform the following operations:

    • Suspend the scheduled task.

    • Restart the scheduled task.

      To stop the scheduled task, you need only to close the ticket. After you close the ticket, you cannot restart the scheduled task unless you submit another ticket.

    • Change the ticket owner.

      By default, the owner of a ticket is the user who submits the ticket. Only the ticket owner has the permissions to suspend and restart the scheduled task that corresponds to the ticket. Each time the scheduled task is run, DMS sends an email only to the ticket owner.

  6. After the scheduled task is generated, DMS runs the SQL script that is generated based on your configurations at the specified point in time and intervals. You can view all scheduling information and the details each time the scheduled task is run on the ticket details page.

    Note

    Each time before the scheduled task is run, DMS checks whether the previous scheduled task is complete. If the previous scheduled task is still in progress, DMS does not run the SQL script again. Therefore, you must set a reasonable interval at which the scheduled task is run.