Machine-Learning Database Query Generation for Schema-Change Resilience
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Large enterprises face challenges in maintaining and utilizing ad hoc database queries due to complex data architectures, ever-changing requirements, and the large number of engineers involved, leading to query complexity, invalidation due to database migration or schema updates, and difficulties in reusing query knowledge between team members.
Innovation Solution
A systematic solution is disclosed that includes a query analysis module and an inquiry plan module to auto-generate new investigation/analysis plans, automate query annotation, and update invalid database and field names, employing abstracted query models and machine learning models to manage ad hoc queries and transfer knowledge from previous investigations.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If ad hoc database queries are manually maintained by multiple engineers, then query flexibility and adaptability are improved, but query complexity and error rates increase
Solution Approach 1:
The patent introduces an automated query generation system that acts as an intermediary between user requirements and database queries. The system includes a query template engine, parameter extractor, and validator that automatically generate and validate queries, eliminating manual query writing while maintaining flexibility through configurable templates and parameters.
Solution Approach 2:
The system enables self-service query generation where the automated system maintains and updates queries based on database schema changes without requiring engineer intervention. The query validator and updater components automatically detect schema changes and adjust queries, making the system self-maintaining.
2Reliability
If frequent database queries are executed to ensure service operation and compliance, then service monitoring and compliance detection are improved, but query execution time and system load increase
Solution Approach 1:
The patent implements query validation and generation before execution, and maintains query templates in advance. The system validates query syntax and logic prior to execution, and pre-configures query templates for common monitoring scenarios, reducing execution time during actual monitoring operations.
Solution Approach 2:
The system dynamically adjusts query parameters based on monitoring needs and database state. The parameter extractor automatically identifies and extracts relevant parameters from monitoring requirements, and the query updater modifies query parameters in response to schema changes, optimizing execution efficiency.
3Adaptability or versatility
If complex queries are written to handle ever-changing requirements, then query functionality and adaptability are improved, but query maintenance difficulty and invalidation risk increase
Solution Approach 1:
The patent segments queries into reusable templates with parameterized components. Each query is broken down into modular template elements that can be independently maintained and updated. The system maintains a library of query templates that can be组合 (combined) to address different requirements without rewriting entire queries.
Solution Approach 2:
The query templates are designed to be universal and multi-functional, capable of handling various monitoring and compliance scenarios. A single template can serve multiple purposes by adjusting parameters, reducing the need for multiple specialized queries and simplifying maintenance.
4Adaptability or versatility
If database schema updates are performed to meet new requirements, then database adaptability and functionality are improved, but existing query validity and reliability deteriorate
Solution Approach 1:
The patent implements a feedback mechanism where the system continuously monitors database schema changes and automatically detects their impact on existing queries. The query updater component receives feedback from schema change events, validates affected queries, and automatically updates them to maintain validity and reliability.
Solution Approach 2:
The system performs preliminary validation and update preparation before schema changes take effect. The query updater pre-processes and validates query updates in response to schema changes, ensuring query reliability is maintained throughout the transition.
Data Source
AI summary
Techniques are disclosed relating to automatically generating and analyzing database queries. In various embodiments, a database inquiry assistance system maintains a first machine learning model trained using query history data for a database and a second machine learning model using analysis history for the database. In an embodiment, the system receives from a user system a request for an inquiry into data stored in the database and identifies a sequence of queries for responding to the request, where identifying the sequence of queries includes applying the second machine learning model to the request. The system generates corresponding database query code for implementing one or more of the queries in the sequence of queries, where generating the corresponding database query code includes applying the first machine learning model to descriptors of one or more of the queries, and sends a plan identifying the sequence of queries to the user system.