Incremental Modification Example

Background

The data of the past four days from a certain table in the database has been recalibrated, and you need to synchronize the data to FineBI.

The data volume of the table is large, and it will take a long time to perform the Full-Volume Update function. However, you can replace only the data of the past four days in the table.

Solution

Perform the Incremental Deletion function to delete the data of the past four days in FineBI.

Perform the Incremental Increase function to extract the latest data of the past four days from the database. Add the extracted data to FineBI.

This document takes MySQL database as an example. The content below will describe how to modify the data of the past four days in Customer_Registration_ Information_Table, as shown in the following figure.

Deleting Data of the Past Four Days

The current date is 2024/03/29. The data from 2024/03/26 to 2023/03/29 needs to be deleted.

1. Select Customer_Registration_Information_Table on the Public Data page in FineBI, click Update Information, and click Single Table Update.

2. Go to the Customer_Registration_Information_Table Update Setting page, as shown in the following figure.

Extract the data from 03/26 to 03/29 in FineBI.

SELECT Phone_Number FROM Customer_Registration_Information_Table WHERE DATE_SUB(CURDATE(), INTERVAL 3 DAY) <= Registration_Date

Notes:

The SQL statement entered here should be consistent with the SQL statement of your own database. In this example, the SQL statement is consistent with that of MySQL database.

SELECT * can be used in Incremental Increase, but not in Incremental Deletion.

You need to select the field with the highest distinctiveness. In this example, Phone_Number is an ID-type field with very high distinctiveness, so the statement SELECT Phone_Number is used here.

If there is no unique field with distinctive in the table, you can choose two fields with relatively high distinctiveness as the basis for the statement SELECT.

3. Click Preview to view if the extracted data meets the requirements. 

Synchronizing the Latest Data of the Past Four Days to FineBI

1. Select Incremental Increase to extract the data between 03/26 and 03/29 from the database, as shown in the following figure.

2. Select Preview to check if the data extracted from the database is accurate, as shown in the following figure.

The latest data of the past four days has included the data between 03/26 and 03/29, so the data extracted from the database is confirmed to be accurate.

Incremental Update Now

Select Incremental Update, and the system will first automatically perform Incremental Deletion and then perform Incremental Increase. 

Select Data Preview to view that the data has changed.

If the data volume remains unchanged after you have performed Incremental Modification, ''0 Record(s) of Data Added'' will be displayed on the Update Information page, and this condition indicates that the Incremental Update function you have performed is successful.