Generate smart charts from natural language with NL2Chart - PolarDB

After NL2SQL generates an SQL statement, you can view the query results. For a more intuitive visualization, you can display the results as a column chart, pie chart, or line chart. The NL2Chart feature in PolarDB converts SQL query results into dynamic reports to help you understand and analyze data more efficiently.

Overview

The core flow of NL2Chart consists of two stages. First, it uses NL2SQL technology to convert a natural language query into an SQL statement. Then, it uses the parsed SQL statement to generate a visual analytics report.

For example, if 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');

The generated SQL statement is as follows:

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

You can then use the NL2Chart feature.

Syntax

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

Parameter description

Parameter	Description	Example
usr_query	The user's question, which specifies the requirements for generating the chart.	'Quarterly sales statistics for 2023'
result_type	Specifies the return result type. Currently, only `'IMAGE'` is supported.	`'IMAGE'`
SQL_statement	The SQL query statement generated by the NL2SQL module to retrieve data.	`SELECT quarter, sales FROM sales_data WHERE year = 2023`

Example: Converting SQL query results into a chart.

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

The following result is returned:

http://db4ai-xxx-.aliyuncs.com/pc-xxx/OSSAccessKeyId=xxx&Expires=1716130199&Signature=KvPFzxxx

Note

The returned link is a URL for a PNG image. This URL has no permission restrictions and is valid for only 90 minutes. To check the exact expiration time, see the Expires field in the URL.

Chart type selection and override

The model selects a suitable chart type based on its understanding of the user's query and the data. You can use the user's query to guide the model in creating the chart.

The following table describes the mapping between query types and chart types.

Question type	Chart type	Example user question	Description
Count statistics	Column chart	"Count the sales in each city"	Shows numerical comparisons between different categories, such as quantity, total amount, and frequency.
Trend changes	Line chart	"Show the user growth trend over the past year"	Shows how data changes over time or across ordered categories. It emphasizes continuity.
Proportion distribution	Pie chart	"Show the sales proportion of each product line"	Shows the proportion of parts to a whole. This requires categorical data with a clear total.

To force the model to use a specific chart type, you can modify the usr_query parameter by adding a command to the end of its value.

-- 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 merchant_count 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 merchant_count FROM hkrt_merchant_info GROUP BY merchtype
) WITH (usr_query = 'merchant type statistics, draw a pie chart', result_type='IMAGE');

Best practices

You can use the NL2Chart and NL2SQL features together to automatically generate charts from natural language queries.

Note

Ensure that your data is compatible with the chart type. For example, pie charts require proportion data, and line charts require ordered time series data.

Automatic chart type matching: The system recommends a suitable chart type based on keywords in the query, such as 'count', 'trend', or 'proportion'.
Manual chart type specification: If the default recommendation does not meet your needs, you can explicitly specify the chart type by modifying the usr_query parameter.

By following these recommendations, you can efficiently generate the statistical charts that you need.