Overview
Background
There is a table in the database, and it will receive new data every day. The data volume of the table is large, and it will take a long time if you update the entire table every day.
Solution
In this case, you can use the Incremental Update function to only update the newly-added data to FineBI every day. You do not need to update the entire data table, saving the time and resources you will consume on the update.
The table with a timestamp field is more suitable for the Incremental Update function, because the update time of the data in the database is often compared with the system update time during Incremental Increase.
Procedure
Add Customer_Registration_Information_Table from MySQL database to FineBI. For details, you can refer to Adding Database Tables.
There is a timestamp field Registration_Date in the table, as shown in the following figure.
Setting Incremental Increase
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 Single Table Update setting page, select Incremental Increase, and enter the SQL statement for data extraction, as shown in the following figure.
The entered SQL statement needs to be consistent with the SQL statement of your own database. In this example, the SQL statement is consistent with that of MySQL database.
SELECT * FROM Customer_Registration_Information_Table WHERE Registration_Date > str_to_date('Last Update Time','%Y-%m-%d %H:%i:%s')
After you enter the SQL statement, the data with Registration_Date later than Last Update Time will be extracted from the database, and the system will add the extracted data to FineBI to achieve Incremental Update. The str_to_date function is used to convert the type of Last update Time from string to date. If the database you use is not MySQL, you need to use the accurate functions that are adapted for your database.
Note:System Parameter | Description |
Last Update Time | Indicates the start time of the last update. The parameter of Last Update Time is a text type of year, month, day, hour, minute, and second (e.g. 2017/12/22 12:00:00). The parameter of Last Update Time needs to be converted to the time type through functions in order to be compared with other fields of time type. Last Update Time can be added to your SQL statement only by clicking the Last Update Time tab right next to Parameter. Last Update Time cannot be copied and pasted to your SQL statement. If you do not want to click the Last Update Time tab to add it to your SQL statement, you can enter ${_last_update_time_} instead. The SQL statement in this example can be written as SELECT * FROM Customer_Registration_Information_Table where Registration_Date > str_to_date('${_last_update_time_}','%Y-%m-%d %H:%i:%s'). |
Current Update Time | Current Update Time indicates the start time of this update. The parameter of Current Update Time is a text type, and you can enter year, month, day, hour, minute, and second (e.g. 2019/12/22 12:00:00). The parameter of Current Update Time needs to be converted to the time type through functions in order to be compared with other fields of time type. Current Update Time can be added to your SQL statement only by clicking the Current Update Time tab right next to Parameter. Current Update Time cannot be copied and pasted to your SQL statement. If you do not want to click the Current Update Time tab to add it to your SQL statement, you can enter ${_current_update_time_} instead. |
3. Click Preview to view the data extracted from the database through the SQL statement, as shown in the following figure.
If the preview fails, you can replace the system parameter in the SQL statement with a fixed date value and then test the changed statement in your own database to see if it is correct.
4. Select Incremental Update from the drop-down list, click OK, and you can perform Incremental Update to the data immediately.
Effect Display
After the update is completed, you can check the update information of Customer_Registration_Information_Table on the Update Information page. After you have performed the Incremental Update function, two records of data will be added to the table, as shown in the following figure.
You can also view the data added through Incremental Update on the Data Preview page, as shown in the following figure.
Setting Scheduled Incremental Update
The following content is to help you configure a scheduled incremental update.
Go to the Update Setting page, select Schedule Setting, select Incremental Update from the drop-down list of Update Method, select Execute Repeatedly from the drop-down list of Execution Frequency, and set the update frequency to once a day.
Details about how to configure a scheduled update, you can refer to Scheduled Update.
Previous：Incremental Update Overview
