Distributed SQL Query Processing via Key-Value Storage Mapping

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Traditional SQL database systems are difficult and expensive to scale due to high overhead and costly hardware requirements, while NoSQL systems lack essential features and are not interoperable, increasing development costs and complexity for web applications.

Innovation Solution

A distributed SQL query processing system that layers an SQL library on top of a transactional key-value system, allowing for scalable and fault-tolerant SQL query execution by mapping SQL queries to operations on a key-value system, with concurrency control mechanisms to handle concurrent transactions, and supporting features like transactions, indices, and schemas.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Reliability

If traditional SQL database systems are used, then powerful query functionality and data consistency are provided, but scalability and cost are worsened due to expensive hardware and high overhead

Engineering Contradiction:

Improvedata consistencyVSAvoidscalability

Core Design Contradiction:

ReliabilityVSProductivity

Solution Approach 1:

The system segments the database functionality into two independent layers: an SQL library for query processing and a key-value storage system for data persistence. This segmentation allows the storage layer to scale independently using inexpensive hardware while the SQL layer provides consistent query interfaces, resolving the contradiction between reliability and scalability.

Inventive Principle:

Principle #1Segmentation

Solution Approach 2:

The patent introduces an SQL library as an intermediary layer between the application and the key-value storage system. This intermediary translates SQL queries into key-value operations, enabling traditional SQL functionality to work over a scalable NoSQL backend, thus maintaining data consistency while improving scalability.

Inventive Principle:

Principle #24Intermediary (Mediator)

2Productivity

If NoSQL storage systems are used, then scalability and reduced costs are achieved, but functionality and interoperability are worsened due to lack of SQL features and custom interfaces

Engineering Contradiction:

ImprovescalabilityVSAvoidfunctionality

Core Design Contradiction:

ProductivityVSAdaptability or versatility

Solution Approach 1:

The SQL library provides universal SQL functionality that works across different key-value storage systems. By implementing standard SQL operations (SELECT, INSERT, UPDATE, DELETE, JOINs, aggregations) as a layer over the key-value interface, the system achieves both the scalability of NoSQL and the functional versatility of SQL, allowing applications to use familiar SQL syntax regardless of the underlying storage system.

Inventive Principle:

Principle #6Universality (Multi-functionality)

3Quantity of substance

If NoSQL storage systems are used, then hardware costs are reduced, but development costs increase due to custom interfaces and missing features requiring application-level implementation

Engineering Contradiction:

Improvehardware costVSAvoiddevelopment effort

Core Design Contradiction:

Quantity of substanceVSEase of manufacture

Solution Approach 1:

The SQL library acts as an intermediary that shields applications from the limitations of NoSQL systems. It provides SQL syntax support, joins, aggregations, and other missing features at the library level rather than requiring application-level implementation, thus reducing development effort while maintaining the use of cost-effective NoSQL storage infrastructure.

Inventive Principle:

Principle #24Intermediary (Mediator)

4Reliability

If traditional SQL database systems are used, then data consistency is maintained, but system overhead and administrative complexity increase

Engineering Contradiction:

Improvedata consistencyVSAvoidsystem overhead

Core Design Contradiction:

ReliabilityVSDevice complexity

Solution Approach 1:

The patent extracts the complex data consistency and transaction management logic from the storage layer and places it in the SQL library layer. The key-value storage system handles only simple, consistent operations, while the SQL library implements transactions, concurrency control, and consistency protocols, reducing the overhead burden on the storage system while maintaining data consistency.

Inventive Principle:

Principle #2Taking out (Extraction)

Data Source

PatentUS9268834B2Distributed SQL query processing using key-value storage system

Publication Date: 2016.02.23 MICROSOFT TECHNOLOGY LICENSING LLC

AI summary

Distributed storage systems support SQL while also providing scalability and fault tolerance by utilizing an SQL library (the SQL execution component) layered on top of a transactional key-value system (the storage component). The SQL library comprises a parser and an execution engine running at the client, while the storage component comprises a key-value system for storing the data. The SQL library parses SQL queries received from client applications and maps them to transactions on the key-value system. The key-value system stores data items identified by key and provides concurrency control mechanisms to handle concurrent transactions. The key-value system may be a distributed system with several storage servers, each attached to one or more storage devices.