Archive and clean data

Use Archive & Clean to move historical data to another database, clean stale rows from the source, and reduce storage pressure on production systems. Run the task once or schedule recurring cleanup.

Background

As online systems accumulate data, writes, updates, and queries can slow down. Older records may no longer be queried often, but they still consume storage in the operational database.

NineData lets you archive or clean data that matches specific time-based conditions so the source database stays smaller and easier to operate.

Before you begin

• You have already created or joined an organization, and the organization has subscribed to DevOps Enterprise. Please ensure that your annual or monthly subscription is still active. For more information, please refer to Manage Organizations.
• Your current account has been switched to the target organization. For more information, please refer to Switching to an Organization.

• The table to be archived must have a timestamp field.
• The target database must be MySQL, PostgreSQL, or MariaDB.

Create an archive and clean task

1. Sign in to the NineData Console.

2. Go to DevOps > Archive & Clean.

3. On the Archive & Clean page, click Create Archive & Clean.

4. On the Create Archive & Clean page, configure the task.

   Parameter	Description
   Name	Enter a meaningful task name for later search and management. Up to 64 characters are supported.
   Note (Optional)	Enter remarks, such as a description of the purpose of the data archiving task.
   Archive Policy	Select the strategy for the data archiving task. Choose either Only Archiving or Archiving + Cleansing. Supported options: Only Archiving: Archives data from the source to the destination, storing the required data from the source into the destination. Archiving + Cleansing: Archives data from the source to the destination, and then deletes the archived data from the source. Only Cleansing: Deletes data from the destination without archiving.
   Source	The data source and schema where the data to be archived is located.
   Target	The data source and schema where archived data is stored. Not visible when Only Cleansing is selected.
   Archiving Frequency	Select the execution frequency of the task. Once-run: Executes only once. Recurring: Creates a periodic task that runs on the selected schedule.
   Time Zone	Select the timezone of your target location.
   Periodic	Required for periodic tasks. Choose whether the task runs on specific days of the week or month. Select Week or Month to execute the task on specific days of the week or month. Check Every day to execute the task every day.
   Launch Time	Required for periodic tasks. Select the time of day when the periodic task runs during the selected cycle. Click Now to set the current time as the task execution time.
   After how many times of cleaning, execute Optimize once	Optional only when the task includes a cleansing operation. After cleanup, the source database does not release storage space by itself. Configure this option to periodically run an Optimize operation and reclaim table space. Choose how many times to execute the Optimize operation after performing data cleanup.
   Execution time limit (1-24 HR), after which the task will be automatically terminated to avoid affecting the business	Limits how long the task can run, in hours. For example, start the task at 00:00 during off-peak hours and set the execution duration to 6 hours. If the task is still running at 06:00, NineData stops it to reduce business impact.
   Data Conflict Strategy (MySQL only)	Select how NineData handles conflicts on the target during archiving: Ignored: Ignore the current operation causing the conflict and continue to the next operation. Replace: Overwrite the target data.

5. Click Next to go to the Select Objects page to configure the data to be archived.

   Parameter	Description
   System Paramter	Defines system variables. The $bizdate variable fetches the current system time. Customize its time format as required. For supported time formats, see Appendix. Use this feature to write new table names or WHERE conditions in data archiving and cleanup tasks. For example, if you set System Paramter to yyyyMMdd, name a new target table new_table_${bizdate}. If the current date is May 10, 2024, the table name becomes new_table_20240510.
   Source Table	Select the source table that contains the data to archive.
   Target Table	Select the target table where archived data is stored. The target table structure must match the source table. If the structures are different, manually map table names and column names through Mapping & Filtering. Click the icon to create a new table in the target database. Manual table and column mapping is not required because NineData creates the new table based on the source table structure. When you specify the table name, use the defined System Paramter, for example, new_table_${bizdate}.
   Time	Select a time field to determine which data to archive. This field typically reflects when the row was generated, such as the order creation time in your order table.
   Column Setting	If the data type of the selected **Time** is numeric (BIGINT, INT) or string (CHAR, VARCHAR), configure how NineData reads the value: Time Format: Define a custom time format. For supported formats, see Appendix. Timestamp: Select **Timestamp** when the time field stores a timestamp. NineData converts the value based on the selected precision, such as **s**, **ms**, or **μs**.
   Reserve Days	Based on the selected Time, select how many days of data to archive. Valid values are 1 to 3650 days. For example, to archive or clean data older than one year, enter 365.
   Actions	Mapping & Filtering: Manually map table names and column names in the data archiving scenario. For example, storing data from a column in the source table to a specified column in the target table. View SQL: Preview the archiving and cleanup statements that NineData executes under the current configuration. Delete: Delete the current record.
   Add Tables	Adds a new record.

6. Click Create Task. In the Pre-Check node, wait until the task status changes to Pending, then click Submit Approval. In the approval dialog, select the approvers and click OK. The task enters the Submit Approval node. In the Pre-Check node, these actions are also available:

   • Click the icon to refresh the task status.

   • Click Withdraw to withdraw the task or edit and resubmit it.

   • Click Duplicate to copy a new task with the same configuration.

     tip

     • The number of approvers depends on the approval process configured for the data source.

     • If the Not Specifying Approvers feature is enabled in the current approval process, this section displays After submission, all approvers can review. Please check the results in the approval process.. Submit the work order without manually specifying an approver. All users authorized to approve the current work order receive approval notifications and can approve it.

     • If the data source does not use an approval process, the task skips the Pre-Check and Submit Approval nodes, proceeds to the Execute Task node, and changes the task status to Approved. Proceed directly to Step 8.

7. When the task status changes to Pending Approval, these actions are available before approval:

   • Click the icon to refresh the task status.
   • Click Withdraw to withdraw the task or edit and resubmit it.
   • Click Transfer to change the approver of the task.
   • Click View Objects in the page to view the execution objects of this task.

8. When the task status changes to Approved, the Executor clicks Execute, chooses Execution Method, and clicks OK.

   • Execute Now: Execute the task immediately.
   • Scheduled Execution (only for one-time tasks): Choose a time to execute the task.

9. Wait for the task status to change to Success.

Create a cleanup-only task

1. Sign in to the NineData Console.

2. Go to DevOps > Archive & Clean.

3. On the Archive & Clean page, click Create Archive & Clean.

4. On the Create Archive & Clean page, configure the task.

   Parameter	Description
   Name	Enter a meaningful task name for later search and management. Up to 64 characters are supported.
   Note (Optional)	Enter remarks, such as a description of the purpose of this task.
   Archive Policy	Select the policy of the Archive & Clean task. Choose Only Cleansing here. Supported options: Only Archiving: Archive the target data, copying the data that needs to be archived from the source database to the target database. Archiving + Cleansing: Archive the target data first, then delete the archived data from the source database. Only Cleansing: This operation only deletes the target data and does not perform archiving.
   Source	The data source and database where the data to be cleaned up is located.
   Archiving Frequency	Select the execution frequency of the task. Once-run: Execute only once. Recurring: Create a periodic task that runs on the selected schedule.
   Time Zone	Select the time zone of your target region.
   Periodic	Select for periodic tasks, choose Week or Month to execute the task on the specified day of the week or month. Check Every day to execute daily.
   Launch Time	For periodic tasks, select the time of day when the task runs during the selected period. Click Now to set the current time as the task execution time.

5. Click Next to go to the Select Objects page to configure the data to be cleaned up.

   Parameter	Description
   Source Table	Select the table where the data to be cleaned up is located in the source table.
   Time	Select a timestamp field as the basis for determining whether data needs to be cleaned up. This field usually reflects the time when the data in the row was generated. For example, the order creation time field in your order table.
   Reserve Days	Based on the Time selected earlier, select how many days of data to clean up. Valid values are 1 to 3650 days. For example, to clean up data older than one year, enter 365.
   Condition (Optional)	Set filter conditions. Only data that matches the filter is cleaned up. If no filter is set, all data that matches the retention condition is cleaned up. For example, in the test table employees, the filter condition emp_no>=10005 keeps rows whose emp_no is less than 10005.
   "Actions"	Delete: Delete the current record.
   Add Tables	Add a new record.

6. Click Create Task. In the Pre-Check node, wait until the task status changes to Pending, then click Submit Approval. In the approval dialog, select the approvers and click OK. The task enters the Submit Approval node. In the Pre-Check node, these actions are also available:

   • Click the icon to refresh the task status.

   • Click Withdraw to withdraw the task or edit and resubmit it.

   • Click Duplicate to copy a new task with the same configuration.

     tip

     • The number of approvers depends on the approval process configured for the data source.

     • If the Not Specifying Approvers feature is enabled in the current approval process, this section displays After submission, all approvers can review. Please check the results in the approval process.. Submit the work order without manually specifying an approver. All users authorized to approve the current work order receive approval notifications and can approve it.

     • If the data source does not use an approval process, the task skips the Pre-Check and Submit Approval nodes, proceeds to the Execute Task node, and changes the task status to Approved. Proceed directly to Step 8.

7. When the task status changes to Pending Approval, these actions are available before approval:

   • Click the icon to refresh the task status.
   • Click Withdraw to withdraw the task or edit and resubmit it.
   • Click Transfer to change the approver of the task.
   • Click View Objects in the page to view the execution objects of this task.

8. When the task status changes to Approved, the Executor clicks Execute, chooses Execution Method, and clicks OK.

   • Execute Now: Execute the task immediately.
   • Scheduled Execution (only for one-time tasks): Choose a time to execute the task.

9. Wait for the task status to change to Success.

Approve a task

Before you begin

• The current task status is Pending Approval.
• You are either Administrator or the Approver for the current task.

Procedure

1. Sign in to the NineData Console.

2. Go to DevOps > Archive & Clean.

3. On the Archive & Clean page, find the target task and click the task ID or Details in the Actions column.

   tip

   Alternatively, hover over the icon at the top of the page and click the target task on the Todo tab.

4. On the Details page, review the approval request, and choose Transfer, Approved, or Rejected based on your decision.

   Operation	Description
   Transfer	Forward the task to another user for approval.
   Approved	Approve the request. After you click Approved, enter Reason if needed, and click OK.
   Rejected	Reject the request. After you click Rejected, enter Reason for rejection, and click OK.

Appendix: Explanation of Time Format

Category	Time Field	Description
Year	yyyy or y	Four-digit current year, e.g., 2024.
	yy	Two-digit current year, e.g., 24.
Month	M	Current month, e.g., 8.
	MM	Current month with leading zero if necessary, e.g., 08.
	MMM	Localized abbreviated month label, e.g., Aug when the console language is English.
	MMMM	Localized full month label, e.g., August when the console language is English.
Day	d	Day of the month, e.g., 1.
	dd	Day of the month with leading zero if necessary, e.g., 01.
	ddd	Day of the year, e.g., 214.
	e	Day of the week, e.g., 4.
Week	w	Week of the year, e.g., 31.
Time Period	a	Localized time period marker, e.g., AM or PM when the console language is English.
Time	h	Hour of the day, e.g., 9.
	hh	Hour of the day with leading zero if necessary, e.g., 09.
	m	Minute, e.g., 5.
	mm	Minute with leading zero if necessary, e.g., 05.
	s	Second, e.g., 8.
	ss	Second with leading zero if necessary, e.g., 08.
Time Zone	z	Time zone offset with a colon, e.g., +08:00.
	zz	Time zone offset without a colon, e.g., +0800.

Combine these time field formats freely.

Example: yyyyMMdd ahh:mm:ss displays the current time as 20240801 PM14:30:00 when the console language is English.

Result

After the task succeeds, archived data is written to the configured target table, cleanup-only tasks remove the matching source rows, and the task detail page shows a success status.

Next steps

• Review task details to confirm the archived or cleaned objects.
• For scheduled tasks, monitor future runs from the Archive & Clean task list.
• If the source database still uses more space than expected after cleanup, review the Optimize strategy configured for the task.