Thursday, August 21, 2014

Introduction to MongoDB Geospatial feature

This post is a quick and simple introduction to Geospatial feature of MongoDB 2.6 using simple dataset and queries.

Storing Geospatial Informations

As you know you can store any type of data, but if you want to query them you need to use some coordinates, and create index on them. MongoDB supports three types of indexes for GeoSpatial queries:
  • 2d Index : uses simple coordinate (longitude, latitude). As stated in the documentation: The 2d index is intended for legacy coordinate pairs used in MongoDB 2.2 and earlier. For this reason, I won't detail anything about this in this post. Just for the record 2d Index are used to query data stored as points on a two-dimensional plane
  • 2d Sphere Index : support queries of any geometries on an-earth-like sphere, the data can be stored as GeoJSON and legacy coordinate pairs (longitude, latitude). For the rest of the article I will use this type of index and focusing on GeoJSON.
  • Geo Haystack : that are used to query on very small area. It is today less used by applications and I will not describe it in this post.
So this article will focus now on the 2d Sphere index with GeoJSON format to store and query documents.

So what is GeoJSON?

You can look at the site, let's do a very short explanation. GeoJSON is a format for encoding, in JSON, a variety of geographic data structures, and support the following types:  Point , LineString , Polygon , MultiPoint , MultiLineString , MultiPolygon and Geometry.

The GeoJSON format  is quite straightforward based, for the simple geometries, on two attributes: type and coordinates. Let's take some examples:

The city where I spend all my childhood, Pleneuf Val-André, France, has the following coordinates (from Wikipedia)
 48° 35′ 30.12″ N, 2° 32′ 48.84″ W
This notation is a point, based on a latitude & longitude using the WGS 84 (Degrees, Minutes, Seconds) system. Not very easy to use by application/code, this is why it is also possible to represent the exact same point using the following values for latitude & longitude:
48.5917, -2.5469
This one uses the WGS 84 (Decimal Degrees) system. This is the coordinates you see use in most of the application/API you are using as developer (eg: Google Maps/Earth for example)

By default GeoJSON, and MongoDB use these values but the coordinates must be stored in the longitude, latitude order, so this point in GeoJSON will look like:

  "type": "Point",
  "coordinates": [

This is a simple "Point", let's now for example look at a line, a very nice walk on the beach :

  "type": "LineString",
  "coordinates": [
    [-2.553677, 48.590898]

So using the same approach you will be able to create MultiPoint, MultiLineString, Polygon, MultiPolygon. It is also possible to mix all these in a single document using a GeometryCollection. The following example is a Geometry Collection of MultiLineString and Polygon over Central Park:

  "type" : "GeometryCollection",
  "geometries" : [
      "type" : "Polygon",
      "coordinates" : [
	  [ -73.9580, 40.8003 ],
          [ -73.9498, 40.7968 ],
	  [ -73.9737, 40.7648 ],
	  [ -73.9814, 40.7681 ],
	  [ -73.9580, 40.8003  ]
      "type" : "MultiLineString",
      "coordinates" : [
         [ [ -73.96943, 40.78519 ], [ -73.96082, 40.78095 ] ],
 	 [ [ -73.96415, 40.79229 ], [ -73.95544, 40.78854 ] ],
         [ [ -73.97162, 40.78205 ], [ -73.96374, 40.77715 ] ],
         [ [ -73.97880, 40.77247 ], [ -73.97036, 40.76811 ] ]

Note: You can if you want test/visualize these JSON documents using the service. 

Now what? Let's store data!

Once you have a GeoJSON document you just need to store it into your document. For example if you want to store a document about JFK Airport with its location you can run the following command:

    "name" : "John F Kennedy Intl",
    "type" : "International",
    "code" : "JFK",
    "loc" : {
      "type" : "Point",
      "coordinates" : [ -73.778889, 40.639722 ]

Yes this is that simple! You just save the GeoJSON as one of the attribute of the document, (loc in this example)

Querying Geospatial Informations

Now that we have the data stored in MongoDB, it is now possible to use the geospatial information to do some interesting queries. 

For this we need a sample dataset. I have created one using some open data found in various places. This dataset contains the following informations:
  • airports collection with the list of US airport (Point)
  • states collection with the list of US states (MultiPolygon)
I have created this dataset from various OpenData sources ( ) and use toGeoJSON to convert them into the proper format.

Let's install the dataset:
  1. Download it from here
  2. Unzip file
  3. Restore the data into your mongoDB instance, using the following command
MongoDB allows applications to do the following types of query on geospatial data:

  • inclusion
  • intersection
  • proximity
Obviously, you will be able to use all the other operator in addition to the geospatial ones. Let's now look at some concrete examples. 


Find all the airports in California. For this you need to get the California location (Polygon) and use the command $geoWithin in the query. From the shell it will look like :

use geo

var cal = db.states.findOne(  {code : "CA"}  );

    loc : { $geoWithin : { $geometry : cal.loc } } 
  { name : 1 , type : 1, code : 1, _id: 0 } 


{ "name" : "Modesto City - County", "type" : "", "code" : "MOD" }
{ "name" : "San Francisco Intl", "type" : "International", "code" : "SFO" }
{ "name" : "San Jose International", "type" : "International", "code" : "SJC" }

So the query is using the "California MultiPolygon" and looks in the airports collection to find all the airports that are in these polygons. This looks like the following image on a map:

You can use any other query features or criteria, for example you can limit the query to international airport only sorted by name :

    loc : { $geoWithin : { $geometry : cal.loc } },
    type : "International" 
  { name : 1 , type : 1, code : 1, _id: 0 } 
).sort({ name : 1 });


{ "name" : "Los Angeles Intl", "type" : "International", "code" : "LAX" }
{ "name" : "Metropolitan Oakland Intl", "type" : "International", "code" : "OAK" }
{ "name" : "Ontario Intl", "type" : "International", "code" : "ONT" }
{ "name" : "San Diego Intl", "type" : "International", "code" : "SAN" }
{ "name" : "San Francisco Intl", "type" : "International", "code" : "SFO" }
{ "name" : "San Jose International", "type" : "International", "code" : "SJC" }
{ "name" : "Southern California International", "type" : "International", "code" : "VCV" }

I do not know if you have looked in detail, but we are querying these documents with no index. You can run a query with the explain()to see what's going on. The $geoWithin operator does not need index but your queries will be more efficient with one so let's create the index:

db.airports.ensureIndex( { "loc" : "2dsphere" } );

Run the explain and you will se the difference.


    Suppose you want to know what are all the adjacent states to California, for this we just need to search for all the states that have coordinates that "intersects" with California. This is done with the following query:

    var cal = db.states.findOne(  {code : "CA"}  );
        loc : { $geoIntersects : { $geometry : cal.loc  }  } ,
        code : { $ne : "CA"  }  
      { name : 1, code : 1 , _id : 0 } 


    { "name" : "Oregon", "code" : "OR" }
    { "name" : "Nevada", "code" : "NV" }
    { "name" : "Arizona", "code" : "AZ" }
    Same as before $geoIntersect operator does not need an index to work, but it will be more efficient with the following index:

    db.states.ensureIndex( { loc : "2dsphere" } );


    The last feature that I want to highlight in this post is related to query with proximity criteria. Let's find all the international airports that are located at less than 20km from the reservoir in NYC Central Park. For this you will be using the $near operator.

    loc : {
    $near : {
    $geometry : { 
    type : "Point" , 
    coordinates : [-73.965355,40.782865]  
    $maxDistance : 20000
    type : "International"
    name : 1,
    code : 1,
    _id : 0


    { "name" : "La Guardia", "code" : "LGA" } { "name" : "Newark Intl", "code" : "EWR"

    So this query returns 2 airports, the closest being La Guardia, since the $near operator sorts the results by distance. Also it is important to raise here that the $near operator requires an index.


    In this first post about geospatial feature you have learned:
    • the basic of GeoJSON
    • how to query documents with inclusion, intersection and proximity criteria.
    You can now play more with this for example integrate this into an application that expose data into some UI, or see how you can use the geospatial operators into a aggregation pipeline.

    Friday, March 28, 2014

    db.person.find( { "role" : "DBA" } )

    Wow! it has been a while since I posted something on my blog post. I have been very busy, moving to MongoDB, learning, learning, learning…finally I can breath a little and answer some questions.

    Last week I have been helping my colleague Norberto to deliver a MongoDB Essentials Training in Paris. This was a very nice experience, and I am impatient to deliver it on my own. I was happy to see that the audience was well balanced between developers and operations, mostly DBA.

    What! I still need DBA?

    This is a good opportunity to raise a point, or comment a wrong idea: the fact that you are using MongoDB, or any other NoSQL datastore does not mean that you do not need a DBA… Like any project, an administrator is not mandatory, but if you have one it is better. So even when MongoDB is pushed by development team it is very important to understand the way the database works, and how to administer, monitor it.

    If you are lucky enough to have real operations teams, with good system and database administrators use them! They are very important for your application.

    Most DBA/System Administrators have been maintaining systems in production for many years. They know how to keep your application up and running. They also most of the time experienced many “disasters”, and then recover (I hope).

    Who knows, you may encounter big issues with your application and you will be happy to have them on your side at this moment.

    "Great, but the DBA is slowing down my development!"

    I hear this, sometimes, and I had this feeling in the past to as developer in large organization. Is it true?

    Developers and DBA are today, not living in the same worlds:

    • Developers want to integrate new technologies as soon as possible, not only because it is fun and they can brag about it during meetups/conferences; but because these technologies, most of the time, are making them more productive, and offer better service/experience to the consumer 
    • DBA, are here to keep the applications up and running! So every time they do not feel confident about a technology they will push back. I think this is natural and I would be probably the same in their position. Like all geeks, they would love to adopt new technologies but they need to understand and trust it before.

    System administrators, DBAS look at the technology with a different angle than developers.

    Based on this assumption, it is important to bring the operation team as early as possible when  the development team wants to integrate MongoDB or any new data store. Having the operation team in the loop early will ease the global adoption of MongoDB in the company.

    Personally, and this will show my age, I have seen a big change in the way developers and DBAs are working together.

    Back in the 90's, when the main architecture was based on client/server architecture  developers and DBAs where working pretty well togethers; probably because they were speaking the same language: SQL was everywhere.  I had regular meetings wit

    Then, since mid 2000, mots of applications have moved to a web based architecture , with for example Java middleware, and the developers stopped working with DBAs. Probably because the abstraction data layer provided by the ORM exposed the database as a "commodity" service that is supposed to work: "Hey Mr DBA, my application has been written with the best middleware technology on the market, so now deal with the performance and scalability! I am done!"

    Yes it is a cliché, but I am sure that some of you will recognize that.

    Nevertheless each time I can, I have been pushing developers to talk more to administrators and look closely to their database!

    A new era for operations and development teams

    The fast adoption of MongoDB by developers, is a great opportunity to fix what we have broken 10 years ago in large information systems:

    • Let's talk again!

    MongoDB has been built first for developers. The document oriented approach gives lot of flexibility to quickly adapt to change. So anytime your business users need a new feature you can implement it, even if this change impact the data structure. Your data model is now driven and controlled by the application, not the database engine.

    However, the applications still need to be available 24x7, and performs well. These topics are managed - and shared- by administrator and developers! This has been always the case but, as I described it earlier, it looks like some of us have forgotten that.

    Schemas design, change velocity, are driven by the application, so the business and development teams, but all this impacts the database, for example:

    • How storage will grow ?
    • Which indexes must be created to speed up my application?
    • How to organize my cluster to leverage the infrastructure properly:
      • Replica-Set organization (and related write concerns, managed by developer)
      • Sharding options
    • And the most important of them : backup/recovery strategies

    So many things that could be managed by the project team, but if you have an operation team with you, it will be better to do that as a single team.

    You, the developer, are convinced that MongoDB is the best database for your projects! Now it is time to work with the ops team and convince them too.  So you should for sure explain why MongoDB is good for you as developer, but also you should highlight all the benefits for the operations, starting with built-in high-availability with replica sets, and easy scalability with sharding. MongoDB is also here to make the life of the administrator easier! I have shared in the next paragraph a lit of resources that are interesting for operations people.

    Let’s repeat it another time, try to involve the operation team as soon as possible, and use that as an opportunity to build/rebuild the relationship between developers and system administrators!


    You can find many good resources on the Site to helps operations or learn about this:

    Tuesday, October 1, 2013

    Pagination with Couchbase

    If you have to deal with a large number of documents when doing queries against a Couchbase cluster it is important to use pagination to get rows by page. You can find some information in the documentation in the chapter "Pagination", but I want to go in more details and sample code in this article.

    For this example I will start by creating a simple view based on the beer-sample dataset, the view is used to find brewery by country:

    function (doc, meta) {
      if (doc.type == "brewery" &&{

    This view list all the breweries by country, the index looks like:

    Doc idKeyValue
    yellowstone_valley_brewingUnited Statesnull
    yuengling_son_brewingUnited Statesnull
    zea_rotisserie_and_breweryUnited Statesnull
    fosters_tien_gangViet Namnull
    hue_breweryViet Namnull

    So now you want to navigate in this index with a page size of 5 rows.

    Thursday, July 18, 2013

    How to implement Document Versioning with Couchbase


    Developers are often asking me how to "version" documents with Couchbase 2.0. The short answer is: the clients and server do not expose such feature, but it is quite easy to implement.

    In this article I will use a basic approach, and you will be able to extend it depending of your business requirements. 


    The first thing to do is to select how to "store/organize" the versions of your document, and for this you have different designs:
    • copy the versions the document into new documents
    • copy the versions of the document into a list of embedded documents
    • store the list of attributes that have been changed into a embedded element (or new documents)
    • store the "delta"
    You will have to chose the design based on your application requirements (business logic, size of the dataset, ...).  For this article, let's use one of the most simplistic approach: create new document for each version with the following rules for the keys:
    1. The current version is is a simple Key/Document, no change to the key.
    2. The version is a copy of the document, and the version number is added to the key.
    This looks like:
    Current Version  mykey
    Version 1  mykey::v1
    Version 2  mykey::v2
    ...     ...

    With this approach, existing applications will always use the current version of the document, since the key is not changed. But this approach creates new documents that will be indexed by existing views.

    For example, in the Beer Sample application, the following view is used to list the beer by name:

    function (doc, meta) {
        if(doc.type && doc.type == "beer") {

    It is quite simple to "support" versioning without impacting the existing code, except the view itself. The new view needs to emit keys,value only for the current version of the document. This is the new view code:

    function (doc, meta) {
        if(doc.type && doc.type == "beer" && ("::v") == -1   ) {

    With this change the existing applications that are using this view will continue to work with the same behavior.

    Implementing the versioning

    Based on this design, when the application needs to version the document, the following logic should happen:
    1. Get the current version of the document
    2. Increment the version number (for example using another key that maintains the version number for each document)
    3. Create the version with the new key  "mykey::v1"
    4. Save the document current version
    Let's look at the code in Java

      Object obj = client.get(key);
      if (obj != null) {
        // get the next version, create or use the key: mykey_version
        long version = client.incr(key + "_version", 1, 1); 
        String keyForVersion = key + "::v" + version; // mykey::v1
        try {
            client.set(keyForVersion, obj).get();
        } catch (Exception e) {
            logger.severe("Cannot save version "+ version + " for key "+ key +" - Error:"+ e.getMessage() );
       client.set(key, value);

    Quite simple isn't?

    The application can access the document using the key, but also get one version or the list of all versions, this is one of the reasons why it is interesting to create a key (mykey_version), and use it also to delete documents and related versions.

    Based on the previous comment, the delete operation looks like:

      Object obj = client.get(key);
      // need to delete all the version first
      Object vObject = this.get(key + "_version");
      if (vObject != null) {
        long biggerVersion = Long.parseLong((String) vObject);
        try {
            // delete all the versions
            for (int i = 1; i <= biggerVersion; i++) {
                String versionKey = key + "::v" + i;
            // delete the counter
            client.delete(key + "_version").get();
        } catch (InterruptedException e) {
        } catch (ExecutionException e) {

    Use versioning

    As an example, I have created a small library available on GitHub, this library extends the Couchbase Client and overrides some of the operations : set, replace and delete. (the basic one: no TLL, no durability) As I said before this is just an example.

    Build and Install 

    git clone
    cd how-to-versioning
    mvn clean install

    Then add this library to your project in addition to Couchbase Java Client, for example in your pom.xml

    Code your application

    Create a document and version it:

     List uris = new LinkedList();
     CouchbaseClientWithVersioning client = null
     try {
      client = new CouchbaseClientWithVersioning(uris, "default", "");
      String key = "key-001";
      client.set(key, "This is the original version");
      System.out.printf("Original '%s' .\n", client.get(key));
      client.set(key, "This is a new version", true); // create a new version
      System.out.printf("Current Version '%s' .\n", client.get(key));
      System.out.printf("Version 1 '%s' .\n", client.get(key, 1));
      client.set(key, "This is another version", true); // create a new version
      System.out.printf("All versions %s .\n", client.getAllVersions(key));
      client.deleteVersion(key, 1); // create a new version
      System.out.printf("All versions %s (after delete 1 version).\n", client.getAllVersions(key));
      client.delete(key); // create a new version
      System.out.printf("All versions %s (after delete the main key).\n", client.getAllVersions(key));
     } catch (Exception e) {
     if (client !=null) {

    Quick explanation:
    • Line 5: instead of using the CouchbaseClient, the application uses the extended  CouchbaseClientWithVersioning class.
    • Line 7: create a new entry
    • Line 9: create a new version, the boolean value to "true" force the versioning of the document
    • The application use other methods such as get a specific version (line 11), get all versions (line 13), delete a specific version (line 14), and finally delete the key and all versions (line 16).
    So using this approach the developer controls explicitly when to create a version, since he has to add the boolean parameter in the set operation. In this small sample library it is also possible to do auto versioning, in this case all set and replace calls will create a version, to achieve that the developer just needs to call the setAutoVersioning(true) method. Something like:

        client = new CouchbaseClientWithVersioning(uris, "default", "");

    With this approach you can provide versioning to your application with minimal code change. You can test it in the Beer Sample application, just do not forget to change the views as documenter above to only return current version of the documents.


    As you can see doing versioning in Couchbase is not that complicated, but it is something that must be done by your application based on its requirements and constraints. You have many different solution and none of these options is perfect for all use cases.

    In this specific sample code, I am working with a simple design where I create a copy of the documents for each version. With this approach also, it is interesting to mention that you can version "anything", not only JSON document but also any values.  As I said before, this is one possible approach, and like any design, it has some impact on the application or database, in this case most the database:
    • Increase the number of keys and documents
    • Double - or more- the number of operations, for example when updating a document, the application needs to get the current value, create a version, save the current version.
    • Consistency management when adding new version and incrementing the version number (need to deal with errors when creating a new version, deleting the versions and counter....)
    Many features could be added to this easily, for example:
    • Limit to a specific number of version,
    • Enable the versioning only of replace() operation
    • Add specific attribute about versions in JSON document (for example date of the version)
    • ....

    If you are using versioning in your Couchbase application feel free to comment or write a small article that describes the way your are doing it.

    Thursday, July 11, 2013

    Deploy your Node/Couchbase application to the cloud with Clever Cloud


    Clever Cloud is the first PaaS to provide Couchbase as a service allowing developers to run applications in a fully managed environment. This article shows how to deploy an existing application to Clever Cloud.

    I am using a very simple Node application that I have documented in a previous article: “Easy application development with Couchbase, Angular and Node”.

    Clever Cloud provides support for various databases MySQL, PostgreSQL, but also and this is most important for me Couchbase. No only Clever Cloud allows you to use database services but also you can deploy and host your application that could be developed in the language/technology of your choice : Java, Node, Scala, Python, PHP, … and all this in a secure, scalable and managed environment.

    Setting up your Clever Cloud environment

    Create your account

    1. Go to the Clever Cloud site :
    2. Click on “Login” link and follow the steps to create your account.
    3. After few seconds you will received an email and be redirected to the Clever Cloud Console.

    Create a Couchbase instance

    The Clever Cloud Console allows you to create your Couchbase Bucket in few clicks:

    1. Cick on “Services” in the left menu

    2.  Click on “Add a Service” in the left menu 
      3. Click on “Couchbase” button.

      4. Select the size of the RAM quota for your bucket

      The size of the RAM Quota for your bucket will have an impact on performance but also on the pricing.

      5. Click “Add this Service”

      You are done, you should receive an email with all the information to access your newly created bucket.

      The mail from Clever Cloud contains the following information:

      db_host = xxxxxxxx.couchbase.clvrcld.netLocation of the database, this is where the endpoint is located.
      db_name = yyyyyyyyName of the Couchbase bucket
      db_username = xxxxxxxxNot used in Couchbase context
      db_password = zzzzzzzzPassword to connect to the Couchbase Bucket

      So you are now ready to use your bucket.

      Note: In the current version of the Clever Cloud Couchbase Service you do not have access to a management console. If you want to get some information about the database or create views you need to do it from you application code.

      Connect your Application to Couchbase@Clever-Cloud

      The first step is to get some code, so let’s clone the “Couchbase Ideas Sample Application”, and install the dependencies, using the following commands:

      git clone -b 03-vote-with-value
      cd couchbase-node-ideas
      git branch mybranch
      git checkout mybranch
      npm install

      Open the app.js and edit the connection info to point your application to the Couchbase instance and modify the HTTP port of your application to 8080 - this is a mandatory step documented here :

      dbConfiguration = {
       "hosts": [""],
       "bucket": "xxxxxxxxxxx",
       "user": "xxxxxxxxxx",
       "password": "yyyyyyyyyyyyyyyyyyyyyyyyy"
        appServer = app.listen(8080, function() {
       console.log("Express server listening on port %d in %s mode", appServer.address().port, app.settings.env);

      Launch your application using
      node app.js

      Go to http://localhost:8080

      Your application is now using Couchbase on the cloud powered by Clever Cloud. Let’s now deploy the application itself on Clever Cloud

      Deploy your application on Clever Cloud

      The easiest way to deploy an application to Clever Cloud is using git. The first thing to do is to add your SSH public key into Clever Cloud Console. If you do not have any SSH yet, follow the steps described on Github : “Generating SSH Keys”.

      Add your SSH key

      Note: As you can guess this should be done only once
      Open the file with a text editor. This is your SSH key. Select all and copy to your clipboard.
      1. Go to the Clever Cloud Console
      2. Click on “Profile” entry in the left menu
      3. Click on “SSH Keys”
      4. Click on “Add a SSH Key”
      5. Enter a name (anything you want) and paste your key
      6. Click “Add” button
      You are now ready to deploy applications to Clever Cloud. The next thing to do, is to create a new node application in Clever Cloud.

      Create your Application

      1. Click “Add an app” in the Application menu in the top menu.
      2. Give a name and description to this application
      3. Select the Instance type, in this case “Node.js”
      4. Configure your instances, you can keep the default values for now, click “Next”
      5. Check the configuration, and click “Create”
      Your application is created, you are redirected to the generic information page, where you can find a Git URL that we will use to deploy the application.
      You can navigate into the entries in the left menu to see more information about your application. In addition to the Information page, you can look at the following entries:
      1. “Domain Names” to configure the URL to access your application
      2. “Logs” to view the application logs

      Deploy the Application

      So we are almost there!
      The deployment to Clever Cloud is done using a Git push command, so you need to add the deployment URL as a remote repository to your application, using the following command:
      git remote add clever git+ssh://[your_app_id].git
      git commit -a -m “Couchbase on Clever Cloud connection”
      git push clever mybranch:master

      Once you have added the application as remote repository you can commit and push your application.

      The last command pushes the application  to Clever Cloud. It is important to note that Clever Cloud will always deploy the application on the “master” branch on the remote repository. The notation mybranch:master is used to mention it. If you work locally on your master branch just use “master”.

      You can now go to the Clever Cloud console and look in the log and click on the URL in the “Domain Names” section to test your application.

      You should be able to see your application, that is running on the Clever Cloud PaaS.

      When you update your application, you just need to do a  git push and git commit.


      In this tutorial you have learned how to:
      • Create your Clever Cloud account
      • Create a Couchbase instance
      • Create and deploye a Node.js application

      Feel free to test this yourself, with Node or other technology, as you can see it is quite easy to setup.

      Wednesday, July 3, 2013

      SQL to NoSQL : Copy your data from MySQL to Couchbase

      TL;DR: Look at the project on Github.


      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:

        "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 properties file.
      ## SQL Information ##
      ## Couchbase Information ##
      ## Import information

      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 

      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"

        4.1. Analyze the ResultSetMetadata to get the list of columns
        4.2. Create a Java map for each rows where the key is the name of the columns and the value…is the value

        4.3. 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.


      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.

      Friday, May 31, 2013

      Create a Couchbase cluster in less than a minute with Ansible

      TL;DR: Look at the Couchbase Ansible Playbook on my Github.


      When I was looking for a more effective way to create my cluster I asked some sysadmins which tools I should use to do it. The answer I got during OSDC was not Puppet, nor Chef, but was Ansible.

      This article shows you how you can easily configure and create a Couchbase cluster deployed and many linux boxes...and the only thing you need on these boxes is an SSH Server!

      Thanks to Jan-Piet Mens that was one of the person that convinced me to use Ansible and answered questions I had about Ansible.

      You can watch the demonstration below, and/or look at all the details in the next paragraph.


      Ansible is an open-source software that allows administrator to configure and manage many computers over SSH.

      I won't go in all the details about the installation, just follow the steps documented in the Getting Started Guide. As you can see from this guide, you just need Python and few other libraries and clone Ansible project from Github. So I am expecting that you have Ansible working with your various servers on which you want to deploy Couchbase.

      Also for this first scripts I am using root on my server to do all the operations. So be sure you have register the root ssh keys to your administration server, from where you are running the Ansible scripts.

      Create a Couchbase Cluster

      So before going into the details of the Ansible script it is interesting to explain how you create a Couchbase Cluster. So here are the 5 steps to create and configure a cluster:
      1. Install Couchbase on each nodes of the cluster, as documented here.
      2. Take one of the node and "initialize" the cluster,  using cluster-init command.
      3. Add the other nodes to the cluster, using server-add command.
      4. Rebalance, using rebalance command
      5. Create a Bucket, using bucket-create command.   
      So the goal now is to create an Ansible Playbook that does these steps for you.

      Ansible Playbook for Couchbase

      The first think you need is to have the list of hosts you want to target, so I have create a hosts file that contains all my server organized in 2 groups:

      The group [couchbase-main] group is just one of the node that will drive the installation and configuration, as you probably already know, Couchbase does not have any master... All nodes in the cluster are identical.

      To ease the configuration of the cluster, I have create another file that contains all parameters that must be sent to all the various commands. This file is located in the group_vars/all see the section Splitting Out Host and Group Specific Data in the documentation.
      # Adminisrator user and password
      admin_user: Administrator
      admin_password: password
      # ram quota for the cluster
      cluster_ram_quota: 1024
      # bucket and replicas
      bucket_name: ansible
      bucket_ram_quota: 512
      num_replicas: 2

      Use this file to configure your cluster.

      Let's describe the playbook file :

      - name: Couchbase Installation
        hosts: all
        user: root
        - name: download Couchbase package
          get_url: url= dest=~/.
        - name: Install dependencies
          apt: pkg=libssl0.9.8 state=present
        - name: Install Couchbase .deb file on all machines
          shell: dpkg -i ~/couchbase-server-enterprise_x86_64_2.0.1.deb

      As expected, the installation has to be done on all servers as root then we need to execute 3 tasks:
      1. Download the product, the get_url command will only download the file if not already present
      2. Install the dependencies with the apt command, the state=present allows the system to only install this package if not already present
      3. Install Couchbase with a simple shell command. (here I am not checking if Couchbase is already installed)
      So we have now installed Couchbase on all the nodes. Let's now configure the first node and add the others:
      - name: Initialize the cluster and add the nodes to the cluster
        hosts: couchbase-main
        user: root 
        - name: Configure main node
          shell: /opt/couchbase/bin/couchbase-cli cluster-init -c  --cluster-init-username=${admin_user} --cluster-init-password=${admin_password} --cluster-init-port=8091 --cluster-init-ramsize=${cluster_ram_quota} 
        - name: Create shell script for configuring main node
          action: template src=couchbase-add-node.j2 dest=/tmp/ mode=750
        - name: Launch config script
          action: shell /tmp/
        - name: Rebalance the cluster
          shell: /opt/couchbase/bin/couchbase-cli rebalance -c -u ${admin_user} -p ${admin_password}      
        - name: create bucket ${bucket_name} with ${num_replicas} replicas
          shell: /opt/couchbase/bin/couchbase-cli bucket-create -c --bucket=${bucket_name} --bucket-type=couchbase --bucket-port=11211 --bucket-ramsize=${bucket_ram_quota}  --bucket-replica=${num_replicas} -u ${admin_user} -p ${admin_password}

      Now we need to execute specific taks on the "main" server:
      • Initialization of the cluster using the Couchbase CLI, on line 06 and 07
      Then the system needs to ask all other server to join the cluster. For this the system needs to get the various IP and for each IP address execute the add-server command with the IP address. As far as I know it is not possible to get the IP address from the main playbook YAML file, so I ask the system to generate a shell script to add each node and execute the script.
      This is done from the line 09 to 13.

      To generate the shell script, I use Ansible Template, the template is available in the couchbase-add-node.j2 file. 

      {% for host in groups['couchbase-nodes'] %}
      /opt/couchbase/bin/couchbase-cli server-add -c -u ${admin_user} -p ${admin_password} --server-add={{ hostvars[host]['ansible_eth0']['ipv4']['address'] }}:8091 --server-add-username=${admin_user} --server-add-password=${admin_password}   
      {% endfor %}

      As you can see this script loop on each server in the [couchbase-nodes] group and use its IP address to add the node to the cluster.

      Finally the script rebalance the cluster (line 16) and add a new bucket (line 19).

      You are now ready to execute the playbook using the following command :

      ./bin/ansible-playbook -i ./couchbase/hosts ./couchbase/couchbase.yml -vv

      I am adding the -vv parameter to allow you to see more information about what's happening during the execution of the script.

      This will execute all the commands described in the playbook, and after few seconds you will have a new cluster ready to be used! You can for example open a browser and go to the Couchase Administration Console and check that your cluster is configured as expected.

      As you can see it is really easy and fast to create a new cluster using Ansible.

      I have also create a script to uninstall properly the cluster.. just launch

      ./bin/ansible-playbook -i ./couchbase/hosts ./couchbase/couchbase-uninstall.yml