Join Recommendation System Using Semantic Similarity and Optimization

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

In a data lakehouse, identifying the most appropriate sub-datasets to join for further analysis is challenging due to the complexity of determining relationships among variously formatted relational datasets, such as CSV, Apache Parquet, and JSON, as existing methods lack an effective means to identify suitable sub-datasets for joining.

Innovation Solution

A computer-implemented method recommends join operations by identifying relationships between column pairs of tables based on semantic similarity and data content join coverage, calculating join strength, and using a combination optimization algorithm to generate scores for proposed join combinations, with user feedback adjusting object weights to improve accuracy.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Measurement precision

If existing methods are used to identify relationships among relational datasets, then the process is simple, but the accuracy of identifying appropriate sub-datasets for joining is poor

Engineering Contradiction:

Improveaccuracy of identifying appropriate sub-datasetsVSAvoidcomplexity of determining relationships among variously formatted datasets

Core Design Contradiction:

Measurement precisionVSDevice complexity

Solution Approach 1:

The patent introduces an intermediary optimization model that mediates between the raw relational datasets and the join recommendations. This model processes multiple factors including schema information, data types, and statistical properties to generate accurate join recommendations without requiring direct complex analysis of all dataset combinations.

Inventive Principle:

Principle #24Intermediary (Mediator)

Solution Approach 2:

The patent transforms the complex problem of identifying relationships among variously formatted datasets into a series of parameter comparisons. By changing the representation of datasets into standardized parameters (schema structure, data type distributions, statistical moments), the system can accurately compare and match sub-datasets across different formats.

Inventive Principle:

Principle #35Parameter changes

2Productivity

If manual methods are used to determine join operations, then the control is flexible, but the time required for analysis is excessive

Engineering Contradiction:

Improvespeed of identifying join operationsVSAvoidtime required for data analysis

Core Design Contradiction:

ProductivityVSLoss of time

Solution Approach 1:

The patent performs preliminary actions by pre-processing and indexing the relational datasets to extract and store schema information, data type distributions, and statistical properties. This preparation enables rapid join recommendations without requiring time-consuming analysis during the actual join determination process.

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The patent replaces manual mechanical analysis with an automated optimization model that uses mathematical algorithms to efficiently determine join operations. The system substitutes human manual comparison with computational methods that can process multiple datasets simultaneously and generate recommendations instantly.

Inventive Principle:

Principle #28Mechanics substitution (Replace mechanical system)

3Adaptability or versatility

If comprehensive join analysis is performed, then the completeness of join options is high, but the complexity of the system increases

Engineering Contradiction:

Improvecompleteness of join optionsVSAvoidcomplexity of determining relationships among datasets

Core Design Contradiction:

Adaptability or versatilityVSDevice complexity

Solution Approach 1:

The patent segments the complex task of analyzing all possible join combinations into smaller, manageable components. By breaking down the analysis into individual factor evaluations (schema matching, data type compatibility, statistical properties), the system can comprehensively explore join options while maintaining low complexity through modular processing.

Inventive Principle:

Principle #1Segmentation

Data Source

PatentUS20240320223A1Recommending join operations of relational data among tables based on optimization model

Publication Date: 2024.09.26 INTERNATIONAL BUSINESS MACHINE CORPORATION

AI summary

A computer-implemented method, system and computer program product for recommending join operations of relational data among different tables. Relationships between one or more pairs of columns of relational data for one or more pairs of tables are identified by determining the semantic similarity between each pair of columns of relational data. The data content join converge for each of the identified relationships in connection with joining the tables involved in such identified relationships is determined. Furthermore, a join strength (indication of the degree that the relational data in the paired columns match) is calculated for each of the identified relationships based on the semantic similarity and the data content join coverage for such identified relationships. Based on the calculated join strength as well as other factors, a combination optimization algorithm identifies the best join combinations of relational data of the tables among a set of tables.