Database object type for querying and transactional consumption of changes in queries results

Patent metadata

Field	Value
Publication number	US-11301451-B1
Application number	US-202117460974-A
Country	US
Kind code	B1
Filing date	Aug 30, 2021
Priority date	Aug 30, 2021
Publication date	Apr 12, 2022
Grant date	Apr 12, 2022

Abstract

Provided herein are systems and methods for a stream object configuration, including configuring and processing streams on views. For example, a method includes detecting a first stream object on a view. The view includes a query associated with a source table. A syntax tree of the query is determined based on a definition of the view. The view is expanded based on replacing the first stream object with the syntax tree. The syntax tree includes a second stream object on the source table. Stream expansion of the second stream object is performed based on computing changes on the source table.

First claim

What is claimed is:

1. A system comprising:
at least one hardware processor; and
at least one memory storing instructions that cause the at least one hardware processor to perform operations comprising:
detecting a first stream object on a view, the view comprising a query associated with a source table, the first stream object comprising a plurality of query results of executing the query and a corresponding plurality of time stamps associated with times the plurality of query results are obtained;
determining a syntax tree of the query based on a definition of the view, the definition of the view comprising at least one database operation associated with the source table;
expanding the view based on replacing the first stream object with the syntax tree, the syntax tree comprising a second stream object, the second stream object applied on the source table and the second stream object corresponding to the at least one database operation; and
performing stream expansion of the second stream object based on computing changes on the source table.

2. The system of claim 1 , wherein during performing the stream expansion, the instructions further cause the at least one hardware processor to perform operations comprising:
converting the second stream object on the source table into a second query; and
performing the second query to compute the changes on the source table.

3. The system of claim 2 , wherein during performing the second query, the instructions further cause the at least one hardware processor to perform operations comprising:
determining added and deleted micro-partitions in the source table.

4. The system of claim 3 , wherein during performing the second query, the instructions further cause the at least one hardware processor to perform operations comprising:
joining rows from the added and deleted micro-partitions to compute the changes in the source table.

5. The system of claim 1 , wherein the query is associated with a plurality of source tables, the plurality of source tables comprising the source table and at least a second source table.

6. The system of claim 5 , wherein the instructions further cause the at least one hardware processor to perform operations comprising:
expanding the view based on replacing the first stream object on the view with the syntax tree, the syntax tree comprising the second stream object on the source table and at least a third stream object on the at least a second source table.

7. The system of claim 6 , wherein the instructions further cause the at least one hardware processor to perform operations comprising:
converting the second stream object on the source table into a second query; and
converting the third stream object on the at least a second source table into a third query.

8. The system of claim 7 , wherein the instructions further cause the at least one hardware processor to perform operations comprising:
performing the stream expansion of the second stream object based on performing the second query to compute the changes on the source table; and
performing stream expansion of the third stream object based on performing the third query to compute changes on the at least a second source table.

9. The system of claim 8 , wherein the syntax tree comprises a join operation between the second stream object and the at least a third stream object, and wherein the instructions further cause the at least one hardware processor to perform operations comprising:
replacing the join operation associated with the expanding of the view with a union of two join operations associated with the stream expansion of the second stream object and the stream expansion of the third stream object.

10. The system of claim 8 , wherein the second query or the third query comprises a change node requesting a change operation, and the instructions further cause the at least one hardware processor to perform operations comprising:
during the stream expansion of the second stream object or the stream expansion of the third stream object:
rewriting the second query or the third query to compute changes associated with the change operation.

11. A method comprising:
detecting a first stream object on a view, the view comprising a query associated with a source table, the first stream object comprising a plurality of query results of executing the query and a corresponding plurality of time stamps associated with times the plurality of query results are obtained;
determining a syntax tree of the query based on a definition of the view, the definition of the view comprising at least one database operation associated with the source table;
expanding the view based on replacing the first stream object with the syntax tree, the syntax tree comprising a second stream object, the second stream object applied on the source table and the second stream object corresponding to the at least one database operation on the source table; and
performing stream expansion of the second stream object based on computing changes on the source table.

12. The method of claim 11 , wherein performing the stream expansion further comprises:
converting the second stream object on the source table into a second query; and
performing the second query to compute the changes on the source table.

13. The method of claim 12 , wherein performing the second query further comprises:
determining added and deleted micro-partitions in the source table.

14. The method of claim 13 , wherein performing the second query further comprises:
joining rows from the added and deleted micro-partitions to compute the changes in the source table.

15. The method of claim 11 , wherein the query is associated with a plurality of source tables, the plurality of source tables comprising the source table and at least a second source table.

16. The method of claim 15 , further comprising:
expanding the view based on replacing the first stream object on the view with the syntax tree, the syntax tree comprising the second stream object on the source table and at least a third stream object on the at least a second source table.

17. The method of claim 16 , further comprising:
converting the second stream object on the source table into a second query; and
converting the third stream object on the at least a second source table into a third query.

18. The method of claim 17 , further comprising:
performing the stream expansion of the second stream object based on performing the second query to compute the changes on the source table; and
performing stream expansion of the third stream object based on performing the third query to compute changes on the at least a second source table.

19. The method of claim 18 , wherein the syntax tree comprises a join operation between the second stream object and the at least a third stream object, and wherein the method further comprises:
replacing the join operation associated with the expanding of the view with a union of two join operations associated with the stream expansion of the second stream object and the stream expansion of the third stream object.

20. The method of claim 18 , wherein the second query or the third query comprises a change node requesting a change operation, and wherein the method further comprises:
during the stream expansion of the second stream object or the stream expansion of the third stream object:
rewriting the second query or the third query to compute changes associated with the change operation.

21. A c

Assignees

• Snowflake Inc

Inventors

• Cseri Istvan
• Jones Tyler
• Mills Daniel
• Sotolongo Daniel E

Classifications

• G06F16/24568 Primary

  Data stream processing; Continuous queries · CPC title

• G06F16/2358 Primary

  Change logging, detection, and notification (replication G06F16/27) · CPC title

• G06F16/2282

  Tablespace storage structures; Management thereof · CPC title

• G06F16/2456

  Join operations · CPC title

• G06F16/278

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