Connect to OceanBase Database in Redash and retrieve data

Redash is an open-source data visualization platform that allows users to connect to various data sources by writing SQL queries and quickly generate charts and dashboards. This topic describes how to connect to OceanBase Database in Redash, retrieve data by using SQL queries, and create a visualization dashboard. Redash supports team collaboration, data sharing, and alert settings, helping data analysts and business users quickly gain insights from data.

Version compatibility

• OceanBase Database version: ≥ V4.3.5
• Redash version: ≥ 26.3.0

Prerequisites

Before you use Redash, make sure that the following conditions are met:

• Redash is installed. For more information, see the official Redash documentation.
• OceanBase Database is deployed and a MySQL user tenant is created. For more information about how to create a user tenant, see Create a tenant.
• A test data table is created for verification.

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. Use the ODP address for ODP connection; use the OBServer IP address for direct connection.

• $port: the connection port. The default value for ODP is 2883; the default value for direct connection is 2881.

• $database_name: the database name.

  Notice

  The user used to connect 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 connection account. The format for ODP is user@tenant#cluster or cluster:tenant:user. The format for direct connection is user@tenant.

• $password: the account password.

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: Deploy Redash

This example uses Redash 26.3.0. Run the following command in an environment where Git, Docker, and Docker Compose are installed:

git clone https://github.com/getredash/setup.git

cd setup

./setup.sh --version 26.3.0

If the deployment is successful, you see an output similar to the following:

✔ Container redash-postgres-1         Running 
✔ Container redash-redis-1            Running 
✔ Container redash-server-1           Started
✔ Container redash-worker-1           Started
✔ Container redash-adhoc_worker-1     Started
✔ Container redash-nginx-1            Started 
✔ Container redash-scheduled_worker-1 Started  
✔ Container redash-scheduler-1        Started

Step 3: Prepare test data for OceanBase Database

After you log in to OceanBase Database, execute the following statements to create a test table and insert sample data for later verification of the query and visualization features of Redash.

Create a test table:

-- If the table already exists, drop it to avoid errors.
DROP TABLE IF EXISTS students;

-- Create the students table.
CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'Student ID',
    name VARCHAR(50) NOT NULL COMMENT 'Name',
    age TINYINT UNSIGNED COMMENT 'Age',
    gender ENUM('Male', 'Female') DEFAULT 'Male' COMMENT 'Gender',
    class_name VARCHAR(50) COMMENT 'Class',
    score DECIMAL(5, 2) DEFAULT 0.00 COMMENT 'Score'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Student information table';

Insert test data:

INSERT INTO students (name, age, gender, class_name, score) VALUES
('Zhang San', 18, 'Male', 'Class 1', 85.50),
('Li Si', 19, 'Male', 'Class 1', 92.00),
('Wang Wu', 18, 'Female', 'Class 1', 78.50),
('Zhao Liu', 20, 'Male', 'Class 2', 88.00),
('Sun Qi', 19, 'Female', 'Class 2', 95.50),
('Zhou Ba', 18, 'Male', 'Class 2', 60.00),
('Wu Ji', 19, 'Female', 'Class 3', 82.00),
('Zheng Shi', 20, 'Male', 'Class 3', 76.50),
('Qian Shiyi', 18, 'Female', 'Class 3', 91.00),
('Feng Shier', 19, 'Male', 'Class 4', 89.50),
('Chen Shisan', 18, 'Female', 'Class 4', 94.00),
('Chu Shisi', 20, 'Male', 'Class 4', 72.00),
('Wei Wushiwu', 19, 'Female', 'Class 5', 86.50),
('Jiang Liushiwu', 18, 'Male', 'Class 5', 90.00),
('Shen Qishiwu', 19, 'Female', 'Class 5', 83.50),
('Han Bishiliu', 20, 'Male', 'Class 6', 79.00),
('Yang Shiyi', 18, 'Female', 'Class 6', 96.50),
('Zhu Erliu', 19, 'Male', 'Class 6', 81.00),
('Qin Erliu', 18, 'Female', 'Class 1', 88.50),
('You Erliu', 20, 'Male', 'Class 2', 93.00);

Step 4: Log in to the Redash console

1. In your browser, go to http://localhost.
2. If it is your first time using Redash, complete the administrator account registration as prompted.
3. After the registration is completed, log in to the Redash console using the administrator account.

Step 5: Connect to OceanBase Database

1. Log in to the Redash console and click Connect a Data Source.

2. In the data source type list, select MySQL.

3. Fill in the OceanBase Database connection information as prompted:

   • Host: the connection address of OceanBase Database.
   • Port: the connection port of OceanBase Database.
   • User: the database username.
   • Password: the database password.
   • Database Name: the database name.

4. Click Test Connection to test the connection.

5. After the test is successful, click Save to save the data source configuration.

Step 6: Create and execute a query

1. On the home page of the Redash console, click Create your first Query.

2. Select the OceanBase data source that you created.

3. Enter an SQL query statement, for example:

   SELECT class_name, AVG(score) AS avg_score
   FROM students
   GROUP BY class_name
   ORDER BY avg_score DESC;
   

4. Click Execute to execute the query.

5. After the query result is returned, save the query.

Step 7: Create a Dashboard

1. On the Redash console homepage, click Create your first Dashboard.
2. Enter the dashboard name and complete the creation.
3. On the Dashboard page, click Add Widget.
4. Select the saved query results and add them to the current Dashboard.
5. Choose the chart type and adjust the display options as needed to complete the visualization configuration.

Verify the results

After you complete the preceding steps, verify whether the following features are working properly:

• Successfully connect to OceanBase Database: You can connect to OceanBase Database in Redash and test the connection.
• Execute SQL queries: You can execute an SQL statement in the SQL Query editor and obtain the correct result.
• Create charts and dashboards: You can convert the query result into a chart and add the chart to a dashboard for visualization.