Structured query language conversion method, system and equipment and storage medium

By constructing an abstract syntax tree for HiveQL and using intermediate code to convert nodes into ANSI SQL nodes, the tedious and complex problem of HiveQL to ANSI SQL conversion is solved, achieving efficient and convenient automated conversion and improving data query and analysis efficiency.

CN119025547APending Publication Date: 2024-11-26BEIJINGLUOTA INFORMATION TECHNOLOGYCO LTD
View PDF 0 Cites 5 Cited by

Patent Information

Application Number
CN202411211515.6
Authority / Receiving Office
CN · China
Patent Type
Applications(China)
Current Assignee / Owner
Filing Date
2024-08-30
Publication Date
2024-11-26

AI Technical Summary

Technical Problem

Since most data analysts are familiar with HiveQL but not ANSI SQL, they need to learn additional techniques when using computing engines that only support ANSI SQL, making the construction process cumbersome and complex, and affecting the efficiency of data query and analysis.

Method used

By constructing an abstract syntax tree for HiveQL and using intermediate code to convert nodes into ANSI SQL nodes, automated conversion from HiveQL to ANSI SQL is achieved, including lexical analysis, syntax analysis, semantic analysis, and intermediate code representation conversion.

Benefits of technology

It achieves efficient and convenient conversion from HiveQL to ANSI SQL, lowers the barrier to entry for using ANSI SQL, improves data query and analysis efficiency, and enhances the user experience for analysts.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN119025547A_ABST
    Figure CN119025547A_ABST
Patent Text Reader

Abstract

The embodiment of the invention discloses a structured query language conversion method, system and device and a storage medium. According to the technical scheme provided by the embodiment of the invention, a first structured query language of a HiveQL standard is input, lexical analysis and grammatical analysis are performed on the first structured query language, and an abstract syntax tree of the first structured query language is generated; traversing the abstract syntax tree, converting each intermediate code of the abstract syntax tree based on a syntax node class of a third-party library to obtain a corresponding first node, and converting the first node into an intermediate node based on a relational algebra expression tree; and converting the intermediate node into a second node based on the grammar rule of the ANSI SQL, and outputting a second structured query language of the ANSI SQL standard based on the second node. By the adoption of the technical means, efficient, convenient and automatic structured query language conversion can be achieved, an analyst does not need to execute manual editing of ANSI SQL, the use threshold of a calculation engine of the ANSI SQL standard is lowered, and data query and analysis efficiency is improved.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The embodiments of the present application relate to the field of data processing technology, and in particular to a structured query language conversion method, system, device and storage medium. Background Art

[0002] At present, in big data computing scenarios, SQL (Structured Query Language) is often needed to create, query, update, and delete databases. Among them, the most common SQL standard is HiveQL (Hive Query Language), which is favored by most data analysts for its simple and easy-to-learn syntax. With the changes in SQL technology, some computing engines only support ANSI SQL (American National Standards Institute Structured Query Language) in order to achieve standardization. When using this type of computing engine, analysts can perform corresponding data query operations by building ANSI SQL that meets the standard.

[0003] However, since most data analysts are mainly familiar with the construction standards of HiveQL, for computing engines that only support the ANSI SQL standard, data analysts need to learn their construction standards. The whole process is cumbersome and complicated, resulting in a high threshold for using the computing engine, affecting the efficiency of data query and analysis, and thus affecting the data query and analysis experience of analysts. Summary of the invention

[0004] The embodiments of the present application provide a structured query language conversion method, system, device and storage medium, which can realize the conversion from HiveQL to ANSI SQL, improve the construction efficiency of ANSI SQL, reduce the technical threshold of ANSI SQL construction, and solve the cumbersome and complex technical problem of the construction process of the structured query language of the ANSI SQL standard.

[0005] In a first aspect, an embodiment of the present application provides a structured query language conversion method, comprising:

[0006] Inputting a first structured query language in the HiveQL standard, performing lexical analysis and grammatical analysis on the first structured query language, and generating an abstract syntax tree of the first structured query language;

[0007] Traverse the abstract syntax tree, convert each intermediate code of the abstract syntax tree based on the syntax node class of the third-party library, obtain the corresponding first node, and convert the first node into an intermediate node based on the relational algebra expression tree;

[0008] The intermediate node is converted into a second node based on the grammatical rule of ANSI SQL, and a second structured query language of ANSI SQL standard is output based on the second node.

[0009] In a second aspect, an embodiment of the present application provides a structured query language conversion system, including:

[0010] An analysis module is configured to input a first structured query language in HiveQL standard, perform lexical analysis and syntax analysis on the first structured query language, and generate an abstract syntax tree of the first structured query language;

[0011] A conversion module is configured to traverse the abstract syntax tree, convert each intermediate code of the abstract syntax tree based on the syntax node class of the third-party library, obtain the corresponding first node, and convert the first node into an intermediate node based on the relational algebra expression tree;

[0012] The output module is configured to convert the intermediate node into a second node based on the grammatical rule of ANSI SQL, and output a second structured query language of the ANSI SQL standard based on the second node.

[0013] In a third aspect, an embodiment of the present application provides a structured query language conversion device, including:

[0014] memory and one or more processors;

[0015] The memory is configured to store one or more programs;

[0016] When the one or more programs are executed by the one or more processors, the one or more processors implement the structured query language conversion method as described in the first aspect.

[0017] In a fourth aspect, an embodiment of the present application provides a computer-readable storage medium, wherein the computer-readable storage medium stores computer-executable instructions, and when the computer-executable instructions are executed by a computer processor, the structured query language conversion method as described in the first aspect is configured to be executed.

[0018] In a fifth aspect, an embodiment of the present application provides a computer program product, which includes instructions. When the instructions are executed on a computer or a processor, the computer or the processor executes the structured query language conversion method as described in the first aspect.

[0019] The embodiment of the present application inputs a first structured query language of the HiveQL standard, performs lexical analysis and syntax analysis on the first structured query language, and generates an abstract syntax tree of the first structured query language; traverses the abstract syntax tree, converts each intermediate code of the abstract syntax tree based on the syntax node class of the third-party library, obtains the corresponding first node, and converts the first node into an intermediate node based on the relational algebra expression tree; converts the intermediate node into a second node based on the syntax rule of ANSI SQL, and outputs a second structured query language of the ANSI SQL standard based on the second node. The above technical means are adopted to construct an abstract syntax tree of HiveQL, use the intermediate code to convert the nodes of the abstract syntax tree into intermediate nodes, and convert the intermediate nodes into nodes of ANSI SQL, so as to realize efficient, convenient and automatic structured query language conversion, without the need for analysts to perform manual editing of ANSI SQL, reduce the use threshold of the computing engine of the ANSI SQL standard, and improve the efficiency of data query and analysis.

[0020] Moreover, during the entire process, analysts only need to input the HiveQL standard structured query language to achieve the ANSISQL conversion, achieving a seamless ANSI SQL construction effect and improving the analyst's user experience. BRIEF DESCRIPTION OF THE DRAWINGS

[0021] Figure 1 is a flow chart of a structured query language conversion method provided in an embodiment of the present application;

[0022] Figure 2 It is a flow chart of generating ANSI SQL in an embodiment of the present application;

[0023] Figure 3 is a flow chart for generating the first node in an embodiment of the present application;

[0024] Figure 4 is a flow chart for generating the second node in an embodiment of the present application;

[0025] Figure 5 It is a schematic diagram of structured query language routing in an embodiment of the present application;

[0026] Figure 6 It is a structural diagram of a structured query language conversion system provided in an embodiment of the present application;

[0027] Figure 7 It is a structural schematic diagram of a structured query language conversion device provided in an embodiment of the present application. DETAILED DESCRIPTION

[0028] In order to make the purpose, technical scheme and advantages of the present application clearer, the specific embodiments of the present application are further described in detail below in conjunction with the accompanying drawings. It is understood that the specific embodiments described herein are only used to explain the present application, rather than to limit the present application. It should also be noted that, for the convenience of description, only the part related to the present application but not all the contents are shown in the accompanying drawings. Before discussing the exemplary embodiments in more detail, it should be mentioned that some exemplary embodiments are described as processes or methods depicted as flow charts. Although the flow chart describes each operation (or step) as a sequential process, many of the operations therein can be implemented in parallel, concurrently or simultaneously. In addition, the order of each operation can be rearranged. The process can be terminated when its operation is completed, but it can also have additional steps not included in the accompanying drawings. The process can correspond to a method, a function, a procedure, a subroutine, a subprogram, etc.

[0029] The present application provides a structured query language conversion method, which aims to achieve efficient, convenient and automated structured query language conversion by constructing an abstract syntax tree of HiveQL, using intermediate codes to convert nodes of the abstract syntax tree into intermediate nodes, and then converting the intermediate nodes into nodes of ANSI SQL.

[0030] In relevant data analysis scenarios, more and more data analysts use SQL to implement business needs, and then execute SQL through the computing engine platform to obtain data results. Currently, there are two most commonly used SQL standards: ANSI SQL and HiveQL. Both are query languages ​​based on SQL, but due to their different scenarios and underlying architectures, there are significant differences in design and implementation. Among them, HiveQL is mainly used for offline batch processing of large-scale data, while ANSI SQL is widely used in the management and real-time query of traditional relational databases.

[0031] In the field of big data computing, HiveQL has become the most important query language standard, and many big data analysts and business personnel are very familiar with HiveQL. Since HiveQL syntax is simple and easy to learn, and provides various syntax sugars to assist, it greatly improves the development efficiency of data analysts in writing SQL. In addition, various widely used big data engines (such as Spark and Hive) only support HiveQL, or add various syntax sugars on the basis of HiveQL. However, with the iteration and change of structured query language technology, various emerging big data OLAP (online analytical processing) computing engines have emerged in an endless stream. In order to achieve standardization and wide compatibility, most of these new engines only support ANSI SQL standard input. Therefore, when using these computing engines, data analysts need to master the editing methods of the ANSI SQL standard or convert HiveQL to ANSI SQL.

[0032] Among the related implementation solutions, there is one that supports HiveQL by transforming the open source OLAP engine, which usually involves rewriting the engine's syntax files, abstract syntax trees (AST), and even related codes such as physical execution plans. However, this method requires large changes to the code architecture, complex functional development, and difficult maintenance. In addition, new problems are easily introduced during the modification process, and subsequent updates to the community version are also very complicated. Another implementation solution is to use an SQL conversion tool to convert HiveQL to ANSI SQL. Among them, Apache Calcite is a popular dynamic data management framework written in Java. It provides SQL parsing, optimization, and execution functions, and supports conversion between multiple SQL dialects. However, directly using Calcite for SQL dialect conversion also has the problem of high development complexity.

[0033] Obviously, whether it is to directly construct ANSI SQL or to directly convert HiveQL to ANSI SQL, there are technical problems of cumbersome and complicated operation procedures. Based on this, a structured query language conversion method of an embodiment of the present application is provided to solve the technical problem of cumbersome and complicated construction procedures of the structured query language of the ANSI SQL standard.

[0034] Example:

[0035] Figure 1 A flow chart of a structured query language conversion method provided in an embodiment of the present application is given. The structured query language conversion method provided in this embodiment can be executed by a structured query language conversion device, which can be implemented by software and / or hardware. The structured query language conversion device can be composed of two or more physical entities, or can be composed of one physical entity. Generally speaking, the structured query language conversion device can be a processing device such as a data analysis server, an SQL query server, etc.

[0036] The following description is made by taking the structured query language conversion device as an example to perform the structured query language conversion method. Figure 1 , the structured query language conversion method specifically includes:

[0037] S110 , inputting a first structured query language in HiveQL standard, performing lexical analysis and syntax analysis on the first structured query language, and generating an abstract syntax tree of the first structured query language.

[0038] The structured query language conversion method of the present application can be applied to scenarios such as database migration, cross-platform data integration, data exchange and data computing. By integrating the structured query language conversion method of the application into a structured query language conversion device, during the structured query language conversion process, the user inputs the HiveQL standard structured query language, which can be seamlessly converted into the ANSI SQL standard structured query language.

[0039] In the process of converting HiveQL to ANSI SQL, this application uses Intermediate Code Representation (ICR) to convert HiveQL to ANSI SQL. Figure 2 , by building the abstract syntax tree of HiveQL, using the intermediate code to convert the first node of the abstract syntax tree into the intermediate node, and converting the intermediate node into the second node of ANSI SQL, the conversion process from HiveQL to ANSI SQL is completed. The whole process mainly includes lexical analysis, syntax analysis, semantic analysis, intermediate code representation transformation, etc., to ensure the validity and correctness of the converted SQL statement.

[0040] When performing structured query language conversion, the user inputs the structured query language of the HiveQL standard, which is defined as the first structured query language. By performing lexical analysis on the first structured query language of the HiveQL standard, basic lexical units (Tokens) are decomposed. Then, an abstract syntax tree (AST) is constructed through syntax analysis. In practical applications, a syntax tree can also be constructed based on lexical units for subsequent node conversion operations based on intermediate codes. Based on the abstract syntax tree, a semantic analysis operation is performed to verify whether each element in the syntax tree conforms to the semantic rules and to perform necessary context processing operations. Through the above steps, an intermediate code representation intermediate node can be generated, and the intermediate node adopts an abstract representation form that is independent of the specific SQL dialect. After obtaining the correct intermediate code representation, the intermediate code representation is reversely converted to ANSI SQL. This conversion process ensures that the final ANSI SQL statement is not only syntactically correct, but also retains the semantics of the original HiveQL statement. In this way, HiveQL is efficiently and accurately converted to ANSI SQL. At the same time, through the use of intermediate code representation, the verifiability and maintainability of the conversion process are improved, the correctness of the conversion result is ensured, and it is easy to adapt to the conversion requirements between different SQL dialects.

[0041] The following is a detailed description of the specific conversion process. Based on the fact that the user inputs a SQL string in the form of HiveQL, the ANTLRLexer (lexical analyzer) and ANTLRParser (syntax analyzer) that support HiveQL grammar are generated by using the ANTLR4 (Another Tool for Language Recognition, a powerful parser generation tool for generating lexical analyzers and grammatical analyzers from grammatical descriptions) tool to parse the input, thereby obtaining the Hive-related abstract syntax tree (Hive AST). This step requires the ANTLR4 tool to be installed in advance, and the Hive repository is searched on GitHub (software project hosting platform), and the Hplsql.g4 file is found in the hplsql (client tool) directory, and the ANTLR4 parser is generated using this file. Then, the ANTLRVisitor file generated by ANTLR4 needs to be improved in order to correctly generate the abstract syntax tree (AST). In ANTLR, when the grammar file is compiled, ANTLR generates a series of codes, including a lexical analyzer, a grammatical analyzer, and a visitor (ie, ANTLRVisitor) interface or class (depending on the specific implementation and target language). ANTLRVisitor (or its concrete implementation) allows developers to perform custom operations by traversing the Abstract Syntax Tree (AST).

[0042] Among them, download and install the ANTLR4 tool through the structured query language, and configure the environment variables to use the ANTLR4 command in the command line. Then get the Hive grammar file, search the Hive warehouse on GitHub, and find the Hplsql.g4 file in the hplsql directory. This is the ANTLR4 grammar file that describes the HiveQL grammar. In addition, the grammar file and the abstract syntax tree can be expanded according to actual needs. Then use the ANTLR4 tool to generate the corresponding ANTLRLexer and ANTLRParser files. For example, run the following command: antlr4 Hplsql.g4 javac Hplsql*.java. Further define and improve the ANTLRVisitor class to generate the correct abstract syntax tree (AST). This may require writing a custom visitor mode (Visitor) method to handle specific grammar rules and nodes. Then use the generated ANTLRLexer and ANTLRParser to parse the HiveQL statement entered by the user and generate the Hive AST, so that the construction of the abstract syntax tree can be completed.

[0043] S120, traversing the abstract syntax tree, converting each intermediate code of the abstract syntax tree based on the syntax node class of the third-party library, obtaining the corresponding first node, and converting the first node into an intermediate node based on the relational algebra expression tree.

[0044] After obtaining the abstract syntax tree (AST) of the SQL string, an intermediate node is generated by means of an intermediate code representation. Since most ASTs are of the Query type, for the AST of the query type, this application uses a visitor pattern to traverse each node of the abstract syntax tree from bottom to top and modify it to the corresponding SqlNode-1 (first node). SqlNode-1 is an intermediate representation, and the conversion process is mainly completed in the ParserTreeBuilder (server tree builder) class of the third-party library Calcite.

[0045] Among them, refer to Figure 3 , traverse the abstract syntax tree, convert the intermediate codes of the abstract syntax tree based on the syntax node class of the third-party library, and obtain the corresponding first node, including:

[0046] S1201, perform semantic verification on the abstract syntax tree. After completing the semantic verification, traverse each intermediate code of the abstract syntax tree based on the server tree builder class and using the visitor pattern;

[0047] S1202: Convert the traversed intermediate code into a first node represented by a syntax node class based on a third-party library.

[0048] Among them, since some operators of HIVEQL cannot be found in ANSI SQL, they first need to be converted to operators in ANSI SQL with equivalent effects. For example, A<=>B, which has no corresponding operator in ANSI SQL, needs to be converted accordingly. In addition, data type conversion is involved, and escape strings are processed. Since the Hive engine's processing of escape characters is inconsistent with the computing engine (such as the Trino engine) that uses the ANSI SQL standard structured query language, escape characters need to be converted. Then, the If expression is converted to a Case when expression. Processing Join connection type conversion, since the Trino engine does not support unconditional implicit Join, it is necessary to convert the implicit Join to an inner join, and add an identical Join condition by default when converting the unconditional implicit Join. Determine whether the type to be converted of the Cast expression exists, and convert the Group by clause to support Group by numbers. Then rename the fields with the same alias to avoid errors in the subsequent verification process. Through the above process, the first node represented by the SqlNode-1 syntax node class of the third-party library Calcite can be obtained.

[0049] For complex query types of HIVEQL, the visitor pattern can be used to convert and modify AST nodes from bottom to top to ensure that the structured query language that complies with the ANSI SQL standard is generated. The entire conversion process is completed in the ParserTreeBuilder (server tree builder) class of the third-party library Calcite, which solves problems such as node conversion, grammar rule adjustment, naming conventions, nested query processing, function and expression conversion. Through the above process, efficient and accurate SQL conversion can be achieved, improving the efficiency of data analysis and processing.

[0050] Furthermore, after the construction of the first node is completed, the first node SqlNode-1 is further converted into an intermediate node RelNode. The first node is converted into an intermediate node based on a relational algebra expression tree, including:

[0051] Modify the custom function of the first node into a specified universal standard function based on the rewriter;

[0052] A setting converter based on a third-party library converts the first node into an intermediate node represented by a relational algebra expression tree, and during the conversion process, the first node is optimized based on a setting optimizer.

[0053] The process of converting SqlNode-1 to RelNode mainly involves transforming and optimizing SqlNode-1, processing user-defined functions, and verifying the legality and validity of SqlNode-1. First, the rewriter class SqlNodePreRewriter is used to process SqlNode-1. This class traverses each type of SqlNode-1 by inheriting the SqlShuttle class of the third-party library Calcite. This class converts some user-defined functions that are not supported by Calcite or the native computing engine into more general functions with equivalent functions. For example, collect_set() is converted to array_distinct(array_agg()), and least and greatest are converted to corresponding functions modified in the Trino computing engine. Secondly, SqlNode-1 is converted to RelNode through Calcite's setting converter ToRelConverterl. During the conversion process, HiveMetastoreClient is used to obtain the metadata of the table, determine whether the table name and field name exist, whether the field name is in the corresponding table, whether the field types match, check whether the grammatical structure of the SQL statement is correct, and check the constraints to ensure the legality and validity of the SQL.

[0054] In addition, the HiveQL field Lateral view explode is further converted to Cross joinunnest. In this process, the first node is optimized. The optimization of the first node based on the set optimizer includes:

[0055] Based on the setting optimizer, the syntax structure and constraint conditions of the first node are checked, and at least one of a redundancy elimination operation, an adjacent element merging operation, a predicate push-down operation, a connection reordering operation, and a subquery removal operation is performed on the first node after the check.

[0056] The optimizer will optimize SqlNode-1 and generate an efficient execution plan to reduce query execution time and resource consumption. The optimization process includes but is not limited to eliminating redundant operations, merging adjacent operations, predicate pushdown, connection reordering, subquery removal, etc. This application does not impose fixed restrictions on the specific optimization process, and will not be elaborated here.

[0057] Optionally, according to different types of AST, the present application may adopt different processing strategies to process the abstract syntax tree. Among them, for complex query types, the AST nodes are converted and modified from bottom to top through the visitor pattern to ensure the generation of query statements that comply with the ANSI SQL standard. For DDL (data definition language) type abstract syntax trees, such as CREATETABLE, ADD COLUMN, SET SESSION, etc. (see the table above for details), since these SQL types are relatively fixed and simple, the present application is modified directly according to the ANSI SQL standard. For example, add single quotes to field names and table names, adjust the format of keywords, etc., to complete the conversion of ANSI SQL, etc., so as to achieve more efficient structured query language conversion.

[0058] S130 : Convert the intermediate node into a second node based on the grammatical rule of ANSI SQL, and output a second structured query language of ANSI SQL standard based on the second node.

[0059] Furthermore, based on the intermediate node RelNode, it is necessary to convert it into a node that complies with the grammatical rules of ANSI SQL to obtain the second structured query language of the ANSI SQL standard. The node obtained by the conversion is defined as the second node, denoted as SqlNode-2. Figure 4 , converting the intermediate node into the second node based on the grammatical rules of ANSI SQL, including:

[0060] S1301, performing statement customization processing on the intermediate node based on the specified engine converter to meet the specified engine input standard;

[0061] S1302, converting the intermediate nodes into designated functions and operators, and removing designated nodes;

[0062] S1303: Call the designated node interface method of the third-party library, and convert the node type and corresponding content of the intermediate node into a string that complies with ANSI SQL syntax rules based on the designated node interface method to obtain a second node.

[0063] The conversion process of the second node is mainly completed by specifying the engine converter class RelToTrinoConverter. Since the generated RelNode does not fully meet the requirements of the Trino computing engine, some changes need to be made. For example, when multiple unions appear at the same time, the names of the same field they select are different. They need to be modified to be the same in SqlNode-2, otherwise errors will occur in subsequent conversions. And due to the difference in computing engines, the default anonymous field name in Calcite needs to be changed from starting with expr$ to _col. In addition, the number of SqlNode-2 obtained in the current stage needs to be adjusted according to the number of search fields in SqlNode-1 obtained in the second stage to ensure the consistency of the number of fields and avoid abnormal operation. If an expression appears in the order by clause and the expression does not appear in the select, the generated SqlNode-2 will add the expression and remove it with an alias, so the expression needs to be removed. If both the query and the subquery contain window expressions, in order to avoid errors, the expression of the query window is not pushed down. When converting RelNode to SqlNode-2, some operators of RelNode need to be specially processed to generate compatible SqlNode-2.

[0064] In addition, map(k1, v1, k2, v2) needs to be converted to map(array(k1, k2), array(v1, v2)) to support it. When processing cast, because Trino does not support direct conversion of varbinary and binary to varchar and char, it is necessary to use the from_utf8 function instead of cast, that is, CAST(binary AS VARCHAR)->from_utf8(binary). Change collect_list and collect_set to Trino's unique array_agg and array_distinct. In substr, if the type of the field passed in is not varchar and char, it needs to be converted to varchar, because Hive supports directly passing in the byte representation array of the string. When processing concat, CONCAT(date,varchar) needs to be converted to CONCAT(varchar,varchar) supported by Trino. Because some SQLs have catalog names, which is the default in trino, it needs to be removed from the nodes of type TableScan in the generated RelNode.

[0065] Finally, the obtained SqlNode-2 is converted to ANSI SQL. There is a specified interface method toSqlString in SqlNode-2, which can generate a string that conforms to a specific syntax according to the type and content of each node. In order to make the generated Sql string conform to the usage of the Trino engine, this application redefines a TrinoSqlDialect by inheriting Calcite's SqlDialect. By rewriting some methods of this class, this class adds double quotes for identifiers; parentheses are added to the Like operator to avoid some errors; for the map constructor, parentheses are used instead of square brackets to conform to the Trino syntax. At this point, the conversion from HiveQL to ANSI SQ is completed.

[0066] Exemplarily, in actual applications, users input HiveQL to the structured query language conversion device of the present application as needed, and the structured query language conversion device defines the user input HiveQL as Input, and the structured query language conversion device outputs ANSI SQL as Output. First, by installing the ANTLR4 dependency and writing a grammar file that conforms to HiveQL, necessary expansion can be made according to demand. Secondly, by executing the ANTLR plug-in to generate relevant code, including ANTLRLexer, ANTLRParser and ANTLRVisitor code, the abstract syntax tree of HiveQL is constructed by inheriting the generated ANTLRVisitor code. The above process can also be assisted by introducing a third-party library, such as hplsql in the Hive official warehouse. Then, by writing ParserTreeBuilder in the third-party library Calcite, the conversion of the abstract syntax tree of HiveQL to SqlNode-1 is completed in visitor mode. Among them, the visitor mode is a software design pattern that separates the algorithm from the object structure, which is also needed in the subsequent intermediate code conversion. SqlNode-1 is an intermediate code representation used by another third-party library Calcite to represent various structures of SQL parse trees. The third-party library Calcite uniformly uses independent SqlNode-1 to represent various SQL parse trees. RelNode is the base class for representing relational expression tree nodes in Calcite, which represents the nodes in relational algebra expression trees converted from SQL statements. Relational algebra trees are a more advanced intermediate representation of SQL statements and are more suitable for optimization and execution. SqlNode-1 is converted to RelNode using ToRelConverter, and then RelNode is converted back to SqlNode-2 using RelToTrinoConverter. After adjusting the code, the final SqlNode-2 can comply with the ANSI SQL syntax rules, and finally the ANSI SQL can be obtained by calling the toSqlString method of SqlNode-2.

[0067] Optionally, after outputting the second structured query language of the ANSI SQL standard based on the second node, the method further includes:

[0068] Extracting the corresponding second structured query language, and performing unit testing and iterative verification on the extracted second structured query language;

[0069] A comparison is performed based on the execution result of the first structured query language and the corresponding execution result of the second structured query language, and the second structured query language is verified based on the comparison result.

[0070] Based on the above structured query language conversion method, a large number of test units are used, and test-driven development and iterative development methods are adopted to speed up the development of the conversion method and improve efficiency. Among them, by adding an audit log function to the computing engine, the running status information of the converted ANSI SQL is recorded, and this information is stored in the Hive table with date as the partition. Then, by extracting the ANSI SQL running online by the user, and using the Explain method to preliminarily determine the cause of the engine's syntax error or the cause of the ANSI SQL conversion failure. Unit test cases are sorted out based on the above problems.

[0071] After that, you only need to ensure that all previous unit tests have passed; secondly, connect the converted ANSI SQL through JDBC in Explain mode to ensure that there are no syntax problems; finally, compare the SQL running results before and after the conversion to ensure their correctness and consistency. When merging the code, in order to ensure the correctness and consistency of the SQL execution results before and after the conversion, the script program automatically compares the execution results of the two. Since the data order of some SQL return results is uncertain, the return results need to be sorted and then the data is compared row by row. For integer and basic types, the values ​​are directly compared; for floating-point types, the comparison is whether they are within a certain error range; for complex types (such as array), only the data is judged whether it is correct, not the order; for row and map types, they need to be completely consistent. Through the above script program, the effect of SQL before and after the conversion can be detected and verified, and strict testing and verification of the converted ANSI SQL can be implemented, which maximizes the stability and reliability of the system.

[0072] Optionally, after outputting the second structured query language of the ANSI SQL standard based on the second node, the method further includes:

[0073] During structured query language routing, determining a language standard type of a current structured query language;

[0074] When the language standard type of the current structured query language belongs to the second structured query language, the current structured query language is scheduled to be processed by a designated engine cluster, and the processing result of the designated engine cluster is monitored in real time to adjust the conversion process of the second structured query language based on the processing result.

[0075] Reference Figure 5 In order to ensure that the converted ANSI SQL does not affect the normal use of users, this application adopts an intelligent routing method to control the scheduling of structured query language to the original computing engine cluster (old cluster) or the specified engine cluster (new cluster, that is, the computing engine cluster for ANSI SQL).

[0076] By introducing an ANSI SQL parser to parse the user's current structured query language SQL, it is determined in advance whether the user task is ANSI SQL according to the SQL language standard type to determine whether the task needs to be routed to a specified engine cluster. In addition, the routing system also supports a dynamic blacklist and whitelist mechanism to allow specific users to pass or not pass a specified engine cluster.

[0077] Then, by monitoring the execution of the engine, errors in SQL conversion can be discovered and recorded in a timely manner for subsequent troubleshooting and resolution, thus ensuring the stability and reliability of the conversion to generate ANSI SQL.

[0078] Optionally, SQL intelligent routing can also support the following functions:

[0079] 1. Advanced configuration management: supports different levels of configuration, including global configuration, user-level configuration, and source-level configuration. Since the routing system is stateless and decentralized, configuration changes and upgrades are simple.

[0080] 2. Intelligent scheduling mechanism: supports load balancing and priority scheduling mechanisms. According to cluster load conditions, SQL types and business priorities, it intelligently allocates SQL requests and schedules different clusters to ensure efficient use of system resources and priority processing of key businesses.

[0081] 3. Monitoring and alarm mechanism: Set alarm rules to automatically send email or phone alarms when errors or performance bottlenecks occur.

[0082] In practical applications, the HiveQL to ANSI SQL conversion method of this application is applied to various OLAP computing engines and loaded and used in the form of plug-ins. The use of HiveQL and ANSI SQL can be supported without modifying the underlying computing engine. This not only meets the stable operation of historical tasks, but also reduces the cost of maintaining new computing engines.

[0083] In the above, by inputting the first structured query language of HiveQL standard, the first structured query language is subjected to lexical analysis and syntax analysis to generate the abstract syntax tree of the first structured query language; the abstract syntax tree is traversed, and each intermediate code of the abstract syntax tree is converted based on the syntax node class of the third-party library to obtain the corresponding first node, and the first node is converted into an intermediate node based on the relational algebra expression tree; the intermediate node is converted into a second node based on the syntax rule of ANSI SQL, and the second structured query language of ANSI SQL standard is output based on the second node. By adopting the above technical means, by constructing the abstract syntax tree of HiveQL, the nodes of the abstract syntax tree are converted into intermediate nodes using intermediate codes, and the intermediate nodes are converted into nodes of ANSI SQL, so as to realize efficient, convenient and automatic structured query language conversion, without the need for analysts to perform manual editing of ANSI SQL, the use threshold of the computing engine of ANSI SQL standard is lowered, and the efficiency of data query and analysis is improved.

[0084] Moreover, during the entire process, analysts only need to input the HiveQL standard structured query language to achieve the ANSISQL conversion, achieving a seamless ANSI SQL construction effect and improving the analyst's user experience.

[0085] Based on the above embodiments, Figure 6 A schematic diagram of the structure of a structured query language conversion system provided in this application. Figure 6 The structured query language conversion system provided in this embodiment specifically includes: an analysis module 21, a conversion module 22 and an output module 23.

[0086] The analysis module 21 is configured to input a first structured query language in HiveQL standard, perform lexical analysis and syntax analysis on the first structured query language, and generate an abstract syntax tree of the first structured query language;

[0087] The conversion module 22 is configured to traverse the abstract syntax tree, convert each intermediate code of the abstract syntax tree based on the syntax node class of the third-party library, obtain the corresponding first node, and convert the first node into an intermediate node based on the relational algebra expression tree;

[0088] The output module 23 is configured to convert the intermediate node into a second node based on the grammatical rules of ANSI SQL, and output a second structured query language of the ANSI SQL standard based on the second node.

[0089] Specifically, traversing the abstract syntax tree, converting each intermediate code of the abstract syntax tree based on the syntax node class of the third-party library, and obtaining the corresponding first node includes:

[0090] Perform semantic verification on the abstract syntax tree, and after completing the semantic verification, traverse each intermediate code of the abstract syntax tree based on the server tree builder class and using the visitor pattern;

[0091] The traversed intermediate code is converted into a first node represented by a syntax node class based on a third-party library.

[0092] The converting the first node into an intermediate node based on a relational algebra expression tree includes:

[0093] Modifying the user-defined function of the first node into a specified universal standard function based on the rewriter;

[0094] A setting converter based on a third-party library converts the first node into an intermediate node represented by a relational algebra expression tree, and during the conversion process, the first node is optimized based on a setting optimizer.

[0095] The optimizing the first node based on the setting optimizer includes:

[0096] Based on the setting optimizer, the syntax structure and constraint conditions of the first node are checked, and at least one of a redundancy elimination operation, an adjacent element merging operation, a predicate push-down operation, a connection reordering operation and a subquery removal operation is performed on the first node that has completed the check.

[0097] Specifically, converting the intermediate node into the second node based on the grammar rule of ANSI SQL includes:

[0098] Based on the specified engine converter, the intermediate node is customized to meet the specified engine input standard;

[0099] Performing conversion of the specified function and operator on the intermediate node, and removing the specified node;

[0100] The designated node interface method of the third-party library is called, and based on the designated node interface method, the node type and corresponding content of the intermediate node are converted into a character string that complies with ANSI SQL grammar rules to obtain a second node.

[0101] Specifically, after the second node outputs the second structured query language according to the ANSI SQL standard, the method further includes:

[0102] Extracting the corresponding second structured query language, and performing unit testing and iterative verification on the extracted second structured query language;

[0103] A comparison is performed based on the execution result of the first structured query language and the corresponding execution result of the second structured query language, and the second structured query language is verified based on the comparison result.

[0104] Specifically, after the second node outputs the second structured query language according to the ANSI SQL standard, the method further includes:

[0105] During structured query language routing, determining a language standard type of a current structured query language;

[0106] When the language standard type of the current structured query language belongs to the second structured query language, the current structured query language is scheduled to be processed by a designated engine cluster, and the processing result of the designated engine cluster is monitored in real time to adjust the conversion process of the second structured query language based on the processing result.

[0107] In the above, by inputting the first structured query language of HiveQL standard, the first structured query language is subjected to lexical analysis and syntax analysis to generate the abstract syntax tree of the first structured query language; the abstract syntax tree is traversed, and each intermediate code of the abstract syntax tree is converted based on the syntax node class of the third-party library to obtain the corresponding first node, and the first node is converted into an intermediate node based on the relational algebra expression tree; the intermediate node is converted into a second node based on the syntax rule of ANSI SQL, and the second structured query language of ANSI SQL standard is output based on the second node. By adopting the above technical means, by constructing the abstract syntax tree of HiveQL, the nodes of the abstract syntax tree are converted into intermediate nodes using intermediate codes, and the intermediate nodes are converted into nodes of ANSI SQL, so as to realize efficient, convenient and automatic structured query language conversion, without the need for analysts to perform manual editing of ANSI SQL, the use threshold of the computing engine of ANSI SQL standard is lowered, and the efficiency of data query and analysis is improved.

[0108] Moreover, during the entire process, analysts only need to input the HiveQL standard structured query language to achieve the ANSISQL conversion, achieving a seamless ANSI SQL construction effect and improving the analyst's user experience.

[0109] The structured query language conversion system provided in the embodiment of the present application can be configured to execute the structured query language conversion method provided in the above embodiment, and has corresponding functions and beneficial effects.

[0110] Based on the above practical example, the present application embodiment also provides a structured query language conversion device, referring to Figure 7, the structured query language conversion device includes: a processor 31, a memory 32, a communication module 33, an input device 34 and an output device 35. The memory, as a computer-readable storage medium, can be configured to store software programs, computer executable programs and modules, such as program instructions / modules corresponding to the structured query language conversion method described in any embodiment of the present application (for example, the analysis module, conversion module and output module in the structured query language conversion system). The communication module is configured to perform data transmission. The processor executes various functional applications and data processing of the device by running the software programs, instructions and modules stored in the memory, that is, realizing the above-mentioned structured query language conversion method. The input device can be configured to receive input digital or character information, and generate key signal input related to the user settings and function control of the device. The output device may include a display device such as a display screen. The structured query language conversion device provided above can be configured to execute the structured query language conversion method provided in the above-mentioned embodiment, and has corresponding functions and beneficial effects.

[0111] On the basis of the above embodiments, the embodiments of the present application further provide a computer-readable storage medium, wherein the computer-readable storage medium stores computer-executable instructions, wherein the computer-executable instructions are configured to execute a structured query language conversion method when executed by a computer processor, and the storage medium may be any of various types of memory devices or storage devices. Of course, the computer-executable instructions of the computer-readable storage medium provided in the embodiments of the present application are not limited to the structured query language conversion method described above, and may also execute related operations in the structured query language conversion method provided in any embodiment of the present application.

[0112] On the basis of the above embodiments, the embodiments of the present application further provide a computer program product. The technical solution of the present application is essentially or the part that contributes to the prior art or all or part of the technical solution can be embodied in the form of a software product. The computer program product is stored in a storage medium and includes a number of instructions for enabling a computer device, a mobile terminal or a processor therein to execute all or part of the steps of the structured query language conversion method described in each embodiment of the present application.

Claims

1. A structured query language conversion method, characterized in that: include: Inputting a first structured query language in HiveQL standard, performing lexical analysis and grammatical analysis on the first structured query language, and generating an abstract syntax tree of the first structured query language; Traversing the abstract syntax tree, converting each intermediate code of the abstract syntax tree based on the syntax node class of the third-party library to obtain a corresponding first node, and converting the first node into an intermediate node based on a relational algebra expression tree; The intermediate node is converted into a second node based on the grammatical rule of ANSI SQL, and a second structured query language of ANSI SQL standard is output based on the second node.

2. The structured query language conversion method according to claim 1, characterized in that: The traversing the abstract syntax tree and converting each intermediate code of the abstract syntax tree based on the syntax node class of the third-party library to obtain the corresponding first node includes: Perform semantic verification on the abstract syntax tree, and after completing the semantic verification, traverse each intermediate code of the abstract syntax tree based on the server tree builder class and using the visitor pattern; The traversed intermediate code is converted into a first node represented by a syntax node class based on a third-party library.

3. The structured query language conversion method according to claim 2, characterized in that: The converting the first node into an intermediate node based on a relational algebra expression tree comprises: Modifying the user-defined function of the first node into a specified universal standard function based on the rewriter; A setting converter based on a third-party library converts the first node into an intermediate node represented by a relational algebra expression tree, and during the conversion process, the first node is optimized based on a setting optimizer.

4. The structured query language conversion method according to claim 3, characterized in that: The optimizing the first node based on the setting optimizer includes: Based on the setting optimizer, the syntax structure and constraint conditions of the first node are checked, and at least one of a redundancy elimination operation, an adjacent element merging operation, a predicate push-down operation, a connection reordering operation and a subquery removal operation is performed on the first node that has completed the check.

5. The structured query language conversion method according to claim 1, characterized in that: The converting the intermediate node into the second node based on the grammar rule of ANSI SQL includes: Based on the specified engine converter, the intermediate node is customized to meet the specified engine input standard; Performing conversion of the specified function and operator on the intermediate node, and removing the specified node; The designated node interface method of the third-party library is called, and based on the designated node interface method, the node type and corresponding content of the intermediate node are converted into a character string that complies with ANSI SQL grammar rules to obtain a second node.

6. The structured query language conversion method according to any one of claims 1 to 5, characterized in that: After the second node outputs the second structured query language of the ANSI SQL standard, the method further includes: Extracting the corresponding second structured query language, and performing unit testing and iterative verification on the extracted second structured query language; A comparison is performed based on the execution result of the first structured query language and the corresponding execution result of the second structured query language, and the second structured query language is verified based on the comparison result.

7. The structured query language conversion method according to any one of claims 1 to 5, characterized in that: After the second node outputs the second structured query language of the ANSI SQL standard, the method further includes: During structured query language routing, determining a language standard type of a current structured query language; When the language standard type of the current structured query language belongs to the second structured query language, the current structured query language is scheduled to be processed by a designated engine cluster, and the processing result of the designated engine cluster is monitored in real time to adjust the conversion process of the second structured query language based on the processing result.

8. A structured query language conversion system, characterized in that: include: An analysis module, configured to input a first structured query language in HiveQL standard, perform lexical analysis and syntax analysis on the first structured query language, and generate an abstract syntax tree of the first structured query language; A conversion module is configured to traverse the abstract syntax tree, convert each intermediate code of the abstract syntax tree based on the syntax node class of the third-party library, obtain the corresponding first node, and convert the first node into an intermediate node based on the relational algebra expression tree; The output module is configured to convert the intermediate node into a second node based on the grammatical rule of ANSI SQL, and output a second structured query language of ANSI SQL standard based on the second node.

9. A structured query language conversion device, characterized in that: include: memory and one or more processors; The memory is configured to store one or more programs; When the one or more programs are executed by the one or more processors, the one or more processors implement the structured query language conversion method as described in any one of claims 1-7.

10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores computer-executable instructions, and when the computer-executable instructions are executed by a computer processor, the computer-executable instructions are configured to execute the structured query language conversion method according to any one of claims 1 to 7.

11. A computer program product, characterized in that The computer program product includes instructions, and when the instructions are executed on a computer or a processor, the computer or the processor executes the structured query language conversion method according to any one of claims 1 to 7.

Citation Information

Cited By

  • Structured query language task execution method and device and electronic equipment

    CN119357243A

  • Database test case extension method and device based on large language model and readable medium

    CN119807057A

  • SQL (Structured Query Language) reconstruction method and device, equipment and storage medium

    CN120508568A

  • A SQL refactoring method, device, equipment and storage medium

    CN120508568B

  • Data processing method and device and storage medium

    CN122614842A