Metabase + MongoDB Connector for BI

Hey all. I have been trying to get Metabase talking to MongoDB 3.4 with Connector for BI. I can see the tables / schema using the Mysql adapter in Metabase but every query results in “parse sql ‘rollback’ error: syntax error at position 9 near rollback”. Here is the debug output from Connector for BI:

I am able to query the the setup using the Mysql cli client and also from a Redash setup.

Is anybody successfully using Metabase + MongoDB Connector for BI? Any suggestions for working around this issue?

Maybe I’m misunderstanding but why use the Mongo Connector for BI with metabase when metabase can just query the Mongo server direct?

Hey @adunbrook. I am comparing different BI tools and not all support mongodb. I also ran into some issues using Metabase directly with mongo. It felt like the results would be more smooth / predictable using an SQL database.

Yea I was in the same boat, we compared Mongo BI Connector and a few 3rd party ones with PowerBI and Tableau. The issue was always the connectors could not handle volumes of data smoothly. Most of that has to do with the translation from SQL Query to MongoQuery language.

What you are suggesting is that pointing Metabase at Mongo BI Connector, which then points at MongoDB, would be quicker because the BI connector knows both SQL and Mongo Query languages, but I think it would most likely add an extra layer of processing/translation which will slow it down.

Another thing to keep in mind, and I don’t know how well you know MongoDB, but I’ve noticed that with large amounts of data Metabase can appear to be slow/buggy but that is because the data is not properly indexed in MongoDB. This is not a problem with Metabase but just how the data is structured in Mongo.

We currently don’t have a large amount of data so the issue wasn’t speed. It was more to do with how Metabase was interpreting our mongo schemas. For example, we have an order collection with a nested payment object but not all orders have a payment and not all payments have exactly the same schema for legacy reasons. We also have an amount field which is stored as a string in mongo but I could not successfully get Metabase to cast it as a number so querying for > 100 didn’t work.

The ability to pass variables to the query via the questions feature also looked nice but wasn’t supported with mongo. Anyway, now I have signed up here I will take another look at the mongo issues we had and see if someone here can help.

@adunbrook, thanks for your advice!

No problem, I never get to answer questions here as there aren’t many Mongo based questions!

Have you tried playing with the Data Model option in the admin menu of Metabase? Once it scans the “columns” of a Mongo collection you can force certain types, and I know there is a “enum” type which should work for the string/int issue.

I think the enum type is for enumerated values. I am not sure that will help me with converting a string field to int. Can you elaborate?

Hmm, I probably misunderstood what changing the column type would do. It doesn’t magically update the queries to cast the real data type to a Number. I am going to look into changing the fields in mongo to a more useful type.

Yes sorry, I got confused by the enum type, yes you’re right it doesn’t have to do with your string -> int problem.

Odds are it’s like you’re thinking where the fields in mongo are not type defined properly. I’ve had that issue in the past with timestamp fields and metabase. Unless I defined in Mongo that the fields were datetime type Metabase would treat it as a string.

If you define the field type in mongo as int, Metabase will pick up on that.

Just ran into the same problem. The problem is that Mongo’s Mysql immitation doesn’t support the “rollback” command. Adding the “useLocalTransactionState=true” to the JDBC connection string in Metabase seems to fix it. This surpresses unneeded calls to rollback.

1 Like

@Oele Can you send me a screenshot of the settings you used to connect to the MongoDB BI connector as I keep getting this error

this is despite loading the server cert etc