INCREMENTAL REFRESH OF A MATERIALIZED VIEW

• US 20200133937A1
• Filed: 10/24/2019
• Published: 04/30/2020
• Est. Priority Date: 10/26/2018
• Status: Active Grant

• Current Assignee: Snowflake, Inc.
• Original Assignee: Snowflake, Inc.
• Inventors: Cruanes, Thierry, Dageville, Benoit, Rajaperumal, Prasanna, Yan, Jiaqi
• Granted Patent: US 11,030,186 B2
• Time in Patent Office: Days
• CPC Class Codes: G06F 16/2282 Tablespace storage structures; Management thereof
• CPC Class Codes: G06F 16/2365 Ensuring data consistency and integrity
• CPC Class Codes: G06F 16/2393 Updating materialised views
• CPC Class Codes: G06F 16/24 Querying
• CPC Class Codes: G06F 16/278 Data partitioning, e.g. horizontal or vertical partitioning

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.

Claims

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 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.

22. The processor of claim 21, 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.

23. The processor of claim 21, 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.

24. The processor of claim 23, wherein the instructions further comprise 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.

25. The processor of claim 21, wherein the instructions further comprise 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.