Connect to OceanBase Database by using Superset

This topic describes how to connect to OceanBase Database by using Superset.

Prerequisites

• You have locally configured and started Superset. For more information, see the Quickstart for Superset.
• You have installed OceanBase Database and created a MySQL tenant.
• You have prepared a dataset. The sample dataset used in this topic is sourced from Kaggle.

Procedure

Step 1: Obtain the OceanBase Database connection string

Contact the deployment personnel or administrator of OceanBase Database to obtain the connection string. The following is a connection string example:

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

The parameters are described as follows:

• $host: the IP address for connecting to OceanBase Database. It is the IP address of OceanBase Database Proxy (ODP) for connection through ODP, or the IP address of an OBServer node for direct connection.

• $port: the port for connecting to OceanBase Database. For connection through ODP, the default value is 2883, which can be customized when ODP is deployed. For direct connection, the default value is 2881, which can be customized when OceanBase Database is deployed.

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

  Notice

  The user for connecting to a 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 tenant account. For connection through ODP, two account formats are supported: username@tenant name#cluster name and cluster name:tenant name:username. For direct connection, the username@tenant name format is supported.

• $password: the password of the account.

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

Here is an example:

obclient -hxxx.xxx.xxx.xxx -P2881 -utest_user001@mysql001 -p****** -Dtest

Step 2: Connect to OceanBase Database from the Superset console

1. In the Superset console, click the plus sign (+) and choose Data > Connect database.

2. In the dialog box that appears, complete the following settings:

   1. In the Select a database to connect step, select a MySQL database.
   2. In the Enter the required MySQL credentials step, enter the connection information in the HOST, PORT, DATABASE NAME, USERNAME, and PASSWORD fields based on the connection string obtained in Step 1. You can enter a custom value for DISPLAY NAME.

3. Click CONNECT and then click FINISH.

Step 3: Import data and create a table

1. Log in to OceanBase Database by using the connection string obtained in Step 1 and create a table by using the following statement:

   CREATE TABLE earthquakes (
    Place VARCHAR(255),
    Latitude DECIMAL(10, 6),
    Longitude DECIMAL(10, 6),
    Country VARCHAR(100),
    Continent VARCHAR(100),
    Magnitude DECIMAL(3, 1)
   );
   

2. Use the LOAD DATA statement to import the prepared dataset to the table. Here is a sample statement:

   LOAD DATA INFILE '/path/earthquake_dataset.csv'
   INTO TABLE earthquakes
   FIELDS TERMINATED BY ','
   ENCLOSED BY '"'
   LINES TERMINATED BY '\n'
   IGNORE 1 LINES
   (Place, Latitude, Longitude, Country, Continent, Magnitude);
   

   If the dataset contains a large amount of data or the data to be imported needs to be converted, you can use obloader to import the dataset.

   After the dataset is imported, execute the following statement for verification:

   obclient> SELECT COUNT(*) FROM earthquakes;
   +----------+
   | COUNT(*) |
   +----------+
   |     1264 |
   +----------+
   1 row in set
   

Step 4: Create a dataset in Superset

1. Go back to the Superset console. Click the plus sign (+) in the upper-right corner and choose Data > Create dataset.

2. On the New dataset page, configure the following parameters.

   Parameter	Description
   DATABASE	Select the database name specified for DISPLAY NAME in Step 2.
   SCHEMA	Select the OceanBase database created in Step 1.
   TABLE	Select the table created in Step 3.

3. Click CREATE DATASET AND CREATE CHART.

What to do next

After you create a dataset, you can select a chart type, click CREATE NEW CHART to create a chart, and configure the chart as needed. For more information, see Superset documentation.