Systems and methods for optimized cloud database query execution

Patent metadata

Field	Value
Publication number	US-11899660-B2
Application number	US-202217813260-A
Country	US
Kind code	B2
Filing date	Jul 18, 2022
Priority date	Jul 18, 2022
Publication date	Feb 13, 2024
Grant date	Feb 13, 2024

Abstract

A method for writing data to a cloud-based database for optimized cloud-based database query execution may include a table writer computer program executed by a local electronic device: (1) receiving table data comprising rows and columns, a schema for the table, and a primary-key for the table; (2) creating a footer for the table, the footer comprising the schema and the primary-key; (3) sorting the rows of the table data using the primary-key; (4) dividing the sorted columns of the table data into a plurality of datapages based on a datapage size; (5) identifying sparse indices for each datapage, wherein the sparse indices comprising a minimum value, a maximum value, a starting row index, an ending row index for each datapage; (6) writing each datapage to cloud storage; (7) inserting the sparse indices into the footer for the table data; and (8) writing the footer to cloud storage.

First claim

What is claimed is:

1. A method for writing data to a cloud-based database for optimized cloud-based database query execution, comprising:
receiving, by a table writer computer program executed by a local electronic device, table data comprising a plurality of rows and a plurality of columns, a schema for the table data, and a primary-key for the table data;
creating, by the table writer computer program, a footer for the table data, the footer comprising the schema and the primary-key;
sorting, by the table writer computer program, the rows of the table data using the primary-key;
dividing, by the table writer computer program, columns of the sorted rows of the table data into a plurality of datapages based on a datapage size;
identifying, by the table writer computer program, sparse indices for each datapage, wherein the sparse indices comprise a minimum value, a maximum value, a starting row index, an ending row index for each datapage;
writing, by the table writer computer program, each datapage to cloud storage;
inserting, by the table writer computer program, the sparse indices into the footer for the table data; and
writing, by the table writer computer program, the footer to cloud storage.

2. The method of claim 1 , wherein the table writer computer program further receives entitlements data for access to data in the table data and writes the entitlements data to the footer, wherein the entitlements data identifies individuals or roles that are authorized to access the data in the table data.

3. The method of claim 1 , wherein the primary-key identifies a value in a column.

4. The method of claim 1 , wherein the primary-key identifies a value in the plurality of columns.

5. The method of claim 1 , wherein each datapage represents a basic unit of data that is transferred into and out of the cloud storage.

6. The method of claim 1 , further comprising:
receiving, by the table writer computer program, modified data for data in one of the plurality of datapages; and
writing, by the table writer computer program, the modified data to a new row-group in the datapage;
wherein the new row-group is returned with the one of the plurality of datapages.

7. The method of claim 1 , wherein the table data comprises a table.

8. A method for fetching data from a cloud-based database for optimized cloud-based database query execution, comprising:
receiving, by a table reader computer program executed by a local electronic device, a data query request from a user for data that is stored in cloud storage, the data query request comprising a row filtering expression and the cloud storage comprises table data having a plurality of datapages;
retrieving, by the table reader computer program, a footer for table data associated with the data stored in cloud storage;
identifying, by the table reader computer program, one of the plurality of datapages comprising data responsive to the row filtering expression based on the footer;
retrieving, by the table reader computer program, the identified datapage from cloud storage;
assembling, by the table reader computer program, matching rows from the datapage; and
returning, by the table reader computer program, the assembled matching rows to the local electronic device.

9. The method of claim 8 , wherein the footer comprises a sparse index comprising a minimum value, a maximum value, a starting row index, an ending row index for each of the plurality of datapages.

10. The method of claim 9 , wherein the row filtering expression is within the sparse index of the identified datapage.

11. The method of claim 8 , further comprising:
retrieving, by the table reader computer program, entitlements data for the datapage from the footer, wherein the entitlements data identifies individuals or roles that are authorized to access the data in the table data; and
determining, by the table reader computer program, that the user is entitled to access the data by comparing the entitlements of the user to the retrieved entitlements data.

12. The method of claim 8 , further comprising:
identifying, by the table reader computer program, a new row-group in the datapage comprising modified data for the identified datapage; and
returning, by the table reader computer program, the new row-group with the identified datapage.

13. The method of claim 12 , wherein the table data comprises a table.

14. A non-transitory computer readable storage medium, including instructions stored thereon, which when read and executed by one or more computer processors, cause the one or more computer processors to perform steps comprising:
receiving table data comprising a plurality of rows and a plurality of columns, a schema for the table data, and a primary-key for the table data;
creating a footer for the table data, the footer comprising the schema and the primary-key;
sorting the rows of the table data using the primary-key;
dividing columns of the sorted rows of the table data into a plurality of datapages based on a datapage size;
identifying sparse indices for each datapage, wherein the sparse indices comprise a minimum value, a maximum value, a starting row index, an ending row index for each datapage;
writing each datapage to cloud storage;
inserting the sparse indices into the footer for the table data; and
writing the footer to cloud storage.

15. The non-transitory computer readable storage medium of claim 14 , further including instructions stored thereon, which when read and executed by one or more computer processors, cause the one or more computer processors to perform steps comprising:
receiving entitlements data for access to data in the table data, wherein the entitlements data identifies individuals or roles that are authorized to access the data in the table data; and
writing the entitlements data to the footer.

16. The non-transitory computer readable storage medium of claim 14 , wherein the primary-key identifies a value in a column.

17. The non-transitory computer readable storage medium of claim 14 , wherein the primary-key identifies a value in the plurality of columns.

18. The non-transitory computer readable storage medium of claim 14 , wherein each datapage represents a basic unit of data that is transferred into and out of the cloud storage.

19. The non-transitory computer readable storage medium of claim 14 , further including instructions stored thereon, which when read and executed by one or more computer processors, cause the one or more computer processors to perform steps comprising:
receiving modified data for data in one of the plurality of datapages; and
writing the modified data to a new row-group in the datapage;
wherein the new row-group is returned with the one of the plurality of datapages.

20. The non-transitory computer readable storage medium of claim 14 , wherein the table data comprises a table.

Assignees

• Jpmorgan Chase Bank Na

Inventors

• Tummala Narayana R
• Pistoia Marco
• Deshpande Pranav
• Azanli Elvir
• Ligman Joseph W
• Schaffa Frank

Classifications

• G06F16/2453 Primary

  Query optimisation · CPC title

• G06F16/221

  Column-oriented storage; Management thereof · CPC title

• G06F16/2282

  Tablespace storage structures; Management thereof · CPC title

• G06F40/177

  of tables; using ruled lines · CPC title