Convert SQL table to mongoDB document

Question

What is the best way to convert an SQL database, say 1 table, into a mongoDB document?

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

Answer 1

The FAR driver path is the most straightforward. Import / export tools are fantastic, but only if you use them as a pair. You are on a wild trip if your table includes dates and you are trying to export from db and import into mongo.

You are also lucky to be in C #. We use ruby ​​and have 32 million table rows that we migrated to mongo. Our final solution was to create a crazy sql statement in postgres that prints json (including some pretty funny things for correctly synchronizing dates) and passes the result of this query on the command line to mongoimport. It was an incredible day to write, and it is not something that can really be changed.

So, if you can handle this, use ado.net with the mongo driver. If not, I wish you the best :-)

(note that this comes from the common mango fauna)

Answer 2

This is an import script that I use to import data from a SQL server to Mongodb, which is in my mailbox. This code will simply create a similar table (existing in SQL DB) in MongoDB. You can provide a list of tables to import as a comma separator, and all of them will 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 3

I needed to create a tool for this. It uses bcp.exe to export data to xml and then converts it to json using Newtonsoft JSON.NET and then mongoimport to import it. It took less than one day, but dates are not supported.

Some code below (very uncleaned :)

bcp.exe uses a 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 play with Ruby, I create a stone to help you do this: http://mongify.com/ .

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

Actually a simple and direct definition of your circuit and how it should translate to mongodb. Including embedding, renaming tables, renaming fields, and many other parameters.

Answer 5

Both Norm and samus C # mongo drivers support strongly typed classes for transferring as documents. Means it's cool, like Nhiberbate and LINQ to SQL.

What is my idea: you can use LINQ to SQL to create an object in C # (classes representing tables) to retrieve data from an SQL server, and you can use the same strongly typed class to insert into mongo db.

Make sure your classes must have the mongo identifier attribute (in NoRM) in any of the fields. This is necessary to create a unique identifier for the document.