Applying Relational Algebraic Operations to Change Result Sets of Source Tables to Update a Materialized View

1. A system, comprising: at least one processor; and a memory, storing program instructions that when executed by the at least one processor cause the at least one processor to implement a database, the database configured to: create a stored procedure for a materialized view that obtains from one or more tables of the database, one or more changes made to the one or more tables of the database used to create the materialized view that are not included in one or more internal tables created for providing the materialized view; and after a determination to update the materialized view: execute the stored procedure to update the materialized view, wherein to execute the stored procedure the database is configured to: perform a respective query, to the one or more tables, to generate a respective result set that includes those changes of the one or more changes made to the table; and perform one or more relational algebra operators in the stored procedure that use as an operand one or more of the respective result sets to include the one or more changes in the one or more internal tables.

2. The system of claim 1 , wherein the one or more relational algebra operators in the stored procedure is a provenance merge operator, wherein the one or more internal tables is a target table operand for the provenance merge operator, and wherein the one or more of the respective result sets is a source table operand for the provenance merge operator, and wherein to perform the provenance merge operator, the database is configured to: identify one or more provenance attributes of the target table and the source table; and update the target table according to a comparison of respective tuples of the target table and the source table according to the one or more provenance attributes.

3. The system of claim 2 , wherein to update the target table according to the comparison of respective tuples of the target table and the source table according to the one or more provenance attributes, the database is configured to: determine that one of the respective tuples in the source table has a matching tuple in the target table, wherein the one respective tuple in the source table is not identified as deleted; and update one or more measure attributes in the matching target tuple according to one or more corresponding scalar expressions in the stored procedure to update the one or more measure attributes based on one or more values of the one respective tuple in the source table.

4. The system of claim 1 , wherein the database is a processing cluster implemented as part of a data warehouse service of a provider network, and wherein the database is further configured to: receive a request to view the stored procedure; and responsive to the request to view the stored procedure, provide the stored procedure.

5. A method, comprising: updating a materialized view for a database, comprising: determining a procedure for the materialized view that obtains from one or more tables of the database, one or more changes made to the one or more tables of the database used to create the materialized view that are not included in one or more internal tables created for providing the materialized view; and executing the procedure to include the one or more changes in the materialized view, comprising: generating, from the one or more tables, a respective result set that includes those changes of the one or more changes made to the table; and performing one or more relational algebra operators in the stored procedure that use as an operand one or more of the respective result sets.

6. The method of claim 5 , wherein the one or more relational algebra operators in the procedure is a provenance merge operator, wherein the one or more internal tables is a target table operand for the provenance merge operator, and wherein the one or more of the respective result sets is a source table operand for the provenance merge operator, and wherein performing the provenance merge operator comprises: identifying one or more provenance attributes of the target table and the source table; and updating the target table according to a comparison of respective tuples of the target table and the source table according to the one or more provenance attributes.

7. The method of claim 6 , wherein updating the target table according to the comparison of respective tuples of the target table and the source table according to the one or more provenance attributes comprises: determining that one of the respective tuples in the target table has no matching tuple in the source table; and retaining the one respective tuple in the target table.

8. The method of claim 6 , wherein updating the target table according to the comparison of respective tuples of the target table and the source table according to the one or more provenance attributes comprises: determining that one of the respective tuples in the source table has a matching tuple in the target table, wherein the one respective tuple in the source table identified as deleted; and deleting the matching tuple.

9. The method of claim 6 , wherein updating the target table according to the comparison of respective tuples of the target table and the source table according to the one or more provenance attributes comprises: determining that one of the respective tuples in the source table has a matching tuple in the target table, wherein the one respective tuple in the source table is not identified as deleted; and updating one or more measure attributes in the matching target tuple according to one or more corresponding scalar expressions in the stored procedure to update the one or more measure attributes based on one or more values of the one respective tuple in the source table.

10. The method of claim 5 , further comprising: creating the procedure as a stored procedure and the one or more internal tables in response to receiving a request to create the materialized view.

11. The method of claim 5 , wherein another operand of the one or more relational algebra operators is another one of the respective result sets.

12. The method of claim 5 , wherein the one or more relational algebra operators in the procedure is a provenance merge operator and wherein performing the provenance merge operator comprises: creating a temporary table to store the one or more respective result sets; querying the temporary table to identify those tuples that exist in the one or more internal tables with measure attributes to be updated; updating the measure attributes of those tuples that exist in the one or more internal tables with measure attributes to be updated; querying the temporary table to identify those tuples in the temporary table to be inserted into the one or more internal tables; inserting into the one or more internal tables those tuples identified in the temporary table to be inserted; and querying the temporary table to identify those tuples in the temporary table identified as deleted; and deleting from the one or more internal tables those tuples in the temporary table identified as deleted.

13. The method of claim 5 , wherein the respective result set that includes those changes of the one or more changes includes the respective values of the fields of the columns in individual tuples in the respective result set.

14. One or more non-transitory, computer-readable storage media, storing program instructions that when executed on or across one or more computing devices cause the one or more computing devices to implement: selecting a materialized view for a database to update; identifying a procedure that obtains one or more changes from one or more tables of the database, made to the one or more tables of the database used to create the materialized view that are not included in one or more internal tables created for providing the materialized view; and executing the procedure to update the materialized view with the one or more changes in the materialized view, wherein the stored procedure: generates, from each of the one or more tables, a respective result set that includes those changes of the one or more changes made to the table; and performs one or more relational algebra operators that use as an operand one or more of the respective result sets.

15. The one or more non-transitory, computer-readable storage media of claim 14 , wherein the one or more relational algebra operators in the procedure is a provenance merge operator, wherein the one or more internal tables is a target table operand for the provenance merge operator, and wherein the one or more of the respective result sets is a source table operand for the provenance merge operator, and wherein, in performing the provenance merge operator, the programming instructions cause the one or more computing devices to implement: identifying one or more provenance attributes of the target table and the source table; and updating the target table according to a comparison of respective tuples of the target table and the source table according to the one or more provenance attributes.

16. The one or more non-transitory, computer-readable storage media of claim 15 , wherein, in updating the target table according to the comparison of respective tuples of the target table and the source table according to the one or more provenance attributes, the program instructions cause the one or more computing devices to further implement: determining that one of the respective tuples in the source table has no matching tuple in the target table; and inserting data determined from the one respective tuple in the source table into the target table.

17. The one or more non-transitory, computer-readable storage media of claim 15 , wherein, in updating the target table according to the comparison of respective tuples of the target table and the source table according to the one or more provenance attributes, the program instructions cause the one or more computing devices to further implement: determining that one of the respective tuples in the source table has a matching tuple in the target table, wherein the one respective tuple in the source table is not identified as deleted; and updating one or more measure attributes in the matching target tuple according to one or more corresponding scalar expressions in the procedure to update the one or more measure attributes based on one or more values of the one respective tuple in the source table.

18. The one or more non-transitory, computer-readable storage media of claim 14 , wherein another operand of the one or more relational algebra operators is another one of the respective result sets.

19. The one or more non-transitory, computer-readable storage media of claim 14 , wherein the respective result set that includes those changes of the one or more changes includes respective variance values for fields of the columns used to calculate the actual values of the fields in individual tuples in the respective result set.

20. The one or more non-transitory, computer-readable storage media of claim 14 , wherein the procedure is created as a stored procedure in response to a request to create the table, and wherein the one or more non-transitory, computer-readable storage media store further program instructions that when executed on or across the one or more computing devices cause the one or more computing devices to further implement: receiving a request to view the stored procedure; and responsive to the request to view the stored procedure, providing the stored procedure.