WO2023045054A1

WO2023045054A1 - Structured query statement generation method and apparatus, and computer device

Info

Publication number: WO2023045054A1
Application number: PCT/CN2021/131748
Authority: WO
Inventors: 孙岩; 盛浩; 孙迁; 张光淡; 华有军; 邰娟
Original assignee: 苏宁易购集团股份有限公司
Priority date: 2021-09-27
Filing date: 2021-11-19
Publication date: 2023-03-30
Also published as: CN113918594A

Abstract

A structured query statement generation method and apparatus, and a computer device and storage medium. The method comprises: acquiring information to be queried and a preset structured query statement template (202); replacing dynamic information in the preset structured query statement template with the information to be queried, and generating a structured execution statement (204); and generating a globally unique ID according to the structured execution statement, and storing the structured execution statement and the ID in a database relationship table (206). By means of the method, the replacement of a dynamic parameter can be realized, so as to generate a new structured execution statement, thereby achieving the effects of saving a lot of time and improving the operation efficiency.

Description

Method, device and computer equipment for generating structured query statement

technical field

The present application relates to the technical field of big data, and in particular to a method, device, computer equipment and storage medium for generating structured query statements.

Background technique

With the development of big data technology, data developers often need to compare and analyze data forms of different offline tables during requirements development, data migration or grayscale testing, and find data deviations and anomalies in data results. Deal with problems in logic.

For the problem of how to obtain data results and discover defects in data processing logic, the traditional technical solution is to manually write structured query statements and execute them to obtain data results.

technical problem

However, the manual writing method makes the structured query statements repetitive and complicated, which takes a lot of time for trial and error and verification, does not support the replacement of dynamic parameters, and the used statements cannot be saved and consulted conveniently; at the same time, it is impossible to Batch processing of structured query statements has poor continuity. After outputting the result of a statement, it is necessary to manually add the next statement. It is impossible to efficiently manage each statement, and the work efficiency is low.

technical solution

Based on this, it is necessary to provide a method, device, computer equipment, and storage medium for generating structured query statements to address the above technical problems.

On the one hand, a method for generating a structured query statement is provided, the method comprising:

Obtain the information to be queried and the preset structured query statement template;

Replace the dynamic information in the preset structured query statement template with the information to be queried to generate a structured execution statement;

Generate a globally unique ID according to the structured execution statement; and store the structured execution statement and the ID in the database relational table.

In one of the embodiments, the above-mentioned acquisition of information to be queried includes:

obtain the first information from the metadata;

performing a legality test on the first information, and setting the first information that passes the legality test as the second information;

The second information is used as the information to be queried.

In one of the embodiments, it also includes:

Receive the first information selected by the user;

comparing the first information with information in a specified database, wherein the metadata is obtained from the specified database;

When the first information exists in the specified database, it is determined that the first information is legal;

When the first information does not exist in the specified database, it is determined that the first information is invalid, and the user is prompted to make a new selection.

In one of the embodiments, the above-mentioned globally unique ID is generated according to the structured execution statement; and after storing the structured execution statement and the ID in the database relational table, it also includes:

Create task instance according to ID;

The structured execution statement corresponding to the task instance is extracted from the database relational table for execution, and the obtained execution result is output to the database result table, and the task instance disappears automatically after the execution is completed.

In one of the embodiments, the above-mentioned structured execution statement corresponding to the task instance is extracted from the database relational table for execution, and the obtained execution result is output to the database result table, and the task instance disappears automatically after the execution is completed, including :

When the task instance is successfully executed, the ID, execution result and success status are stored in the database result table and displayed on the operation interface;

When the execution of the task instance fails, the ID, failure status and error log are stored in the database result table and displayed on the operation interface.

In one of the embodiments, it also includes:

Perform formatting and grammar proofreading on structured execution statements.

In one of the embodiments, the above-mentioned formatting of the structured execution statement includes:

Replace the blank characters in the structured execution statement with a single space and unify the structured execution statement into uppercase or lowercase letters;

Syntax proofreading of structured execution statements, including:

Error correction for special characters and reserved words in structured execution statements.

On the other hand, a device for generating a structured query statement is provided, and the device includes:

The obtaining module is used to obtain the information to be queried and the preset structured query statement template;

The preprocessing module is used to replace the dynamic information in the preset structured query statement template with the information to be queried, and generate a structured execution statement;

The generating module is used for generating a globally unique ID according to the structured execution statement; and storing the structured execution statement and the ID in a relational table of the database.

In yet another aspect, a computer device is provided, including a memory, a processor, and a computer program stored on the memory and operable on the processor. When the processor executes the computer program, the following steps are implemented:

In yet another aspect, a computer-readable storage medium is provided, on which a computer program is stored, and when the computer program is executed by a processor, the following steps are implemented:

Beneficial effect

The method, device, computer equipment, and storage medium for generating the above-mentioned structured query statement, firstly, by obtaining the information to be queried and the preset structured query statement template, the dynamic information in the preset structured query statement template is replaced For the information to be queried, a structured execution statement is generated; then, a globally unique ID is generated according to the structured execution statement; finally, the structured execution statement and the ID are stored in a database relational table. The above process does not need to be written manually, and the structured query statement template can be obtained directly, and a new structured execution statement can be generated by replacing the dynamic parameters in the structured query statement template, so as to save a lot of time and improve work efficiency; At the same time, in the later stage, the user can directly implement the execution of the structured execution statement and query the results according to the ID, so as to achieve the effect of conveniently saving and consulting the structured execution statement, and realize the efficient management of the structured execution statement.

Description of drawings

Fig. 1 is an application environment diagram of a method for generating a structured query statement in an embodiment;

Fig. 2 is a schematic flow chart of a method for generating a structured query statement in an embodiment;

Fig. 3 is a structural block diagram of a device for generating a structured query statement in an embodiment;

Figure 4 is an internal block diagram of a computer device in one embodiment.

Embodiments of the present invention

In order to make the purpose, technical solution and advantages of the present application clearer, the present application will be further described in detail below in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described here are only used to explain the present application, and are not intended to limit the present application.

A method for generating a structured query statement provided in this application can be applied to the application environment shown in FIG. 1 . Wherein, the terminal 102 communicates with the server 104 through the network. The server 104 obtains the information to be queried and the preset structured query statement template; the server replaces the dynamic information in the preset structured query statement template with the information to be queried to generate a structured execution statement; and generates a global query statement according to the structured execution statement Unique ID; and store the structured execution statement and ID in the relational table of the database. Wherein, the terminal 102 can be, but not limited to, various personal computers, notebook computers, tablet computers and other devices, and the server 104 can be realized by an independent server or a server cluster composed of multiple servers.

In one embodiment, as shown in FIG. 2, a method for generating a structured query statement is provided. The method is applied to the terminal in FIG. 1 as an example for illustration, including the following steps:

Step 202, obtaining the information to be queried and a preset structured query statement template.

Wherein, the information to be queried includes but not limited to database name, table name, field and other information; the preset structured query statement template refers to the structured query statement saved in the query database in advance.

Specifically, data developers often need to compare and analyze data forms of different offline tables during requirements development, data migration, or grayscale testing. At this time, they need to obtain the information to be queried, that is, the corresponding database name, Information such as table names and fields; at the same time, the server obtains the corresponding structured query statement template from the query database.

Step 204, replacing the dynamic information in the preset structured query statement template with the information to be queried to generate a structured execution statement.

Among them, structured query statement is a kind of database query and programming language, which is used to access and query data, etc.; dynamic information refers to dynamic parameters that can be replaced, and the server will preset the dynamic information in the structured query statement template. The information is replaced with the information to be queried that we need to compare to generate a new structured query statement, that is, a structured execution statement, which is a complete structured query statement.

Step 206, generate a globally unique ID according to the structured execution statement; and store the structured execution statement and the ID in the relational table of the database.

Generate an ID based on each structured execution statement, this ID is globally unique, each structured execution statement and each ID are in one-to-one correspondence, without duplication; and, store this ID and the structured execution statement in the database In the relational table; in this way, later users can directly realize the execution of the structured execution statement and the query of the result according to the ID.

Among them, the database includes the Hive data warehouse and relational database, and the relational database includes the mainstream MySQL database and DB2 database. How many types of tables are there in a relational database? A database relational table refers to a table used to store the correspondence between structured execution statements and IDs.

The method, device, computer equipment, and storage medium for generating the above-mentioned structured query statement, firstly, by obtaining the information to be queried and the preset structured query statement template, the dynamic information in the preset structured query statement template is replaced For the information to be queried, a structured execution statement is generated; then, a globally unique ID is generated according to the structured execution statement; finally, the structured execution statement and the ID are stored in a database relational table. The above process does not need to be written manually, and the structured query statement template can be obtained directly, and a new structured execution statement can be generated by replacing the dynamic parameters in the structured query statement template, so as to save a lot of time and improve work efficiency; At the same time, in the later stage, the user can directly implement the execution of the structured execution statement and query the results according to the ID, so as to achieve the effect of conveniently saving and consulting the structured execution statement, and realize the efficient management of the structured execution statement .

In one of the embodiments, the above-mentioned acquisition of information to be queried includes the following steps:

The first information is obtained from the metadata. The metadata is a description of the data resource, and the first information includes the library name, table name, and field name selected by the user. Carry out a legality test on the first information, and set the first information that passes the legality test as the second information. This operation is mainly for the preliminary check of the first information selected by the user, because if the first information selected by the user If it is illegal, it means that the structured execution statement generated later is wrong, and there is no meaning of generation.

Conduct a legality test on the first information, specifically including:

The server receives the first information selected by the user, and compares the first information with the information in the specified database. The specified database here refers to the database storing metadata, and the information in the metadata is obtained from the specified database. Therefore, it indirectly indicates that the first information is derived from the specified database. When the first information exists in the specified database, it is determined that the first information is legal, that is, the first information is real, and the first information that passes the legality test is set as the second information, and the second information is used as the query information.

Here, it needs to be further explained that because the metadata information is not a real database, it is obtained from the specified database, and will not be updated synchronously with the data in the specified database, and there may be delays in updating relative to the specified database Therefore, it is necessary to compare the first information selected by the user with the information in the specified database. When the first information does not exist in the designated database, it is judged that the first information is illegal, and the user is prompted to choose again, which means that there is a situation that the update is not timely, and the data to be analyzed does not exist in the designated database, so the user needs to be prompted Make your selection again.

By testing the legitimacy of the first information above, it is ensured that the information to be queried selected by the user is real, and further analysis and comparison of the information to be queried is realized later.

In one of the embodiments, the structured execution statement needs to be formatted and grammatically checked, so that the structured execution statement complies with the syntax standard of the relational database.

Among them, when the structured execution statement is formatted, it includes replacing blank characters in the structured execution statement with a single space and unifying the structured execution statement into uppercase or lowercase letters, so that the structured execution statement is unified; Wherein, the syntax proofreading of the structured execution statement includes error correction of special characters and reserved words in the structured execution statement, so as to ensure the correctness and executability of the structured execution statement.

In one of the embodiments, a globally unique ID is generated according to the structured execution statement; and after storing the structured execution statement and the ID in the relational table of the database, the following steps are further included:

Create a task instance according to the ID. Since each ID corresponds to an independent task, after the user triggers the task instance, the task instance will create a spark task according to the passed ID, and the server extracts the structured execution corresponding to the task instance from the database relational table. Statements are executed, and the obtained execution results are output to the database result table, and each task instance disappears automatically after execution, and multiple task instances can be performed simultaneously. It should be noted that the database result table here is similar to the database relationship table, both of which are in the same database, but belong to different types of tables and are used to store different types of data.

When the task instance is successfully executed, the ID, execution result, and success status are stored in the database result table. Here, after the task instance is successfully executed, there will be a corresponding execution result, and the ID, execution result, and successful status are in one-to-one correspondence It is saved in the database result table, which is convenient for the staff to view it later, and the execution result is echoed to the operation interface, so that the staff can see the current running result; when the execution of the task instance fails, the ID, failure status and The error log is stored in the database result table, because there is no execution result when the task execution fails, then the failure log will be generated and stored in the database result table together with the failure status and ID, so as to remind the staff later to avoid Make the same type of mistake, and echo the error log to the operation interface, so that the staff can see the current execution status; of course, the server also needs to keep the data in the database relationship table and database result table updated in real time.

Furthermore, the user operation interface initiates a data display request to the server, and the server processes the request to obtain the data to be displayed from the database result table and returns it to the user operation interface. The user calls the graph visualization library on the user operation interface according to the terminal’s graph visualization library. The chart style specified in the library is used to assemble the data to realize the visual display of the data.

The above operations support real-time batch execution of multiple structured execution statements, and provide storage and management of execution statements, execution status, and execution results, which can improve the processing efficiency of comparison and analysis of different offline table data forms, and reduce data development. The problems and exceptions in the database can improve the quality of data development; at the same time, the visual display of the data in the database result table solves the problems of unintuitive display of data in the database result table and inconvenient unified viewing.

It should be understood that although the various steps in the flow chart of FIG. 2 are displayed sequentially according to the arrows, these steps are not necessarily executed sequentially in the order indicated by the arrows. Unless otherwise specified herein, there is no strict order restriction on the execution of these steps, and these steps can be executed in other orders. Moreover, at least some of the steps in FIG. 2 may include multiple sub-steps or multiple stages. These sub-steps or stages are not necessarily executed at the same time, but may be executed at different times. The execution of these sub-steps or stages The order is not necessarily performed sequentially, but may be performed alternately or alternately with at least a part of other steps or sub-steps or stages of other steps.

In one embodiment, as shown in FIG. 3 , an apparatus for generating a structured query statement is provided, including: an acquisition module 302, a preprocessing module 304, and a generation module 306, wherein:

The acquiring module 302 is configured to acquire the information to be queried and a preset structured query statement template.

The preprocessing module 304 is configured to replace the dynamic information in the preset structured query statement template with the information to be queried, and generate a structured execution statement.

The generating module 306 is configured to generate a globally unique ID according to the structured execution statement; and store the structured execution statement and the ID in a relational table of the database.

In one embodiment, the acquisition module 302 is also specifically configured to:

The first information is obtained from the metadata; the legality test is performed on the first information, and the first information that passes the legality test is set as the second information; and the second information is used as the information to be queried.

In one of the embodiments, the above-mentioned device for generating structured query sentences further includes a legality judgment module (not shown in the figure), which is used to receive the first information selected by the user; combine the first information and The information in the designated database is compared, wherein the metadata is obtained from the designated database; when the first information exists in the designated database, it is determined that the first information is legal; when the first information does not exist in the designated database, It is determined that the first information is invalid, and the user is prompted to make a new selection.

In one embodiment, the preprocessing module 304 is also used to:

Formatting and grammar proofreading of structured execution statements, and formatting of structured execution statements, including: replacing blank characters in structured execution statements with single spaces and unifying structured execution statements into uppercase letters or lowercase Letters; grammar proofreading for structured execution statements, including: error correction for special characters and reserved words in structured execution statements.

In one embodiment, the generation module 306 is also used to:

Create a task instance according to the ID; extract the structured execution statement corresponding to the task instance from the database relational table for execution, and output the obtained execution result to the database result table, and the task instance will disappear automatically after the execution is completed.

When the task instance executes successfully, store the ID, execution result and success status in the database result table, and display it on the operation interface; when the task instance fails to execute, store the ID, failure status and error log in the database result table , and echo it on the operation interface.

For specific limitations on a device for generating a structured query statement, refer to the above-mentioned limitation on a method for generating a structured query statement, and details are not repeated here. Each module in the above-mentioned device for generating structured query statements can be fully or partially realized by software, hardware and a combination thereof. The above-mentioned modules can be embedded in or independent of the processor in the computer device in the form of hardware, and can also be stored in the memory of the computer device in the form of software, so that the processor can invoke and execute the corresponding operations of the above-mentioned modules.

In one embodiment, a computer device is provided. The computer device may be a server, and its internal structure may be as shown in FIG. 4 . The computer device includes a processor, memory, network interface and database connected by a system bus. Wherein, the processor of the computer device is used to provide calculation and control capabilities. The memory of the computer device includes a non-volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system, computer programs and databases. The internal memory provides an environment for the operation of the operating system and computer programs in the non-volatile storage medium. The database of the computer device is used to store the relationship between the ID and the structured execution statement and the data of the execution result. The network interface of the computer device is used to communicate with an external terminal via a network connection. When the computer program is executed by the processor, a method for generating structured query statements is realized.

Those skilled in the art can understand that the structure shown in Figure 4 is only a block diagram of a part of the structure related to the solution of the present application, and does not constitute a limitation to the computer equipment on which the solution of the application is applied. The specific computer equipment can be More or fewer components than shown in the figures may be included, or some components may be combined, or have a different arrangement of components.

In one embodiment, a computer device is provided, including a memory, a processor, and a computer program stored on the memory and operable on the processor. When the processor executes the computer program, the following steps are implemented:

In one embodiment, when the processor executes the computer program to implement the above-mentioned steps of obtaining the current information to be queried, the following steps are specifically implemented:

When testing the legality of the first information, including:

The server receives the first information selected by the user; compares the first information with the information in the specified database, wherein the metadata is obtained from the specified database; when the first information exists in the specified database, determines the first information Legal; when the first information does not exist in the specified database, it is judged that the first information is not legal, and the user is prompted to choose again.

In one embodiment, the following steps are also implemented when the processor executes the computer program:

Perform formatting processing and grammar proofreading on the structured execution statement; among them, perform formatting processing on the structured execution statement, including: replacing blank characters in the structured execution statement with a single space and unifying the structured execution statement into uppercase letters or lowercase letters;

Grammatical proofreading for structured execution statements, including: error correction for special characters and reserved words in structured execution statements.

In one embodiment, after the processor executes the computer program to realize the above-mentioned steps of generating a globally unique ID according to the structured execution statement; and storing the structured execution statement and the ID in the database relational table, it also includes:

Specifically, when the processor executes the computer program, it also includes:

In one embodiment, a computer-readable storage medium is provided, on which a computer program is stored, and when the computer program is executed by a processor, the following steps are implemented:

In one embodiment, when the computer program is executed by the processor for the above-mentioned steps of obtaining the current information to be queried, the following steps are specifically implemented:

Obtain the first information from the metadata; perform a legality test on the first information, and set the first information that passes the legality test as the second information; and use the second information as the information to be queried.

When testing the legality of the first information, including:

In one embodiment, the following steps are also implemented when the computer program is executed by the processor:

In one embodiment, after the computer program is executed by the processor to generate the globally unique ID according to the structured execution statement; and store the structured execution statement and the ID in the database relational table, it also includes:

When the computer program is executed by the processor, it specifically includes:

Those of ordinary skill in the art can understand that realizing all or part of the processes in the methods of the above embodiments can be completed by instructing related hardware through computer programs, and the computer programs can be stored in a non-volatile computer-readable storage medium , when the computer program is executed, it may include the procedures of the embodiments of the above-mentioned methods. Wherein, any references to memory, storage, database or other media used in the various embodiments provided in the present application may include non-volatile and/or volatile memory. Nonvolatile memory can include read only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable programmable ROM (EEPROM), or flash memory. Volatile memory can include random access memory (RAM) or external cache memory. By way of illustration and not limitation, RAM is available in many forms such as Static RAM (SRAM), Dynamic RAM (DRAM), Synchronous DRAM (SDRAM), Double Data Rate SDRAM (DDRSDRAM), Enhanced SDRAM (ESDRAM), Synchronous Chain Synchlink DRAM (SLDRAM), memory bus (Rambus) direct RAM (RDRAM), direct memory bus dynamic RAM (DRDRAM), and memory bus dynamic RAM (RDRAM), etc.

The technical features of the above embodiments can be combined arbitrarily. To make the description concise, all possible combinations of the technical features in the above embodiments are not described. However, as long as there is no contradiction in the combination of these technical features, they should be It is considered to be within the range described in this specification.

The above examples only express several implementation modes of the present application, and the description thereof is relatively specific and detailed, but should not be construed as limiting the scope of the patent for the invention. It should be noted that those skilled in the art can make several modifications and improvements without departing from the concept of the present application, and these all belong to the protection scope of the present application. Therefore, the scope of protection of the patent application should be based on the appended claims.

Claims

A method for generating a structured query statement, characterized in that the method comprises:

Obtain the information to be queried and the preset structured query statement template;

replacing the dynamic information in the preset structured query statement template with the information to be queried to generate a structured execution statement;

A globally unique ID is generated according to the structured execution statement; and the structured execution statement and the ID are stored in a database relational table.
The method according to claim 1, wherein said obtaining the information to be queried comprises:

obtain the first information from the metadata;

performing a legality test on the first information, and setting the first information that passes the legality test as the second information;

The second information is used as the information to be queried.
The method according to claim 2, further comprising:

Receive the first information selected by the user;

comparing the first information with information in a specified database, wherein the metadata is obtained from the specified database;

determining that the first information is legal when the first information exists in the specified database;

When the first information does not exist in the specified database, it is determined that the first information is invalid, and the user is prompted to make a new selection.
The method according to claim 1, wherein said generating a globally unique ID according to said structured execution statement; and storing said structured execution statement and said ID in a database relational table, further comprising:

Create a task instance according to the ID;

The structured execution statement corresponding to the task instance is extracted from the database relational table for execution, and the obtained execution result is output to the database result table, and the task instance disappears automatically after execution is completed.
The method according to claim 4, wherein the structured execution statement corresponding to the task instance is extracted from the database relational table for execution, and the obtained execution result is output to the database result table, And the task instance disappears automatically after execution is completed, including:

When the task instance is successfully executed, the ID, execution result and success status are stored in the database result table, and are echoed on the operation interface;

When the execution of the task instance fails, the ID, failure status and error log are stored in the database result table, and are echoed on the operation interface.
The method according to claim 1, further comprising: performing formatting processing and grammar checking on the structured execution statement.
The method according to claim 6, wherein formatting the structured execution statement includes:

replacing blank characters in the structured execution statement with a single space and unifying the structured execution statement into uppercase or lowercase letters;

Grammatically proofreading the structured execution statement, including:

Error correction is performed on special characters and reserved words in the structured execution statement.
A device for generating a structured query statement, characterized in that the device includes:

The obtaining module is used to obtain the information to be queried and the preset structured query statement template;

A preprocessing module, configured to replace the dynamic information in the preset structured query statement template with the information to be queried, and generate a structured execution statement;

A generating module, configured to generate a globally unique ID according to the structured execution statement; and store the structured execution statement and the ID into a database relational table.
A computer device, comprising a memory, a processor, and a computer program stored on the memory and operable on the processor, characterized in that, when the processor executes the computer program, any one of claims 1 to 7 is realized. steps of the method described in the item.
A computer-readable storage medium, on which a computer program is stored, wherein, when the computer program is executed by a processor, the steps of the method according to any one of claims 1 to 7 are realized.