Virtual columns to expose row specific details for query execution in column store databases

Patent metadata

Field	Value
Publication number	US-10762068-B2
Application number	US-201815945723-A
Country	US
Kind code	B2
Filing date	Apr 4, 2018
Priority date	Apr 4, 2018
Publication date	Sep 1, 2020
Grant date	Sep 1, 2020

Abstract

A system for implementing virtual columns in a column store database is provided. In some implementations, the system performs operations including accessing a database table in a column store database system, the database table comprising at least one virtual column, the column store database system storing data in a column-oriented structure. The operations further include calculating a value for the at least one virtual column based on at least one existing column of the database table, the calculated value mapped to a row identifier associated with a row of the database table. The operations further include providing, in response to a query, the calculated value to a user interface, the query associated with the row identifier.

First claim

What is claimed is:

1. A system, comprising:
at least one data processor; and
at least one memory storing instructions which, when executed by the at least one data processor, result in operations comprising:
accessing a database table in a column store database system, the database table comprising at least one virtual column, the column store database system storing data in a column-oriented structure;
calculating a value for the at least one virtual column based on at least one existing column of the database table, the calculated value mapped to a row identifier associated with a row of the database table, wherein calculating the value comprises:
retrieving data values from a plurality of columns of the database table, the data values located in a same row of the database table, and
mapping the row identifier of the same row to the data values from the plurality of columns; and

providing, in response to a query, the calculated value to a user interface, the query associated with the row identifier.

2. The system of claim 1 , wherein calculating the value comprises:
retrieving metadata of a logical wrapper, the logical wrapper associated with a plurality of columns of the database table, the metadata located in a same row of the database table; and
mapping the row identifier of the same row to the metadata from the plurality of columns.

3. The system of claim 1 , wherein the calculated value is based on metadata of a logical wrapper associated with the at least one existing column.

4. The system of claim 1 , wherein the operations further comprise:
receiving the query; and
optimizing the query based on a partition identifier of a partition of the database table.

5. The system of claim 4 , wherein optimizing the query based on the partition identifier comprises joining, based on a first partition identifier of the first table and a second partition identifier of the second table, at least a portion of a first table with at least a portion of a second table to form a joined table, the calculated value of the at least one virtual column comprising the first partition identifier and the second partition identifier.

6. The system of claim 1 , wherein the calculated value comprises at least one of a creation timestamp, a last locked timestamp, and/or a partition identifier.

7. The system of claim 1 , wherein the providing the calculated value to the user interface comprises providing, by an application programming interface, the calculated value to the user interface.

8. The system of claim 1 , wherein the row identifier comprises a hash value.

9. The system of claim 1 , wherein the operations further comprise:
receiving the query, the query specifying the at least one virtual column and a threshold timestamp, wherein providing the calculated value to the user interface comprises providing one or more rows of a table associated with a creation timestamp that occurs after the threshold timestamp.

10. A method comprising:
accessing, by at least one processor, a database table in a column store database system, the database table comprising at least one virtual column, the column store database system storing data in a column-oriented structure;
calculating, by the at least one processor, a value for the at least one virtual column based on at least one existing column of the database table, the calculated value mapped to a row identifier associated with a row of the database table, wherein calculating the value comprises:
retrieving data values from a plurality of columns of the database table, the data values located in a same row of the database table, and

mapping the row identifier of the same row to the data values from the plurality of columns; and
providing, by the at least one processor and in response to a query, the calculated value to a user interface, the query associated with the row identifier.

11. The method of claim 10 , wherein calculating the value comprises:
retrieving metadata of a logical wrapper, the logical wrapper associated with a plurality of columns of the database table, the metadata located in a same row of the database table; and
mapping the row identifier of the same row to the metadata from the plurality of columns.

12. The method of claim 10 , wherein the calculated value is based on metadata of a logical wrapper associated with the at least one existing column.

13. The method of claim 10 , further comprising:
receiving the query; and
optimizing the query based on a partition identifier of a partition of the database table.

14. The method of claim 13 , wherein optimizing the query based on the partition identifier comprises joining, based on a first partition identifier of the first table and a second partition identifier of the second table, at least a portion of a first table with at least a portion of a second table to form a joined table, the calculated value of the at least one virtual column comprising the first partition identifier and the second partition identifier.

15. The method of claim 10 , wherein the calculated value comprises at least one of a creation timestamp, a last locked timestamp, and/or a partition identifier.

16. The method of claim 10 , wherein the providing the calculated value to the user interface comprises providing, by an application programming interface, the calculated value to the user interface.

17. The method of claim 10 , wherein the row identifier comprises a hash value.

18. A non-transitory computer readable medium storing instructions which, when executed by at least one processor, cause operations comprising:
accessing a database table in a column store database system, the database table comprising at least one virtual column, the column store database system storing data in a column-oriented structure;
calculating a value for the at least one virtual column based on at least one existing column of the database table, the calculated value mapped to a row identifier associated with a row of the database table, wherein calculating the value comprises:
retrieving data values from a plurality of columns of the database table, the data values located in a same row of the database table, and
mapping the row identifier of the same row to the data values from the plurality of columns; and

providing, in response to a query, the calculated value to a user interface, the query associated with the row identifier.

Assignees

• Sap Se

Inventors

• Legler Thomas
• Tonder Andreas

Classifications

• G06F16/2453 Primary

  Query optimisation · CPC title

• G06F16/2255

  Hash tables · CPC title

• G06F16/221 Primary

  Column-oriented storage; Management thereof · CPC title

• G06F16/256

  in federated or virtual databases · CPC title