Data sharing and materialized views in multiple tenant database systems

Patent metadata

Field	Value
Publication number	US-11113278-B2
Application number	US-201916428367-A
Country	US
Kind code	B2
Filing date	May 31, 2019
Priority date	May 31, 2019
Publication date	Sep 7, 2021
Grant date	Sep 7, 2021

Abstract

Systems, methods, and devices for generating and updating cross-account materialized views in multiple tenant database systems. A method includes defining a share object in a first account wherein the share object includes data associated with the first account. The method includes granting cross-account access rights to the share object to a second account such that the second account has access to the share object without copying the share object. The method includes generating a materialized view over the share object. The method includes updating the data associated with the first account. The method includes identifying whether the materialized view is stale with respect to the share object by merging the materialized view and the share object.

First claim

What is claimed is:

1. A system comprising:
means for defining a share object in a first account, the share object comprising data associated with the first account;
means for granting cross-account access rights to the share object to a second account such that the second account has access to the share object without copying the share object;
means for generating a materialized view over the share object;
means for updating the data associated with the first account; and
means for identifying whether the materialized view is stale with respect to the share object by merging the materialized view and the share object.

2. The system of claim 1 , wherein the means for identifying whether the materialized view is stale with respect to the share object comprises:
means for identifying whether data in the share object has been modified since a last refresh of the materialized view; and
means for refreshing the materialized view with respect to the share object in response to identifying a modification to the share object since the last refresh of the materialized view.

3. The system of claim 1 , further comprising means for querying the share object, the means for querying comprising:
means for merging the materialized view and the share object; and
means for executing the query based on information in the materialized view and any modifications made to the share object since a last refresh of the materialized view.

4. The system of claim 1 , further comprising:
means for storing the data associated with the first account across one or more of a plurality of shared storage devices;
means for defining an execution platform associated with the first account that has read access and write access for the data associated with the first account; and
means for defining an execution platform associated with the second account that has read access to the share object.

5. The system of claim 4 , wherein:
the execution platform associated with the second account includes the means for generating the materialized view over the share object;
the execution platform associated with the first account includes the means for updating the data associated with the first account; and
the execution platform associated with the second account includes the means for identifying whether the materialized view is stale with respect to the share object.

6. The system of claim 1 , further comprising means for defining a secure view definition for the materialized view, the means for defining the secure view definition comprising one or more of:
means for granting the second account read access and write access to the materialized view;
means for granting the first account read access to the materialized view; or
means for hiding the materialized view from the first account such that the first account does not have visibility into whether the materialized view was generated.

7. The system of claim 1 , further comprising means for defining view privileges for the cross-account access rights to the share object such that an underlying detail of the share object comprises a secure view definition, wherein the underlying detail of the share object comprises one or more of:
a data field in the share object;
a column of data in the share object;
a structural element of an underlying table of the share object; or
a quantity of data in the share object.

8. The system of claim 7 , wherein the means for defining the view privileges for the cross-account access rights to the share object comprises means for hiding the view privileges from the second account and means for making the view privileges visible to the first account.

9. The system of claim 1 , wherein the means for defining the share object comprises one or more of:
means for defining an object name unique to the first account;
means for defining an object role; or
means for generating a reference list comprising a list of one or more accounts that are eligible for receiving cross-account access rights to the share object.

10. The system of claim 1 , further comprising:
means for receiving a request from the second account to generate a materialized view over certain data associated with the first account;
means for identifying whether the certain data is included in the share object;
means for granting the second account authorization to generate the materialized view over the certain data; and
means for providing a notification to the first account indicating that the second account received authorization to generate the materialized view over the certain data.

11. A method comprising:
defining a share object in a first account, the share object comprising data associated with the first account;
granting cross-account access rights to the share object to a second account such that the second account has access to the share object without copying the share object;
generating a materialized view over the share object;
updating the data associated with the first account; and
identifying whether the materialized view is stale with respect to the share object by merging the materialized view and the share object.

12. The method of claim 11 , wherein identifying whether the materialized view is stale with respect to the share object comprises:
identifying whether data in the share object has been modified since a last refresh of the materialized view, wherein the data in the share object may be modified by one or more of an update, a delete, or an insert; and
refreshing the materialized view with respect to the share object in response to identifying a modification to the share object since the last refresh of the materialized view.

13. The method of claim 11 , further comprising querying the share object by:
merging the materialized view and the share object; and
executing the query based on information in the materialized view and any modifications made to the share object since a last refresh of the materialized view.

14. The method of claim 11 , further comprising:
storing the data associated with the first account across one or more of a plurality of shared storage devices;
defining an execution platform associated with the first account that has read access and write access for the data associated with the first account; and
defining an execution platform associated with the second account that has read access to the share object.

15. The method of claim 14 , wherein:
the generating the materialized view over the share object is processed by the execution platform associated with the second account;
the updating the data associated with the first account is processed by the execution platform associated with the first account; and
the identifying whether the materialized view is stale with respect to the share object is processed by the execution platform associated with the second account.

16. The method of claim 11 , further comprising:
defining a secure view definition for the materialized view, wherein defining the secure view definition comprises one or more of:
granting the second account read access and write access to the materialized view;
granting the first account read access to the materialized view; or
hiding the materialized view from the first account such that the first account does not have visibility into whether the materialized view was generated.

17. The method of claim 11 , further comprising:
defining view privileges for the cross-account access rights to the share object such that an underlying detail of the share object comprises a secure view definition

Assignees

• Snowflake Inc

Inventors

• Rajaperumal Prasanna
• Cruanes Thierry
• Lee Allison Waingold
• Demura Igor
• Yan Jiaqi
• Dageville Benoit

Classifications

• G06F16/278

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

• G06F16/256

  in federated or virtual databases · CPC title

• G06F16/24552

  Database cache management · CPC title

• G06F16/24539 Primary

  using cached or materialised query results · CPC title

• G06F16/2393 Primary

  Updating materialised views · CPC title