Data sharing and materialized views in multiple tenant database systems

Patent metadata

Field	Value
Publication number	US-10628415-B1
Application number	US-201916705664-A
Country	US
Kind code	B1
Filing date	Dec 6, 2019
Priority date	May 31, 2019
Publication date	Apr 21, 2020
Grant date	Apr 21, 2020

Abstract

Systems, methods, and devices for generating and updating cross-account materialized views in multiple tenant database systems. A methods 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:
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:
defining a share object in a first account, the share object including 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 subsequent to generating the materialized view;
merging the materialized view and the share object;
determining that the materialized view is stale with respect to the share object based at least in part on the merging;
generating a refreshed materialized view with respect to the share object in response to the determining;
receiving a request from the second account to access to the share object; and
sharing the refreshed materialized view to the second account in response to the received request.

2. The system of claim 1 , wherein determining that the materialized view is stale further comprises:
identifying that the data in the share object has been modified since a previous refresh of the materialized view.

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:
executing a query of the share object based on information in the materialized view and a set of modifications made to the share object since a previous refresh of the materialized view.

4. 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:
storing the data associated with the first account across one or more shared storage devices;
defining a first execution platform associated with the first account that has read access and write access for the data associated with the first account; and
defining a second execution platform associated with the second account that has read access to the share object.

5. The system of claim 4 , wherein
the second execution platform associated with the second account performs the generating the materialized view over the share object,
the first execution platform associated with the first account includes the updating the data associated with the first account, and
the second execution platform associated with the second account performs the determining that the materialized view is stale with respect to the share object.

6. 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:
defining a secure view definition for the materialized view, the defining the secure view definition including:
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.

7. 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:
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:
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 defining the view privileges for the cross-account access rights to the share object comprises hiding the view privileges from the second account and making the view privileges visible to the first account.

9. The system of claim 1 , wherein defining the share object comprises:
defining an object name unique to the first account,
defining an object role, or
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 , 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 request from the second account to generate a materialized view over certain data associated with the first account;
identifying whether the certain data is included in the share object;
granting the second account authorization to generate the materialized view over the certain data; and
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 including 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 subsequent to generating the materialized view;
merging the materialized view and the share object;
determining that the materialized view is stale with respect to the share object based at least in part on the merging;
generating a refreshed materialized view with respect to the share object in response to the determining;
receiving a request from the second account to access to the share object; and
sharing the refreshed materialized view to the second account in response to the received request.

12. The method of claim 11 , wherein determining that the materialized view is stale further comprises:
identifying that the data in the share object has been modified since a previous refresh of the materialized view, wherein the data in the share object has been modified by one or more of an update, a delete, or an insert.

13. The method of claim 11 , further comprising:
executing a query based on information in the materialized view and a set of modifications made to the share object since a previous 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 shared storage devices;
defining a first execution platform associated with the first account that has read access and write access for the data associated with the first account; and
defining a second execution platform associated with the second account that has read access to the share object.

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

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/2393 Primary

  Updating materialised views · CPC title

• G06F16/24539 Primary

  using cached or materialised query results · CPC title

• G06F16/2282

  Tablespace storage structures; Management thereof · CPC title

• G06F16/24552

  Database cache management · CPC title