Converting Nested MongoDB/BSON/JSON to MySQL/RDBMS
The Source: Business Service Providers
It is an impressive feat for MongoSluice to be able to produce multiple tables that are linked together by foreign and primary keys. But can MongoSluice perform the same when data begins to get more complex? The answer is YES! Recently, we found a JSON file that was composed of an API call to US gov data. The file was not chosen for its size, but its complexity (4 layers deep). We have company info embedded in properties, embedded in provider, embedded in definitions, all embedded in the root object, businessServiceProviders.
A link to the file can be found here in JSON format. The API call can be found here. Check out the Mongo Docs to see how to import this file into MongoDB for easy viewing.
The Data: (Just a Little Taste)
“definitions” : {
“Provider” : {
“properties” : {
“ita_contact_email” : {
“type” : “string”,
“description” : “Email for ITA contact.”
},
“company_name” : {
“type” : “string”,
“description” : “Name of company providing the service.”
},
“company_phone” : {
“type” : “string”,
“description” : “Phone number for company.”
},
“company_address” : {
“type” : “string”,
“description” : “Street, city, and country address for company.”
},
“company_website” : {
“type” : “string”,
“description” : “URL for company site.”
},
“company_description” : {
“type” : “string”,
“description” : “Description of company.”
},
“company_email” : {
“type” : “string”,
“description” : “Email for contact at company.”
},
“ita_office” : {
“type” : “string”,
“description” : “Name of ITA office that has provided company information.”
},
“contact_title” : {
“type” : “string”,
“description” : “Title of contact at company.”
},
“contact_name” : {
“type” : “string”,
“description” : “Name of contact at company.”
},
“category” : {
“type” : “string”,
“description” : “Category of services that company provides.”
}
}
}
}
Streaming MongoDB to MySQL
- 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 –applyDeltas option in the MongoSluice docs to update existing documents and ignore duplicate documents.
- Now that MongoSluice has achieved its most difficult task, it can quickly dump all the data to SQL.
The Results: Inexpensive Stores, Easy Analysis
Check out the tables that MongoSluice generated below. You can see multiple children tables inside of children tables all originating from the root table businessServiceProviders. Moving data between MongoDB and SQL is made so simple with MongoSluice allowing inexpensive stores without compromising analysis.
MongoSluice Can Keep MongoDB and the RDBMS of Your Choice In Sync
A convenient feature of MongoSluice is its ability to quickly sync changed or new data without doing any additional work such as investigating a schema…
Speeding Up Streaming from MongoDB To RDBMS Through MongoSluice’s “Commit Size”
MongoSluice is great at accurately Sluicing through complex data, but it is important to have a tool that is also built for speed. MongoSluice meets…
ETL from MongoDB To MySQL That Gets Varying Data Types Right
The Problem: How To Migrate MongoDB When A Field Has A Few Different Data Types. There are a couple tools out there that try to…
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.