Dataframe workloads using read-only data snapshots

Patent metadata

Field	Value
Publication number	US-12277101-B2
Application number	US-202318393279-A
Country	US
Kind code	B2
Filing date	Dec 21, 2023
Priority date	Sep 18, 2023
Publication date	Apr 15, 2025
Grant date	Apr 15, 2025

Abstract

A method provides deterministic row ordering for query result sets in a database system. A framework receives a query referencing a database table and generating a row index column in response. The framework generates the row index column that involves determining an order associated with source files underlying the referenced table and assigns each table row a unique index value based on the file order and row position within the files. The framework executes the received query to produce a result set ordered by the generated row index column. The framework determines row order based on analyzing underlying data file metadata and facilitates returning the result set with consistent, reliable ordering, enabling stable positional-based access semantics when querying distributed database tables.

First claim

What is claimed is:

1. A method comprising:
receiving, by at least one hardware processor, a query referencing a table;
in response to receiving the query, generating a row index column for rows of a referenced table, the generating of the row index column comprising:
determining an order associated with source files underlying the referenced table; and
assigning each row of the referenced table a unique row position value based on the order of the source files and a position of each row within the source files;
generating a result set based on execution of the received query, the result set being ordered based on the generated row index column to provide deterministic row ordering; and
returning the result set with the deterministic row ordering.

2. The method of claim 1 , wherein determining the order associated with the source files comprises:
identifying names of the source files underlying the referenced table; and
sorting the source files based on the identified names.

3. The method of claim 1 , wherein assigning each row of the referenced table the unique row position value comprises:
determining an offset associated with each data file based on the order of the source files; and
assigning each row of the referenced table the unique row position value based on the offset of the data file containing the row and the position of the row within the data file.

4. The method of claim 1 , further comprising:
employing the generated row position value for the referenced table for reuse when additional queries referencing the table are received by a database system.

5. The method of claim 1 , wherein executing the received query comprises:
modifying the received query to include an ORDER BY clause based on the generated row index column.

6. The method of claim 1 , further comprising:
receiving a request to create a snapshot of the referenced table;
creating a read-only snapshot of the referenced table; and
wherein executing the received query comprises executing the query against the read-only snapshot instead of the referenced table.

7. The method of claim 6 , wherein creating the read-only snapshot further comprises:
disabling one or more background processes associated with the read-only snapshot that modify a structure of the source files associated with the read-only snapshot.

8. The method of claim 1 , wherein creating a read-only snapshot of the referenced table comprises:
generating a clone of metadata associated with the referenced table without copying data of the referenced table; and
associating a read-only access mode with the cloned table.

9. The method of claim 1 , wherein the referenced table comprises one of a database table, a view, or a snapshot.

10. The method of claim 1 , wherein assigning each row of the referenced table the unique row position value further comprises:
determining that two or more rows of the referenced table have identical values across all columns; and
assigning the two or more rows different unique row position values.

11. A system comprising:
one or more hardware processors; and
at least one memory storing instructions that, when executed by the one or more hardware processors, cause the system to perform operations comprising:
receiving, by at least one hardware processor, a query referencing a table;
in response to receiving the query, generating a row index column for rows of a referenced table, the generating of the row index column comprising:
determining an order associated with source files underlying the referenced table; and
assigning each row of the referenced table a unique row position value based on the order of the source files and a position of each row within the source files;

generating a result set based on execution of the received query, the result set being ordered based on the generated row index column to provide deterministic row ordering; and
returning the result set with the deterministic row ordering.

12. The system of claim 11 , wherein determining the order associated with the source files comprises:
identifying names of the source files underlying the referenced table; and
sorting the source files based on the identified names.

13. The system of claim 11 , wherein assigning each row of the referenced table the unique row position value comprises:
determining an offset associated with each data file based on the order of the source files; and
assigning each row of the referenced table the unique row position value based on the offset of the data file containing the row and the position of the row within the data file.

14. The system of claim 11 , the operations comprising:
employing the generated row position values for the referenced table for reuse when additional queries referencing the table are received by a database system.

15. The system of claim 11 , wherein executing the received query comprises:
modifying the received query to include an ORDER BY clause based on the generated row index column.

16. The system of claim 11 , the operations comprising:
receiving a request to create a snapshot of the referenced table;
creating a read-only snapshot of the referenced table; and
wherein executing the received query comprises executing the query against the read-only snapshot instead of the referenced table.

17. The system of claim 16 , wherein creating the read-only snapshot further comprises:
disabling one or more background processes associated with the read-only snapshot that modify a structure of the source files associated with the read-only snapshot.

18. The system of claim 11 , wherein creating a read-only snapshot of the referenced table comprises:
generating a clone of metadata associated with the referenced table without copying data of the referenced table; and
associating a read-only access mode with the cloned table.

19. The system of claim 11 , wherein the referenced table comprises one of a database table, a view, or a snapshot.

20. The system of claim 11 , wherein assigning each row of the referenced table the unique row position value further comprises:
determining that two or more rows of the referenced table have identical values across all columns; and
assigning the two or more rows different unique row position values.

21. A machine-storage medium embodying instructions that, when executed by a machine, cause the machine to perform operations comprising:
receiving, by at least one hardware processor, a query referencing a table;
in response to receiving the query, generating a row index column for rows of a referenced table, the generating of the row index column comprising:
determining an order associated with source files underlying the referenced table; and
assigning each row of the referenced table a unique row position value based on the order of the source files and a position of each row within the source files;
generating a result set based on execution of the received query, the result set being ordered based on the generated row index column to provide deterministic row ordering; and
returning the result set with the deterministic row ordering.

22. The machine-storage medium of claim 21 , wherein determining the order associated with the source files comprises:
identifying names of the source files underlying the referenced table; and
sorting the source files based on the identified names.

23. The machine-storage medium of claim 21 , wherein assigning each row of

Assignees

• Snowflake Inc

Inventors

• Chintala Srilakshmi
• Du Jianzhun
• Kumar Naresh
• Shankar Srinath
• Spiegelberg Leonhard Franz
• Vandenberg Eric Shawn
• Zhan andong
• Zou Yun

Classifications

• G06F16/128

  Details of file system snapshots on the file-level, e.g. snapshot creation, administration, deletion (error detection or correction of the data by redundancy in operations or in hardware G06F11/14, G06F11/16) · CPC title

• G06F16/2272 Primary

  Management thereof · CPC title

• G06F16/2453 Primary

  Query optimisation · CPC title

• G06F16/256

  in federated or virtual databases · CPC title

• G06F16/244 Primary

  Grouping and aggregation · CPC title