PolarDB:Data-Agent best practices

Configure question templates

You can use general question templates to guide the model. These templates provide specific knowledge to help the model generate SQL statements.

1. Run the following SQL statement to create the question template table 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 AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

   The table name must start with polar4ai_nl2sql_pattern. The table schema must include the five columns from the `CREATE TABLE` statement.

2. Next, create the index table pattern_index for the question template.

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

   To fine-tune the model, configure a template for the second question. The goal is to return the outlet's name.

   Run the following SQL statement to add a new pattern:

   INSERT INTO polar4ai_nl2sql_pattern (id, pattern_question, pattern_description, pattern_sql, pattern_params) VALUES (
     1, 
     "Which outlet in #{position} has the highest income?", 
     "Which outlet in [location] has the highest income?", 
     "SELECT r.position FROM bill_info b JOIN restaurant_info r ON b.restaurant_id = r.id WHERE r.position LIKE '%#{position}%' ORDER BY b.actual_amount DESC LIMIT 1;", 
     '[{"table_name":"bill_info","param_info":[{"param_name":"#{position}","value":["Shanghai"]}], "explanation": "Consumption location"}]'
   );

   The pattern uses slots to match multiple locations. In the pattern_sql column, enter the correct SQL statement and mark the slot with #{}. The pattern_params column is used for additional post-processing of table information, but you can ignore it here.

3. Next, import the question template information into the index table.

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

   Similar to the process for building the schema_index table, a task ID is returned. You can check the task status by running /*polar4ai*/show task 'xxx-xxx-xxx'.

   Note

   If the data in the polar4ai_nl2sql_pattern table is updated, you must recreate pattern_index and import the data again. Use the following SQL statement to delete the old index table:

   /*polar4ai*/DROP TABLE pattern_index;

   Re-execute the problematic SQL statement and add the pattern_index hint.

   /*polar4ai*/SELECT * FROM PREDICT (MODEL _polar4ai_nl2sql, select 'Which outlet in Shanghai has the highest income?') WITH 
   (basic_index_name='schema_index',pattern_index_name='pattern_index');

   

Create a configuration table

If you want to pre-process questions or post-process the generated SQL, you can use a configuration table.

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`)
);

INSERT INTO polar4ai_nl2sql_llm_config (id, is_functional, text_condition, query_function, formula_function, sql_condition, sql_function) VALUES (
  1, 
  1, 
  "customer traffic||customer flow", 
  "", 
  "Customer traffic or customer flow is counted as the sum of the number of diners", 
  "", 
  ""
);

INSERT INTO polar4ai_nl2sql_llm_config (id, is_functional, text_condition, query_function, formula_function, sql_condition, sql_function) VALUES (
  2, 
  1, 
  "", 
  "", 
  "Matching for the outlet location 'position' requires a fuzzy search", 
  "", 
  ""
);

Train the model

/*polar4ai*/CREATE MODEL udf_qwen14b WITH (model_class='qwen-turbo', model_parameter=(basic_index_name='schema_index', pattern_index_name='pattern_index',training_type='efficient_sft')) as (SELECT '')

Parameter descriptions

Parameter descriptions