SQL statement conversion methods, devices, equipment, and storage media

Abstract

This invention discloses a method, apparatus, device, and storage medium for converting SQL statements. The method includes: acquiring a first SQL statement to be converted; performing abstract syntax tree parsing on the first SQL statement to obtain its syntax structure information and database table field information; querying metadata information associated with the first SQL statement based on the syntax structure information and database table field information, and determining the data source configuration information corresponding to the database table field information based on the metadata information; and calling a preset conversion model based on the syntax structure information, database table field information, and data source configuration information to convert the first SQL statement into a second SQL statement. Because this invention extracts syntax structure information and database table field information through abstract syntax tree parsing, determines the data source configuration in conjunction with metadata, and calls a preset conversion model to achieve intelligent SQL conversion, it improves the accuracy of SQL statement conversion compared to existing technologies.

Description

Technical Field

[0001] This invention relates to the field of big data processing technology, and in particular to a method, apparatus, device, and storage medium for converting SQL statements. Background Technology

[0002] With the rapid development of big data technology, online analytical processing (OLAP) platforms have become the core carriers for enterprise data aggregation and decision support. HETU (HetuEngine), as a high-performance distributed SQL query engine, is widely used due to its advantages such as cross-data source querying and high computational efficiency. Meanwhile, HIVE, as a mature data warehouse tool, uses HIVESQL as the mainstream SQL syntax for data analysts. Therefore, converting HIVESQL to HETU SQL to achieve syntax compatibility and interoperability has become a key requirement for improving data query efficiency.

[0003] Currently, there are two main technical solutions in the industry for this type of SQL transformation, but both suffer from insufficient conversion accuracy. One type is based on fixed-rule syntax transformation tools. These tools perform transformations using preset keyword replacements, function mapping tables, and other static rules. They lack intelligent analysis capabilities, cannot handle complex scenarios such as nested subqueries and window functions, and are difficult to adapt to HETU version iterations and kernel switching (e.g., switching from OpenLookeng to Trino), leading to frequent syntax errors in the transformed SQL. The other type is based on a general large model transformation solution. This solution directly inputs HIVE SQL into a large model to generate HETU SQL, but it does not customize or optimize for the syntax characteristics of HIVE and HETU, nor does it incorporate data lake metadata information. Its knowledge base deviates from the actual deployed engine version, and the generated SQL statements often have problems such as function incompatibility, missing or incorrect directory configurations, making it difficult to guarantee conversion accuracy and rendering them unexecutable. Therefore, existing technologies generally suffer from insufficient conversion accuracy in the SQL statement transformation process, failing to meet the actual needs of OLAP platforms for high-precision, automated transformation.

[0004] Therefore, there is an urgent need for an SQL statement conversion method that can improve the accuracy of SQL statement conversion. Summary of the Invention

[0005] The main objective of this invention is to provide a method, apparatus, device, and storage medium for converting SQL statements, aiming to solve the technical problem of insufficient accuracy in SQL statement conversion in the prior art.

[0006] To achieve the above objectives, the present invention provides an SQL statement conversion method, the method comprising the following steps: Get the first SQL statement to be converted; The first SQL statement is parsed using an abstract syntax tree to obtain its syntax structure information and database table field information. Based on the syntax structure information and the database table field information, query the metadata information associated with the first SQL statement, and determine the data source configuration information corresponding to the database table field information based on the metadata information; Based on the syntax structure information, the database table field information, and the data source configuration information, a preset conversion model is invoked to convert the first SQL statement into a second SQL statement.

[0007] Optionally, the step of performing abstract syntax tree parsing on the first SQL statement to obtain the syntax structure information and database table field information of the first SQL statement includes: The first SQL statement is preprocessed to obtain the preprocessed first SQL statement; Based on the first syntax rule file corresponding to the syntax type of the first SQL statement, lexical analysis and syntax analysis are performed on the preprocessed first SQL statement to generate a first abstract syntax tree; Traverse the nodes of the first abstract syntax tree to extract the syntax structure information and database table field information of the first SQL statement.

[0008] Optionally, the step of querying metadata information associated with the first SQL statement based on the syntax structure information and the database table field information, and determining the data source configuration information corresponding to the database table field information based on the metadata information, includes: Based on the grammatical structure information and the database table field information, a query request is sent to the metadata management system, and the metadata information returned by the metadata management system is received. The metadata information is parsed, and the number of data sources corresponding to the table names in the database table field information is determined based on the parsing results. If the number of data sources is greater than 1, then multiple data sources are matched and recommended based on the field reference information in the syntax structure information to obtain matching results; If the matching result indicates a failure, a selection instruction containing multiple data source identifiers is generated and sent to the client; The system receives the target data source fed back by the client based on the selection instruction, and generates corresponding directory configuration information according to the type of the target data source and the current kernel version of the target query engine, and uses the directory configuration information as the data source configuration information.

[0009] Optionally, the step of converting the first SQL statement into a second SQL statement by invoking a preset conversion model based on the syntax structure information, the database table field information, and the data source configuration information includes: Conversion prompt words are constructed based on the grammatical structure information, the database table field information, and the data source configuration information; Input the conversion prompt and the first SQL statement into the preset conversion model to generate the initial second SQL statement; The initial second SQL statement is preprocessed to obtain the second SQL statement.

[0010] Optionally, after the step of converting the first SQL statement into a second SQL statement by invoking a preset conversion model based on the syntax structure information, the database table field information, and the data source configuration information, the method further includes: Perform syntax validation on the second SQL statement and obtain the validation result; If the verification result indicates that the verification failed, then error feature information is extracted from the verification result; The error feature information is fed back to the preset conversion model, and the preset conversion model corrects the second SQL statement based on the error feature information to generate a corrected second SQL statement; Return to the step of performing syntax validation on the second SQL statement and obtaining the validation result, until the validation result indicates that the validation has passed or the preset retry threshold has been reached; Accordingly, after the step of performing syntax validation on the second SQL statement and obtaining the validation result, the method further includes: If the verification result indicates that the verification passed, the second SQL statement and the verification result are sent to the client.

[0011] Optionally, the method further includes: Acquire basic training data and incremental training data; Based on the aforementioned basic training data, the initial conversion model is trained using the first model to obtain the pre-trained conversion model. Based on the incremental training data, the pre-trained conversion model is incrementally trained using reinforcement learning to obtain a preset conversion model. During incremental training, the syntax verification result of the second SQL statement is used as the reward signal for reinforcement learning. If the second SQL statement passes the verification, a positive reward is given; if the verification fails, a negative reward is given, so as to guide the preset conversion model to optimize the conversion logic.

[0012] Optionally, the step of performing syntax validation on the second SQL statement and obtaining the validation result includes: Obtain the current version information of the target query engine, and select the corresponding second syntax rule file based on the current version information; Invoke the validation tool associated with the second syntax rule file, and drive the validation tool to construct a second abstract syntax tree corresponding to the second SQL statement based on the second syntax rule file; Based on the second abstract syntax tree, the second SQL statement is validated for syntax, function validity, and data source compatibility, and the validation results are obtained.

[0013] Furthermore, to achieve the above objectives, the present invention also proposes an SQL statement conversion device, the device comprising: The statement retrieval module is used to retrieve the first SQL statement to be converted. The statement parsing module is used to perform abstract syntax tree parsing on the first SQL statement to obtain the syntax structure information and database table field information of the first SQL statement; The metadata query module is used to query the metadata information associated with the first SQL statement based on the syntax structure information and the database table field information, and to determine the data source configuration information corresponding to the database table field information based on the metadata information. The statement conversion module is used to convert the first SQL statement into a second SQL statement by calling a preset conversion model based on the syntax structure information, the database table field information and the data source configuration information.

[0014] Furthermore, to achieve the above objectives, the present invention also proposes an SQL statement conversion device, the device comprising: a memory, a processor, and an SQL statement conversion program stored in the memory and executable on the processor, the SQL statement conversion program being configured to implement the steps of the SQL statement conversion method as described above.

[0015] In addition, to achieve the above objectives, the present invention also proposes a storage medium storing an SQL statement conversion program, which, when executed by a processor, implements the steps of the SQL statement conversion method as described above.

[0016] This invention discloses a method for obtaining a first SQL statement to be converted; performing abstract syntax tree parsing on the first SQL statement to obtain its syntax structure information and database table field information; querying metadata information associated with the first SQL statement based on the syntax structure information and the database table field information, and determining the data source configuration information corresponding to the database table field information based on the metadata information; and calling a preset conversion model based on the syntax structure information, the database table field information, and the data source configuration information to convert the first SQL statement into a second SQL statement. Because this invention extracts syntax structure information and database table field information through abstract syntax tree parsing, determines the data source configuration in conjunction with metadata, and calls a preset conversion model to achieve intelligent SQL conversion, it improves the accuracy of SQL statement conversion compared to existing technologies. Attached Figure Description

[0017] Figure 1 This is a flowchart illustrating the first embodiment of the SQL statement conversion method of the present invention; Figure 2 This is a schematic diagram of the information extraction process in the SQL statement conversion method of the present invention; Figure 3 This is a flowchart illustrating the second embodiment of the SQL statement conversion method of the present invention; Figure 4 This is a schematic diagram of the SQL verification process in the SQL statement conversion method of the present invention; Figure 5 This is a flowchart illustrating the third embodiment of the SQL statement conversion method of the present invention; Figure 6 This is a schematic diagram of the conversion model update and training process in the SQL statement conversion method of the present invention; Figure 7 This is a schematic diagram of the system architecture of the SQL statement conversion method of the present invention; Figure 8 This is a structural block diagram of the first embodiment of the SQL statement conversion device of the present invention; Figure 9 This is a schematic diagram of the structure of the SQL statement conversion device in the hardware operating environment involved in the embodiments of the present invention.

[0018] The realization of the objective, functional features and advantages of the present invention will be further explained in conjunction with the embodiments and with reference to the accompanying drawings. Detailed Implementation

[0019] It should be understood that the specific embodiments described herein are for illustrative purposes only and are not intended to limit the scope of the invention.

[0020] This invention provides a method for converting SQL statements, referring to... Figure 1 , Figure 1This is a flowchart illustrating the first embodiment of the SQL statement conversion method of the present invention.

[0021] In this embodiment, the SQL statement conversion method includes steps S10 to S40: Step S10: Obtain the first SQL statement to be converted.

[0022] It should be noted that the executing entity in this embodiment can be a computer server device with data processing, network communication, and program execution functions applied in the data query scenario of an OLAP platform, such as a server, tablet computer, or personal computer, or an electronic device capable of performing the above functions (such as an SQL statement conversion device). The following uses a system containing an SQL statement conversion device (hereinafter referred to as the system) as an example to illustrate this embodiment and the following embodiments.

[0023] It's important to clarify that the first SQL statement to be converted mentioned above can refer to the original query statement that needs to be converted from one SQL dialect to another. In OLAP platform scenarios, the first SQL statement is typically a user-written HIVE SQL statement, such as a complex query statement containing nested subqueries, window functions, or user-defined functions. The statement to be converted indicates that this SQL statement has not yet undergone syntax transformation and is the input object for the conversion process.

[0024] In its implementation, the aforementioned system, acting as the execution entity of the SQL statement conversion method, first obtains the original SQL statement input by the user; this original SQL statement is the first SQL statement. The system can obtain the first SQL statement in various ways, such as receiving SQL text entered by the user in a client-side edit box, reading pre-stored SQL statements from a local cache or remote interface, or receiving SQL statements transmitted by other modules through an application programming interface (API). After obtaining the first SQL statement, the system temporarily stores it in memory and marks its status as "pending conversion," awaiting processing in subsequent parsing steps.

[0025] To facilitate understanding, the following example illustrates the concept, but does not impose specific limitations on this embodiment. For instance, suppose a user needs to convert a HIVE SQL statement into a HETU SQL statement suitable for the HETUEngine execution engine. The user enters the following HIVE SQL statement in the SQL client edit box of the OLAP platform: "SELECT name, age FROM employee WHERE department = 'IT'", then right-clicks the edit box and selects the "Convert to HETU SQL" option. At this point, the system obtains this HIVE SQL statement as the first SQL statement to be converted.

[0026] Step S20: Perform abstract syntax tree parsing on the first SQL statement to obtain the syntax structure information and database table field information of the first SQL statement.

[0027] It should be noted that the above-mentioned abstract syntax tree parsing can refer to the process of converting the text form of an SQL statement into a tree structure representation, where each node of the tree structure corresponds to a syntax element in the SQL statement, such as a keyword, table name, field name, or operator.

[0028] It should be explained that the above-mentioned syntactic structure information can refer to information about the overall framework of the SQL statement extracted from the abstract syntax tree, such as whether the query statement contains structural features such as subqueries, joins, window functions, or grouping operations.

[0029] The aforementioned database table field information can refer to relevant information about the database, tables, and fields extracted from the abstract syntax tree, including the database name (schema name), table name, table alias, field name, field alias, and the reference relationships between fields.

[0030] In its implementation, after obtaining the first SQL statement to be converted, the system performs an abstract syntax tree (AST) parsing operation on the first SQL statement to accurately identify its syntactic components and referenced data objects. The system inputs the first SQL statement into the AST parsing engine, which first performs lexical analysis, breaking down the string-like SQL statement into a series of tags. Each tag represents an indivisible syntactic unit, such as the keyword "SELECT", the identifier "name", or the operator "=". Next, based on the syntax rule file corresponding to the syntactic type of the first SQL statement, the system organizes these tags into an AST according to the syntax rules. Subsequently, the system traverses each node of the generated AST, extracting information describing the overall query structure from the nodes to form syntactic structure information. Simultaneously, it extracts all database names, table names, field names, their aliases, and reference relationships from the nodes to form database and table field information. Finally, the system stores the syntactic structure information and database and table field information in memory for subsequent steps.

[0031] To facilitate understanding, the following explanation uses examples, but does not impose specific limitations on this embodiment. For example, suppose the first SQL statement obtained by the above system is: "SELECT t1.name, t2.age FROM db1.employee t1 JOIN db2.salary t2 ON t1.id = t2.id WHERE t1.department = 'IT'". After the above system performs abstract syntax tree parsing on this statement, the obtained syntax structure information may include: this statement is a SELECT query statement with JOIN join query and WHERE filter condition. The obtained database table field information may include: the table "employee" and its alias "t1" in database "db1", the table "salary" and its alias "t2" in database "db2", the field "name" belonging to table "t1", the field "age" belonging to table "t2", the field "id" used for join condition, and the field "department" belonging to table "t1" and used for filter condition.

[0032] To improve conversion accuracy, the step of parsing the first SQL statement using an abstract syntax tree to obtain its syntax structure information and database table field information may include: preprocessing the first SQL statement to obtain a preprocessed first SQL statement; performing lexical and syntactic analysis on the preprocessed first SQL statement based on a first syntax rule file corresponding to the syntax type of the first SQL statement to generate a first abstract syntax tree; and traversing the nodes of the first abstract syntax tree to extract the syntax structure information and database table field information of the first SQL statement.

[0033] It should be understood that by performing lexical and syntactic analysis on the first SQL statement based on the first grammar rule file to generate an abstract syntax tree, and traversing the nodes to extract grammatical structure information and database table field information, accurate structured parsing of complex nested subqueries, alias references and other scenarios is achieved. Compared with the traditional string regular expression parsing method, this improves the accuracy of database table field recognition and provides a reliable data foundation for subsequent metadata querying and syntax conversion.

[0034] Understandably, the aforementioned preprocessing can refer to the formatting and cleaning of the SQL statement text, such as removing extra spaces, newlines, and comments, as well as standardizing the capitalization of keywords, so that the subsequent parsing engine can correctly identify the statement structure.

[0035] It should be explained that the aforementioned first syntax rule file may refer to the syntax definition file corresponding to the syntax type of the first SQL statement. This file is usually written in the .g4 format of ANTLR4, which defines the lexical and syntax rules of the SQL dialect. For example, the syntax rule file for HIVE SQL defines the combination of keywords such as "SELECT", "FROM", and "JOIN".

[0036] Furthermore, the lexical analysis described above can refer to the process of decomposing an SQL statement string into a series of tokens, each token corresponding to an indivisible syntactic unit, such as a keyword, identifier, operator, or constant. The syntax analysis described above can refer to the process of organizing the token sequence into a tree structure according to a syntax rule file.

[0037] It should be noted that the aforementioned first abstract syntax tree can refer to a tree-like data structure generated through lexical analysis and syntax analysis. Each node of this tree structure represents a syntax element in an SQL statement, the root node represents the entire SQL statement, and the child nodes represent clauses or expressions.

[0038] Understandably, the traversal mentioned above can refer to the operation of visiting each node in the tree in a specific order (such as depth-first or post-order traversal). The node mentioned above can refer to a basic building block in an abstract syntax tree, with each node containing information such as node type, a list of child nodes, and associated text values.

[0039] In its implementation, the system first performs preprocessing operations on the first SQL statement to be transformed when parsing its abstract syntax tree. This includes removing leading, trailing, and intermediate spaces, replacing consecutive spaces with single spaces; removing comments, including single-line comments (e.g., those starting with "--") and multi-line comments (e.g., those enclosed in " / * * / "); removing unnecessary newlines and tabs; and converting all SQL keywords to uppercase, such as converting "select" to "SELECT" and "from" to "FROM". After this preprocessing, the system obtains a formatted, redundant first SQL statement. Next, based on the syntax type of the first SQL statement, the system retrieves the corresponding first syntax rule file from local storage or a remote configuration center. For example, for HIVE SQL statements, it retrieves the syntax rule definition generated from the .g4 file in the HIVE open-source code. The system invokes the ANTLR4 parsing engine to perform lexical and syntax analysis on the preprocessed first SQL statement based on the first syntax rule file: the lexical analyzer reads the character stream and breaks it down into a sequence of tokens; the syntax analyzer organizes the token sequences into a tree structure according to the syntax rules, generating the first abstract syntax tree. Subsequently, starting from the root node of the first abstract syntax tree, the system recursively traverses each node in the tree. During the traversal, for each node, the system determines the type of syntax element it represents and extracts the corresponding information: if the node represents a SELECT clause, it extracts the list of queried fields; if the node represents a FROM clause, it extracts the referenced database name, table name, and alias; if the node represents a WHERE clause, it extracts the field references in the filter conditions; if the node represents a JOIN clause, it extracts the join conditions and related table information. Finally, the system categorizes and summarizes all extracted information to form syntax structure information and database / table field information, and stores it in memory for use in subsequent steps.

[0040] For example, refer to Figure 2 , Figure 2This diagram illustrates the information extraction process in the SQL statement conversion method of this invention. First, the input SQL statement undergoes cleaning and splitting preprocessing, followed by lexical verification to ensure basic statement standardization. After verification, the system generates an Abstract Syntax Tree (AST) based on the corresponding syntax rule file, constructing a structured expression of the statement. Next, the system traverses the leaf nodes of the AST to locate table names and further extracts field correspondences through a table lineage tracing mechanism, forming complete syntactic structure information and database table field information. At this point, the system determines whether the SQL has been fully parsed—if not (branch N), it returns to the lexical verification stage for reprocessing (potentially targeting uncovered substatements); if parsing is complete (branch Y), it outputs metadata information including the schema name, table name, and field names, finally ending the process.

[0041] Step S30: Based on the syntax structure information and the database table field information, query the metadata information associated with the first SQL statement, and determine the data source configuration information corresponding to the database table field information based on the metadata information.

[0042] It should be noted that the aforementioned metadata information can refer to data describing the characteristics of the data tables stored in the data lake, such as the database name where the data table resides, the column information of the data table, the underlying storage path corresponding to the data table, and the data source type to which the data table belongs (such as HIVE, Gaussian Database, ClickHouse, etc.).

[0043] It should be explained that the above-mentioned data source configuration information can refer to configuration parameters used to locate and access a specific data source in the target query engine. The most common form is directory configuration information. For example, in HetuEngine, directory configuration information is represented by a three-level naming structure of "catalog name.database name.table name", where the catalog name corresponds to a specific data source connection.

[0044] In its implementation, after obtaining the syntax structure information and table field information of the first SQL statement, the system performs a metadata query operation based on this information to determine which data source the tables referenced in the first SQL statement are actually stored in, thus generating the correct data access path for the target query engine. The system extracts the database name (schema name) and table name from the table field information and constructs a metadata query request by combining this with the field reference relationships in the syntax structure information. This query request is then sent to a metadata management system, such as a data lake metadata service. The metadata management system retrieves matching records from the metadata storage based on the table names in the query request and returns the corresponding metadata information, including the data source type, data source connection address, and catalog mapping rules for the table. Upon receiving the returned metadata information, the system parses it and extracts the actual data source identifier corresponding to each table name in the table field information. Then, based on the data source identifier and the current configuration rules of the target query engine, the system generates the corresponding data source configuration information. For example, if metadata indicates that a table is stored in a HIVE data source, and the target query engine requires access through a catalog named "hive", then the system generates catalog configuration information "hive.database_name.table_name". Finally, the system establishes a mapping relationship between the generated data source configuration information and the table names in the database table field information, stores it in memory, and uses it for subsequent conversion steps.

[0045] To facilitate understanding, the following explanation uses examples, but does not impose specific limitations on this embodiment. For example, suppose the system extracts the table name "employee" and its database name "default" from the database table field information of the first SQL statement, and the syntax structure information indicates that the table appears in the FROM clause. The system sends a query request to the metadata management system, requesting "query the metadata information of the table 'employee' under the database 'default'". The metadata information returned by the metadata management system shows that the table 'employee' is actually stored in the HIVE data source, and the corresponding catalog mapping name is "hive_catalog". Based on this metadata information, the system determines the data source configuration information as "hive_catalog.default.employee". If the first SQL statement also references another table "salary", and the metadata information shows that this table is stored in the Gauss database, and the catalog mapping name is "gauss_catalog", then the system generates the corresponding data source configuration information "gauss_catalog.default.salary". The system associates and stores these two data source configuration information with the table names "employee" and "salary" respectively.

[0046] To ensure that the converted SQL statement can accurately access the target data, the step of querying the metadata information associated with the first SQL statement based on the syntax structure information and the database table field information, and determining the data source configuration information corresponding to the database table field information based on the metadata information, may include: sending a query request to the metadata management system based on the syntax structure information and the database table field information, and receiving the metadata information returned by the metadata management system; parsing the metadata information, and determining the number of data sources corresponding to the table names in the database table field information based on the parsing results; if the number of data sources is greater than 1, then matching and recommending multiple data sources based on the field reference information in the syntax structure information to obtain a matching result; if the matching result indicates a matching failure, then generating a selection instruction containing multiple data source identifiers and sending the selection instruction to the client; receiving the target data source fed back by the client based on the selection instruction, and generating corresponding directory configuration information based on the type of the target data source and the current kernel version of the target query engine, and using the directory configuration information as the data source configuration information.

[0047] It should be noted that the aforementioned metadata management system can refer to a service system that centrally stores and manages various types of metadata information in a data lake, such as a data lake metadata service or a data asset management platform. This system provides a metadata query interface that can return information such as the corresponding data source type, storage location, and field structure based on conditions such as table name and database name.

[0048] The above parsing results can refer to the data content obtained after parsing metadata information, such as data source type, catalog mapping rules, and the correspondence between database tables and data sources.

[0049] Understandably, the number of data sources mentioned above could refer to the number of times the same table name appears in different data sources in the data lake. For example, the table "employee" may exist in both the HIVE data source and the Gaussian data source, in which case the number of data sources would be 2.

[0050] It should be explained that the aforementioned field reference information can refer to information about the relationship between fields and tables extracted from the syntax structure information of the first SQL statement. For example, "t1.name" indicates that the field 'name' belongs to the table alias 't1'. The aforementioned matching recommendation can refer to the process of automatically selecting the most matching data source from multiple possible data sources based on the field reference information and the mapping relationship between table aliases. The aforementioned matching result can refer to the conclusion generated by the matching recommendation operation, such as "match successful" or "match failed".

[0051] Understandably, the aforementioned selection instruction could refer to a control signal used to trigger the client to display an options interface, containing a list of multiple data source identifiers that the user needs to select. The aforementioned client could refer to a front-end interface program that interacts with the aforementioned system, such as the SQL execution client of an OLAP platform, used to receive user input and display transformation results.

[0052] It should be noted that the target data source mentioned above can refer to a specific data source selected by the user from multiple data sources with the same name. The target query engine mentioned above can refer to the query engine that ultimately executes the transformed SQL statement, such as HetuEngine. The current kernel version mentioned above can refer to the core technology branch version used by the target query engine, such as the OpenLookeng version or the Trino version.

[0053] In its implementation, after obtaining the syntax structure information and database / table field information, the system performs metadata queries and data source configuration operations based on this information. The system extracts the database name and table name to be queried from the database / table field information, constructs a query request, and sends this request to the metadata management system. The system waits to receive metadata information returned by the metadata management system, which contains the storage status of the queried database / table in various data sources. Next, the system parses the received metadata information and extracts the list of data source records corresponding to each table name from the parsing results. The system counts the number of data source records corresponding to each table name and determines if this number is greater than 1. If the result indicates that the number of data source records is equal to 1, the system directly generates catalog configuration information based on the type of this unique data source and catalog rules; if the result indicates that the number of data source records is greater than 1, it indicates a scenario of duplicate table names, requiring further processing. When the number of data source records is greater than 1, the system obtains field reference information from the syntax structure information, such as the attribution relationship between each field in the first SQL statement and the table alias. The system utilizes field reference information to match and recommend data sources from multiple sources. For example, by analyzing the fields referenced in the SELECT and WHERE clauses and combining this with the field definitions in the data source table structure of the metadata information, the system calculates a matching score for each data source and selects the data source with the highest score as the recommended result. The system obtains the matching result, which includes whether the match was successful and the identifier of the recommended data source. If the matching result indicates a successful match, the system directly uses the recommended data source to generate directory configuration information. If the matching result indicates a failed match, for example, if the data source cannot be uniquely determined based on the field reference information, the system generates a selection instruction containing multiple data source identifiers. The system sends this selection instruction to the client, triggering a selection interface that lists the identifiers of all data sources with the same name for the user to choose from. The system waits to receive feedback from the client regarding the target data source based on the selection instruction, i.e., the data source selected by the user. Then, the system obtains the type of the target data source selected by the user (e.g., HIVE, Gaussian Database, or ClickHouse) and the current kernel version of the target query engine (e.g., OpenLookeng or Trino). The system generates corresponding catalog configuration information based on the data source type and the current kernel version, following the catalog naming conventions of the target query engine. For example, it might be formatted as "data source type_catalog.database name.table name". Finally, the system stores this generated catalog configuration information in memory as the data source configuration information corresponding to that table name.

[0054] To facilitate understanding, the following explanation uses examples, but does not impose specific limitations on this embodiment. For example, suppose the above system extracts the table name "employee" and the database name "default" from the database table field information. After the system sends a query request to the metadata management system, the received metadata information shows that the table "employee" exists in two data sources: data source A (type HIVE, catalog name "hive_catalog") and data source B (type Gaussian database, catalog name "gauss_catalog"), i.e., the number of data sources is 2. The system further obtains the field reference information in the syntax structure information and finds that the SELECT clause in the first SQL statement contains the fields "name" and "age", and the metadata information shows that the employee table in data source A contains the fields "name" and "age", while the employee table in data source B only contains the fields "id" and "salary". The system performs a matching recommendation, recommending data source A based on the degree of field matching, and the matching result is "match successful". The system directly generates the catalog configuration information "hive_catalog.default.employee" based on the type of data source A (HIVE) and the current kernel version of the target query engine (assuming it's OpenLookeng). In another scenario, if the table structures of the two data sources are identical and the field reference information is indistinguishable, resulting in a "match failed" result, the system generates a selection instruction containing both "HIVE data source" and "Gaussian data source" options and sends it to the client. The user selects "HIVE data source" in the client interface, and the system receives this feedback as the target data source and generates the corresponding catalog configuration information.

[0055] It should be added that, after parsing the metadata information and determining the number of data sources corresponding to the table names in the database table field information based on the parsing results, the method further includes: if the parsing results indicate that the number of data sources corresponding to the table names in the database table field information is 0, then the table name is marked as not requiring configuration; skipping the generation of directory configuration information, and when calling the preset conversion model, instructing the preset conversion model to retain the original form of the table name and not add directory configuration information.

[0056] It should be understood that by sending query requests to the metadata management system based on syntactic structure information and database table field information, deep integration between AST parsing and data lake metadata is achieved, enabling accurate identification of the actual data source type corresponding to the database table field information. For scenarios with multiple data sources and tables with the same name, matching and recommendations are performed based on field reference information, and a selection instruction is generated for user confirmation when matching fails, resolving the issue of missing or incorrect catalog configuration in existing technologies. For scenarios with non-real database tables, marking a state where no configuration is required expands the applicable scenarios for SQL transformation. Compared to existing technologies, this ensures the accuracy of catalog configuration information, enabling the transformed target SQL statement to accurately access the target data.

[0057] Step S40: Based on the syntax structure information, the database table field information, and the data source configuration information, call the preset conversion model to convert the first SQL statement into a second SQL statement.

[0058] It should be explained that the aforementioned preset conversion model can refer to a machine learning model that has been pre-trained and has the ability to convert one SQL dialect into another, such as an SQL conversion model trained based on a large language model architecture. This model can receive the original SQL statement and its context information and output an SQL statement that conforms to the syntax specification of the target query engine.

[0059] Understandably, the second SQL statement mentioned above can refer to an SQL statement that is obtained by transforming the first SQL statement and is suitable for execution by the target query engine, such as the HETU SQL statement obtained by transforming HIVE SQL.

[0060] In its implementation, after obtaining the syntax structure information, database table field information, and data source configuration information, the system calls a preset conversion model to perform a conversion operation in order to transform the original first SQL statement into a second SQL statement that the target query engine can execute. The system uses the syntax structure information, database table field information, and data source configuration information as auxiliary inputs, passing them along with the first SQL statement to the preset conversion model. Upon receiving this input information, the preset conversion model uses its internal conversion logic to analyze each syntax element in the first SQL statement, understands the overall query framework based on the syntax structure information, identifies the referenced data objects based on the database table field information, and determines the access path to the target data source based on the data source configuration information. The preset conversion model then generates the corresponding second SQL statement, which, while preserving the original query semantics, adopts a syntax format, function names, and data source referencing method compatible with the target query engine. The system receives the second SQL statement output by the preset conversion model and uses it as the conversion result for subsequent processing.

[0061] To facilitate understanding, the following explanation uses examples, but does not impose specific limitations on this embodiment. For example, assume that the above system has obtained the syntax structure information (containing a simple SELECT query with no subqueries), table field information (including table name "employee", field names "name" and "age"), and data source configuration information (the directory configuration information is "hive_catalog.default.employee"). The above system calls the preset transformation model, inputting the original first SQL statement "SELECT name, age FROM employee" and the above three pieces of information into the model. After transformation, the preset transformation model outputs the second SQL statement "SELECT name, age FROM hive_catalog.default.employee". In this second SQL statement, the table name "employee" has been replaced with the complete path "hive_catalog.default.employee" with the catalog prefix, conforming to the syntax specifications of the target query engine HetuEngine.

[0062] To ensure the structured output quality of the target SQL statement, the step of converting the first SQL statement into a second SQL statement by invoking a preset conversion model based on the syntax structure information, the database table field information, and the data source configuration information may include: constructing conversion prompt words based on the syntax structure information, the database table field information, and the data source configuration information; inputting the conversion prompt words and the first SQL statement into the preset conversion model to generate an initial second SQL statement; and preprocessing the initial second SQL statement to obtain a second SQL statement.

[0063] It should be understood that by constructing structured conversion prompts based on grammatical structure information, database table field information, and data source configuration information, the preset conversion model is driven to perform grammatical conversion, function mapping, and directory configuration addition. This achieves precise guidance and automated generation of the SQL conversion process, avoiding conversion deviations caused by the lack of scenario adaptation in general models. At the same time, by preprocessing the initial generated results to unify the format specifications, the structured output quality of the target SQL statement is ensured.

[0064] It should be noted that the aforementioned conversion prompts can refer to structured text descriptions used to guide the preset conversion model to understand the requirements of the conversion task. These prompts contain various contextual information required for the conversion, such as the syntactic structure features of the original SQL statement, the mapping relationship between database and table fields, the data source configuration rules, and the syntactic specification requirements of the target query engine.

[0065] Understandably, the aforementioned initial second SQL statement may refer to an intermediate version of the SQL statement that has not yet undergone format standardization, generated directly by the preset conversion model based on the conversion prompts and the first SQL statement.

[0066] In its implementation, after obtaining the syntax structure information, database table field information, and data source configuration information, the system first constructs conversion prompts based on these three types of information to invoke the preset conversion model for SQL statement conversion. The system extracts features from the syntax structure information, such as query type, subquery level, relationships, and function calls, and converts them into natural language descriptions. It also organizes the database name, table name, field name, and alias mapping relationships from the database table field information into a structured description. Furthermore, it integrates the correspondence between catalog names and table names from the data source configuration information into the prompts. The system then concatenates all the above information according to a preset template format into a complete conversion prompt. This prompt clearly informs the model that the first SQL statement needs to be converted into a second SQL statement that conforms to the target query engine's syntax specifications, and indicates the syntax items that need to be modified, the functions that need to be replaced, and the catalog prefix that needs to be added. Subsequently, the system inputs the constructed conversion prompts and the original first SQL statement into the preset conversion model. The preset conversion model analyzes and converts the first SQL statement according to the guidance in the conversion prompts, generating an initial second SQL statement. The initial second SQL statement already includes core changes such as syntax transformation and catalog addition, but it may have formatting issues, such as extra spaces, inconsistent capitalization, or disordered line breaks. Next, the system performs preprocessing operations on the initial second SQL statement, including removing leading, trailing, and middle extra spaces, standardizing keyword capitalization, normalizing line breaks, and removing unnecessary comment symbols, thereby obtaining a formatted and clean second SQL statement.

[0067] To facilitate understanding, the following explanation uses examples, but does not impose specific limitations on this embodiment. For example, assume that the syntax structure information obtained by the above system is "simple SELECT query, including a WHERE filter condition", the table field information is "table employee, fields name and age", and the data source configuration information is "hive_catalog.default.employee". The conversion prompt word constructed by the above system is: "Please convert the following HIVE SQL statement to a HETU SQL statement. In the original statement, the table employee needs to be prefixed with catalog, becoming hive_catalog.default. Keep the fields name and age unchanged. Output an SQL statement that conforms to the HETU syntax specification." The above system inputs this prompt word and the first SQL statement "SELECT name, age FROM employee WHERE age>18" into the preset conversion model. The initial second SQL statement output by the model is "select name, age from hive_catalog.default.employee where age>18". The keywords in this initial second SQL statement are lowercase, which is an irregular format. The system performs preprocessing, converting keywords to uppercase, resulting in the second SQL statement: "SELECT name,age FROM hive_catalog.default.employee WHERE age>18".

[0068] This embodiment discloses the following steps: obtaining a first SQL statement to be converted; performing abstract syntax tree parsing on the first SQL statement to obtain its syntax structure information and database table field information; querying metadata information associated with the first SQL statement based on the syntax structure information and the database table field information, and determining the data source configuration information corresponding to the database table field information based on the metadata information; and calling a preset conversion model based on the syntax structure information, the database table field information, and the data source configuration information to convert the first SQL statement into a second SQL statement. Because this embodiment extracts syntax structure information and database table field information through abstract syntax tree parsing, determines the data source configuration in conjunction with metadata, and calls a preset conversion model to achieve intelligent SQL conversion, this embodiment improves the accuracy of SQL statement conversion compared to existing technologies.

[0069] refer to Figure 3 , Figure 3 This is a flowchart illustrating the second embodiment of the SQL statement conversion method of the present invention.

[0070] Based on the first embodiment described above, in this embodiment, after step S40, steps S501 to S504 are further included: Step S501: Perform syntax validation on the second SQL statement and obtain the validation result.

[0071] Step S502: If the verification result indicates that the verification failed, then extract the error feature information from the verification result.

[0072] Step S503: Feed back the error feature information to the preset conversion model, and use the preset conversion model to correct the second SQL statement based on the error feature information to generate the corrected second SQL statement.

[0073] Step S504: Return to the step of performing syntax validation on the second SQL statement and obtaining the validation result, until the validation result indicates that the validation has passed or the preset retry threshold has been reached.

[0074] It should be added that if the verification result indicates that the verification passed, the second SQL statement and the verification result will be sent to the client.

[0075] It should be noted that the above-mentioned syntax validation refers to the process of checking whether the SQL statement conforms to the syntax rules, function definitions, and data source access rules of the target query engine. For example, it may check whether the keywords in the SQL statement are spelled correctly, whether the function names exist, whether the parameter types match, and whether the catalog configuration is valid. The validation result refers to the conclusion information obtained after the syntax validation operation is completed. This conclusion includes a status indicator indicating whether the validation passed, and, if the validation failed, the error type, error location, and error description.

[0076] It should be explained that the error characteristic information mentioned above can refer to structured data extracted from the verification results to describe the essential characteristics of the error, such as error code, error type (e.g., syntax error, function incompatibility, catalog configuration error), the specific location where the error occurred, and the system's recommended correction suggestions.

[0077] Understandably, the aforementioned correction may refer to the process by which the preset conversion model adjusts and regenerates the original second SQL statement based on error characteristic information, a process designed to eliminate errors discovered during the verification process.

[0078] It should be noted that the aforementioned preset retry threshold may refer to the maximum number of retries preset by the system. When the cumulative number of re-verifications after correction reaches this value, the system will stop the retry loop even if the verification still fails. This threshold is used to prevent infinite loops. For example, the preset retry threshold can be set to 3 times.

[0079] In its implementation, after obtaining the second SQL statement, the system performs syntax validation on it to ensure that the converted SQL statement can be directly executed in the target query engine. The system calls the syntax validation module, taking the second SQL statement as input, and drives the validation tool to perform a comprehensive check. The validation tool returns a validation result containing a flag indicating whether the validation passed. Upon receiving this result, the system determines whether the validation passed. If the result indicates a failure, the system parses the error information from the validation result, extracting error characteristics, including the error type, the location of the error (e.g., the character or line number in the SQL statement), a detailed error description, and possible correction suggestions.

[0080] Next, the system feeds back the extracted error feature information to the preset conversion model. Upon receiving the error feature information, the preset conversion model, combining the previously received first SQL statement, syntax structure information, database table field information, data source configuration information, and the previously generated second SQL statement, corrects the original second SQL statement based on the guidance in the error feature information, generating a corrected second SQL statement. After obtaining the corrected second SQL statement, the system returns to the step of performing syntax verification on the second SQL statement, that is, it calls the syntax verification module again to verify the corrected second SQL statement and obtains the verification result again. The system repeats the above-mentioned loop process of verification, error feature extraction, and correction feedback until a verification result indicates that the verification passed, or the cumulative number of retries reaches the preset retry threshold, at which point the loop stops.

[0081] It should be added that if the verification result indicates that the verification passed, the result sending operation is executed. The system obtains the second SQL statement that passed verification and the corresponding verification result, whereby the verification result includes verification success information and optional prompt information. The system encapsulates the second SQL statement and the verification result into response data and sends the response data to the client through a pre-established communication connection. After receiving the second SQL statement and the verification result, the client automatically fills the second SQL statement into the client's SQL execution edit box and displays the pass indicator or success prompt information from the verification result, allowing the user to execute it directly or confirm further.

[0082] To ensure the accuracy of the verification results, the step of performing syntax verification on the second SQL statement and obtaining the verification results includes: obtaining the current version information of the target query engine and selecting the corresponding second syntax rule file according to the current version information; calling the verification tool associated with the second syntax rule file and driving the verification tool to construct a second abstract syntax tree corresponding to the second SQL statement based on the second syntax rule file; and performing syntax format verification, function validity verification, and data source compatibility verification on the second SQL statement based on the second abstract syntax tree to obtain the verification results.

[0083] It should be understood that by obtaining the current version information of the target query engine and selecting the corresponding syntax parsing rule file, the validation tool constructs an abstract syntax tree of the target SQL statement, and then performs multi-dimensional validations such as syntax format, function validity, and data source compatibility. This achieves dynamic adaptation between the validation rules and the target query engine kernel version, solving the problem of validation failure caused by version iteration and kernel switching that existing technologies cannot handle, thus ensuring the accuracy of the validation results.

[0084] It should be explained that the aforementioned current version information may refer to the specific version identifier of the target query engine currently running, such as the version number or kernel branch name. This information is used to distinguish the syntax differences and functional support range between different versions. For example, the OpenLookeng version and the Trino version have different syntax rules and function sets.

[0085] The aforementioned second syntax rule file may refer to the syntax definition file corresponding to the current version information. This file is usually written in the .g4 format of ANTLR4, which defines the lexical and syntax rules supported by the specific version of the query engine. For example, the syntax rule file for the Trino version defines the SQL keyword combination methods and function call specifications supported by that version.

[0086] It should be noted that the aforementioned verification tool can refer to a program component specifically designed to verify whether an SQL statement conforms to specific syntax rules. For example, a syntax verification service built on the ANTLR4 parser can take SQL statements and syntax rule files as input and output verification results.

[0087] Understandably, the aforementioned second abstract syntax tree can refer to a tree-like data structure generated after performing lexical and syntactic analysis on the second SQL statement according to the second syntax rule file. Each node of this tree structure represents a syntax element in the second SQL statement, which is used for subsequent verification checks.

[0088] It should be understood that the above-mentioned syntax format validation can refer to the process of checking whether the spelling of keywords, punctuation, bracket matching, clause order, etc., of the SQL statement conform to the syntax rule file definition. The above-mentioned function validity validation can refer to the process of checking whether the function name called in the SQL statement exists, whether the number and type of function parameters are correct, and whether the function usage conforms to the version specification. The above-mentioned data source compatibility validation can refer to the process of checking whether the catalog configuration in the SQL statement is correct, whether the referenced data source exists, and whether the table names and field names match the actual structure in the target data source.

[0089] In its implementation, when performing syntax validation on the second SQL statement, the system first obtains the current version information of the target query engine. This can be done by reading a configuration file, calling a version query interface, or obtaining the version identifier of the target query engine from system environment variables, such as "OpenLookeng 1.2.0" or "Trino 4.0.0". After obtaining the current version information, the system selects the corresponding second syntax rule file from local storage or a remote configuration center based on this information. For example, if the current version is OpenLookeng, it selects a syntax rule file generated based on the .g4 file in the OpenLookeng open-source code; if the current version is Trino, it selects a syntax rule file generated based on the Trino open-source .g4 file. Next, the system calls the validation tool associated with the second syntax rule file. The system starts the validation tool through an application programming interface (API) and passes the second SQL statement and the selected second syntax rule file as parameters to the validation tool. Upon receiving the request, the validation tool performs lexical and syntactic analysis on the second SQL statement based on the second syntax rule file, constructing a second abstract syntax tree corresponding to the second SQL statement. The validation tool traverses each node of the second abstract syntax tree (BST), performing syntax format validation, function validity validation, and data source compatibility validation. During syntax format validation, it checks the completeness of the BST structure, the validity of keywords, and the matching of brackets. During function validity validation, it extracts the function call node, verifies the function name's existence in the current version's supported function list, and checks the number and type of parameters. During data source compatibility validation, it extracts the catalog and table name nodes, verifies whether the catalog configuration points to a valid data source connection, and confirms the existence of the table name and field names in the target data source's actual table structure. Finally, the validation tool summarizes all the check results, generating a validation result object containing a flag indicating whether the validation passed and error details in case of failure. The system receives this validation result and uses it for subsequent processing.

[0090] To facilitate understanding, the following explanation uses examples, but does not impose specific limitations on this embodiment. For example, assume that the target query engine version information obtained by the above system is "Trino 4.0.0". The above system selects the corresponding Trino syntax rule file "trino_v4.g4" based on this version information. The above system calls the Trino validation tool associated with this syntax rule file, and inputs the second SQL statement "SELECT name, age FROM hive_catalog.default.employee WHERE array_join(ARRAY[name, age], ',') = 'Tom,25'" and the "trino_v4.g4" file into the validation tool. After constructing the second abstract syntax tree, the validation tool performs syntax format validation and finds that the format is correct. It performs function validity validation and finds that the "array_join" function exists in Trino 4.0.0 and the parameters are correct. It performs data source compatibility validation and finds that the catalog "hive_catalog" exists and the table "employee" contains the fields "name" and "age". Finally, the validation tool returns the validation result "Validation passed". If the second SQL statement uses the function "concat_ws" which is not supported by Trino, the function validity check will return "Validation failed" with the error message "Function concat_ws does not exist".

[0091] For example, refer to Figure 4 , Figure 4 This is a schematic diagram of the SQL verification process in the SQL statement conversion method of this invention. In the diagram, the system first receives the HETU SQL to be checked, then proceeds to the syntax tree generation and syntax verification stages. If an exception is found during syntax parsing or verification (such as an invalid syntax structure), the process jumps directly to return a verification failure. If the verification passes, it proceeds to function validity verification; if a function exception is found, it also returns a verification failure. If the function is valid, it proceeds to data source validity verification; if the data source exception is found, it also returns a verification failure. When all three verifications (syntax, function, and data source) pass, the system returns a verification success and the process ends. If any stage of verification fails, the system enters the exception handling branch: generating a verification failure reason (i.e., error characteristic information) and returning the failure reason to the large model (i.e., the preset conversion model), triggering the large model to regenerate the SQL statement. The process then returns to the "receive the HETU SQL to be checked" stage, forming a closed-loop processing mechanism of "verification-failure-feedback-regeneration".

[0092] This embodiment discloses performing syntax validation on the second SQL statement and obtaining a validation result. If the validation result indicates that the validation passed, the second SQL statement and the validation result are sent to the client. If the validation result indicates that the validation failed, error feature information is extracted from the validation result. The error feature information is fed back to the preset conversion model, and the preset conversion model corrects the second SQL statement based on the error feature information to generate a corrected second SQL statement. The process of performing syntax validation on the second SQL statement and obtaining a validation result is repeated until the validation result indicates that the validation passed or a preset retry threshold is reached. Because this embodiment performs syntax validation on the second SQL statement and feeds back error feature information to the preset conversion model for correction when the validation fails, compared with the prior art, this embodiment ensures that the conversion result can be directly executed in the target query engine, improving the automation and reliability of SQL conversion.

[0093] refer to Figure 5 , Figure 5 This is a flowchart illustrating the third embodiment of the SQL statement conversion method of the present invention.

[0094] Based on the above embodiments, in this embodiment, the method further includes steps S601 to S604: Step S601: Obtain basic training data and incremental training data.

[0095] Step S602: Based on the basic training data, train the first model on the initial conversion model to obtain the pre-trained conversion model.

[0096] Step S603: Based on the incremental training data, the pre-trained conversion model is incrementally trained using reinforcement learning to obtain a preset conversion model.

[0097] Step S604: During incremental training, the syntax verification result of the second SQL statement is used as the reward signal for reinforcement learning. If the second SQL statement passes the verification, a positive reward is given; if the verification fails, a negative reward is given, so as to guide the preset conversion model to optimize the conversion logic.

[0098] It's important to clarify that the aforementioned basic training data can refer to a large-scale corpus used for initial model training. This dataset contains paired input and output samples, such as multiple sets of HIVE SQL statements paired with their corresponding correct HETU SQL statements, used to enable the model to master basic SQL syntax conversion capabilities. The aforementioned incremental training data can refer to supplementary datasets used for further fine-tuning and updating the model after the initial model training is complete. This dataset is typically smaller in scale and focused on specific scenarios, such as conversion samples for new version syntax differences, new function support, or specific business scenarios.

[0099] Understandably, the aforementioned initial conversion model can refer to a raw machine learning model that has not undergone any training or has only been initialized with pre-set weights, such as a basic large language model that has not been fine-tuned. The aforementioned first model training can refer to the process of adjusting the initial parameters of the model using basic training data, which enables the model to learn basic conversion rules. The aforementioned pre-trained conversion model can refer to the model obtained after completing the first model training, which has mastered basic conversion capabilities but has not yet been optimized for specific scenarios.

[0100] It should be noted that reinforcement learning, as mentioned above, can refer to a machine learning method that optimizes a model's decision-making strategy through trial and error and reward feedback. After performing actions in the environment, the model receives reward signals and adjusts its behavior based on these rewards to maximize cumulative rewards. Incremental training, as mentioned above, refers to the process of further adjusting the model parameters using new training data and methods, building upon a pre-trained model. The reward signal, as mentioned above, can refer to a feedback value used to evaluate the quality of the model's output; the model adjusts its parameters based on the magnitude and direction of this signal.

[0101] Furthermore, the aforementioned positive reward can refer to a positive reward signal given when the model output meets expectations, used to reinforce correct conversion behavior. The aforementioned negative reward can refer to a negative reward signal given when the model output does not meet expectations, used to suppress incorrect conversion behavior.

[0102] In its implementation, the system first acquires basic training data and incremental training data when executing the training process of the preset conversion model. The system reads a pre-organized basic training dataset from local storage, a remote database, or a data lake. This dataset contains a large number of paired samples of HIVE SQL and its corresponding HETU SQL. The system also acquires an incremental training dataset, which contains supplementary samples for specific version differences, custom functions, or special syntax scenarios. Next, the system performs first model training on the initial conversion model based on the basic training data. The system uses the HIVE SQL from the basic training data as model input and the corresponding correct HETU SQL as the expected output, adjusting the parameters of the initial conversion model using supervised learning to gradually learn the basic mapping relationship from HIVE SQL to HETU SQL. After the first model training is completed, the system obtains a pre-trained conversion model. Subsequently, the system performs incremental training on the pre-trained conversion model using reinforcement learning based on the incremental training data to obtain the final preset conversion model. During incremental training, the system inputs the HIVE SQL from the incremental training data into the pre-trained conversion model, and the model generates the corresponding conversion result, i.e., the second SQL statement. The system calls a syntax validation module to perform syntax validation on the generated second SQL statement and uses the validation result as a reward signal for reinforcement learning. Specifically, if the syntax validation result indicates that the validation passed, meaning the second SQL statement generated by the model conforms to the target query engine's specifications, the system provides a positive reward, such as a positive value. If the validation result indicates that the validation failed, meaning the second SQL statement generated by the model contains errors, the system provides a negative reward, such as a negative value. The system associates this reward signal with the second SQL statement generated by the model and updates the model parameters through a reinforcement learning algorithm: positive rewards guide the model to increase the probability of generating this output, while negative rewards guide the model to decrease the probability of generating this output. The system repeats the above incremental training process, causing the preset conversion model to gradually optimize the conversion logic and increase the proportion of correctly generated SQL statements.

[0103] For example, refer to Figure 6 , Figure 6This diagram illustrates the process of updating and training the conversion model in the SQL statement conversion method of this invention. In the model training phase, a batch of old SQL statements is first captured and combined with a large model to generate training cases. These cases are then verified and confirmed by the administrator for use in the initial model training. Subsequently, the administrator can upload new HETU rule files to update the model knowledge base and fine-tune the HETU conversion model using prompts (such as "You are an expert in..."). In the core conversion phase, the model receives cleaned and syntax-checked SQL statements, parses them based on grammatical structure information and metadata information (such as database table fields), and calls a preset conversion model (i.e., the trained HETU conversion model) to convert them into target HETU statements. This conversion process is supervised by a model reinforcement learning mechanism: the HETU syntax checker verifies the generated statements; if they pass, a positive reward is given; if they fail, a negative reward is given, thereby optimizing the model logic. In addition, the system supports a combination of manual intervention and automated services: administrators can manually trigger translation and updates through the "HETU Syntax Checker", or users can submit SQL through the "HETU Syntax Conversion Service" interface, which will be processed by the model and returned as a converted statement, thereby achieving intelligent and efficient conversion and continuous optimization of SQL between different query engines.

[0104] refer to Figure 7 , Figure 7 This is a schematic diagram of the system architecture for the SQL statement conversion method of this invention. As shown in the figure, this system mainly consists of the following parts: 1. Presentation Layer - Front-end Page: Provides users with a visual SQL editor. The SQL editor includes the following: User Login Authentication: Checks if the user has HETU operation capabilities; Intelligent SQL Translation: Users can right-click to perform intelligent translation on selected SQL statements in the editor; Metadata Selection: When metadata queries return multiple tables with the same name, users can select which database in the data lake the table resides in; Syntax File Update: Users can upload HETU's g4 syntax file to achieve real-time updates of model syntax and HETU version; Knowledge Base Update: When HETU built-in functions, UDF functions, or major HETU version updates are performed, the knowledge base can be used to update the HETU usage syntax and optimization schemes; Model Training: Administrators can use this function to trigger a syntax conversion model retraining with one click to obtain a model adapted to the current HETU version syntax.

[0105] 2. Business Logic Layer - Backend Services include: Hive Syntax Parsing: This service primarily parses user-input Hive SQL to be converted. This parsing method mainly uses the Hive AST syntax tree to obtain the table source of the root node of the syntax tree, and combines this with the user's current open mode information to extract the schema name, table information, and key fields such as select and group by fields, facilitating subsequent metadata lookup and replacement. Metadata Query: This service mainly retrieves the database location in the data lake based on the schema name, table, and field information obtained from the preceding Hive syntax parsing, and returns it. If multiple databases have the same schema and table information, a second judgment is made based on the query field information in the SQL. If a judgment still cannot be made, the frontend returns the selection to the user. The backend metadata knowledge base maps the databases in the data lake to the catalog, ensuring that HETU can be accessed across sources. Model Training Module: This module mainly performs secondary training on the general large model to adapt to the current in-row HETU database syntax and update as the database updates. It also completes the HETU syntax and catalog embedding functionality through the generated large model. This module can compile and perform diff analysis on HETU g4 syntax uploaded by users. For the diff portion, it generates corpus using a large model. After administrator confirmation and triggering of training, it automatically retrains the model to adapt to the in-line HETU database. It also features model knowledge base updates, facilitating real-time updates of UDFs, newly added function usage, etc. Furthermore, this module can perform secondary translation and conversion on SQL statements that fail HETU syntax validation, based on the error root causes returned by the validation. HETU Syntax Validation Module: This module primarily performs secondary validation on the syntax generated by the large model, ensuring that the syntax format meets business logic requirements. If validation fails, this module generates error root causes and returns them to the model for secondary generation. Additionally, this module also manages HETU UDF functions.

[0106] 3. Data Access Layer: Contains an accessible OLAP metadata knowledge base, a vector knowledge base essential for the model, and a large trained model. The underlying layer can freely switch between DeepSeek and Qwen.

[0107] This embodiment discloses the acquisition of basic training data and incremental training data; training an initial conversion model using the basic training data to obtain a pre-trained conversion model; and incrementally training the pre-trained conversion model using reinforcement learning based on the incremental training data to obtain a preset conversion model. During incremental training, the syntax verification result of the second SQL statement is used as the reward signal for reinforcement learning. A positive reward is given if the second SQL statement passes verification, and a negative reward is given if it fails, thus guiding the preset conversion model to optimize its conversion logic. Because this embodiment constructs the preset conversion model by combining basic training and incremental training, and uses the syntax verification result as the reward signal for reinforcement learning, it can guide the model to autonomously optimize its conversion logic without requiring a large amount of manually labeled data. Compared to existing technologies, this embodiment reduces model training costs while ensuring that the preset conversion model continuously adapts to changes in the syntax rules of the target query engine, solving the problem that general-purpose large models cannot be customized for training and continuous optimization.

[0108] Furthermore, embodiments of the present invention also propose a storage medium storing an SQL statement conversion program, wherein the SQL statement conversion program, when executed by a processor, implements the steps of the SQL statement conversion method described above.

[0109] Reference Figure 8 , Figure 8 This is a structural block diagram of the first embodiment of the SQL statement conversion device of the present invention.

[0110] like Figure 8 As shown, the SQL statement conversion device proposed in this embodiment of the invention includes: a statement acquisition module 901, a statement parsing module 902, a metadata query module 903, and a statement conversion module 904.

[0111] The statement acquisition module 901 is used to acquire the first SQL statement to be converted.

[0112] The statement parsing module 902 is used to perform abstract syntax tree parsing on the first SQL statement to obtain the syntax structure information and database table field information of the first SQL statement.

[0113] The metadata query module 903 is used to query the metadata information associated with the first SQL statement based on the syntax structure information and the database table field information, and to determine the data source configuration information corresponding to the database table field information based on the metadata information.

[0114] The statement conversion module 904 is used to convert the first SQL statement into a second SQL statement by calling a preset conversion model based on the syntax structure information, the database table field information and the data source configuration information.

[0115] The statement parsing module 902 is further configured to preprocess the first SQL statement to obtain a preprocessed first SQL statement; perform lexical analysis and syntax analysis on the preprocessed first SQL statement based on a first syntax rule file corresponding to the syntax type of the first SQL statement to generate a first abstract syntax tree; traverse the nodes of the first abstract syntax tree to extract the syntax structure information and database table field information of the first SQL statement.

[0116] The metadata query module 903 is further configured to send a query request to the metadata management system based on the syntax structure information and the database table field information, and receive metadata information returned by the metadata management system; parse the metadata information, and determine the number of data sources corresponding to the table names in the database table field information based on the parsing results; if the number of data sources is greater than 1, then match and recommend multiple data sources based on the field reference information in the syntax structure information to obtain a matching result; if the matching result indicates a matching failure, then generate a selection instruction containing multiple data source identifiers and send the selection instruction to the client; receive the target data source fed back by the client based on the selection instruction, and generate corresponding directory configuration information based on the type of the target data source and the current kernel version of the target query engine, and use the directory configuration information as the data source configuration information.

[0117] The statement conversion module 904 is further configured to construct conversion prompt words based on the syntax structure information, the database table field information, and the data source configuration information; input the conversion prompt words and the first SQL statement into a preset conversion model to generate an initial second SQL statement; and preprocess the initial second SQL statement to obtain a second SQL statement.

[0118] This device embodiment discloses the following steps: acquiring a first SQL statement to be converted; performing abstract syntax tree parsing on the first SQL statement to obtain its syntax structure information and database table field information; querying metadata information associated with the first SQL statement based on the syntax structure information and the database table field information, and determining the data source configuration information corresponding to the database table field information based on the metadata information; and invoking a preset conversion model based on the syntax structure information, the database table field information, and the data source configuration information to convert the first SQL statement into a second SQL statement. Because this device embodiment extracts syntax structure information and database table field information through abstract syntax tree parsing, determines the data source configuration in conjunction with metadata, and invokes a preset conversion model to achieve intelligent SQL conversion, compared to existing technologies, this device embodiment improves the accuracy of SQL statement conversion.

[0119] Based on the first embodiment of the SQL statement conversion device of the present invention described above, a second embodiment of the SQL statement conversion device of the present invention is proposed.

[0120] In this embodiment, the statement conversion module 904 is further configured to perform syntax verification on the second SQL statement and obtain a verification result; if the verification result indicates that the verification failed, then extract error feature information from the verification result; feed the error feature information back to the preset conversion model, and use the preset conversion model to correct the second SQL statement based on the error feature information to generate a corrected second SQL statement; return to execute the step of performing syntax verification on the second SQL statement and obtaining a verification result, until the verification result indicates that the verification passed or a preset retry threshold is reached.

[0121] The statement conversion module 904 is further configured to send the second SQL statement and the verification result to the client if the verification result indicates that the verification passed.

[0122] The statement conversion module 904 is further configured to obtain the current version information of the target query engine and select the corresponding second syntax rule file according to the current version information; call the verification tool associated with the second syntax rule file, drive the verification tool to construct the second abstract syntax tree corresponding to the second SQL statement based on the second syntax rule file; and perform syntax format verification, function validity verification, and data source compatibility verification on the second SQL statement based on the second abstract syntax tree to obtain the verification result.

[0123] Other embodiments or specific implementations of the SQL statement conversion device of the present invention can be found in the above-described method embodiments, and will not be repeated here.

[0124] This application provides an SQL statement conversion device, which includes: at least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor, and the instructions are executed by the at least one processor to enable the at least one processor to perform the SQL statement conversion method in the first embodiment described above.

[0125] The following is for reference. Figure 9The diagram illustrates a structural schematic of an SQL statement conversion device suitable for implementing embodiments of this application. The SQL statement conversion device in these embodiments may include, but is not limited to, mobile terminals such as mobile phones, laptops, digital radio receivers, PDAs (Personal Digital Assistants), PADs (Portable Application Description), PMPs (Portable Media Players), in-vehicle terminals (e.g., in-vehicle navigation terminals), and fixed terminals such as digital TVs and desktop computers. Figure 9 The SQL statement conversion device shown is merely an example and should not impose any limitations on the functionality and scope of use of the embodiments of this application.

[0126] like Figure 9 As shown, the SQL statement conversion device may include a processing unit 1001 (e.g., a central processing unit, a graphics processing unit, etc.) that can perform various appropriate actions and processes according to a program stored in read-only memory 1002 or a program loaded from storage device 1003 into random access memory 1004. Random access memory 1004 also stores various programs and data required for the operation of the SQL statement conversion device. The processing unit 1001, read-only memory 1002, and random access memory 1004 are interconnected via bus 1005. Input / output interface 1006 is also connected to the bus. Typically, the following systems can be connected to input / output interface 1006: input devices 1007 including, for example, touch screens, touchpads, keyboards, mice, image sensors, microphones, accelerometers, gyroscopes, etc.; output devices 1008 including, for example, liquid crystal displays (LCDs), speakers, vibrators, etc.; storage devices 1003 including, for example, magnetic tapes, hard disks, etc.; and communication devices 1009. Communication device 1009 allows the SQL statement conversion device to communicate wirelessly or wiredly with other devices to exchange data. Although the figure shows SQL statement conversion devices with various systems, it should be understood that it is not required to implement or have all of the systems shown. More or fewer systems may be implemented alternatively.

[0127] Specifically, according to the embodiments disclosed in this application, the processes described above with reference to the flowcharts can be implemented as computer software programs. For example, embodiments disclosed in this application include a computer program product comprising a computer program carried on a computer-readable medium, the computer program containing program code for performing the methods shown in the flowcharts. In such embodiments, the computer program can be downloaded and installed from a network via a communication device, or installed from storage device 1003, or installed from read-only memory 1002. When the computer program is executed by processing device 1001, it performs the functions defined in the methods of the embodiments disclosed in this application.

[0128] The SQL statement conversion device provided in this application, employing the SQL statement conversion method in the above embodiments, can solve the technical problem of insufficient accuracy in SQL statement conversion in the prior art. Compared with the prior art, the beneficial effects of the SQL statement conversion device provided in this application are the same as those of the SQL statement conversion method provided in the above embodiments, and other technical features in this SQL statement conversion device are the same as those disclosed in the previous embodiment method, and will not be repeated here.

[0129] It should be understood that the various parts disclosed in this application can be implemented using hardware, software, firmware, or a combination thereof. In the description of the above embodiments, specific features, structures, materials, or characteristics can be combined in any suitable manner in one or more embodiments or examples.

[0130] The above description is merely a specific embodiment of this application, but the scope of protection of this application is not limited thereto. Any variations or substitutions that can be easily conceived by those skilled in the art within the scope of the technology disclosed in this application should be included within the scope of protection of this application. Therefore, the scope of protection of this application should be determined by the scope of the claims.

[0131] It should be noted that, in this document, the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or system that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or system. Unless otherwise specified, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or system that includes that element.

[0132] The sequence numbers of the above embodiments of the present invention are for descriptive purposes only and do not represent the superiority or inferiority of the embodiments.

[0133] Through the above description of the embodiments, those skilled in the art can clearly understand that the methods of the above embodiments can be implemented by means of software plus necessary general-purpose hardware platforms. Of course, they can also be implemented by hardware, but in many cases the former is a better implementation method. Based on this understanding, the technical solution of the present invention, or the part that contributes to the prior art, can be embodied in the form of a software product. This computer software product is stored in a storage medium (such as read-only memory / random access memory, magnetic disk, optical disk) and includes several instructions to cause a terminal device (which may be a mobile phone, computer, server, air conditioner, or network device, etc.) to execute the methods described in the various embodiments of the present invention.

[0134] The above are merely preferred embodiments of the present invention and do not limit the scope of the patent. Any equivalent structural or procedural transformations made based on the description and drawings of the present invention, or direct or indirect applications in other related technical fields, are similarly included within the scope of patent protection of the present invention.

Claims

1. A method for converting SQL statements, characterized in that, The method includes: Get the first SQL statement to be converted; The first SQL statement is parsed using an abstract syntax tree to obtain its syntax structure information and database table field information. Based on the syntax structure information and the database table field information, query the metadata information associated with the first SQL statement, and determine the data source configuration information corresponding to the database table field information based on the metadata information; Based on the syntax structure information, the database table field information, and the data source configuration information, a preset conversion model is invoked to convert the first SQL statement into a second SQL statement.

2. The SQL statement conversion method as described in claim 1, characterized in that, The step of performing abstract syntax tree parsing on the first SQL statement to obtain the syntax structure information and database table field information of the first SQL statement includes: The first SQL statement is preprocessed to obtain the preprocessed first SQL statement; Based on the first syntax rule file corresponding to the syntax type of the first SQL statement, lexical analysis and syntax analysis are performed on the preprocessed first SQL statement to generate a first abstract syntax tree; Traverse the nodes of the first abstract syntax tree to extract the syntax structure information and database table field information of the first SQL statement.

3. The SQL statement conversion method as described in claim 1, characterized in that, The step of querying metadata information associated with the first SQL statement based on the syntax structure information and the database table field information, and determining the data source configuration information corresponding to the database table field information based on the metadata information, includes: Based on the grammatical structure information and the database table field information, a query request is sent to the metadata management system, and the metadata information returned by the metadata management system is received. The metadata information is parsed, and the number of data sources corresponding to the table names in the database table field information is determined based on the parsing results. If the number of data sources is greater than 1, then multiple data sources are matched and recommended based on the field reference information in the syntax structure information to obtain matching results; If the matching result indicates a failure, a selection instruction containing multiple data source identifiers is generated and sent to the client; The system receives the target data source fed back by the client based on the selection instruction, and generates corresponding directory configuration information according to the type of the target data source and the current kernel version of the target query engine, and uses the directory configuration information as the data source configuration information.

4. The SQL statement conversion method as described in claim 1, characterized in that, The step of converting the first SQL statement into a second SQL statement by invoking a preset conversion model based on the syntax structure information, the database table field information, and the data source configuration information includes: Conversion prompt words are constructed based on the grammatical structure information, the database table field information, and the data source configuration information; Input the conversion prompt and the first SQL statement into the preset conversion model to generate the initial second SQL statement; The initial second SQL statement is preprocessed to obtain the second SQL statement.

5. The SQL statement conversion method as described in claim 1, characterized in that, After the step of converting the first SQL statement into a second SQL statement by invoking a preset conversion model based on the syntax structure information, the database table field information, and the data source configuration information, the method further includes: Perform syntax validation on the second SQL statement and obtain the validation result; If the verification result indicates that the verification failed, then error feature information is extracted from the verification result; The error feature information is fed back to the preset conversion model, and the preset conversion model corrects the second SQL statement based on the error feature information to generate a corrected second SQL statement; Return to the step of performing syntax validation on the second SQL statement and obtaining the validation result, until the validation result indicates that the validation has passed or the preset retry threshold has been reached; Accordingly, after the step of performing syntax validation on the second SQL statement and obtaining the validation result, the method further includes: If the verification result indicates that the verification passed, the second SQL statement and the verification result are sent to the client.

6. The SQL statement conversion method as described in claim 5, characterized in that, The method further includes: Acquire basic training data and incremental training data; Based on the aforementioned basic training data, the initial conversion model is trained using the first model to obtain the pre-trained conversion model. Based on the incremental training data, the pre-trained conversion model is incrementally trained using reinforcement learning to obtain a preset conversion model. During incremental training, the syntax verification result of the second SQL statement is used as the reward signal for reinforcement learning. If the second SQL statement passes the verification, a positive reward is given; if the verification fails, a negative reward is given, so as to guide the preset conversion model to optimize the conversion logic.

7. The SQL statement conversion method as described in claim 5, characterized in that, The step of performing syntax validation on the second SQL statement and obtaining the validation result includes: Obtain the current version information of the target query engine, and select the corresponding second syntax rule file based on the current version information; Invoke the validation tool associated with the second syntax rule file, and drive the validation tool to construct a second abstract syntax tree corresponding to the second SQL statement based on the second syntax rule file; Based on the second abstract syntax tree, the second SQL statement is validated for syntax, function validity, and data source compatibility, and the validation results are obtained.

8. An SQL statement conversion device, characterized in that, The device includes: The statement retrieval module is used to retrieve the first SQL statement to be converted. The statement parsing module is used to perform abstract syntax tree parsing on the first SQL statement to obtain the syntax structure information and database table field information of the first SQL statement; The metadata query module is used to query the metadata information associated with the first SQL statement based on the syntax structure information and the database table field information, and to determine the data source configuration information corresponding to the database table field information based on the metadata information. The statement conversion module is used to convert the first SQL statement into a second SQL statement by calling a preset conversion model based on the syntax structure information, the database table field information and the data source configuration information.

9. An SQL statement conversion device, characterized in that, The device includes: a memory, a processor, and an SQL statement conversion program stored in the memory and executable on the processor, the SQL statement conversion program being configured to implement the steps of the SQL statement conversion method as described in any one of claims 1 to 7.

10. A storage medium, characterized in that, The storage medium stores an SQL statement conversion program, which, when executed by a processor, implements the steps of the SQL statement conversion method as described in any one of claims 1 to 7.

Images