Build a ChatBI app with Dify and NL2SQL

ChatBI, built on Dify on Data Management Service (DMS) with NL2SQL, redefines data analytics. ChatBI uses AI tools to understand user questions and respond in natural language. It then converts natural language into SQL using NL2SQL, executes the SQL in Dify, and displays the results visually. This ChatBI solution extends data analysis capabilities beyond technical teams to all employees, improves business response speed, and makes it easier to gain data insights.

Prerequisites

You have deployed Dify.
Ensure your Dify on DMS instance is v1.4.3 or later. For more information, see View the Dify version.

Step 1: Install the AliyunDMS plugin

Log in to DMS 5.0.
Move the pointer over the icon in the upper-left corner and choose All Features > Data+AI > Dify.

Note
If you use the DMS console in normal mode, choose Data+AI > Dify in the top navigation bar.
Click the name of the target workspace.
In the left-side navigation pane of the workspace, choose > Studio.
In the upper-right corner, click Plugins, and then click Install plugin.
Select the installation source and install the Qwen and AliyunDMS plugins.
- Install the Qwen plugin from Marketplace.
- Install the AliyunDMS plugin from DMS Plugins.
Wait for the plugins to be installed.
When the AliyunDMS and Qwen plugins appear in the plugin list, the installation is successful.

Step 2: Authorize the AliyunDMS plugin

Click the AliyunDMS plugin, and then click Authorize.
Depending on the plugin edition that you want to use, decide whether to configure the DMS API key.
Note
- If you only need to use the Basic Edition of the DMS SQL Executor, you do not need to enter a DMS API key. Click Save.
- If you need to use the Enterprise Edition plugins, such as the Enterprise SQL Executor or the Enterprise NL2SQL algorithm, you must enter a DMS API key. The DMS API key consists of the AccessKey ID and AccessKey Secret of the Alibaba Cloud account that is currently logged on to DMS. The format is <AccessKey ID>:<AccessKey Secret>.
Click Save.
When Authorized appears on the component page, the authorization is successful.

Step 3: Configure an API key for the Qwen model

On the Studio page, click your account name and choose Settings.
On the Model Providers page, click Set next to the Qwen model and configure its API key.
Go to Alibaba Cloud Model Studio to get an API key.
Click Save.

Step 4: Apply the ChatBI template

On the Studio > All page, click Create from Template.
In the left-side navigation pane, click DMS Data+AI.
DMS provides a series of built-in templates.
Hover over the ChatBI Quick Start (Integrated Edition) card, and then click Use This Template that appears on the card.
In the dialog box that appears, click Create.
When the ChatBI Quick Start block appears on the page, the creation is successful.

Step 5: Run the demo

Click the ChatBI Quick Start template.
The ChatBI template has a built-in demo dataset. You can click Preview in the upper-right corner to try out the ChatBI Agent.
Click Preview in the upper-right corner to try out the ChatBI Agent.
You can ask the agent questions to query related data.
In the preview window, the ChatBI Agent sends a welcome message and provides recommended questions, such as Tell me what data is in the table, How many rows of data are in the table?, What are the sales volume and price trends for October 2023?, and What are the top 10 product categories by sales volume and their sales figures?. You can enter a question in the input box at the bottom and click the Send button to start a conversation.

Step 6: Configure your own ChatBI application

Click to edit the db_connection_url variable. Replace its value with the internal or public endpoint of the target database.
Click the ENV button in the top toolbar to open the environment variables panel. Then, click the pencil icon next to the db_connection_url entry to edit it.
Note
The db_connection_url is a global environment variable used in the workflow. When the variable value is demo_db, the built-in dataset of the plugin is automatically loaded.
The supported database types and endpoint formats are as follows:
- MySQL: mysql+pymysql://<user>:<password>@<host>:<port>/<database>
- PostgreSQL: postgresql+psycopg2://<user>:<password>@<host>:<port>/<database>
- SQL Server: mssql+pymssql://<user>:<password>@<host>:<port>/<database>
- Oracle: oracle+oracledb://<user>:<password>@<host>:<port>/<service_name>
- Clickhouse: clickhouse+native://<user>:<password>@<host>:<port>/<database>
- MongoDB: mongodb://<user>:<password>@<host>:<port>/<database>
Click Save.
Based on your database connection method, add the egress IP or internal IP address of Dify to the database allowlist.
- If you need to connect to the database over the internet, add the Dify public egress IP to the database allowlist.
  Run the Query DIFY Public Egress IP node to get the IP address.
  This workflow contains both an AliyunDMS node and a Query DIFY Public Egress IP code node. In the workflow editor canvas, select and run this code node.
- If you connect to the database over a VPC, add the IPv4 CIDR block of the VPC where Dify is located to the database allowlist.
After adding the IP address, click Preview and ask the agent questions about the data in the target database.

AliyunDMS Enterprise and Basic plugins

Plugin name	Description	ChatBI Enterprise edition	ChatBI Basic edition
DMS NL2SQL (Basic Edition)	Built-in NL2SQL algorithm (Agile Edition). An agile natural language-to-SQL algorithm with real-time, streaming output of SQL results.	√	√
DMS NL2SQL (Enterprise Edition)	Built-in NL2SQL algorithm (Enterprise Edition) with the following features: Provides a simple and intuitive interface for knowledge base configuration. Advanced natural language-to-SQL algorithm for precise query conversion. No database account or password configuration required.	√	×
DMS SQL Executor (Basic Edition)	Connects to a database and executes SQL by using database credentials. Supports MySQL, PostgreSQL, SQL Server, Oracle, ClickHouse, and MongoDB databases.	√	√
DMS SQL Executor (Enterprise Edition)	Uses password-free authentication, which eliminates the need to configure database credentials in the workflow and prevents credential leakage. Supports connections to more than 30 mainstream data sources. Powerful enterprise-level control capabilities: Fine-grained resource access permission management. Complete audit trail. Strict SQL compliance checks.	√	×
DMS Data Visualization	Converts SQL execution results into ECharts charts, supporting bar charts, line charts, pie charts, and tables.	√	√

Use ChatBI Enterprise Edition

If you want to use the ChatBI Enterprise Edition template, select the ChatBI Quick Start (Enterprise Edition) template in Step 4: Apply the ChatBI template to create the corresponding application.

Notes

Before you use the features of ChatBI Enterprise Edition, ensure that the DMS API KEY for the AliyunDMS plugin is created by the Alibaba Cloud account that is currently logged in to DMS.

Enterprise Edition plugin configuration

DMS NL2SQL

Before you configure this plugin, add the target database instance to DMS and enable security hosting for the instance.

Parameter description:

Question: Enter the natural language query to be converted to SQL.
DMS-hosted database: Select the type and name of the database that is hosted in DMS.
DMS API KEY: The DMS API key required to call the plugin. If a value is entered here, it will override the DMS API key that was entered during plugin authorization.
Domain knowledge: Enter domain knowledge specific to your business scenario to improve the accuracy of NL2SQL.
Model: Select the large language model (LLM) used to generate SQL. By default, DMS-AI/XiYan-SQL-32B is used. If you select the adaptive model, the NL2SQL algorithm automatically selects a suitable model based on the difficulty of the query.
Privately deployed model: The ID of a privately deployed model.
Reasoning Effort: The reasoning effort for the NL2SQL algorithm. A higher effort level increases the time required to generate SQL. You can select Standard (Recommended), Enhanced, or Complex. The default setting is Standard (Recommended), which offers balanced performance.

DMS SQL Executor

Parameter description:

SQL: The SQL statement to be executed.
DMS-hosted database: Select the type and name of the database that is hosted in DMS.
DMS API KEY: The DMS API key required to call the plugin. If a value is entered here, it will override the DMS API key that was entered during plugin authorization.
Region ID: Select the region where Dify is located.
Output Format: You can select Markdown or JSON. The current default is Markdown.
Timeout: The timeout period for SQL execution.