About Tugdual Grall

Tugdual "tug" is Technical Evangelist at Couchbase. He is passionate about development and software architecture using Java platform but also others one. Tug He is also co-founder of the Nantes Java User Group (NantesJUG) in France and developer of the Resultri site hosted on GAE.

SQL to NoSQL : Copy your data from MySQL to Couchbase

Introduction

During my last interactions with the Couchbase community, I had the question how can I easily import my data from my current database into Couchbase. And my answer was always the same:

  • Take an ETL such as Talend to do it
  • Just write a small program to copy the data from your RDBMS to Couchbase…

So I have written this small program that allows you to import the content of a RDBMS into Couchbase. This tools could be used as it is, or you can look at the code to adapt it to your application.

The Tool: Couchbase SQL Importer

The Couchbase SQL Importer, available here, allows you with a simple command line to copy all -or part of- your SQL schema into Couchbase. Before explaining how to run this command, let’s see how the data are stored into Couchbase when they are imported:

  • Each table row is imported a single JSON document
    • where each table column becomes a JSON attribute
  • Each document as a key made of the name of the table and a counter (increment)

The following concrete example, based on the MySQL World sample database, will help you to understand how it works. This database contains 3 tables : City, Country, CountryLanguage. The City table looks like:

+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   |     |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+

The JSON document that matches this table looks like the following:

city:3805
{ 
  "Name": "San Francisco",
  "District": "California",
  "ID": 3805,
  "Population": 776733,
  "CountryCode": "USA"
}

You see that here I am simply taking all the rows and “moving” them into Couchbase. This is a good first step to play with your dataset into Couchbase, but it is probably not the final model you want to use for your application; most of the time you will have to see when to use embedded documents, list of values, .. into your JSON documents.

In addition to the JSON document the tool create views based on the following logic:

  • a view that list all imported documents with the name of the “table” (aka type) as key
  • a view for each table with the primary key columns

View: all/by_type

{
  "rows": [
    {"key": "city", "value": 4079}, 
    {"key": "country", "value": 239}, 
    {"key": "countrylanguage", "value": 984}
   ]
}

As you can see this view allows you to get with a single Couchbase query the number of document by type.

Also for each table/document type, a view is created where the key of the index is built from the table primary key. Let’s for example query the “City” documents.

View: city/by_pk?reduce=false&limit=5

{
  "total_rows": 4079,
  "rows": [
    {"id": "city:1", "key": 1, "value": null}, 
    {"id": "city:2", "key": 2, "value": null}, 
    {"id": "city:3", "key": 3, "value": null}, 
    {"id": "city:4", "key": 4, "value": null},
    {"id": "city:5", "key": 5, "value": null}
  ]
}

The index key matches the value of the City.ID column.  When the primary key is made of multiple columns the key looks like:

View: CountryLanguage/by_pk?reduce=false&limit=5

{
  "total_rows": 984,
  "rows": [
    {"id": "countrylanguage:1", "key": ["ABW", "Dutch"], "value": null}, 
    {"id": "countrylanguage:2", "key": ["ABW", "English"], "value": null}, 
    {"id": "countrylanguage:3", "key": ["ABW", "Papiamento"], "value": null},
    {"id": "countrylanguage:4", "key": ["ABW", "Spanish"], "value": null},
    {"id": "countrylanguage:5", "key": ["AFG", "Balochi"], "value": null}
  ]
}

This view is built from the CountryLanguage table primary key made of CountryLanguage.CountryCode and CountryLanguage.Language columns.

+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| CountryCode | char(3)       | NO   | PRI |         |       |
| Language    | char(30)      | NO   | PRI |         |       |
| IsOfficial  | enum('T','F') | NO   |     | F       |       |
| Percentage  | float(4,1)    | NO   |     | 0.0     |       |
+-------------+---------------+------+-----+---------+-------+

How to use Couchbase SQL Importer tool? 

The importer is a simple Java based command line utility, quite simple to use:

  1. Download the CouchbaseSqlImporter.jar file from here. This file is contains all the dependencies to work with Couchbase: the Java Couchbase Client, and GSON.
  2. Download the JDBC driver for the database you are using as data source. For this example I am using MySQL and I have download the driver for MySQL Site.
  3. Configure the import using a proper tie file.
    ## SQL Information ##
    sql.connection=jdbc:mysql://192.168.99.19:3306/world
    sql.username=root
    sql.password=password
    
    ## Couchbase Information ##
    cb.uris=http://localhost:8091/pools
    cb.bucket=default
    cb.password=
    
    ## Import information
    import.tables=ALL
    import.createViews=true
    import.typefield=type
    import.fieldcase=lower

    This sample properties file contains three sections :

    • The two first sections are used to configure the connections to your SQL database and Couchbase cluster (note that the bucket must be created first)
    • The third section allow you to configure the import itself
      • import.tables : ALL to import all tables, or a the list of tables you want to import, for example City, Country
      • import.createViews : true or false, to force the creation of the views.
      • import.typefield : this is use to add a new attribute in all documents that contains the “type”.
      • import.fieldcase : null, lower, upper : this will force the case of the attributes name and the value of the type (City or city or CITY for example).
  4. Run the tool !
    java -cp "./CouchbaseSqlImporter.jar:./mysql-connector-java-5.1.25-bin.jar" com.couchbase.util.SqlImporter import.properties

    So you run the Java command with the proper classpath (-cp parameter).

And you are done, you can get your data from your SQL database into Couchbase. If you are interested to see how it is working internally, you can take a look to the next paragraph.

The Code: How it works?

The main class of the tool is really simple com.couchbase.util.SqlImporter, the process is:

  1. Connect to the SQL database
  2. Connect to Couchbase
  3. Get the list of tables
  4. For each tables execute a “select * from table”
    • Analyze the ResultSetMetadata to get the list of columns
    • Create a Java map for each rows where the key is the name of the columns and the value…is the value
    • Serialize this Map into a GSON document and save it into Couchbase

The code is available in the ImportTable(String table) Java method.

One interesting point is that you can use and extend the code to deal with your application.

Conclusion

I have created this tool quickly to help some people in the community, if you are using it and need new features, let me know, using comment or pull request.
 

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you two of our best selling eBooks for FREE!

JPA Mini Book

Learn how to leverage the power of JPA in order to create robust and flexible Java applications. With this Mini Book, you will get introduced to JPA and smoothly transition to more advanced concepts.

JVM Troubleshooting Guide

The Java virtual machine is really the foundation of any Java EE platform. Learn how to master it with this advanced guide!

Given email address is already subscribed, thank you!
Oops. Something went wrong. Please try again later.
Please provide a valid email address.
Thank you, your sign-up request was successful! Please check your e-mail inbox.
Please complete the CAPTCHA.
Please fill in the required fields.

Leave a Reply


3 × eight =



Java Code Geeks and all content copyright © 2010-2014, Exelixis Media Ltd | Terms of Use | Privacy Policy | Contact
All trademarks and registered trademarks appearing on Java Code Geeks are the property of their respective owners.
Java is a trademark or registered trademark of Oracle Corporation in the United States and other countries.
Java Code Geeks is not connected to Oracle Corporation and is not sponsored by Oracle Corporation.
Do you want to know how to develop your skillset and become a ...
Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you two of our best selling eBooks for FREE!

Get ready to Rock!
You can download the complementary eBooks using the links below:
Close