Database object type for querying and transactional consumption of changes in queries results
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.
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 computer-storage medium comprising instructions that, when executed by one or more processors of a machine, configure the machine 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 on the source table; and
performing stream expansion of the second stream object based on computing changes on the source table.
22. The computer-storage medium of claim 21 , wherein the operations for performing the stream expansion further comprise:
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.
23. The computer-storage medium of claim 22 , wherein the operations for performing the second query further comprise:
determining added and deleted micro-partitions in the source table.
24. The computer-storage medium of claim 23 , wherein the operations for performing the second query further comprise:
joining rows from the added and deleted micro-partitions to compute the changes in the source table.
25. The computer-storage medium of claim 21 , 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.
26. The computer-storage medium of claim 25 , the operations 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.
27. The computer-storage medium of claim 26 , the operations 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.
28. The computer-storage medium of claim 27 , the operations 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.
29. The computer-storage medium of claim 28 , wherein the syntax tree comprises a join operation between the second stream object and the at least a third stream object, and wherein the operations further comprise:
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.
30. The computer-storage medium of claim 28 , wherein the second query or the third query comprises a change node requesting a change operation, and wherein the operations further comprise:
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.