Connect to OceanBase Database by using Superset

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

Prerequisites

• You have configured and run Superset on your local machine. For more information, see Superset quickstart.
• You have installed OceanBase Database and created a MySQL tenant.
• You have prepared an available dataset for testing. The dataset used in this topic is from Kaggle.

Procedure

Step 1: Obtain a database connection string

Obtain a database connection string from a database deployment engineer or administrator. For example:

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

where

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

• $port indicates 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 indicates 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 indicates the tenant account. For connection through ODP, the tenant account can be in the Username@Tenant name#Cluster name or Cluster name:Tenant name:Username format. For direct connection, the tenant account is in the Username@Tenant name format.

• $password indicates the password of the account.

For more information about the connection string, see Connect to an OceanBase 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 on 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. You can 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 data.

   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 entered 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 creating a dataset, you can choose a chart type according to your preference and click CREATE NEW CHART to set up the chart. For more information on setting up the charts, see Superset documentation.