How To Easily Move Data from MongoDB to MySQL

by | Dec 12, 2018 | Case Studies, News

The Source: Movie Data Since 1951

Movies are cool, but this dataset was nasty: arrays, nested documents, and lots of different fields.  Normally, this would be a nightmare to do any useful SQL type analysis on.  However, MongoSluice bridges the gap by pushing it to SQL with ease, making it ready for analysis.

A link to the dataset can be found here in BSON format.  Check out the Mongo Docs to see how to restore this document into MongoDB for easy viewing.

The Data: Sample Document

{

“_id” : ObjectId(“5692a56e24de1e0ce2dfdd09”),

“title” : “Bu Iste Bir Yalnizlik Var”,
“year” : 2013,
“rated” : null,
“released” : ISODate(“2013-12-13T05:00:00Z”),
“runtime” : 122,
“countries” : [
“Turkey”
],
“genres” : [
“Romance”
],
“director” : “Ketche”,
“writers” : [
“Burak Göral”,
“Burak Göral”,
“Tuna Kiremitçi”
],
“actors” : [
“Devrim Özder Akin”,
“Merve Ates”,
“Atiye”,
“Turgut Berkes”
],
“plot” : null,
“poster” : null,
“imdb” : {
“id” : “tt3420850”,
“rating” : 5.8,
“votes” : 674
},
“awards” : {
“wins” : 0,
“nominations” : 0,
“text” : “”
},
“type” : “movie”

}

Transforming NoSQL to SQL

MongoSluice works in two steps.
  1. First, it interrogates every single document within the Mongo collection in order to build an accurate schema.  MongoSluice does not take shortcuts or samples of the data because it likes to be perfect.  Check out the –useCachedSchema option in the MongoSluice docs for updating existing data without interrogating the entire structure.
  2. Now that MongoSluice has achieved its most difficult task, it can quickly dump all the data to SQL.

The Results: Scalability of NoSQL, Analytic Capability of SQL

The data is now neatly stored into different tables that have relational integrity, visible by the PK_UID and the corresponding moviedetails_ID.

 

About MongoSluice

MongoSluice is the most complete solution for leveraging your data in MongoDB in BI application and other RDBMS systems.

Guarantee

We guarantee satisfaction.
Zero hassles.