Projections for big database systems
A database system comprised of a decoupled compute layer and storage layer is implemented to store, build, and maintain a canonical dataset, a temporary buffer, and projection datasets. The canonical dataset is a set of batch updated data. The data is appended in chunks to the canonical dataset such that the canonical dataset becomes a historical dataset over time. The buffer is a write ahead log that contains the most recent chunks of data and provides atomicity and durability for the database system. The projection datasets are indexes of the canonical dataset and/or the buffer that may have single or multiple column sort-orders and/or particular data formats. The writes to the canonical dataset, projection datasets, and buffer may be asynchronous and therefore the database system is advantageously less resource constrained.
1 . A system comprising:
one or more non-transitory computer readable storage mediums configured to store:
program instructions;
a canonical dataset;
one or more projection datasets; and
a buffer; and
one or more processors configured to execute the program instructions to cause the system to:
access a first data chunk comprising an edit to the canonical dataset;
temporarily store the first data chunk in the buffer;
receive a query configured to execute on the canonical dataset;
analyze the query to determine a requested result of the query;
access the one or more projection datasets stored in the one or more non-transitory computer readable storage mediums to determine a selected projection dataset from among the one or more projection datasets to optimize query performance based on referencing projection rules associated with the one or more projection datasets to determine a projection rule that corresponds with the requested result, wherein the selected projection dataset is associated with the projection rule, the projection rule having been pre-applied to the selected projection dataset to obviate applying the projection rule to the selected projection dataset during query execution to reduce query execution time allowing the query to execute faster on the selected projection dataset than on other projection datasets of the one or more projection datasets to which the projection rule has not been pre-applied;
combine the selected projection dataset with the buffer in the one or more non-transitory computer readable storage mediums to form a combined projection dataset with data governed by the projection rule;
rewrite the query to execute on the combined projection dataset instead of the canonical dataset to produce the requested result of the query faster by executing on the combined projection dataset than by executing on the canonical dataset;
execute the rewritten query on the combined projection dataset; and
return the requested result.
2 . The system of claim 1 , wherein the one or more processors is further configured to execute the program instructions to cause the system to:
combine the buffer with a second projection dataset asynchronously with combining the buffer to the selected projection dataset based on resource availability of the system to form a second combined projection dataset.
3 . The system of claim 1 , wherein the one or more processors is further configured to execute the program instructions to cause the system to:
transform the combined projection dataset according to a projection rule set associated with the selected projection dataset.
4 . The system of claim 3 , wherein transforming the combined projection dataset includes one or more of sorting data contents of the combined projection dataset and compacting the data contents of the combined projection dataset.
5 . The system of claim 3 , wherein the one or more processors is further configured to execute the program instructions to cause the system to:
combine the buffer with a second projection dataset to form a second combined projection dataset; and
transform, according to a second projection rule set associated with the second projection dataset, the second combined projection dataset asynchronously with transforming the selected projection dataset based on resource availability of the system.
6 . The system of claim 1 , wherein the one or more processors is further configured to execute the program instructions to cause the system to:
transform the buffer according to a projection rule set associated with the selected projection dataset, wherein transforming the buffer includes one or more of sorting data contents of the buffer and compacting the data contents of the buffer.
7 . The system of claim 1 , wherein the one or more processors is further configured to execute the program instructions to cause the system to:
combine the buffer with the selected projection dataset by appending data contents of the buffer to data contents of the selected projection dataset.
8 . The system of claim 1 , wherein the one or more processors is further configured to execute the program instructions to cause the system to:
combine the buffer with the selected projection dataset by appending less than all data contents of the buffer to data contents of the selected projection dataset based on at least a timestamp associated with the selected projection dataset indicating a time the selected projection dataset was previously updated and one or more timestamps associated with data in the buffer.
9 . The system of claim 1 , wherein the one or more processors is further configured to execute the program instructions to cause the system to:
flush the first data chunk from the buffer.
10 . A computer-implemented method comprising:
accessing a first data chunk comprising an edit to a canonical dataset;
temporarily storing the first data chunk in a buffer;
receiving a query configured to execute on the canonical dataset;
analyzing the query to determine a requested result of the query;
accessing one or more projection datasets stored in one or more non-transitory computer readable storage mediums to determine a selected projection dataset from among the one or more projection datasets to optimize query performance based on referencing projection rules associated with the one or more projection datasets to determine a projection rule that corresponds with the requested result, wherein the selected projection dataset is associated with the projection rule, the projection rule having been pre-applied to the selected projection dataset to obviate applying the projection rule to the selected projection dataset during query execution to reduce query execution time allowing the query to execute faster on the selected projection dataset than on other projection datasets of the one or more projection datasets to which the projection rule has not been pre-applied;
combining the selected projection dataset with the buffer in the one or more non-transitory computer readable storage mediums to form a combined projection dataset with data governed by the projection rule;
rewriting the query to execute on the combined projection dataset instead of the canonical dataset to produce the requested result of the query faster by executing on the combined projection dataset than by executing on the canonical dataset;
executing the rewritten query on the combined projection dataset; and
returning the requested result.
11 . The computer-implemented method of claim 10 further comprising:
transforming the combined projection dataset according to a projection rule set associated with the selected projection dataset.
12 . The computer-implemented method of claim 10 further comprising:
transforming the buffer according to a projection rule set associated with the selected projection dataset, wherein transforming the buffer includes one or more of sorting data contents of the buffer and compacting the data contents of the buffer.
13 . Non-transitory computer-readable media including computer-executable instructions that, when executed by a computing system, cause the computing system to perform operations comprising:
accessing a first data chunk comprising an edit to a canonical dataset;
temporarily storing the first data chunk in a buffer;
receiving a query configured to execute on the canonical dataset;
analyzing the query to determine a requested result of the query;
accessing one or more projection datasets stored in one or more non-transitory computer readable storage mediums to determine a selected projection dataset from among the one or more projection datasets to optimize query performance based on referencing projection rules associated with the one or more projection datasets to determine a projection rule that corresponds with the requested result, wherein the selected projection dataset is associated with the projection rule, the projection rule having been pre-applied to the selected projection dataset to obviate applying the projection rule to the selected projection dataset during query execution to reduce query execution time allowing the query to execute faster on the selected projection dataset than on other projection datasets of the one or more projection datasets to which the projection rule has not been pre-applied;
combining the selected projection dataset with the buffer in the one or more non-transitory computer readable storage mediums to form a combined projection dataset with data governed by the projection rule;
rewriting the query to execute on the combined projection dataset instead of the canonical dataset to produce the requested result of the query faster by executing on the combined projection dataset than by executing on the canonical dataset;
executing the rewritten query on the combined projection dataset; and
returning the requested result.
14 . The non-transitory computer-readable media of claim 13 , wherein the computer-executable instructions, when executed by the computing system, further cause the computing system to perform operations comprising:
transforming the combined projection dataset according to a projection rule set associated with the selected projection dataset.
15 . The non-transitory computer-readable media of claim 13 , wherein the computer-executable instructions, when executed by the computing system, further cause the computing system to perform operations comprising:
transforming the buffer according to a projection rule set associated with the selected projection dataset, wherein transforming the buffer includes one or more of sorting data contents of the buffer and compacting the data contents of the buffer.
16 . The system of claim 1 , wherein the projection rule includes one or more of a data sorting rule, a data format rule, or a data aggregation rule.
17 . The computer-implemented method of claim 10 , wherein the projection rule includes one or more of a data sorting rule, a data format rule, or a data aggregation rule.
18 . The non-transitory computer-readable media of claim 13 , wherein the projection rule includes one or more of a data sorting rule, a data format rule, or a data aggregation rule.