Performing in-memory columnar analytic queries on externally resident data
Techniques herein use in-memory column vectors to process data that is external to a database management system (DBMS) and logically join the external data with data that is native to the DBMS. In an embodiment, a computer maintains a data dictionary for native data that is durably stored in an DBMS and external data that is not durably stored in the DBMS. From a client through a connection to the DBMS, the computer receives a query. The computer loads the external data into an in-memory column vector that resides in random access memory of the DBMS. Based on the query and the data dictionary, the DBMS executes a data join of the in-memory column vector with the native data. To the client through said connection, the computer returns results of the query based on the data join.
1 . A method comprising:
maintaining a data dictionary for:
native data that is persisted in a database management system (DBMS), and
external data that is not persisted in the DBMS;
loading, based on the data dictionary, the external data into a first in-memory column vector of a plurality of in-memory column vectors that reside in a random access memory of the DBMS, wherein said loading the external data comprises:
dictionary encoding the external data, and
generating a monotonically increasing sequence of distinct row numbers;
generating a second in-memory vector of said plurality of in-memory column vectors that contains the monotonically increasing sequence of distinct row numbers;
after said loading and dictionary encoding the external data, receiving a query; and
executing the query based on: the data dictionary, said monotonically increasing sequence of distinct row numbers, and the plurality of in-memory column vectors;
wherein the method is performed by one or more computers.
2 . The method of claim 1 , wherein results of the query include a particular distinct row number of said monotonically increasing sequence of distinct row numbers.
3 . The method of claim 1 further comprising using, for a relational operation, a particular distinct row number of said monotonically increasing sequence of distinct row numbers.
4 . The method of claim 3 wherein said using, for a relational operation, a particular distinct row number comprises using, for a relational join, said particular distinct row number.
5 . The method of claim 4 further comprising performing the relational join by using a dense key while scanning the first in-memory column vector that contains the external data.
6 . The method of claim 5 further comprising applying, based on said monotonically increasing sequence of distinct row numbers, a window function to the first in-memory column vector that contains the external data.
7 . The method of claim 6 further comprising generating the dense key based on a column in a relational table persisted in the DBMS.
8 . The method of claim 7 wherein said generating the dense key comprises obtaining the dense key based on an encoding dictionary of the column in the relational table.
9 . The method of claim 4 wherein the relational join comprises using an octet that contains multiple key values.
10 . The method of claim 1 further comprising:
persisting a database checkpoint that contains the plurality of in-memory column vectors;
copying the plurality of in-memory column vectors into the random access memory of the DBMS.
11 . One or more non-transitory computer-readable media storing instructions that, when executed by one or more processors, cause:
maintaining a data dictionary for:
native data that is persisted in a database management system (DBMS), and
external data that is not persisted in the DBMS;
loading, based on the data dictionary, the external data into a first in-memory column vector of a plurality of in-memory column vectors that reside in a random access memory of the DBMS, wherein said loading the external data comprises:
dictionary encoding the external data, and
generating a monotonically increasing sequence of distinct row numbers;
generating a second in-memory vector of said plurality of in-memory column vectors that contains the monotonically increasing sequence of distinct row numbers;
after said loading and dictionary encoding the external data, receiving a query; and
executing the query based on: the data dictionary, said monotonically increasing sequence of distinct row numbers, and the plurality of in-memory column vectors.
12 . The one or more non-transitory computer-readable media of claim 11 , wherein results of the query include a particular distinct row number of said monotonically increasing sequence of distinct row numbers.
13 . The one or more non-transitory computer-readable media of claim 11 wherein the instructions further cause using, for a relational operation, a particular distinct row number of said monotonically increasing sequence of distinct row numbers.
14 . The one or more non-transitory computer-readable media of claim 13 wherein said using, for a relational operation, a particular distinct row number comprises using, for a relational join, said particular distinct row number.
15 . The one or more non-transitory computer-readable media of claim 14 wherein the instructions further cause performing the relational join by using a dense key while scanning the first in-memory column vector that contains the external data.
16 . The one or more non-transitory computer-readable media of claim 15 wherein the instructions further cause applying, based on said monotonically increasing sequence of distinct row numbers, a window function to the first in-memory column vector that contains the external data.
17 . The one or more non-transitory computer-readable media of claim 16 wherein the instructions further cause generating the dense key based on a column in a relational table persisted in the DBMS.
18 . The one or more non-transitory computer-readable media of claim 17 wherein said generating the dense key comprises obtaining the dense key based on an encoding dictionary of the column in the relational table.
19 . The one or more non-transitory computer-readable media of claim 14 wherein the relational join comprises using an octet that contains multiple key values.
20 . The one or more non-transitory computer-readable media of claim 11 wherein the instructions further cause:
persisting a database checkpoint that contains the plurality of in-memory column vectors;
copying the plurality of in-memory column vectors into the random access memory of the DBMS.