Connecting Spotfire to Mongo via JDBC and Info Links
To avoid ODBC connections from Spotfire to Mongo and all related issues around it (every user would need to install and configure an ODBC driver; there are mainly commercial products and the ODBC driver from TIBCO (which is actually the one from Simba) has issues) we tried to use JDBC connections to centrally set up and configure the connections in Information Designer.
We finally succeeded in setting up data sources for Mongo DB. Needed steps are to copy a Mongo DB driver to the lib directory on the Spotfire server (we use mongodb_unityjdbc_full.jar) and to add a data source template. For the latter we use a very simple one:
<jdbc-type-settings> <type-name>MongoDB</type-name> <driver>mongodb.jdbc.MongoDriver</driver> <connection-url-pattern>jdbc:mongo://<host>:<port27017>/<database></connection-url-pattern> <supports-catalogs>false</supports-catalogs> <supports-schemas>true</supports-schemas> <supports-procedures>false</supports-procedures> </jdbc-type-settings>
The tricky part is that the Information Designer creates an xml file on the Spotfire server in the tomcat directory when you create a data source. This xml file is an undocumented feature of Spotfire and it specifies the relational mapping of the Mongo collection. If you modify the structure of your Mongo collection this won't be reflected in Information Designer (even if you refresh the data source) unless you delete this xml file on the server.
So our recommended steps to deal with Mongo data sources and when you need to reflect changes in the collection are:
Delete the xml mapping file on the Spotfire server
Create a table alias for the data source. Once the table alias is expanded a new xml mapping file is created on the server and the alias will reflect the updated structure of the Mongo collection.
You can delete the table alias again
Refreshing the original data source in Information Designer will now also reflect the new structure
You can now create new Column objects and update the Info Links according to the updated structure
Is there any way to avoid steps 1-3? Ideally the xml file on the server would be automatically updated when you refresh the data source in Information Designer. I created also an Idea for this.
I was told that TIBCO would not officially support connections to Mongo via JDBC yet and they only recommend ODBC connections. However since the xml files are created by Spotfire, I wonder why this is the case as the feature is there.