SQL (Structured Query Language) statement generation and model training method and device

Abstract

The invention discloses an SQL (Structured Query Language) statement generation and model training method and device. The method comprises the steps of obtaining historical data for generating SQL statements; the historical data comprises a plurality of historical data records, and the historical data records comprise source table structure data, target table structure data, index definitions and generated SQL statements; the source table structure data and the target table structure data comprise table names, corresponding Chinese instructions, fields in the tables and corresponding Chinese annotations; historical data are input into the selected corpus pre-training model for learning, and related model structures and model parameters of the corpus training model are adjusted according to learned source table structure data features, target table structure data features, index definition features and SQL statement generation rules; the SQL statement generation model is obtained after the preset learning times of iterative learning, and the to-be-processed data of the English and Chinese environment data set is input into the SQL statement generation model to obtain the consistent SQL, so that the actual business and actual environment requirements are met.

Description

Technical Field

[0001] The present invention relates to the field of data processing technology, and in particular to a method and device for generating SQL statements and training models. Background Art

[0002] In data warehouses, especially in the context of big data, there are many indicators that need to be calculated. The speed of indicator development largely determines the delivery of the entire project. The main work of data warehouses is indicator development. How to improve the development speed of indicators has become a difficult problem that the entire industry needs to solve. The common technology for developing indicators in data warehouses is to form a large wide table at the detailed level through data cleaning, processing, and multi-table association based on the original table, and then manually write SQL to generate indicators based on the large wide table.

[0003] With the development of deep learning technology, especially natural language processing (NLP) technology, the industry already has the ability to generate SQL for calculating indicators based on indicator definitions on the basis of large wide tables. The text-to-SQL technology is currently mainly used on data sets in English environments and has achieved good results. Summary of the invention

[0004] The text-to-SQL technology trained with data in an English environment is suitable for processing datasets in an English environment, but it is not suitable for datasets using Chinese or a combination of Chinese and English. For example, datasets in an English environment generally do not have a target table, and questions and table creation statements are all in English. However, for datasets involving Chinese, the actual application environment is not consistent with datasets in an English environment only. In actual business, a target table is required, and the table creation statement contains both English and Chinese. In the actual data environment, the table names involved, and their field names are in English and have corresponding Chinese annotations. The indicator calculation has a source table and a target table, and the indicator definition is also in Chinese. Directly using text-to-SQL in an English environment cannot meet the needs of generating database statements for datasets using Chinese or a combination of Chinese and English. How to automatically generate SQL statements for datasets using Chinese or a combination of Chinese and English is a technical problem that urgently needs to be solved.

[0005] In view of the above problems, the present invention is proposed to provide a method and device for SQL statement generation and model training that overcomes the above problems or at least partially solves the above problems.

[0006] An embodiment of the present invention provides a SQL statement generation model training method, comprising:

[0007] Acquire historical data for generating SQL statements; the historical data includes multiple historical data records, and the historical data records include source table structure data, target table structure data, indicator definition, and generated SQL statements; the source table structure data and the target table structure data include the table name and the corresponding Chinese description, the fields in the table and the corresponding Chinese comments;

[0008] Input historical data into the selected corpus pre-training model for learning. According to the learned source table structure data characteristics, target table structure data characteristics, indicator definition characteristics and SQL statement generation rules, adjust the relevant model structure and model parameters of the corpus training model; after iterative learning for a preset number of learning times, obtain the SQL statement generation model.

[0009] In some preferred embodiments, obtaining historical data of generating SQL statements includes:

[0010] The source table structure data, the target table structure data, the indicator definition, and the generated SQL statement used to generate the SQL statement are obtained, and are sorted into a historical data record to obtain historical data including multiple historical data records;

[0011] The data structure of historical data records is as follows: indicator definition | target table name-target table Chinese name: field 1-field 1 Chinese comment, field 2-field 2 Chinese comment, ... | source table name-source table Chinese name: field 1-field 1 Chinese comment, field 2-field 2 Chinese comment, ... | SQL.

[0012] In some preferred embodiments, historical data of generating SQL statements are obtained according to database types, and a SQL statement generation model is trained for each database type, including:

[0013] The corpus pre-training model is a multilingual training model that has learned basic language knowledge and translation rules between different languages.

[0014] An embodiment of the present invention provides a method for generating an SQL statement, comprising:

[0015] The source table structure data, target table structure data and indicator definition to be processed are input into the SQL statement generation model, and the corresponding SQL statement is output; the SQL statement generation model is trained using the SQL statement generation model training method as described above.

[0016] In some preferred embodiments, a method for generating a SQL statement further includes:

[0017] The generated SQL statement is submitted to the database for execution. Based on the execution result of the SQL statement by the database, it is determined whether the generated SQL statement has passed the verification. If so, the generated SQL statement is returned to the user. If not, the process returns to the step of inputting the source table information, target table information and indicator definition to be processed into the SQL statement generation model until the preset number of executions is reached, and the first generated SQL statement is returned to the user.

[0018] In some preferred embodiments, the source table structure data, target table structure data and indicator definition to be processed are input into the SQL statement generation model, and the corresponding SQL statement is output, including:

[0019] Get the indicator definition entered by the user through the input page, as well as the source table name and target table name selected by the user on the data table selection page;

[0020] Query the database according to the source table name and the target table name to obtain the source table structure data and the target table structure data;

[0021] The obtained source table structure data, target table structure data and indicator definition are input into the SQL statement generation model, and the corresponding SQL statement is output.

[0022] In some preferred embodiments, the obtained source table structure data, target table structure data and indicator definition are input into a SQL statement generation model, and the corresponding SQL statement is output, including:

[0023] After the source table structure data, target table structure data and indicator definition are input into the SQL statement generation model, the SQL statement generation model calls the SQL statement generation rules according to the preset calling rules, processes the source table structure data and target table structure data in combination with the development indicators in the indicator definition, and generates corresponding SQL statements.

[0024] In some preferred embodiments, a method for generating a SQL statement further includes:

[0025] According to the database type of the source table and target table to be processed, select the SQL statement generation model trained based on the historical data of the same database type.

[0026] An embodiment of the present invention provides a SQL statement generation model training device, comprising:

[0027] Data acquisition module: used to acquire historical data for generating SQL statements; the historical data includes multiple historical data records, and the historical data records include source table structure data, target table structure data, indicator definition and generated SQL statements; the source table structure data and target table structure data include the table name and the corresponding Chinese description, the field in the table and the corresponding Chinese annotation;

[0028] Model training module: used to input the historical data into the selected corpus pre-training model for learning, adjust the relevant model structure and model parameters of the corpus training model according to the learned source table structure data characteristics, target table structure data characteristics, indicator definition characteristics and SQL statement generation rules; after iterative learning for a preset number of learning times, obtain the SQL statement generation model.

[0029] An embodiment of the present invention provides a SQL statement generating device, comprising:

[0030] SQL statement generation module: used to input the source table structure data, target table structure data and indicator definition to be processed into the SQL statement generation model, and output the corresponding SQL statement;

[0031] The SQL statement generation model training device as described above.

[0032] In some preferred embodiments, the above device further comprises:

[0033] Verification module: used to submit the generated SQL statement to the database for execution, and determine whether the generated SQL statement has passed the verification based on the execution result of the SQL statement by the database; if so, return the generated SQL statement to the user; if not, return to continue the step of inputting the source table information, target table information and indicator definition to be processed into the SQL statement generation model until the preset number of executions is reached, and return the first generated SQL statement to the user.

[0034] An embodiment of the present invention provides a computer storage medium, in which computer executable instructions are stored. When the computer executable instructions are executed by a processor, the above-mentioned SQL statement generation model training method and / or the above-mentioned SQL statement generation method are implemented.

[0035] An embodiment of the present invention provides a data processing device, including: a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the program, a SQL statement generation model training method as described above and / or a SQL statement generation method as described above are implemented.

[0036] The beneficial effects of the above technical solution provided by the embodiment of the present invention include at least:

[0037] Acquire historical data for generating SQL statements; the historical data contains multiple historical data records, the historical data records include source table structure data, target table structure data, indicator definition and generated SQL statements; the source table structure data and target table structure data include table names and corresponding Chinese descriptions, fields in the table and corresponding Chinese comments; input the historical data into the selected corpus pre-training model for learning, and adjust the relevant model structure and model parameters of the corpus training model according to the learned source table structure data features, target table structure data features, indicator definition features and SQL statement generation rules; iterate learning and preset learning times Finally, an SQL statement generation model is obtained; the SQL statement generation model is trained based on historical data containing Chinese, and the model is adjusted to obtain a generation model suitable for data sets containing Chinese. It is applicable to usage environments containing Chinese, for example: table creation statements contain English and Chinese, environment table names and field names are English corresponding to Chinese comments, indicator calculations have source tables and target tables, and indicator definitions are in Chinese, etc., which meets the SQL statement generation requirements of most data sets using Chinese or a combination of Chinese and English. It can also process data sets containing Chinese and accurately generate corresponding SQL statements, improving the universal applicability of SQL statement generation technology.

[0038] The source table structure data, target table structure data and indicator definition to be processed are input into the SQL statement generation model, and the corresponding SQL statement is output; the generated SQL statement is submitted to the database for execution, and whether the generated SQL statement is verified is determined according to the execution result of the SQL statement by the database; if so, the generated SQL statement is returned to the user; if not, the step of inputting the source table information, target table information and indicator definition to be processed into the SQL statement generation model is returned to the user until the preset number of executions is reached, and the first generated SQL statement is returned to the user. This method can adapt to the SQL statement generation requirements of the data set containing Chinese, and at the same time, by verifying in the database, the accuracy of the generated SQL statement is further improved, meeting the actual needs of the accuracy of the generated SQL statement using the data set in Chinese or Chinese and English environments.

[0039] Other features and advantages of the present invention will be described in the following description, and partly become apparent from the description, or understood by practicing the present invention. The purpose and other advantages of the present invention can be realized and obtained by the structures particularly pointed out in the written description, claims, and drawings.

[0040] The technical solution of the present invention is further described in detail below through the accompanying drawings and embodiments. BRIEF DESCRIPTION OF THE DRAWINGS

[0041] The accompanying drawings are used to provide a further understanding of the present invention and constitute a part of the specification. Together with the embodiments of the present invention, they are used to explain the present invention and do not constitute a limitation of the present invention. In the accompanying drawings:

[0042] Figure 1 This is a flowchart of a SQL statement generation model training method in Embodiment 1 of the present invention;

[0043] Figure 2 This is a flow chart of a method for generating SQL statements in Embodiment 2 of the present invention;

[0044] Figure 3 This is a flowchart of a specific implementation of a method for generating an SQL statement in Embodiment 3 of the present invention;

[0045] Figure 4 It is a schematic diagram of the structure of a SQL statement generation model training device in an embodiment of the present invention;

[0046] Figure 5 The figure is a schematic diagram of the structure of a SQL statement generating device in an embodiment of the present invention. DETAILED DESCRIPTION

[0047] The exemplary embodiments of the present disclosure will be described in more detail below with reference to the accompanying drawings. Although the exemplary embodiments of the present disclosure are shown in the accompanying drawings, it should be understood that the present disclosure can be implemented in various forms and should not be limited by the embodiments set forth herein. On the contrary, these embodiments are provided to enable a more thorough understanding of the present disclosure and to fully convey the scope of the present disclosure to those skilled in the art.

[0048] In order to solve the problem that the existing text-to-SQL technology for data training in an English environment cannot adapt to the data processing requirements of a data set containing Chinese, an embodiment of the present invention provides a method and device for generating SQL statements and training a model. By using a certain amount of historical data to train the SQL statement generation model, the corresponding SQL can be automatically generated through the indicator definition. For the multiple SQL statements generated, the accuracy of the generated SQL can be improved by using database verification and other methods. This improves the indicator development speed and reduces labor costs.

[0049] Embodiment 1:

[0050] Embodiment 1 of the present invention provides a SQL statement generation model training method, the process of which is as follows: Figure 1 As shown, the following steps are included:

[0051] Step S101: Acquire historical data for generating SQL statements; wherein the historical data includes multiple historical data records, and the historical data records include source table structure data, target table structure data, indicator definition, and generated SQL statements; the source table structure data and the target table structure data include the table name and the corresponding Chinese description, the field in the table and the corresponding Chinese comment;

[0052] Optionally, the source table structure data, target table structure data, indicator definition, and generated SQL statement used to generate the SQL statement are obtained and organized into a historical data record to obtain historical data including multiple historical data records; wherein the data structure of the historical data record is as follows: indicator definition|target table name-target table Chinese name: field 1-field 1 Chinese comment, field 2-field 2 Chinese comment, …|source table name-source table Chinese name: field 1-field 1 Chinese comment, field 2-field 2 Chinese comment, …|SQL.

[0053] For example: Based on the historical data of other similar projects, organize the indicator definition, the corresponding SQL, the source table structure, and the target table structure into data. The indicator definition must be clear and the terminology must be standardized. The source table structure and the target table structure must contain the Chinese annotations of the table and field. The organized data structure is as follows: indicator definition | target table-target table Chinese name: field 1-field 1 Chinese annotation, field 2-field 2 Chinese annotation, ... | source table name-source table Chinese name: field 1-field 1 Chinese annotation, field 2-field 2 Chinese annotation, ... | SQL.

[0054] Optionally, the SQL of historical data and the predicted SQL are the SQL of the same database, such as Apache Spark SQL, and there is only one source table and one target table. In other words, historical data for generating SQL statements are obtained according to the database type, and the SQL statement generation model is trained for each database type; the corpus pre-training model as described above is a multi-language training model that has learned the basic knowledge of language and translation rules between different languages.

[0055] For example, the Google mT5 model has been trained with 750GB of web page data. It has learned the basics of language, including translation between different languages, but specific translations are not that good.

[0056] Step S102: Input historical data into the selected corpus pre-training model for learning, and adjust the relevant model structure and model parameters of the corpus training model according to the learned source table structure data characteristics, target table structure data characteristics, indicator definition characteristics and SQL statement generation rules; after iterative learning for a preset number of learning times, obtain the SQL statement generation model.

[0057] For example, historical data from similar projects is used to adjust the Google mT5 model. The adjusted Google mT5 model retains the generalization capability of the original model and learns the generation rules of indicator definition, source table structure, target table structure and SQL. The adjusted model is saved and called externally through services.

[0058] The adjustment process is to input historical data of similar projects including indicator definition, source table structure, target table structure, and SQL statements into the Google mT5 model. The Google mT5 model is trained based on these historical data. During the training process, the structure and parameters in the model are adjusted until the preset number of training times is reached, which can be 10 times, 20 times, or other times. After the adjustment of the Google mT5 model is completed, an SQL statement generation model is obtained. This SQL statement generation model is a model generated after learning information such as source table structure data characteristics, target table structure data characteristics, indicator definition characteristics, and SQL statement generation rules.

[0059] The above method trains the model based on historical data containing Chinese, and after learning the relevant features, adjusts the model to obtain SQL statement generation requirements that can adapt to data sets containing Chinese. In this method, the arrangement of historical data and current data to be processed is completed by the program, and the intermediate steps are also completed in series by the program. Based on the pre-trained large model, the file is generated to SQL (text-to-SQL), which improves the efficiency and accuracy of indicator development and greatly reduces the development workload.

[0060] Embodiment 2:

[0061] Embodiment 2 of the present invention provides a method for generating SQL statements, the process of which is as follows: Figure 2 As shown, the following steps are included:

[0062] Step S201: Acquire a SQL statement generation model, which is obtained through steps S101-102.

[0063] Step S202: input the source table structure data, target table structure data and indicator definition to be processed into the SQL statement generation model, and output the corresponding SQL statement.

[0064] In some optional embodiments, the above method further includes:

[0065] Step S203: Submit the generated SQL statement to the database for execution, and execute the SQL statement according to the database.

[0066] Step S204: Determine whether the generated SQL statement has passed verification;

[0067] If yes, execute step S205; if no, return to continue executing step S201.

[0068] Step S205: Return the generated SQL statement to the user.

[0069] If the SQL statement is verified successfully, the generated SQL statement is returned to the user; if the SQL statement is not verified successfully, the process returns to the step of inputting the source table information, target table information and indicator definition to be processed into the SQL statement generation model until the preset number of executions is reached, and the first generated SQL statement is returned to the user.

[0070] In the above step S202, the indicator definition entered by the user through the input page, as well as the source table name and target table name selected by the user on the data table selection page are obtained; the database is queried according to the source table name and the target table name to obtain the source table structure data and the target table structure data; the obtained source table structure data, target table structure data and indicator definition are input into the SQL statement generation model, and the corresponding SQL statement is output.

[0071] Among them: according to the database type of the source table and the target table to be processed, select the SQL statement generation model trained based on the historical data of the same database type. Optionally, after the source table structure data, the target table structure data and the indicator definition are input into the SQL statement generation model, the SQL statement generation model calls the SQL statement generation rule according to the preset calling rule, combines the development indicator in the indicator definition, processes the source table structure data and the target table structure data, and generates the corresponding SQL statement.

[0072] Specifically, the SQL of historical data and the predicted SQL are the SQL of the same database, for example, they are all SQL statements of ApacheSpark SQL, SQL Server, and MY SQL, and there is only one source table and target table; compared with historical data, the current data has no SQL field content, and everything else must be completely consistent with the historical data structure.

[0073] The Google mT5 model is adjusted using the sorted historical data structure to generate an adjusted Google mT5 model for model service calls.

[0074] Using the adjusted Google mT5 model, the output results are organized by the program into the data structure required for model prediction; the specific indicators to be generated are output according to the above data structure, which specifically includes indicator definition, source table structure, and target table structure; the indicator definition is input on the web page, the source table and target table names are selected by the developer, and the remaining data is obtained by the program by querying the database information according to the source table and target table names to obtain the source table and target table structures, and obtain the data to be processed; the processed data is input into the adjusted Google mT5 model according to the indicator definition, source table structure, and target table structure to generate the predicted SQL.

[0075] The indicator definition is entered on the web page. The source table and target table names are selected by the developer. The program queries the database information for the remaining data based on the source table and target table names to obtain the source table and target table structures. The program organizes the above data into the data structure required for model prediction to obtain the data to be input. The data to be input is input into the adjusted Google mT5 model according to the indicator definition, source table structure, and target table structure to generate the predicted SQL.

[0076] The model service is then called, SQL is generated and verified with the database, and the final correctness is determined by the developer.

[0077] The prediction SQL generated by the adjusted Google mT5 model is automatically submitted to the database for execution, and the verification result is returned. If it is executed correctly, the verification passes, otherwise the verification fails. If the first prediction SQL is verified, it is considered that the first prediction SQL is required to be predicted; if the first prediction SQL is verified, the total probability of the prediction SQL generated based on the subsequent output passing the verification is higher than the probability of the first prediction SQL being verified, then the adjusted Google mT5 model is called again to continue to generate prediction SQL, and if the prediction SQL is verified, the prediction SQL is continued to be generated as the prediction SQL required, until the preset number of times is executed. Here, the preset number of times can be set as needed, for example, set to 5 times, and other times can be preset according to actual conditions; if the SQL generated after the preset number of times fails to be verified, it is considered that the first prediction SQL is required to be predicted.

[0078] The SQL will need to be predicted and confirmed or modified by the developer. The results predicted by the deep learning model cannot be guaranteed to be completely correct and need to be confirmed or modified by the developer. The developer will ultimately ensure that the predicted SQL is correct.

[0079] The results predicted by the deep learning model cannot be guaranteed to be completely correct and need to be confirmed or modified by the developer. The developer ultimately ensures that the SQL required for calculation is correct. Specifically, the adjusted Google mT5 model generates a predicted SQL, and the predicted SQL is verified to obtain the required predicted SQL. The required predicted SQL is confirmed or modified by the developer to obtain the final SQL.

[0080] Embodiment three:

[0081] Embodiment 3 of the present invention provides a specific implementation process of a method for generating a SQL statement, and the process is as follows: Figure 3 As shown, the following steps are included:

[0082] According to the indicator definition, target table, source table, and SQL, the historical data is sorted to obtain the historical data for generating SQL statements; the Google mT5 model is adjusted using the historical data for generating SQL statements to obtain the adjusted Google mT5 model; the data to be processed is sorted according to the indicator definition, target table, source table, and SQL, and the historical data is input into the adjusted Google mT5 model to obtain the corresponding SQL; the corresponding SQL is verified using a database to obtain the predicted SQL; the predicted SQL is confirmed or modified by the developer to obtain the final SQL.

[0083] Step S301: Arrange historical data by indicator definition, target table, source table, and SQL structure.

[0084] The data includes all fields and Chinese comments of the source and target tables, so that the model can learn the correspondence between Chinese descriptions and English table names and field names.

[0085] For example, the historical data structure of the sorting, indicator definition | target table, target table Chinese comments, field, field Chinese comments | source table, source table Chinese comments, field, field Chinese comments | SQL example is as follows:

[0086]

[0087] Step S302: Using the collated historical data, adjust the Google mT5 model.

[0088] The Google mT5 model in steps S101-102 is adjusted by using the collated historical data to obtain a SQL statement generation model.

[0089] Step S303: The data to be processed is input into the adjusted Google mT5 model according to the indicator definition, source table, and target table structure to generate prediction SQL.

[0090] For example, after a developer enters the indicator definition on a web page, selects the source table and target table names, and generates a data structure using database metadata, the following example is shown:

[0091]

[0092] Then call the model service, input the above data into the SQL statement generation model, and output the predicted SQL.

[0093] Step S304: Submit the predicted SQL to the database for verification.

[0094] The predicted SQL is submitted to the database for verification, and the output predicted SQL is verified to obtain the required predicted SQL; verification by the database reduces the problems caused by the randomness of deep learning generation; up to N optional predicted SQLs are verified, which greatly improves the accuracy compared to verifying only 1 predicted SQL, and ensures the real-time generation, thereby improving the accuracy of the predicted SQL.

[0095] Step S305: Manual modification or confirmation.

[0096] The final correctness is determined by the developer. The developer will modify or confirm the SQL that needs to be predicted to obtain the final SQL.

[0097] By using deep learning technology, especially pre-trained large models, and adjusting with a certain amount of historical data, the corresponding SQL can be automatically generated through indicator definition. For multiple generated SQL statements, database verification and other methods are used to improve the accuracy of generated SQL. This will increase the speed of indicator development and reduce labor costs.

[0098] Based on the same inventive concept, an embodiment of the present invention provides a SQL statement generation model training device, the structure of which is as follows: Figure 4 As shown, it includes: a data acquisition module 111 and a model training module 112;

[0099] Data acquisition module 111: used to acquire historical data for generating SQL statements; the historical data includes multiple historical data records, which include source table structure data, target table structure data, indicator definition and generated SQL statements; the source table structure data and target table structure data include table names and corresponding Chinese descriptions, fields in the table and corresponding Chinese comments;

[0100] Model training module 112: used to input the historical data into the selected corpus pre-training model for learning, and adjust the relevant model structure and model parameters of the corpus training model according to the learned source table structure data characteristics, target table structure data characteristics, indicator definition characteristics and SQL statement generation rules; after iterative learning for a preset number of times, an SQL statement generation model is obtained.

[0101] Based on the same inventive concept, an embodiment of the present invention provides a device for generating SQL statements. The structure of the device is as follows: Figure 5 As shown, it includes: a SQL statement generation module 211 and the above-mentioned SQL statement generation model training device;

[0102] SQL statement generation module 211: used to input the source table structure data, target table structure data and indicator definition to be processed into the SQL statement generation model, and output the corresponding SQL statement.

[0103] Optionally, the above device further comprises:

[0104] Verification module 212: used to submit the generated SQL statement to the database for execution, and determine whether the generated SQL statement has passed the verification based on the execution result of the SQL statement by the database; if so, return the generated SQL statement to the user; if not, return to continue the step of inputting the source table information, target table information and indicator definition to be processed into the SQL statement generation model until the preset number of executions is reached, and the first generated SQL statement will be returned to the user.

[0105] Based on the same inventive concept, an embodiment of the present invention provides a computer storage medium, in which computer executable instructions are stored. When the computer executable instructions are executed by a processor, a SQL statement generation model training method as described above and / or a SQL statement generation method as described above are implemented.

[0106] Based on the same inventive concept, an embodiment of the present invention provides a data processing device, including: a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes a SQL statement generation model training method as described above and / or a SQL statement generation method as described above.

[0107] Regarding the device in the above embodiment, the specific manner in which each module performs operations has been described in detail in the embodiment of the method, and will not be elaborated here.

[0108] Unless otherwise specifically stated, terms such as processing, computing, calculating, determining, displaying, etc. may refer to the actions and / or processes of one or more processing or computing systems, or similar devices, which operate and convert data represented as physical (e.g., electronic) quantities within registers or memories of a processing system into other data similarly represented as physical quantities within memories, registers, or other such information storage, transmission, or display devices of the processing system. Information and signals may be represented using any of a variety of different techniques and methods. For example, data, instructions, commands, information, signals, bits, symbols, and chips mentioned throughout the above description may be represented by voltages, currents, electromagnetic waves, magnetic fields or particles, light fields or particles, or any combination thereof.

[0109] It should be understood that the specific order or hierarchy of steps in the disclosed process is an example of an exemplary method. Based on design preferences, it should be understood that the specific order or hierarchy of steps in the process can be rearranged without departing from the scope of protection of the present disclosure. The attached method claims present the elements of the various steps in an exemplary order and are not intended to be limited to the specific order or hierarchy described.

[0110] In the above detailed description, various features are grouped together in a single embodiment to simplify the disclosure. This method of disclosure should not be interpreted as reflecting an intention that the embodiments of the claimed subject matter require more features than are clearly stated in each claim. On the contrary, as reflected in the appended claims, the invention is in a state of having less than all the features of the disclosed individual embodiments. Therefore, the appended claims are hereby expressly incorporated into the detailed description, with each claim standing on its own as a separate preferred embodiment of the invention.

[0111] Those skilled in the art will also appreciate that the various illustrative logic blocks, modules, circuits, and algorithmic steps described in conjunction with the embodiments herein can all be implemented as electronic hardware, computer software, or a combination thereof. In order to clearly illustrate the interchangeability between hardware and software, various illustrative components, blocks, modules, circuits, and steps are generally described above around their functions. Whether such functions are implemented as hardware or software depends on specific applications and the design constraints imposed on the entire system. A skilled person can implement the described functions in an alternative manner for each specific application, but such implementation decisions should not be interpreted as departing from the scope of protection of the present disclosure.

[0112] The steps of the method or algorithm described in conjunction with the embodiments herein may be directly embodied as hardware, a software module executed by a processor, or a combination thereof. The software module may be located in a RAM memory, a flash memory, a ROM memory, an EPROM memory, an EEPROM memory, a register, a hard disk, a mobile disk, a CD-ROM, or any other form of storage medium well known in the art. An exemplary storage medium is connected to the processor so that the processor can read information from the storage medium and can write information to the storage medium. Of course, the storage medium may also be an integral part of the processor. The processor and the storage medium may be located in an ASIC. The ASIC may be located in a user terminal. Of course, the processor and the storage medium may also be present in a user terminal as discrete components.

[0113] For software implementation, the techniques described in this application can be implemented with modules (e.g., procedures, functions, etc.) that perform the functions described in this application. These software codes can be stored in a memory unit and executed by a processor. The memory unit can be implemented within the processor or outside the processor. In the latter case, it is coupled to the processor in a communication manner via various means, which are well known in the art.

[0114] The above description includes examples of one or more embodiments. Of course, it is impossible to describe all possible combinations of components or methods for the purpose of describing the above embodiments, but it should be recognized by those skilled in the art that the various embodiments may be further combined and arranged. Therefore, the embodiments described herein are intended to cover all such changes, modifications and variations that fall within the scope of protection of the appended claims. In addition, with respect to the term "comprising" used in the specification or claims, the word is covered in a manner similar to the term "including", just as "including," is explained as a transitional word in the claims. In addition, any term "or" used in the specification of the claims is intended to mean "non-exclusive or".

Claims

1. A SQL statement generation model training method, characterized in that: include: Acquire historical data for generating SQL statements; the historical data includes multiple historical data records, and the historical data records include source table structure data, target table structure data, indicator definition, and generated SQL statements; the source table structure data and target table structure data include table names and corresponding Chinese descriptions, and fields in the table and corresponding Chinese comments; The historical data is input into the selected corpus pre-training model for learning, and the relevant model structure and model parameters of the corpus training model are adjusted according to the learned source table structure data characteristics, target table structure data characteristics, indicator definition characteristics and SQL statement generation rules; after iterative learning for a preset number of learning times, an SQL statement generation model is obtained.

2. The method according to claim 1, characterized in that The obtaining of historical data for generating SQL statements includes: The source table structure data, the target table structure data, the indicator definition, and the generated SQL statement used to generate the SQL statement are obtained, and are sorted into a historical data record to obtain historical data including multiple historical data records; The data structure of the historical data record is as follows: indicator definition|target table name-target table Chinese name: field 1-field 1 Chinese comment, field 2-field 2 Chinese comment, ...|source table name-source table Chinese name: field 1-field 1 Chinese comment, field 2-field 2 Chinese comment, ...|SQL.

3. The method according to claim 1, characterized in that According to the database type, the historical data of generated SQL statements are obtained respectively, and the SQL statement generation model is trained for each database type; The corpus pre-training model is a multilingual training model that has learned basic language knowledge and translation rules between different languages.

4. A method for generating SQL statements, characterized in that: include: Input the source table structure data, target table structure data and indicator definition to be processed into the SQL statement generation model, and output the corresponding SQL statement; The SQL statement generation model is trained using the SQL statement generation model training method according to any one of claims 1 to 4.

5. The method according to claim 4, characterized in that Also includes: Submit the generated SQL statement to the database for execution, and determine whether the generated SQL statement has passed verification based on the execution result of the SQL statement by the database; If yes, the generated SQL statement is returned to the user; if no, the process returns to the step of inputting the source table information, target table information and indicator definition to be processed into the SQL statement generation model until the preset number of executions is reached, and the first generated SQL statement is returned to the user.

6. The method according to claim 4, characterized in that The source table structure data, target table structure data and indicator definition to be processed are input into the SQL statement generation model, and the corresponding SQL statement is output, including: Get the indicator definition entered by the user through the input page, as well as the source table name and target table name selected by the user on the data table selection page; Query the database according to the source table name and the target table name to obtain source table structure data and target table structure data; The obtained source table structure data, target table structure data and indicator definition are input into the SQL statement generation model, and the corresponding SQL statement is output.

7. The method according to claim 4, characterized in that Input the obtained source table structure data, target table structure data and indicator definition into the SQL statement generation model, and output the corresponding SQL statement, including: After the source table structure data, target table structure data and indicator definition are input into the SQL statement generation model, the SQL statement generation model calls the SQL statement generation rules according to the preset calling rules, processes the source table structure data and target table structure data in combination with the development indicators in the indicator definition, and generates corresponding SQL statements.

8. The method according to any one of claims 4 to 7, characterized in that: Also includes: According to the database type of the source table and target table to be processed, select the SQL statement generation model trained based on the historical data of the same database type.

9. A SQL statement generation model training device, characterized in that: include: Data acquisition module: used to acquire historical data for generating SQL statements; the historical data includes multiple historical data records, and the historical data records include source table structure data, target table structure data, indicator definition and generated SQL statements; the source table structure data and target table structure data include the table name and the corresponding Chinese description, the field in the table and the corresponding Chinese annotation; Model training module: used to input the historical data into the selected corpus pre-training model for learning, adjust the relevant model structure and model parameters of the corpus training model according to the learned source table structure data characteristics, target table structure data characteristics, indicator definition characteristics and SQL statement generation rules; after iterative learning for a preset number of learning times, obtain the SQL statement generation model.

10. A SQL statement generating device, characterized in that: include: SQL statement generation module: used to input the source table structure data, target table structure data and indicator definition to be processed into the SQL statement generation model, and output the corresponding SQL statement; The SQL statement generation model training device as described in claim 9.

11. The device according to claim 10, characterized in that Also includes: Verification module: used to submit the generated SQL statement to the database for execution, and determine whether the generated SQL statement has passed the verification based on the execution result of the SQL statement by the database; if so, return the generated SQL statement to the user; if not, return to continue the step of inputting the source table information, target table information and indicator definition to be processed into the SQL statement generation model until the preset number of executions is reached, and return the first generated SQL statement to the user.

12. A computer storage medium, characterized in that: The computer storage medium stores computer executable instructions, which, when executed by a processor, implement a SQL statement generation model training method described in any one of claims 1-3 and / or a SQL statement generation method described in any one of claims 4-8.

13. A data processing device, characterized in that: include: A memory, a processor, and a computer program stored in the memory and executable on the processor, wherein when the processor executes the program, the method for training a model for generating a SQL statement as described in any one of claims 1 to 3 and / or the method for generating a SQL statement as described in any one of claims 4 to 8 are implemented.

Images