Quick start with the relational distributed database: OceanBase

OceanBase is a relational distributed database capable of OLTP and OLAP operations on the same database cluster. This solution was designed and developed by the ANT/Alibaba group. The OceanBase database is MySQL/MariaDB compatible. In this article, I am going to install a standalone database server and configure the client to execute a few SQL queries to test it's capabilities.

Let's start with the definition of a relational distributed database. A relational (sometimes called SQL) distributed database contains entities like tables and other database objects that are spread across different, but interconnected, servers (nodes). Each server keeps its own portion or copy of the data from tables. Data consistency and fault tolerance between nodes (servers) are accomplished by the Raft or Paxos consensus algorithms. Examples of modern distributed relational (SQL) databases are CockroachDB and Google Cloud Spanner.

OceanBase is a new player in the world of distributed SQL databases with it's own ecosystem, which can be used to solve different IT problems. Moreover, it promises the following key features:

• Transparent Scalability: OceanBase cluster can be scaled out to 1,500 nodes transparently

• Ultra-fast Performance: the only distributed database that has refreshed both the TPC-C record, at 707 million tmpC, and the TPC-H record, at 15.26 million QphH @ 30000 GB.

• Real-time Operational Analytics: Online OLAP capabilities.

• Continuous Availability: It adopts the Paxos Consensus algorithm to achieve zero RPO and less than 8 seconds of RTO.

• MySQL Compatible. Compatible with MySQL and MariaDB.

From the above features, the OLTP and OLAP capabilities on single cluster caught my attention. So, I decided to take a quick look at OceanBase. My configuration is as below:

Now, let's start with a fresh standalone mini OceanBase database instance.

Step 1. Start an OceanBase database instance as a Docker container. Run the following script:

docker run -p 2881:2881 --name oceanbase-ce -e MODE=mini -d oceanbase/oceanbase-ce

The above command will deploy an instance of the OceanBase database on port 2881.

Step 2. Use MySQL CLI client to connect to the server. The client is also deployed as a Docker container on the same machine.

docker exec -it oceanbase-ce ob-mysql sys

Note that the root user with the sys tenant will be used to connect.

Step 3. Execute a few SQL queries. Execute the Show databases statement to list all databases on the current database server as follows:

obclient [oceanbase]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| LBACSYS            |
| mysql              |
| oceanbase          |
| ocs                |
| ORAAUDITOR         |
| SYS                |
| test               |
+--------------------+
8 rows in set (0.002 sec)

See the documentation for basic SQL operations on the OceanBase Database. Note that Oracle Mode SQL operations are only available on the Enterprise edition.

Step 4. Connect with the DBeaver SQL client. Open the DBeaver and click the new Database connection icon. A new wizard will be opened, and fill the following connection parameters:

Host: your Docker host IP address.

Port: 2881 by default

Database: test

Tenant: sys

Username: root

password: EMPTY

If everything goes fine with the connection, you are ready to execute queries for creating tables and so on.

Step 5. Create two tables: Emp and Dept :-)

CREATE TABLE dept
(
  deptno INT PRIMARY KEY,
  dname  VARCHAR(200),
  loc    VARCHAR(200)
);

CREATE TABLE emp
(
  empno    INT PRIMARY KEY,
  ename    VARCHAR(200),
  job      VARCHAR(200),
  mgr      VARCHAR(200),
  hiredate DATE,
  sal      INT,
  comm     INT,
  deptno   INT
);

CREATE INDEX ename_idx ON emp (ename);

The above DDL script will create two tables and one index on the ename column of the EMP table.

Step 6. Enter some data into the tables.

insert into dept (deptno, dname, loc) values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept (deptno, dname, loc) values(20, 'RESEARCH', 'DALLAS');
insert into dept (deptno, dname, loc) values(30, 'SALES', 'CHICAGO');

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values(7839, 'KING', 'PRESIDENT', null, null, 5000, null, 10);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values( 7698, 'BLAKE', 'MANAGER', 7839, null, 2850, null, 30);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values(7782, 'CLARK', 'MANAGER', 7839, null, 2450, null, 10);

Now, you can execute SQL queries on the two tables. Let's execute a SQL statement to find the count of employees by department.

select dname, count(*) count_of_employees 
from dept, emp 
where dept.deptno = emp.deptno 
group by DNAME 
order by 2 desc

The result should be as follows:

select dname, count(*) count_of_employees
    -> from dept, emp
    -> where dept.deptno = emp.deptno
    -> group by DNAME
    -> order by 2 desc;
+------------+--------------------+
| dname      | count_of_employees |
+------------+--------------------+
| ACCOUNTING |                  2 |
| SALES      |                  1 |
+------------+--------------------+
2 rows in set (0.003 sec)

It's enough for now. In the next few posts, I will try to describe and explain the nuts and bolts of OceanBase.

Resources:

1. Paxos, Raft, EPaxos: How Has Distributed Consensus Technology Evolved?