Export data from MySQL to Mongo DB

This is a continuation of my previous article Moving data between HBase and MySQL. In this article we are adding one more database, which is mongoDB. I will be using the same base framework and interfaces. The example shows how to export mysql to mongodb.

1. Introduction

mongoDB is a leading noSQL document database. It stores data in document format. If you are familiar with JSON structure, then you will understand exactly how data is represented in mongoDB. For example

{
  id : "1234",
  name : 
    {
       first_name : "ABC",
       last_name  : "xyz",
    }
}

mongoDB uses a type of JSON called BSON. There is no concept of tables in mongoDB. Instead it uses collections. In mongoDB, you need not create objects like collections, db, explicitely. First usage of the same creates it. For installation details please refer getting started from mongoDB.

For using examples, from this article, you need java driver for mongoDB. This can be donloaded from here .

2. Settig up to mongoDB reader and writer

I will use the same framework mentioned in my earlier post. The constructor will take the name of collection and the columns we are looking for.

Note: This is an old article. Copied here when moving to new hosting provider.

This sample will not use any nested values. But that does not stop you from using one.

public class MongoDBSource implements DataReader, DataWriter{
    private String _mCollectionName = null;
    private String _mColumns[] = null;
    
    public MongoDBSource(String collectionName, String cols[]) {
        _mCollectionName = collectionName;
        _mColumns = cols;
    }
}

3. Connecting to mongoDB

The connect() method implemented by MongoDBSource will create an instance of MongoClient. This is enough for connecting to mongoDB instance. As we are using one class instance for a collection, lets create the collection instance also and save it.

@Override
public void connect() throws IOException {
    _mClient = new MongoClient( "localhost" , 27017 );
        
    DB db = _mClient.getDB("sample");
    _mCollection = db.getCollection(_mCollectionName);
}

4. Reading records

Reading from collection is as simple as processing an iterator. As we are not using any filters, we call the find() API of DBCollection instance. This will return DBCursor instance to iterate through records. Every record is a DBObject instance from which column values can be accessed like a map.

@Override
public DataRecord readRecord() throws IOException {
    if(_mCursor == null) {
        _mCursor = _mCollection.find();
    }
        
    if(!_mCursor.hasNext()) {
        return null;
    }
        
    DBObject object = _mCursor.next();
    Object values[] = new Object[_mColumns.length];
    for(int i=0;i<_mColumns.length;i++) {
        values[i] = object.get(_mColumns[i]);
    }
        
    DataRecord rec = new DataRecord(values);
    return rec;
}

5. Writing to mongoDB

Writing is very simple. Create an instance of DBObject and insert it to the collection object. Column values are set to DBobject like a map.

@Override
public void writeRecord(DataRecord rec) throws IOException {
    BasicDBObject doc = new BasicDBObject(_mColumns[0], rec.getDataAt(0));
    for(int i=1;i<_mColumns.length;i++) {
        doc = doc.append(_mColumns[i], rec.getDataAt(i));
    }
        
    _mCollection.insert(doc);
}

6. Application

We will move data from MySQL to mongoDB first. To start with insert few records in MySQL database,

CREATE TABLE PERSON(PERS_ID INT PRIMARY KEY, PERS_DESC VARCHAR(100));
INSERT INTO PERSON(PERS_ID, PERS_DESC) VALUES(1,'First person');
INSERT INTO PERSON(PERS_ID, PERS_DESC) VALUES(2,'Second person');
commit;

The sample program below will read records from MySQL and insert to mongoDB.

public class Main {
    
    public static void main(String[] args) {
        
        //MySQL columns
        String mySQLCols[] =  {"PERS_ID","PERS_DESC"};
        
        //MongoDB columns
        String mongoDBCols[] =  {"id","desc"};
        
        MongoDBSource mg = new MongoDBSource("person",mongoDBCols);
        MySQLSource my = new MySQLSource("PERSON",mySQLCols);
        
        try {
            mg.connect();
            my.connect();
            DataRecord rec = my.readRecord();
            while(rec != null) {
                mg.writeRecord(rec);
                rec = my.readRecord();
            }
        } catch (IOException ex) {
            ex.printStackTrace();
            return;
        }
    }

Run the above java file, and check the records in mongodb shell.

$./mongo
MongoDB shell version: 2.4.7
connecting to: test
> use sample
switched to db sample
> db.person.find()
{ "_id" : ObjectId("526bf52444aecc25d1e5b6a3"), "id" : 1, "desc" : "First person" }
{ "_id" : ObjectId("526bf52444aecc25d1e5b6a4"), "id" : 2, "desc" : "Second person" }
> 

Now let's try to insert records to MySQL from mongoDB. In the above code, just swap the usage 'my' and 'mg' inside try-catch block.

try {
    my.connect();
    mg.connect();
    DataRecord rec = mg.readRecord();
    while(rec != null) {
        my.writeRecord(rec);
        rec = mg.readRecord();
    }
}
catch (IOException ex) {
    ex.printStackTrace();
    return;
}

Remove all the rows from the table in MySQL and run the above java class. Once the execution is complete, verify th result in MySQL

mysql> select * from PERSON;
+---------+---------------+
| PERS_ID | PERS_DESC     |
+---------+---------------+
|       1 | First person  |
|       2 | Second person |
+---------+---------------+
2 rows in set (0.00 sec)

Sources for this example can be found here(MongoDB.zip).