Supporting Mongo DB Connections
We've been being asked by a number of customers to support Mongo DB connections in Data Sync. So we are pleased to announce that we have listened and have developed a new connector that supports MongoDB.
This is currently a custom connector available on our Simego Git Hub account, but will most likely become part of our default providers in the future.
Some examples of how this connector could be used are :
- Syncing data between two Mongo Databases.
- Syncing data from a data source to Mongo to act as a system backup.
- Syncing data to Mongo so that it can be processed. For example your source might have API limits or run slow, so by adding the data to Mongo you can process it and synchronise it to other data sources.
- Exporting data from Mongo to another system.
Requirements
Before getting started you need to ensure you have the following:
- Windows 10 or Windows Server
- Downloaded & Installed Data Synchronisation Studio (Version 3.0.2150 +)
- Have access to a Mongo Database
If you do not have Data Synchronisation Studio you can get a free evaluation edition.
Get the Connector
To use this connector you need to start by installing the Mongo DB connector from the Simego GitHub page. We have built a connector installer inside Data Sync that will download and install the relevant files for you.
To get to the installer go to File > Install Data Connector. This will open the Connector Installer Window, where all you need to do is select the connector you want to install from the drop down and click OK.
If the installation was successful you should get a confirmation popup and you now need to close all instances of Data Sync and then re-start the program.
If you get an error saying it could not install the connector because it is "Unable to access folder", then this is because the folder is locked by Ouvvi. Please stop your Ouvvi services and try again.
You can now access both of the Mongo connectors from the connection window under Mongo Db.
Connect to Mongo DB
To connect to your Mongo databases you will need your connection string. You can find this in Mongo by going to Databases > Connect > Connect your Application.
Copy the connection string and make sure to edit it so that you change <password> to use your actual password.
Enter this connection string into the ConnectionString field. If the connection string is valid you should now be able to view a list of your Databases in the Database drop down. Select the database to connect to and then select the Collection.
Once you are done click Connect & Create Library Connection to connect and save the connection to your Mongo DB. You only need to save the connection once per cluster as you will be able to access all of the databases and subsequent collections from the connection library window.
Getting Data into Mongo
If you have a clean collection in your database with no data in it, you will need to ensure that one document is added to Mongo so then Data Sync can discover a schema (the columns).
Luckily we have made it easy to get a JSON file in Data Sync, you can now preview your data and export it as a JSON file.
In this case we only need one record to upload. To get this filter the source data to return one record e.g. ProductID == 1.
Then go to the schema map and click onto Preview A, then click onto the Export JSON button in the preview tab.
We can then open this file copy the data from within the array. We now need to go to Mongo to import it.
In Mongo, click onto your collection and then select INSERT DOCUMENT. Click onto the curly braces to switch to the JSON view, remove the text already there and paste the JSON we copied a moment ago into the window. Click Insert to insert it.
Now in Data Sync, refresh your connection by clicking onto the refresh button in the data source toolbar. You should now have columns available and ready to map.
Map the Columns
The next step is to map your source columns to the columns in Mongo and make sure to set a key column. The key column should be unique and be able to be used to identify each record. If this is not unique, your data will show as having duplicates which will be excluded from the sync.
Compare, Preview and Sync
Once you have mapped your columns you can run the compare to view the differences between your source and target. Click onto the Compare A > B button in the toolbar and the results will then be shown on screen. You can view the additions, updates and deletes to be made if there are any.
Please note that deletes are disabled by default. To enable them please set EnableDelete to True on the target properties.
In this example when we run the compare there are 76 records to add to Mongo DB.
If you have a large data set to add or delete and the project takes a while to run, you should find that setting UpdateBatchSize to 25 in the target properties gives you a better performance. Please note that updates will still do 1 record at a time.
Once you are ready click Synchronise and then Start to begin the sync. The documents will then be imported to Mongo DB.
Make sure to save the project so that you can use it again in the future.
Getting Data Out of Mongo
To get Data out of Mongo DB you simply reverse the process above. Start by connecting your source to your Mongo collection.
If you haven't already saved your connection then please see the guidance on how to get the connection string below. Otherwise if you have saved the connection to the connection library, you can expand the tree and select the collection you want to connect to. Either drag and drop this onto the source window or right click and select Connect to Source (A).
Connecting to Mongo for the first time
You can find your connection string in Mongo by going to Databases > Connect > Connect your Application.
Copy the connection string and make sure to edit it so that you change <password> to use your actual password.
Enter this connection string into the ConnectionString field. If the connection string is valid you should now be able to view a list of your Databases in the Database drop down. Select the database to connect to and then select the Collection.
Once you are done click Connect & Create Library Connection to connect and save the connection to your Mongo DB. You only need to save the connection once per cluster as you will be able to access all of the databases and subsequent collections from the connection library window.
Export the Preview
If this is a one time export, rather than connecting to a target you can export the preview results. To do this start by adding the columns from Mongo that you want to be included in the export. Then click onto Preview A in the schema window toolbar, which will show you a list of all of the data within those columns.
To export click onto one of the options. Either Export Excel, Export CSV, Export XML DataSet, or Export Json.
You can then save the file to your file system and the data will be exported.
Connect to your Target
If this is likely to be a regular export then you can connect the target datasource window to your target dataset.
For this example we are creating a new SharePoint list to hold our Mongo DB data, using the quick create function built into Data Sync.
Start by adding the columns you want to include in the export to the schema map. Then go to Tools > Create SharePoint List
Now select your SharePoint connection from the dropdown and configure the other fields as you need to. Note that you will need to have configured and saved your connection to SharePoint in order to use this feature.
Once this has been created it will be loaded into the target datasource window and the corresponding columns should be matched in the schema map. Double check that the columns are correct and you are then ready to move onto comparing and synchronising the results.
Compare & Sync
Once you have determined your columns are mapped correctly you can run the compare to view the differences between your source and target. Click onto the Compare A > B button in the toolbar and the results will then be shown on screen. You can view the additions, updates and deletes to be made if there are any.
Please note that deletes are disabled by default. To enable them please set EnableDelete to True on the target properties.
In this example when we run the compare there are 35 records to add to the new list.
If you have a large data set to add or delete and the project takes a while to run, you should find that setting UpdateBatchSize to 25 in the target properties gives you a better performance. Please note that updates will still do 1 record at a time.
Once you are ready click Synchronise and then Start to begin the sync. The data will then be added to the list in SharePoint.
Finally make sure to save the project so that you can use it again in the future.
These are just a few ways you can use Data Sync to integrate with your business data. For more idea take a look at our other blog posts.