--- url: /v6.2/en-us/mongodb-guide.md --- # MongoDB BI Connector Reference Guide # 1. Overview # 2. Installation * System: This document is based on Centos 7.x installation * mongodb bi connector version: mongodb-bi-linux-x86_64-rhel70-v2.14.2.tgz ## 2.1 Installation Requirements Installing mongodb bi connector requires the system to have openssl installed. If not installed, you can use the yum command to install it. ```bash yum -y install openssl ``` ## 2.2 Installation Installing mongodb is very simple. Execute the following command to extract: ```bash tar -xzvf mongodb-bi-linux-x86_64-rhel70-v2.14.2.tgz ``` ## 2.3 Configuration The configuration file for mongosqld uses the yaml format, and most parameters also have corresponding command-line parameters. ### 2.3.1 Log Parameters ```yml systemLog: logAppend: logRotate: "rename" or "reopen" path: quiet: verbosity: ``` Parameter Description: Parameter|Type|Default Value|Corresponding Command-line Parameter|Description -|-|-|-|- systemLog.logAppend|boolean||--logAppend|Outputs logs to the log file specified by `systemLog.path`, and must set `systemLog.logRotate`| systemLog.path|string|rename|--logRotate|Specifies how the log should rotate, with two options: **rename** : Closes the existing file and renames it, appending a timestamp in [RFC3339 format](https://www.ietf.org/rfc/rfc3339.txt) to the filename. A new log file is then created. **reopen** : Closes the file and then reopens it.| systemLog.path|string||--logPath|Specifies the file to store the output logs| systemLog.quiet|boolean||--quiet|Hides all log output| systemLog.verbosity|integer||--verbose / -v|Sets this option to output more detailed logs| ### 2.3.2 Schema Parameters ```yml schema: path: maxVarcharLength: ``` Parameter Description: Parameter|Type|Default Value|Corresponding Command-line Parameter|Description -|-|-|-|- schema.path|string||--schema|Specifies the schema file or directory| schema.maxVarcharLength|integer||--maxVarcharLength|Specifies the maximum character length for varchar fields. If exceeded, it will be truncated to the specified length and a warning log will be printed| ### 2.3.2 Data Sample Parameters ```yml schema: sample: size: prejoin: namespaces: uuidSubtype3Encoding: <[old|csharp|java]> stored: mode: <[custom|auto]> source: name: refreshIntervalSecs: ``` Parameter Description: Parameter|Type|Default Value|Corresponding Command-line Parameter|Description -|-|-|-|- schema.stored.mode|integer|custom|--schemaMode|Configures the sample model for mongosqld, must be used with `schema.stored.source`, with two options: **custom** : mongosqld reads the stored schema from the MongoDB database **auto** : mongosqld samples the schema and writes it to the MongoDB database| schema.stored.source|string||--schemaSource|Specifies the database to store schema information| schema.stored.name|string|defaultSchema|--schemaName|The schema name used for reading and writing| schema.sample.size|integer|1000|--sampleSize|The number of documents sampled per database when collecting schema information| schema.sample.prejoin|boolean||--prejoin|Schema option to combine array and non-array data into a single table| schema.sample.namespaces|string or string array||--sampleNamespaces|Specifies the database and collection to include or exclude in the data sampling process for creating the schema| schema.sample.uuidSubtype3Encoding|string||--uuidSubtype3Encoding / -b|Specifies the encoding used to generate the UUID binary subtype, including: **old** : Old BSON binary subtype representation **csharp** : C# or .NET legacy UUID representation **java** : Java legacy UUID representation| schema.refreshIntervalSecs|integer|0|--schemaRefreshIntervalSecs|The interval in seconds for mongosqld to resample and create the schema. The default is 0, meaning no resampling after initialization during the connection. The value must be a positive integer| ### 2.3.3 Runtime Parameters ```yml runtime: memory: maxPerStage: maxPerServer: maxPerConnection: ``` Parameter Description: Parameter|Type|Default Value|Corresponding Command-line Parameter|Description -|-|-|-|- runtime.memory.maxPerStage|integer|||Specifies the maximum memory (in bytes) that can be used by a query execution stage| runtime.memory.maxPerServer|integer|||Specifies the maximum memory (in bytes) that can be used by the mongosqld process| runtime.memory.maxPerConnection|integer|||Specifies the maximum memory (in bytes) that can be used by a mongosqld client connection| ### 2.3.4 Network Parameters ```yml net: bindIp: port: unixDomainSocket: enabled: pathPrefix: filePermissions: ssl: mode: allowInvalidCertificates: PEMKeyFile: PEMKeyPassword: CAFile: minimumTLSVersion: ``` Parameter Description: Parameter|Type|Default Value|Corresponding Command-line Parameter|Description -|-|-|-|- net.bindIp|string|127.0.0.1|Host part of --addr|Specifies the host address to listen on, multiple addresses separated by ","| net.port|integer|3307|Port part of --addr|Specifies the port to listen on| net.unixDomainSocket.enabled|boolean||`true` : Indicates listening on a Unix domain socket `false` : Disables listening on a Unix domain socket, equivalent to the command-line parameter `--noUnixSocket`| net.unixDomainSocket.pathPrefix|string|/tmp|--unixSocketPrefix|Specifies the directory for the mongosqld Unix domain socket. mongosqld will create a mysql.sock socket in this path. If not specified, it will create /tmp/mysql.sock| net.unixDomainSocket.filePermissions|string|448|--filePermissions|Specifies the permissions for the Unix domain socket file| net.ssl.allowInvalidCertificates|boolean||--sslAllowInvalidCertificates|Allows MySQL clients to provide invalid client TLS/SSL certificates| net.ssl.mode|string|disabled|--sslMode|Enables or disables TLS/SSL connections to mongosqld. Includes the following 3 options: **disabled** : mongosqld does not accept connections protected by TLS/SSL **allowSSL** : mongosqld accepts connections protected by TLS/SSL (also accepts connections without TLS/SSL) **requireSSL** : mongosqld **only** accepts connections protected by TLS/SSL| net.ssl.PEMKeyFile|string||--sslPEMKeyFile|Specifies the `.pem` file containing the TLS/SSL certificate and MySQL client key. Can use relative or absolute paths| net.ssl.PEMKeyPassword|string||--sslPEMKeyPassword|Specifies the password to decrypt the private key specified by `net.ssl.PEMKeyFile`| net.ssl.CAFile|string|--sslCAFile|Specifies the mongosqld `.pem` file containing the root certificate chain of the certificate authority. Can use relative or absolute paths| net.ssl.minimumTLSVersion|string|TLS1_1|--minimumTLSVersion|Specifies the minimum TLS version, with 3 options: **TLS1\_0**, **TLS1\_1**, **TLS1\_2**| ### 2.3.5 Security Parameters ```yml security: enabled: defaultMechanism: defaultSource: gssapi: hostname: serviceName: ``` Parameter Description: Parameter|Type|Default Value|Corresponding Command-line Parameter|Description -|-|-|-|- security.enabled|boolean||--auth|Requires client requests to be authenticated| security.defaultMechanism|string|SCRAM-SHA-1|--defaultAuthMechanism|Specifies the default authentication mechanism, including **SCRAM-SHA-1**, **SCRAM-SHA-256**, **PLAIN**, **GSSAPI**| security.defaultSource|string|admin|--defaultAuthSource|Specifies the default source for MongoDB authentication| security.gssapi.hostname|string|First IP specified by `net.bindIp`|--gssapiHostname|Configures the FQDN for Kerberos authentication| security.gssapi.serviceName|string|mongosql|--gssapiServiceName|The service name registered with Kerberos| security.gssapi.constrainedDelegation|string|False|--gssapiConstrainedDelegation|Uses proxy credentials for Kerberos authorization, enabling constrained delegation| ### 2.3.6 MongoDB Host Parameters ```yml mongodb: versionCompatibility: net: uri: ssl: enabled: allowInvalidCertificates: allowInvalidHostnames: PEMKeyFile: PEMKeyPassword: CAFile: CRLFile: FIPSMode: auth: username: password: source: mechanism: gssapiServiceName: ``` Parameter Description: Parameter|Type|Default Value|Corresponding Command-line Parameter|Description -|-|-|-|- mongodb.versionCompatibility|string||--mongo-versionCompatibility|Limits the features provided by mongosqld based on the given MongoDB version. This is only necessary when the replica set and its members use different versions, or during a rolling upgrade of MongoDB| mongodb.net.uri|string|mongodb://localhost:27017|--mongo-uri|Specifies the MongoDB connection string| mongodb.net.ssl.enabled|boolean|False|Indicates whether to use TLS/SSL to connect to the MongoDB instance| mongodb.net.ssl.allowInvalidCertificates|boolean||--mongo-sslAllowInvalidCertificates|Allows MongoDB to provide invalid TLS/SSL certificates| mongodb.net.ssl.allowInvalidHostnames|boolean||--mongo-sslAllowInvalidHostnames|Allows mongosqld to connect to MongoDB using a hostname different from the one in the TLS/SSL certificate| mongodb.net.ssl.PEMKeyFile|string||--mongo-sslPEMKeyFile|Specifies the `.pem` file containing the TLS/SSL certificate and key for mongosqld to connect to MongoDB. Can use relative or absolute paths| mongodb.net.ssl.PEMKeyPassword|string||--mongo-sslPEMKeyPassword|Specifies the path to the file containing the certificate and private key for connecting to MongoDB| mongodb.net.ssl.CAFile|string||--mongo-sslCAFile|Specifies the MongoDB `.pem` file containing the root certificate chain of the certificate authority. Can use relative or absolute paths| mongodb.net.ssl.CRLFile|string||--mongo-sslCRLFile|Specifies the MongoDB `.pem` file containing the list of revoked certificates| mongodb.net.ssl.FIPSMode|boolean||--mongo-sslFIPSMode|Enables FIPS mode in the installed OpenSSL library| mongodb.net.ssl.minimumTLSVersion|string||--mongo-minimumTLSVersion|Specifies the minimum TLS version, with 3 options: **TLS1\_0**, **TLS1\_1**, **TLS1\_2**| mongodb.net.auth.username|string||--mongo-username / -u|Specifies the username for discovering the schema. Only needed if --auth is enabled. Must be a valid MongoDB user with `listDatabases` permission| mongodb.net.auth.password|string||--mongo-password / -p|Specifies the user password| mongodb.net.auth.source|string|admin|--mongo-authenticationSource|Specifies the database for authentication| mongodb.net.auth.mechanism|string|SCRAM-SHA-1|--mongo-authenticationMechanism|Specifies the authentication mechanism, including **SCRAM-SHA-1**, **SCRAM-SHA-256**, **PLAIN**, **GSSAPI**| mongodb.net.auth.gssapiServiceName|string|mongodb|--mongo-gssapiServiceName|Sets the Kerberos SPN for Kerberos authentication| ### 2.3.7 Process Management Parameters ```yml processManagement: service: name: displayName: description: ``` Parameter Description: Parameters|Type|Default Value|Corresponding Command Line Parameter|Description -|-|-|-|- processManagement.service.name|string||--serviceName|System service name for running mongosqld| processManagement.service.displayName|string||--serviceDisplayName|Display name for the system service running mongosqld| processManagement.service.description|string||--serviceDescription|Description for the system service running mongosqld| ## 2.4 Configuration File Example Below is a sample configuration file: ```yml 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" ``` # 3. Start 1. Start using command line parameters ```bash bin/mongosqld --mongo-uri "mongodb://127.0.0.1:27017" --auth -u root -p 123456 --addr "0.0.0.0:3307" ``` 2. Start using a configuration file ```bash bin/mongosqld --config /mongosqld.conf ``` # 4. Containerization ## 4.1 Create Image 1. Write a Dockerfile ```dockerfile FROM ubuntu:xenial COPY mongo* /usr/local/bin/ RUN apt-get update \ && apt-get install -y --no-install-recommends ca-certificates numactl procps gnupg dirmngr gnupg-curl \ && apt-get install -y rsyslog \ && service rsyslog start EXPOSE 3307 ENTRYPOINT ["mongosqld"] ``` 2. Write a build.sh script ```bash #! /bin/sh cd $(dirname $0) set -ex DOCKER_FILE=mongodb-bi.Dockerfile IMAGE_NAME=mongodb-bi-connector IMAGE_VERSION=2.14.2 MONGODB_BI="mongodb-bi-linux-x86_64-ubuntu1604-v${IMAGE_VERSION}" MONGODB_BI_URL="https://info-mongodb-com.s3.amazonaws.com/mongodb-bi/v2/${MONGODB_BI}.tgz" wget -N ${MONGODB_BI_URL} tar -xzvf ${MONGODB_BI}.tgz docker build -f $DOCKER_FILE -t ${IMAGE_NAME}:${IMAGE_VERSION} ${MONGODB_BI}/bin/ ``` 3. Create the image Execute the following command ```bash sh build.sh ``` ## 4.2 Start Assuming a mongodb has already been installed with the following information: |Configuration|Value| |-|-| |Address|192.16.1.10| |Port|27017| |Username|mongodb| |Password|mongodb| The start command is as follows: ```bash docker run --name mongodb-bi -i -t -p 3307:3307 -d mongodb-bi-connector:2.14.2 --mongo-uri "mongodb://192.168.1.10:27017" --auth -u mongodb -p mongodb --addr "0.0.0.0:3307" ``` # Appendix ## Official Website https://docs.mongodb.com/bi-connector/current/local-quickstart/