Convert SQL table to mongoDB document

Question

What's the best way to convert a SQL database, let's say 1 table, to a mongoDB document?

I guess I could use a C# driver and implement a loop, which selects each row in the table and saves that in Mongo.. However, I am looking for a better way to convert a lot of data..

Answer 1

This is the import script that I am using for Importing data from SQL server to Mongodb located on my box. This code will just create a similar table(existing in SQL DB) in MongoDB. You can supply the table list to import as comma seperated and all of them would be imported without problems.

static void Main(string[] args)
{
    List<string> tablelist = new List<string>();
    if (!args[0].Contains(','))
        tablelist.Add(args[0]);
    else
        tablelist.AddRange(args[0].Split(','));
    string sqlconnectionstring = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
    var connectionString = "mongodb://localhost/?safe=true;w=1;wtimeout=30s";
    var safemode = SafeMode.True;
    MongoServer server = MongoServer.Create(connectionString);
    MongoDatabase db = server.GetDatabase("testdb");
    MongoCollection<MongoDB.Bson.BsonDocument> coll = db.GetCollection<BsonDocument>("test");
    //coll.Find().Count();
    int i = 0;
    foreach (string table in tablelist)
    {

        using (SqlConnection conn = new SqlConnection(sqlconnectionstring))
        {
            string query = "select * from " + table;
            using (SqlCommand cmd = new SqlCommand(query, conn))
            {
                /// Delete the MongoDb Collection first to proceed with data insertion

                if (db.CollectionExists(table))
                {
                    MongoCollection<BsonDocument> collection = db.GetCollection<BsonDocument>(table);
                    collection.Drop();
                }
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                List<BsonDocument> bsonlist = new List<BsonDocument>(1000);
                while (reader.Read())
                {
                    if (i == 1000)
                    {
                        using (server.RequestStart(db))
                        {
                            //MongoCollection<MongoDB.Bson.BsonDocument> 
                            coll = db.GetCollection<BsonDocument>(table);
                            coll.InsertBatch(bsonlist);
                            bsonlist.RemoveRange(0, bsonlist.Count);
                        }
                        i = 0;
                    }
                    ++i;
                    BsonDocument bson = new BsonDocument();
                    for (int j = 0; j < reader.FieldCount; j++)
                    {
                        if (reader[j].GetType() == typeof(String))
                            bson.Add(new BsonElement(reader.GetName(j), reader[j].ToString()));
                        else if ((reader[j].GetType() == typeof(Int32)))
                        {
                            bson.Add(new BsonElement(reader.GetName(j), BsonValue.Create(reader.GetInt32(j))));
                        }
                        else if (reader[j].GetType() == typeof(Int16))
                        {
                            bson.Add(new BsonElement(reader.GetName(j), BsonValue.Create(reader.GetInt16(j))));
                        }
                        else if (reader[j].GetType() == typeof(Int64))
                        {
                            bson.Add(new BsonElement(reader.GetName(j), BsonValue.Create(reader.GetInt64(j))));
                        }
                        else if (reader[j].GetType() == typeof(float))
                        {
                            bson.Add(new BsonElement(reader.GetName(j), BsonValue.Create(reader.GetFloat(j))));
                        }
                        else if (reader[j].GetType() == typeof(Double))
                        {
                            bson.Add(new BsonElement(reader.GetName(j), BsonValue.Create(reader.GetDouble(j))));
                        }
                        else if (reader[j].GetType() == typeof(DateTime))
                        {
                            bson.Add(new BsonElement(reader.GetName(j), BsonValue.Create(reader.GetDateTime(j))));
                        }
                        else if (reader[j].GetType() == typeof(Guid))
                            bson.Add(new BsonElement(reader.GetName(j), BsonValue.Create(reader.GetGuid(j))));
                        else if (reader[j].GetType() == typeof(Boolean))
                        {
                            bson.Add(new BsonElement(reader.GetName(j), BsonValue.Create(reader.GetBoolean(j))));
                        }
                        else if (reader[j].GetType() == typeof(DBNull))
                        {
                            bson.Add(new BsonElement(reader.GetName(j), BsonNull.Value));
                        }
                        else if (reader[j].GetType() == typeof(Byte))
                        {
                            bson.Add(new BsonElement(reader.GetName(j), BsonValue.Create(reader.GetByte(j))));
                        }
                        else if (reader[j].GetType() == typeof(Byte[]))
                        {
                            bson.Add(new BsonElement(reader.GetName(j), BsonValue.Create(reader[j] as Byte[])));
                        }
                        else
                            throw new Exception();
                    }
                    bsonlist.Add(bson);
                }
                if (i > 0)
                {
                    using (server.RequestStart(db))
                    {
                        //MongoCollection<MongoDB.Bson.BsonDocument> 
                        coll = db.GetCollection<BsonDocument>(table);
                        coll.InsertBatch(bsonlist);
                        bsonlist.RemoveRange(0, bsonlist.Count);
                    }
                    i = 0;
                }
            }
        }
    }
}

Answer 2

The driver way is by FAR the most straight forward. The import/export tools are fantastic, but only if you are using them as a pair. You are in for a wild ride if your table includes dates and you try to export from the db and import into mongo.

You are lucky too, being in c#. We are using ruby, and have a 32million row table we migrated to mongo. Our ending solution was to craft an insane sql statement in postgres that output json (including some pretty kludgy things to get dates going properly) and piped the output of that query on the command line into mongoimport. It took an incredibly frustrating day to write, and is not the sort of thing that can ever really be changed.

So if you can get away with it, use ado.net with the mongo driver. If not, I wish you well :-)

(note that this is coming from a total mongo fanboi)

Answer 3

I had to create tool for doing that. It uses bcp.exe to export data to xml, then convert it to json using Newtonsoft JSON.NET and then mongoimport to import it. It took less then a day, but dates are not supported.

Some code below (very uncleaned:)

bcp.exe uses syntax like: bcp.exe "SELECT * from GeoData.dbo.Airports FOR XML RAW('Row'),ROOT('Root'),ELEMENTS" queryout D:\TEMP\tmp1045.tmp -Uxxxx -Pxxxx -Sxxxx -w -r "" -q

json:

var r=XmlReader.Create("file://D:/1.xml");
    XmlDocument xdoc=new XmlDocument();
    xdoc.Load(r);
    string result="";





    //o["Root"]["Airport"];
    foreach(XmlNode n in xdoc.ChildNodes[0]){
        var rr= JsonConvert.SerializeXmlNode(n);    

        JObject o=JObject.Parse(rr);    

        var co=o.Children().Children().First();     

        foreach (JToken c in co.Children().Where(cc=>cc.Type==JTokenType.Property).ToList()){                   
            var prop=c as JProperty;            
            double d;
            if (double.TryParse(co[prop.Name].Value<string>(),out d))
            {
                co[prop.Name] = d;
            }           
            //c.Value<string>().Dump();
            //c.Value<string>().Dump();
            //co[c.Name]
        }

        //co["APT_Latitude"].Value<decimal>().Dump();       
        result=result + co.ToString(Newtonsoft.Json.Formatting.None)+"\r\n";


    }
    File.WriteAllText("D:/1.json",result);

    //result.Dump();

Mongoimport: D:\MongoDB\mongoimport.exe -c "test" -d "MongoStatic" 1.json >1

Answer 4

If you're up with playing with Ruby, I've make a gem that helps you do this: http://mongify.com/.

Source code can be found: https://github.com/anlek/mongify/

Really easy and straight forward defining of your schema and how it should translate into mongodb. Including embedding, renaming tables, renaming fields and a bunch of other options.