Incremental Materialized View
What is Incremental Materialized View?
We learned how to create a Materialized View, but when the Materialized View is updated, the entire SQL query will be re-executed. It will retrieve the whole data from the source again. If the amount of data is large, it will cause a significant burden on the system.
The Incremental Materialized View is designed to solve this problem. The operating mechanism is as follows:
The user needs to specify the source Table,
Incremental Key
andUnique Key
Incremental Key
(Required): This column's maximum value will record in each update.Unique Key
(Optional): This field is used for data merging in the subsequent incremental update. If this field is not specified, it will only append new rows without updated rows.
The entire data will be copied when fetching for the first time, like a normal Materialized View. At this time, we will record the maximum value of the
incremental key
field.We grab the
users
table and useid
as a unique key andupdated_at
as an incremental key.In the second update, will be an incremental update.
In the last update, we recorded that the maximum value of
update_at
was1/5
; we obtained> 1/5
data from the source and usedid
to merge and update the old data; if there is no suchid
it will view it as new append rows.
Currently, Canner Enterprise only supports Append and Update updates but does not support Delete updates.
Steps
Step 1: Prepare Tables / Views
It would help if you had Tables or Views ready for Incremental Materialized Views in your workspace.
Select the Tables or Views type in the filter at the top right of Datasets to create an Incremental Materialized View.
After filtering, click the ...
icon in the table to expand the menu, and select the Create an Incremental Materialized View
option.
Step 2: Fill in the information on Incremental Materialized View
Name
: the name of the Materialized ViewSelect a source table
: Select a source Table from the drop-downColumns
: Select the required columnsSelect unique key(s)
: Select the field of the unique key. The data is identified based on this field in the subsequent incremental update. If this field is not specified, it will only append new rows without updated rows. Update and append only the new data inserted into the data source to the Materialized View.Select incremental key
: Select the incremental key field; each update will record the maximum value of this field and obtain the updated data of the fragment for the query that is greater than (>
) the maximum value under the data source, only AcceptTINYINT
,SMALLINT
,INTEGER
,BIGINT
,REAL
,DOUBLE
,DECIMAL
,DATE
,TIME
,TIMESTAMP
and other types.Schedule
: You can schedule hourly, daily, monthly, or customize the schedule according to your needs. For periodic updates, you can set the interval hour (Hourly), daily time (Daily), weekly time (Weekly), or customize the Crontab syntax. If you specify a specific hour in Crontab, please note that the time here is UTC +0 time zone.
Step 3: Build completed
After creating the Incremental Materialized View, set the upper right filter to filter the Materialized View type Dataset.
After filtering, you can see the newly created Incremental Materialized View on the screen. Click the name of this dataset to enter the details page.
You can see the details under the name heading on the details page.