Incremental refresh of a materialized view

Patent metadata

Field	Value
Publication number	US-2020133937-A1
Application number	US-201916662645-A
Country	US
Kind code	A1
Filing date	Oct 24, 2019
Priority date	Oct 26, 2018
Publication date	Apr 30, 2020
Grant date	—

Abstract

Systems, methods, and devices for incrementally refreshing a materialized view are disclosed. A method includes generating a materialized view based on a source table. The method includes merging the source table and the materialized view to generate a merged table to identify whether an update has been executed on the source table that is not reflected in the materialized view. The method includes, in response to detecting an update made to the source table that is not reflected in the materialized view, applying the update to the materialized view.

First claim

What is claimed is:

1 . A system comprising:
a compute service manager for managing internal operations of a cloud-based database platform; a plurality of shared storage devices collectively storing database data, wherein the plurality of shared storage devices is independent of the compute service manager; and an execution platform comprising a plurality of execution nodes, wherein the execution platform independent is independent of the plurality of shared storage devices and the compute service manager; wherein the compute service manager is configured to:
generate a materialized view based on a source table;
merge the source table and the materialized view to generate a merged table to identify whether an update has been executed on the source table that is not reflected in the materialized view; and
in response to detecting an update made to the source table that is not reflected in the materialized view, assign tasks to one or more execution nodes of the execution platform to apply the update to the materialized view.

2 . The system of claim 1 , wherein the update comprises one or more of:
one or more rows inserted into the source table; one or more rows deleted from the source table; or one or more rows updated in the source table.

3 . The system of claim 1 , wherein the compute service manager is configured to assign tasks for applying the update to the materialized view by:
in response to determining a new row has been inserted into the source table, assign a task to refresh the materialized that comprises inserting the new row into the materialized view; and in response to determining a row has been deleted from the source table, assign a task to compact the materialized view that comprises deleting the row from the materialized view.

4 . The system of claim 3 , wherein the compute service manager is configured to prioritize inserting the new row into the materialized view over deleting the row from the materialized view.

5 . The system of claim 3 , wherein the compute service manager is configured to assign a task to compact the materialized view only after a plurality of rows have been deleted from the source table such that a quantity of the plurality of rows exceeds a predetermined threshold number of deleted rows.

6 . The system of claim 1 , wherein the source table is organized into a plurality of micro-partitions, and wherein the update comprises one or more of a new micro-partition being added to the source table or a micro-partition being deleted from the source table.

7 . The system of claim 1 , wherein the compute service manager is further configured to scan the merged table to detect one or more modified micro-partitions in the source table by:
identifying whether a new micro-partition has been inserted into the source table that is not present in the materialized view; or identifying an absence of a deleted micro-partition removed from the source table that is still present in the materialized view.

8 . The system of claim 1 , wherein the compute service manager is further configured to redirect queries away from the materialized view and to the source table while the update is being applied to the materialized view.

9 . The system of claim 1 , wherein the compute service manager is further configured to generate a source table log comprising:
a log of one or more rows inserted into the source table since a last refresh of the materialized view; and a log of one or more rows deleted from the source table since a last compaction of the materialized view.

10 . The system of claim 1 , wherein the compute service manager is further configured to generate cost statistics for the materialized view comprising one or more of:
a number of micro-partitions on the source table that have been materialized; a total number of partitions on the source table; a total number of partitions of the materialized view; a number of times a query is accessed on the materialized view; or a number of times a query is accessed on the source table.

11 . A method comprising:
generating a materialized view based on a source table; merging the source table and the materialized view to generate a merged table to identify whether an update has been executed on the source table that is not reflected in the materialized view; and in response to detecting an update made to the source table that is not reflected in the materialized view, applying the update to the materialized view.

12 . The method of claim 11 , wherein the update comprises one or more of:
one or more rows inserted into the source table; one or more rows deleted from the source table; or one or more rows updated in the source table.

13 . The method of claim 11 , wherein applying the update to the materialized view comprises:
in response to determining a new row has been inserted into the source table, refreshing the materialized view by inserting the new row into the materialized view; and in response to determining a row has been deleted from the source table, compacting the materialized view by deleting the row from the materialized view.

14 . The method of claim 13 , wherein refreshing the materialized view by inserting the new row into the materialized view is prioritized over compacting the materialized view by deleting the row from the materialized view.

15 . The method of claim 13 , wherein compacting the materialized view occurs only after detecting a plurality of rows deleted from the source table such that a quantity of the plurality of rows exceeds a predetermined threshold number of deleted rows.

16 . The method of claim 11 , wherein the source table is organized into a plurality of micro-partitions, and wherein the update comprises one or more of a new micro-partition being added to the source table or a micro-partition being deleted from the source table.

17 . The method of claim 11 , further comprising scanning the merged table to detect one or more modified micro-partitions in the source table by:
identifying whether a new micro-partition has been inserted into the source table that is not present in the materialized view; or identifying an absence of a deleted micro-partition removed from the source table that is still present in the materialized view.

18 . The method of claim 11 , further comprising redirecting queries away from the materialized view and to the source table while the update is being applied to the materialized view.

19 . The method of claim 11 , further comprising generating a source table log comprising:
a log of one or more rows inserted into the source table since a last refresh of the materialized view; and a log of one or more rows deleted from the source table since a last compaction of the materialized view.

20 . The method of claim 11 , further comprising generating cost statistics for the materialized view comprising one or more of:
a number of micro-partitions on the source table that have been materialized; a total number of partitions on the source table; a total number of partitions of the materialized view; a number of times a query is accessed on the materialized view; or a number of times a query is accessed on the source table.

21 . A processor that is configurable to execute instructions stored in non-transitory computer readable storage media, the instructions comprising:
generating a materialized view based on a source table; merging the source table and the materialized view to genera

Assignees

• Snowflake Inc

Inventors

• Cruanes Thierry
• Dageville Benoit
• Rajaperumal Prasanna
• Yan Jiaqi

Classifications

• G06F16/2393 Primary

  Updating materialised views · CPC title

• G06F16/24

  Querying · CPC title

• G06F16/278

  Data partitioning, e.g. horizontal or vertical partitioning · CPC title

• G06F16/2365 Primary

  Ensuring data consistency and integrity · CPC title

• G06F16/2282

  Tablespace storage structures; Management thereof · CPC title