Migrating data from MongoDB to SQL

[rohitkummah]

Have a doubt , if you could help me on it..... do you have any idea on migrating data from MongoDB to SQL .

If you know , let me know the process.

Thanks in advance

[danilang]

Hi @rohitkummah 

There's no automatic method to migrate from a MongoDB to any SQL db that you can easily implement in Alteryx.  This is because of the difference in which the data is stored in each type.  Data in MongoDB is stored as JSON documents(objects).  These documents can have a nested structure with sub-documents and arrays embedded within them.  Data in SQL databases is stored in a series of relationally related flat tables.  Each of these tables contains a fixed set of fields with static types.   Any single object in MongoDB can contain information that could possibly spread across multiple SQL tables. 

As an example consider an employee HR record that contains information about multiple phone numbers.  In MongoDB this would be stored as an employee JSON record with an embedded array of X phone records, each containing the type, i.e. Home, Cell, Fax and the corresponding numbers.  To add new phone number, you simply add a new object in the Phone array.   In a SQL db, with its flat table structure, you have to make a choice.  You can either put multiple fields in the employee table called CellPhone, HomePhone, Fax, etc, or you can have a phone table with type, number and the primary key of the related employee.   If you put the fields in the Employee table, you've limited the number of phone numbers to the number of available fields.  If you use a related table you need to build insert triggers to maintain referential referential integrity so inserts will be slower

Because of underlying differences in the way that the records are stored in each type of database you need to analyze each object type in the MongoDB database and determine the "best" way to represent it in SQL.  This analysis can be performed by you, a third party, or you can buy off-the-shelf products that will migrate them for you.  Third party products will have a preferred method of conversion though.   This can result in sub-optimal approach to representing complex objects JSON objects in a SQL environment

Dan    

[rohitkummah]

Hi @danilang 

Thanks for the info . So my concern was I have to convert the data from MongoDB to MySQL and connect tableau with MySQL and visualize the data.

For that purpose only , I asked if we can convert the data from MongoDB to MYSQL.