Docs Menu

Docs HomeView & Analyze DataBI Connector

mongosqld

On this page

Note

The MongoDB Connector for BI and associated utilities are compatible with all currently supported MongoDB server versions.

mongosqld accepts incoming requests from a SQL client and proxies those requests to a mongod or mongos instance.

New in version 2.3:

You can start mongosqld either with a schema file in .drdl format using the --schema option or by sampling data from a MongoDB instance to create the schema.

You can specify which namespace or namespaces to sample data from with the --sampleNamespaces option. If you don't specify any namespaces or a schema file, mongosqld samples data from all databases in the target MongoDB instance except the admin and local databases.

You can specify a database in which to store schema information with the --schemaSource option. Otherwise, mongosqld holds the schema in memory.

Use the --schema option to specify a schema file when starting mongosqld.

mongosqld --schema /path/to/schema-file.drdl

Use mongodrdl to create a schema file from a MongoDB instance.

Use the --schemaSource option to specify a database to store schema information.

mongosqld --schemaSource sampleDb

Use the --sampleNamespaces option to specify databases and collections for mongosqld to sample data from to create the schema.

mongosqld --sampleNamespaces contacts.addresses

See additional usage examples below.

If your MongoDB instance uses authentication, your BI Connector instance must also use authentication. The user that connects to MongoDB via the mongosqld program must have permission to read from all the namespaces you wish to sample data from.

For more details about MongoDB user permissions in BI Connector, see User Permissions for Cached Sampling.

For more information on MongoDB users and roles, see Role-Based Access Control.

See the example below of mongosqld with authentication.

New in version 2.6.

The default minimum TLS version is 1.1 for all client connections. This includes incoming client connections to mongosqld and outgoing connections to MongoDB.

You can adjust this setting for incoming connections with the --minimumTLSVersion option and for outgoing connections with the --mongo-minimumTLSVersion option.

--help

Returns information on the options and use of mongosqld.

--addr

Default: 127.0.0.1:3307

Specifies the host address to listen on.

--version

Returns the mongosqld release number.

--config <path>

Specifies the path to a configuration file.

--mongo-uri <uri>

Default: mongodb://localhost:27017

Specifies a MongoDB connection string to connect to.

The --mongo-uri option supports the following options within the connection string:

For more information on these URI options see Read Preference Options and Replica Set Option.

For options set in the Mongo URI not included in the list above, use the equivalent mongosqld option. For the complete list of mongosqld options, see Command Line Options.

Note

Instead of specifying a username and password in your connection string, run mongosqld with the --auth option to direct mongosqld to pass the authentication credentials provided by the SQL client to the MongoDB server.

Similarly, instead of enabling ssl in the connection string, run mongosqld with --mongo-ssl.

To disable automatic replica set server discovery logic and force a connection to the specified server, use the connect=direct option.

mongosqld --mongo-uri "mongodb://<hostname>:<port>/?connect=direct"

URI options not in the list above nor in the list of supported mongosqld options are not supported.

--mongo-versionCompatibility <version-number>

Restricts mongosqld to using features that the specified version of MongoDB supports. Only necessary when used with replica sets in which members use different MongoDB versions or when performing a rolling upgrade of MongoDB. Only supports MongoDB version 3.2 or later.

For example, if your replica set contains members running MongoDB 3.2 and other members running MongoDB 3.4, set the following option to restrict mongosqld to only use features supported by MongoDB 3.2:

mongosqld --mongo-versionCompatibility 3.2
--maxVarcharLength <length>

New in version 2.2.

Specifies the maximum length, in characters, for all varchar fields. If mongosqld encounters a string that is longer than the maximum length, mongosqld truncates it to the maximum length and logs a warning.

--mongo-username <username>, -u <username>

New in version 2.3.

Specifies the authentication username to use for schema discovery. Only required if --auth is enabled. The user specified by --mongo-username must be a valid MongoDB user with the listDatabases privilege. See mongosqld User Permissions.

--mongo-password <password>, -p <password>

New in version 2.3.

Specifies the authentication password to use for schema discovery. Only required if --auth is enabled. Use in conjunction with --mongo-username.

--mongo-authenticationSource <auth-db-name>

Default: admin

New in version 2.3.

Specifies the database that holds the credentials for the schema discovery user. Only available if --auth is enabled. Use in conjunction with credential options --mongo-username and --mongo-password.

--mongo-authenticationMechanism <authMechanism>

Default: SCRAM-SHA-1

New in version 2.3.

Specifies the authentication mechanism to use for schema discovery. Only available if --auth is enabled. Use in conjunction with credential options --mongo-username and --mongo-password.

Value
Description
SCRAM-SHA-1
New in version 2.6: RFC 7677 standard Salted Challenge Response Authentication Mechanism using the SHA2 hash function.
PLAIN (LDAP SASL)
External authentication using LDAP. You can also use PLAIN for authenticating in-database users. PLAIN transmits passwords in plain text. This mechanism is available only in MongoDB Enterprise.
GSSAPI (Kerberos)
External authentication using Kerberos. This mechanism is available only in MongoDB Enterprise.
--schema <filename>

Specifies the path to a schema file or the schema directory.

--schemaDirectory <directoryname>

Deprecated since version 2.2.

Use --schema instead.

--sampleNamespaces <db.collection>

New in version 2.5.

--sampleNamespaces specifies a database and collection for either inclusion or exclusion from the data sampling process which creates the schema. It is also possible to specify multiple collections from a single databases, or multiple collections from multiple databases. See examples below.

If you do not use the --sampleNamespaces option or the --schema option, mongosqld samples data from all available MongoDB databases and collections except the admin and local databases.

--schemaMode <[custom|auto]>

Default: custom

New in version 2.11.

Configures the sampling mode of mongosqld. Must be used with the --schemaSource option. The following values determine the sampling behavior:

Value
--schemaMode Behavior
custom
mongosqld reads a stored schema from the MongoDB database specified by the --schemaSource option.
auto
mongosqld samples the schema and writes the schema data to the MongoDB database specified by the --schemaSource option.

For more information on configuring the sample mode, see the Sampling Mode Reference Chart.

Important

If mongosqld has authentication enabled, the authenticated user must have the write privilege on the specified --schemaSource database. See Built-In Roles for more information about the readWrite role.

--schemaSource <db-name>

New in version 2.11.

Required whenever the --schemaMode is set. Specifies the database where the schema information is stored.

Note

If you do not specify any of the --schema, --schemaMode, and --schemaSource options, mongosqld holds its schema in memory.

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

--schemaName <db-name>

Default: defaultSchema

New in version 2.11.

Optional. The name of the schema to load from or write to the --schemaSource database. Specifying schema names allows you to store multiple schemas in the --schemaSource database. The behavior depends on the value of --schemaMode:

--schemaName Behavior
custom
The name of the schema to load from the database specified by the --schemaSource option.
auto
The name of the schema to write to the --schemaSource database after the BI Connector samples the schema on startup.

Important

If you upload a custom schema, you must store it with its specified name, using name-schema, and then specify this name to the mongosqld with --schemaName. If you don't store the schema's name when you upload it, the schema name defaults to defaultSchema. If the schema's name doesn't exist, this results in an error from mongosqld similar to the following: MongoDB schema not yet available. Error initializing schema: no schema found for name.

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

--sampleSize <number>

Default: 1000

New in version 2.3.

The number of documents per namespace to sample when gathering schema information.

Set --sampleSize to 0 to include all documents in the specified namespace when building the schema. If you do not specify a namespace, setting --sampleSize to 0 causes mongosqld to consider all documents in all databases (excluding local, admin, and system) when building the schema. See an example below.

--schemaRefreshIntervalSecs <number>

Default: 0

Changed in version 2.11: Renamed --sampleRefreshIntervalSecs to --schemaRefreshIntervalSecs

The interval in seconds at which mongosqld re-samples data to create its schema. The default value is 0, which means that after the initial sampling no re-sampling occurs for the duration of the connection. The specified value must be a positive integer.

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

--uuidSubtype3Encoding <old|csharp|java>, -b <old|csharp|java>

Specify the encoding used to generate UUID binary subtype 3. Choose one of the following values:

  • old: Old BSON binary subtype representation

  • csharp: The C#/.NET legacy UUID representation

  • java: The Java legacy UUID representation

--prejoin

New in version 2.6.

Schema option for combining array and non-array data into a single table.

MongoDB documents which contain arrays are normally translated into tabular format with separate tables for array data and non-array data. Consider a MongoDB collection named test with the following document:

{ "_id" : 1, "a" : 3, "b" : [ "orange", "apple", "pear" ] }

The above collection translates to the following two tables in tabular format:

mysql> select * from test;
+------+------+
| _id | a |
+------+------+
| 1 | 3 |
+------+------+
mysql> select * from test_b;
+------+--------+-------+
| _id | b | b_idx |
+------+--------+-------+
| 1 | orange | 0 |
| 1 | apple | 1 |
| 1 | pear | 2 |
+------+--------+-------+

The --prejoin flag causes data from the a column to be included in the test_b table:

mysql> select * from test_b;
+------+------+--------+-------+
| _id | a | b | b_idx |
+------+------+--------+-------+
| 1 | 3 | orange | 0 |
| 1 | 3 | apple | 1 |
| 1 | 3 | pear | 2 |
+------+------+--------+-------+
--logAppend

Appends new logging output to an existing log file specified by --logPath.

Requires --logRotate.

--logPath <filename>

Default: stderr

Specifies a path to a log file for storing logging output.

--logRotate reopen | rename

Default: rename

Specifies that you want to rotate logs and how they should be rotated.

When this option is set, the logs rotate when you issue a FLUSH LOGS command to the MongoDB Connector for BI or when you restart mongosqld.

If you set --logRotate to rename:
The existing log file is closed. An RFC3339-formatted timestamp is appended to the closed log file. A new log file is created.
If you set --logRotate to reopen:
The existing log file is closed and reopened.

Note

On UNIX and macOS platforms, you can issue a SIGUSR1 signal to restart the mongosqld process and rotate the logs.

--usageLogInterval <number>

Default: 60

New in version 2.14.

The interval, in seconds, at which usage statistics are written to the log. Set to 0 to disable usage logging.

Note

Usage logging is not enabled for Windows.

--verbose, -v

Specifies that mongosqld should provide more detailed log output.

The following table describes the information provided at each log level:

Letter Option
Log Level
Message Content
--quiet
None
Log nothing.
No flag
Default
Log messages that notify user of basic mongosqld events and state changes.
-v
Administrator
Log messages that provie information to mongosqld administrators.
-vv
Developer
Log messages useful to MongoDB support and development.
--quiet

Hides all log output.

--mongo-ssl

Default: False

Instructs mongosqld to use TLS/SSL when connecting to a MongoDB instance.

--mongo-sslPEMKeyFile <filename>

Specifies the .pem file containing both the TLS/SSL certificate and key for mongosqld to use when connecting to MongoDB. You can specify the file name of the .pem file using either using a relative or absolute path.

This option is required when using the --mongo-ssl option to connect to a mongod or mongos that has CAFile enabled without allowConnectionsWithoutCertificates.

--mongo-sslPEMKeyPassword <password>

Specifies the path to a file containing the certificate and private key for connecting to MongoDB.

--mongo-sslAllowInvalidHostnames

Permits mongosqld to connect to a MongoDB server whose hostname differs from the hostname on its TLS/SSL certificate.

--mongo-sslAllowInvalidCertificates

Permits the MongoDB instance to present an invalid server SSL/TLS certificate. When using the allowInvalidCertificates setting, MongoDB logs the use of the invalid certificate as a warning.

--mongo-sslCAFile <filename>

Specifies the MongoDB instance's .pem file containing the root certificate chain from the Certificate Authority. Specify the file name of the .pem file using relative or absolute paths.

Warning

For SSL connections (--mongo-ssl) to mongod and mongos, if the mongosqld runs without the --mongo-sslCAFile, mongosqld will not attempt to validate the server certificates. This creates a vulnerability to expired mongod and mongos certificates as well as to foreign processes posing as valid mongod or mongos instances. Ensure that you always specify the CA file to validate the server certificates in cases where intrusion is a possibility.

--mongo-sslCRLFile <filename>

Specifies the MongoDB instance's .pem file containing the certificate revocation list.

--mongo-sslFIPSMode

Enables FIPS mode in the installed OpenSSL library.

--mongo-minimumTLSVersion <TLS1_0|TLS1_1|TLS1_2>

Default: TLS1_1

Specifies the minimum required TLS version for outgoing connections to a mongod or mongos. instance. The default value TLS1_1 corresponds to TLS version 1.1.

--sslMode <mode>

Default: disabled

New in version 2.3.

Enable or disable TLS/SSL for connections to mongosqld. The argument to the sslMode option can be one of the following:

Value
Description
disabled
mongosqld cannot accept connections secured using TLS/SSL.
allowSSL
mongosqld can accept connections secured using TLS/SSL.
requireSSL
mongosqld can only accept connections secured using TLS/SSL.
--sslPEMKeyFile <filename>

Specifies the .pem file containing both the TLS/SSL certificate and key for MySQL clients. Specify the file name of the .pem file using relative or absolute paths.

--sslPEMKeyPassword <password>

Specifies the password used to decrypt the private key specified by --sslPEMKeyFile.

--sslAllowInvalidCertificates

Permits MySQL clients to present invalid client TLS/SSL certificates.

--sslAllowInvalidHostnames

Permits SQL clients to connect to a mongosqld whose hostname differs from the hostname on its TLS/SSL certificate.

--sslCAFile <filename>

Specifies the mongosqld .pem file containing the root certificate chain from the Certificate Authority. Specify the file name of the .pem file using relative or absolute paths.

--sslCRLFile <filename>

Specifies the mongosqld .pem file containing the certificate revocation list.

--auth

Requires authentication for incoming client requests.

Important

Changed in version 2.4.0.

When authentication is enabled, admin credentials must be provided with the --mongo-username and --mongo-password options or the mongodb.net.auth.username and mongodb.net.auth.password settings in the configuration file.

mongosqld uses the admin credentials to gather metadata on the sampled namespaces and uses the credentials of the connecting client to restrict data to only what the client is authorized to read. For more information on the required permissions for the admin user, see User Permissions for Cached Sampling.

--defaultAuthSource <authSource>

Default: admin

Specifies the default MongoDB authentication source. Set this value to specify a default source that mongosqld uses when authenticating with a MongoDB database. The authentication mechanisms GSSAPI and PLAIN use the $external source, while SCRAM-SHA-1 and SCRAM-SHA-256 use a MongoDB database as its source.

If no value is given for this option it defaults to the the MongoDB admin database.

The $external authentication source stores a reference to system users in a MongoDB database called $external, but the credentials are stored in an external, non-MongoDB system, such as an LDAP server.

Any connection which uses the default value can omit the source parameter from its MySQL or Tableau username.

--defaultAuthMechanism <authMechanism>

Default: SCRAM-SHA-1

Specifies the default authentication mechanism. Set this value to specify a default mechanism for connecting to mongosqld. Any connection which uses this specified default value can omit the mechanism value from its MySQL or Tableau username.

Value
Description
RFC 5802 standard Salted Challenge Response Authentication Mechanism using the SHA1 hash function.
New in version 2.6: RFC 7677 standard Salted Challenge Response Authentication Mechanism using the SHA2 hash function.
PLAIN (LDAP SASL)
External authentication using LDAP. You can also use PLAIN for authenticating in-database users. PLAIN transmits passwords in plain text. This mechanism is available only in MongoDB Enterprise.
GSSAPI (Kerberos)
External authentication using Kerberos. This mechanism is available only in MongoDB Enterprise.
--minimumTLSVersion <TLS1_0|TLS1_1|TLS1_2>

Default: TLS1_1

Specifies the minimum required TLS version for clients to connect to mongosqld. The default value TLS1_1 corresponds to TLS version 1.1.

--serviceName <service-name>

Name of the system service which runs mongosqld.

--serviceDisplayName <service-name>

Display name of the system service which runs mongosqld.

--serviceDescription <service-name>

Description of the system service which runs mongosqld.

--gssapiHostname <hostname>

Default: First IP address for net.bindIp.

New in version 2.5.

An FQDN for the purpose of configuring Kerberos authentication. The Kerberos hostname overrides the hostname only for the configuration of Kerberos.

--gssapiServiceName <service-name>

Default: mongosql

New in version 2.5.

Registered name of the service using Kerberos. This option allows you to override the default Kerberos service name component of the Kerberos SPN, on a per-instance basis. If unspecified, the default value is used.

--mongo-gssapiServiceName <service-name>

Default: mongodb

New in version 2.5.

Set the Kerberos SPN when connecting to Kerberized MongoDB instances. This value must match the service name set on MongoDB instances.

--gssapiConstrainedDelegation

Default: False

New in version 2.11.

Use proxy credentials for Kerberos authorization, enabling constrained delegation. Requires mongosqld service credentials to be present in the client keytab as well as the service keytab. See Configure Kerberos for BI Connector for more information about Kerberos configuration.

--filePermissions <mode>

Default: 0700

Specify the permissions for the Unix domain socket file.

--noUnixSocket

Disable listening on Unix domain sockets.

--unixSocketPrefix <path>

Default: /tmp

Specifies an alternative directory for the mongosqld Unix domain socket.

mongosqld will create a socket file called mysql.sock underneath this path. If you do not specify --unixSocketPrefix, the socket will exist at /tmp/mysql.sock.

--setParameter <parameter>
Parameter
Type
Corresponds to
"polymorphic_type_conversion_mode=<value>"
string

Determines how mongosqld evaluates document fields that are specified with multiple data types. Accepted values are off, fast, and safe. To learn more about these values, see System Variables.

"type_conversion_mode=<value>"
string

Specifies which mode mongosqld uses to convert data types. BI Connector uses its own mode (mongosql) by default. Accepted values are mongosql and mysql. To learn more about these values, see System Variables.

The following example starts mongosqld and uses the --setParameter option to specify the type conversion mode:

mongosqld --setParameter "type_conversion_mode=mongosql"

You may configure mongosqld using a YAML configuration file. This file may contain the settings listed in the following sections.

Note

You may use expansion directives in your configuration file to load externally sourced configuration values. Expansion directives obscure confidential information like security certificates and passwords.

To learn more about using expansion directives, see Externally Sourced Configuration File Values in the MongoDB manual.

systemLog:
logAppend: <boolean>
logRotate: "rename"|"reopen"
path: <string>
quiet: <boolean>
verbosity: <integer>
Name
Type
Corresponds to
systemLog.logAppend
boolean
systemLog.logRotate
string
systemLog.path
string
systemLog.quiet
boolean
systemLog.verbosity
integer
schema:
path: <string>
maxVarcharLength: <integer>
Name
Type
Corresponds to
schema.path
string
schema.maxVarcharLength
integer
schema:
sample:
size: <integer>
prejoin: <boolean>
namespaces: <array of strings>
uuidSubtype3Encoding: <[old|csharp|java]>
stored:
mode: <[custom|auto]>
source: <string>
name: <db-name>
refreshIntervalSecs: <integer>
Name
Type
Corresponds to
schema.stored.mode
string
schema.stored.source
string
schema.stored.name
string
schema.sample.size
integer
schema.sample.prejoin
boolean
schema.sample.namespaces
string or array of strings
schema.refreshIntervalSecs
integer
schema.sample.uuidSubtype3Encoding
string
runtime:
memory:
maxPerStage: <integer>
maxPerServer: <integer>
maxPerConnection: <integer>

Important

BI Connector provides the following options for controlling the amount of system memory which the mongosqld process may use for sending queries to MongoDB. These limits do not include the memory which BI Connector uses for other purposes, such as data structure overhead, so the total amount of memory BI Connector uses will be higher than the limits set with these options.

runtime.memory.maxPerStage

Type: integer

Default: unlimited

Specifies the maximum amount of memory in bytes that a query execution stage may use.

runtime.memory.maxPerServer

Type: integer

Default: unlimited

New in version 2.5.

Specifies the maximum amount of memory in bytes that a mongosqld process may use.

runtime.memory.maxPerConnection

Type: integer

Default: unlimited

New in version 2.5.

Specifies the maximum amount of memory in bytes that a mongosqld client connection may use.

net:
bindIp: <string>
port: <integer>
unixDomainSocket:
enabled: <boolean>
pathPrefix: <string>
filePermissions: <string>
ssl:
mode: <string>
allowInvalidCertificates: <boolean>
PEMKeyFile: <string>
PEMKeyPassword: <string>
CAFile: <string>
Name
Type
Corresponds to
net.bindIp
string

The hostname component of --addr

Changed in version 2.2: To bind to multiple IP addresses, enter a list of comma separated values.

Example

"72.198.41.200,72.198.41.201,72.198.41.202"

net.port
integer
The port component of --addr
net.unixDomainSocket.enabled
boolean
Inverse of --noUnixSocket
net.unixDomainSocket.pathPrefix
string
net.unixDomainSocket.filePermissions
string
net.ssl.allowInvalidCertificates
boolean
net.ssl.mode
string
net.ssl.PEMKeyFile
string
net.ssl.PEMKeyPassword
string
net.ssl.CAFile
string
net.ssl.minimumTLSVersion
string
security:
enabled: <boolean>
defaultMechanism: <string>
defaultSource: <string>
gssapi:
hostname: <string>
serviceName: <string>
Name
Type
Corresponds to
security.enabled
boolean
security.defaultMechanism
string
security.defaultSource
string
security.gssapi.hostname
string
security.gssapi.serviceName
string
security.gssapi.constrainedDelegation
string
mongodb:
versionCompatibility: <string>
net:
uri: <string>
ssl:
enabled: <boolean>
allowInvalidCertificates: <boolean>
allowInvalidHostnames: <boolean>
PEMKeyFile: <string>
PEMKeyPassword: <string>
CAFile: <string>
CRLFile: <string>
FIPSMode: <boolean>
auth:
username: <username>
password: <password>
source: <auth-db-name>
mechanism: <auth-mechanism>
gssapiServiceName: <service>
Name
Type
Corresponds to
mongodb.versionCompatibility
string
mongodb.net.uri
string
mongodb.net.ssl.enabled
boolean
mongodb.net.ssl.allowInvalidCertificates
boolean
mongodb.net.ssl.allowInvalidHostnames
boolean
mongodb.net.ssl.PEMKeyFile
string
mongodb.net.ssl.PEMKeyPassword
string
mongodb.net.ssl.CAFile
string
mongodb.net.ssl.CRLFile
string
mongodb.net.ssl.FIPSMode
boolean
mongodb.net.ssl.minimumTLSVersion
string
mongodb.net.auth.username
string
mongodb.net.auth.password
string
mongodb.net.auth.source
string
mongodb.net.auth.mechanism
string
mongodb.net.auth.gssapiServiceName
string
processManagement:
service:
name: <string>
displayName: <string>
description: <string>
Name
Type
Corresponds to
processManagement.service.name
string
processManagement.service.displayName
string
processManagement.service.description
string
setParameter:
polymorphic_type_conversion_mode: <string>
type_conversion_mode: <string>
Name
Type
Corresponds to
setParameter.polymorphic_type_conversion_mode
string

Determines how mongosqld evaluates document fields that are specified with multiple data types. Accepted values are off, fast, and safe. To learn more about these values, see System Variables.

setParameter.type_conversion_mode
string

Specifies which mode mongosqld uses to convert data types. BI Connector uses its own mode (mongosql) by default. Accepted values are mongosql and mysql. To learn more about these values, see System Variables.

These configuration file options are also available as system variables. For more information, see System Variables.

You may use expansion directives in your configuration file to load externally sourced configuration values. Expansion directives obscure confidential information like security certificates and passwords.

To learn more about using expansion directives, see Externally Sourced Configuration File Values in the MongoDB manual.

Note

The paths used in this example configuration file are Linux-specific. Check your local system documentation to determine the correct paths for your system.

systemLog:
logAppend: false
path: "/var/log/mongosqld/mongosqld.log"
verbosity: 2
security:
enabled: true
mongodb:
net:
uri: "mongo.example.com:27017"
auth:
username: "root"
password: "changeme"
net:
bindIp: 192.0.2.14
port: 3307
ssl:
mode: "allowSSL"
PEMKeyFile: "/vagrant/certificates/mongosqld-server.pem"
CAFile: "/vagrant/certificates/ca.crt"
schema:
sample:
namespaces: "inventory.*"
processManagement:
service:
name: mongosqld
displayName: mongosqld
description: "BI Connector SQL proxy server"

Use the --schema option to specify a schema file for mongosqld to use.

mongosqld --schema /path/to/schema-file.drdl

Use the --sampleNamespaces option to specify a database and collection for mongosqld to sample data from. If you start mongosqld without the --sampleNamespaces option, it samples data from all available databases except the admin and local databases.

The following example samples data from a collection named addresses in a database named contacts:

mongosqld --sampleNamespaces contacts.addresses

To sample data from all the collections in a database, use the --sampleNamespaces option with a wildcard (*).

The following example samples data from all the collections in a database named inventory:

mongosqld --sampleNamespaces 'inventory.*'

You can repeat the --sampleNamespaces at the command line to sample data from multiple collections within a database, or from multiple collections across several databases.

The following example samples data from all the collections in databases named cars and boats:

mongosqld --sampleNamespaces 'cars.*' \
--sampleNamespaces 'boats.*' \

You can mix and match wildcards and specific collections with multiple --sampleNamespaces options.

The following example samples data from all collections in a database named orders, as well as the FY2015 and FY2016 collections from the expenses database:

mongosqld --sampleNamespaces 'orders.*' \
--sampleNamespaces expenses.FY2015 \
--sampleNamespaces expenses.FY2016 \

Note

It is only necessary to enclose the namespace in single quotes when you use a wildcard.

To exclude specified namespaces from the data sampling process, precede the namespace with a ~ (tilde) character. The following example includes all databases and collections except the apples collection in the fruit database:

mongosqld --sampleNamespaces ~fruit.apples

Use the * wildcard character to indicate all collections in a database. The following example excludes the entire vehicles database from data sampling:

mongosqld --sampleNamespaces '~vehicles.*'

If you have a collection in which some documents have different fields from others and you want to ensure that the schema takes them all into consideration, set the --sampleSize option to 0. This causes mongosqld to examine all documents in the specified namespace when it builds its schema.

The following example instructs mongosqld to build the schema based on all documents in the inventory.electronics namespace.

mongosqld --sampleNamespaces inventory.electronics \
--sampleSize 0

Use the --schemaSource option to specify a database in which to store schema information. Use the --schemaMode option to specify whether mongosqld can write to the schema database or only read from it.

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

mongosqld --schemaSource sampleDb --schemaMode auto

Important

If mongosqld has authentication enabled, the authenticated user must have the write privilege on the specified --schemaSource database. See Built-In Roles for more information about the readWrite role.

Use the --schemaRefreshIntervalSecs option to specify an interval in seconds for mongosqld to resample data and regenerate the schema. The default value for this option is 0, which means that mongosqld never resamples data.

If you set a resampling interval with --schemaRefreshIntervalSecs and you specify a schema database with --schemaSource, you cannot set --schemaMode to custom.

The following example does not specify a schema database or a schema file, so it holds its schema in memory. It uses --schemaRefreshIntervalSecs to specify a data resampling interval of 3600 seconds.

mongosqld --schemaRefreshIntervalSecs 3600

To connect mongosqld to a MongoDB instance running with authentication enabled, use the --auth option and provide MongoDB user credentials.

The following example starts mongosqld with credentials for a user named reportsUser with password reportsPass, who has access to the reports.inventory namespace.

mongosqld --auth \
--mongo-username reportsUser \
--mongo-password reportsPass \
--sampleNamespaces reports.inventory

This example does not specify an authenticating database with the --mongo-authenticationSource option, so it defaults to admin. It does not specify an authentication mechanism with the --mongo-authenticationMechanism option, so it defaults to SCRAM-SHA-1.

If you wish to specify a configuration file which saves logs to /var/log/mongosqld.log and loads a schema from /var/schema.drdl, you may save a file such as the following to /etc/mongosqld.conf:

Warning

All paths specified in the configuration file must be absolute, e.g. they must begin with /.

systemLog:
path: /var/log/mongosqld.log
schema:
path: /var/schema.drdl

You may then start mongosqld with the --config option:

mongosqld --config /etc/mongosqld.conf

For more information on starting mongosqld as a system service, see the Installation Guide for your operating system.

Atlas is a cloud service for running, monitoring, and maintaining MongoDB deployments, including the provisioning of dedicated servers for the MongoDB instances.

Atlas uses TLS/SSL to encrypt connections and enforces authentication by default.

Note

With the MongoDB Atlas free tier, BI Connector cannot run aggregation pipelines using the allowDiskUse option. This option allows aggregation stages to write data as temporary files to disk. To review the MongoDB Atlas free tier constraints, see the MongoDB Atlas manual.

mongosqld can use any valid TLS certificate issued by a certificate authority, or a self-signed certificate. If you use a self-signed certificate, although the communications channel will be encrypted, there will be no validation of server identity. Although such a situation will prevent eavesdropping on the connection, it leaves you vulnerable to a man-in-the-middle attack. Using a certificate signed by a trusted certificate authority will permit your MySQL client to verify the server's identity.

For testing purposes, you can create a .pem key file named test.pem using the openssl tool:

openssl req -nodes -newkey rsa:2048 -keyout test.key -out test.crt -x509 -days 365 -subj "/C=US/ST=test/L=test/O=test Security/OU=IT Department/CN=test.com"
cat test.crt test.key > test.pem

Start mongosqld with the following options:

If you named your key file test.pem as in the previous example, you can run the following, substituting your cluster URI and login credentials:

mongosqld --mongo-ssl \
--auth \
--sslPEMKeyFile test.pem \
--mongo-uri <uri> \
--sslMode allowSSL \
--mongo-username <username> \
--mongo-password <password>

Pass your username, password, and authentication database to your SQL client. For example, using mysql without verifying your mongosqld server certificate:

mysql --host <mongosqld-host> --port <mongosqld-port> -u <username>?source=admin -p --ssl-mode required --enable-cleartext-plugin

Refer to Connect from the MySQL Client for more details on using the mysql client with the BI Connector.

The chart below describes types of mongosqld startup configurations and the options used with each.

Mode
mongosqld Options
Behavior
Standalone Schema

mongosqld samples data upon startup. If --schemaRefreshIntervalSecs > 0, resample at specified interval.

Example

mongosqld --schemaRefreshIntervalSecs 3600

For more information, see Standalone Schema Mode (Cached Sampling).

Custom Schema

Read schema data from the database specified by --schemaSource.

Example

mongosqld --schemaMode custom \
--schemaSource schemaDb
Auto Schema

Sample and persist schema data in a user-specified schema database.

Example

mongosqld --schemaMode auto \
--schemaSource schemaDb \
--schemaRefreshIntervalSecs 3600

For more information, see Auto Schema Mode (Persist a Schema in MongoDB).

The following mongosqld option configurations are invalid and cause an error at startup.

Mode
mongosqld Options
Behavior
Standalone Writer (invalid)
This configuration is invalid for a standalone mongod instance because in auto mode there must be a writeable database specified.
Clustered Sampling Reader (invalid)
When used in a MongoDB replica set or sharded cluster, this configuration is invalid. When a database is specified for storing schema data with --schemaSource, --schemaMode must be set to auto so that the schema may be updated from the primary to maintain cluster-wide consistency.
←  MongoDB Connector for BI Componentsmongodrdl →