PolarDB:NL2SQL: Natural language querying

Instructions

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 from 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;

   Question template column description

   Column name	Description
   Template question	A question with parameters, provided as input to the LLM-based NL2SQL model. In the template question, write parameters in the format #{XXX}.
   Template description	A summary of the template question, with some entities as parameters, such as date, year, or organization. These entities usually correspond to specific columns in a table. In the template description, write parameters in the format [XXX], and they must be in the same order as the parameters in the template question.
   Template SQL	The correct SQL statement corresponding to the template question. This SQL statement must treat the parameters from the template question as variables. Note The parameters in the template question and template SQL can be different, but they must be related. For example, the parameters should have the same prefix, and their values must have a one-to-one mapping. For #{category} and #{categoryCode}, if the parameter values for category are common trademark, special trademark, and collective trademark, the corresponding categoryCode values are 0, 1, and 2. For more information, see the example below.
   Template parameters	The template parameters are a JSON string with a structure consisting of three parameters: table_name, param_info, and explanation. The parameters are as follows: table_name string: The name of the table in the template SQL. param_info array: A 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 corresponds to a finite set of enumerated values, list all the values in value. If they are just sample values, you can list 2 to 4 values. If there are corresponding parameters, you need to map them using the array index. For #{category} and #{categoryCode}, "common trademark" in category corresponds to 0 in categoryCode, and "special trademark" corresponds to 2 in categoryCode. For more information, see the example below. explanation string: Additional information. This usually includes requirements for the generated SQL statement, such as what information to output or descriptions of fields. Note If you do not need to specify template parameters, you can set the template parameter value to any 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 status #{status}	What are the courses with [Course Name] and [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 the year #{issueDate} for projects with the status #{projectStat}?	What are the national standards planned for release in [Year] for projects with [Project Status]?	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 the standard name (projectCnName), plan number, and project status"}]
   What are the trademarks of type #{category} and international classification #{intCls}?	What are the trademarks of [Trademark Type] and [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 and varnishes","Cosmetics and cleaning preparations","Industrial oils and greases","Pharmaceuticals"]},{"param_name":"#{category}","value":["Common trademark","Special trademark","Collective trademark"]},{"param_name":"#{intClsCode}","value":[1,2,3,4,5]},{"param_name":"#{categoryCode}","value":[0,1,2]}],"explanation":"Output the 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."}]

   Using the scenario in this topic as an example, create the first template question in the preceding table. Run the following SQL statement:

   INSERT INTO `polar4ai_nl2sql_pattern` (`pattern_question`,`pattern_description`,`pattern_sql`,`pattern_params`) VALUES ('Query for courses with course name #{courseName} and status #{status}','What are the courses with [Course Name] and [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 index table name that complies with database specifications. This topic uses pattern_index as an example. The SQL statement to create the question template index table is as follows:

   /*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 is not directly displayed in the database. To view related information, you can run the SQL statement /*polar4ai*/SHOW TABLES;.

   • To delete the question template index table, using pattern_index as an example, you can run the SQL statement /*polar4ai*/DROP TABLE IF EXISTS pattern_index;.

3. Import information from the question template table into the index table

   Note

   The question template table cannot be empty. Before you run the SQL statement to import data into the index table, you must add at least one record.

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

   Parameter description

   • _polar4ai_text2vec is the text-to-vector model.

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

   • You can configure multiple parameters in WITH() to set the related behavior:

     Parameter	Required	Description
     mode	Yes	The data writing mode. This is fixed to `async` for asynchronous mode.
     resource	Yes	The resource type. This is fixed to `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: Create a question template table. The default value is polar4ai_nl2sql_pattern, which performs vectorization on the polar4ai_nl2sql_pattern table. When setting this, specify the name of a question template table that starts with polar4ai_nl2sql_pattern.

     If you want to maintain different question template index tables for different scenarios or businesses, you can specify different table names when you create the question template index tables. For example, you can create the polar4ai_nl2sql_pattern_user table for user-related scenarios, and then in the second step of creating the question template index, set the index name to pattern_index_user. When you import information into the index table, 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 you run the statement to import data into the index table, the task_id for the task is returned, such as bce632ea-97e9-11ee-bdd2-492f4dfe0918. You can use the following command to check the import status.

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

   When the return value is finish, the task is complete. The question template information can then be referenced by the model. 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 run Step 3. Import information from the question template table into the index table again.

5. Use question templates online

   You can run the following SQL statement to use LLM-based NL2SQL and question templates online:

   /*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;

   Parameter description

   • After SELECT, enter the question that you want to convert into an SQL statement.

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

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

   • You can configure multiple parameters in WITH() to set the related behavior. For more information about other parameters, see Use LLM-based NL2SQL online:

     Parameter name	Parameter description	Value range
     pattern_index_top	The number of most similar question templates to retrieve.	Valid values: [1,10]. The default value is 2, which selects the 2 most optimal templates for the current question template.
     pattern_index_threshold	The threshold used to determine the similarity of retrieved question templates.	Valid values: (0,1]. The default value is 0.85. A question template is selected only if its vector match score exceeds 0.85.

Scenarios

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

  For example, for all questions that involve Zhang San, you can 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 this year?, you can use the configuration table to pre-process them into What were ZS001's sales last month? and What are ZS001's total sales this year? before you call the large language model.

• Scenario 2: Add extra information to questions that contain specific words.

  For example, for all questions that involve total sales, you can add the calculation formula for total sales: Total sales = SUM(sales). Before you call the large language model, you can use the configuration table to add this type of information, which is appended when a question meets the corresponding condition.

• Scenario 3: Map and replace values for specific tables or columns.

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

Syntax

The SQL statement to build the configuration table is as follows. 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 'Whether it is effective',
  `text_condition` text COMMENT 'Text condition',
  `query_function` text COMMENT 'Query processing',
  `formula_function` text COMMENT 'Formula information',
  `sql_condition` text COMMENT 'SQL condition',
  `sql_function` text COMMENT 'SQL processing',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Parameter description

Example

1. Before you add the configuration table, run the following SQL statement:

   /*polar4ai*/SELECT * FROM PREDICT (MODEL _polar4ai_nl2sql, SELECT 'Filter for the 2 students with the most absences, sort them by the number of absences in descending order, and display their names and number of absences.') 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. Build the configuration table according to the Syntax.

3. Add a configuration record. If the students table or the student_courses table is in the SQL statement, and the courses table is not, replace 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 SQL statement from Step 1. The generated SQL statement now has the status value replaced.

   /*polar4ai*/SELECT * FROM PREDICT (MODEL _polar4ai_nl2sql, SELECT 'Filter for the 2 students with the most absences, sort them by the number of absences in descending order, and display their names and number of absences.') 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 SQL statement to create the custom table and column comments table is as follows. The table name polar4ai_nl2sql_table_extra_info is fixed and 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 table and column comments table.

2. Modify the comment for the status column in the student_courses table. In this example, a new option description is added for the status column: 2-Absent.

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

3. Run the step to import data from the data tables into the search index table again.

   /*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 statement to import data into the search index table, the task_id for the task is returned, such as bce632ea-97e9-11ee-bdd2-492f4dfe0918. You can 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. Use LLM-based NL2SQL online.

   /*polar4ai*/SELECT * FROM PREDICT (MODEL _polar4ai_nl2sql, SELECT 'Filter for the 2 students with the most absences, sort them by the number of absences in descending order, and display their names and number of absences.') 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;

   From the preceding output, you can see that the LLM-based NL2SQL model has mapped absent to the column value 2 for the status column in the student_courses table.