Materialized views over external tables in database systems

Patent metadata

Field	Value
Publication number	US-11507571-B2
Application number	US-201916385720-A
Country	US
Kind code	B2
Filing date	Apr 16, 2019
Priority date	Apr 16, 2019
Publication date	Nov 22, 2022
Grant date	Nov 22, 2022

Abstract

Systems, methods, and devices for generating a materialized view over an external table. A method includes connecting a database platform to an external table such that the database platform has read access for the external table and does not have write access for the external table. The method includes generating, by the database platform, a materialized view over the external table. The method includes receiving a notification that a modification has been made to the external table, the modification comprising one or more of an addition of a file, a deletion of a file, or an update to a file in a source location for the external table. The method includes, in response to the external table being modified, refreshing the materialized view such that the materialized view comprises an accurate representation of the external table.

First claim

What is claimed is:

1. A system comprising:
at least one processor;
a memory device including instructions, which when executed by the at least one processor, cause the at least one processor to perform operations comprising:
connecting a database platform to an external table such that the database platform has read access for the external table and does not have write access for the external table;
generating a materialized view over the external table;
receiving a notification that a modification has been made to the external table, the modification comprising inserting at least one row of new data to the external table, the at least one row corresponding to a first micro-partition that includes a first portion of data from the external table prior to the inserting, the notification being received after inserting at least one row of new data to the external table has been completed and based at least in part on scanning the external table to detect at least one change to the external table;
in response to the notification indicating the modification to the external table, generating a new micro-partition different from the first micro-partition, the new micro-partition including the inserted at least one row of new data and the first portion of data from the external table, the new micro-partition comprising immutable one or more new contiguous units of storage;
generating a refreshed materialized view in response to the external table being modified such that the refreshed materialized view comprises representation of the external table after the modification has been made; and
providing the refreshed materialized view to a client device in response to a request from the client device.

2. The system of claim 1 , wherein the new micro-partition is stored in persistent storage provided by the database platform.

3. The system of claim 1 , wherein the memory device includes further instructions, which when executed by the at least one processor, cause the at least one processor to perform further operations comprising:
determining content for the materialized view, wherein the determining the content comprises receiving an indication to generate the materialized view with the content.

4. The system of claim 2 , wherein the external table is based on a source location in a data storage platform, wherein the data storage platform is separate from the database platform and receiving the notification comprises:
querying the data storage platform to determine whether any modifications have been made to the source location.

5. The system of claim 4 , wherein the receiving the notification comprises:
receiving a notification from the data storage platform that a modification has been made to the source location.

6. The system of claim 2 , wherein the receiving the notification comprises:
receiving a notification from a respective client device associated with a source directory, the notification indicating that the modification has been made to a source location.

7. The system of claim 1 , wherein generating the refreshed materialized view comprises:
generating a new materialized view and removing an original materialized view that is stale with respect to the external table.

8. The system of claim 1 , wherein the memory device includes further instructions, which when executed by the at least one processor, cause the at least one processor to perform further operations comprising:
receiving a query;
determining one or more partitions in the external table that are necessary to respond to the query by reading metadata for the external table; and
determining whether part or all of the query can be satisfied by one or more materialized views.

9. The system of claim 8 , wherein the memory device includes further instructions, which when executed by the at least one processor, cause the at least one processor to perform further operations comprising:
generating a query plan for responding to the query, the query plan comprising a plurality of discrete subtasks; and
assigning the plurality of discrete subtasks to one or more nodes of an execution platform,
wherein, in response to there being a matching materialized view that matches part or all of the query, at least one of the plurality of discrete subtasks comprises reading the matching materialized view.

10. The system of claim 9 , wherein, in response to there being no matching materialized view that matches part or all of the query, at least one of the plurality of discrete subtasks comprises reading the external table.

11. The system of claim 9 , further comprising determining whether the matching materialized view is stale with respect to the external table.

12. The system of claim 1 , further comprising generating metadata for the materialized view, the metadata comprising:
an identification of one or more partitions of the external table that are materialized in the materialized view.

13. The system of claim 1 , further comprising generating metadata for the materialized view, the metadata comprising:
a minimum/maximum value pair for each of one or more partitions that are materialized in the materialized view.

14. The system of claim 1 , further comprising generating metadata for the materialized view, the metadata comprising:
a timestamp for a most recent refresh of the materialized view.

15. The system of claim 14 , wherein the memory device includes further instructions, which when executed by the at least one processor, cause the at least one processor to perform further operations comprising:
reading the timestamp for the most recent refresh of the materialized view; and
determining whether the materialized view is stale with respect to the external table based on the timestamp for the most recent refresh of the materialized view.

16. A method comprising:
connecting a database platform to an external table such that the database platform has read access for the external table and does not have write access for the external table;
generating, by the database platform, a materialized view over the external table;
receiving a notification that a modification has been made to the external table, the modification comprising inserting at least one row of new data to the external table, the at least one row corresponding to a first micro-partition that includes a first portion of data from the external table prior to the inserting, the notification being received after inserting at least one row of new data to the external table has been completed and based at least in part on scanning the external table to detect at least one change to the external table;
in response to the notification indicating the modification to the external table, generating a new micro-partition different from the first micro-partition, the new micro-partition including the inserted at least one row of new data and the first portion of data from the external table, the new micro-partition comprising immutable one or more new contiguous units of storage;
in response to the external table being modified, generating a refreshed materialized view such that the refreshed materialized view comprises a representation of the external table after the modification has been made; and
providing the refreshed materialized view to a client device in response to a request from the client device.

17. The method of claim 16 , wherein the external table is based on a source location in a data storage platform, wherein the data storage platform is separate from the database platform.

18. The method of claim 16 ,

Assignees

• Snowflake Inc

Inventors

• Muralidhar Subramanian
• Dageville Benoit
• Cruanes Thierry
• Shingte Nileema
• Shah Saurin
• Grabs Torsten
• Cseri Istvan

Classifications

• G06F16/256 Primary

  in federated or virtual databases · CPC title

• G06F3/0605

  by facilitating the interaction with a user or administrator · CPC title

• G06F16/254

  Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses · CPC title

• G06F3/067

  Distributed or networked storage systems, e.g. storage area networks [SAN], network attached storage [NAS] · CPC title

• G06F16/2393 Primary

  Updating materialised views · CPC title