Docs Menu
Docs Home
BI Connector

Auto Schema Mode (Persist a Schema in MongoDB)

On this page

  • Overview
  • Regenerate the Schema
  • User Permissions for Persisted Schemas

The --schemaSource option directs mongosqld to use a particular database on the connected MongoDB instance for schema storage. mongosqld can either create a new database to use for schema storage or use an existing database.

If you specify an existing database which has been used previously for schema storage, the --schemaMode option determines whether mongosqld writes new schema data to the specified database or only reads from it. If you specify a database which doesn't currently exist, you must set --schemaMode to auto.

The following example command uses a database named sampleDb to store schema information and sets --schemaMode to auto.

mongosqld --schemaSource sampleDb --schemaMode auto

To learn more about sampling modes, see the Sampling Mode Reference Chart.

By default, mongosqld does not automatically resample data after generating the schema. Specify the --schemaRefreshIntervalSecs option to direct mongosqld to automatically resample the data and regenerate the schema on a fixed schedule.

To force a one-time update of the schema, use the FLUSH SAMPLE command from your SQL client.

If your MongoDB instance uses authentication, you must specify a MongoDB user with write permission on the specified schema database in addition to the permissions described under cached sampling.

To create a user with the necessary permissions to run mongosqld with a persisted schema, you can either create a custom role with the minimum required permissions as described in User Permissions for Cached Sampling, or use the built-in readAnyDatabase role. In either case, the user also needs write permission on the specified schema database.

user: "<username>",
pwd: "<password>",
roles: [
{ "role": "readAnyDatabase", "db": "admin" },
{ "role": "readWrite", "db": "<dbname>" }

In the following example, a user named bicUser with the password myPass provides credentials to read from and update a schema database called schemaDb.

mongosqld --auth -u bicUser -p myPass --schemaSource schemaDb --schemaMode auto
← Standalone Schema Mode (Cached Sampling)