I’m working on importing data from a collection to Power BI. The new named connector is working great, but when the preview shows up, the schema is based on some old test records left in the collection. Here’s what I’ve tried so far:
- Removed the old documents from the collection.
- Set the Schema Sample Size Documents per database to 0 per this post.
- Tried to generate a new schema in mongosh using the instructions here from @Alexi_Antonino, but I’m getting an error “MongoServerError: no such command: ‘sqlGenerateSchema’”
Hi @Joel_Zehring I think I received this error when I didn’t have enough permissions to run this command. I am very confident that once we get this schema regenerated, all will be well for you. But your second bullet point, that is something to do with our older BI Connector, and not for the new Custom Power BI Connector (Atlas SQL)- so they are not related. I wanted to let you know this to alleviate some confusion.
For Atlas SQL, you should have a federated database (either one you created or one that was created through the SQL Quickstart). You could either create a new virtual collection in your Data Federation configuration or you could delete and recreate your Quick Start Federated DB - this should also give you an updated SQL Schema, reflecting any new schema changes in your source collection.
email me if you’d like to do a screen share session and we can make sure everything is set.
alexi.antonino@mongodb.com
2 Likes
when i run sqlGetSchema i get the default answer: { “ok” : 1, “metadata” : { }, “schema” : { } }
has anyone managed to generate the schema successfully? i also can’t run the sqlGenerateSchema command
Hello @Matheus_Brito welcome to the community. A few things to help.
First, when you run the sqlGeneratSchema, you must do so from the admin db. Here are some instructions that might help. You may need permissions to do this, let me know if you get stuck.
Also, when using Shell, I needed to change my results output to show more verbose response. If you enter this command in: config.set(‘inspectDepth’, Infinity)
It will be able to show the whole schema back.
Hope this helps.
Alexi
1 Like
@Alexi_Antonino thanks for you reply! I’m going to try these commands but first I want to clarify some points:
I have a collection with almost 300k documents with polymorphic data and I’m not sure how big should be the sampleSize. To sample between ALL documents should I set it to 0, right?
To be sure I have a schema containing all the fields I need, instead of running sqlGenerateSchema, can I run sqlSetSchema with an exported schema from MongoDB Compass? All I have to do is paste the generated JSON’s schema inside the sqlSetSchema command?
@Alexi_Antonino I try to run sqlGenerateSchema from the admin db and I’m getting “not authorized” response. The user who run these commands need any specific role or permission? I can’t find this info at documentation.
running the config change you suggested in the Compass Shell produces the following error ```
Error: clone(t={}){const r=t.loc||{};return e({loc:new Position("line"in r?r.line:this.loc.line,"column"in r?r.column:……)} could not be cloned.
Hello @Michael_Doyle1 - I haven’t run this in Compass Shell lately. I will try it now to make sure it is still supported there. I did run the sqlGenerateSchema yesterday within MongoSh without issues. I will report back soon about Compass.
Hi Alexi, it’s only supported in Mongo Shell now, not compass. we have it working. Thanks, Mike
@Alexi_Antonino I have been running this command successfully since you explained it to me step by step
but I had a problem with one of my collections last time, apparently the schema is created normally but when trying to access the data I receive the following error:
ODBC: ERROR [HY000] [MongoDB][Core] Trying to execute query failed with error: Kind: Command failed: Error code 96 (OperationFailed): failed getting result set schema: failed to convert BSON catalog to json_schema::Schema format: data did not match any variant of untagged enum BsonType, correlationID = 179b87ef113feea733a0bcca, labels: {}
Any thoughts on what’s going on?
hello @Matheus_Brito this seems like a bug. I will ask my team about it and let you know if there is anything else that you should provide to help get this sorted out.
Hello @Matheus_Brito would you be able to provide me the results/output from when you ran the sqlGenerateSchema command? This should provide us with an answer as to what is going on (or at least get us much closer). It probably makes more sense to email this to me: alexi.antonino@mongodb.com
Thanks so much,
Alexi Antonino
Product Manager Atlas SQL
I just sent it to your email.
Thanks!
