Mongo-BI（bi-connector）配置使用笔记

一、概述

MongoDB 官方提供的 BI Connector ，可以用来通过SQL(MySQL协议)方式直接访问MongoDB。

仅可以查询数据(基本上聚合函数、多表关联查询等都可支持)，但不能通过此方式编辑或删除mongodb数据。

bi-connector官方文档：https://docs.mongodb.com/bi-connector/master/reference

二、下载安装

bi-connector支持不同平台安装部署，这里针对Linux环境安装部署配置进行记录。

通过官网下载：https://www.mongodb.com/try/download/bi-connector

我这里下载的文件版本为mongodb-bi-linux-x86_64-rhel70-v2.14.0.tgz

下载后解压到/usr/local/mongodb-bi/目录

三、创建证书

当MongoDB启用认证时，bi-connector必须要配置使用证书，才能通过bi-connector连接mongodb

这里先创建证书

cd /usr/local/mongodb-bi/
mkdir certs && cd ./certs/
openssl req -nodes -newkey rsa:2048 -keyout myapp.key -out myapp.crt -x509 -days 3650 -subj "/C=US/ST=myapp/L=myapp/O=myapp Security/OU=IT Department/CN=myapp.com"
cat myapp.crt myapp.key > myapp.pem

四、生成Schema

bi-connector需要schema(sql查询时对应的字段、类型与mongodb中对应的字段和类型的映射关系)来支持查询访问。可以有两种方案，一是预生成，即根据现有mongodb中集合，生产对应的静态schema文件；二是动态更新生成，实际生产环境使用时，建议使用预生成，动态方式对服务器存储和压力较大(需要有单独的mongodb库存储动态生成的schema，时间长会比较大)。这里使用预生成方式(缺点是如果集合有字段编号，需要手动重新生成)。

预生成schema命令如下：

cd /usr/local/mongodb-bi/
./bin/mongodrdl --host 192.168.1.100:27017 --username myapp --password mypwd --db myapp --authenticationDatabase myapp --out schemas/schemas.drdl

也可以根据条件限制仅针对指定的库和集合(或视图)生成schema，而不是全部库或集合

cd /usr/local/mongodb-bi/
./bin/mongodrdl --host 127.0.0.1:27017 --username myapp --password mypwd --db myapp --authenticationDatabase myapp sample --db myapp --collection view_* --sampleSize 10000 --out ./schemas/schemas.drdl

五、修改配置 mongosqld-config.yml

解压目录下默认有example-mongosqld-config.yml，拷贝一份改名为mongosqld-config.yml
根据实际修改配置：

## This is a example configuration file for mongosqld.

## The full documentation is available at:
## https://docs.mongodb.com/bi-connector/master/reference/mongosqld/#configuration-file

## Network options - configure how mongosqld should accept connections.
## https://docs.mongodb.com/bi-connector/master/reference/mongosqld/#network-options
net:
  bindIp: "0.0.0.0" # To bind to multiple IP addresses, enter a list of comma separated values.
  port: 3307
  # unixDomainSocket:
  #   enabled: false
  #   pathPrefix: "/var"
  #   filePermissions: "0600"
  ssl:
    mode: "allowSSL"
    allowInvalidCertificates: true
    PEMKeyFile: './certs/myapp.pem'
    # PEMKeyPassword: <string>
    # CAFile: <string>
    minimumTLSVersion: TLS1_0

## MongoDB options - configure how mongosqld should connect to your MongoDB cluster.
## https://docs.mongodb.com/bi-connector/master/reference/mongosqld/#mongodb-host-options
mongodb:
  # versionCompatibility: <string>
  net:
    uri: "mongodb://192.168.1.100:27017" # https://docs.mongodb.com/manual/reference/connection-string/#mongodb-uri
    ssl:
      enabled: false
    ## https://docs.mongodb.com/bi-connector/master/reference/mongosqld/#mongodb-tls-ssl-options
    #   allowInvalidCertificates: false
    #   allowInvalidHostnames: false
    #   PEMKeyFile: <string>
    #   PEMKeyPassword: <string>
    #   CAFile: <string>
    #   CRLFile: <string>
    #   FIPSMode: false
    #   minimumTLSVersion: TLSv1_1
    auth:
      username: myapp
      password: mypwd
      source: myapp # This is the name of the database to authenticate against.
      mechanism: SCRAM-SHA-1
     #gssapiServiceName: mongodb

# Security options - configure mongosqld's authentication (disabled by default).
## Enable security options if your MongoDB cluster requires authentication.
## https://docs.mongodb.com/bi-connector/master/reference/mongosqld/#security-options
security:
  enabled: true
  defaultMechanism: "SCRAM-SHA-1"
  defaultSource: "myapp"
#   gssapi:
#     hostname: <string>
#     serviceName: "mongosql"

## Logging options
## https://docs.mongodb.com/bi-connector/master/reference/mongosqld/#logging-options
systemLog:
  ## The path to the file where log output will be written to.
  ## Defaults to stderr.
  path: ./mongosqld.log
  quiet: true
  ## 0|1|2 - Verbosity of the log output, this is overridden if `quiet` is true.
  verbosity: 1
  logAppend: true
  #logRotate: "rename" # "rename"|"reopen"

## Schema options
## These configuration options define how the mongosqld should sample your MongoDB
## data so that it can be used by the relational application.
## https://docs.mongodb.com/bi-connector/master/reference/mongosqld/#data-sampling-options
schema:
  ## If you've generated a DRDL schema file using mongodrdl, you can supply the
  ## path for mongosqld to use that schema DRDL file.
  path: ./schemas
  maxVarcharLength: 65535
  ## Use the `refreshIntervalSecs` option to specify an interval in seconds for
  ## mongosqld to update its schema, either by resampling or by re-reading from
  ## the schema source. The default value for this option is 0, which means that
  ## mongosqld does not automatically refresh the schema after it is
  ## initialized.
  refreshIntervalSecs: 0
  #stored:
    #mode: "custom" # "auto"|"custom"
    #source: "mongosqld_data" # the database where schemas are stored in stored-schema modes
    #name: "schema" # the named schema to read/write to in stored-schema modes
  sample:
    size: 1000 # The amount of random documents we sample from each collection.
    namespaces: ["*.*"]
    #namespaces: ["myapp*.view_*"]
    prejoin: false
    # uuidSubtype3Encoding: "old" # <[old|csharp|java]>

## Process management options
## https://docs.mongodb.com/bi-connector/master/reference/mongosqld/#process-management-options
processManagement:
  service:
    name: "mongosql"
    displayName: "MongoSQL Service"
    description: "MongoSQL accesses MongoDB data with SQL"

## Runtime options
## https://docs.mongodb.com/bi-connector/master/reference/mongosqld/#runtime-options
# runtime:
#   memory:
#     ## A value of `0` indicates there is no enforced maximum.
#     maxPerStage: 0
#     maxPerServer: 0
#     maxPerConnection: 0

这里需要注意的是，生产环境 systemLog.quiet需要配置为true,即无日志记录，不然会生产比较大的日志文件

六、启动mongodb-bi

/usr/local/mongodb-bi/bin/mongosqld --config mongosqld-config.yml

七、通过数据库工具和JDBC方式连接使用

通过MySQL工具（如Navicat等）连接时，需要勾选“使用SSL",可以不用实际指定证书。

通过jdbc访问也必须要启用ssl ,加上useSSL=true&verifyServerCertificate=false

例如：

jdbc:mysql://192.168.1.100:3307/myapp?useUnicode=true&zeroDateTimeBehavior=convertToNull&characterEncoding=UTF-8&serverTimeZone=Asia/Shanghai&useSSL=true&verifyServerCertificate=false