Workflow driven database partitioning
A database is configured to analyze user queries to dynamically partition the database according to a partition scheme. User queries can be rewritten based on the partition scheme so that, in response to queries, partitions including relevant data are read while partitions including irrelevant data can be skipped, reducing latency. Files can be named according to the partition scheme and stored on respective partitions so that low partition management can be implemented by underlying systems. Blocks within files can be sorted and statistics can be determined. The statistics can be used to find and read relevant blocks and skip irrelevant blocks.
1. A computer system comprising:
a data store comprising:
a plurality of first data each having a first value for a first variable, stored in a first partition, wherein the first partition comprises a plurality of blocks and includes statistics about variables included in the plurality of blocks, and wherein the plurality of blocks is sorted; and
a plurality of second data each having a second value for the first variable, stored in a second partition; and
one or more hardware computer processors configured to execute computer executable instructions to cause the computer system to:
receive a user-written query that does not specify the first variable;
rewrite the user-written query to include the first value for the first variable in a rewritten query;
based at least in part on the first variable in the rewritten query, execute the rewritten query on the first plurality of data stored in the first data partition; and
based at least in part on the statistics, read a first block in the first partition and skip reading a second block in the first partition.
2. The computer system of claim 1 , wherein the statistics include data about values of the first variable.
3. The computer system of claim 1 , wherein the plurality of first data in the first partition are configured to be fetched as the plurality of blocks.
4. The computer system of claim 1 , wherein the plurality of blocks are of a common block size.
5. The computer system of claim 1 , wherein the one or more hardware computer processors are configured to execute computer executable instructions to further cause the computer system to:
based at least in part on the statistics indicating that values of the first variable in the first block include the value for the first variable, read the first block in the first partition; and
based at least in part on the statistics indicating that values of the first variable in the second block do not include the value for the first variable, reading the second block in the first partition.
6. The computer system of claim 1 , wherein the one or more hardware computer processors are configured to execute computer executable instructions to further cause the computer system to:
execute the rewritten query on the first plurality of data stored in the first partition without reading the plurality of second data from the second partition.
7. The computer system of claim 1 , wherein:
a first data file has a first filename and is stored in the first partition, the first filename indicating the first value of the first variable; and
a second data file has a second filename and is stored in the second partition, the second filename indicating the second value of the first variable.
8. The computer system of claim 1 , wherein the one or more hardware computer processors are configured to execute computer executable instructions to further cause the computer system to:
determine a frequency of a presence of the first variable in user-written queries.
9. The computer system of claim 1 , wherein the one or more hardware computer processors are configured to execute computer executable instructions to further cause the computer system to:
determine a frequency of values of the first variable in query results.
10. The computer system of claim 1 , wherein the one or more hardware computer processors are configured to execute computer executable instructions to further cause the computer system to:
determine how frequently the first variable separates query results from data not included in the query results.
11. The computer system of claim 1 , wherein at least the first variable is part of a nested partition scheme, and wherein the one or more hardware computer processors are configured to execute computer executable instructions to further cause the computer system to:
determine the nested partition scheme based at least in part on a frequency of the first variable, wherein the nested partition scheme includes storing data files having the first value for the first variable on one or more partitions that include the first partition and storing data files having the second value for the first variable on one or more different partitions that include the second partition.
12. The computer system of claim 11 , wherein the one or more hardware computer processors are configured to execute computer executable instructions to further cause the computer system to:
rewrite the user-written query to include the first value for the first variable in a rewritten query based at least in part on the first variable being included in the nested partition scheme.
13. The computer system of claim 11 , wherein the nested partition scheme comprises at least two levels of variables used to separate partitions.
14. The computer system of claim 13 , wherein the variables at the higher levels of the nested partition scheme more frequently reduce a search space of the data store by larger amounts than the other variables at the lower levels of the nested partition scheme.
15. The computer system of claim 1 , wherein the first variable is a lower cardinality superset of a user-written variable in the user-written query.
16. The computer system of claim 1 , wherein the first variable is a Boolean variable.
17. The computer system of claim 1 , wherein the first variable is a lower cardinality hash of a user-written variable in the user-written query.
18. The computer system of claim 1 , wherein the one or more hardware computer processors are configured to execute computer executable instructions in order to cause the computer system to:
determine a first partition scheme based at least in part on queries in a first workflow; and
determine a second partition scheme based at least in part on queries in a second workflow,
wherein the data store includes data redundantly stored under both the first partition scheme and under the second partition scheme.
19. A computer-implemented method comprising:
by one or more hardware computer processors executing computer executable instructions:
accessing a data store comprising:
a plurality of first data each having a first value for a first variable, stored in a first partition, wherein the first partition comprises a plurality of blocks and includes statistics about variables included in the plurality of blocks, and wherein the plurality of blocks is sorted; and
a plurality of second data each having a second value for the first variable, stored in a second partition;
receiving a user-written query that does not specify the first variable;
rewriting the user-written query to include the first value for the first variable in a rewritten query;
based at least in part on the first variable in the rewritten query, executing the rewritten query on the first plurality of data stored in the first data partition of the data store; and
based at least in part on the statistics, reading a first block in the first partition and skipping reading a second block in the first partition.
20. A computer program product comprising a non-transitory computer readable storage medium having program instructions embodied therewith, the program instructions executable by one or more processors to cause the one or more processors to:
access a data store comprising:
a plurality of first data each having a first value for a first variable, stored in a first partition, wherein the first partition comprises a plurality of blocks and includes statistics about variables included in the plurality of blocks, and wherein the plurality of blocks is sorted; and
a plurality of second data each having a second value for the first variable, stored in a second partition;
receive a user-written query that does not specify the first variable;
rewrite the user-written query to include the first value for the first variable in a rewritten query;
based at least in part on the first variable in the rewritten query, execute the rewritten query on the first plurality of data stored in the first data partition of the data store; and
based at least in part on the statistics, read a first block in the first partition and skip reading a second block in the first partition.