Connect to OceanBase Database in Guanyuan BI

This article will use OceanBase Database in MySQL-compatible mode as an example to introduce how to connect to OceanBase Database in Guanyuan BI for data display.

Prerequisites

• You have purchased View+ BI.
• You have installed OceanBase Database and created a MySQL-compatible tenant.

Step 1: Obtain the connection string of OceanBase Database

Contact the deployment personnel or administrator of OceanBase Database to obtain the corresponding database connection string, for example:

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

Parameter description:

• $host: the IP address for connecting to OceanBase Database. If you connect through OceanBase Database Proxy (ODP), use the IP address of the ODP. If you connect directly, use the IP address of an OBServer node.

• $port: the port for connecting to OceanBase Database. If you connect through ODP, the default port is 2883, which can be customized when you deploy ODP. If you connect directly, the default port is 2881, which can be customized when you deploy OceanBase Database.

• $database_name: the name of the database to be accessed.

  Notice

  The user for connecting to the tenant must have the CREATE, INSERT, DROP, and SELECT privileges on the database.

• $user_name: the connection account of the tenant. For ODP connection, the common format is username@tenant name#cluster name or cluster name:tenant name:username. For direct connection, the format is username@tenant name.

• $password: the password of the account.

Step 2: Configure drivers and connectors in Guanyuan BI

1. Log in to the Guanyuan BI console and choose Resource Management > Driver and Connector Management in the upper-right corner.

2. On the Driver Management tab, click Add Driver in the upper-right corner. Edit the driver name and click Save.

3. Click the newly created driver OceanBase-MySQL, click Upload File, upload the driver Jar package and the available Driver Class, and click Apply to complete the driver configuration.

   Note

   To download the OceanBase (MySQL) driver Jar package, visit the OceanBase Software Download Center and select the appropriate package based on your CPU architecture.

4. On the Database Connector Management tab, click Create Database Connector. In the dialog box that appears, configure the database connector. Click OK after the configuration is complete.

   Configuration Item	Description
   Display ICON	Set the custom icon for the data connection. This icon will be displayed when creating a data account or a database dataset.
   Connector Name	Define the name according to your preference. Special characters are not allowed.
   Default Dialect Support	Select MySQL.
   Get Table List SQL	Enter {"sql": "SHOW TABLES", "tableNameIdx": 1}.
   Default Driver	Select the driver OceanBase-MySQL created in Step 3.

Step 3: Create a data account in Guanyuan BI

1. In the Guanyuan BI console, choose Data Preparation > Data Account.

2. Click Create Data Account and configure the data account:

   a. In the Select Connector section, select the connector OceanBase-MySQL created in Step 2. Click Next after the selection is complete.

   b. In the Configure Data Account section, configure the data account information. After the configuration is complete, click Test Connection to confirm whether the OceanBase database can be connected. Click Next after the confirmation.

   Configuration Item	Description
   Username	The username specified in the connection string, which is the account name.
   Password	The password specified in the connection string, which is the account password.
   JDBC URL	Concatenate the host, port, database name, username, and password from the connection string. The syntax is as follows: jdbc:oceanbase://host:port/databasename?[username&password] Example: jdbc:oceanbase://t5******.aws-ap-southeast-1.oceanbase.cloud:3306/test?user=test_user001@sys&password=***1**
   Maximum Connections	Set the maximum number of connections for a single data account.
   Driver	Select the driver OceanBase-MySQL created in Step 2.

   c. In the Confirm Data Account section, name the data account for the current database and click Confirm.

Step 4: Connect to a data source in Guanyuan BI

1. In the Guanyuan BI console, choose Data Preparation > Datasets in the upper-right corner, and then choose Create Dataset > Database.

2. Configure the dataset connection information:

   a. In the Select Data Account and Data Table section, select the data account OceanBase Account created in step 3, and then query data by using SQL. Click Next after you finish.

   b. In the Data Connection and Update Settings section, configure the data connection and update information. Click Next after you finish.

   c. In the Confirm Data Table Information section, confirm the query result, and configure the dataset name, save path, and other information. Click Create after you finish.

3. If the dataset appears in the dataset list, you have successfully connected to the data source.

What to do next

After you create a data source, you can start using Guanyuan BI. For more information about how to use Guanyuan BI, see Guanyuan BI Help Center.