SQL2Chart (Legacy)-PolarDB(PolarDB)-阿里云帮助中心

After you generate an SQL statement using the natural language to SQL (NL2SQL) feature, you can use SQL2Chart to visualize the query results. SQL2Chart executes the SQL statement and converts the results into a dynamic report, such as a column chart or a pie chart. This helps you understand and analyze data more efficiently.

Note

For an improved experience, PolarDB now offers the NL2Chart (natural language generation for intelligent charts) feature.

Quick start

After you add an AI node to your PolarDB cluster, you can use the SQL2Chart feature. This feature converts an executable SQL statement in PolarDB into an image or an HTML snippet.

Assume that you have a table named employee_salary in your database with the following content:

id	name	month	salary
1	Zhang San	April	2000.00
2	Zhang San	May	1500.00
3	Li Si	May	1500.00

You also create a pattern table. This table is used for both PolarDB NL2SQL and PolarDB SQL2Chart:

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',
  `chart_setting` text COMMENT 'Chart configuration',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Insert a record into the pattern table:

INSERT INTO `auto_test`.`polar4ai_nl2sql_pattern` (`id`, `pattern_question`, `pattern_description`, `pattern_sql`, `pattern_params`, `chart_setting`) VALUES (5, 'What are the salaries of #{name} in April and May?', 'What are the salaries of [Name] in April and May?', 'select month,salary from employee_salary where name=#{name}', '[{\'table_name\':\'employee_salary\'}]','{\'chart_type\':\'bar\',\'x_axis\':\'month\',\'y_axis\':\'salary\',\'x_label\':\'Month\',\'y_label\':\'Salary\',\'title\':\'Monthly Salary Chart\'}');

Then, run a single SQL statement to retrieve the image link for the corresponding SQL statement:

/*polar4ai*/SELECT * FROM PREDICT (MODEL _polar4ai_data2chart, select `month`,salary from employee_salary where name='Zhang San') WITH (setting_table_name='polar4ai_nl2sql_pattern', setting_id='3', result_type='IMAGE')

Note

In this SQL statement, you can obtain the `setting_id` from the result returned by NL2SQL.

Example:

/*polar4ai*/SELECT * FROM PREDICT (MODEL _polar4ai_nl2sql, 
 SELECT 'What is the salary of Zhang San?') WITH (basic_index_name='schema_index',pattern_index_name='pattern_index',
 pattern_index_threshold=0.1,with_pattern_id=1);

Running the preceding SQL statement returns the SQL statement and the `pattern_id`. You can use the returned `pattern_id` as the `setting_id` in SQL2Chart.

The following image link is returned:

http://db4ai-aiengine-cn-hangzhou-dataset-pre.oss-cn-hangzhou.aliyuncs.com/pc-2ze47ma2c515087l6/OSSAccessKeyId=xxxxxxx&Expires=1716130199&Signature=KvPFzfMebIEmqxPIXURurwwbsXM%3D

Open the link to view the following image:

Usage

PolarDB SQL2Chart supports two modes:

Online SQL2Chart: Generates an image or HTML file online to display the execution results of a specified SQL statement based on the provided SQL and `chart_setting`.
Note
The generated charts can be pie charts, line charts, or column charts.
Offline SQL2Chart: Generates an image or HTML file to display the execution results of a specified SQL statement. The chart type and parameters are inferred offline based on the provided SQL.

Online SQL2Chart

Syntax

Return an HTML file

Run the following SQL statement to return an HTML file:

/*polar4ai*/SELECT * FROM PREDICT (MODEL _polar4ai_data2chart, select `month`,salary from employee_salary where name='Zhang San') WITH (setting_table_name='polar4ai_nl2sql_pattern', setting_id='5', result_type='HTML')

Note

The returned link has no permission restrictions but expires after one hour.

Return a PNG image

Run the following SQL statement to return a PNG image:

/*polar4ai*/SELECT * FROM PREDICT (MODEL _polar4ai_data2chart, select `month`,salary from employee_salary where name='Zhang San') WITH (setting_table_name='polar4ai_nl2sql_pattern', setting_id='5', result_type='IMAGE')

Parameters

Parameter Name	Description
setting_table_name	The pattern table that contains the `chart_setting`.
setting_id	The ID that corresponds to the `chart_setting`.
result_type	The type of the generated chart. Valid values are HTML and IMAGE.
sql_fetching	`sql_fetching=0` (default): Uses the input SQL statement directly. /polar4ai/SELECT * FROM PREDICT (MODEL _polar4ai_data2chart, SELECT `month`,salary FROM employee_salary WHERE name='Zhang San') WITH (setting_table_name='polar4ai_nl2sql_pattern', setting_id='5', result_type='IMAGE',sql_fetching=0) This SQL statement uses `select `month`,salary from employee_salary where name='Zhang San'` to draw the chart. `sql_fetching=1`: Uses the input SQL statement to fetch the actual SQL statement for drawing the chart. Example: `/polar4ai/SELECT * FROM PREDICT (MODEL _polar4ai_data2chart, SELECT sql_text FROM msg_log WHERE id=2) WITH (setting_table_name='polar4ai_nl2sql_pattern', setting_id='5', result_type='IMAGE',sql_fetching=1)` This SQL statement fetches the SQL statement from `select sql_text from msg_log where id=2` and then uses the fetched SQL statement to draw the chart.

Generate a pie chart

Syntax

If you generate a pie chart, the sample parameters for `chart_setting` in the `polar4ai_nl2sql_pattern` table are as follows:

{'chart_type':'pie','labels':'month','content':'salary','x_label':'Month','y_label':'Salary','title':'Monthly Salary Chart'}

Parameters

Parameter	Description
labels	The column in the query result that corresponds to the labels. It is used for categorization or grouping.
content	The column in the query result that corresponds to the data. It is used to display specific values or content.
x_label	The display name of the label column. It is used as the label for the x-axis in the chart.
y_label	The display name of the data column. It is used as the label for the y-axis in the chart.
title	The title of the image. It is used to describe the content or subject of the chart.

For the sample data and parameters, run the following SQL statement:

/*polar4ai*/SELECT * FROM PREDICT (MODEL _polar4ai_data2chart, select `month`,salary from employee_salary where name='Zhang San') WITH (setting_table_name='polar4ai_nl2sql_pattern', setting_id='5', result_type='IMAGE')

After you run the statement, a link to the following image is generated:

Generate a line chart

Syntax

If you generate a line chart, the sample parameters for `chart_setting` in the `polar4ai_nl2sql_pattern` table are as follows:

{'chart_type':'line','x_axis':'month','y_axis':'salary','x_label':'Month','y_label':'Salary','title':'Monthly Salary Chart'}

Parameters

Parameter name	Parameter description
labels	The target column in the query results. This column is used for categorization or grouping.
content	The data column in the query results. It displays specific values or content.
x_label	The display name of the target column. This name is used as the label for the x-axis of the chart.
y_label	The display name of the data column. This name is used as the label for the y-axis of the chart.
title	The title of the chart. It describes the chart's content or subject.

Run the following SQL statement with the sample data and parameters:

/*polar4ai*/SELECT * FROM PREDICT (MODEL _polar4ai_data2chart, select `month`,salary from employee_salary where name='Zhang San') WITH (setting_table_name='polar4ai_nl2sql_pattern', setting_id='5', result_type='IMAGE')

Running the statement generates a link to the following image:

Generate a column chart

Syntax

To generate a column chart, you can use the following sample parameters for `chart_setting` in the `polar4ai_nl2sql_pattern` table:

{'chart_type':'bar','x_axis':'month','y_axis':'salary','x_label':'Month','y_label':'Salary','title':'Monthly Salary Chart'}

Parameters

Parameter	Description
labels	The column in the query results that contains the labels for categorization or grouping.
content	The column in the query results that contains the data to display.
x_label	The display name for the label column. This name is used as the label for the x-axis of the chart.
y_label	The display name for the data column. This name is used as the label for the y-axis of the chart.
title	The title of the chart. It describes the content or subject of the chart.

Run the following SQL statement to use the sample data and parameters:

/*polar4ai*/SELECT * FROM PREDICT (MODEL _polar4ai_data2chart, select `month`,salary from employee_salary where name='Zhang San') WITH (setting_table_name='polar4ai_nl2sql_pattern', setting_id='5', result_type='IMAGE')

After you run the statement, the output is a link to the following image:

Offline SQL2Chart

Syntax

Return a PNG file

Run the following SQL statement to return a PNG file:

/*polar4ai*/SELECT * FROM PREDICT (MODEL _polar4ai_data2chart, select `name` as `Name`,salary as `Salary` from employee_salary where `month`='May') WITH (mode='async', result_type='IMAGE')

Return an HTML file

Run the following SQL statement to return an HTML file:

/*polar4ai*/SELECT * FROM PREDICT (MODEL _polar4ai_data2chart, select `name` as `Name`,salary as `Salary` from employee_salary where `month`='May') WITH (mode='async', result_type='HTML')

Note

The returned link does not have permission restrictions but expires after one hour.

Running the preceding SQL statement selects the data to be converted into a chart based on `select `name` as `Name`,salary as `Salary` from employee_salary where `month`='May'` and starts an offline conversion task.

Note

The data volume cannot exceed 50 columns and 10,000 rows.

After you run the SQL statement, a `task_id` is returned, such as `bce632ea-97e9-11ee-bdd2-492f4dfe0918`. You can then run the following SQL statement to check if the task is complete:

/*polar4ai*/show task `bce632ea-97e9-11ee-bdd2-492f4dfe0918`

When the `taskStatus` is `finish`, you can download the generated image from the `filePath`.

Parameters

In the `WITH` clause of the `SQL_DATA2CHART` statement, in addition to the required `mode='async'` parameter, the following parameter is also supported:

result_type: Specifies the type of chart to return. Valid values are 'IMAGE' and 'HTML'.

If you do not specify this parameter, the default value is 'HTML'. The returned `filePath` is a link to a web page where you can edit the chart format.
If you set this parameter to 'IMAGE', the returned `filePath` is an image download link.

For the SQL statement in this example that returns a PNG image, the resulting image is shown below:

Extended usage

You can combine the SQL2Chart feature with the NL2SQL feature to implement NL2Chart.