Method, device, equipment and program product for rewriting structured query language

By generating SQL syntax tree and query relationship analysis, identifying and adjusting nodes in the syntax tree, the problem that SQL rewriting technology in the existing technology cannot effectively solve the complex query rewriting, and improves the executability rate and compatibility of SQL rewriting.

CN118916371APending Publication Date: 2024-11-08BEIJING VOLCANO ENGINE TECH CO LTD +1

Patent Information

Application Number
CN202410903140.3
Authority / Receiving Office
CN · China
Patent Type
Applications(China)
Current Assignee / Owner
Filing Date
2024-07-05
Publication Date
2024-11-08

Smart Images

  • Figure CN118916371A_ABST
    Figure CN118916371A_ABST
Patent Text Reader

Abstract

The embodiment of the invention relates to a method and device for rewriting a structured query language, equipment and a program product. The method comprises the step of generating query relation analysis of an input SQL based on a syntax tree of the input SQL. The method further comprises the step of identifying nodes needing to be adjusted in the syntax tree based on syntax rules and query relation analysis of the target database. In addition, the method further comprises the step of generating a rewritten SQL corresponding to the target database by adjusting the nodes. Therefore, the node needing to be modified is analyzed, recognized and adjusted through the grammar rule and the query relation, the part needing to be adjusted can be accurately determined, it can be ensured that the rewritten SQL statement conforms to the grammar specification of the target database, and therefore the execution rate of SQL rewriting is increased.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present application relates to the field of computer technology, and more particularly to a method, device, electronic device and computer program product for rewriting structured query language (SQL). Background Art

[0002] SQL is a standard language for managing and operating relational databases. It is widely used in data management systems to perform data storage, retrieval, modification, and deletion operations by writing query statements. SQL is widely used in relational database management systems as a core tool for developing and maintaining database applications.

[0003] In daily use, SQL statements are often unexecutable due to various reasons and cannot be successfully run in the database. Such problems not only cause query failures, but may also cause performance bottlenecks, resource waste, and even database crashes. Therefore, it is particularly important to rewrite SQL statements. Summary of the invention

[0004] Embodiments of the present disclosure provide a method, apparatus, electronic device, computer program product, and medium for rewriting SQL.

[0005] According to a first aspect of the present disclosure, a method for rewriting SQL is provided. The method includes generating a query relationship analysis of the input SQL based on a syntax tree of the input SQL. The method also includes identifying nodes that need to be adjusted in the syntax tree based on the grammatical rules of the target database and the query relationship analysis. In addition, the method also includes generating a rewritten SQL corresponding to the target database by adjusting the nodes.

[0006] According to a second aspect of the present disclosure, a device for rewriting SQL is provided. The device includes a query relationship generation module, which is configured to generate a query relationship analysis of the input SQL based on a syntax tree of the input SQL. The device also includes an adjustment node identification module, which is configured to identify nodes that need to be adjusted in the syntax tree based on the syntax rules of the target database and the query relationship analysis. In addition, the device also includes a rewriting SQL generation module, which is configured to generate a rewriting SQL corresponding to the target database by adjusting the nodes.

[0007] According to a third aspect of the present disclosure, an electronic device is provided, comprising a processor and a memory coupled to the processor, wherein the memory has instructions stored therein, and when the instructions are executed by the processor, the electronic device executes the method according to the first aspect.

[0008] In a fourth aspect of the present disclosure, a computer-readable storage medium is provided. The computer program product is tangibly stored on a non-transitory computer-readable medium and includes computer-executable instructions, which, when executed, cause a computer to perform the steps of the method of the first aspect of the present disclosure.

[0009] In a fifth aspect of the present disclosure, a computer-readable storage medium is provided, wherein one or more computer instructions are stored on the computer-readable storage medium, wherein the one or more computer instructions are executed by a processor to implement the method according to the first aspect.

[0010] The purpose of this Summary is to introduce a selection of concepts in a simplified form that are further described in the Detailed Description below. This Summary is not intended to identify key features or essential features of the claimed subject matter, nor is it intended to limit the scope of the claimed subject matter. BRIEF DESCRIPTION OF THE DRAWINGS

[0011] The above and other features, advantages and aspects of the embodiments of the present disclosure will become more apparent with reference to the following detailed description in conjunction with the accompanying drawings. In the accompanying drawings, the same or similar reference numerals represent the same or similar elements, wherein:

[0012] Figure 1 A schematic diagram showing an example environment in which devices and / or methods according to embodiments of the present disclosure may be implemented;

[0013] Figure 2 A flowchart of a method for rewriting SQL according to an embodiment of the present disclosure is shown;

[0014] Figure 3 A flowchart of a process of rewriting SQL in a large model scenario according to an embodiment of the present disclosure is shown;

[0015] Figure 4A A flowchart showing a process of SQL parsing according to an embodiment of the present disclosure is shown;

[0016] Figure 4B A schematic diagram showing the architecture of a SQL parsing module according to an embodiment of the present disclosure;

[0017] Figure 5 A flowchart showing a process of SQL lineage analysis according to an embodiment of the present disclosure is shown;

[0018] Fig. 6A A flowchart showing a process of SQL rewriting according to an embodiment of the present disclosure is shown;

[0019] Figure 6B A schematic diagram showing a process of traversing an abstract syntax tree using a visitor pattern according to an embodiment of the present disclosure;

[0020] Figure 7 A block diagram showing an apparatus for rewriting SQL according to an embodiment of the present disclosure; and

[0021] Figure 8 A block diagram of an electronic device according to an embodiment of the present disclosure is shown.

[0022] Throughout the drawings, the same or similar reference numbers denote the same or similar elements. DETAILED DESCRIPTION

[0023] It is understandable that before using the technical solutions disclosed in the embodiments of the present disclosure, the type, scope of use, usage scenarios, etc. of the personal information (such as voice) involved in the present disclosure should be informed to the user and the user's authorization should be obtained in an appropriate manner in accordance with relevant laws and regulations.

[0024] Embodiments of the present disclosure will be described in more detail below with reference to the accompanying drawings. Although certain embodiments of the present disclosure are shown in the accompanying drawings, it should be understood that the present disclosure can be implemented in various forms and should not be construed as being limited to the embodiments described herein, which are instead provided for a more thorough and complete understanding of the present disclosure. It should be understood that the drawings and embodiments of the present disclosure are only for exemplary purposes and are not intended to limit the scope of protection of the present disclosure.

[0025] In the description of the embodiments of the present disclosure, the term "including" and similar terms should be understood as open inclusion, that is, "including but not limited to". The term "based on" should be understood as "based at least in part on". The term "one embodiment" or "the embodiment" should be understood as "at least one embodiment". The terms "first", "second", etc. may refer to different or the same objects, unless explicitly stated. Other explicit and implicit definitions may also be included below.

[0026] As mentioned above, SQL rewriting is of great significance in improving query efficiency and compatibility. However, existing SQL rewriting technologies have significant defects and often cannot effectively solve the rewriting needs of complex queries. Traditional SQL rewriting methods often perform poorly when dealing with syntax differences, optimizing query structures, and cross-platform compatibility. For example, the executable rate of rewritten SQL statements is not high, and syntax errors, execution failures, and other problems are prone to occur. This deficiency is mainly due to the inaccurate parsing and conversion of the SQL syntax tree and the lack of fine-grained adjustments for the target database.

[0027] To this end, the embodiment of the present disclosure proposes a solution for rewriting SQL, which first generates a syntax tree from an input SQL statement, and performs query relationship analysis through the syntax tree to understand the fields and data tables in SQL and their relationships, and then identifies the nodes that need to be adjusted in the SQL syntax tree according to the syntax rules of the target database and the query relationship analysis results, and finally modifies these identified nodes to generate a rewritten SQL statement. Thus, by identifying and adjusting the nodes that need to be modified through syntax rules and query relationship analysis, not only can the parts that need to be adjusted be accurately determined, but also the rewritten SQL statement can be ensured to comply with the syntax specifications of the target database, thereby significantly improving the executable rate of SQL rewriting.

[0028] Figure 1 Schematic diagram of an example environment in which the device and / or method according to an embodiment of the present disclosure may be implemented. Figure 1 As shown, the example environment 100 may include a computing device 110, which may be a user terminal, a mobile device, a computer, etc., or a computing system, a single server, a distributed server, or a cloud-based server. The computing device 110 may receive an input SQL 120. In some embodiments, the input SQL may be an SQL generated by a large model. Due to the randomness of the large model, the output SQL syntax and aggregation and other aspects may be missing to a certain extent, resulting in the SQL not being executable SQL, so it needs to be adjusted by SQL rewriting. For example, the input SQL may have confusion of different database knowledge, or a mixed use of function syntax. The management systems of different databases have their own unique grammar rules and function implementations. For example, MySQL uses the CONCAT function for string concatenation, while PostgreSQL uses the "||" operator. Therefore, it is necessary to take into account the specific syntax of the target database when rewriting SQL to ensure the compatibility and executability of SQL.

[0029] The computing device can generate a syntax tree 140 by parsing the input SQL 120. The syntax tree is also called an abstract syntax tree (AST), which is used to represent the syntax structure of the source code or text, and each node represents a syntax element, such as an operator, a keyword, a variable, or a statement. In addition, by performing lineage analysis on the syntax tree 140, a query relationship analysis 150 of the input SQL 120 can be generated. For example, the lineage analysis can trace the data transmission path and identify the source, reference, and dependency of each field and data table by parsing the syntax tree.

[0030] In addition, the query relationship analysis 150 may include field query analysis, data table query analysis, and field tracking analysis. Through the query relationship analysis 150 and the grammar rules 160 of the target database, an adjustment node 180 may be generated. For example, through the query relationship analysis 150, a field node representing inconsistent field naming in the grammar tree 140 may be used as an adjustment node 170, or a function node whose function grammar does not conform to the grammar rules 160 may be used as an adjustment node 170. By adjusting the adjustment node 170, the computing device 110 may generate a rewritten SQL 180. For example, if the target database is PostgreSQL, the computing device 110 may adjust the CONCAT function node in the grammar tree 140 and rewrite it into the "||" operator supported by the target database, thereby generating a rewritten SQL 180 that conforms to the grammar of the target database. This adjustment ensures that the rewritten SQL can be correctly executed in the target database.

[0031] It should be understood that the architecture and functions in the example environment 100 are described for exemplary purposes only and do not imply any limitation on the scope of the present disclosure. Embodiments of the present disclosure may also be applied to other environments with different structures and / or functions.

[0032] The following will combine Figures 2 to 8 The process according to the embodiment of the present disclosure is described in detail. For ease of understanding, the specific data mentioned in the following description are exemplary and are not intended to limit the scope of protection of the present disclosure. It is understood that the embodiments described below may also include additional actions not shown and / or the actions shown may be omitted, and the scope of the present disclosure is not limited in this respect.

[0033] Figure 2 FIG. 2 is a flowchart of a method 200 for rewriting SQL according to an embodiment of the present disclosure. At block 202, a query relationship analysis of the input SQL may be generated based on a syntax tree of the input SQL. For example, referring to Figure 1 , the computing device 110 may generate a query relationship analysis 150 of the input SQL 120 based on the syntax tree 140 of the input SQL 120. At block 204, nodes in the syntax tree that need to be adjusted may be identified based on the syntax rules of the target database and the query relationship analysis. Figure 1 The computing device 110 may identify nodes 170 that need to be adjusted in the syntax tree 140 based on the grammar rules 160 of the target database and the query relationship analysis 150 .

[0034] At block 206, the rewritten SQL corresponding to the target database may be generated by adjusting the node. Figure 1, the computing device 110 can generate a rewritten SQL 180 corresponding to the target database by adjusting the node 170. Therefore, according to the method 200 of the embodiment of the present disclosure, the nodes that need to be modified are identified and adjusted through grammatical rules and query relationship analysis, which can not only accurately determine the parts that need to be adjusted, but also ensure that the rewritten SQL statement complies with the grammatical specifications of the target database, thereby significantly improving the executable rate of SQL rewriting.

[0035] Figure 3 A flowchart of a process 300 of rewriting SQL in a large model scenario according to an embodiment of the present disclosure is shown. Using natural language to query structured data in a large model (e.g., a text-to-SQL conversion (Text2SQL) model) scenario is a current industry trend. For example, the Text2SQL model can automatically convert the natural language input by the user into SQL statements, improve the efficiency of SQL writing, and even enable users without programming experience to implement SQL writing, lowering the coding threshold. However, even if the large model can correctly understand the user's intention, the generated SQL may also have various problems such as database non-correspondence, confusing naming, and incorrect function usage, resulting in a low executable rate of the generated SQL. Process 300 shows a solution for implementing an embodiment of the present disclosure in a large model scenario, which can help improve the executable rate of the generated SQL and help the large model to be better used in actual work. At box 302, the generated SQL can be obtained from the Text2SQL model. For example, the user inputs the query intention or prompt content to the Text2SQL model, and can specify the target database (e.g., requiring execution in MySQL), and the Text2SQL model can generate the corresponding SQL, but the generated SQL may not be executable due to various reasons.

[0036] At box 304, an abstract syntax tree may be generated by parsing the input SQL. For example, the input SQL may be parsed by a parser generator to generate an abstract syntax tree corresponding to the input SQL. The example parser generator used in the embodiments of the present disclosure may be ANTLR, which is used to read, process, execute or translate structured text or binary files. For example, ANTLR may be used to perform syntax parsing and lexical parsing on the input SQL, thereby generating an abstract syntax tree. It should be understood that the embodiments of the present disclosure do not limit the parser generator used, and other parser generators may also be used. This will be discussed later in conjunction with the examples of parser generators used in the present disclosure. Figure 4A and Figure 4B Describe the implementation of the SQL parsing process in detail.

[0037] At box 306, the abstract syntax tree can be processed through SQL lineage analysis to generate lineage analysis results. For example, SQL lineage analysis can track and analyze the data flow and field sources in SQL query statements, and determine the source, destination, and relationship of each field, table, and alias in the SQL query. In some embodiments, a lineage analysis result can be generated, which may include field alias identification, table alias identification, and field tracking path. For example, fields generated by a large model may be given aliases, resulting in inconsistent field names. Through field alias identification, different names for fields can be reconciled. For example, the field list in the SELECT statement and any aliases used in the query process can be parsed, which also includes identifying fields and aliases used in subqueries and join queries. This will be discussed later in conjunction with Figure 5 Describe in detail the implementation of the SQL lineage analysis process.

[0038] At box 308, SQL rewriting can be completed based on the abstract syntax tree and the results of lineage analysis. For example, the visitor mode of ANTLR can be used to traverse the abstract syntax tree, and combined with the results of lineage analysis, to identify and abstract syntax tree nodes that need syntax adjustment. For example, nodes with incorrect function types, nodes with incorrect function parameters, and so on. Then, the nodes can be converted according to the syntax rules of different databases. For example, change the function name, adjust SQL keywords, and modify the structure of SQL expressions, etc. Finally, each node can be recursively traversed to convert the rewritten abstract syntax tree back to SQL statements. Followed by the combination Fig. 6A and Figure 6B The implementation of the SQL rewriting process is described in detail. Therefore, the process 300 of rewriting SQL in a large model scenario provided by the embodiment of the present disclosure can improve the executability of SQL statements, thereby promoting the application and implementation of the large model technology in actual work scenarios.

[0039] Figure 4A FIG. 4 is a flowchart showing a process 400A of SQL parsing according to an embodiment of the present disclosure. Figure 4B A schematic diagram of the architecture 400B of the SQL parsing module according to an embodiment of the present disclosure is shown below. Figure 4A and Figure 4B The implementation of SQL parsing is described in detail. At block 402, a lexical analyzer may be used to perform lexical analysis on SQL to generate a sequence of lexical units. Figure 4BAs shown, SQL 410 can be passed into runtime environment 408, and lexical analyzer 416 can perform lexical analysis on SQL 410 to generate a sequence of lexical units. Parser generator 414 can generate lexical analyzer 416 and grammatical analyzer 418 through grammar definition file 412. In some embodiments, the type of database corresponding to the input SQL can be determined through the front-end recognition service, and the grammar definition file 412 matching the database can be selected. In some embodiments, grammar definition file 412 has parsed and processed for error instances. For example, data tracing problems caused by mismatched field aliases, and grammatical format errors, etc.

[0040] Return to reference Figure 4A At block 404, a syntax analyzer may be used to perform syntax analysis to assemble a sequence of lexical units into an expression. Figure 4B As shown, the syntax analyzer 418 can assemble the lexical unit sequence into an expression according to the grammatical rules of SQL. For example, the syntax analyzer 418 can check whether the combination of the lexical unit sequence conforms to the specification of the SQL grammar. In some embodiments, the grammar of SQL can be parsed for compatibility. For example, if a syntax error is parsed, such as missing keywords or mismatched brackets or whether the SELECT statement correctly includes the FROM clause, the syntax analyzer 418 can also parse to the corresponding node of the abstract syntax tree 420.

[0041] Return to reference Figure 4A At block 406, a syntax analyzer may be used to construct the syntax expression into an abstract syntax tree. Figure 4B As shown, the parser 418 can construct an abstract syntax tree 420 based on the expression of the lexical unit sequence. For example, each node in the abstract syntax tree 420 represents a constituent element in the SQL statement, such as the entire query, the SELECT clause, the FROM clause, the conditional expression, etc. The root node of the abstract syntax tree 420 can represent the entire query, and the child nodes represent the clauses and expressions. In addition, the child nodes can also represent more specific elements, such as field names, table names, operators, etc. In the process of constructing the abstract syntax tree 420, the callback listener 422 can monitor and process the events generated during the parsing process, and the tree traverser 424 can traverse the abstract syntax tree 422, so as to perform semantic analysis and adjustment on the SQL statement.

[0042] Figure 5A flow chart of a process 500 of SQL lineage analysis according to an embodiment of the present disclosure is shown. Lineage analysis can track the source and flow of data tables and fields in a data warehouse, display the transfer path of data from the source to the final location, and generate SQL query relationship analysis. Query relationship analysis can include field identification analysis, data table identification analysis, and field tracking analysis. In the scenario where a large model generates SQL queries as described above, SQL lineage analysis can solve the problem that the large model does not have a sufficient understanding of the data set fields, resulting in incorrect field alias references, table aliases of common table expressions (CTEs), and other errors.

[0043] refer to Figure 5 , at box 502, a correspondence between field names and field aliases can be established, and field queries and analyses can be performed. For example, fields generated by the Text2SQL model may be given aliases, but the field naming may be confusing due to inconsistency in the alias assignment. For example, the original field "first_name" may be given an alias "fn" or "fname", but they actually all point to the "first_name" field. Therefore, by establishing a correspondence between field names and field aliases, a mapping result between fields and aliases can be generated, and a one-to-one mapping or a one-to-many mapping can be established for each field and alias, and these relationships can be recorded for subsequent analysis. For example, the field list in a SELECT statement and any aliases used in the query process can be parsed based on the abstract syntax tree, including identifying fields and aliases used in subqueries and join queries.

[0044] At box 504, a correspondence between a data table and an alias, i.e., a data table query analysis, can be established. For example, it is possible to track in which data tables each alias is used and how it is associated with fields in other data tables. If multiple data tables are used in an SQL query, and these data tables are referenced and connected through aliases, the actual data tables and their fields corresponding to the aliases can be collected. In some embodiments, the data tables and their aliases in the FROM clause can be identified, it is determined whether each data table has an alias, and the correspondence between the data table and the alias is recorded. For example, if the FROM clause is "FROM usersas u", then the mapping between the data table "users" and the alias "u" can be determined, thereby obtaining the correspondence between the data table and the alias.

[0045] At box 506, fields and selection and propagation paths can be tracked, that is, field tracking analysis. For example, you can track which fields are selected from the data table (for example, through a SELECT statement), and how these fields are propagated and transformed through the original data table through query operations (such as JOIN, GROUP BY, ORDER BY, etc.). Since the data set fields are generated by processing the base table fields, the data set fields have added unique properties compared to the base table fields. For example, whether the field has been aggregated, whether it is a LOD expression (level of detail expression), etc. In addition, the tracking process also includes the calculation and conversion of field values, and how the field is modified by aggregate functions or conditional expressions. By tracking fields and selection and propagation paths, detailed field-level lineage analysis information can be generated, thereby providing detailed information for each ladder table, which helps to repair the alias information missing from the SQL.

[0046] Fig. 6A A flowchart of a process 600A of SQL rewriting according to an embodiment of the present disclosure is shown. At box 602, nodes that need to be adjusted in the abstract syntax tree can be marked. For example, the nodes in the syntax tree can be identified and marked to determine which nodes need to be adjusted according to the grammatical rules of the target database. For example, the input SQL is a target database for ClickHouse, but it includes the SQL syntax of MySQL data, so it needs to be adjusted according to the grammatical rules of the target database. In some embodiments, the data of the marked node can be saved. For example, the node position, node type and adjustment reason of the marked node can be saved.

[0047] In some embodiments, marking the nodes may include traversing each node in the abstract syntax tree via a visitor pattern. Figure 6B FIG. 6 is a schematic diagram showing a process 600B of traversing an abstract syntax tree using a visitor pattern according to an embodiment of the present disclosure. Figure 6B As shown, the abstract syntax tree 610 may include StatContext (statement context), AssignContext (assignment context), ExprContext (expression context) and TerminalNode (terminal node, such as variable name and constant value), wherein the dotted line 612 indicates that the abstract syntax tree 610 can be traversed by pre-order traversal. The visitor pattern 614 may include the MyVisitor class and the corresponding application programming interface (API). By traversing the abstract syntax tree through the visitor pattern, the algorithm can be separated from the object structure, so that new operations can be defined without modifying the object.

[0048] Return to reference Fig. 6A, at box 604, the marked nodes can be adjusted to generate a rewritten abstract syntax tree. For example, the marked nodes can be subjected to a necessary syntax conversion according to the syntax requirements of the target database. This process includes applying predefined conversion rules to adjust the syntax structure of the nodes. In some embodiments, the syntax adjustment of the nodes may include changing the function name, adjusting the SQL keywords, or modifying the structure of the SQL expression. For example, if the SQL function naming does not meet the syntax requirements of the target data source, the function naming needs to be adjusted. In addition, if the target database does not support the CONCAT function, it may need to be converted to the "||" operator to implement the concatenation function. In some embodiments, a rewritten syntax tree can be generated based on the data and field tracking analysis of the node. In some embodiments, the data of the node can be subjected to syntax conversion based on the syntax rules of the target database. In some embodiments, missing nodes in the syntax tree can be completed based on field tracking analysis.

[0049] At block 606, the rewritten abstract syntax tree may be converted back to an SQL statement to generate the rewritten SQL. For example, the nodes in the abstract syntax tree may be recursively traversed, and different parts of the SQL statement may be constructed according to the type and content of each node when traversing each node, and then all the SQL fragments of the parts may be combined into a complete SQL statement and output as the final rewritten SQL.

[0050] Figure 7 A block diagram of an apparatus 700 for rewriting SQL according to an embodiment of the present disclosure is shown. The apparatus 700 includes a query relationship generation module 702, which is configured to generate a query relationship analysis of the input SQL based on a syntax tree of the input SQL. The apparatus 700 also includes an adjustment node identification module 704, which is configured to identify nodes that need to be adjusted in the syntax tree based on the syntax rules of the target database and the query relationship analysis. In addition, the apparatus 700 also includes a rewriting SQL generation module 706, which is configured to generate a rewriting SQL corresponding to the target database by adjusting the nodes.

[0051] Figure 8 A block diagram of an electronic device 800 is shown in accordance with certain embodiments of the present disclosure. Figure 8 8 is a block diagram of an electronic device 800 according to some embodiments of the present disclosure. The device 800 may be a device or apparatus described in an embodiment of the present disclosure. Figure 8As shown, the device 800 includes a central processing unit (CPU) and / or a graphics processing unit (GPU) 801, which can perform various appropriate actions and processes according to computer program instructions stored in a read-only memory (ROM) 802 or loaded from a storage unit 808 to a random access memory (RAM) 803. In the RAM 803, various programs and data required for the operation of the device 800 can also be stored. The CPU / GPU 801, the ROM 802, and the RAM 803 are connected to each other via a bus 804. An input / output (I / O) interface 805 is also connected to the bus 804. Although not shown in FIG. Figure 8 As shown in FIG. 8 , device 800 may also include a co-processor.

[0052] A number of components in the device 800 are connected to the I / O interface 805, including: an input unit 806, such as a keyboard, a mouse, etc.; an output unit 807, such as various types of displays, speakers, etc.; a storage unit 808, such as a disk, an optical disk, etc.; and a communication unit 809, such as a network card, a modem, a wireless communication transceiver, etc. The communication unit 809 allows the device 800 to exchange information / data with other devices through a computer network such as the Internet and / or various telecommunication networks.

[0053] The various methods or processes described above may be performed by the CPU / GPU 801. For example, in some embodiments, the methods may be implemented as a computer software program, which is tangibly contained in a machine-readable medium, such as a storage unit 808. In some embodiments, part or all of the computer program may be loaded and / or installed on the device 800 via the ROM 802 and / or the communication unit 809. When the computer program is loaded into the RAM 803 and executed by the CPU / GPU 801, one or more steps or actions in the methods or processes described above may be performed.

[0054] In some embodiments, the methods and processes described above may be implemented as a computer program product. The computer program product may include a computer-readable storage medium carrying computer-readable program instructions for executing various aspects of the present disclosure.

[0055] Computer readable storage medium can be a tangible device that can hold and store instructions used by an instruction execution device. Computer readable storage medium can be, for example, but not limited to, an electrical storage device, a magnetic storage device, an optical storage device, an electromagnetic storage device, a semiconductor storage device, or any suitable combination thereof. More specific examples (non-exhaustive list) of computer readable storage medium include: a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), a static random access memory (SRAM), a portable compact disk read-only memory (CD-ROM), a digital versatile disk (DVD), a memory stick, a floppy disk, a mechanical encoding device, for example, a punch card or a convex structure in a groove on which instructions are stored, and any suitable combination thereof. The computer readable storage medium used here is not interpreted as a transient signal itself, such as a radio wave or other freely propagating electromagnetic wave, an electromagnetic wave propagated by a waveguide or other transmission medium (for example, a light pulse by an optical fiber cable), or an electrical signal transmitted by a wire.

[0056] The computer-readable program instructions described herein can be downloaded from a computer-readable storage medium to each computing / processing device, or downloaded to an external computer or external storage device via a network, such as the Internet, a local area network, a wide area network, and / or a wireless network. The network can include copper transmission cables, optical fiber transmissions, wireless transmissions, routers, firewalls, switches, gateway computers, and / or edge servers. The network adapter card or network interface in each computing / processing device receives the computer-readable program instructions from the network and forwards the computer-readable program instructions for storage in the computer-readable storage medium in each computing / processing device.

[0057] The computer program instructions for performing the disclosed operation may be assembler instructions, instruction set architecture (ISA) instructions, machine instructions, machine-related instructions, microcode, firmware instructions, state setting data, or source code or object code written in any combination of one or more programming languages, programming languages ​​including object-oriented programming languages, and conventional procedural programming languages. Computer-readable program instructions may be executed completely on a user's computer, partially on a user's computer, executed as an independent software package, partially on a user's computer, partially on a remote computer, or completely on a remote computer or server. In the case of a remote computer, the remote computer may be connected to the user's computer via any type of network, including a local area network (LAN) or a wide area network (WAN), or may be connected to an external computer (e.g., utilizing an Internet service provider to connect via the Internet). In certain embodiments, by utilizing the state information of a computer-readable program instruction to customize an electronic circuit, such as a programmable logic circuit, a field programmable gate array (FPGA), or a programmable logic array (PLA), the electronic circuit may execute a computer-readable program instruction, thereby realizing various aspects of the present disclosure.

[0058] These computer-readable program instructions can be provided to a processing unit of a general-purpose computer, a special-purpose computer, or other programmable data processing device, thereby producing a machine, so that when these instructions are executed by the processing unit of the computer or other programmable data processing device, a device that implements the functions / actions specified in one or more boxes in the flowchart and / or block diagram is generated. These computer-readable program instructions can also be stored in a computer-readable storage medium, and these instructions cause the computer, programmable data processing device, and / or other equipment to work in a specific manner, so that the computer-readable medium storing the instructions includes a manufactured product, which includes instructions for implementing various aspects of the functions / actions specified in one or more boxes in the flowchart and / or block diagram.

[0059] Computer-readable program instructions may also be loaded onto a computer, other programmable data processing apparatus, or other device so that a series of operating steps are performed on the computer, other programmable data processing apparatus, or other device to produce a computer-implemented process, thereby causing the instructions executed on the computer, other programmable data processing apparatus, or other device to implement the functions / actions specified in one or more boxes in the flowchart and / or block diagram.

[0060] The flow chart and block diagram in the accompanying drawings show the possible architecture, function and operation of the equipment, method and computer program product according to multiple embodiments of the present disclosure. In this regard, each frame in the flow chart or block diagram can represent a part of a module, program segment or instruction, and a part of the module, program segment or instruction includes one or more executable instructions for realizing the specified logical function. In some alternative implementations, the function marked in the frame can also occur in a sequence different from that marked in the accompanying drawings. For example, two continuous frames can actually be executed substantially in parallel, and they can also be executed in the opposite order sometimes, depending on the functions involved. It should also be noted that each frame in the block diagram and / or flow chart, and the combination of frames in the block diagram and / or flow chart can be implemented with a dedicated hardware-based system that performs a specified function or action, or can be implemented with a combination of dedicated hardware and computer instructions.

[0061] The embodiments of the present disclosure have been described above, and the above description is exemplary, not exhaustive, and is not limited to the disclosed embodiments. Many modifications and changes will be apparent to those of ordinary skill in the art without departing from the scope and spirit of the described embodiments. The choice of terms used herein is intended to best explain the principles of the embodiments, practical applications, or technical improvements to the technology in the market, or to enable other persons of ordinary skill in the art to understand the embodiments disclosed herein.

[0062] Some example implementations of the present disclosure are listed below.

[0063] Example 1. A method for rewriting structured query language SQL, comprising:

[0064] Based on the syntax tree of the input SQL, generate a query relation analysis of the input SQL; and

[0065] Based on the grammatical rules of the target database and the query relationship analysis, identifying nodes in the grammatical tree that need to be adjusted; and

[0066] By adjusting the nodes, a rewritten SQL corresponding to the target database is generated.

[0067] Example 2. The method according to Example 1 further includes:

[0068] Generate a lexical unit sequence of the input SQL using a lexical analyzer; and

[0069] Based on the lexical unit sequence, a syntax analyzer is used to generate the syntax tree of the input SQL.

[0070] Example 3. The method according to any one of Examples 1-2, wherein the input SQL is generated by a model, and the target database is specified in a prompt content of the model.

[0071] Example 4. The method according to any one of Examples 1-3, further comprising:

[0072] Obtaining a predefined grammar definition file corresponding to the target database; and

[0073] Based on the grammar definition file, the lexical analyzer and the grammar analyzer are generated by using a parser generator.

[0074] Example 5. The method according to any one of Examples 1-4, wherein the query relationship analysis includes field identification analysis, data table identification analysis, and field tracking analysis, and the query relationship analysis for generating the input SQL includes:

[0075] Generate the field query analysis by parsing the field name and field alias of the field node in the syntax tree;

[0076] Generate the data table query analysis by parsing the table references and table connections of the data table nodes in the syntax tree; and

[0077] The field tracking analysis is generated by tracking the calculation process and the conversion process of the field nodes in the syntax tree.

[0078] Example 6. The method according to any one of Examples 1-5, wherein identifying the node in the syntax tree that needs to be adjusted comprises:

[0079] In response to a function node not complying with the grammatical rule, identifying the function node;

[0080] In response to the field query analysis indicating that the name of the field node is inconsistent, identifying the field node;

[0081] In response to the data table query analysis indicating that the names of data table nodes are inconsistent, the data table nodes are identified.

[0082] Example 7. The method according to any one of Examples 1-6, further comprising:

[0083] Generate data of the node that needs to be adjusted, the data at least including the node position, the node type and the adjustment type.

[0084] Example 8. The method according to any one of Examples 1-7, wherein generating the rewritten SQL corresponding to the target database comprises:

[0085] Generate a rewrite syntax tree based on the data of the node and the field tracking analysis;

[0086] Based on the rewritten syntax tree, the rewritten SQL is generated through recursive traversal.

[0087] Example 9. The method according to any one of Examples 1-8, wherein generating the rewritten syntax tree comprises:

[0088] Based on the grammatical rules of the target database, performing grammatical conversion on the data of the node; and

[0089] Based on the field tracking analysis, missing nodes in the syntax tree are supplemented.

[0090] Example 10. A device for rewriting structured query language SQL, comprising:

[0091] A query relationship generation module, configured to generate a query relationship analysis of the input SQL based on a syntax tree of the input SQL; and

[0092] an adjustment node identification module configured to identify nodes that need to be adjusted in the syntax tree based on the grammatical rules of the target database and the query relationship analysis; and

[0093] The rewriting SQL generation module is configured to generate rewriting SQL corresponding to the target database by adjusting the nodes.

[0094] Example 11. The apparatus of Example 10, further comprising:

[0095] A lexical sequence generation module, which generates a lexical unit sequence of the input SQL using a lexical analyzer; and

[0096] The syntax tree generation module is configured to generate the syntax tree of the input SQL based on the lexical unit sequence using a syntax analyzer.

[0097] Example 12. An apparatus according to any one of Examples 10-11, wherein the input SQL is generated by a model, and the target database is specified in a prompt content of the model.

[0098] Example 13. The apparatus of any one of Examples 10-12, further comprising:

[0099] A grammar definition acquisition module, configured to acquire a predefined grammar definition file corresponding to the target database; and

[0100] The analyzer generation module is configured to generate the lexical analyzer and the grammatical analyzer by using a parser generator based on the grammar definition file.

[0101] Example 14. The apparatus according to any one of Examples 10-13, wherein the query relationship analysis includes field identification analysis, data table identification analysis, and field tracking analysis, and the query relationship analysis for generating the input SQL includes:

[0102] A field query generation module, configured to generate the field query analysis by parsing the field name and field alias of the field node in the syntax tree;

[0103] a data table query generation module configured to generate the data table query analysis by parsing table references and table connections of data table nodes in the syntax tree; and

[0104] The field tracking generation module is configured to generate the field tracking analysis by tracking the calculation process and conversion process of the field node in the syntax tree.

[0105] Example 15. The apparatus of any one of Examples 10-14, wherein identifying the node in the syntax tree that needs to be adjusted comprises:

[0106] A function node identification module, configured to identify the function node in response to the function node not conforming to the grammatical rule;

[0107] A field node identification module, configured to identify the field node in response to the field query analysis indicating that the name of the field node is inconsistent;

[0108] The data table node identification module is configured to identify the data table node in response to the data table query analysis indicating that the name of the data table node is inconsistent.

[0109] Example 16. The apparatus of any one of Examples 10-15, further comprising:

[0110] The node data storage module is configured to generate data of the node that needs to be adjusted, and the data at least includes the node position, node type and adjustment type.

[0111] Example 17. The apparatus according to any one of Examples 10-16, wherein generating the rewritten SQL corresponding to the target database comprises:

[0112] A rewrite syntax tree generation module, configured to generate a rewrite syntax tree based on the data of the node and the field tracking analysis;

[0113] The rewriting SQL second generating module is configured to generate the rewriting SQL through recursive traversal based on the rewriting syntax tree.

[0114] Example 18. The apparatus according to any one of Examples 10-17, wherein generating the generated rewritten syntax tree comprises:

[0115] a node syntax conversion module, configured to perform syntax conversion on the data of the node based on the syntax rule of the target database; and

[0116] The missing node completion module is configured to complete the missing nodes in the syntax tree based on the field tracking analysis.

[0117] Example 19. An electronic device comprising:

[0118] Processor; and

[0119] A memory coupled to the processor, the memory having instructions stored therein, wherein when the instructions are executed by the processor, the electronic device performs actions, the actions comprising:

[0120] Based on the syntax tree of the input SQL, generate a query relation analysis of the input SQL; and

[0121] Based on the grammatical rules of the target database and the query relationship analysis, identifying nodes in the grammatical tree that need to be adjusted; and

[0122] By adjusting the nodes, a rewritten SQL corresponding to the target database is generated.

[0123] Example 20. The electronic device of Example 19, further comprising:

[0124] Generate a lexical unit sequence of the input SQL using a lexical analyzer; and

[0125] Based on the lexical unit sequence, a syntax analyzer is used to generate the syntax tree of the input SQL.

[0126] Example 21. An electronic device according to any one of Examples 19-20, wherein the input SQL is generated by a model, and the target database is specified in a prompt content of the model.

[0127] Example 22. The electronic device of any of Examples 19-21, further comprising:

[0128] Obtaining a predefined grammar definition file corresponding to the target database; and

[0129] Based on the grammar definition file, the lexical analyzer and the grammar analyzer are generated by using a parser generator.

[0130] Example 23. An electronic device according to any one of Examples 19-22, wherein the query relationship analysis includes field identification analysis, data table identification analysis, and field tracking analysis, and the query relationship analysis for generating the input SQL includes:

[0131] Generate the field query analysis by parsing the field name and field alias of the field node in the syntax tree;

[0132] Generate the data table query analysis by parsing the table references and table connections of the data table nodes in the syntax tree; and

[0133] The field tracking analysis is generated by tracking the calculation process and the conversion process of the field nodes in the syntax tree.

[0134] Example 24. The electronic device of any one of Examples 19-23, wherein identifying the node in the syntax tree that needs to be adjusted comprises:

[0135] In response to a function node not complying with the grammatical rule, identifying the function node;

[0136] In response to the field query analysis indicating that the name of the field node is inconsistent, identifying the field node;

[0137] In response to the data table query analysis indicating that the names of data table nodes are inconsistent, the data table nodes are identified.

[0138] Example 25. The electronic device of any of Examples 19-24, further comprising:

[0139] Generate data of the node that needs to be adjusted, the data at least including the node position, the node type and the adjustment type.

[0140] Example 26. The electronic device according to any one of Examples 19-25, wherein generating the rewritten SQL corresponding to the target database comprises:

[0141] Generate a rewrite syntax tree based on the data of the node and the field tracking analysis;

[0142] Based on the rewritten syntax tree, the rewritten SQL is generated through recursive traversal.

[0143] Example 27. The electronic device of any one of Examples 19-26, wherein generating the generated rewrite syntax tree comprises:

[0144] Based on the grammatical rules of the target database, performing grammatical conversion on the data of the node; and

[0145] Based on the field tracking analysis, missing nodes in the syntax tree are completed.

[0146] Example 28. A computer-readable storage medium having one or more computer instructions stored thereon, wherein the one or more computer instructions are executed by a processor to implement the method according to any one of Examples 1 to 9.

[0147] Example 29. A computer program product tangibly stored on a computer-readable medium and comprising computer-executable instructions that, when executed by a device, cause the device to perform a method according to any one of Examples 1 to 9.

[0148] Although the disclosure has been described in language specific to structural features and / or methodological logical actions, it should be understood that the subject matter defined in the appended claims is not necessarily limited to the specific features or actions described above. Instead, the specific features and actions described above are merely example forms of implementing the claims.

Claims

1. A method for rewriting structured query language SQL, comprising: Generate a query relationship analysis of the input SQL based on the syntax tree of the input SQL; as well as Based on the grammatical rules of the target database and the query relationship analysis, identifying nodes that need to be adjusted in the grammar tree; as well as By adjusting the nodes, a rewritten SQL corresponding to the target database is generated.

2. The method according to claim 1, further comprising: Generate a lexical unit sequence of the input SQL using a lexical analyzer; as well as Based on the lexical unit sequence, a syntax analyzer is used to generate the syntax tree of the input SQL. 3 . The method according to claim 2 , wherein the input SQL is generated by a model, and the target database is specified in a prompt content of the model.

4. The method according to claim 3, further comprising: Obtaining a predefined grammar definition file corresponding to the target database; as well as Based on the grammar definition file, the lexical analyzer and the grammar analyzer are generated by using a parser generator.

5. The method according to claim 1, wherein the query relationship analysis includes field identification analysis, data table identification analysis and field tracking analysis, and the query relationship analysis for generating the input SQL includes: Generate the field query analysis by parsing the field name and field alias of the field node in the syntax tree; Generate the data table query analysis by parsing the table references and table connections of the data table nodes in the syntax tree; as well as The field tracking analysis is generated by tracking the calculation process and the conversion process of the field nodes in the syntax tree.

6. The method according to claim 5, wherein identifying the node in the syntax tree that needs to be adjusted comprises: In response to a function node not complying with the grammatical rule, identifying the function node; In response to the field query analysis indicating that the name of the field node is inconsistent, identifying the field node; In response to the data table query analysis indicating that the names of data table nodes are inconsistent, the data table nodes are identified.

7. The method according to claim 6, further comprising: Generate data of the node that needs to be adjusted, the data at least including the node position, the node type and the adjustment type.

8. The method according to claim 7, wherein generating the rewritten SQL corresponding to the target database comprises: Generate a rewrite syntax tree based on the data of the node and the field tracking analysis; Based on the rewritten syntax tree, the rewritten SQL is generated through recursive traversal.

9. The method according to claim 8, wherein generating the rewritten syntax tree comprises: Based on the grammatical rules of the target database, performing grammatical conversion on the data of the node; as well as Based on the field tracking analysis, missing nodes in the syntax tree are completed.

10. A device for rewriting structured query language SQL, comprising: A query relationship generation module, configured to generate a query relationship analysis of the input SQL based on a syntax tree of the input SQL; as well as An adjustment node identification module is configured to identify nodes that need to be adjusted in the syntax tree based on the grammatical rules of the target database and the query relationship analysis; as well as The rewriting SQL generation module is configured to generate rewriting SQL corresponding to the target database by adjusting the nodes.

11. An electronic device, comprising: processor; as well as A memory coupled to the processor, the memory having instructions stored therein, wherein when the instructions are executed by the processor, the electronic device executes the method according to any one of claims 1 to 9.

12. A computer program product tangibly stored on a non-transitory computer readable medium and comprising computer executable instructions for performing the method according to any one of claims 1 to 9.

Citation Information

Patent Citations

  • SQL translation method and system for mimicry database

    CN113467785A

  • Data query method and device based on relational database, medium and product

    CN114138817A

  • Blood relationship extraction method and device and electronic equipment

    CN114265945A

  • Data query method and device, electronic equipment and computer readable storage medium

    CN114328574A

  • Multilayer analysis method of structured query statement, computer equipment and storage medium

    CN114756569A

Cited By

  • Version updating method and device, equipment and storage medium

    CN121455537A