Natural language to SQL with an LLM

Procedure

1. Create a question template table

   The name of the question template table must start with polar4ai_nl2sql_pattern, and its schema must include the five columns defined in the following CREATE TABLE statement.

   DROP TABLE IF EXISTS `polar4ai_nl2sql_pattern`;
   CREATE TABLE `polar4ai_nl2sql_pattern` (
     `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
     `pattern_question` text COMMENT 'Template question',
     `pattern_description` text COMMENT 'Template description',
     `pattern_sql` text COMMENT 'Template SQL',
     `pattern_params` text COMMENT 'Template parameters',
     PRIMARY KEY (`id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

   Column descriptions

   Parameter	Description
   template question	A parameterized question that is the input for the LLM-based NL2SQL model. In the template question, write parameters in the #{XXX} format.
   template description	A summary of the template question, with entities such as dates, years, or organizations extracted as parameters. These entities typically map to specific columns in a table. In the template description, you must write parameters in the [XXX] format, and their order must be consistent with the order of the parameters in the template question.
   template SQL	The correct SQL for the template question. In this SQL, parameters from the template question are treated as variables. Note The parameters in the template question and template SQL do not have to be identical, but they must be related. For example, parameters can share a common prefix, and their values can have a one-to-one mapping. With #{category} and #{categoryCode}, when the parameter values for category are "ordinary trademark", "special trademark", and "collective trademark", the corresponding categoryCode values are 0, 1, and 2, respectively. For details, see the example below.
   template parameters	A JSON string consisting of three parameters: table_name, param_info, and explanation. table_name string: The table name used in the template SQL. param_info array: Description of the parameters in the template SQL. param_name string: The parameter name. value array: Sample values for the parameter. Note If the parameter has a finite set of enumerated values, list all of them in the value array if possible. If you provide only sample values, list 2 to 4 examples. If parameters are interdependent, map them using the array index. For example, with #{category} and #{categoryCode}, "ordinary trademark" in category corresponds to 0 in categoryCode, and "special trademark" corresponds to 1 in categoryCode. For details, see the example below. explanation string: Additional notes, which usually describe requirements for the generated SQL, such as what information to return or how to interpret specific fields. Note If template parameters are not needed, you can set the value to one of the following: NULL An empty string An empty list string: []

   Example

   Template question	Template description	Template SQL	Template parameters
   Query for courses with course name #{courseName} and teaching status #{status}	What are the courses with [Course Name] and [Teaching Status]?	SELECT course_name, course_time, course_location FROM courses WHERE course_name=#{courseName} AND status=#{statusCode}	[{"table_name":"courses","param_info":[{"param_name":"#{courseName}","value":["Mathematics","Physics","Chemistry","English","History","Geography","Biology","Computer Science","Art","Music","Physical Education","Programming","Literature","Psychology","Philosophy","Economics","Sociology","Physics Lab","Chemistry Lab","Biology Lab"]},{"param_name": "#{status}", "value": ["Not started","In progress"]},{"param_name": "#{statusCode}","value": [0,1]}], "explanation": "Output the course name (course_name), course time (course_time), and course location (course_location). Note: status is a constant mapping type. The variable mapping field is statusCode."}]
   What are the national standards planned for release in year #{issueDate} with project status #{projectStat}?	What are the [Project Status] national standards planned for release in [Year]?	SELECT DISTINCT planNum, projectCnName, projectStat FROM sy_cd_me_buss_std_gjbzjh WHERE `planNum` IS NOT NULL AND `dataStatus` != 3 AND `isValid` = 1 AND projectStat=#{projectStat} AND DATE_FORMAT(`issueDate`, '%Y')=#{issueDate}	[{"table_name":"sy_cd_me_buss_std_gjbzjh","param_info":[{"param_name":"#{issueDate}","value":[2009,2010,2011,2012]},{"param_name":"#{projectStat}","value":["Seeking comments","Published","Under review"]}],"explanation":"Output standard name (projectCnName), plan number, and project status."}]
   What are the trademarks with category #{category} and international classification #{intCls}?	What are the trademarks for [Trademark Type] in [International Classification]?	SELECT DISTINCT tmName, regNo, status FROM sy_cd_me_buss_ip_tdmk_new WHERE dataStatus!=3 AND isValid = 1 AND category=#{categoryCode} AND intCls=#{intClsCode}	[{"table_name":"sy_cd_me_buss_ip_tdmk_new","param_info":[{"param_name":"#{intCls}","value":["Chemical raw materials","Paints","Cosmetics and cleaning preparations","Fuels and lubricants","Pharmaceuticals"]},{"param_name":"#{category}","value":["ordinary trademark","special trademark","collective trademark"]},{"param_name":"#{intClsCode}","value":[1,2,3,4,5]},{"param_name":"#{categoryCode}","value":[0,1,2]}],"explanation":"Output trademark name (tmName), application/registration number (regNo), and trademark status (status). Note: category is a constant mapping type. The variable mapping field is categoryCode. intCls is a constant mapping type. The variable mapping field is intClsCode."}] For example, to create the first template question shown in the table above, run the following SQL:

   INSERT INTO `polar4ai_nl2sql_pattern` (`pattern_question`,`pattern_description`,`pattern_sql`,`pattern_params`) VALUES ('Query for courses with course name #{courseName} and teaching status #{status}','What are the courses with 【Course Name】【Teaching Status】?','SELECT course_name, course_time, course_location FROM courses WHERE course_name=#{courseName} AND status=#{statusCode}','[{"table_name":"courses","param_info":[{"param_name":"#{courseName}","value":["Mathematics","Physics","Chemistry","English","History","Geography","Biology","Computer Science","Art","Music","Physical Education","Programming","Literature","Psychology","Philosophy","Economics","Sociology","Physics Lab","Chemistry Lab","Biology Lab"]},{"param_name": "#{status}", "value": ["Not started","In progress"]},{"param_name": "#{statusCode}","value": [0,1]}], "explanation": "Output the course name (course_name), course time (course_time), and course location (course_location). Note: status is a constant mapping type. The variable mapping field is statusCode."}]');

2. Create a question template index table

   You can use a custom name for the index table, as long as it follows database naming conventions. This example uses pattern_index. Run the following SQL to create the question template index table:

   /*polar4ai*/CREATE TABLE pattern_index(id integer, pattern_question text_ik_max_word, pattern_description text_ik_max_word, pattern_sql text_ik_max_word, pattern_params text_ik_max_word, pattern_tables text_ik_max_word, vecs vector_768, PRIMARY KEY (id));

   Note

   • The question template index table does not appear in the database object list. To view it, run /*polar4ai*/SHOW TABLES;.

   • To delete the question template index table, for example, pattern_index, run the SQL statement /*polar4ai*/DROP TABLE IF EXISTS pattern_index;.

3. Import data into the index table

   Note

   The question template table cannot be empty. Before running the SQL to import data, add at least one record.

   /*polar4ai*/SELECT * FROM PREDICT (MODEL _polar4ai_text2vec, SELECT '') WITH (mode='async', resource='pattern') INTO pattern_index;

   Parameters

   • _polar4ai_text2vec is the text-to-vectorization model.

   • After INTO, specify the name of the question template index table created in Step 2.

   • You can configure multiple parameters in the WITH() clause:

     Parameter	Required	Description
     mode	Yes	Specifies the write mode. It must be async for asynchronous mode.
     resource	Yes	Specifies the resource type. It must be pattern to vectorize question template information.
     pattern_table_name	No	The name of the question template table to vectorize. This is the table name from Step 1. The default value is polar4ai_nl2sql_pattern, meaning vectorization is performed on the polar4ai_nl2sql_pattern table. If you set this parameter, you must specify a table name that starts with polar4ai_nl2sql_pattern.

     If you want to maintain different question template index tables for different scenarios or business domains, you can specify different table names when creating them. For example, if you create a polar4ai_nl2sql_pattern_user table for user-related scenarios, you can set the index name to pattern_index_user in Step 2. When importing the information, use the following SQL statement:

     /*polar4ai*/SELECT * FROM PREDICT (MODEL _polar4ai_text2vec, SELECT '') WITH (mode='async', resource='pattern', pattern_table_name='polar4ai_nl2sql_pattern_user') INTO pattern_index_user;

4. Check the task status

   After running the import statement, the system returns a task_id, such as bce632ea-97e9-11ee-bdd2-492f4dfe0918. You can run the following command to check the task status.

   /*polar4ai*/SHOW TASK `bce632ea-97e9-11ee-bdd2-492f4dfe0918`;

   When the task status is finish, the import is complete, and the model can reference the question template information. You can run the following SQL statement to view the question template index information:

   /*polar4ai*/SELECT * FROM pattern_index;

   Note

   If the data in the polar4ai_nl2sql_pattern table changes, you must re-run the procedure in Step 3.

5. Run an online query with question templates

   Run the following SQL statement to perform an online query using LLM-based NL2SQL and question templates:

   /*polar4ai*/SELECT * FROM PREDICT (MODEL _polar4ai_nl2sql, SELECT 'Query for courses with the course name Mathematics and status in progress') WITH (basic_index_name='schema_index', pattern_index_name='pattern_index');

   SELECT course_name, course_time, course_location FROM courses WHERE course_name='Mathematics' AND status=1;

   Parameters

   • After SELECT, enter the question to be converted into SQL.

   • basic_index_name is the name of the current database search index table.

   • pattern_index_name is the name of the question template index table.

   • You can configure multiple parameters in the WITH() clause. For descriptions of other parameters, see Run an online query with LLM-based NL2SQL.

     Parameter	Description	Value range
     pattern_index_top	The number of closest question templates to recall.	Value range: [1,10]. The default value is 2, which means only the top 2 templates are recalled for the current question.
     pattern_index_threshold	The similarity threshold for recalling a question template.	Value range: (0,1]. The default value is 0.85, which means a template is selected only if its vector similarity score exceeds 0.85.

Use cases

• Scenario 1: Replace specific terms in a question, such as names, industry jargon, or product names.

  For example: for all questions involving Zhang San, replace Zhang San with ZS001. In this case, for the questions What were Zhang San's sales last month? and What are Zhang San's total sales for this year?, you can use a configuration table to preprocess them into What were ZS001's sales last month? and What are ZS001's total sales for this year? before the final call to the large language model.

• Scenario 2: Add extra context to questions that contain specific terms.

  For example, for all questions involving Total Sales, the calculation formula Total Sales = SUM(Sales) should be appended. Before the final call to the large model, this information can be added through a configuration table and is appended when the question meets the corresponding condition.

• Scenario 3: Map and replace values for specific tables or columns in the final SQL.

  For example, for all SQL statements that ultimately involve the student_courses table, replace status = 'leave' with status = 0 as a fallback measure for column value mapping.

Syntax

Use the following SQL statement to create the configuration table. The table name polar4ai_nl2sql_llm_config is fixed and cannot be changed.

DROP TABLE IF EXISTS `polar4ai_nl2sql_llm_config`;
CREATE TABLE `polar4ai_nl2sql_llm_config` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  `is_functional` int(11) NOT NULL DEFAULT '1' COMMENT 'Indicates if the configuration row is active',
  `text_condition` text COMMENT 'Text condition for pre-processing',
  `query_function` text COMMENT 'Query processing function',
  `formula_function` text COMMENT 'Formula information function',
  `sql_condition` text COMMENT 'SQL condition for post-processing',
  `sql_function` text COMMENT 'SQL processing function',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Parameters

Example

1. When run without a configuration table, the following query:

   /*polar4ai*/SELECT * FROM PREDICT (MODEL _polar4ai_nl2sql, SELECT '筛选出2个请假次数最多的学生，按照学生的请假次数降序排列，显示学生的名字和请假次数。') WITH (basic_index_name='schema_index');

   SELECT s.student_name, COUNT(sc.id) AS leave_count FROM students s JOIN student_courses sc ON s.id = sc.student_id WHERE sc.status = 0 GROUP BY s.student_name ORDER BY leave_count DESC LIMIT 2;

2. Create the configuration table as described in the Syntax section.

3. Add a configuration record. This rule specifies that if an SQL statement references the students table or the student_courses table, but not the courses table, the system replaces status = 0 with status = 10.

   INSERT INTO `polar4ai_nl2sql_llm_config` (`is_functional`,`sql_condition`,`sql_function`) VALUES (1,'students||student_courses&&!!courses','{"replace":{"status = 0":"status = 10"}}');

4. Run the query from Step 1 again. The status value in the generated SQL is now replaced as specified by the rule.

   /*polar4ai*/SELECT * FROM PREDICT (MODEL _polar4ai_nl2sql, SELECT '筛选出2个请假次数最多的学生，按照学生的请假次数降序排列，显示学生的名字和请假次数。') WITH (basic_index_name='schema_index');

   SELECT s.student_name, COUNT(sc.id) AS leave_count FROM students s JOIN student_courses sc ON s.id = sc.student_id WHERE sc.status = 10 GROUP BY s.student_name ORDER BY leave_count DESC LIMIT 2;

Syntax

The following SQL statement creates the custom comments table. The table name polar4ai_nl2sql_table_extra_info cannot be changed.

DROP TABLE IF EXISTS `polar4ai_nl2sql_table_extra_info`;
CREATE TABLE `polar4ai_nl2sql_table_extra_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
  `table_name` text COMMENT 'Table name',
  `table_comment` text COMMENT 'Table description',
  `column_name` text COMMENT 'Column name',
  `column_comment` text COMMENT 'Column description',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Example

1. Create the custom comments table.

2. Update the comment for the status column in the student_courses table. This example adds a new option mapping: 2-Absent.

   INSERT INTO `polar4ai_nl2sql_table_extra_info` (`table_name`,`table_comment`,`column_name`,`column_comment`) VALUES ('student_courses','Student-course information table','status','Student status: 0-On leave, 1-Normal, 2-Absent.');

3. Re-run the step to import data table information into the schema index.

   /*polar4ai*/SELECT * FROM PREDICT (MODEL _polar4ai_text2vec, SELECT '') WITH (mode='async', resource='schema') INTO schema_index;

4. Check the task status.

   After you run the import statement, the system returns a task_id, such as bce632ea-97e9-11ee-bdd2-492f4dfe0918. Use the following command to check the import status. When taskStatus is finish, the task is complete.

   /*polar4ai*/SHOW TASK `bce632ea-97e9-11ee-bdd2-492f4dfe0918`;

5. Run a query using LLM-based NL2SQL.

   /*polar4ai*/SELECT * FROM PREDICT (MODEL _polar4ai_nl2sql, SELECT 'Show the names and number of absences for the 2 students with the most absences, sorted in descending order.') WITH (basic_index_name='schema_index');

   SELECT s.student_name, COUNT(sc.id) AS absence_count FROM student_courses sc JOIN students s ON sc.student_id = s.id WHERE sc.status = 2 GROUP BY s.student_name ORDER BY absence_count DESC LIMIT 2;

   The output shows that the LLM-based NL2SQL model now correctly maps Absent to the value 2 for the status column in the student_courses table.