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.

yum -y install openssl

2.2 Installation

Installing mongodb is very simple. Execute the following command to extract:

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

systemLog:
  logAppend: <boolean>
  logRotate: "rename" or "reopen"
  path: <string>
  quiet: <boolean>
  verbosity: <integer>

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 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

schema:
  path: <string>
  maxVarcharLength: <integer>

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

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>

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

runtime:
  memory:
    maxPerStage: <integer>
    maxPerServer: <integer>
    maxPerConnection: <integer>

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

net:
  bindIp: <string>
  port: <integer>
  unixDomainSocket:
    enabled: <boolean>
    pathPrefix: <string>
    filePermissions: <string>
  ssl:
    mode: <string>
    allowInvalidCertificates: <boolean>
    PEMKeyFile: <string>
    PEMKeyPassword: <string>
    CAFile: <string>
    minimumTLSVersion: <string>

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

security:
  enabled: <boolean>
  defaultMechanism: <string>
  defaultSource: <string>
  gssapi:
    hostname: <string>
    serviceName: <string>

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

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>

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

processManagement:
  service:
    name: <string>
    displayName: <string>
    description: <string>

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:

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

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

bin/mongosqld --config <pathToConfigFile>/mongosqld.conf

4. Containerization

4.1 Create Image

1. Write a 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

#! /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

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:

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/