Histogram with integrated distinct value sketches
Provided are systems and methods for creating histograms with distinct value sketches integrated therein and for query processing based on the histograms with distinct value sketches. In one example, the method may include storing a histogram that comprises a representation of a bucket of data from a database and that includes a distinct value sketch with a distinct value attribute that identifies an estimated number of distinct values within the bucket of data, receiving a database query, generating a query execution plan for the database query based on the distinct value attribute of the bucket within the distinct value sketch embedded within the histogram, and executing the database query on the bucket of data from the database based on the generated query execution plan.
1. A computing system comprising:
a storage configured to store a data set; and
a processor configured to
execute, via a database, a HyperLogLog (HLL) sketch algorithm on the data set which hashes data values in the data set and estimates distinct values in the data set based on hashed data values,
dynamically divide the data set into a plurality of buckets that have different size widths based on a skewed density of the estimated distinct values within the data set and a predefined threshold of distinct values allowed per bucket,
generate a histogram that includes a distinct value sketch with identifiers of the plurality of buckets, the different size widths, and an estimated number of distinct values for each of the plurality of buckets embedded therein, respectively,
receive a database query request,
estimate processing times for a database query corresponding to the database query request based on an estimated number of distinct values of a bucket among the plurality of buckets within the histogram and generate a query execution plan for the database query based on the estimated processing times, and
execute the database query on data from the database based on the generated query execution plan.
2. The computing system of claim 1 , wherein each bucket comprises its own respective distinct value sketch within the histogram including a respective distinct value attribute stored therein.
3. The computing system of claim 2 , wherein the processor is configured to generate the query execution plan for the database query based on distinct value attributes of a plurality of distinct value sketches for the plurality of buckets.
4. The computing system of claim 1 , wherein each bucket among the plurality of buckets represents a range of values on an X axis of a representation of the histogram, and the distinct value sketch includes an aggregated distinct value attribute generated by combining distinct values from a range of rows corresponding to the range of values.
5. The computing system of claim 1 , wherein the processor is further configured to dynamically determine the plurality of different widths of the plurality of buckets such that each bucket has a number of distinct values that is equal to or less than the predefined threshold.
6. The computing system of claim 1 , wherein the processor is further configured to generate, for each respective bucket among the dynamic number of buckets, a distinct value sketch with a distinct value attribute of the respective bucket stored therein.
7. The computing system of claim 1 , wherein the processor is further configured to store a total attribute value that identifies a total number of data rows included in a bucket of data within the distinct value sketch in the histogram.
8. The computing system of claim 1 , wherein the processor is configured to embed the estimated number of distinct values for each of the plurality of buckets within a histogram object that includes a graphic representation of the plurality of buckets.
9. A method comprising:
storing a data set;
executing, via a database, a HyperLogLog (HLL) sketch algorithm on the data set which hashes data values in the data set and estimates a number of distinct values in the data set based on the hashed data values;
dynamically dividing the data set into a plurality of buckets that have different size widths based on a skewed density of the estimated distinct values within the data set and a predefined threshold of distinct values allowed per bucket;
generating a histogram that includes a distinct value sketch with identifiers of the plurality of buckets, the different size widths, and identifiers of an estimated number of distinct values for each of the plurality of buckets embedded therein, respectively;
receiving a database query request;
estimating processing times for a database query corresponding to the database query based on an estimated number of distinct values of a bucket among the plurality of buckets within the histogram and generating a query execution plan for the database query based on the estimated processing times; and
executing the database query on data from the database based on the generated query execution plan.
10. The method of claim 9 , wherein each bucket comprises its own respective distinct value sketch within the histogram including a respective distinct value attribute stored therein.
11. The method of claim 10 , wherein the generating comprises generating the query execution plan for the database query based on distinct value attributes of a plurality of distinct value sketches for the plurality of buckets.
12. The method of claim 9 , wherein each bucket among the plurality of buckets represents a range of values on an X axis of a representation of the histogram, and the distinct value sketch includes an aggregated distinct value attribute generate by combining distinct values from a range of rows corresponding to the range of values.
13. The method of claim 9 , further comprising dynamically determining the plurality of different widths of the plurality of buckets such that each bucket has a number of distinct values that is equal to or less than the predefined threshold.
14. The method of claim 9 , further comprising generating, for each respective bucket among the dynamic number of buckets, a distinct value sketch with a distinct value attribute of the respective bucket stored therein.
15. The method of claim 9 , wherein the method further comprises storing a total attribute value that identifies a total number of data rows included in a bucket within the distinct value sketch in the histogram.
16. The method of claim 9 , wherein the method further comprises embedding the estimated number of distinct values for each of the plurality of buckets within a histogram object that includes a graphic representation of the plurality of buckets.
17. A method comprising:
retrieving a data set comprising a plurality of rows and columns from a database;
executing, via the database, a HyperLogLog (HLL) sketch algorithm on the data set which hashes data values in the data set and estimates of a number of distinct values in the data set based on the hashed data values;
dynamically dividing the data set into a plurality of buckets that have different size widths based on a skewed density of the estimated distinct values within the data set and a predefined threshold of distinct values allowed per bucket;
generating a histogram object that includes a distinct value sketch with identifiers of the plurality of buckets, the different size widths, and an estimated number of distinct values for each of the plurality of buckets embedded therein, respectively; and
storing the histogram in the database.
18. The method of claim 17 , wherein the dynamically dividing comprises dividing the dataset into the plurality of buckets via execution of the HLL sketch algorithm on the data set.
19. The method of claim 17 , wherein the generating further comprises generating a graphic representation of the plurality of buckets and storing the graphic representation within the histogram.
20. The method of claim 17 , wherein the method further comprises generating a query execution plan based on the histogram and executing a database query against the database based on the generated query execution plan.