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