Method and system for executing sql queries in nosql databases

Abstract

The application discloses a method and system for executing SQL query in NoSQL database, the method extracts the unique semantics of NoSQL into standardized rule slot plug-ins and registers the rule slot plug-ins into a central repository, and binds all the rule slot plug-ins with a SQL parser, the SQL parser can automatically query the corresponding rule slot plug-in for semantic compensation in the central repository according to the current SQL query and the binding result, and the rule slot plug-in can be automatically instantiated during parsing, so that the standard SQL and NoSQL extension can be seamlessly written in the same statement, the seamless completion from the normal SQL to the complete SQL with the unique semantics of NoSQL is realized, any unique semantics of NoSQL can be expressed with zero loss, the migration cost and maintenance cost from NoSQL to SQL are significantly reduced, and analysts can directly use the standard SQL to query NoSQL data in the NoSQL database.

Description

Technical Field

[0001] This invention relates to the field of data query technology, and in particular, to a method and system for executing SQL queries in a NoSQL database, an electronic device, and a computer-readable storage medium. Background Technology

[0002] In recent years, NoSQL (Not Only SQL) has replaced RDBMS (Relational Database Management System) as the core of big data due to its advantages such as horizontal scaling and high throughput. However, it sacrifices transactions, indexes, and the SQL (Structured Query Language) interface, resulting in analysts wanting to use SQL, but platforms only providing API interfaces, making it impossible to directly execute SQL queries in NoSQL databases. To reduce migration costs, the industry is strongly promoting the "SQL on NoSQL" technical approach. However, because NoSQL databases have unique semantics, such as TTL (Time to Live), column families, and nesting, these semantic characteristics lack direct mapping in traditional SQL. Therefore, how to losslessly compensate for NoSQL semantics within valid SQL has become a critical problem that urgently needs to be solved in executing SQL queries in NoSQL databases.

[0003] Current research often employs hard-coded templates or deep models to directly translate NoSQL's Abstract Syntax Tree (AST) into SQL. For example, Zeng Zhenzhen, in her article "Code Generation Engine Based on SQL Intelligent Parsing," proposed a "dynamic execution + reverse metadata" approach: first, using regular expressions and JDBC (Java Database Connectivity) to parse SQL parameters and column types, then automatically filling in random values ​​for execution, generating data access layer code based on the result set, and outputting it to any path using FreeMarker templates. This method eliminates the need to manually write a DAO (Data Access Object) and can quickly build CRUD prototypes. However, this method only supports single SQL templates and requires pre-generated executable samples; complex business logic still requires manual secondary development. Furthermore, it lacks sufficient support for advanced semantic features such as nested queries and stored procedures, limiting its applicability. Karime Maamari, in her article "End-to-end Text-to-SQL Generation within an Analytics Insight Engine," proposed CTE (Common Table Expressions) decomposition + multiple LLM (Large Language) models. The Text-to-SQL approach called by the Model (Large Language Model) involves breaking down historical SQL into hierarchical CTE examples during the preprocessing stage. During inference, examples, instructions, and patterns are retrieved based on intent. Candidate statements are then generated via CoT (Chain of Thought) + pseudo-SQL, and feedback-driven self-correction and knowledge updates are implemented. This method significantly reduces manual annotation and can produce complex queries within 60 seconds. However, the entire process relies heavily on LLM calls, resulting in high computational costs. The external knowledge set needs continuous maintenance; otherwise, performance will plummet during cross-domain migration. Furthermore, it may still generate incorrect results for advanced semantics such as nesting and windows, indicating insufficient robustness. Therefore, existing methods based on hard-coded templates or deep models cannot directly execute SQL queries in NoSQL databases. Moreover, when new semantics are added to a NoSQL database, the model needs to be retrained or the template rewritten, leading to high migration and maintenance costs, poor generalization ability, and the distortion and loss of key information caused by direct translation flattening. Summary of the Invention

[0004] This invention provides a method and system for executing SQL queries in a NoSQL database, an electronic device, and a computer-readable storage medium. It can express any NoSQL-specific semantics with zero loss, significantly reducing the migration and maintenance costs from NoSQL to SQL, and allowing analysts to directly query NoSQL data in a NoSQL database using standard SQL.

[0005] According to one aspect of the present invention, a method for executing SQL queries in a NoSQL database is provided, comprising the following:

[0006] The unique semantics of NoSQL are extracted into standardized rule slot plugins and registered in the central repository;

[0007] Bind all rule slot plugins to the SQL parser for triggering;

[0008] Based on the trigger binding result, obtain multiple rule slot plugins triggered in the current SQL query, and perform semantic compensation based on multiple rule slot plugins to convert ordinary SQL query statements into SQL query statements with NoSQL-specific semantics;

[0009] Data is retrieved from a NoSQL database using SQL query statements with NoSQL-specific semantics.

[0010] Furthermore, the process of extracting the unique semantics of NoSQL into standardized rule slot plugins includes the following:

[0011] Identify the unique semantic types of NoSQL;

[0012] Set parameter patterns with unique semantics for each category;

[0013] Design SQL compensation templates for each type of unique semantics to convert each type of unique semantics into SQL clauses;

[0014] Design a conflict detector to detect and resolve conflicts between multiple rule slots;

[0015] The parameter patterns and SQL compensation templates with unique semantics for each class are integrated with the conflict detector into a metadata model, and the metadata model is converted into a rule slot plugin in JSON Schema format.

[0016] Furthermore, the process of semantic compensation based on multiple rule slot plugins includes the following:

[0017] Multiple rule slot plugins are instantiated into specific rule slot objects to obtain multiple rule slot instances;

[0018] Generate a conflict-free queue of rule slot instances based on multiple rule slot instances;

[0019] SQL fragment compensation rewriting is performed based on a queue of conflict-free rule slot instances to generate SQL query statements with NoSQL-specific semantics.

[0020] Furthermore, the process of generating a conflict-free queue of rule slot instances based on multiple rule slot instances includes the following:

[0021] Multiple rule slot instances are sorted according to a preset priority sorting rule to generate a rule slot instance queue. The preset priority sorting rule is as follows: all rule slot instances are assigned a priority based on the type, data type, or business logic of the rule slot. The higher the priority, the earlier the instance is sorted. For rule slot instances with the same priority, they are then sorted according to the column family order.

[0022] Perform data type validation on the rule slot instance queue;

[0023] A conflict detector is used to detect and resolve conflicts between multiple rule slot instances, resulting in a queue of conflict-free rule slot instances.

[0024] Furthermore, the process of SQL fragment compensation rewriting based on a conflict-free rule slot instance queue includes the following:

[0025] For each conflict-free rule slot instance, call its corresponding SQL compensation template to convert the corresponding NoSQL-specific semantics into SQL fragments;

[0026] Based on the parameter pattern with NoSQL-specific semantics, placeholders in SQL fragments are replaced with specific parameter values;

[0027] The replaced SQL fragment is injected into the abstract syntax tree of the original SQL, and the abstract syntax tree is reorganized.

[0028] The reorganized abstract syntax tree is converted into a complete SQL query statement with NoSQL-specific semantics.

[0029] Furthermore, after generating the SQL query statement with NoSQL-specific semantics, the following is also included:

[0030] Cost optimization is performed on SQL queries with NoSQL-specific semantics to generate an optimal physical plan, which includes join order, index selection, and column family parallelism.

[0031] Furthermore, after generating the optimal physics plan, the following is also included:

[0032] The optimal physical plan is translated into executable code, and monitoring and feedback are provided during execution.

[0033] In addition, the present invention also provides a system for executing SQL queries in a NoSQL database, comprising:

[0034] The semantic extraction module is used to extract the unique semantics of NoSQL into standardized rule slot plugins and register them in the central repository;

[0035] The trigger binding module is used to bind all rule slot plugins to the SQL parser.

[0036] The semantic compensation module is used to obtain multiple rule slot plugins triggered in the current SQL query based on the trigger binding result, and to perform semantic compensation based on multiple rule slot plugins to convert ordinary SQL query statements into SQL query statements with NoSQL-specific semantics.

[0037] The query execution module is used to perform data queries in NoSQL databases based on SQL query statements with NoSQL-specific semantics and obtain query results.

[0038] In addition, the present invention also provides an electronic device, including a processor and a memory, wherein the memory stores a computer program, and the processor executes the steps of the method described above by calling the computer program stored in the memory.

[0039] In addition, the present invention provides a computer-readable storage medium for storing a computer program in a NoSQL database that executes SQL queries, the computer program performing the steps of the method described above when running on a computer.

[0040] The present invention has the following beneficial effects:

[0041] This invention presents a method for executing SQL queries in NoSQL databases. By extracting NoSQL-specific semantics into standardized rule slot plugins and registering them in a central repository, this encapsulation method completely decouples the compensation logic from the engine core. This allows new semantics to be released without modifying the source code, improving system flexibility and scalability, and providing a standardized template for subsequent semantic compensation, ensuring the consistency and accuracy of the compensation logic. Furthermore, all rule slot plugins are triggered and bound to the SQL parser. The SQL parser can automatically query the corresponding rule slot plugin in the central repository for semantic compensation based on the current SQL query and the trigger binding result. During parsing, the rule slot plugin can be automatically instantiated, so users do not need to worry about the specific implementation details of NoSQL semantics when writing SQL queries. The SQL parser automatically completes the corresponding processing, ensuring that standard SQL and NoSQL extensions can be seamlessly written in the same statement. This achieves seamless completion from ordinary SQL to complete SQL with NoSQL-specific semantics, expressing any NoSQL-specific semantics with zero loss. It significantly reduces the migration and maintenance costs from NoSQL to SQL, allowing analysts to directly query NoSQL data in NoSQL databases using standard SQL.

[0042] In addition, the system of the present invention for executing SQL queries in a NoSQL database also has the above-mentioned advantages.

[0043] In addition to the objectives, features, and advantages described above, the present invention has other objectives, features, and advantages. The invention will now be described in further detail with reference to the figures. Attached Figure Description

[0044] The accompanying drawings, which form part of this application, are used to provide a further understanding of the invention. The illustrative embodiments of the invention and their descriptions are used to explain the invention and do not constitute an undue limitation of the invention. In the drawings:

[0045] Figure 1 This is a flowchart illustrating a preferred embodiment of the method for executing SQL queries in a NoSQL database according to this application;

[0046] Figure 2 yes Figure 1 A schematic diagram of the sub-process of step S1;

[0047] Figure 3 yes Figure 1 A schematic diagram of the sub-process of step S3;

[0048] Figure 4 yes Figure 3 A schematic diagram of the sub-process of step S32;

[0049] Figure 5yes Figure 3 A schematic diagram of the sub-process of step S33;

[0050] Figure 6 This is another flowchart illustrating a preferred embodiment of the method for executing SQL queries in a NoSQL database according to this application;

[0051] Figure 7 This is another flowchart illustrating a preferred embodiment of the method for executing SQL queries in a NoSQL database according to this application;

[0052] Figure 8 This is a schematic diagram of the module structure of a system for executing SQL queries in a NoSQL database, according to another embodiment of this application. Detailed Implementation

[0053] It should be noted that, unless otherwise specified, the embodiments and features described in this application can be combined with each other. This application will now be described in detail with reference to the accompanying drawings and embodiments.

[0054] Reference Figure 1 A preferred embodiment of this application provides a method for executing SQL queries in a NoSQL database, including the following:

[0055] Step S1: Extract the unique semantics of NoSQL into standardized rule slot plugins and register them in the central repository;

[0056] Step S2: Bind all rule slot plugins to the SQL parser for triggering;

[0057] Step S3: Based on the trigger binding result, obtain multiple rule slot plugins triggered in the current SQL query, and perform semantic compensation based on multiple rule slot plugins to convert ordinary SQL query statements into SQL query statements with NoSQL-specific semantics;

[0058] Step S4: Perform data queries in the NoSQL database using SQL query statements with NoSQL-specific semantics to obtain the query results.

[0059] As can be understood, the method for executing SQL queries in a NoSQL database in this embodiment achieves complete decoupling of the compensation logic from the engine core by extracting the unique semantics of NoSQL into standardized rule slot plugins and registering them in a central repository. This encapsulation method not only improves the system's flexibility and scalability but also provides a standardized template for subsequent semantic compensation, ensuring the consistency and accuracy of the compensation logic. Furthermore, all rule slot plugins are triggered and bound to the SQL parser. The SQL parser can automatically query the corresponding rule slot plugin in the central repository for semantic compensation based on the current SQL query and the trigger binding result. During parsing, the rule slot plugin can be automatically instantiated, so users do not need to care about the specific implementation details of NoSQL semantics when writing SQL queries. The SQL parser will automatically complete the corresponding processing, ensuring that standard SQL and NoSQL extensions can be seamlessly written in the same statement. This achieves seamless completion from ordinary SQL to complete SQL with NoSQL-specific semantics, and can express any NoSQL-specific semantics with zero loss. It significantly reduces the migration cost and maintenance cost from NoSQL to SQL, allowing analysts to directly use standard SQL to query NoSQL data in NoSQL databases.

[0060] Among them, such as Figure 2 As shown, in step S1, the process of extracting the unique semantics of NoSQL into standardized rule slot plugins includes the following:

[0061] Step S11: Identify the unique semantic types of NoSQL;

[0062] Step S12: Set the parameter pattern for each type of semantics;

[0063] Step S13: Design SQL compensation templates for each type of unique semantics to convert each type of unique semantics into SQL clauses;

[0064] Step S14: Design a conflict detector to detect and resolve conflicts between multiple rule slots;

[0065] Step S15: Integrate the parameter patterns and SQL compensation templates with each class of unique semantics into a metadata model, and convert the metadata model into a rule slot plugin in JSON Schema format.

[0066] Specifically, by studying the use cases of business logs, HBase (a typical NoSQL database), and JSON data, we identified five high-frequency NoSQL-specific semantics: lifecycle, column families, nested arrays, time-series windows, and JSON objects. We clarified the usage patterns and contexts of each NoSQL-specific semantic, providing a basis for subsequent encapsulation. The specific identification process is existing technology and will not be elaborated upon here.

[0067] Then, for each type of NoSQL-specific semantics, a corresponding parameter pattern is set. The parameter pattern includes the required input parameters and their types. For example, the lifecycle requires a time interval Δt and a timestamp field ts. The parameter pattern can ensure the correct expression of NoSQL-specific semantics in SQL.

[0068] Next, SQL compensation templates with specific semantics for each category are designed. Each SQL compensation template is a rule slot, whereby the SQL compensation template is used to convert NoSQL-specific semantics into SQL clauses. For example, the SQL compensation templates designed in this application are shown in Table 1:

[0069] Table 1. SQL Compensation Template

[0070] .

[0071] Next, a conflict detector is designed to detect and resolve conflicts between multiple rule slots. Specifically, each rule slot is assigned a priority score p. When a conflict is detected, the rule slots are sorted in descending order of priority score p, and conflicting rule slots are merged. The merging function can be expressed as: ,in, and They represent the first i The first rule slot and the first j One rule slot, and Indicates the first i The first rule slot and the first j The priority score for each rule slot. Furthermore, the priority score for each rule slot can be set according to the type of specific semantics. For example, the priority score configurations for the five types of NoSQL specific semantics in this application are shown in Table 2:

[0072] Table 2. Priority Score Configuration Results

[0073] .

[0074] Then, the parameter patterns and SQL compensation templates for each type of NoSQL-specific semantics are integrated with the conflict detector into a metadata model. This ensures that each type of NoSQL-specific semantic has a complete definition and processing logic, providing a unified structure for subsequent encapsulation and registration. The metadata model is then converted into rule slot plugins in JSON Schema format, ensuring readability and extensibility. The encapsulated rule slot plugin includes the specific semantic name, parameter pattern, SQL compensation template, and conflict detector. Finally, the encapsulated rule slot plugin is uploaded to a central repository, supporting version control and hot-swapping. This allows new semantics to be seamlessly integrated into the system without modifying the source code, improving the system's flexibility and maintainability.

[0075] It is understood that this application is the first to propose an extensible rule slot mechanism, which abstracts the unique semantics of NoSQL into pluggable templates and registers them into the database. This encapsulation method achieves complete decoupling of the compensation logic from the engine core. When adding semantics, there is no need to modify the parsing core, retrain the model, or rewrite the rules. This not only improves the flexibility and scalability of the system, but also provides a standardized template for subsequent semantic compensation, ensuring the consistency and accuracy of the compensation logic.

[0076] Furthermore, in step S2, to enable the SQL parser to recognize and process the unique semantics of NoSQL, all rule slot plugins need to be triggered and bound to the SQL parser. Specifically, this application extends the syntax layer of the SQL parser by adding keyword branches, such as TTL_AFTER, CFAMILY, and NESTED, to the SQL parser's syntax file. These keywords are pre-declared as exclusive tokens. Once the SQL parser hits a key during the lexical stage, it can trigger the key. token The chained mapping of "custom syntax rules" directly triggers the corresponding CustomSqlCall (customized SQL call) branch. Subsequent token sequences can be bound to the NoSQL semantic processing logic without regular expression backtracking, thus determining whether it belongs to a NoSQL extension while completing syntax analysis. Custom syntax rules refer to syntax logic added to the syntax file to identify NoSQL-specific semantics. For example, defining a noSQL Extension Clause containing branches such as TTL Extension Clause and column Family Clause is used to parse mixed statements of standard SQL and NoSQL extension keywords. First, NoSQL keywords are pre-declared as exclusive tokens (e.g., TTL_AFTER corresponds to a dedicated token) to avoid confusion with standard SQL elements. Then, the dedicated token is explicitly referenced in the custom syntax rules (e.g., the TTLExtension Clause rule binds the TTL_AFTER dedicated token with a sequence of related tokens such as numbers and field names). When the parser scans a matching token sequence, it directly triggers the corresponding custom syntax rule and generates a CustomSqlCall object without regular expression backtracking, simultaneously determining whether it belongs to NoSQL. The determination of "expansion".

[0077] When the SQL parser encounters a new keyword, it first instantiates an empty `CustomSqlCall` object as a placeholder. Then, it continues scanning the remaining tokens in the same clause, filling the `CustomSqlCall` with the extracted TTL value, column family list, nesting path, and other actual parameters. This encapsulates the NoSQL semantic parsing result, facilitating subsequent processing and transformation. The process of generating a `CustomSqlCall` can be represented by the formula: Here, Parser represents the SQL parser, and Keyword represents the newly added keyword. Then, using pattern matching technology, the identified keywords are matched against predefined patterns to determine the corresponding rule slot component name. The predefined pattern refers to the JSON schema fragment generated in step S1 for each type of NoSQL-specific semantics. It declares the literal values ​​of keywords, parameter order, and types. For example, the specific semantic name included in the rule slot plugin will be associated with the corresponding keyword literal (e.g., TTL semantics corresponds to the keyword "TTL_AFTER"). The parameter pattern specifies the parameter order and type. This information is further organized into a predefined pattern in the form of a JSON schema fragment for the SQL parser's matching logic. In short, the predefined pattern is a structured information fragment extracted from the specific semantic name and parameter pattern of the rule slot plugin, adapted to the pattern matching scenario. At runtime, the SQL parser only needs to compare the current keyword with this schema fragment to uniquely identify the rule slot name. Pattern matching can be represented as: SlotName represents the rule slot name, Match represents the matching function, and Pattern represents the predefined pattern. Next, based on the matched rule slot name, the corresponding rule slot template is retrieved from the central repository and instantiated into a specific rule slot object. The instantiated rule slot object contains all necessary parameters and compensation logic, preparing for subsequent semantic compensation. The instantiation process can be represented by the formula: SlotInstance represents the instantiated rule slot object, Instantiate represents the instantiation function, and Template represents the rule slot plugin. Finally, the instantiated rule slot object is automatically hooked into the SQL parser's processing flow, ensuring that the rule slot's compensation logic is automatically triggered during parsing. This process achieves seamless integration of standard SQL and NoSQL extensions. Users do not need to worry about the specific implementation details of NoSQL semantics when writing SQL queries; the parser will automatically handle the corresponding processing. This automatic hooking can be expressed by the formula: Hook stands for hook function, which ensures that the rule slot object is correctly triggered in the parser.

[0078] Understandably, this application adds keyword branches to the syntax file of the SQL parser. When the SQL parser encounters these keywords, it can quickly find the corresponding rule slot plugin in the central repository and automatically instantiate it through pattern matching technology and pattern-slot name hash table. This enables all rule slot plugins to be triggered and bound to the SQL parser, so that users do not need to care about the specific implementation details of NoSQL semantics when writing SQL queries. The SQL parser will automatically complete the corresponding processing, ensuring that standard SQL and NoSQL extensions can be written seamlessly in the same statement. This trigger binding mechanism not only improves the user experience, but also provides a reliable trigger mechanism for subsequent semantic compensation.

[0079] In addition, in step S3, the SQL parser will find multiple corresponding rule slot plugins from the central repository based on the current SQL query and the trigger binding result in step S2, and perform semantic compensation based on the multiple rule slot plugins, thereby converting the ordinary SQL query statement into an SQL query statement with NoSQL-specific semantics.

[0080] Among them, such as Figure 3 As shown, the process of semantic compensation based on multiple rule slot plugins includes the following:

[0081] Step S31: Instantiate multiple rule slot plugins into specific rule slot objects to obtain multiple rule slot instances;

[0082] Step S32: Generate a conflict-free queue of rule slot instances based on multiple rule slot instances;

[0083] Step S33: Perform SQL fragment compensation rewriting based on the conflict-free rule slot instance queue to generate SQL query statements with NoSQL-specific semantics.

[0084] The SQL parser automatically instantiates multiple rule slot components into specific rule slot objects, resulting in multiple rule slot instances. Since a query may activate multiple rule slot instances simultaneously—for example, a query might involve both TTL and column family semantics—to avoid conflicts between these rule slots, the activated rule slot instances need to be sorted and merged to generate a queue of conflict-free rule slot instances. For example, ... Figure 4 As shown, the process of generating a conflict-free queue of rule slot instances based on multiple rule slot instances includes the following:

[0085] Step S321: Sort multiple rule slot instances according to a preset priority sorting rule to generate a rule slot instance queue. The preset priority sorting rule is as follows: set a priority for all rule slot instances based on the type, data type, or business logic of the rule slot. The higher the priority, the earlier the instance is sorted. For rule slot instances with the same priority, sort them according to the column family order.

[0086] Step S322: Perform data type validation on the rule slot instance queue;

[0087] Step S323: Use a conflict detector to detect and resolve conflicts between multiple rule slot instances to obtain a queue of conflict-free rule slot instances.

[0088] Specifically, multiple rule slot instances are first sorted according to a preset priority sorting rule. This preset priority sorting rule assigns a priority to all rule slot instances based on their type, data type, or business logic. Higher priority instances are sorted earlier. For example, rule slots corresponding to TTL have a higher priority because they directly affect the timeliness of the data. The sorting process can be represented as follows: SortedSlots represents the queue of rule slot instances sorted according to priority rules; Sort represents the sorting function; ActivatedSlots represents the set of activated rule slot instances; and Priority represents the priority rule, which can be set according to actual needs and is not specifically limited here. Furthermore, for rule slot instances with the same priority, the sorting is adjusted according to column family order. Column family order is usually defined based on the physical structure or business logic of the database table. For example, in HBase, the order of column families may affect data storage and access efficiency. The sorting adjustment process can be represented as follows: AdjustedSlots represents the collection of rule slot instances after column family order adjustment, Adjust represents the adjustment function, and ColumnFamilyOrder represents the column family order rule, which can be set according to actual needs.

[0089] Then, data type validation is performed on the queue of rule slot instances to ensure that the parameter types of each slot instance meet expectations. Data type validation can prevent runtime errors caused by type mismatches. The validation process can be represented as follows: Where ValidSlots represents the queue of rule slot instances that have been validated and confirmed to be valid after data type validation, Validate represents the validation function, and DataType represents the expected data type.

[0090] Next, the conflict detector of the rule slot instance is invoked to detect and resolve potential conflicts. Conflicts may occur when multiple rule slot instances act on the same field or data type. The conflict detector handles conflicts according to a predefined merging strategy (i.e., the merging function in step S1), employing a priority overriding strategy. That is, when two rule slot instances conflict, the rule slot instance with higher priority will override the settings of the rule slot instance with lower priority. The rule slot merging process can be represented as follows: Here, MergedSlots represents the queue of merged rule slot instances after conflict detection and resolution, Merge represents the merge function, and ConflictResolver represents the conflict detector. Finally, a queue of conflict-free rule slot instances is generated for subsequent SQL fragment compensation rewriting. This queue ensures that each rule slot instance can correctly apply its compensation logic without interfering with each other. The generation process can be represented as follows: , where ConflictFreeSlots represents the final queue of conflict-free rule slot instances, and Finalize represents the finalization function.

[0091] Additionally, after generating a queue of conflict-free rule slot instances, it is necessary to convert NoSQL-specific semantics based on the SQL compensation template into executable SQL clauses and inject them into the original SQL statement for SQL fragment compensation rewriting, generating SQL query statements with NoSQL-specific semantics. For example, Figure 5 As shown, the process of SQL fragment compensation rewriting based on a conflict-free rule slot instance queue includes the following:

[0092] Step S331: For each conflict-free rule slot instance, call its corresponding SQL compensation template to convert the corresponding NoSQL-specific semantics into SQL fragments;

[0093] Step S332: Based on the parameter pattern with NoSQL-specific semantics, replace the placeholders in the SQL fragment with specific parameter values;

[0094] Step S333: Inject the replaced SQL fragment into the abstract syntax tree of the original SQL, and reorganize the abstract syntax tree;

[0095] Step S334: Convert the reorganized abstract syntax tree into a complete SQL query statement with NoSQL-specific semantics.

[0096] Specifically, for each conflict-free rule slot instance, its corresponding SQL compensation template is invoked to convert the corresponding NoSQL-specific semantics into SQL fragments. For example, for TTL, the invoked template will convert the time filtering conditions into SQL expressions. The invocation process can be represented as follows: Here, SQLFragment represents the SQL clause (i.e., SQL fragment) generated from a predefined SQL compensation template called from a conflict-free rule slot instance, Render represents the template rendering function, and SlotInstance represents the rule slot instance.

[0097] Then, in the generated SQL fragment, based on the parameter pattern corresponding to the rule slot instance, the placeholders in the SQL fragment are replaced with specific parameter values. For example, for TTL, the time interval placeholder is replaced with the actual time interval value. The parameter replacement process can be expressed by the formula: In this context, Replace represents the parameter replacement function, and Parameters represents the specific parameter values.

[0098] Next, the replaced SQL fragment is dynamically injected into the abstract syntax tree of the original SQL. This ensures that the SQL fragment can be correctly integrated into the original SQL query. The injection process can be represented as follows: Here, Updated AST represents the updated SQL Abstract Syntax Tree (AST) after the injection of SQL fragments, Inject represents the fragment injection function, and Original AST represents the original SQL Abstract Syntax Tree. The AST is then reorganized to ensure its correct structure and compliance with SQL syntax rules. This reorganization process can be represented by the following formula: Wherein, Reorganized AST represents the reorganized SQL abstract syntax tree, and Reorganize represents the syntax tree reorganization function.

[0099] Finally, the reconstructed abstract syntax tree is converted into a complete SQL query statement, resulting in a complete SQL query statement with NoSQL-specific semantics. This ensures that the compensated SQL query statement is not only syntactically correct but also accurately expresses the NoSQL-specific semantics. The conversion process can be represented as follows: In this context, FinalSQL represents the final generated SQL query statement, and Generate represents the SQL generation function.

[0100] It is understandable that this application performs semantic compensation based on multiple rule slot plugins triggered by the execution chain, securely embedding multiple rule slot instances into the execution chain. This achieves seamless completion between ordinary SQL queries and SQL queries with NoSQL-specific semantics, solving the problem of the lack of direct mapping between traditional SQL and NoSQL-specific semantics. It breaks the syntactic gap of "SQL on NoSQL", allowing ordinary SQL queries written by users to be executed directly on NoSQL databases without additional modifications by users. This enables analysts to use standard SQL to query NoSQL data such as TTL, column families, and nested documents, while fully preserving field lineage and optimization capabilities. This not only improves the user experience but also provides an accurate semantic foundation for subsequent query execution.

[0101] In addition, in step S4, based on the SQL query statement with NoSQL-specific semantics generated in step S3, data can be queried in the NoSQL database to obtain query results. Analysts can directly use standard SQL to query NoSQL data such as TTL, column families, and nested documents.

[0102] Optional, such as Figure 6 As shown, the method for executing SQL queries in a NoSQL database, after generating an SQL query statement with NoSQL-specific semantics, also includes the following:

[0103] Step S3a: Optimize the cost of SQL queries with NoSQL-specific semantics to generate an optimal physical plan, which includes join order, index selection, and column family parallelism.

[0104] Specifically, the cost of a SQL query with NoSQL-specific semantics (referred to as a full SQL query) is typically calculated based on the following formula: Where C represents the cost, R represents the number of rows queried, I represents the number of I / O operations, Q represents the computational complexity, and α, β, and γ represent weighting coefficients, which are adjusted according to actual needs. Specifically, the number of rows R queried is adjusted in this application based on the row count trimming factor provided by the rule slots, resulting in the following adjusted number of rows: Where R' represents the adjusted number of rows, and f represents the row pruning factor, determined based on historical data and statistical information, used to reduce the number of rows in the query, thereby reducing I / O operations and computational complexity. Furthermore, this application also modifies the cost based on the column family parallelism p provided by the rule slot. Column family parallelism p represents the number of threads that can execute the query in parallel. Increasing column family parallelism can significantly reduce the query execution time. The specific modification formula is: C' = C / p, where C' represents the modified cost.

[0105] Then, the join order and index selection are recalculated based on the corrected and optimized costs to generate the optimal physical plan, which includes the join order, index selection, and column family parallelism. The optimized join order can be expressed as: , Let $n$ represent the cost of the $i$-th join operation, and $n$ represent the number of join operations. This indicates the optimized join order. Additionally, index selection optimization can be represented as: , Let represent the cost of using the j-th index, and m represent the number of indices used. This indicates the optimized index selection. Furthermore, the specific join order optimization and index selection optimization are existing technologies, and their detailed principles will not be elaborated upon here.

[0106] It is understandable that this application uses the row number pruning factor and column family parallelism provided by the rule slots to correct the cost and then re-optimize the join order and index selection, thereby generating the optimal physical plan, ensuring that the query can achieve the best performance during execution, improving query efficiency and the overall performance of the system, and making the execution of SQL queries on NoSQL databases both accurate and efficient.

[0107] In addition, such as Figure 7 As shown, the method for executing SQL queries in a NoSQL database, after generating the optimal physical plan, also includes the following:

[0108] Step S3b: Convert the optimal physical plan into executable code and monitor and provide feedback during execution.

[0109] Specifically, the optimal physical plan is converted into specific SQL or NoSQL query code to ensure that the physical plan can be correctly executed by the database engine. The conversion formula can be expressed as: Q=T(P), where Q represents the final generated query code, P represents the optimized physical plan, and T represents the conversion operation. The specific conversion process is existing technology and can be completed by the query compiler of the database management system; the specific principles will not be elaborated here. Furthermore, during the query process, key performance indicator data, such as query latency D, row count R, and memory usage M, is collected using data tracking technology and written to a central repository. Based on the key performance indicator data, it is determined whether to trigger hot updates of rule slot plugins to adapt to constantly changing query loads and data patterns. The priority of rule slots can also be automatically adjusted based on key performance indicator data to optimize the execution efficiency of future queries, thus forming a monitoring-driven continuous optimization closed loop. This monitoring feedback mechanism ensures that the system can self-optimize based on actual operating conditions, achieving zero-downtime optimization and ensuring continuous improvement in query performance and efficient system operation. The specific hot update rules and priority adjustment rules can be set according to actual needs and will not be elaborated here.

[0110] In addition, such as Figure 8 As shown, another embodiment of the present invention also provides a system for executing SQL queries in a NoSQL database, preferably employing the method for executing SQL queries in a NoSQL database as described above, including:

[0111] The semantic extraction module is used to extract the unique semantics of NoSQL into standardized rule slot plugins and register them in the central repository;

[0112] The trigger binding module is used to bind all rule slot plugins to the SQL parser.

[0113] The semantic compensation module is used to obtain multiple rule slot plugins triggered in the current SQL query based on the trigger binding result, and to perform semantic compensation based on multiple rule slot plugins to convert ordinary SQL query statements into SQL query statements with NoSQL-specific semantics.

[0114] The query execution module is used to perform data queries in NoSQL databases based on SQL query statements with NoSQL-specific semantics and obtain query results.

[0115] As can be understood, the system in this embodiment that executes SQL queries in a NoSQL database achieves complete decoupling of the compensation logic from the engine core by extracting the unique semantics of NoSQL into standardized rule slot plugins and registering them in a central repository. This encapsulation method not only improves the system's flexibility and scalability but also provides a standardized template for subsequent semantic compensation, ensuring the consistency and accuracy of the compensation logic. Furthermore, all rule slot plugins are triggered and bound to the SQL parser. The SQL parser can automatically query the corresponding rule slot plugin in the central repository for semantic compensation based on the current SQL query and the trigger binding result. During parsing, the rule slot plugin can be automatically instantiated, so users do not need to worry about the specific implementation details of NoSQL semantics when writing SQL queries. The SQL parser will automatically complete the corresponding processing, ensuring that standard SQL and NoSQL extensions can be seamlessly written in the same statement. This achieves seamless completion from ordinary SQL to complete SQL with NoSQL-specific semantics, and can express any NoSQL-specific semantics with zero loss. It significantly reduces the migration cost and maintenance cost from NoSQL to SQL, allowing analysts to directly use standard SQL to query NoSQL data in NoSQL databases.

[0116] In addition, another embodiment of the present invention provides an electronic device including a processor and a memory, wherein the memory stores a computer program, and the processor executes the steps of the method described above by calling the computer program stored in the memory.

[0117] In addition, another embodiment of the present invention provides a computer-readable storage medium for storing a computer program in a NoSQL database that performs SQL queries, the computer program executing the steps of the method described above when running on a computer.

[0118] Common computer-readable storage media include: floppy disks, flexible disks, hard disks, magnetic tapes, any other magnetic media, CD-ROMs, any other optical media, punch cards, paper tape, any other physical media with perforated patterns, random access memory (RAM), programmable read-only memory (PROM), erasable programmable read-only memory (EPROM), flash erasable programmable read-only memory (FLASH-EPROM), any other memory chips or cartridges, or any other media readable by a computer. Instructions may further be transmitted or received by a transmission medium. The term transmission medium can include any tangible or intangible medium used to store, encode, or carry instructions for execution by a machine, and includes digital or analog carrier communication signals or intangible media that facilitate communication of such instructions. Transmission media include coaxial cables, copper wires, and optical fibers, which contain conductors for transmitting a bus of computer data signals.

[0119] Those skilled in the art will understand that embodiments of this application can be provided as methods, systems, or computer program products. Therefore, this application can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, this application can take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code. The solutions in the embodiments of this application can be implemented in various computer languages, such as the object-oriented programming language Java and the interpreted scripting language JavaScript.

[0120] This application is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of this application. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart... Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.

[0121] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.

[0122] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.

[0123] Although preferred embodiments of this application have been described, those skilled in the art, upon learning the basic inventive concept, can make other changes and modifications to these embodiments. Therefore, the appended claims are intended to be interpreted as including the preferred embodiments as well as all changes and modifications falling within the scope of this application.

[0124] Obviously, those skilled in the art can make various modifications and variations to this application without departing from the spirit and scope of this application. Therefore, if such modifications and variations fall within the scope of the claims of this application and their equivalents, this application also intends to include such modifications and variations.

[0125] The above description is merely a preferred embodiment of the present invention and is not intended to limit the invention. Various modifications and variations can be made to the present invention by those skilled in the art. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of the present invention should be included within the scope of protection of the present invention.

Claims

1. A method for executing SQL queries in a NoSQL database, characterized in that, Includes the following: The unique semantics of NoSQL are extracted into standardized rule slot plugins and registered in the central repository; Bind all rule slot plugins to the SQL parser for triggering; Based on the trigger binding result, obtain multiple rule slot plugins triggered in the current SQL query, and perform semantic compensation based on multiple rule slot plugins to convert ordinary SQL query statements into SQL query statements with NoSQL-specific semantics; Data is retrieved from a NoSQL database using SQL query statements with NoSQL-specific semantics. The process of extracting NoSQL-specific semantics into standardized rule slot plugins includes the following: Identify the unique semantic types of NoSQL; Set parameter patterns with unique semantics for each category; Design SQL compensation templates for each type of unique semantics to convert each type of unique semantics into SQL clauses; Design a conflict detector to detect and resolve conflicts between multiple rule slots; The parameter patterns and SQL compensation templates with unique semantics for each class are integrated with the conflict detector into a metadata model, and the metadata model is converted into a rule slot plugin in JSON Schema format.

2. The method for executing SQL queries in a NoSQL database as described in claim 1, characterized in that, The process of semantic compensation based on multiple rule slot plugins includes the following: Multiple rule slot plugins are instantiated into specific rule slot objects to obtain multiple rule slot instances; Generate a conflict-free queue of rule slot instances based on multiple rule slot instances; SQL fragment compensation rewriting is performed based on a queue of conflict-free rule slot instances to generate SQL query statements with NoSQL-specific semantics.

3. The method for executing SQL queries in a NoSQL database as described in claim 2, characterized in that, The process of generating a conflict-free queue of rule slot instances based on multiple rule slot instances includes the following: Multiple rule slot instances are sorted according to a preset priority sorting rule to generate a rule slot instance queue. The preset priority sorting rule is as follows: all rule slot instances are assigned a priority based on the type, data type, or business logic of the rule slot. The higher the priority, the earlier the instance is sorted. For rule slot instances with the same priority, they are then sorted according to the column family order. Perform data type validation on the rule slot instance queue; A conflict detector is used to detect and resolve conflicts between multiple rule slot instances, resulting in a queue of conflict-free rule slot instances.

4. The method for executing SQL queries in a NoSQL database as described in claim 2, characterized in that, The process of SQL fragment compensation and rewriting based on a conflict-free rule slot instance queue includes the following: For each conflict-free rule slot instance, call its corresponding SQL compensation template to convert the corresponding NoSQL-specific semantics into SQL fragments; Based on the parameter pattern with NoSQL-specific semantics, placeholders in SQL fragments are replaced with specific parameter values; The replaced SQL fragment is injected into the abstract syntax tree of the original SQL, and the abstract syntax tree is reorganized. The reorganized abstract syntax tree is converted into a complete SQL query statement with NoSQL-specific semantics.

5. The method for executing SQL queries in a NoSQL database as described in claim 1, characterized in that, After generating the SQL query with NoSQL-specific semantics, the following is also included: Cost optimization is performed on SQL queries with NoSQL-specific semantics to generate an optimal physical plan, which includes join order, index selection, and column family parallelism.

6. The method for executing SQL queries in a NoSQL database as described in claim 5, characterized in that, After generating the optimal physics plan, the following is also included: The optimal physical plan is translated into executable code, and monitoring and feedback are provided during execution.

7. A system for executing SQL queries in a NoSQL database, employing the method for executing SQL queries in a NoSQL database as described in any one of claims 1 to 6, characterized in that, include: The semantic extraction module is used to extract the unique semantics of NoSQL into standardized rule slot plugins and register them in the central repository; The trigger binding module is used to bind all rule slot plugins to the SQL parser. The semantic compensation module is used to obtain multiple rule slot plugins triggered in the current SQL query based on the trigger binding result, and to perform semantic compensation based on multiple rule slot plugins to convert ordinary SQL query statements into SQL query statements with NoSQL-specific semantics. The query execution module is used to perform data queries in NoSQL databases based on SQL query statements with NoSQL-specific semantics and obtain query results.

8. An electronic device, characterized in that, The method includes a processor and a memory, wherein the memory stores a computer program, and the processor executes the steps of the method as described in any one of claims 1 to 6 by calling the computer program stored in the memory.

9. A computer-readable storage medium for storing a computer program that executes SQL queries in a NoSQL database, characterized in that, The computer program, when run on a computer, performs the steps of the method as described in any one of claims 1 to 6.

Images