Hyperloglog Sketches for Inclusion Coefficient Estimation

Resolve Bottlenecks,
Find Innovative Solutions
Generate Solutions

Solution Overview

Problem

Existing inclusion coefficient estimation techniques, such as Bottom-k sketches, become inaccurate as the number of distinct values in database columns exceeds a given memory budget and are not amenable to incremental maintenance, especially in data warehousing scenarios where data is frequently added or removed.

Innovation Solution

The use of Hyperloglog (HLL) sketches with the Binomial Mean Lookup (BML) estimator, which efficiently estimates inclusion coefficients within a bounded memory budget and supports incremental maintenance by maintaining HLL sketches with a constant memory overhead, using a single hash function for each value and employing a max-heap for deletions.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Measurement precision

If Bottom-k sketch is used for inclusion coefficient estimation, then accuracy is good when cardinality is small, but estimation error becomes larger when cardinality exceeds memory budget

Engineering Contradiction:
Improveinclusion coefficient estimation accuracyVSAvoidcardinality of column
Core Design Contradiction:
Measurement precisionVSQuantity of substance

Solution Approach 1:

The patent changes the fundamental parameter of the sketch data structure from Bottom-k to Hyperloglog. Hyperloglog sketches use probabilistic counting with logarithmic space complexity, allowing accurate estimation even when cardinality exceeds the memory budget. The estimation accuracy is maintained through mathematical properties of hyperloglog sketches that provide constant-factor approximation regardless of the relationship between cardinality and memory size.

Inventive Principle:
Principle #35Parameter changes

2Reliability

If Bottom-k sketch is used for inclusion coefficient estimation, then it works for static data, but it is not amenable to incremental maintenance when data is deleted

Engineering Contradiction:
Improveinclusion coefficient estimation reliabilityVSAvoidincremental maintenance capability
Core Design Contradiction:
ReliabilityVSEase of operation

Solution Approach 1:

The patent makes the sketch data structure dynamic by enabling incremental updates for both insertions and deletions. The hyperloglog sketch maintains the ability to update efficiently: for insertions, it simply adds the new element to the sketch; for deletions, it uses a auxiliary structure (such as a heap or counter) to track and remove elements. This dynamic capability allows the sketch to maintain estimation accuracy over time as data is added or removed from the database.

Inventive Principle:
Principle #15Dynamics

3Device complexity

If Bottom-k sketch is used, then it provides simple structure, but it becomes computationally expensive to maintain when data is deleted

Engineering Contradiction:
Improvesketch data structure complexityVSAvoidmaintenance speed when data is deleted
Core Design Contradiction:
Device complexityVSProductivity

Solution Approach 1:

The patent implements dynamic maintenance with efficient time complexity. For insertions, the operation is O(1). For deletions, the patent uses a auxiliary structure (such as a max-heap or counter array) that allows deletion in O(log k) or O(1) time. This dynamic design ensures that maintenance operations remain computationally efficient even as data changes over time, significantly improving productivity compared to recomputing the entire sketch.

Inventive Principle:
Principle #15Dynamics

Data Source

PatentUS10824592B2Database management using hyperloglog sketches
Publication Date: 2020.11.03 MICROSOFT TECHNOLOGY LICENSING LLC
  • US10824592B2 patent drawing
  • US10824592B2 patent drawing
  • US10824592B2 patent drawing

AI summary

Generally discussed herein are devices, systems, and methods for database management. A method may include determining a first hyperloglog (HLL) sketch of a first column of data, determining a second HLL sketch of a second column of data, estimating an inclusion coefficient based on the first and second HLL sketches, and performing operations on the first column of data or the second column of data in response to determining the inclusion coefficient is greater than, or equal to, a specified threshold.