Moving SQL database content to Couchbase

Since the GA release of N1QL, we get a lot of questions about moving content from a SQL database to Couchbase. There are many different ways to do so. Today, I have chosen what is probably the simplest. I will transform each row of each table in a JsonDocument and store it in Couchbase. I will do my test with Postgres and their sample dataset inspired by MySQL Sakila sample. I will use Java, but the guidelines presented here are applicable to other languages.

Connecting to a running SQL database

Since I am using Java, I will implement Spring Boot and their JDBC package, which handles the db connection for me. All I have to do is set up the right dependencies and properties to configure the JdbcTemplate. This object makes running a SQL query a breeze.

Dependencies

To make sure you have everything configured neatly and automatically you need the following dependencies:

I am testing with Postgres but you could add any other driver supported by Spring JDBC. The spring-boot-starter-data-jpa will allow me to inject the preconfigured JdbcTemplate.

Configuration

To make sure the Spring framework finds your database, add the following properties to your configuration file (for example, src/main/resources/application.properties).

Of course you would need to fine-tune this according to the database you are using. Here I am using Postgres running on 192.168.99.100 with default port 5432. The name of the database I want to use is dvdrental.

Code

If everything is configured correctly you should be able to inject the JdbcTemplate and start querying your SQL DB.

Connecting to Couchbase

My goal is to move content from a SQL database to Couchbase, so we also need a Couchbase connection.

Dependencies

Working with Couchbase on your Java project requires you to add the following dependency:

This will give you access to the Couchbase Java SDK.

Configuration

A basic Couchbase configuration requires basically three properties: one server IP address, a bucket name, and a bucket password. Doing this in a Spring Boot fashion would look like this:

The properties hostname, bucket, and password can be added directly to your application properties file.

Code

With Couchbase, the equivalent granularity level of a database would be a bucket, which is where you store documents. With the previous configuration you can simply inject a bucket and start playing around.

Tables

At this point you have a connection to a SQL database and Couchbase. Now we can start moving things around. The easiest way to move data is to consider each row of each table as a document.

Getting the SQL schema

Let’s start by getting the schema of the database automatically using the JdbcTemplate. The interesting object here is DatabaseMetaData, which can give us the complete structure of the database. The API is not the easiest to use, but at least it’s documented.

I will map the result of the DatabaseMetaData query to a list of Table and Column. I have created the following Java class to do so:

It’s definitely not the most exciting code to write, but at the end you get a JSON representation of your SQL database tables.

Content

Here’s the fun part. This is where we start mapping a table row to a JsonDocument. The previous section puts us in a state where we can retrieve the name of all the tables. From one table name, we can create a SQL query that returns every row of the table.

Spring has a mechanism that allows you to define a RowMapper. For each row returned by the query, you can return the object you want. Since I am using Couchbase, I want a JsonDocument.

Following is an implementation example. This RowMapper needs a Table object previously defined; therefore, we have to implement the mapRow method. There are several things we need to do here.

The first task is to create a unique key. As rows are scoped by tables, some id can be exactly the same for rows in different tables. But documents are scoped by bucket, so we need to create a unique document key that reflects the row id and the table name. To keep track of where the document comes from, I will also add a _tableName field for the table name.

Then, the exciting step comes from the type mapping. JSON supports fewer types than a SQL database, so we have some conversion to do here. This is what the getJsonTypedValue method does. It makes sure most JDBC type can be converted to a native JSON type (String, number, boolean, array, object, null). At the end, we have a JsonDocument that can be saved in Couchbase.

With that RowMapper it makes things really easy. We can loop through the table’s name, run the query, and save the results in Couchbase. Doing this in a synchronous fashion would look like this:

But I prefer the async version:

Here I am not using the full potential of Rx; take a look at this function that writes a doc to Couchbase and handles timeout and error management.

For convenience, I have packaged all steps implemented and previously shown in a single project. All you have to do is make sure your properties file is configured right and run the importer:

を見てみよう。 README file for more information.

結論

Today we have learn how to move SQL content to Couchbase, but there is still some work to do. Next time I will tell you how to move the SQL business logic to the application layer.