An internal error occured on db.runCommand( { sqlGenerateSchema: 1

Working with Data Connectors & Integrations
1

Hey,
I am loading the data into Power BI using Mongodb Atlas SQL ODBC Driver (since new power BI connector does not allow dataset refresh nor allow live query option for now). I was not getting any column names in power BI. After initial research, I found that I have to execute the following command

db.runCommand({
sqlGenerateSchema: 1,
sampleNamespaces: [‘DB.col1’],
sampleSize: 100,
setSchemas: true
})

Once I executed the above command, It initially executed successfully and columns were visible to the power BI. After that, I have exposed new fields/columns into the same collection however Power BI is not showing the new columns/fields. I am under the impression that I have to execute the above command again to expose new fields to Power BI, However, when I am executing this command again, I am getting the following error.

{
ok: 1,
schemas: ,
failedNamespaces: [ { namespace: ‘DB.col1’, error: ‘an internal error occurred’ } ]
}

Could anyone help me out how to resolve this issue or how I can have all fields/columns visible into Power BI?

Best
Shuja

2

hi @Shuja
You are correct that if new fields are added to you underlying data source collection, you would need to regenerate the sql schema to pick up those new fields. We are working on a project now that will allow users to manage the SQL schema from within the Atlas UI, I think this would help tremendously.

As for the error you are now getting, can you verify this namespace does still exist? The fact that you ran this command to success before, leads me to believe you did this in the past with correct syntax etc.

Are you by any chance able to run this command for other virtual collections within your federated db? I just want to rule out that this error maybe caused by some unforeseen data type within the underlying collections or this is a wider spread error.

Screenshot 2023-08-25 at 9.15.00 AM
Screenshot 2023-08-25 at 9.15.00 AM1285×722 152 KB

3

Hey Alexi, Thanks for the prompt response. I have also tried wild card (*) and unfortunately, I am getting this error for all collections. Would you please let me know if there is other thing that I need to try?

thanks
shuja

4

Hey Alexi, We have further investigated the issue that found that the collections, that are using data lakes throwing this error. We are unable to run schema against these collections. Would you please let us know the solution?

Thanks
Shuja

5

Thanks for this information. I will attempt to recreate this on my end and submit a case to the engineers. Please feel free to email me the screen shot error still, just incase I can’t recreate it and it is not a global issue.

Best,
Alexi