How can I refresh the schema that's presented to SQL clients?

Greetings all.

I hope that my inquiry is relatively simple, but I have searched thoroughly for a solution, with no luck.

I am evaluating MongoDB Atlas using the M0 Shared Tier Cluster. I have successfully loaded my data to into three collections. I am able to connect to my cluster and view my data in MongoDB Compass. Everything looks fine.

The goal of this project is to view the data using Tableau and Power BI.

While I was loading my data, I connected to my cluster using Tableau and the MongoDB Atlas connection driver. I was able to authenticate and view my data.

The problem is that I cannot see any field changes that were made after the moment I connected with Tableau. I cannot see a new field that I added to a collection, and I cannot see any data at all in a collection that I populated after connecting with Tableau.

From my reading I gather that I need to refresh the schema that MongoDB Atlas is presenting to the client (e.g. Tableau, Power BI). I am perplexed at how to accomplish this. I can find no option to do this, nor can I find an option to restart my cluster.

Before tackling the cloud-based Atlas, I installed MongoDB on my local computer. I faced the same issue. To fix it, I restarted the mongosqld process that’s part of the MongoDB BI Connector package. However, as I’m not using mongosqld to connect to Atlas, that isn’t an option.

I would appreciate any guidance.

Hi @Rick_Byrne

The command you need is FLUSH SAMPLE executed from the SQL Client. When running a local BI-Connector the schema can also be periodically refreshed with the startup option --schemaRefreshIntervalSecs <number> or the corresponding configuration file option.

Thank you Chris for your kind reply.

I’m still having difficulty when I attempt to connect using the Tableau MongoDB Atlas plugin.

Please see the attached screen shots. Atlas apparently doesn’t accept the FLUSH SAMPLE command.

This post was flagged by the community and is temporarily hidden.

Sorry I missed this. The minimum tier to use BI Connector is M10, this has to be enabled specifically on the deployment.

Thank you for responding on a weekend.

I will try again once we upgrade to a higher tier.

RB

This post was flagged by the community and is temporarily hidden.

We have upgraded to the M10 tier, but I’m still having the problem.

I have successfully uploaded my data to our cluster, and I can see the data using MongoDB Compass.

I have installed the connector for Microsoft Power BI. I’m able to connect to the database, but I don’t see the fields I should be seeing. I put FLUSH SAMPLE in the field for the initial query, and I get the error below.

flush error

I have located the option to enable the Business Intelligence Connector. I notice that there’s an additional cost for this, and MongoDB suggests I use the Atlas SQL Interface instead. Correct me if I’m wrong, but using the Business Intelligence Connector involves configuring an ODBC entry on each report user’s computer, whereas with the Atlas SQL Interface, I only have to install, but not configure, the ODBC connector.

I would be happy to elaborate if anything I’ve said is unclear.

RB

@Rick_Byrne Ok - It was unclear if you were using Atlas SQL or the BI Connector. Ideally, you are using Atlas SQL since this is newer and BI Connector will be replaced.
Flush is only recognized with BI Connector. I have reviewed your initial post and I can suggest a few things.

First, you are using Tableau Desktop (I believe), and to use Atlas SQL an M0 will work just fine. You will have needed to enable Atlas SQL (either with the Quickstart or the Advanced) and this enablement will provide you with the SQL endpoint (it is actually pointing to a federated data base). It sounds like you passed in this SQL endpoint, a database name (this is necessary), and your login credentials. You see your data (represented as tables and rows), but some fields (maybe they are new) are not being represented?

To fix this, we need to regenerated you SQL Schema. You can do this today within MongoSH. We have some Atlas UI for SQL Schemas coming out in March/April - so this will be much easier then. Until the UI is available, you can generate a new SQL Schema by running this command. I would use a sample size that is large enough to find the missing fields. You run this command by namespace, so it doesn’t take long to run. Now, if you run this and still those missing fields do not show up, there is a command to force or set the schema, but let’s start with the generate command.

2 Likes

Thank you for your prompt response to my inquiry.

Thank you for this information. I’m pleased to learn that MongoDB will be favoring the Atlas SQL going forward, because the Atlas SQL is easier to deploy on the machines of the users who will be using my reports.

Thank you for the clarification that flush is only recognized with the BI Connector.

Exactly. This is a very eloquent explanation of my situation.

Running this command solved my problem. Thank you. For the benefit of others who may find this post in the future, replace the word “datalake” with the name of your database. Replace testDL with the name of your collection, or, as in the last code example, replace testDL is an asterisk to rebuild the schemas for all your collections.

Thank you again for your patient assistance, Alexi.

2 Likes

This post was flagged by the community and is temporarily hidden.

This post was flagged by the community and is temporarily hidden.

This post was flagged by the community and is temporarily hidden.

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.