A method, device, equipment and medium for generating SQL based on large language model

By introducing industry dictionaries and similar problem methods, the method of generating SQL in large language models is optimized, complex query comprehension problems and high training costs are solved, high accuracy and adaptability are achieved, and implementation and maintenance processes are simplified.

CN118820277BActive Publication Date: 2024-12-06BEIJING BAOLANDE SOFTWARE CORP
View PDF 10 Cites 0 Cited by

Patent Information

Application Number
CN202411104987.1
Authority / Receiving Office
CN · China
Patent Type
Patents(China)
Current Assignee / Owner
Filing Date
2024-08-13
Publication Date
2024-12-06
Estimated Expiration
2044-08-13

AI Technical Summary

Technical Problem

The prior art is difficult to accurately understand user intentions when dealing with complex and diverse natural language queries, and methods based on large language models require a large amount of training data and computing resources, resulting in high costs and long deployment time.

Method used

By introducing industry dictionaries and similar problem methods, we optimize the method of generating SQL in large language models, including data source configuration, table governance, domain knowledge sorting, problem SQL governance, user problem reception and abbreviation propt assembly, using large model table selection and complete propt Assemble and generate SQL, improve the accuracy and generalization of transformations and reduce training and maintenance costs.

Benefits of technology

Significantly improves the accuracy of conversion from natural language to SQL statements, ensures the accuracy of complex and rare queries, enhances the adaptability and robustness of the system, simplifies the implementation process and reduces maintenance complexity.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN118820277B_ABST
    Figure CN118820277B_ABST
Patent Text Reader

Abstract

The present invention relates to the field of computer software and database management, and specifically to a method, device, equipment and medium for generating SQL based on a large language model, the method comprising: data source configuration; table management; table relationship management; field knowledge combing; problem SQL management; user problem reception; abbreviated prompt assembly; large model table selection call; complete prompt assembly; large model call to generate SQL. The present invention significantly improves the accuracy of the conversion from natural language to SQL statements by introducing methods such as industry dictionaries and similar questions, and can ensure that the generated SQL statements are accurate even in the face of complex and rare query requirements; and can handle a wide range of language patterns and database structures without special training, greatly enhancing the adaptability and robustness of the system; at the same time, through clever design, the model can be directly applied to different database environments without additional training, greatly simplifying the implementation process and reducing the complexity of maintenance and updates.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present invention relates to the fields of computer software and database management, and in particular to a method, device, equipment and medium for generating SQL based on a large language model. Background Art

[0002] At present, there are mainly the following technical solutions for converting natural language into SQL statements:

[0003] Rule-based approach: This approach maps natural language to SQL statements through predefined grammars and vocabularies. However, this approach performs poorly when processing complex queries because it is difficult for the rules to cover all possible semantic variations.

[0004] Machine learning-based methods: In recent years, deep learning technology has made significant progress in the field of natural language processing, especially the neural network-based sequence-to-sequence (Seq2Seq) model, which can convert natural language input into SQL output. However, existing models often require a large amount of training data and are not effective in processing long-tail queries.

[0005] Template matching-based method: This solution generates query statements by matching pre-set SQL templates with natural language instructions. However, this method lacks flexibility and has poor adaptability to unforeseen query types.

[0006] Generate SQL based on a large language model: The SQL generation method based on a large language model uses advanced natural language understanding and generation technology. However, due to the limitation of the token data of the large model, it is impossible to input the entire database into the large model.

[0007] Generate SQL based on large language model fine-tuning: Use large language model fine-tuning technology (SFT) to fine-tune common SQL and user natural language input into the large language model. The main problems are high training costs and insufficient model generalization capabilities.

[0008] In summary, whether it is a rule-based, machine learning or template matching method, when processing complex and diverse natural language queries, errors often occur due to the inability to accurately understand user intent, and traditional machine learning-based models often have reduced performance when encountering unseen data, and existing methods based on large language models usually require a large amount of additional training data and computing resources to fine-tune the model, which not only increases costs but also prolongs deployment time. Based on this, the inventor has developed a method, device, equipment and medium for generating SQL based on a large language model. Summary of the invention

[0009] In order to solve the above technical problems, the present invention provides a method, device, equipment and medium for generating SQL based on a large language model. The present invention significantly improves the accuracy of conversion from natural language to SQL statements by introducing methods such as industry dictionaries and similar questions, so that even in the face of complex and rare query requirements, it can ensure that the generated SQL statements are accurate; and it also improves its generalization ability, and at the same time enables the large model to be directly applied to different database environments without additional training, greatly simplifying its implementation process and reducing the complexity of its maintenance and update.

[0010] In a first aspect, the present invention provides a method for generating SQL based on a large language model, which adopts the following technical solution:

[0011] A method for generating SQL based on a large language model comprises the following steps:

[0012] Data source configuration, configure database connection information;

[0013] Table management: Based on data source configuration, obtain all tables in the data source and DDL information in table creation statements, query the data in the table, and convert the original DDL information into two new DDL descriptions: abbreviated DDL information and full DDL information according to preset rules;

[0014] Table relationship management: sort out the relationship between tables in the database and build a table relationship diagram;

[0015] Domain knowledge sorting: sort out domain knowledge in a specific field and establish a domain knowledge base;

[0016] Problem SQL management: correct the problem SQL generated by the large model and generate the corrected SQL, establish an OA question-and-answer system, and store the problem SQL and the corrected SQL in the vector database;

[0017] User question receiving, receiving the natural language query question input by the user, recorded as the initial question;

[0018] Abbreviated prompt assembly: segment the initial question through the word segmentation algorithm and obtain the word segmentation result, match the word segmentation result with the domain knowledge in the domain knowledge base, and replace the keywords in the initial question with the matched domain knowledge to form a replacement question;

[0019] Vectorize the replacement problem to generate a vectorized problem, use the Euclidean distance to calculate the vector similarity between the vectorized problem and the problem SQL, and find the top three problem SQLs with the highest similarity, which are recorded as the top three problem SQLs;

[0020] Generate an abbreviated prompt based on the replacement question, the first three questions SQL, and the abbreviated DDL information;

[0021] The large model selects the table and calls it. The abbreviated prompt is input into the large language model, and the most relevant table for the user's query is selected from the database and recorded as the most relevant table.

[0022] Complete prompt assembly, based on the most relevant table and table relationship diagram, find the table information and table relationship information of the most relevant table in the table relationship diagram;

[0023] Find the complete DDL description information in the complete DDL version based on the table information and the associated table of the table information;

[0024] Assemble a complete prompt using the complete DDL description information, table relationship information, replacement questions, and the first three questions SQL;

[0025] The big model calls to generate SQL, inputs the complete prompt into the big language model, and the big model generates SQL.

[0026] Preferably, the database connection information includes a database URL, a user name and a password.

[0027] Preferably, the table relationship diagram is in JSON format or XML format.

[0028] Preferably, the domain knowledge includes professional terms and synonyms of professional terms.

[0029] Preferably, the abbreviated prompt includes color calibration definition, database table information and user questions.

[0030] In a second aspect, the present invention provides a device for generating SQL based on a large language model, which adopts the following technical solution:

[0031] A device for generating SQL based on a large language model, comprising:

[0032] Data source configuration module, used to configure database connection information;

[0033] The table management module is used to obtain all tables in the data source and the DDL information in the table creation statement based on the data source configuration, query the data in the table, and convert the original DDL information into two new DDL descriptions: abbreviated DDL information and full DDL information according to preset rules;

[0034] Table relationship management module, used to sort out the relationship between tables in the database and build a table relationship diagram;

[0035] The domain knowledge combing module is used to comb the domain knowledge in a specific field and establish a domain knowledge base;

[0036] The problem SQL management module is used to correct the problem SQL generated by the large model and generate the correction SQL, establish the OA question-answering system, and store the problem SQL and the correction SQL in the vector database;

[0037] A user question receiving module, used to receive a natural language query question input by a user, recorded as an initial question;

[0038] The prompt assembly module is used to segment the initial question through the word segmentation algorithm and obtain the word segmentation result, match the word segmentation result with the domain knowledge in the domain knowledge base, and replace the keywords in the initial question with the matched domain knowledge to form a replacement question;

[0039] Vectorize the replacement problem to generate a vectorized problem, use the Euclidean distance to calculate the vector similarity between the vectorized problem and the problem SQL, and find the top three problem SQLs with the highest similarity, which are recorded as the top three problem SQLs;

[0040] Generate an abbreviated prompt based on the replacement question, the first three questions SQL, and the abbreviated DDL information;

[0041] The large model table selection calling module is used to input the abbreviated prompt into the large language model, filter out the most relevant table for the user's query from the database, and record it as the most relevant table;

[0042] Complete prompt assembly module, used to find the table information and table relationship information of the most relevant table in the table relationship diagram based on the most relevant table and table relationship diagram;

[0043] Find the complete DDL description information in the complete DDL version based on the table information and the associated table of the table information;

[0044] Assemble a complete prompt using the complete DDL description information, table relationship information, replacement questions, and the first three questions SQL;

[0045] The large model calls the SQL generation module, which is used to input the complete prompt into the large language model, and the large model generates SQL.

[0046] In a third aspect, the present invention provides an electronic device, which adopts the following technical solution:

[0047] An electronic device comprising:

[0048] One or more processors;

[0049] Memory;

[0050] One or more applications, wherein the one or more applications are stored in the memory and configured to be executed by the one or more processors, and the one or more programs are configured to: execute a method for generating SQL based on a large language model according to any one of the above items.

[0051] In a fourth aspect, the present invention provides a computer-readable storage medium, which adopts the following technical solution:

[0052] A computer-readable storage medium stores a computer program, which, when executed by a processor, implements any of the above methods for generating SQL based on a large language model.

[0053] In summary, the present invention includes the following beneficial technical effects:

[0054] 1. The present invention significantly improves the accuracy of the conversion from natural language to SQL statements by introducing methods such as industry dictionaries and similar questions. At the same time, it can ensure that the generated SQL statements are accurate even when faced with complex and rare query requirements.

[0055] 2. The present invention solves the problem that the DDL language of multiple data tables is too long and cannot be input into a large language model by innovating prompt templates and data table management methods, so that it can handle a wide range of language patterns and database structures even without special training, greatly enhancing the adaptability and robustness of the system.

[0056] 3. The present invention solves the problem that the DDL language of multiple data tables is too long and cannot be input into a large language model through innovative prompt templates and data table management methods. Through clever design, the large language model can be directly applied to different database environments without additional training, which greatly simplifies the implementation process and reduces the complexity of maintenance and updating. BRIEF DESCRIPTION OF THE DRAWINGS

[0057] Figure 1 is a flowchart of the steps of the method in an embodiment of the present invention. DETAILED DESCRIPTION

[0058] The present invention is further described in detail below in conjunction with the accompanying drawings.

[0059] The embodiment of the present invention discloses a method for generating SQL based on a large language model.

[0060] Reference Figure 1 , the method for generating SQL based on a large language model comprises the following steps:

[0061] S101, data source configuration;

[0062] Specifically, configure database connection information, where the database connection information includes a database URL, user name, and password to ensure that the system can successfully connect to the target database.

[0063] S102, table governance;

[0064] Based on the data source configuration in step S101, all tables in the data source and DDL information in the table creation statement are obtained, and the data in the table is queried and the original DDL information is converted into two new DDL descriptions, abbreviated DDL information and full DDL information, according to preset rules; wherein the preset rules can be manually pre-set. Specifically, refer to the following example:

[0065] Original DDL example:

[0066] CREATE TABLE `sx_bk_cjsj`(

[0067] `XH`varchar(32)NOT NULL COMMENT'Student Number',

[0068] `JXB_ID`varchar(32)DEFAULT NULL COMMENT'class number',

[0069] `JXB`varchar(100)DEFAULT NULL COMMENT'Teaching Class',

[0070] `XN`varchar(32)DEFAULT NULL COMMENT'school year',

[0071] `XQ`varchar(32)DEFAULT NULL COMMENT'semester',

[0072] `KCMC`varchar(100)DEFAULT NULL COMMENT'Course Name',

[0073] `KCXZ`varchar(100)DEFAULT NULL COMMENT'Course Nature',

[0074] `KHFS`varchar(100)DEFAULT NULL COMMENT'Assessment method',

[0075] `ZPCJ` decimal(10,2) DEFAULT NULL COMMENT 'Overall score',

[0076] `JD` decimal(5,2) DEFAULT NULL COMMENT 'Grade points',

[0077] `XF` decimal(5,2) DEFAULT NULL COMMENT 'credits',

[0078] `KCDM` varchar(100) DEFAULT NULL COMMENT 'Course code'

[0079] )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

[0080] COLLATE = utf8mb4_0900_ai_ci COMMENT = 'Undergraduate performance data';

[0081] Example of converted abbreviated DDL information:

[0082] Table name: Student performance information Table column description: ['student number', 'class number', 'class', 'school year', 'semester', 'course name', 'course nature', 'assessment method', 'overall score', 'grade point', 'credits', 'course code'];

[0083] Example of complete DDL information after conversion:

[0084] table:sx_bk_cjsj

[0085] table_comment: Student performance information table

[0086] columns=[

[0087] XH(varchar|comment:student number|values:for example, 202200101, 202200122, etc.)

[0088] JXB_ID (varchar|comment:class number|values:for example, rj_001, rj_002, etc.)

[0089] JXB (varchar|comment:teaching class|values:for example, software class 001, software class 002, etc.)

[0090] XN(varchar|comment:school year|values:enumeration values ​​included,2021-2022)

[0091] XQ(varchar|comment:semester|values:enumeration values ​​included,1)

[0092] KCMC (varchar|comment:course name|values:for example, basic comprehensive examination, systemic anatomy A(Ⅰ), biochemistry D, introduction to Chinese medicine B, biochemistry B, medical organic chemistry, etc.)

[0093] KCXZ (varchar|comment: nature of course|values: enumeration values ​​include, extracurricular practice, major compulsory courses, major limited elective courses, general compulsory courses, professional compulsory courses)

[0094] KHFS(varchar|comment:assessment method|values:)

[0095] ZPCJ(decimal(10,2)|comment: overall score|values: for example, 60.00, 62.00, 66.00, 70.00, 64.00, 63.00, etc.)

[0096] JD(decimal(5,2)|comment:GPA|values:e.g., 1.00, 0.00, 1.20, 1.60, 2.00, 1.30, etc.)

[0097] XF(decimal(5,2)|comment:credits|values:for example,0.50,4.00,3.00,5.00,4.50,2.00,etc.)

[0098] KCDM(varchar|comment:course code|values:

[0099] Such as, RJKC01, JZKC02, GSKC03, etc.)];

[0100] S103, table relationship governance;

[0101] Use both program and manual methods to sort out the relationship between tables in the database, build a table relationship diagram, and organize it into a format that is easier to query, such as JSON format or XML format, so that the table relationship diagram can be easily applied in the subsequent SQL generation process.

[0102] The following is an example of a table relationship:

[0103]

[0104] S104, domain knowledge sorting;

[0105] Sort out the domain knowledge of a specific field and establish a domain knowledge base, where a specific field is a relatively broad or specific field, such as the communications field, and the domain knowledge includes the professional terms in the field and the synonyms of the professional terms, etc. For example, in the communications industry, "comprehensive service guarantee system" may have multiple abbreviations, such as "comprehensive guarantee", "service guarantee", "service guarantee system", etc. These abbreviations will be used to correctly resolve user questions in subsequent steps.

[0106] S105, Problem SQL Management,

[0107] The problem SQL generated for the large model is corrected, and the corrected SQL is generated, an OA question-and-answer system is established, and both the problem SQL and the corrected SQL are stored in a vector database, so that when the same problem SQL is generated subsequently, the erroneous problem SQL can be quickly identified and similar errors of the problem SQL can be corrected.

[0108] S106, receiving user questions;

[0109] Receive a natural language query question input by a user and record it as an initial question;

[0110] S107, abbreviated prompt assembly;

[0111] Based on the initial question received in step S106, the initial question is segmented by a segmentation algorithm to obtain a segmentation result; wherein the segmentation algorithm may be an ieba segmentation algorithm, a HanLP segmentation algorithm, a Hulac segmentation algorithm, or a similar segmentation algorithm;

[0112] The word segmentation result is fully matched with the domain knowledge in the domain knowledge base in step S104, and the matched domain knowledge is used to replace the keywords in the initial question, thereby forming a replacement question;

[0113] The replacement question is vectorized by using a vector model to generate a vectorized question, and the vector similarity between the vectorized question and the question SQL is calculated using the Euclidean distance, and the top three question SQLs with the highest similarity are found and recorded as the top three question SQLs; wherein the vector model can be a vector model such as word2Vce, m3e-base, tao-8k, etc.

[0114] Generate an abbreviated prompt based on the replacement question and its similar questions or synonym questions, the SQL of the first three questions, and the abbreviated DDL information;

[0115] The abbreviated prompt includes three parts: color calibration definition, database table information and user questions.

[0116] An example of a shorthand prompt is as follows:

[0117] S108, large model selection table call;

[0118] Based on the database table information and user questions, the tables that the user questions may involve are given and returned in the form of JSON data:

[0119] #Table information in the database:

[0120] Table name: Table name: Student academic information Table column description: ['Enrollment time', 'School system', 'College', 'Major', 'Grade', 'Class', 'Academic status', 'Student category', 'Campus', 'Whether day student', 'Candidate category', 'Training level', 'Semester code', 'Semester name', 'Student number', 'Name', 'Training method', 'Student tag']

[0121] Table name: Student course schedule information Table column description: ['school year', 'semester', 'student number', 'elective course number', 'course name', 'course code', 'class name', 'section', 'day of the week', 'week number', 'odd and even weeks', 'starting week', 'ending week', 'section number', 'venue name', 'class length']

[0122] Table name: Student class information Table column description: ['class number', 'class establishment year', 'grade', 'class teacher's work number', 'class monitor's student number', 'counselor's number', 'class name', 'class abbreviation', 'class teacher's name', 'class monitor's name', 'counselor's name', 'unit number', 'unit name', 'number of students in school', 'major number', 'major name', 'campus']

[0123] Table name: Student performance information Table column description: ['Student ID', 'Class ID', 'Class', 'Academic Year', 'Semester', 'Course Name', 'Course Nature', 'Assessment Method', 'Overall Score', 'GPA', 'Credits', 'Course Code']

[0124] #Question: Did Li Dan pass the English test?

[0125] #Involved tables:

[0126] S108, large model selection table call;

[0127] The large model is a large language model. The large language model can be a large language model with more than 700 million parameters, and there are no other restrictions.

[0128] Input the abbreviated prompt into the big language model, and use the semantic understanding ability of the big language model to filter out the most relevant tables for the user's query from the database and record them as the most relevant tables;

[0129] The following is an example of the output of a large model:

[0130] ["Student performance information table","Student basic information table"]

[0131] S109, complete prompt assembly;

[0132] Based on the most relevant table and the table relationship diagram, find the table information and table relationship information of the most relevant table in the table relationship diagram;

[0133] Then, according to the table information and the associated table of the table information, find the complete DDL description information in the complete version of DDL;

[0134] Then use the complete DDL description information, table relationship information, replacement questions, and the first three questions SQL to assemble a complete prompt;

[0135] The following is an example of a SQL generation prompt:

[0136] The following table structure and table association relationship description, write SQL according to the user's question, SQL format is markdown, do not add ";" after the generated SQL

[0137] #Table Description

[0138] {'BKSJCSJ':'table: sx_bk_xssj\ntable_comment: basic student information table\ncolumns = [\nXH(varchar|comment: student number|values: for example, 2017S1101011711001, 2017S1101011711002, 2017S1101011711003, 2017S1101011711004, 2017S1101011711005, 2017S1101021711007, etc.)\nXM(varchar|comment: name|values: for example, Zhang San, Li Si, Wang Wu, etc.)\nXMPY(varchar|comment: name pinyin |values:)\nCYM(varchar|comment:Former name|values:)\nXB(varchar|comment:Gender|values:Enumeration values ​​include, Female, Male)\nCSRQ(varchar|comment:Date of birth|values:For example, 1995-01-15, 1993-05-05, 1994-03-26, 1994-01-05, 1994-08-11, 1994-10-28, etc.)\nJG(varchar|comment:Native place|values:Enumeration values ​​include, Zhejiang Province)\nMZ\nSFZJLX(varchar|comment:ID document type|values:)\nSFZJHM

[0139] values:)\nSFZJHM(varchar|comment:ID number|values:)\nSYD(varchar|comment:Place of origin|values:)\nXHID(varchar|comment:Student ID|values:For example, 201711010111001, 201711010111002, 201711010111003, 201711010111004, 201711010111005, 201711010211007, etc.)\nCSD(varchar|comment:Place of birth|values:)\n lues:)\nHKXZ(varchar|comment:household registration|values:)\nXQ(varchar|comment:campus|values:)\nDWMC(varchar|comment:unit name|values:for example, XX medical college, XX university, etc.)\nSZZY(varchar|comment:major|values:for example, clinical integration of Chinese and Western medicine (research), Chinese medicine internal medicine (research), acupuncture and massage (research), Chinese medicine (research), Chinese medicine orthopedics (research), etc.)\nZYFX(varchar|comment:professional direction|

[0140] values:)\nSZNJ(varchar|comment:grade|values:enumeration values ​​include,2017,2020)\nSZBJ(varchar|comment:class|values:for example,2017 Clinical Research of Integrated Chinese and Western Medicine,2017 Acupuncture and Massage,2017 Internal Medicine of Traditional Chinese Medicine,2017 Traumatology of Traditional Chinese Medicine,2017 Traditional Chinese Medicine,2017 Chinese Medicine,2017 Chinese Pharmacy,etc.)\nZZMMJRSJ(varchar|comment:political status,joining time|values:)\nSFZX

[0141] (varchar|comment: whether in school|values: enumeration values ​​include, no, yes)\nRXZF(varchar|comment: total score for admission|values:)\nDWH(varchar|comment: unit number (college)|values: for example, 1520000, 1540000, 1530000, 1550000, 1510000, 1570000, etc.)\nRXRQ(varchar|comment: enrollment date|values: enumeration values ​​include, 20170915)\nSJHM(varchar|comment: mobile phone number|

[0142] values:)\nXZ(varchar|comment:school system|values:enumeration values ​​include,3)]','BKSBJXXSJ':'table:BKSBJXXSJ\ntable_comment:student class information table

[0143] \ncolumns = [\nBH(varchar|comment:class number|values:for example,0212201701,0212201702,0212201703,0212201704,0212201801,0212201802, etc.)\nJBNY(varchar|comment:class establishment year and month|values:for example,2022-04-27,2020-04-23,2021-01-28,2019-04-30,2022-06-09,2021-04- 30, etc.)\nSSNJ(varchar|comment:Grade|values:for example,2022,2019,2021,2020,2018,2017, etc.)\nBZRGH(varchar|comment:Class Teacher ID|values:for example,19939851008,20029661036,20139811032,20099901019,19959881007,19999951004, etc.)\nBZXH(varchar|comment:Class Monitor ID|

[0144] values:)\nFDYH(varchar|comment: counselor number|values:)\nBJMC(varchar|comment:01 class, 02 class, 03 class, etc.)\nBJJC(varchar|comment: class abbreviation|values:)\nBZRXM(varchar|comment: head teacher's name|values: for example, Zhang San, Li Si, Wang Wu, Lao Liu, etc.)\nBZXM(varchar|comment: monitor's name|

[0145] values:)\nFDYXM(varchar|comment: counselor name|values:)\nDWH(varchar|comment: unit number|values: for example, 1520000, 1550000, 1590000, 1540000, 1570000, 1560000, etc.)\nDWMC(varchar|comment: unit name|values: for example, the First Medical College, the Second Medical College, the Third Medical College, the School of Life Sciences, the School of Humanities and Management, etc.)\nZXRS(varchar|comment: Number of students on campus|values: for example, 0, 30, 31, 35, 33, 37, etc.)\nZYH(varchar|comment: major number|values: for example, 0616, 0312, 0317, 0626, 0414, 0413, etc.)\nZYMC(varchar|comment: major name|values: for example, Nursing, Traditional Chinese Medicine, Traditional Chinese Medicine (Undergraduate and Master's Degree), Nursing (Bin), Traditional Chinese Medicine, Pharmacy, etc.)\nXQ(varchar|comment: Campus|values: enumeration values ​​include, a certain campus, a certain second campus)]'}# Sample SQL

[0146] #Table association description

[0147] Related table: sx_bk_cjsj, sx_bk_xjsj Related field: sx_bk_cjsj.SZBJ=

[0148] sx_bk_xjsj.BJMC Relationship description: Student information is associated with class information#Question: Class information of undergraduate student with student number 00001

[0149] #SQL:

[0150] S110, large model calls to generate SQL;

[0151] Similarly, the large model is a large language model, and the large language model can be a large language model with more than 700 million parameters, and there are no other restrictions.

[0152] Input the complete prompt into the large language model and use the semantic understanding ability of the large model to generate SQL.

[0153] The following is the generated sample SQL:

[0154] SELECT2 BKSBJXXSJ.BH AS class number, BKSBJXXSJ.JBNY AS class establishment year, BKSBJXXSJ.SSNJAS grade, BKSBJXXSJ.BZRGH AS class teacher's work number, BKSBJXXSJ.BZXH AS monitor's student number, BKSBJXXSJ.FDYH AS counselor's number, BKSBJXXSJ.BJMC AS class name, BKSBJXXSJ.BJJC AS class abbreviation, BKSBJXXSJ.BZRXM AS class teacher's name, BKSBJXXSJ.BZXM AS monitor's name, BKSBJXXSJ.FDYXM AS counselor's name, BKSBJXXSJ.DWH AS unit number, BKSBJXXSJ.DWMC AS unit name, BKSBJXXSJ.ZXRS AS number of students in school, BKSBJXXSJ.ZYH AS major number, BKSBJXXSJ.ZYMC AS major name FROM20BKSJCSJ21 JOIN22 BKSBJXXSJ ON BKSJCSJ.SZBJ=

[0155] BKSBJXXSJ.BJMC23WHERE24 BKSJCSJ.XH='00001'AND BKSJCSJ.XZ='4'.

[0156] The embodiment of the present invention also discloses a device for generating SQL based on a large language model, which includes the following modules:

[0157] The data source configuration module is used to configure database connection information, where the database connection information includes the database URL, user name and password.

[0158] The table management module is used to obtain all tables in the data source and the DDL information in the table creation statement based on the data source configuration, query the data in the table and convert the DDL information into two new DDL descriptions: abbreviated DDL information and full DDL information according to preset rules.

[0159] The table relationship management module is used to sort out the relationship between tables in the database and construct a table relationship diagram; the table relationship diagram is in JSON format or XML format.

[0160] The domain knowledge combing module is used to comb the domain knowledge of a specific domain and establish a domain knowledge base; domain knowledge includes professional terms and synonyms of professional terms.

[0161] The problem SQL management module is used to correct the problem SQL generated by the large model and generate the correction SQL, establish the OA question-answering system, and store the problem SQL and the correction SQL in the vector database;

[0162] A user question receiving module is used to receive a natural language query question input by a user and record it as an initial question;

[0163] The prompt assembly module is used to segment the initial question through the word segmentation algorithm and obtain the word segmentation result, match the word segmentation result with the domain knowledge in the domain knowledge base, and replace the keywords in the initial question with the matched domain knowledge to form a replacement question;

[0164] Vectorize the replacement problem to generate a vectorized problem, use the Euclidean distance to calculate the vector similarity between the vectorized problem and the problem SQL, and find the top three problem SQLs with the highest similarity, which are recorded as the top three problem SQLs;

[0165] Generate an abbreviated prompt based on the replacement question, the first three question SQL, and the abbreviated DDL information; the abbreviated prompt includes the calibration definition, database table information, and user questions.

[0166] The large model table selection and calling module is used to input the abbreviated prompt into the large language model, filter out the most relevant table for the user query from the database, and record it as the most relevant table; among them, the large language model selects a large language model with more than 700 million parameters.

[0167] Complete prompt assembly module, used to find the table information and table relationship information of the most relevant table in the table relationship diagram based on the most relevant table and table relationship diagram;

[0168] Find the complete DDL description information in the complete DDL version based on the table information and the associated table of the table information;

[0169] Assemble the complete prompt using the complete DDL description information, table relationship information, replacement questions, and the SQL for the first three questions.

[0170] The large model calls the SQL generation module, which is used to input the complete prompt into the large language model, and the large model generates SQL. The large language model uses a large language model with more than 700 million parameters.

[0171] An embodiment of the present invention further discloses an electronic device, which includes: a processor and a memory. The processor and the memory are connected, such as through a bus. Optionally, the electronic device may also include a transceiver. It should be noted that in actual applications, the transceiver is not limited to one, and the structure of the electronic device does not constitute a limitation on the embodiment of the present invention.

[0172] The processor may be a CPU central processing unit, a general processor, a DSP data signal processor, an ASIC application specific integrated circuit, an FPGA field programmable gate array or other programmable logic devices, transistor logic devices, hardware components or any combination thereof. It may implement or execute various exemplary logic blocks, modules and circuits described in conjunction with the disclosure of the present invention. The processor may also be a combination that implements computing functions, such as a combination of one or more microprocessors, a combination of a DSP and a microprocessor, etc.

[0173] The bus may include a path to transmit information between the above components. The bus may be a PCI peripheral component interconnect standard bus or an EISA extended industry standard architecture bus, etc. The bus may be divided into an address bus, a data bus, a control bus, etc.

[0174] The memory can be a ROM read-only memory or other types of static storage devices that can store static information and instructions, a RAM random access memory or other types of dynamic storage devices that can store information and instructions, or an EEPROM electrically erasable programmable read-only memory, a CD-ROM read-only disc or other optical disc storage, an optical disc storage (including compressed optical discs, laser discs, optical discs, digital versatile discs, Blu-ray discs, etc.), a magnetic disk storage medium or other magnetic storage device, or any other medium that can be used to carry or store the desired program code in the form of instructions or data structures and can be accessed by a computer, but is not limited to this.

[0175] The memory is used to store the application code for executing the solution of the present invention, and the execution is controlled by the processor. The processor is used to execute the application code stored in the memory to implement the content shown in the method for generating SQL based on a large language model disclosed in the above embodiment.

[0176] The electronic devices include, but are not limited to, mobile terminals such as mobile phones, laptop computers, digital broadcast receivers, PDAs (personal digital assistants), PADs (tablet computers), PMPs (portable multimedia players), vehicle-mounted terminals (such as vehicle-mounted navigation terminals), and fixed terminals such as digital TVs, desktop computers, etc. It can also be a server, etc.

[0177] An embodiment of the present invention discloses a computer-readable storage medium, on which a computer program is stored. When the computer-readable storage medium is run on a computer, the computer can execute the corresponding content of the method for generating SQL based on a large language model disclosed in the above embodiment.

[0178] The above are all preferred embodiments of the present invention, and are not intended to limit the protection scope of the present invention. Therefore, any equivalent changes made based on the structure, shape, and principle of the present invention should be included in the protection scope of the present invention.

Claims

1. A method for generating SQL based on a large language model, characterized in that: The following steps are involved: Data source configuration, configure database connection information; Table management: Based on data source configuration, obtain all tables in the data source and DDL information in table creation statements, query the data in the table, and convert the original DDL information into two new DDL descriptions: abbreviated DDL information and full DDL information according to preset rules; Table relationship management: sort out the relationship between tables in the database and build a table relationship diagram; Domain knowledge sorting: sort out domain knowledge in a specific field and establish a domain knowledge base; Problem SQL management: correct the problem SQL generated by the large model and generate the corrected SQL, establish an OA question-and-answer system, and store the problem SQL and the corrected SQL in the vector database; User question receiving, receiving the natural language query question input by the user, recorded as the initial question; Abbreviated prompt assembly: segment the initial question through the word segmentation algorithm and obtain the word segmentation result, match the word segmentation result with the domain knowledge in the domain knowledge base, and replace the keywords in the initial question with the matched domain knowledge to form a replacement question; Vectorize the replacement problem to generate a vectorized problem, use the Euclidean distance to calculate the vector similarity between the vectorized problem and the problem SQL, and find the top three problem SQLs with the highest similarity, which are recorded as the top three problem SQLs; Generate an abbreviated prompt based on the replacement question, the first three questions SQL, and the abbreviated DDL information; The abbreviated prompt includes color calibration definition, database table information and user questions; The large model selects the table and calls it. The abbreviated prompt is input into the large language model, and the most relevant table for the user's query is selected from the database and recorded as the most relevant table. Complete prompt assembly, based on the most relevant table and table relationship diagram, find the table information and table relationship information of the most relevant table in the table relationship diagram; Find the complete DDL description information in the complete DDL version based on the table information and the associated table of the table information; Assemble a complete prompt using the complete DDL description information, table relationship information, replacement questions, and the first three questions SQL; The big model calls to generate SQL, inputs the complete prompt into the big language model, and the big model generates SQL.

2. The method for generating SQL based on a large language model according to claim 1, characterized in that: The database connection information includes database URL, user name and password.

3. The method for generating SQL based on a large language model according to claim 1, characterized in that: The table relationship diagram is in JSON format or XML format.

4. The method for generating SQL based on a large language model according to claim 1, characterized in that: The domain knowledge includes professional terms and synonyms of professional terms.

5. A device for generating SQL based on a large language model, characterized in that: include: Data source configuration module, used to configure database connection information; The table management module is used to obtain all tables in the data source and the DDL information in the table creation statement based on the data source configuration, query the data in the table, and convert the original DDL information into two new DDL descriptions: abbreviated DDL information and full DDL information according to preset rules; Table relationship management module, used to sort out the relationship between tables in the database and build a table relationship diagram; The domain knowledge combing module is used to comb the domain knowledge in a specific field and establish a domain knowledge base; The problem SQL management module is used to correct the problem SQL generated by the large model and generate the correction SQL, establish the OA question-answering system, and store the problem SQL and the correction SQL in the vector database; A user question receiving module, used to receive a natural language query question input by a user, recorded as an initial question; The prompt assembly module is used to segment the initial question through the word segmentation algorithm and obtain the word segmentation result, match the word segmentation result with the domain knowledge in the domain knowledge base, and replace the keywords in the initial question with the matched domain knowledge to form a replacement question; Vectorize the replacement problem to generate a vectorized problem, use the Euclidean distance to calculate the vector similarity between the vectorized problem and the problem SQL, and find the top three problem SQLs with the highest similarity, which are recorded as the top three problem SQLs; Generate an abbreviated prompt based on the replacement question, the first three questions SQL, and the abbreviated DDL information; The abbreviated prompt includes color calibration definition, database table information and user questions; The large model table selection calling module is used to input the abbreviated prompt into the large language model, filter out the most relevant table for the user's query from the database, and record it as the most relevant table; Complete prompt assembly module, used to find the table information and table relationship information of the most relevant table in the table relationship diagram based on the most relevant table and table relationship diagram; Find the complete DDL description information in the complete DDL version based on the table information and the associated table of the table information; Assemble a complete prompt using the complete DDL description information, table relationship information, replacement questions, and the first three questions SQL; The large model calls the SQL generation module, which is used to input the complete prompt into the large language model, and the large model generates SQL.

6. An electronic device, characterized in that: It includes: One or more processors; Memory; One or more applications, wherein the one or more applications are stored in the memory and are configured to be executed by the one or more processors, and the one or more programs are configured to: execute a method for generating SQL based on a large language model according to any one of claims 1 to 4.

7. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the program is executed by a processor, a method for generating SQL based on a large language model as described in any one of claims 1 to 4 is implemented.

Citation Information

Patent Citations

  • Improved NL2SQL method based on generative large language model

    CN116821168A

  • Knowledge graph generation type question answering method and system based on large language model

    CN117033608A

  • Virtual expert analysis method and device based on big language model intention recognition

    CN117931834A

  • Knowledge base response method and system based on large language model

    CN117951249A

  • Method and system for automatically generating sql and replying questions

    CN118093634A