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.

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.

• $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.

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.