PolarDB:Data-Agent best practices

Vocabulary meaning hints

For the sixth question, because the Large Language Model (LLM) cannot accurately understand the term 'foot traffic', you can perform pre-processing by configuring the polar4ai_nl2sql_llm_config 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 'Is active',
  `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 the relevant configuration item to configure the LLM to count "customer traffic" or "customer flow" as "number of diners".

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 calculated as the sum of the number of diners", 
  "", 
  ""
);

In this case, a value of 1 for is_functional indicates that the configuration item is valid. The value of the text_condition field is 'people traffic||customer traffic', which matches questions that contain 'people traffic' or 'customer traffic'. The formula_function field explains specialized terms to the Large Language Model (LLM) using text or formulas.

In this case, you can directly execute SQL generation without building an index table or performing vectorization. The result is as follows.

Fuzzy match hints

In question 3, using the = operator to retrieve place names will fail if the name is not an exact match. Therefore, you should use a fuzzy search for place name matching. You can add the following configuration item.

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

If text_condition is empty, the configuration item applies globally. (Use with caution.)

The result is shown in the figure below. As you can see, the location matching successfully uses a fuzzy search.

Similarly, for question 5, you can add the calculation formulas for month-over-month and year-over-year to the polar4ai_nl2sql_llm_config configuration table to improve the precision of the generated SQL. You can try this on your own.

1. Assume your statement in NL2SQL is as follows:

   /*polar4ai*/SELECT * FROM PREDICT (MODEL _polar4ai_nl2sql, select 'Merchant type statistics') WITH (basic_index_name='schema_index',pattern_index_name='pattern_index');

   After the corresponding SQL statement is generated, check that it runs and returns a meaningful, non-empty result.

   SELECT merchtype AS merchant_type, COUNT(*) AS product_count FROM hkrt_merchant_info GROUP BY merchtype;

2. Use NL2Chart:

   Syntax

   /*polar4ai*/SELECT * FROM PREDICT (MODEL _polar4ai_nl2chart, <SQL_statement>) WITH (usr_query = <usr_query>, result_type = <result_type>);

   Parameters

   Parameter name	Description	Sample value
   usr_query	The user's input question, used to clarify the requirements for generating the chart.	"Sales statistics for each quarter of 2023"
   result_type	Specifies the type of the returned result. Currently, only 'IMAGE' is supported.	'IMAGE'
   SQL statement	The SQL query statement generated by the NL2SQL module, used to retrieve data.	SELECT quarter, sales FROM sales_data WHERE year = 2023

   Example: Convert the query result of the generated SQL statement into a chart

   /*polar4ai*/SELECT * FROM PREDICT (MODEL _polar4ai_nl2chart, SELECT merchtype AS merchant_type, COUNT(*) AS number_of_merchants FROM hkrt_merchant_info GROUP BY merchtype) WITH (usr_query = 'Merchant type statistics', result_type='IMAGE');

   The result is as follows:

   Note

   The returned link is an image URL that is valid for 90 minutes.

   http://db4ai-xxx-xx-xxxx-xxx-xxxx.aliyuncs.com/pc-bpze47ma2c515087l6/OSSAccessKeyId=xxxxxxx&Expires=1716130199&Signature=KvPFzfMebIEmqxPIXURurwwbsXM%3D

   

3. (Optional) Chart type selection and forced selection

   The model selects an appropriate chart based on its understanding of the user's question and the data. We recommend using the user's question to guide the model in generating the chart.

   The following table shows the mapping between question types and chart types:

   Question type	Chart type	Example user question	Description
   Quantity statistics	Column chart	"Please provide statistics on sales by city"	Shows numerical comparisons between different categories, such as quantity, total amount, or frequency.
   Trend change	Line chart	"Please show the user growth trend over the past year"	Shows the trend of data over time or across ordered categories, emphasizing continuity.
   Proportion distribution	Pie chart	"Please show the sales proportion of each product line"	Suitable for showing the proportional relationship of parts to a whole. The data must be categorical and have a clear total.

   Force a specific chart type by modifying the usr_query parameter. Add a supplementary command to the end of the usr_query parameter:

   -- Enter the output SQL into nl2chart to draw a line chart
   /*polar4ai*/SELECT * FROM PREDICT (MODEL _polar4ai_nl2chart,
   SELECT merchtype AS merchant_type, COUNT(*) AS number_of_merchants FROM hkrt_merchant_info GROUP BY merchtype
   ) WITH (usr_query = 'Merchant type statistics, draw a line chart', result_type='IMAGE');

   

   -- Enter the output SQL into nl2chart to draw a pie chart
   /*polar4ai*/SELECT * FROM PREDICT (MODEL _polar4ai_nl2chart,
   SELECT merchtype AS merchant_type, COUNT(*) AS number_of_merchants FROM hkrt_merchant_info GROUP BY merchtype
   ) WITH (usr_query = 'Merchant type statistics, draw a pie chart', result_type='IMAGE');

   

For more information, see NL2Chart: Generate smart charts from natural language.