Incremental Materialized View

What is Incremental Materialized View?

We learned how to create a Materialized View in the Materialized View (data materialization) chapter, 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:

1. The user needs to specify the source Table, Incremental Key and Unique Key

   1. Incremental Key (Required): This column's maximum value will record in each update.
   2. 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.

2. 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 use id as a unique key and updated_at as an incremental key.

3. In the second update, will be an incremental update.

   In the last update, we recorded that the maximum value of update_at was 1/5; we obtained > 1/5 data from the source and used id to merge and update the old data; if there is no such id it will view it as new append rows.

Caution

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 View
• Select a source table: Select a source Table from the drop-down
• Columns: Select the required columns
• Select 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 Accept TINYINT, 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.