Oracle was the first database I developed with, so I know that thinking about switching to something like NoSQL and leaving behind the relational model can seem like a scary thing. The thing is, it really wasn’t scary when I finally opted to make the switch to the NoSQL document model that Couchbase offers. This is because unlike other NoSQL databases, Couchbase offers a SQL-like language that would seem very familiar to an RDBMS user.
To make the transition easier, we’re going to walk through a scenario where you’re using an Oracle RDBMS and would like to transition away to Couchbase.
Key Differences Between Oracle and Couchbase
Coming from Oracle, I take it you already know that it is a relational database that consists of tables, rows, and columns. Pretty standard when it comes to any relational database. This is not the case with a NoSQL document database like Couchbase. Instead you’re working with JSON objects and arrays that have no structure and pretty much no limitations.
Although data modeling is, in my opinion, the largest difference, it isn’t the only one. However, let’s start there.
The Relational Database Data Model
To keep things simple and easy to follow in this article, let’s assume we have the following tables:
- customer
- id: numeric primary key
- first_name: varchar
- last_name: varchar
- customer_history
- id: numeric primary key
- product_id: numeric foreign key
- quantity: numeric
- customer_address
- id: numeric primary key
- customer_id: numeric foreign key
- city: varchar
- state: varchar
- product
- id: numeric primary key
- name: varchar
- description: varchar
- product_review
- id: numeric primary key
- product_id: numeric foreign key
- customer_id: numeric foreign key
- review: varchar
The above tables and columns are not the most complex, but they prove the relational model. They are all connected by the use of primary and foreign key relationships.
Options for a NoSQL Data Model
Because NoSQL is schema-less, there are multiple ways to model the data we just saw in Oracle.
Referring Documents
Referring documents will probably seem most familiar to you in terms of relational data. In an RDBMS like Oracle, you are referring to other rows of data through primary and foreign keys. There is no concept of a primary or foreign key in NoSQL, but that doesn’t mean you can’t rig together the same kind of relationship.
For example, take the following NoSQL documents:
c::1
|
1 2 3 4 5 6 7 |
{ "type": "customer", "first_name": "Nic", "last_name": "Raboy" } |
ca:1
|
1 2 3 4 5 6 7 8 |
{ "type": "customer_address", "customer_id": "c::1", "city": "San Francisco", "state": "California" } |
Assume the above documents are modeled similarly to their RDBMS equivilent. c::1 is just some id value I made up for the customer document and ca:1 is an id I made up for the customer_address document.
Now although we won’t query them yet, we can think of these documents as each being the equivalent of a single row in a relational database. For example one row of the customer Oracle table would be one document in Couchbase.
Very similar, correct?
Embedding Documents
This is where things can become very different coming from Oracle. Being that JSON is complex data, we can have arrays within our documents. So what if we wanted to keep all like data together?
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
{ "type": "customer", "first_name": "Nic", "last_name": "Raboy", "addresses": [ { "city": "San Francisco", "state": "California" }, { "city": "Mountain View", "state": "California" } ] } |
What do you think of that? With the above, instead of one address per document, we are now storing all addresses for any particular customer, with the customer data.
You might be wondering what happens if you have very complex relationships in your Oracle data that, when transposed to Couchbase, would result in the same data being embedded in more than one Couchbase document. This could happen, but it isn’t a bad thing. You don’t need normalized data in a NoSQL database such as Couchbase. However, if you’re really concerned, why not mix both approaches? Keep data such as customer_history together without relationships and refer to others that might change more frequently.
Query Differences Between Oracle and Couchbase
Oracle SQL vs Couchbase N1QL
It’s no secret that Oracle uses its own flavor of traditional SQL to query data in the database, but it is SQL. For example, if you wanted to return all product reviews and expand the relational tables around them, you would do a query like this:
|
1 2 3 4 5 6 7 |
SELECT c.firstname, c.lastname, p.name, r.review FROM review r JOIN customer c ON r.customer_id = c.id JOIN product p ON r.product_id = p.id |
What if I told you that you could do almost the same thing with Couchbase NoSQL data? Take the following Couchbase N1QL query:
|
1 2 3 4 5 6 7 |
SELECT c.firstname, c.lastname, p.name, r.review FROM `bucket-name` r JOIN `bucket-name` c ON KEYS r.customer_id JOIN `bucket-name` p ON KEYS r.product_id |
Not too different right? You might notice that we’re using bucket-name three times. This is because there are no tables in NoSQL and all the different documents and document types will exist in the same bucket. The document key is the value that we join on.
Maybe you want to insert new data into the Oracle customer table. In Oracle, you might do something like this:
|
1 2 3 4 |
INSERT INTO customer (id, first_name, last_name) VALUES (1, 'Arun', 'Gupta'); |
If you wanted to insert data in Couchbase you can do the following:
|
1 2 3 4 |
INSERT INTO `bucket-name` (KEY, VALUE) VALUES (1, {"first_name": "Arun", "last_name": "Gupta"}); |
Development Differences Between Oracle and Couchbase
From the developer perspective, many who use Oracle as their database tend to use Java. Oracle isn’t restricted to just Java, but since Java is Oracle, it often makes sense. This is why the next few examples will be based around Java specifically.
The Oracle JDBC Driver
In a Java application, if you wanted to connect to an Oracle database you’d use the Java Database Connector (JDBC) driver. With the driver included in your project, either through tools like Maven or manually, you can load it and start querying for data.
An example of this might look like the following:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Class.forName("oracle.jdbc.driver.OracleDriver"); Properties info = new Properties(); info.put("user", "nraboy"); info.put("password", "password"); Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@//HOST:PORT/DATABASE", info); Statement stmt = connection.getConnection().createStatement(); ResultSet rs = stmt.executeQuery("SELECT * FROM customer"); while(rs.next()) { // rs.getString("first_name"); } stmt.close(); |
The Couchbase Java SDK
To connect to Couchbase via a Java application you would use the Couchbase Java SDK rather than a JDBC driver, even though one does exist. The reason we would use the SDK is because things become incredibly easy with it.
For example, with the Couchbase SDK, the same kind of operation as Oracle might look like the following:
|
1 2 3 4 5 6 7 8 |
CouchbaseCluster.create(HOST).openBucket(BUCKET, PASSWORD); String query = "SELECT * FROM `bucket-name-here`"; N1qlQueryResult queryResult = bucket.query(query); for (N1qlQueryRow row : queryResult) { // row.value().toMap(); } |
The above assumes, of course, that you downloaded the Couchbase Java SDK or used Maven to obtain it.
Tool Differences
When using Oracle you have many tools that you can use. For example, if you want to execute queries against the database you could use the command line tool SQLPlus. You still have the ability to use comparable tools when making the switch to Couchbase. If you’re looking for a command line tool, you can use CBQ to query your data. If you’re a power user of Oracle’s SQL Developer, don’t worry because Couchbase has its Query Workbench in the works.
Data Migration Tools
When it comes to your data, you might be wondering how you might get your data out of Oracle and into Couchbase as quickly as possible. Manuel Hurtado wrote an excellent blog article for moving data from Oracle to Couchbase.
In short, Manuel’s post walks through using a Java utility called oracle2couchbase. This tool will export rows of a table into JSON documents.
Conclusion
Even though Oracle and Couchbase are two very different database platforms, there are enough similarities to where a switch wouldn’t be so difficult to do. The relational data model can still be preserved to an extent using referred documents and these documents can still be queried using a SQL-like language similar enough to Oracle SQL that would make you feel right at home.
If you’re an Oracle user, Couchbase should not be dismissed. The way data exists now is different than it did twenty years ago. Having the flexibility of NoSQL is great for the future.
[…] Moving from Oracle database to Couchbase […]