Automatic Cleanup of FineDataLink Running Records

Application Scenario

Prolonged operation of the FineDataLink project will lead to data accumulation in the fine_dp_work_exec_record table, which logs the execution history of scheduled tasks. The excessive amount of data may lead to load time exceeding 10 seconds when you access O&M Center > Scheduled Task > Running Record.

You may want to regularly clean up data in the fine_dp_work_exec_record table.

Note:

Starting from V4.1.7.3,  the fine_dp_work_exec_record table has been migrated to LogDB, with the table name unchanged. Subsequent data will be written to the migrated table. After you upgrade the project version to V4.1.7.3, historical running records will be migrated to LogDB, and this data can be cleaned up using the Log Cleanup function.

Implementation Method

Implement a scheduled cleanup process using FineDataLink to delete scheduled task execution records older than 30 days.

Precautions

Note:

The steps in the "Procedure" section of this document apply to FineDataLink of versions before V4.1.

The steps in the "Procedure" section of this document apply to FineDataLink of versions before V4.1.

For FineDataLink of V4.1 and later versions, the full execution records of scheduled tasks are stored in the fine_dp_work_exec_record table, while the most recent record is stored in the fine_dp_work_last_record table. Both tables need to be cleaned up simultaneously. For details about the cleanup steps, see the "Procedure" section of this document.

The fine_dp_work_exec_record table resides in the FineDB database. In the examples provided in this document, the FineDB database has been migrated to a MySQL database. Therefore, the SQL statements used in the examples of this document follow the syntax of MySQL databases.

You should adjust the SQL statements according to the actual condition.

Obtaining Dates from 30 Days Ago to Create a Parameter

1. Create a scheduled task.

2. Drag in a Parameter Assignment node, and configure it to output a field named date, which holds the date from 30 days ago, as shown in the following figure.

SELECT DATE_SUB(NOW(), INTERVAL 30 DAY) AS date

3. Output date as a parameter. Set Debug Value, where you can copy and paste a value from the values on the right after clicking Parameter Preview. The default value is only for preview purposes in downstream nodes and will not be used during actual execution, as shown in the following figure.

Note:

For details about the Parameter Assignment node, see Parameter Assignment.

Deleting Running Records from 30 Days Ago

1. Drag in an SQL Script node and configure it to delete the execution records from 30 days ago for scheduled tasks, as shown in the following figure.

delete from `demo1`.`fine_dp_work_exec_record` where FROM_UNIXTIME(startTime/1000,'%Y-%m-%d %H:%i:%s')<='${date}'

Running the Task

1. Click Run in the upper right corner to execute the task. The running result in the Log upon successful execution is shown in the following figure.

2. Verify whether data older than 30 days has been deleted using an SQL statement.

For example, if the date from 30 days ago is 2023-07-17 19:34:37, its millisecond-level timestamp is 1689593677000. Check and confirm that the data where startTime is less than 1689593677000 is empty.