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.
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.
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.
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.
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.
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.
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.
滑鼠選中內容，快速回饋問題
滑鼠選中存在疑惑的內容，即可快速回饋問題，我們將會跟進處理。
不再提示
10s後關閉
Submitted successfully
Network busy