Streams on shared database objects
A database platform can track changes using a stream that records changes made by database transactions. Streams on views can be implemented by applying a stream to source data, and applying the view to the streamed data. Users of the database platform can query the stream on the view to review recent changes to the view, such as changes made since the last database transaction.
1. A method comprising:
identifying, by one or more processors of a machine, a database view of one or more source tables of a database platform, the database view comprising a table definition specifying data to include in the database view from the one or more source tables;
receiving a query that specifies a database stream object to be applied to the database view to track changes made to the database view, the database stream object recording one or more row level changes to the database view caused by one or more database transactions, the one or more row level changes being tracked in a change table of the database stream object; and
in response to the query, performing query expansion to rearrange a query plan such that the database stream object is applied to the one or more source tables and the database view is applied to the change table;
receiving an additional query for change data from the database stream object;
generating, using the rearranged query plan, the change data by generating intermediate change data by applying the additional query to the change table that tracks changes made to the one or more source tables and applying the database view on the intermediate change data to generate the change data; and
storing the change data.
2. The method of claim 1 , wherein the one or more source tables comprises a plurality of tables, and wherein the database view is generated from the plurality of tables.
3. The method of claim 2 , wherein the change table comprises indications of changes to one of the plurality of tables.
4. The method of claim 3 , wherein changes to other tables of the plurality of tables are not indicated in the change table.
5. The method of claim 1 , further comprising:
generating the database stream object at least in part by appending metadata columns to the one or more source tables to generate the change table.
6. The method of claim 5 , wherein the metadata columns describe the one or more row level changes made in each transaction of the one or more database transactions.
7. The method of claim 6 , wherein the one or more row level changes described by the metadata columns include one or more of: an insert operation, an update operation, or a delete operation.
8. The method of claim 1 , wherein the database view and the one or more source tables are managed by a data provider end-user of the database platform, and the database stream object is managed by a data consumer end-user of the database platform.
9. The method of claim 8 , wherein the database view is a secure database view having restricted access to tables from which the secure database view includes data, and wherein the method further comprises:
receiving, from the data provider end-user, a request to share the secure database view on the database platform with the data consumer end-user without enabling access to the one or more source tables by the data consumer end-user; and
causing, on a user device of the data consumer end-user, display of data from the secure database view.
10. The method of claim 1 , wherein the one or more source tables comprises a plurality of tables, and wherein the database view includes data from different tables of the plurality of tables using a join operation included in the database view.
11. The method of claim 1 , wherein each of the one or more database transactions includes one or more read operations or write operations.
12. A system comprising:
one or more processors of a machine; and
a memory storing instructions that, when executed by the one or more processors, cause the machine to perform operations comprising:
identifying, in a database platform, a database view of one or more source tables, the database view comprising a table definition specifying data to include in the database view from the one or more source tables;
receiving a query that specifies a database stream object to be applied to the database view to track changes made to the database view, the database stream object recording one or more row level changes to the database view caused by one or more database transactions, the one or more row level changes being tracked in a change table of the database stream object; and
in response to the query, performing query expansion to rearrange a query plan such that the database stream object is applied to the one or more source tables and the database view is applied to the change table;
receiving an additional query for change data from the database stream object:
generating, using the rearranged query plan, the change data by generating intermediate change data by applying the additional query to the change table that tracks changes made to the one or more source tables and applying the database view on the intermediate change data to generate the change data; and
storing the change data.
13. The system of claim 12 , wherein the one or more source tables comprises a plurality of tables, and wherein the database view is generated from the plurality of tables.
14. The system of claim 13 , wherein the change table comprises indications of changes to one of the plurality of tables.
15. The system of claim 14 , wherein changes to other tables of the plurality of tables are not indicated in the change table.
16. The system of claim 12 , the operations further comprising:
generating the database stream object at least in part by appending metadata columns to the one or more source tables to generate the change table.
17. The system of claim 16 , wherein the metadata columns describe the one or more row level changes made in each transaction of the one or more database transactions.
18. The system of claim 17 , wherein the one or more row level changes described by the metadata columns include one or more of: an insert operation, an update operation, or a delete operation.
19. The system of claim 12 , wherein the database view and the one or more source tables are managed by a data provider end-user of the database platform, and the database stream object is managed by a data consumer end-user of the database platform.
20. The system of claim 19 , wherein the database view is a secure database view having restricted access to tables from which the secure database view includes data, and wherein the operations further comprise:
receiving, from the data provider end-user, a request to share the secure database view on the database platform with the data consumer end-user without enabling access to the one or more source tables by the data consumer end-user; and
causing, on a user device of the data consumer end-user, display of data from the secure database view.
21. The system of claim 12 , wherein the one or more source tables comprises a plurality of tables, and wherein the database view includes data from different tables of the plurality of tables using a join operation included in the database view.
22. The system of claim 12 , wherein each of the one or more database transactions includes one or more read operations or write operations.
23. A machine storage medium embodying instructions that, when executed by a machine, cause the machine to perform operations comprising:
identifying, in a database platform, a database view of one or more source tables, the database view comprising a table definition specifying data to include in the database view from the one or more source tables;
receiving a query that specifies a database stream object to be applied to the database view to track changes made to the database view, the database stream object recording one or more row level changes to the database view caused by one or more database transactions, the one or more row level changes being tracked in a change table of the database stream object; and
in response to the query, performing query expansion to rearrange a query plan such that the database stream object is applied to the one or more source tables and the database view is applied to the change table;
receiving an additional query for change data from the database stream object;
generating, using the rearranged query plan, the change data by generating intermediate change data by applying the additional query to the change table that tracks changes made to the one or more source tables and applying the database view on the intermediate change data to generate the change data; and
storing the change data.
24. The machine storage medium of claim 23 , wherein the one or more source tables comprises a plurality of tables, and wherein the database view is generated from the plurality of tables.
25. The machine storage medium of claim 24 , wherein the change table comprises indications of changes to one of the plurality of tables.
26. The machine storage medium of claim 25 , wherein changes to other tables of the plurality of tables are not indicated in the change table.
27. The machine storage medium of claim 23 , the operations further comprising:
generating the database stream object at least in part by appending metadata columns to the one or more source tables to generate the change table.
28. The machine storage medium of claim 27 , wherein the metadata columns describe the one or more row level changes made in each transaction of the one or more database transactions.
29. The machine storage medium of claim 28 , wherein the one or more row level changes described by the metadata columns include one or more of: an insert operation, an update operation, or a delete operation.
30. The machine storage medium of claim 23 , wherein the database view and the one or more source tables are managed by a data provider end-user of the database platform, and the database stream object is managed by a data consumer end-user of the database platform.