Hello,
We are trying to migrate postgreSQL DB to MongoDb using FME Desktop. My question is:-
1. Is it possible to migrate PostgreSQL to MongoDB alongwith the relation from postgre?
2. Is it possible that while migrating first insert in one table and then pass the objectId of that document into another table to maintain the relation?
If yes, How can we do it?
Thanks
Hi!
You will have to recreate all relationships yourself, unfortunately, since the MongoDB Writer is pretty "dumb".
When you set the writer to Document Source = Feature (see docs), you can set the Document ID yourself, which allows you to generate it for any incoming Postgres feature primary key and set it on any relating feature as foreign key (using a FeatureJoiner or whatever).
The MongoDBObjectIDGenerator (created by yours truly :)) might come in handy concerning the ID's, since the format is quite specific.
Hi!
You will have to recreate all relationships yourself, unfortunately, since the MongoDB Writer is pretty "dumb".
When you set the writer to Document Source = Feature (see docs), you can set the Document ID yourself, which allows you to generate it for any incoming Postgres feature primary key and set it on any relating feature as foreign key (using a FeatureJoiner or whatever).
The MongoDBObjectIDGenerator (created by yours truly :)) might come in handy concerning the ID's, since the format is quite specific.
If only Safe would expose more of the FMEObjects API -especially the geometry part - to JS, then we could really do some insane stuff! And an easy import option in the JavascriptCaller for module depencies, so Node can install those on the fly. Now I had to copy the entire BSON module into it to make it a portable transformer...
If only Safe would expose more of the FMEObjects API -especially the geometry part - to JS, then we could really do some insane stuff! And an easy import option in the JavascriptCaller for module depencies, so Node can install those on the fly. Now I had to copy the entire BSON module into it to make it a portable transformer... :/
Hi!
You will have to recreate all relationships yourself, unfortunately, since the MongoDB Writer is pretty "dumb".
When you set the writer to Document Source = Feature (see docs), you can set the Document ID yourself, which allows you to generate it for any incoming Postgres feature primary key and set it on any relating feature as foreign key (using a FeatureJoiner or whatever).
The MongoDBObjectIDGenerator (created by yours truly :)) might come in handy concerning the ID's, since the format is quite specific.
Thank you for the response.
1. What if i want to transform a table with multiple foreign keys?
2. Is there any demo or sample available of how to use MongoDBObjectIDGenerator? Is there any demo available for process of migrating postgre DB to Mongo DB with relation?
Thanks.
@bipin
There really are a lot of ways to do this. It's not that hard.
However, I get the feeling that you are quite new with FME and have the impression that things can all be done with a magical push of a button. Although that's partially true, you first need to cook up the magic recipe though... This takes time and effort. FME has a learning curve to it, but the documentation is good and there's plenty of tutorials. And it's actually an enjoyable tool to learn.
Having said that: I've made an example (hope you're using 2018...) for you that shows how to establish these relationships (for multiple FK's) the easiest way (but not the best). Note that there's no PostgreSQL Reader in the example, since I don't have any available DB's at the moment, so I have created "fake" tables.
The main trick is to rename the mongodb_id attributes generated by the MongoDBObjectIDGenerator (it's one of the easiest transformers in the world...) on the child features so they can be joined with the parent features using the original Postgres ID's, without overwriting other mongodb_id's. Then, set the Document ID attribute on the output feature types.
The other renaming and removal of attributes is just to keep things tidy.
@bipin
There really are a lot of ways to do this. It's not that hard.
However, I get the feeling that you are quite new with FME and have the impression that things can all be done with a magical push of a button. Although that's partially true, you first need to cook up the magic recipe though... This takes time and effort. FME has a learning curve to it, but the documentation is good and there's plenty of tutorials. And it's actually an enjoyable tool to learn.
Having said that: I've made an example (hope you're using 2018...) for you that shows how to establish these relationships (for multiple FK's) the easiest way (but not the best). Note that there's no PostgreSQL Reader in the example, since I don't have any available DB's at the moment, so I have created "fake" tables.
The main trick is to rename the mongodb_id attributes generated by the MongoDBObjectIDGenerator (it's one of the easiest transformers in the world...) on the child features so they can be joined with the parent features using the original Postgres ID's, without overwriting other mongodb_id's. Then, set the Document ID attribute on the output feature types.
The other renaming and removal of attributes is just to keep things tidy.
Thanks a-lot for the example . It was very useful.
I was able to migrate one table from postgreSQL to mongoDB along with relations.
We are using a library named Mongoose along with nodeJS server for fetching data from database. Mongoose is a Object modelling library. We specify the foreign key inside mongoose model whose data is automatically fetched by mongoose using built In 'Populate' method.
We are facing following issue after migration:-
1. Mongoose is unable to fetch data from foreign key referenced collection. I guess the data type of foreign key is not as per the libraries requirement. We are using Mongo DB Object Id as the foreign Key.
Is their any way to specify the data type of foreign key as Object Id? Or Is the error because of some other reason?
Please find attached screenshot of my FME workspace. let me know if i am doing anything in wrong way.
Thanks.
@bipin
There really are a lot of ways to do this. It's not that hard.
However, I get the feeling that you are quite new with FME and have the impression that things can all be done with a magical push of a button. Although that's partially true, you first need to cook up the magic recipe though... This takes time and effort. FME has a learning curve to it, but the documentation is good and there's plenty of tutorials. And it's actually an enjoyable tool to learn.
Having said that: I've made an example (hope you're using 2018...) for you that shows how to establish these relationships (for multiple FK's) the easiest way (but not the best). Note that there's no PostgreSQL Reader in the example, since I don't have any available DB's at the moment, so I have created "fake" tables.
The main trick is to rename the mongodb_id attributes generated by the MongoDBObjectIDGenerator (it's one of the easiest transformers in the world...) on the child features so they can be joined with the parent features using the original Postgres ID's, without overwriting other mongodb_id's. Then, set the Document ID attribute on the output feature types.
The other renaming and removal of attributes is just to keep things tidy.
We would also like to know if their is any FME transformer using which we could create nested JSON objects before migrating data from postgres to MongoDB.
Thanks.
Thanks a-lot for the example . It was very useful.
I was able to migrate one table from postgreSQL to mongoDB along with relations.
We are using a library named Mongoose along with nodeJS server for fetching data from database. Mongoose is a Object modelling library. We specify the foreign key inside mongoose model whose data is automatically fetched by mongoose using built In 'Populate' method.
We are facing following issue after migration:-
1. Mongoose is unable to fetch data from foreign key referenced collection. I guess the data type of foreign key is not as per the libraries requirement. We are using Mongo DB Object Id as the foreign Key.
Is their any way to specify the data type of foreign key as Object Id? Or Is the error because of some other reason?
Please find attached screenshot of my FME workspace. let me know if i am doing anything in wrong way.
Thanks.
I can't really tell from the image if all transformers and the MongoDB output feature type have been set up correctly. You'd have to include the FMW for that.
You say that you were "able to migrate one table from postgreSQL to mongoDB along with relations". However, you are only writing 1 table/collection. Okay, they contain proper foreign keys now, but in order for Mongoose to validate the referential integrity, the referenced collections also have to exist. This means you should write the samplecategories and samplesubcategories to new MongoDB collections as well. Just add 2 more feature types on your MongoDB writer and directly connect them to the PostgreSQL tables. You might want to do some attribute renaming, but since they already have mongodb_id's, it's not required.
Hope that helps.
We would also like to know if their is any FME transformer using which we could create nested JSON objects before migrating data from postgres to MongoDB.
Thanks.
Also check out this tutorial.