You may know Couchbase as the fastest, most scalable NoSQL platform available. However, did you know that Couchbase can also be a relational database? Couchbase is a data platform that can operate with multiple simultaneous personalities all in the same platform. It can be structured or unstructured. And it can be transactional or analytical. In this blog, we are going to look at moving data from Oracle to Couchbase.

Source Oracle Database

For a source database, we are going to use Swingbench. It is a synthetic workload generator for Oracle. It is self-contained and creates schemas that are reminiscent of what might be found in an actual production application. For this blog, we are going to use the Simple Order Entry (SOE) schema that is created by the utility. A visual representation of the SOE schema can be seen below. Generating the schema with a scale parameter of 1 results in a dataset with about 15 million records.

Oracle to Couchbase - swingbench schema

Data Import

To migrate the data from Oracle to Couchbase, we will use the cbperf demo tool. This utility primarily generates randomized synthetic schemas for Couchbase, but it also can import data into Couchbase via a plugin from external data sources. The Oracle plugin for cbperf will replicate all tables available to the connecting user, copy associated table data, and for each table, it will extract the columns that are indexed to recreate the indexes on Couchbase. 

Assuming that the SOE schema was set up with the default username and password, which is soe and soe, respectively, this syntax will export the data from the database SID testdb on host orahost and import it to the Couchbase bucket soe with scope soe where one node in the cluster is cbhost.

The Data in Couchbase

Let’s get the important disclaimer out of the way up front. For a production application, you will likely not want to just “lift and shift” the data and indexes. Instead, an analysis needs to be done to determine the optimal configuration in Couchbase along with the required application changes. The ultimate level of effort will vary by application.

That said, once the cbperf import is complete, there will be about 15 million documents in Couchbase. The soe scope will have collections that correspond to each table in Oracle.

Here are the tables in Oracle:

Oracle tables for Couchbase import

Here are the collections in Couchbase:

Couchbase collections set up for Oracle schema import

Each row that was in the Oracle database is now a document in Couchbase. The document key is constructed from the collection name plus a colon “:” plus the sequential document number (basically the row number). The document JSON keys are the table column names, and the values are the column values for that row.

Query in Oracle

Oracle document in Couchbase query

Finally, we can see all the indexes that were automatically generated based on the indexed columns in the Oracle database. To allow for the most query flexibility, each index covers one column. 

Oracle indexes imported into Couchbase

Running a Query

Looking through the Swingbench source code, I found the following query:

Running this query against the Oracle database, I get the following results:

Oracle query results

And running the query on Couchbase, I get the same results. The only thing I had to change in the query was to prefix the collections with soe.soe. to construct a Couchbase key space.

Couchbase query results from Oracle import

table of query results in Couchbase

Fun Fact

You may be familiar with the ORACLE_SID as the Oracle database instance identifier, but do you know what it does under the covers? It stands for the Site Identifier. It is hashed with ORACLE_HOME to get an SGA (System Global Area) key which is the shared memory key on Unix/Linux systems. When you get an ORA-01034: ORACLE not available error, it is because this shared memory key is not present on the system. Thus you can’t communicate with the database. Fortunately, working with Couchbase is vastly simpler, allowing you to focus on other things. Unless, of course, you like operating system internals.

Conclusion

While it might seem like a daunting task, importing data from a relational database into Couchbase is not that difficult. To be fair, there is more to consider than just columns and indexes, such as stored procedures (user-defined functions in Couchbase), but it is certainly very doable.

References

Author

Posted by Michael Minichino

Michael Minichino is a Principal Solutions Engineer at Couchbase

Leave a reply