Connect to OceanBase Database in Metabase

Metabase is a powerful and easy-to-use data visualization tool that helps users create and share dashboards. This topic describes how to connect to OceanBase Database in Metabase, configure a data source, and perform data visualization analysis. Metabase supports connecting to various data sources, including OceanBase Database, through a web interface, enabling data exploration, querying, and visualization.

Compatibility

• Metabase version: ≥ V0.48.13.
• OceanBase Database version: ≥ V4.2.5 BP2.

Prerequisites

Before using Metabase, make sure that:

• Java Runtime Environment (JRE) or Java Development Kit (JDK) is installed.
• OceanBase Database is deployed and a MySQL mode user tenant is created. 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 port for connection. For ODP connection, the default value is 2883. For direct connection, the default value is 2881.

• $database_name: the name of the database.

  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. For ODP connection, the 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.

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

Example:

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

Step 2: Create a database for Metabase

Metabase needs an independent database to store its own metadata, such as user information, question definitions, and dashboard configurations.
First, create a database in OceanBase Database to store the metadata of Metabase.

CREATE DATABASE metabase;

After the execution, record the name of the database for subsequent configuration of the Metabase metadata connection.

Step 3: Install Metabase

This example uses Metabase v0.48.13.

1. Download the Metabase JAR package: Download the latest version of the JAR package from the Metabase website, or use the following command to download a specific version:

   sudo wget https://downloads.metabase.com/v0.48.13/metabase.jar
   

2. Prepare the runtime environment: Make sure that Java Runtime Environment (JRE) is installed. We recommend that you use Java 11 or later.

Step 4: Configure Metabase to connect to OceanBase Database

Set environment variables: Before starting Metabase, configure its metadata database connection information by using environment variables.

export MB_DB_TYPE=mysql
export MB_DB_DBNAME=metabase
export MB_DB_PORT=$port
export MB_DB_USER=$user_name@$tenant_name
export MB_DB_PASS=$password
export MB_DB_HOST=$host

Parameter description:

• MB_DB_TYPE: the type of the database. Set it to mysql.
• MB_DB_DBNAME: the name of the database for storing Metabase metadata.
• MB_DB_PORT: the port for connecting to OceanBase Database.
• MB_DB_USER: the username for connecting to OceanBase Database (format: username@tenant name).
• MB_DB_PASS: the password for connecting to OceanBase Database.
• MB_DB_HOST: the IP address of OceanBase Database.

Step 5: Start and initialize Metabase

1. Execute the following command to start the Metabase service:

   java --add-opens java.base/java.nio=ALL-UNNAMED -jar metabase.jar
   

2. After the service is started, access http://<metabase_host>:3000 in your browser.

3. Complete the initialization settings of Metabase as prompted, such as administrator account creation and language settings.

Step 6: Add OceanBase Database as a data source in Metabase

After Metabase is initialized, you can add OceanBase Database as a data source.

1. Log in to the Metabase management console.

2. Choose Admin > Databases.

3. Click Add database.

4. In the database type list, select MySQL.

5. Fill in the connection information of the OceanBase Database as prompted:

   Fill in the connection information of the OceanBase Database:

   • Host: the IP address for connecting to OceanBase Database.
   • Port: the port for connecting to OceanBase Database.
   • Database name: the name of your business database.
   • Username: the username for connecting to OceanBase Database.
   • Password: the password for connecting to OceanBase Database.

6. Click Save.

Step 7: Upgrade Metabase (optional)

If you want to upgrade Metabase from v0.48.13 to v0.49.0:

1. Back up the Metabase metadata database.

2. Download the JAR package of the new version:

   sudo wget https://downloads.metabase.com/v0.49.0/metabase.jar
   

3. Stop the current Metabase service.

4. Restart Metabase by using the JAR package of the new version:

   java --add-opens java.base/java.nio=ALL-UNNAMED -jar metabase.jar
   

Verification

1. Connection test: On the database configuration page of Metabase, click Test Connection to confirm that the connection is successful.
2. Data browsing: Browse the tables and views of OceanBase Database in Metabase to confirm that you can access the data.
3. Query execution: Create a simple SQL query to verify that the query result is correctly returned.
4. Dashboard creation: Create a data visualization dashboard to confirm that the charts can display the data of OceanBase Database.

After the connection is successful, you can:

• Browse all tables and views in OceanBase Database.
• Use the query builder or native SQL to query data.
• Create charts, dashboards, and reports.
• Set data alerts and scheduled reports.