Request processing method and related device

Abstract

The embodiment of the invention discloses a request processing method which is applied to a first database system and comprises the steps that the first database system obtains a first structured query language (SQL) statement, and grammar rules of the first SQL statement are the same as grammar rules of a second database system; the first database system converts the first SQL statement into a second SQL statement according to a configured mapping relation, and the second SQL statement conforms to a grammar rule of a second database system; the mapping relationship comprises a corresponding relationship between a first SQL statement expression conforming to a grammar rule of the first database system and a second SQL statement expression conforming to a grammar rule of the second database system; the first database system executes the second SQL statement, and the result that the first database system executes the second SQL statement is the same as the result that the second database system executes the first SQL statement. According to the scheme, the compatibility of the database can be improved.

Description

[0001] This application claims priority to the Chinese patent application filed with the State Intellectual Property Office on October 19, 2023, with application number 202311361894.2 and invention name “A statement processing method, device and computing device cluster”, all contents of which are incorporated by reference in this application. Technical Field

[0002] The present application relates to the technical field of database management, and in particular to a request processing method and related devices. Background Art

[0003] Currently, mainstream database systems are compatible with the standard syntax of structured query language (SQL). However, different database management systems (DBMS) have some specific syntax and functions due to their own characteristics or their customized extensions. The SQL for the same operation request is expressed differently in different DBMSs.

[0004] As business needs change or architecture adjustments occur, data needs to be moved from the first database system to the second database system to meet new business needs. At this time, the syntax and functions of SQL with different expressions between the two database systems need to be adapted and compatible. For example, the SQL statements in the application code that access the first database system are converted into SQL statements that access the second database system, or the syntax parsing file of the second database system is modified.

[0005] However, both the modification of application-side code and the modification of database system syntax parsing files are very time-consuming. Therefore, how to achieve rapid adaptation of SQL between different database systems is an urgent problem that needs to be solved. Summary of the invention

[0006] The embodiment of the present application provides a request processing method, which can improve the compatibility of a database. The embodiment of the present application also provides a corresponding device and system.

[0007] A first aspect of an embodiment of the present application provides a request processing method, which is applied to a first database system, and includes:

[0008] The first database system obtains a first structured query language SQL statement from a client, wherein the grammatical rules of the first SQL statement are the same as the grammatical rules of the SQL statement of the second database system; the first database system converts the first SQL statement into a second SQL statement according to a pre-configured mapping relationship, wherein the second SQL statement complies with the grammatical rules of the SQL statement of the second database system, and the mapping relationship includes a correspondence between a first SQL statement expression that complies with the grammatical rules of the first database system and a second SQL statement expression that complies with the grammatical rules of the second database system; the first database system executes the second SQL statement, wherein a result of the first database system executing the second SQL statement is the same as a result of the second database system executing the first SQL statement.

[0009] In the present application, the method is applied to the DBMS of the first database, which can be abstractly understood as adding a SQL conversion module before the SQL parser of the first database performs parsing, and establishing an abstract layer between the application and the database, which provides a mapping relationship set of different database SQL grammar rules to process the received SQL request. Exemplarily, the first database system is a GaussDB database system, and the second database system is an Oracle database system. The grammar rules of the first SQL statement originally conform to the grammar rules of the SQL statement of the Oracle database system, and are used to access the GaussDB database system. Using the above method, after the first database system obtains the first SQL statement, before performing SQL parsing on the first SQL statement, it converts it into a second SQL statement that conforms to the grammar rules of this database system, so that the result of querying the database is the same as the result of accessing the second database system using the first SQL statement. This method can improve the compatibility of the database without changing the application code and the grammar parsing file of the first database system, and facilitate the modification or expansion of the request to access the database.

[0010] In a possible implementation, the first database system converts the first SQL statement into the second SQL statement according to the configured mapping relationship, including: the first database system determines a first SQL statement expression that matches the first SQL statement; based on the first SQL statement expression and the mapping relationship, determines a second SQL statement expression corresponding to the first SQL statement expression; and generates a second SQL statement according to the second SQL statement expression and the first SQL statement.

[0011] By adopting the above method, the first SQL statement expression is mapped according to predefined rules and algorithms to obtain the second SQL statement expression, which can avoid query failures caused by syntax errors or semantic errors.

[0012] In a possible implementation, the mapping relationship further includes a mapping type, and the mapping type is used to indicate a processing method for generating the second SQL statement based on the second SQL statement expression.

[0013] In the present application, a SQL statement expression is associated with a SQL statement by using a mapping relationship. This mapping relationship includes not only simple mappings, but may also include more complex generation processing methods. Using the above method, a processing method for generating a second SQL statement based on a second SQL statement expression can be obtained through a mapping type. This enables the system to adapt to different scenarios and requirements. In addition, since the mapping relationship can be flexibly defined, new mapping types and expressions can be easily added. This makes the system have good scalability and can be expanded and updated over time.

[0014] In a possible implementation, when the mapping type indicates complete mapping, the first SQL statement expression is the same as the first SQL statement, and the second SQL statement is the second SQL statement expression.

[0015] By adopting the above method, if the relationship between the first SQL statement and the first SQL statement expression is exactly the same, the second SQL statement expression can be directly used as the second SQL statement, thereby improving the efficiency and accuracy of request processing.

[0016] In one possible implementation, when the mapping type indicates variable mapping, the first SQL statement expression includes a variable extractor, which is used to extract at least one variable from the first SQL statement; the second SQL statement expression includes a variable matcher, and the second SQL statement is obtained by replacing the variable matcher in the second SQL statement expression with the at least one variable.

[0017] For example, the first SQL statement is a SQL statement for querying time, amount or order number. The specific values ​​of these parameters vary, but the data format does not change. In this case, such dynamic variable parameters can be extracted separately using regular matching operators and replaced in the second SQL statement. By adopting the above method, various types of variable mappings can be processed uniformly without writing separate mapping rules for each specific request mapping, thereby improving the efficiency of request processing. Using variable extractors and variable matching operators in expressions makes the mapping relationship easier to read and understand.

[0018] In one possible implementation, when the mapping type indicates template mapping, the first SQL statement expression includes a first template variable symbol; the first template variable symbol is used to extract at least one variable from the first request; or, the first template variable symbol is used to extract at least one variable from the first SQL statement, and is used to indicate the processing logic of the at least one variable; the second SQL statement expression includes a second template variable symbol, and the second SQL statement is obtained by replacing the second template variable symbol in the second SQL statement expression with a variable processing result, the variable processing result is obtained by processing at least one variable or an intermediate result according to the processing logic indicated by the second template variable symbol, and the intermediate result is a result obtained by processing at least one variable according to the processing logic indicated by the first template variable symbol.

[0019] In the present application, the first database system corresponds to the SQL statement for accessing the second database system, and although they have the same request processing capabilities, they may not have similar function expressions. At this time, corresponding to the first SQL statement, the second SQL statement may be significantly different in structure. Using the above method, by using template variable symbols, the method allows for flexible extraction and manipulation of variables when processing different requests, which enables the method to adapt to different data processing requirements. This method can also perform processing logic on the extracted variables, such as converting arrays to strings and other operations. Due to the use of templates, the same processing logic can be applied to different data sets. The flexibility, reusability and scalability of request processing are improved.

[0020] A second aspect of an embodiment of the present application provides a database system, which is a first database system, including:

[0021] An acquisition device is used to acquire a first structured query language SQL statement from a client, wherein a grammatical rule of the first SQL statement is the same as a grammatical rule of an SQL statement of a second database system;

[0022] A conversion device, used for converting the first SQL statement into a second SQL statement according to a configured mapping relationship, wherein the second SQL statement conforms to the grammatical rules of the SQL statements of the second database system, and the mapping relationship includes a correspondence between a first SQL statement expression conforming to the grammatical rules of the first database system and a second SQL statement expression conforming to the grammatical rules of the second database system;

[0023] The execution device is used for the second SQL statement, wherein the result of the first database system executing the second SQL statement is the same as the result of the second database system executing the first SQL statement.

[0024] In one possible implementation, the conversion device is specifically used to: determine a first SQL statement expression that matches the first SQL statement; based on the first SQL statement expression and a mapping relationship, determine a second SQL statement expression corresponding to the first SQL statement expression; and generate a second SQL statement based on the second SQL statement expression and the first SQL statement.

[0025] In a possible implementation, the mapping relationship further includes a mapping type, and the mapping type is used to indicate a processing method for generating the second SQL statement based on the second SQL statement expression.

[0026] In a possible implementation, when the mapping type indicates complete mapping, the first SQL statement expression is the same as the first SQL statement, and the second SQL statement is the second SQL statement expression.

[0027] In one possible implementation, when the mapping type indicates variable mapping, the first SQL statement expression includes a variable extractor, which is used to extract at least one variable from the first SQL statement; the second SQL statement expression includes a variable matcher, and the second SQL statement is obtained by replacing the variable matcher in the second SQL statement expression with at least one variable.

[0028] In one possible implementation, when the mapping type indicates template mapping, the first SQL statement expression includes a first template variable symbol; the first template variable symbol is used to extract at least one variable from the first request; or, the first template variable symbol is used to extract at least one variable from the first SQL statement, and is used to indicate the processing logic of the at least one variable; the second SQL statement expression includes a second template variable symbol, and the second SQL statement is obtained by replacing the second template variable symbol in the second SQL statement expression with a variable processing result, the variable processing result is obtained by processing at least one variable or an intermediate result according to the processing logic indicated by the second template variable symbol, and the intermediate result is a result obtained by processing at least one variable according to the processing logic indicated by the first template variable symbol.

[0029] In a third aspect, an embodiment of the present application provides a request processing device, the request processing device comprising: a processor, a memory, and a transceiver. The memory stores a computer program or a computer instruction, the processor is used to call and run the computer program or the computer instruction stored in the memory, so that the processor implements the processing operation in the first aspect and any one of the implementations of the first aspect, and the transceiver is used to send and receive signals, such as: implementing the operations of receiving and sending in the first aspect and any one of the implementations of the first aspect.

[0030] In a fourth aspect, an embodiment of the present application provides a computing device cluster, which includes at least one computing device, each computing device includes a processor and a memory, the memory stores a computer program or computer instructions, and the processor is used to call and run the computer program or computer instructions stored in the memory so that the computing device cluster executes the above-mentioned first aspect and any optional method thereof.

[0031] In a fifth aspect, an embodiment of the present application provides a computer-readable storage medium, in which a computer program is stored. When the computer-readable storage medium is run on a computer, the computer executes the above-mentioned first aspect and any optional method thereof.

[0032] In a sixth aspect, an embodiment of the present application provides a computer program, which, when executed on a computer, enables the computer to execute the above-mentioned first aspect and any optional method thereof.

[0033] In a seventh aspect, the present application provides a chip system, which includes a processor for supporting an execution device or a training device to implement the functions involved in the above aspects, such as sending or processing the data involved in the above methods; or, information. In a possible design, the chip system also includes a memory, which is used to store program instructions and data necessary for the execution device or the training device. The chip system can be composed of chips, or it can include chips and other discrete devices.

[0034] As mentioned above, the technical effects of the second, third, fourth, fifth and sixth aspects of the present application can be understood in conjunction with the technical effects of the first aspect and any implementation method of the first aspect. BRIEF DESCRIPTION OF THE DRAWINGS

[0035] In order to more clearly illustrate the technical solutions in the embodiments of the present application, the drawings required for use in the description of the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present application. For those skilled in the art, other drawings can be obtained based on these drawings without creative work.

[0036] Figure 1A It is a schematic diagram of a stand-alone database system;

[0037] Figure 1B It is a schematic diagram of a cluster database system using a shared disk architecture;

[0038] Figure 1C It is a schematic diagram of a cluster database system using a shared-nothing architecture;

[0039] Figure 1D is a schematic diagram of a structure of a node in a database system provided in an embodiment of the present application;

[0040] Figure 1E is another structural diagram of a node in a database system provided in an embodiment of the present application;

[0041] Figure 2 It is a flowchart of a request processing method provided by an embodiment of the present application;

[0042] Figure 3 is another flowchart of the request processing method provided by the real-time example of the present application;

[0043] Figure 4 is another flowchart of the request processing method provided by the real-time example of the present application;

[0044] Figure 5 is another flowchart of the request processing method provided by the real-time example of the present application;

[0045] Figure 6 is a structural diagram of a request processing device provided in an embodiment of the present application;

[0046] Figure 7 is a schematic diagram of a structure of a computing device provided in an embodiment of the present application;

[0047] Figure 8 is a schematic diagram of a structure of a computing device cluster provided in an embodiment of the present application;

[0048] Fig. 9 It is another structural diagram of the computing device cluster provided in an embodiment of the present application. DETAILED DESCRIPTION

[0049] The following describes the embodiments of the present application in conjunction with the accompanying drawings. Obviously, the described embodiments are only embodiments of a part of the present application, rather than all embodiments. It is known to those skilled in the art that with the development of technology and the emergence of new scenarios, the technical solutions provided in the embodiments of the present application are also applicable to similar technical problems.

[0050] The terms "first", "second", etc. in the specification and claims of the present application and the above-mentioned drawings are used to distinguish similar objects, and are not necessarily used to describe a specific order or sequence. It should be understood that the data used in this way can be interchangeable where appropriate, so that the embodiments described herein can be implemented in an order other than that illustrated or described herein. In addition, the terms "including" and "having" and any of their variations are intended to cover non-exclusive inclusions, for example, a process, method, system, product or device that includes a series of steps or units is not necessarily limited to those steps or units that are clearly listed, but may include other steps or units that are not clearly listed or inherent to these processes, methods, products or devices.

[0051] At present, basically all mainstream databases are compatible with SQL2003 and above SQL syntax semantics, but each database vendor has some independent syntax and function expressions for some custom features.

[0052] For example, the difference between SQL statements in Oracle database and GaussDB database. For example, for queries without from, Oracle does not support queries without from, so it needs to bring the dual virtual table: "select func(xxx)from dual", while GaussDB supports queries without from, so it does not need to bring the from virtual table: "select func(xxx)".

[0053] For another example, add an alias for a reserved word in a column. Oracle can use the column alias without as: "select decode('a',0,0,1)c1", while GaussDB needs to add as as the column alias: "select decode('a',0,0,1)as c1".

[0054] The differences in SQL implementations between different database vendors may cause problems for applications, leading to difficulties in database matching, replacement, and evolution. Such differences may prevent applications from interacting smoothly with the new database system, thus affecting data processing and query efficiency.

[0055] Based on this, the embodiment of the present application provides a request processing method, which can solve the compatibility problem of the database management system (DBMS) and improve the efficiency of database migration and integration. The embodiment of the present application also provides a corresponding device and system. The following are detailed descriptions.

[0056] The method provided in the embodiment of the present application can be implemented as a part of a database management system, or as an independent component. The method provided in the embodiment of the present application can be applied to a stand-alone database system or a cluster database system. A database system (database system) generally consists of the following three parts: (1) Database (DB), which refers to a collection of organized, shareable data stored in a computer for a long time. The data in the database is organized, described and stored according to a certain mathematical model, has low redundancy, high data independence and easy scalability, and can be shared by various users. (2) Hardware, including data storage devices required to store data, such as memory and / or disk. (3) Software, including DBMS. DBMS is the core software of the database system. It is a system software used to scientifically organize and store data, and efficiently acquire and maintain data. The database engine is the core component of DBMS.

[0057] Specifically, Figure 1A The diagram of a stand-alone database system includes a database management system and a data storage device. The database management system is used to provide services such as querying and modifying the database, and the database management system stores data in the data storage device. In a stand-alone database system, the database management system and the data storage device are usually located on a single server, such as a symmetric multi-processor (SMP) server. The SMP server includes multiple processors, and all processors share resources such as a bus, memory, and I / O system. The functions of the database management system can be implemented by one or more processors executing programs in memory.

[0058] Figure 1B A schematic diagram of a cluster database system using a shared-storage architecture, including multiple nodes (such as Figure 1BIn the embodiment of the present invention, there are nodes 1-N in the cluster database system, each of which is deployed with a database management system to provide users with services such as query and modification of the database. Multiple database management systems store shared data in a shared data storage device, and perform read and write operations on the data in the data storage device through a switch. The shared data storage device can be a shared disk array. The nodes in the cluster database system can be physical machines, such as database servers, or virtual machines (VMs) or containers running on abstract hardware resources. When the node is a virtual machine or a container, the database management system deployed in the node is a DBMS instance, which can be a process or a thread. If the node is a physical machine, the switch is a storage area network (SAN) switch, an Ethernet switch, a fiber switch or other physical switching devices. If the node is a virtual machine or a container, the switch is a virtual switch.

[0059] Figure 1C This is a diagram of a cluster database system that uses a shared-nothing architecture. Each node has its own exclusive hardware resources (such as data storage), operating system, and database, and the nodes communicate through the network. In this system, data will be distributed to each node according to the database model and application characteristics. The query task will be divided into several parts and executed in parallel on all nodes. The nodes will coordinate calculations with each other to provide database services as a whole. All communication functions are implemented on a high-bandwidth network interconnection system. Figure 1B Like the cluster database system with shared-storage architecture described above, the nodes here can be either physical machines or virtual machines.

[0060] In all embodiments of the present application, the data storage of the database system includes but is not limited to solid state drives (SSDs), disk arrays, or other types of non-transitory computer-readable media. Figures 1A-1C Although the database is not shown in the figure, it should be understood that the database is stored in the data storage. A person skilled in the art can understand that a database system may include Figures 1A-1C Fewer or more components than those shown in the figure, or including Figures 1A-1C The components shown in the figure are different components. Figures 1A-1C Only components more relevant to the implementation disclosed in the embodiments of the present application are shown. Figure 1B and 1CFour nodes have been described in the above description, but those skilled in the art can understand that a cluster database system can include any number of nodes. The database management system functions of each node can be implemented by a suitable combination of software, hardware and / or firmware running on each node.

[0061] Above Figures 1A to 1C The database system includes nodes. The structure of the nodes is introduced below with reference to the attached figure. Figure 1D As shown, an embodiment of the present application provides a node 100, including: at least one processor 104, a non-transitory computer-readable medium (non-transitory computer-readable medium) 106 storing executable code, and a database management system 108. The executable code is configured to implement the components and functions of the database management system 108 when executed by at least one processor 104. The non-transitory computer-readable medium 106 may include one or more non-volatile memories. As an example, the non-volatile memory includes a semiconductor memory device, such as an erasable programmable read only memory (EPROM), an electrically erasable programmable read only memory (EEPROM) and a flash memory; a disk, such as an internal hard disk or a removable disk, a magneto optical disk, and a CD ROM and a DVD-ROM. In addition, the non-transitory computer-readable medium 106 may also include any device configured as a main memory. At least one processor 104 may include any type of general computing circuit or dedicated logic circuit, such as a field-programmable gate array (FPGA) or an application specific integrated circuit (ASIC). At least one processor 104 may also be one or more processors coupled to one or more semiconductor substrates, such as a CPU.

[0062] The database management system 108 may be a relational database management system (RDBMS). The database management system 108 supports structured query language (SQL). Generally, SQL refers to a special programming language specifically used to manage data stored in a relational database. SQL may refer to various types of data-related languages, including, for example, data definition languages ​​and data manipulation languages, where the scope of SQL may include data insertion, query, update and deletion, schema creation and modification, and data access control.

[0063] A query statement is a request to view, access and / or manipulate data stored in a database. The database management system 108 may receive a query in SQL format (referred to as an SQL query) from the database client 102. The SQL query may also be referred to as an SQL statement. The SQL here may be an original SQL statement issued based on the database's SimpleQuery interface (also referred to as a Q message interface), or a precompiled SQL issued by a PrepareStatement interface (also referred to as a precompiled P message interface in a PBE message).

[0064] Generally, the database management system 108 generates query results corresponding to the query by accessing relevant data from the database and manipulating the relevant data, and returns the query results to the database client 102. A database is a collection of data organized, described, and stored according to a certain mathematical model. A database may include one or more database structures or formats, such as row storage and column storage. A database is usually stored in a data storage device, such as Figure 1D The external data storage 140 in the non-transitory computer readable medium 106. When the database is stored in the non-transitory computer readable medium 106, the database management system 108 is an in-memory database management system.

[0065] Database client 102 may include any type of device or application configured to interact with database management system 108. In some examples, database client 102 includes one or more application servers.

[0066] The database management system 108 may include an SQL processing engine 109 and a storage engine 140. The SQL processing engine 109 includes a request conversion device 110, a query engine 120 and an execution engine 130. The request conversion device 110 includes a conversion engine 111. In one embodiment, the conversion engine 111 may be an SQL conversion engine. The query engine 120 generates a corresponding execution plan based on the query statement processed by the conversion engine 111, and the execution engine 130 operates according to the execution plan of the statement to generate a query result. The storage engine 140 is responsible for managing the actual content of the data and index of the table on the file system, and also manages the cache, buffer, transaction, log and other data during runtime. For example, the storage engine 140 can write the processing result of the SQL processing engine 109 (i.e., the execution result of the execution engine 130) to the data storage 150 through physical I / O.

[0067] The conversion engine 111 provided in the embodiment of the present application includes a configuration loader 112, a matcher 113 and a transformer 114, wherein the configuration loader 112 is used to load a pre-configured mapping relationship set, providing the matcher 113 with a basis for comparing and matching SQL statement strings. The matcher 113 matches the received SQL statement with the configured mapping relationship set, and the matching includes a complete match and a regular match. The transformer 114 receives the output result of the matcher 113, and then performs character conversion on the SQL statement that meets the matching condition based on the mapping relationship set, and outputs the converted SQL statement to the query engine 120, and the conversion includes a complete conversion, a variable conversion and a template conversion.

[0068] The query engine 120 includes a parser 121 and an optimizer 122, wherein the parser 121 is used to perform grammatical and semantic analysis on the query statement, expand the view in the query, and divide it into small query blocks. The optimizer 114 generates a set of possible execution plans for the statement, estimates the cost of each execution plan, compares the cost of the plans, and finally selects an execution plan with the minimum cost.

[0069] For the mapping relationship set configured in the configuration loader 112, such as Figure 1EAs shown, the mapping relationship set in the configuration loader 112 can be configured by an input conversion file, which defines which types of conversions are to be performed on those SQL statements. Further, to avoid restarting the database when the conversion file is input, the configuration loader 112 can be designed to load at a scheduled time. When the configuration file is updated, the configuration loader 112 will reread the configuration file in the next query execution cycle and notify the matcher 113 and the converter 114 to update the conversion rules. The query execution cycle is the cycle of the database management system 118 processing an SQL query.

[0070] For example, for the input conversion file, a standard JSON library can be introduced to solve the configuration problem. All configurations are presented in JSON format. In one implementation method, the design format for each mapping relationship in the mapping relationship set is as follows:

[0071]

[0072] Among them, "mapping type", "first expression" and "second expression" are required settings for each mapping relationship. The conversion file of the database SQL can be written by experts who are familiar with the syntax rules of different databases, or automatically generated after AI training, which is not limited here.

[0073] Based on the above Figures 1A to 1C database system, and Figure 1D and Figure 1E Nodes, combined Figure 2 , introduces the request processing method for accessing a database by the request conversion device 110 provided in an embodiment of the present application.

[0074] See also Figure 2 , an embodiment of the request processing method provided in the embodiment of the present application includes:

[0075] 201. Get the first SQL statement;

[0076] The request conversion device 110 receives a first SQL statement from the client 102 to access the database.

[0077] 202. Determine a first expression matching the first SQL statement;

[0078] When a request is received, the matcher 113 processes the request to determine whether it matches a specific expression according to the mapping relationship set in the configuration loader 112. Since the mapping relationship set includes mapping relationships of various mapping types, the matcher 113 also includes different matching rules when matching requests.

[0079] In view of the situation where functions with the same functions exist in different databases but with different function names, the applicant has designed a completely equivalent replacement SQL conversion method with the mapping type of "complete mapping".

[0080] Exemplarily, the mapping type of the first mapping relationship in the mapping relationship set is "complete mapping". The matching rule at this time is complete equality matching, also known as exact matching, requiring that the SQL to be matched is exactly the same as the specified first expression. For example, the systimestamp constant in the Oracle database can be used to obtain the current time, and the equivalent function that can be converted is the clock_timestamp() function in the GaussDB database or the openGauss database.

[0081] Specifically, the first mapping relationship is presented in JSON format as follows:

[0082]

[0083] It is understandable that "EQUAL" is a possible identification method for identifying the mapping type of the mapping relationship as "complete mapping", and "from" and "to" are possible identification methods for identifying the first expression and the second expression. In actual applications, there are more identification methods for the mapping type of the mapping relationship, such as other custom character identifiers, such as "EQ", "eq", etc., or identification in the form of comments other than JSON; for the first expression and the second expression, they can also be designed as: "start" and "end", "before" and "after", etc., which are not limited here.

[0084] See also Figure 3 , Figure 3 This is a schematic diagram of a request processing flow for a mapping type of "complete mapping". When the characters of the first SQL statement are "select systimestamp-interval'10min'", the first SQL statement is completely identical to the first expression of the first mapping relationship, and the first mapping relationship can be determined to be the target mapping relationship corresponding to the first SQL statement.

[0085] Furthermore, since database access requests often contain dynamically changing SQL statements, these statements usually contain numbers or strings such as time, amount, order number, etc. In this case, the complete mapping method may not be applicable because it is impossible to exhaustively list all possible SQL statements. Based on this, the applicant also designed a method of first extracting dynamic variables in SQL and then replacing the SQL statement, in which the dynamic variables are placed in the appropriate position in the replaced SQL statement, and the mapping type is "variable mapping".

[0086] Exemplarily, the mapping type of the second mapping relationship in the mapping relationship set is "variable mapping", the first expression of the second mapping relationship includes a variable extractor, and the variable is extracted specifically by using a regular expression; the second expression includes a variable matcher. At this time, the matching rule is regular expression matching, and when the first SQL statement meets the regular matching in the first expression, the dynamic variables in the first SQL statement can be extracted. For example, there are multiple SQL statements that have the same statement fragments except for different time parameters. At this time, only the same part needs to be extracted, and the time parameter part is replaced by a regular expression that can extract the time parameter as the first expression, so that the multiple SQL statements can be matched.

[0087] It should be noted that a regular expression, also known as a regular expression, is a text pattern. It can be used to match complex string patterns. Regular expressions can contain various metacharacters, character classes, qualifiers, grouping, selection, repetition and other elements, which enables them to handle various complex matching requirements. For example, you can use a regular expression to match a string containing a specific set of characters, repeated a specific number of times, or a string that starts or ends with a specific character, etc.

[0088] Specifically, the second mapping relationship is presented in JSON format as follows:

[0089]

[0090] It can be understood that "REGX" is a possible identification method for identifying the mapping type of the mapping relationship as "variable mapping". In actual applications, there are more identification methods for the mapping type of the mapping relationship, such as other custom character identifiers, such as "REGEX", "RE", etc., or identification in the form of comments other than JSON, which is not limited here.

[0091] See also Figure 4 , Figure 4 The figure is a request processing flow diagram for a mapping type of "variable mapping". 401b is a variable extractor, which extracts the regular expression "[^']+" in parentheses through "()". Specifically, the regular expression represents matching any character except the single quote ('), and matching once or multiple times. If there are multiple dynamic variables in the first SQL statement, multiple variable extractors can be set simultaneously in the first expression.

[0092] In practical applications, in addition to "()", other symbols can be set to define the boundaries of regular matching, which are not specifically limited here.

[0093] like Figure 4When the characters of the first SQL statement are "select sysdate-to_date('2023-09-2508:00:00','yyyy-mm-dd hh24:mi:ss')from dual", except for the dynamic variable 401a, the remaining characters in the first SQL statement are exactly the same as the first expression except for the 401b part, and the data in 401a matches the regular expression in 401b. At this time, it can be determined that the second mapping relationship is the target mapping relationship corresponding to the first SQL statement.

[0094] However, the applicant has found that when there is a regular expression in the SQL statement itself, and this part does not want to be recognized and replaced, the "variable mapping" solution cannot be solved. In addition, the second database may not fully possess the functions expressed by all functions of the first database, but some functions of the first database can be implemented in other forms. For example, in Oracle, the queried data can be output by row-to-column conversion through the pivot() function, but in GaussDB, function expressions similar to the pivot() function are not supported.

[0095] Based on this, the applicant also designed a method to replace two SQL statements using a template expression, and the mapping type is "template mapping".

[0096] Exemplarily, the mapping type of the third mapping relationship in the mapping relationship set is "template mapping", and the first expression and the second expression of the third mapping relationship both include different template variables, and the different template variables respectively indicate a logical processing method, such as regular expression matching or other processing methods, wherein the acquired SQL can be processed by regular expression matching.

[0097] Specifically, the third mapping relationship is presented in JSON format as follows:

[0098]

[0099] It can be understood that "TEMPLATE" is a possible identification method for identifying the mapping type of the mapping relationship as "template mapping". In actual applications, there are more identification methods for the mapping type of the mapping relationship, such as other custom character identifiers, such as "TEMP", "TMPL", etc., or identification in the form of comments other than JSON, which is not limited here.

[0100] Among them, "colExpr", "items" and "output" are template variables of the third mapping relationship, and template variables can act on multiple places in the mapping relationship.

[0101] Exemplarily, template variables are introduced in the expression in the form of "{$$variable name$$}". For example, in the first expression, {$$colExpr$$} introduces the template variable "colExpr"; in the second expression, {$$output$$} introduces the template variable "output".

[0102] See also Figure 5 , Figure 5 The figure is a request processing flow diagram for a mapping type of "template mapping". 501b is a template variable "colExpr". The expression corresponding to the template variable is usually used to describe the logical processing method for the template variable, and can also be regarded as a rule for equivalent replacement. The content of 501c can be replaced into 501b. At this time, the first expression is "select*from ScoreTbl pivot(sum(score)for subject in(['(\w)+'\w+]+,?))", which is the first expression that can be matched by regular expression.

[0103] like Figure 5 When the characters of the first SQL statement are "select * from ScoreTbl pivot (sum (score) for subject in ('SUB1'SUB1, 'SUB2'SUB2, 'SUB3'SUB3))", except for part 501a, the remaining characters in the first SQL statement are exactly the same as those in the first expression except for part 501b, and the data in 501a matches the regular expression in 501b. At this time, it can be determined that the third mapping relationship is the target mapping relationship corresponding to the first SQL statement.

[0104] Exemplarily, template variables can also be introduced into expressions corresponding to other template variables in the form of "$variable name". For example, the parameters extracted from the first expression after regular matching need to be used in the expression of the second template variable "items". Introducing "$colExpr" into the expression corresponding to "items" can act as the parameters extracted after the regular matching.

[0105] It is understandable that referencing the template variable in the first expression or the second expression in the form of "{$$variable name$$}", or introducing the template variable in the expression corresponding to the template variable in the form of "$variable name", is only a preset way of introducing the template variable. In actual applications, there may be many other possible forms of expression, such as "{##variable name##}", "{&variable name&}" or "@variable name", etc., as long as similar expressions in SQL can be avoided, which leads to ambiguity in extraction or replacement, and no specific limitation is made here.

[0106] Specifically, if the first SQL statement fails to match any mapping relationship in the mapping relationship set in the matcher 113, the first SQL statement will be directly sent to the query engine 120 and parsed by the SQL parser.

[0107] In addition, "full mapping", "variable mapping" and "template mapping" are all generalizations and abbreviations for specific mapping methods. In fact, there may be more specific technical names or business terms to describe these mapping methods.

[0108] 203. Generate a second SQL statement based on the first expression and the target mapping relationship.

[0109] After determining the target mapping relationship corresponding to the first SQL statement, a second SQL statement is generated according to a second expression of the target mapping relationship and the first SQL statement.

[0110] The following describes the process of generating the second SQL statement for mapping types "complete mapping", "variable mapping" and "template mapping":

[0111] For example, Figure 3 As shown, when the mapping type of the target mapping relationship is "full mapping", the second expression in the target mapping relationship can be directly used as the second SQL statement, and the generated SQL statement is "select clock_timestamp()–interval'10min'", which can be used to access the GaussDB database.

[0112] For example, Figure 4 As shown, when the mapping type of the target mapping relationship is "variable mapping", in step 202, the dynamic variable 401a "2023-09-25 08:00:00" is matched according to the regular expression of the variable extractor of the first expression. Figure 4 402a in the second expression is replaced, that is, the variable matcher "\1" of the second expression is used to generate a second SQL statement. The generated SQL statement is "select cast((sysdate-cast('2023-09-25 08:00:00'as timestamp))asnumber)", which can be used to access the GaussDB database.

[0113] Correspondingly, if there are multiple dynamic variables in the first SQL statement, multiple variable matchers, such as "\2" and "\3", can be set in the second expression at the same time. In actual applications, in addition to "\number", other symbols can be set to identify variable matchers, which are not limited here.

[0114] For example, Figure 5 As shown, when the mapping type of the target mapping relationship is "template mapping", in step 202, the dynamic variable 501a is matched according to the regular expression of the template variable "colExpr" of the first expression: "'SUB1'SUB1,'SUB2'SUB2,'SUB3'SUB3". Since the GaussDB database cannot recognize the grammatical rules, the dynamic variable 501a cannot be directly replaced in the second expression.

[0115] The applicant has found that the GaussDB database can query the data of each row and output them in columns for the pivot() function of the Oracle database, so as to achieve the same query function. Specifically, the data of 'SUB1', 'SUB2' and 'SUB3' can be queried respectively.

[0116] At this time, an intermediate variable, template variable 2 "items", is needed. This template variable is not in the first expression and the second expression. In the expression 502 corresponding to template variable 2 "items", two "template functions" are used, including: $LIST: generate an array from the result, $REG_EXTRACT: extract the value in the regular expression. Therefore, a set of arrays ["SUB1", "SUB2", "SUB3"] will be processed in template variable 2 "items".

[0117] Template variable three "output" can be regarded as the output variable in the second expression, which generates a set of output strings. Template variable two "items" is used as an intermediate process in the expression 503b corresponding to template variable three "output", as well as a "template function": $LIST_TO_STR: Generate a string from an array. The usage of this function is $LIST_TO_STR (string template, array, separator), in which two dynamic values ​​are introduced in the string template: $items.it (the value in the iteration array) and $items.i (the subscript in the iteration array, such as: 1, 2, 3... etc.), and the iteration value is written to spell the string; correspondingly, the array in the $LIST_TO_STR function is the array obtained by processing template variable two "items" ["SUB1", "SUB2", "SUB3"].

[0118] Furthermore, if there are single quotes in the string, the single quotes in the internal spelling are escaped using "\".

[0119] Finally, the template variable three "output" is processed to obtain "sum((decode(subject,'SUB1',score))SUB1,sum((decode(subject,'SUB2',score))SUB2,sum((decode(subject,'SUB3',score))SUB3", which is replaced with {$$output$$}: 503a in the second expression. The second SQL statement is obtained, and the generated SQL statement is "select stu_name,term,sum((decode(subject,'SUB1',score))SUB1,sum((decode(subject,'SUB2',score))SUB2,sum((decode(subject,'SUB3',score))SUB3from ScoreTbl group by stu_name,term". The result obtained by the second SQL statement accessing the GaussDB database is the same as the result obtained by the first SQL statement accessing the Oracle database.

[0120] It should be noted that for the "template functions" used internally, standard definitions need to be expressed, for example, all internal "template functions" are capitalized, and the first letter of internal variable definitions is lowercase. "Template functions" are pre-defined processing functions in the engine kernel, and their specific names and functions are displayed to users through documents.

[0121] For example, in addition to the "template functions" shown above, more commonly used "template functions" can be introduced, such as the function for extracting keywords: $EXTRACT, the function for traversing array elements: $FOR_EACH, the function for generating random numbers: $RANDOM_NUM, etc. "Template functions", which will not be introduced one by one here.

[0122] In the embodiment of the present application, before processing the SQL query in the database, by introducing the SQL converter, a conversion capability for SQL is provided. Through configuration, the database compatibility problem can be solved without modifying the database kernel and without negatively affecting the existing syntax and functions. In addition, based on the SQL converter, the expansion of the database is also made easier. The data of one database system can be easily converted to another database system without modifying the original data and functions, which provides convenience for data migration and integration.

[0123] The above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit it. Although the present invention has been described in detail with reference to the aforementioned embodiments, a person skilled in the art should understand that the technical solutions described in the aforementioned embodiments may still be modified, or some of the technical features may be replaced by equivalents. However, these modifications or replacements do not deviate the essence of the corresponding technical solutions from the protection scope of the technical solutions of the embodiments of the present invention.

[0124] The above describes the request processing method provided by the embodiment of the present application. The following describes the request processing device provided by the embodiment of the present application. Figure 6 , Figure 6 FIG. 6 is a schematic diagram of a structure of a request processing device according to an embodiment of the present application. The request processing device 600 can be used to execute Figures 2 to 5 For the steps performed in the embodiment shown in , please refer to the relevant introduction in the above method embodiment for details.

[0125] The request processing device 600 includes an acquisition device 601, a conversion device 602 and an execution device 603. The acquisition device 601 can realize the corresponding communication function, and the conversion device 602 and the execution device 603 are used for data processing. The acquisition device 601 can also be called a communication interface or a communication unit.

[0126] Optionally, the request processing device 600 may further include a storage unit, which may be used to store instructions and / or data. The conversion device 602 and the execution device 603 may read the instructions and / or data in the storage unit so that the network device implements the aforementioned method embodiment.

[0127] The request processing device 600 can be used to perform the actions in the above method embodiment. The request processing device 600 can be a network device or a component that can be configured in a network device. The acquisition device 601 is used to perform the reception-related operations in the above method embodiment, and the conversion device 602 and the execution device 603 are used to perform the processing-related operations in the above method embodiment.

[0128] The request processing device 600 includes:

[0129] The acquisition means 601 is used to acquire a first structured query language SQL statement from a client, wherein the grammatical rule of the first SQL statement is the same as the grammatical rule of the SQL statement of the second database system;

[0130] The conversion device 602 is used to convert the first SQL statement into a second SQL statement according to the configured mapping relationship, wherein the second SQL statement conforms to the grammatical rules of the SQL statement of the second database system, and the mapping relationship includes a correspondence between the first SQL statement expression conforming to the grammatical rules of the first database system and the second SQL statement expression conforming to the grammatical rules of the second database system;

[0131] The execution device 603 is used for the second SQL statement, wherein the result of the first database system executing the second SQL statement is the same as the result of the second database system executing the first SQL statement.

[0132] The conversion device 602 and the execution device 603 in the above embodiment can be implemented by at least one processor or processor-related circuit. The acquisition device 601 can be implemented by a transceiver or a transceiver-related circuit. The acquisition device 601 can also be called a communication unit or a communication interface. The storage unit can be implemented by at least one memory.

[0133] The present application also provides a computing device 100. Figure 7 As shown, the computing device 100 includes: a bus 102, a processor 104, a memory 106, and a communication interface 108. The processor 104, the memory 106, and the communication interface 108 communicate with each other through the bus 102. The computing device 100 can be a server or a terminal device. It should be understood that the present application does not limit the number of processors and memories in the computing device 100.

[0134] The bus 102 may be a peripheral component interconnect (PCI) bus or an extended industry standard architecture (EISA) bus. The bus may be divided into an address bus, a data bus, a control bus, etc. For ease of representation, Figure 7 The bus 104 is represented by only one line, but does not mean that there is only one bus or one type of bus. The bus 104 may include a path for transmitting information between various components of the computing device 100 (eg, the memory 106, the processor 104, and the communication interface 108).

[0135] The processor 104 may include any one or more of a central processing unit (CPU), a graphics processing unit (GPU), a microprocessor (MP), or a digital signal processor (DSP).

[0136] The memory 106 may include a volatile memory, such as a random access memory (RAM). The processor 104 may also include a non-volatile memory, such as a read-only memory (ROM), a flash memory, a hard disk drive (HDD), or a solid state drive (SSD).

[0137] The memory 106 stores executable program codes, and the processor 104 executes the executable program codes to respectively implement the functions of the aforementioned acquisition device, conversion device, and execution device, thereby implementing the request processing method. That is, the memory 106 stores instructions for executing the request processing method.

[0138] Alternatively, the memory 106 stores executable codes, and the processor 104 executes the executable codes to respectively implement the functions of the request conversion device, thereby implementing the request processing method. That is, the memory 106 stores instructions for executing the request processing method.

[0139] The communication interface 103 uses a transceiver module such as, but not limited to, a network interface card or a transceiver to implement communication between the computing device 100 and other devices or a communication network.

[0140] The embodiment of the present application also provides a computing device cluster. The computing device cluster includes at least one computing device. The computing device can be a server, such as a central server, an edge server, or a local server in a local data center. In some embodiments, the computing device can also be a terminal device such as a desktop computer, a laptop computer, or a smart phone.

[0141] like Figure 8 As shown, the computing device cluster includes at least one computing device 100. The memory 106 in one or more computing devices 100 in the computing device cluster may store the same instructions for executing the request processing method.

[0142] In some possible implementations, the memory 106 of one or more computing devices 100 in the computing device cluster may also store partial instructions for executing the request processing method. In other words, the combination of one or more computing devices 100 may jointly execute instructions for executing the request processing method.

[0143] It should be noted that the memory 106 in different computing devices 100 in the computing device cluster may store different instructions, which are respectively used to execute part of the functions of the request conversion device. That is, the instructions stored in the memory 106 in different computing devices 100 may implement the functions of one or more of the acquisition device, the conversion device and the execution device.

[0144] In some possible implementations, one or more computing devices in the computing device cluster may be connected via a network, which may be a wide area network or a local area network. Fig. 9 A possible implementation is shown. Fig. 9 As shown, two computing devices 100A and 100B are connected via a network. Specifically, the network is connected via a communication interface in each computing device. In this type of possible implementation, the memory 106 in the computing device 100A stores instructions for executing the functions of the acquisition device and the conversion device. At the same time, the memory 106 in the computing device 100B stores instructions for executing the functions of the execution device.

[0145] Fig. 9 The connection method between the computing device clusters shown can be considered to be that the request processing method provided in this application needs to be associated with a large amount of storage data in the object storage service, so it is considered to entrust the function of executing the second SQL statement to the computing device 100B.

[0146] It should be understood that Fig. 9 The functions of the computing device 100A shown in FIG. 1 may also be completed by multiple computing devices 100. Similarly, the functions of the computing device 100B may also be completed by multiple computing devices 100.

[0147] The embodiment of the present application also provides a computer program product including instructions. The computer program product may be software or a program product including instructions that can be run on a computing device or stored in any available medium. When the computer program product is run on at least one computing device, the at least one computing device executes the request processing method.

[0148] The embodiment of the present application also provides a computer-readable storage medium. The computer-readable storage medium can be any available medium that can be stored by the computing device or a data storage device such as a data center containing one or more available media. The available medium can be a magnetic medium (e.g., a floppy disk, a hard disk, a tape), an optical medium (e.g., a DVD), or a semiconductor medium (e.g., a solid-state hard disk). The computer-readable storage medium includes instructions that instruct the computing device to execute the request processing method.

[0149] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit it. Although the present invention has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the aforementioned embodiments, or make equivalent replacements for some of the technical features therein. However, these modifications or replacements do not cause the essence of the corresponding technical solutions to deviate from the protection scope of the technical solutions of the embodiments of the present invention.

Claims

1. A request processing method, characterized in that: The method is applied to a first database system, and the method comprises: The first database system acquires a first structured query language SQL statement, wherein a grammatical rule of the first SQL statement conforms to a grammatical rule of the second database system; The first database system converts the first SQL statement into a second SQL statement according to a configured mapping relationship, wherein the second SQL statement complies with a grammatical rule of the second database system, and the mapping relationship includes a correspondence between a first SQL statement expression that complies with the grammatical rule of the first database system and a second SQL statement expression that complies with the grammatical rule of the second database system; The first database system executes the second SQL statement, wherein a result of the first database system executing the second SQL statement is the same as a result of the second database system executing the first SQL statement.

2. The method according to claim 1, characterized in that The first database system converts the first SQL statement into a second SQL statement according to the configured mapping relationship, including: The first database system determines the first SQL statement expression that matches the first SQL statement; Based on the first SQL statement expression and the mapping relationship, determine the second SQL statement expression corresponding to the first SQL statement expression; The second SQL statement is generated according to the second SQL statement expression and the first SQL statement.

3. The method according to claim 1 or 2, characterized in that: The mapping relationship further includes a mapping type, and the mapping type is used to indicate a processing method for generating the second SQL statement based on the second SQL statement expression.

4. The method according to claim 3, characterized in that When the mapping type indicates complete mapping, the first SQL statement expression is the same as the first SQL statement, and the second SQL statement is the second SQL statement expression.

5. The method according to claim 3, characterized in that: When the mapping type indicates variable mapping, the first SQL statement expression includes a variable extractor, and the variable extractor is used to extract at least one variable from the first SQL statement; The second SQL statement expression includes a variable matcher, and the second SQL statement is obtained by replacing the variable matcher in the second SQL statement expression with the at least one variable.

6. The method according to claim 3, characterized in that When the mapping type indicates template mapping, the first SQL statement expression includes a first template variable symbol; The first template variable symbol is used to extract at least one variable from the first request; or, the first template variable symbol is used to extract at least one variable from the first SQL statement, and is used to indicate processing logic for the at least one variable; The second SQL statement expression includes a second template variable symbol. The second SQL statement is obtained by replacing the second template variable symbol in the second SQL statement expression with a variable processing result. The variable processing result is obtained by processing the at least one variable or intermediate result according to the processing logic indicated by the second template variable symbol. The intermediate result is a result obtained by processing the at least one variable according to the processing logic indicated by the first template variable symbol.

7. A database system, characterized in that: The database system is a first database system, comprising: An acquisition device is used to acquire a first structured query language SQL statement, wherein the grammatical rules of the first SQL statement conform to the grammatical rules of the second database system; A conversion device, used for converting the first SQL statement into a second SQL statement according to a configured mapping relationship, wherein the second SQL statement conforms to the grammatical rules of the second database system, and the mapping relationship includes a correspondence between a first SQL statement expression conforming to the grammatical rules of the first database system and a second SQL statement expression conforming to the grammatical rules of the second database system; An execution device is used to execute the second SQL statement, wherein a result of the first database system executing the second SQL statement is the same as a result of the second database system executing the first SQL statement.

8. The system according to claim 7, characterized in that The conversion device is specifically used for: Determine the first SQL statement expression that matches the first SQL statement; Based on the first SQL statement expression and the mapping relationship, determine the second SQL statement expression corresponding to the first SQL statement expression; The second SQL statement is generated according to the second SQL statement expression and the first SQL statement.

9. The system according to claim 7 or 8, characterized in that The mapping relationship further includes a mapping type, and the mapping type is used to indicate a processing method for generating the second SQL statement based on the second SQL statement expression.

10. The system according to claim 9, characterized in that When the mapping type indicates complete mapping, the first SQL statement expression is the same as the first SQL statement, and the second SQL statement is the second SQL statement expression.

11. The system according to claim 9, characterized in that When the mapping type indicates variable mapping, the first SQL statement expression includes a variable extractor, and the variable extractor is used to extract at least one variable from the first SQL statement; The second SQL statement expression includes a variable matcher, and the second SQL statement is obtained by replacing the variable matcher in the second SQL statement expression with the at least one variable.

12. The system according to claim 9, characterized in that When the mapping type indicates template mapping, the first SQL statement expression includes a first template variable symbol; The first template variable symbol is used to extract at least one variable from the first request; or, the first template variable symbol is used to extract at least one variable from the first SQL statement, and is used to indicate processing logic for the at least one variable; The second SQL statement expression includes a second template variable symbol. The second SQL statement is obtained by replacing the second template variable symbol in the second SQL statement expression with a variable processing result. The variable processing result is obtained by processing the at least one variable or intermediate result according to the processing logic indicated by the second template variable symbol. The intermediate result is a result obtained by processing the at least one variable according to the processing logic indicated by the first template variable symbol.

13. A request processing device, characterized in that: The apparatus includes at least one processor coupled to a memory; The memory is used to store programs or instructions; The at least one processor is configured to execute the program or instruction so that the apparatus implements the method according to any one of claims 1 to 6.

14. A computing device cluster, characterized in that: It comprises at least one computing device, each computing device comprises a processor and a memory; the processor of the at least one computing device is used to execute instructions stored in the memory of the at least one computing device, so that the computing device cluster executes the method according to claims 1-6.

15. A computer program product comprising instructions, characterized in that When the instructions are executed by a computing device cluster, the computing device cluster executes the method according to any one of claims 1 to 6.

16. A computer-readable storage medium, characterized in that: The method comprises computer program instructions. When the computer program instructions are executed by a computing device cluster, the computing device cluster executes the method according to any one of claims 1 to 6.

Images