Connect to OceanBase Database in DataV and retrieve data

DataV is a data visualization service provided by Alibaba Cloud that supports connecting to various data sources and offers a wide range of visualization components. You can quickly build data dashboards and visualization applications in DataV. By adding an RDS for MySQL data source, you can read data from OceanBase Database and use components such as charts and maps to visualize and analyze the data.

Version compatibility

• OceanBase Database: ≥ V4.2.1

Prerequisites

Before you use DataV, make sure that:

• You have activated the DataV service. For more information, see DataV official documentation.
• You have deployed OceanBase Database and created a MySQL user tenant. For more information, see Create a tenant.

Procedure

Step 1: Obtain the connection string of OceanBase Database

Contact the OceanBase Database deployment personnel to obtain the connection string. For example:

obclient -h$host -P$port -u$user_name -p$password -D$database_name

Parameter description:

• $host: the IP address for connection. For ODP connection, use the ODP address. For direct connection, use the OBServer IP address.

• $port: the connection port. For ODP connection, the default value is 2883. For direct connection, the default value is 2881.

• $database_name: the database name.

  Notice

  The user who connects to the tenant must have the CREATE, INSERT, DROP, and SELECT privileges on the database. For more information about user privileges, see Privilege types in MySQL mode.

• $user_name: the account for connection. For ODP connection, the format is User@Tenant#Cluster or Cluster:Tenant:User. For direct connection, the format is User@Tenant.

• $password: the password of the account.

For more information about the connection string, see Connect to an OceanBase tenant by using OBClient.

Example:

obclient -h$host -P2881 -u$user_name -p$password -D$database_name

Step 2: Add an OceanBase data source

DataV allows you to add an OceanBase data source over the Internet or the internal network.

Method 1: Add a data source over the Internet

1. Log in to the DataV console.

2. In the left-side navigation pane, choose Data Source Management. On the My Data tab, click Add Data.

3. In the Type list, select RDS for MySQL.

4. In the Network Type list, select Internet.

5. Specify the information of the RDS for MySQL data source. Internet configuration parameters:

   Parameter	Description
   Name	The display name of the data source. You can name it as you like.
   Domain Name	The public IP address or domain name of the database. This IP address or domain name allows the DataV server to access the database over the Internet or the internal network of Alibaba Cloud in some regions. For example, if you use an Alibaba Cloud RDS for MySQL instance over the Internet, the domain name is $domain. You can click Apply for Internet Access in the details page of the tenant in the OceanBase console and add the DataV Internet IP address to the allowlist. After the IP address is activated, you can use it.
   Port	The port of the database. The default value is 3306.
   Username	The username for logging in to the database.
   Password	The password for logging in to the database.
   Database	The name of the selected database.
   insecureAuth	If your MySQL version is low, you must enable this parameter to configure MySQL.

6. After you specify the information, click OK.

7. The added data source is displayed in the data source list.

Method 2: Add a data source over the internal network

1. Log in to the DataV console.

2. In the left-side navigation pane, choose Data Source Management. On the My Data tab, click Add Data.

3. In the Type list, select RDS for MySQL.

4. In the Network Type list, select VPC. In the Region list, select the region where the OceanBase instance is located.

5. Specify the information of the database. VPC configuration parameters:

   Parameter	Description
   Name	The display name of the data source. You can name it as you like.
   VPC ID	The ID of the VPC where the OceanBase instance is located. You can obtain this parameter by clicking the proxy address in the topology of the tenant details page in the OceanBase console. For example, if you want to access the database over the internal network, you need to obtain the VPC ID from the main address card. The VPC ID is specified in the main address card as 《VPC Network: $vpc_id》.
   Instance ID	The ID of the database instance in the VPC. You can obtain this parameter by clicking the proxy address in the topology of the tenant details page in the OceanBase console. For example, if you want to access the database over the internal network, you need to obtain the instance ID from the main address card. The instance ID is specified in the main address card as 《Private IP Address: $instance_id》.
   Domain Name	The internal IP address or domain name of the database. This IP address or domain name allows the DataV server to access the database over the Internet or the internal network of Alibaba Cloud in some regions. For example, if you use an Alibaba Cloud RDS for MySQL instance over the internal network, the domain name is $domain. You can click Configure Allowlist in the details page of the tenant in the OceanBase console and add the DataV internal network IP address to the allowlist. After the IP address is activated, you can use it.
   Port	The port of the database. The default value is 3306.
   Username	The username for logging in to the database.
   Password	The password for logging in to the database.
   Database	The name of the selected database.
   insecureAuth	If your MySQL version is low, you must enable this parameter to configure MySQL.

6. After you specify the information, the system automatically tests the connection to verify whether the database is accessible.

7. After the test is successful, click OK.

8. The added data source is displayed in the data source list.

Verify the result

After you add a data source, you can perform the following operations in DataV to verify the connection:

1. Create a visualization dashboard: In the DataV console, click Create Visualization and select a template or create a blank dashboard.

2. Add chart components: Drag and drop chart components (such as line charts, bar charts, and pie charts) from the left-side component library to the canvas.

3. Configure Data Source: Select the chart component, and in the Data tab of the right-side configuration panel, select the added OceanBase data source.

4. Write an SQL query: Write an SQL query in the SQL editor. For example:

   SELECT * FROM your_table LIMIT 100
   

5. Preview the data: Click View Data Response to confirm that you can successfully retrieve data from the OceanBase database.

6. Configure styles and interactions: Configure chart styles, colors, titles, and other settings as needed, and set interaction effects.

If the chart can display data from the OceanBase database, the connection between DataV and OceanBase is successful.