Persist a BI Connector Schema in MongoDB

Overview

The --sampleSource 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 --sampleMode 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 --sampleMode to write.

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

mongosqld --sampleSource sampleDb --sampleMode write

By default, mongosqld does not automatically resample data after generating the schema. Specify the --sampleRefreshIntervalSecs 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.

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

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 --sampleSource schemaDb --sampleMode write