PolarDB:Data-Agent best practices

Add question templates

Question templates provide verified question-to-SQL mappings. When a user's question matches a template, the model uses the pre-defined SQL as a reference, which significantly improves accuracy for known query patterns.

Step 1: Create the template table.

The table name must start with polar4ai_nl2sql_pattern and include these five columns:

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;

Step 2: Create the pattern 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));

Step 3: Add a template.

The following template fixes question 2 so it returns the outlet name. Use #{} slots to match variable parts of the question — here, #{position} matches any location name.

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"}]'
);

Step 4: Import the templates into the index.

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

A task ID is returned. Check the status with /*polar4ai*/SHOW TASK 'xxx-xxx-xxx'.

If you update polar4ai_nl2sql_pattern, recreate pattern_index from scratch. Delete the old index first: Then re-run the import statement.

/*polar4ai*/DROP TABLE pattern_index;

Step 5: Run the query with the pattern index.

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

Configure LLM hints

The polar4ai_nl2sql_llm_config table lets you define preprocessing rules that are injected into the LLM prompt. Use it when the model misinterprets domain-specific terms or applies the wrong matching logic.

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

Vocabulary hints

Question 6 uses "customer traffic", but the LLM doesn't reliably map this to the diner_count column. Add a hint to define the term:

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",
  "",
  ""
);

• is_functional=1 activates the rule.

• text_condition uses || as a separator — the rule applies when the question contains "customer traffic" or "customer flow".

• formula_function uses text or a formula to explain the meaning of a technical term to the LLM.

No index rebuild is required for configuration table changes. The model reads the rules directly at query time.

Fuzzy matching hints

Question 3 may fail if the model uses = for location matching instead of a LIKE clause. Add a global hint to enforce fuzzy matching on the position column:

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",
  "",
  ""
);

An empty text_condition means the rule applies to all queries. Use global rules carefully to avoid unintended side effects.

For question 5, try adding month-over-month and year-over-year calculation formulas to the configuration table yourself as a practice exercise.

Prerequisites

• AI node specification: polar.mysql.x8.2xlarge.gpu (16-core, 125 GB, one GU100)

• Only one model can be trained at a time

• Only one model can be deployed at a time

Train a 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 '')

Use a deployed model for NL2SQL

Pass the deployment name via the llm_model parameter:

/*polar4ai*/SELECT * FROM PREDICT (MODEL _polar4ai_nl2sql, SELECT 'What is the content for id 1?') WITH (basic_index_name='schema_index', llm_model='udf_qwen14b')