Database Key Identification Using SetSketch Cardinality Estimation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing methods are inefficient in computationally determining both primary and foreign keys in large datasets, particularly in big data applications, lacking computational efficiency and accuracy.
Innovation Solution
Utilizing SetSketch probabilistic data structures to partition columns into registers, compute cardinality estimates, and compare ratios or inclusion coefficients to thresholds for identifying primary and foreign keys, leveraging efficient memory usage and computational speed.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Quantity of substance
If sketching algorithms (HyperLogLog, Bottom-k) are used for key identification, then memory usage is reduced, but computational efficiency and accuracy for both primary and foreign key detection deteriorate
Solution Approach 1:
The patent segments the key identification process into two distinct phases: primary key detection using HyperLogLog sketches for memory efficiency, and foreign key detection using set-based operations for accuracy. This segmentation allows each phase to use the most appropriate algorithm for its specific task, resolving the contradiction between memory usage and computational efficiency.
Solution Approach 2:
The patent introduces an intermediary set-based representation layer between the sketching algorithm and the final key identification. The HyperLogLog sketches provide cardinality estimates that inform set-based operations, which then perform the actual foreign key detection. This intermediary layer enables both memory efficiency from sketches and accuracy from set operations.
2Extent of automation
If machine learning approaches are used for key identification, then automation is improved, but computational complexity and resource requirements worsen
Solution Approach 1:
The patent implements self-service through automated schema analysis and pattern recognition rules that examine table names, column names, and data types to identify potential key candidates. This automated preliminary analysis reduces manual intervention while maintaining computational efficiency by focusing subsequent detailed analysis only on promising candidates.
3Measurement precision
If complete data checking for duplicates is performed, then accuracy is improved, but computational time and memory consumption worsen
Solution Approach 1:
The patent performs preliminary filtering using HyperLogLog cardinality estimates before conducting complete duplicate checking. By first identifying candidate columns with high cardinality ratios using efficient sketch-based estimation, the system reduces the scope of subsequent expensive exact duplicate checks, thereby maintaining accuracy while reducing computational time.
Solution Approach 2:
The patent employs a two-stage approach where partial checking via cardinality estimation is performed first on all candidate columns, followed by complete exact duplicate checking only on columns that pass the preliminary filter. This partial action strategy ensures high accuracy for final results while minimizing overall computational time by avoiding exhaustive checking of all columns.
4Measurement precision
If manual definition of keys is used, then accuracy is improved, but ease of operation and adaptability worsen
Solution Approach 1:
The system performs self-service by automatically analyzing database schemas, examining naming conventions, data types, and cardinality patterns to identify and suggest primary and foreign keys. This automated self-service capability maintains high accuracy through multiple verification stages while dramatically improving ease of operation by eliminating manual configuration requirements.
Solution Approach 2:
The system implements feedback mechanisms where initial automated key identification results are validated against database queries and operations. The system learns from successful and unsuccessful key identifications, continuously refining its algorithms and patterns to improve both accuracy and ease of operation over time, adapting to different database schemas and conventions.
Data Source
AI summary
A computer-implemented method is presented for determining primary keys in a table of a database system. The method includes: determining a number of rows in the table; for a given column of the table, generating a probabilistic data structure for the given column, where the probabilistic data structure is partitioned into a plurality of registers and configuration parameters for the probabilistic data structure includes a first recording parameter, base, that controls recording of data into the probabilistic data structure; computing a cardinality estimate for the given column using the probabilistic data structure; computing a ratio between the cardinality estimate for the given column and the number of rows in the table; comparing the ratio to a threshold; and designating the given column as a primary key for the table in response to the ratio being greater than the threshold.