Data Management:Tutorial: Build a ChatBI application with Dify and NL2SQL for easy data analytics

Prerequisites

• You have successfully deployed Dify.

• Your Dify on DMS is running version V1.4.3 or later. For more information, see View the Dify version.

Step 1: Install the AliyunDMS plugin required by ChatBI

1. Log on to the DMS console V5.0.

2. Go to the Workspace Management page.

   You can choose one of the following methods to go to the workspace based on your requirements:

   Method 1

   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.

   

   Method 2

   In the left-side navigation pane, click the icon and then click Workspace.

   Note

   If you use the DMS console in normal mode, choose Digital Intelligence Factory > Workspace in the top navigation bar.

3. Click the name of the target workspace.

4. In the left-side navigation pane, choose > Studio.

5. In the upper-right corner, click Plugins, and then click Install plugin.

6. Select the installation source and install the TONGYI and AliyunDMS plugins.

   • Install the TONGYI plugin from Marketplace.

   • Install the AliyunDMS plugin from DMS Plugins.

7. Wait for the plugins to be installed.

   The installation is successful when the AliyunDMS and TONGYI plugins appear in the plugin list.

Step 2: Authorize the AliyunDMS plugin

1. Click the AliyunDMS plugin, and then click API Key Authorization Configuration.

2. Decide whether to configure the DMS API Key based on the plugin edition you need.

   Note

   • Basic Edition: No API key needed. 

   • Enterprise Edition: Requires DMS API key in <AccessKeyID>:<AccessKeySecret> format. The AccessKey ID and AccessKey secret must be associated with your Alibaba Cloud account.

3. Click Save.

   The authorization is successful when 1 Authorization appears on the component page.

Step 3: Configure an API key for the Qwen model

1. On the Studio page, click your account name and choose Settings.

2. On the Model Provider page, click Set next to the TONGYI (Qwen) model to configure its API Key.

   You can obtain an API key from Alibaba Cloud Model Studio.

3. Click Save.

Step 4: Apply the ChatBI template

1. On the Studio > All page, click Create From App Template.

2. In the template navigation pane on the left, click DMS Data+AI.

   This category contains a series of templates provided by DMS.

3. Move the pointer over the Create app from ChatBI Plug-and-Play (Integrated Edition V6) card, and then click Use this template.

4. In the dialog box that appears, click Create.

   The creation is successful when the ChatBI Plug-and-Play block appears on the page.

Step 5: Run the demo and try the ChatBI Agent

1. Click the ChatBI Plug-and-Play template.

   The ChatBI template includes a built-in demo dataset.

2. In the upper-right corner, click Preview to try the ChatBI Agent.

   Ask the Agent questions to query data.

Step 6: Configure your own ChatBI Agent

1. Click , edit the db_connection_url variable, and change its value to the internal or public endpoint of the target database.

   Note

   db_connection_url is a global environment variable used in the workflow. When set to 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>".

2. Click Save.

3. Depending on the database connection method, add the egress IP address or internal IP address of Dify to the database whitelist.

   • If you connect to the database over the internet, add the public egress IP address of Dify to the database whitelist.

     Run the Query DIFY Public Egress IP node to obtain the specific IP address.

   • If you connect to the database through a VPC, add the IPv4 CIDR block of the VPC where Dify resides to the database whitelist.

4. After you add the IP address, click Preview and ask the Agent questions about the data in your target database.

Features of the AliyunDMS enterprise and basic edition plugins

Use the AliyunDMS enterprise edition plugins

To use the AliyunDMS enterprise edition plugins, select the ChatBI Plug-and-Play (Enterprise Edition V6) template in Step 4: Apply the ChatBI template to create the application.