Auto Schema Mode (Persist a Schema in MongoDB)

Overview

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.

Regenerate the Schema

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.

User Permissions for Persisted Schemas

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.

db.getSiblingDB("admin").createUser(
  {
    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