N2SQL shipping vertical field data query method based on five-stage self-repairing

Abstract

The invention discloses an N2SQL shipping vertical field data query method based on five-stage self-repairing. The N2SQL shipping vertical field data query method comprises the following steps: (1) establishing a cue word template of a database Schema structure; (2) providing a plurality of sample data for each high-base number field in the table; (3) providing all values of the low-base number fields in the columns; (4) constructing a target extraction module; (5) constructing a table selection module, screening out a required table, and requiring a large model to explain a selection reason; (6) constructing a column selection module, screening out a required column, and requiring a large model to explain a selection reason; (7) generating an SQL Generation module by the SQL, and generating an executable SQL statement by combining a database Schema according to the obtained table name and column name; and (8) constructing a self-correction module. According to the method, the efficiency and quality of knowledge retrieval and generation can be remarkably improved, richer and more accurate information services are provided for users, and technical progress and application expansion in the vertical field of shipping are promoted.

Description

Technical Field

[0001] The present invention relates to the field of shipping data query, and specifically relates to a method for querying N2SQL shipping vertical domain data based on five-stage self-repair. Background Art

[0002] N2SQL refers to a technology or system that converts natural language queries into SQL queries, aiming to provide users with a more user-friendly and lower learning-cost interaction method, and improve the work and learning efficiency of relevant personnel. Before the emergence of large language models, the research on N2SQL was more focused on regarding it as a translation task. After the emergence of large language models, their powerful semantic understanding ability has made people turn their attention to large models, using them as an intermediate "medium" from natural language to SQL query statements.

[0003] Current research on N2SQL is mainly based on ICL (In-Context Learning) and Prompt engineering. Therefore, the database Schema and the format of the Prompt provided to the large model are particularly crucial. Pourreza and Rafiei improved the performance of large language models in N2SQL tasks by decomposing tasks, classifying problems of different difficulty levels, and setting corresponding COT-based Prompts for SQL statements of different difficulties. Dawei Gao, Haibin Wang, etc. conducted empirical analyses on several popular Prompt formats, the writing formats of database Schemas, and the organizational forms of some sample organizations. And they used dynamic few-shot prompting, matching similar samples based on the user's questions and the generated SQL statements, and then using these similar samples to guide the large language model to generate the final SQL statement.

[0004] The above research has achieved good results on the Spider dataset, but the databases in real industrial scenarios are more complex. In academic scenarios, the database Schema information of the selected datasets is not actually complex. People often expect large language models to generate complex SQL statements to handle complex SQL query tasks, and pursue the ability of large language models to generate SQL statements of various difficulty levels. However, in the real scenarios of the shipping field, pursuing the ability of large language models to complete complex SQL generation tasks has no practical significance. The real difficulty lies in the fact that large language models cannot fully understand the complex Schema information of shipping databases. Taking the shipping database as an example, data tables are usually named using abbreviations, making it difficult for large language models to obtain much information from the table names. In addition, it is difficult to describe the purpose of a table completely in natural language. There are a large number of columns in shipping data tables, and these columns involve some professional field data of ships, such as the draft depth of ships, the duration of AIS loss information, wind direction, the type of wave direction relative to the ship's sailing direction, etc. These column names use a large number of abbreviations, making it difficult for large models to accurately locate the corresponding columns according to the user's natural language questions. In addition to the naming problems of shipping data tables and columns, the values in the columns are even more complex. The data tables contain a large number of low-cardinality fields containing domain information. Taking the column corresponding to ship classification as an example, the ship type (primary classification) value 10000 represents Passenger Ship, 20000 represents Full Container Ship, 30000 represents Liquid Bulk Carrier, etc. In addition to ship types, there are also a large number of columns containing low-cardinality fields. In real scenarios, the user's questions may contain the above-mentioned information in various forms. In the absence of domain knowledge, large language models are very likely to directly translate the ship types that appear into the corresponding Chinese and English character strings for matching in the corresponding column records, which will result in no results being retrieved. In the above scenarios, if the large language model does not obtain the database Schema in advance, it will not be able to complete the N2SQL process. Summary of the Invention

[0005] To solve the above problems of the prior art, the present invention provides an N2SQL shipping vertical domain data query method based on five-stage self-repair.

[0006] The technical solution of the present invention is as follows:

[0007] An N2SQL shipping vertical domain data query method based on five-stage self-repair, characterized by including the following steps:

[0008] (1) Establish a prompt word template for the database Schema structure;

[0009] (2) Provide several sample data for each high-radix field in the table;

[0010] (3) Provide all the values of the low-radix fields in the column;

[0011] (4) Construct the target extraction module;

[0012] (5) Construct the table selection module, which combines the output of the previous stage, the table schema, and the user's question to filter out the required table, and asks the large model to explain the reason for the selection;

[0013] (6) Construct the column selection module, which combines the output of the previous stage, the table schema, and the user's question to filter out the required columns, and asks the large model to explain the reason for the selection;

[0014] (7) The SQL Generation module generates executable SQL statements according to the table names and column names obtained above, in combination with the database Schema; for queries containing name classes, the large model is required to use a fuzzy matching strategy to perform case-insensitive fuzzy matching on the English names of ships;

[0015] (8) Construct a self-correction module. If the SQL statement generated in the previous step encounters an error, this module will capture the error information and input the incorrect SQL statement and error information into the large model for self-correction.

[0016] Preferably, in step (1), use comments to make a short description of the main use of the table, and then use the SQL CREATE statement to show the organizational structure of the table. Use the SQL comment symbol '--' to explain each column after each column, specifically:

[0017]

[0018] Preferably, in step (2), provide three lines of sample data to the large language model for each high-radix field in the table using SQL comment-style prompt words, specifically:

[0019]

[0020]

[0021] Preferably, in step (3), filter out the query target according to the user's natural language question, analyze the required information data, and return it in the form of a list, specifically:

[0022]

[0023] Preferably, in step (4), filter out the query target according to the user's natural language question, analyze the required information data, and return it in the form of a list, specifically:

[0024]

[0025]

[0026] Preferably, the prompt word in step (5) is specifically:

[0027]

[0028] Preferably, the prompt word in step (6) is specifically:

[0029]

[0030]

[0031] Preferably, the prompt word in step (7) is specifically:

[0032]

[0033]

[0034] Preferably, the prompt word in step (8) is specifically:

[0035]

[0036] The technical effects of the present invention are as follows:

[0037] The present invention studies the organizational structure and Schema of the database in the shipping field, designs a corresponding N2SQL solution for the characteristics of the shipping field database, and proposes a method for querying N2SQL shipping vertical domain data based on five-stage self-repair, aiming to provide more convenient and efficient data query services for relevant practitioners.

[0038] A method for querying N2SQL shipping vertical domain data based on five-stage self-repair, disclosed by the present invention, aims to provide simple and economical data query services for shipping practitioners. The system is based on Prompt Engineering. First, it designs a database Schema template to address issues such as ambiguous column names and chaotic data values in the actual production database in the shipping field, helping the large language model better understand the database information. Secondly, the system improves the accuracy of generating SQL statements through five-stage modules. The first stage is the Target extraction module, which filters out the query target based on the user's natural language question and analyzes the required information data. The second stage is the Table Selection module, which combines the output of the previous stage, the Table Schema, and the user's question to filter out the required tables and asks the large language model to explain the reasons for the selection. The third stage is the Column Selection module, which filters the required columns based on the output of the previous stage and the user's question and asks the large language model to explain the process. The fourth stage is the SQL Generation module, which generates an executable SQL statement based on the table names and column names obtained above and the database Schema. The fifth stage is the Self Correction module. If the SQL statement generated in the previous step encounters an error during execution, this module will capture the error information and input the incorrect SQL statement and error information into the large language model for self-correction. This system significantly improves the accuracy and efficiency of database queries in the shipping field.

[0039] Specifically, based on the characteristics of the shipping database in the industrial scenario, the present invention designs five cascaded modules to improve the query accuracy, having the following advantages:

[0040] (1) The present invention does not need to construct training data. Given the complex structure of the shipping database in the industrial scenario, the lack of training data, and the large amount of manpower and financial resources consumed in constructing training data. The present invention utilizes the context understanding ability of the large language model and does not need to construct a training data set;

[0041] (2) The present invention is decoupled from the database Schema. For different databases, users do not need to modify the processes or prompt words of the above five modules significantly. They only need to give the corresponding database Schema prompt words to use.

[0042] (3) The present invention adopts the COT idea. By disassembling the entire SQL generation process, it can effectively reduce the hallucination phenomenon of the large language model and improve the accuracy of SQL queries.

[0043] (4) Improve the user's decision-making ability: By providing high-quality knowledge support, the present invention helps users make more informed decisions in complex situations. This decision-making support ability provides strong assistance to users in various practical applications.

[0044] In summary, the implementation of the present invention can significantly improve the efficiency and quality of knowledge retrieval and generation, provide users with richer and more accurate information services, and promote the technological progress and application expansion in related fields. Brief Description of the Drawings

[0045] Figure 1 It is a flowchart of the method of the embodiment of the present invention. Detailed Embodiment

[0046] To better understand the present invention, the present invention will be further explained below in conjunction with the drawings and specific embodiments.

[0047] Embodiment

[0048] Based on the context understanding ability of the large language model, the present invention divides the process of SQL generation and repair into five stages. A five-stage module is constructed to help the large language model fully understand the Schema information of the shipping database.

[0049] The preconditions for the application of the present invention are as follows:

[0050] (1) Establish a prompt template for the database Schema structure. This part helps the large language model obtain the Schema information of the database. Some studies have shown that using prompt words in the SQL statement code style can better help the large language model understand the field information of the tables and columns in the database. The table names in the shipping field are usually abbreviated, which makes it difficult for the large language model to obtain information from the table names. Therefore, we first use comments to make a short description of the main use of the table, and then use the SQL CREATE statement to show the organizational structure of the table. Since the column names involve professional knowledge in the shipping field, we use the SQL comment symbol '--' to explain each column after it. As follows:

[0051]

[0052] (2) Provide three rows of sample data for each high-cardinality field in the table. This step aims to enable the large language model to obtain the value information of the table. We also use prompt words in the SQL comment style to provide sample data to the large language model, as follows:

[0053]

[0054] (3) Provide all the values of the low-base number fields in the column. We found that a large number of the values in the database tables in the shipping field use low-base number fields. All possible values need to be listed as follows:

[0055]

[0056] Given that there are a large number of columns in the database data tables in the shipping field, the maximum number of tokens that can be accepted by the large language model in one processing should be evaluated according to the actual situation. It is recommended to use a model with a processing context capacity greater than 8k.

[0057] Such as Figure 1 As shown, next, improve the accuracy of the generated SQL statements through five-stage modules:

[0058] (4) Build a Target Extraction module. This step filters out the query target based on the user's natural language question, analyzes the required information data, and returns it in a list form. Conduct a full investigation of the questions that users may ask in practice, and write a prompt template according to the actual situation to help the large language model analyze the search target.

[0059]

[0060] (5) Build a Table Selection module. Combine the output of the previous stage, the TableSchema, and the user's question to filter out the required tables, and require the large model to explain the reasons for the selection. Write accurate database Schema explanation information to help the large language model locate the query target to the corresponding table.

[0061]

[0062] (6) Build a Column Selection module. Combine the output of the previous stage, the Table Schema, and the user's question to filter out the required columns, and require the large model to explain the reasons for the selection. For the columns in the table, write short and accurate explanation information to help the large language model locate the query target to the columns in the determined table. The prompt design is as follows:

[0063]

[0064] (7) SQL Generation module. According to the table names and column names obtained above, combined with the database Schema, generate executable SQL statements. For queries involving names, such as the name of a ship, we require the large language model to use a fuzzy matching strategy. This is because in actual scenarios, we found that some users would add 'number' after the Chinese name of a specific ship when querying, and the large language model sometimes mistakenly regarded 'number' as part of the ship name and generated an SQL query statement for querying, which would result in no results returned. Similarly, based on users' query habits, we require the large language model to perform case-insensitive fuzzy matching on the English names of ships. Thoroughly investigate the questions that users may ask in practice and the actual situation of the shipping domain database. Analyze the possible types of SQL that may be generated and flexibly design corresponding prompt templates. The prompt templates are designed as follows:

[0065]

[0066] (8) Build a Self Correction module. If the SQL statement generated in the previous step encounters an error during execution, this module will capture the error information and input the incorrect SQL statement and the error information into the large language model for self-correction. During our experiments, we found that the SQL statements initially generated by the large language model sometimes had simple syntax errors or simple errors such as trying to find a non-existent column. For these simple errors, feedback the initially generated SQL statement combined with the error information from the database to the large language model to enable it to fix such simple errors. Through experiments, it was found that the large language model can well fix such simple-level errors, and users are not aware of the occurrence of intermediate query errors during actual use, ensuring a good user experience. The designed prompt templates are shown as follows:

[0067]

Claims

1. A five-stage self-repair-based N2SQL shipping vertical data query method, characterized by The following steps are involved: (1) Establish a prompt word template for the database schema structure; (2) Provide some sample data for each high-cardinality field in the table; (3) Provides all values ​​of low-cardinality fields in the column; (4) Constructing target extraction module; (5) Build a table selection module, combine the output of the previous stage with the table schema and user questions, filter out the required tables, and ask the big model to explain the reasons for the selection; (6) Build a column selection module, combine the output of the previous stage with the table schema and user questions, filter out the required columns, and ask the big model to explain the reasons for the selection; (7) SQL Generation The SQL Generation module generates executable SQL statements based on the table names and column names obtained above and in combination with the database schema. For queries involving name classes, the large model is required to use a fuzzy matching strategy to perform fuzzy matching of uppercase and lowercase letters on the English names of ships. (8) Construct a self-correction module. If an error occurs in the execution of the SQL statement generated in the previous step, the module will capture the error information and input the incorrect SQL statement and error information into the large model for self-correction.

2. The method according to claim 1, characterized in that In step (1), use comments to briefly describe the main purpose of the table, then use the SQL CREATE statement to display the organizational structure of the table, and use the SQL comment symbol '--' to explain the column after each column, as follows:

3. The method according to claim 1, characterized in that In step (2), three rows of sample data are provided to the large language model using SQL comment-style prompt words for each high-cardinality field in the table, specifically:

4. The method according to claim 1, characterized in that In step (3), the query target is screened according to the user's natural language question, the required information data is analyzed, and returned in the form of a list, specifically:

5. The method according to claim 1, characterized in that In step (4), the query target is screened according to the user's natural language question, the required information data is analyzed, and returned in the form of a list, specifically: """ Target: Analyze the given questions and prompts to identify and extract keywords, key phrases, and named entities. These elements are critical to understanding the core components of the query and providing guidance. This process involves identifying and isolating important terms and phrases that may be useful in formulating searches or queries related to the question being asked. illustrate:

1. Read the question carefully: Understand the main focus and specific details of the question. Extract the entities to be queried from the user's question, looking for any named entities (such as organizations, places, etc.), professional terms, and other phrases that contain important aspects of the inquiry.

2. Analysis prompts: Prompts are designed to direct attention toward certain elements relevant to answering the question. Extract any key words, phrases, or named entities that can provide further clarity or provide direction in formulating your answer.

3. List key phrases and entities: Combine the findings from the question and prompt into a Python list. This list should contain: - keywords: Single words or hints that capture important aspects of a question or hint. -Keyphrases: Phrases or named entities that represent specific concepts, places, organizations, or other important details. Make sure to maintain the original wording or terminology used in the questions and prompts. Task: Given the following questions and prompts, find and list all relevant keywords, key phrases, and named entities. User question: {QUESTION} Please provide your findings in the form of a list that captures the essence of the issues and prompts through the identified terms and phrases. """。 6. The method according to claim 1, characterized in that The specific prompt words in step (5) are: """ You are an expert in shipping and ships, and also a very smart data analyst. Your task is to analyze the provided database schema, understand the question being asked, and use the hints to determine which tables are needed to generate the SQL query that answers the question. Database Schema: {DATABASE_SCHEMA} The schema provides a detailed definition of the database structure, including tables, columns, primary keys, foreign keys, and any relevant details about relationships or constraints. Question: {QUESTION} Hint: {kew_words} Hints are designed to direct your attention to specific elements of the database schema that are critical to answering the question effectively. Task: Based on the provided database schema, questions and prompts, your task is to determine which tables should be used in SQL query construction. For each table selected, explain why it is necessary to answer the question. Your explanation should be logical, concise, and demonstrate a clear understanding of the database schema, the question, and the prompt. Please respond in the following JSON object format:

7. The method according to claim 1, characterized in that The specific prompt words in step (6) are: """ You are an expert in shipping and ships, and also a very smart data analyst. Your task is to examine the provided database schema, understand the question posed, and use the hints to identify the specific columns in the table that are critical to constructing the SQL query that answers the question. Database Schema Overview: {DATABASE_SCHEMA} The schema provides a detailed description of the database schema, including tables, columns, primary keys, foreign keys, and any relevant information about relationships or constraints. Pay special attention to the examples listed next to the columns, as they provide a direct hint as to which columns are relevant to our query. For the key phrases mentioned in the question, we have provided the most similar values ​​in the columns marked with "--examples" in front of the corresponding column name. This is a key hint to identify the columns that will be used in the SQL query. Question: {QUESTION} Hint: {table_selection} Hints are designed to direct your attention to specific elements of the database schema that are critical to answering the question effectively. Task: Based on the database schema, question, and prompt provided, your task is to identify all and only the columns that are essential to writing an SQL query to answer the question. For each column selected, explain why it is necessary to answer the question. Your reasoning should be clear and concise, showing a logical connection between the columns and the question asked. Tip: If you select a column to filter values ​​in, make sure that column contains the values ​​in the example. Please respond with a JSON object with the following structure: Please make sure your response includes table names as keys, each associated with a list of column names that are required to write the SQL query to answer the question. For each question, provide a clear and concise explanation of the reasoning behind your choice of column. """。 8. The method according to claim 1, characterized in that The specific prompt words for step (7) are: """ You are a data science expert. A database schema and a question are provided below. Your task is to read the schema, understand the question, and generate a valid Postgresql query to answer it. Before you generate your final SQL query, think about how you would write the query, step by step. Note: When searching for names, codes, or other text fields, you should use a flexible matching strategy: a. Use fuzzy matching: - For ship name matching, prefer using the LIKE operator with wildcard characters ('%' and '_'). For example: WHERE column_name LIKE '%keyword%' or WHERE column_name LIKE '%keyword%' b. Consider case sensitivity: -For English data, consider using the LOWER() or UPPER() functions for case-insensitive matching. For example: WHERE LOWER(column_name)LIKE LOWER('%Keyword%') c. Processing Chinese data: - For Chinese data, you may want to consider using full-text search capabilities (if supported by your database) or other Chinese-specific matching methods. - Consider using regular expressions for more complex matching if your database supports it. d.Multi-keyword search: -If the query involves multiple keywords, consider using multiple LIKE conditions or full-text search function. For example: WHERE column_name LIKE '%keyword1%' OR column_name LIKE '%keyword2%' Database Schema: {DATABASE_SCHEMA} This schema provides an in-depth description of the database schema, including tables, columns, primary keys, foreign keys, and any relevant information about relationships or constraints. Pay special attention to the examples listed next to each column, as they directly hint at which columns are relevant to our query.

9. The method according to claim 1, characterized in that Step (8) prompt word is: """ You are a Postgresql expert and your task is to modify the SQL query statement based on the error message of the database. You need to refer to the database schema, error message, original erroneous SQL statement and user questions to re-edit and modify to generate the correct SQL statement Database Schema: {DATABASE_SCHEMA} Question: {QUESTION} Error_message: {Message} Original_query: {Original_query} Take a deep breath, revisit the Database Schema and modify Original_query to the correct PostgreSQL query based on the Error_message provided above. Just give the modified SQL statement without providing any explanation. """。

Images