Navigation

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

See Sampling Mode Reference Chart for more information on sampling modes.

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