Cross-Database Query Engine with Fixed Datatype Conversion
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Cross-database queries that operate on both relational and NoSQL databases face challenges due to differences in schema and data types, leading to incompatibilities and difficulties in handling syntactically identical but incompatible SQL and NoSQL expressions.
Innovation Solution
A query engine is configured to generate a query execution plan that optimizes cross-database queries by identifying and differentiating between SQL and NoSQL operations, converting intermediate results to a fixed datatype, and resolving NoSQL path expressions at the NoSQL database, ensuring compatibility and proper data processing across database types.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If cross-database queries operate on both relational and NoSQL databases using their native query languages, then each database can process data according to its own schema and data types, but syntax incompatibilities and data type mismatches prevent effective query execution and data integration
Solution Approach 1:
The patent introduces an intermediary translation layer that converts NoSQL query syntax into equivalent SQL syntax. The translation module acts as a mediator between the NoSQL database engine and the SQL-based query processor, transforming path expressions and projection operations from NoSQL into SQL-compatible forms, thereby enabling unified query execution without direct compatibility between the two query languages
Solution Approach 2:
The system dynamically changes data type parameters during query execution. When NoSQL data with varying data types is retrieved, the system determines appropriate SQL data types based on the operation context (e.g., converting to numeric types for aggregate operations, to strings for text operations) and performs type casting to ensure compatibility between NoSQL results and SQL operations
2Ease of operation
If NoSQL path expressions are resolved at the relational database, then unified query execution can be achieved, but schema mismatches and missing column definitions cause resolution failures
Solution Approach 1:
The patent segments the query execution process into distinct phases: NoSQL path expression resolution at the NoSQL database engine, retrieval of intermediate results, and subsequent SQL operation execution at the relational database. This segmentation allows each database system to operate within its own schema context while maintaining overall query coherence through the translation layer
Solution Approach 2:
The translation module serves as an intermediary that captures NoSQL path expressions before they reach the relational database, translates them into SQL-compatible forms, and manages the resolution process. This intermediary prevents schema mismatch errors by ensuring that only SQL-compatible expressions are submitted to the relational database
3Productivity
If intermediate results from NoSQL operations are directly used in SQL operations, then query processing efficiency can be maintained, but data type incompatibilities prevent successful operation execution
Solution Approach 1:
The system dynamically determines and applies data type conversions for intermediate results based on the specific SQL operation context. For aggregate operations like SUM and COUNT, results are converted to numeric types; for text operations, results are converted to strings. This contextual parameter adjustment ensures data type compatibility while maintaining processing efficiency through targeted rather than universal conversion
Data Source
AI summary
A method can include: generating a query execution plan for a query including a plurality of operations that operate on data from a relational database and data from a non-structured query language (NoSQL) database, the generating comprising optimizing the query by: identifying a first operation that operate on data from the relational database and an intermediate result output by a second operation, the second operation outputting the intermediate result by operating on the data from the NoSQL database; and determining a fixed datatype for the intermediate result, an indication of the fixed datatype being included in the query execution plan; and executing, based on the query execution plan, the query, the executing of the query comprising converting the intermediate result to the fixed datatype, the converting enabling the first operation to operate on the intermediate result output by the second operation along with the data from the relational database.