报表工具 连接 MongoDB 问题处理

背景

由于mongoDB 官方未提供JDBC驱动包，在使用 davinci 连接mongoDB 时遇到了问题；

问题

1. 没有可用的驱动包；
2. 使用 unityjdbc 进行连接时， 连接成功但是读取库表信息有误， 无法使用；
3. 使用mongoDB官方提供的连接器连接有安全认证的数据库，再使用工具连接 连接器 对外的服务时报错；

2022-10-20T13:53:59.290+0800 E NETWORK    [conn2] handshake error: ERROR 1759 (HY000): ssl is required when using cleartext authentication
2022-10-20T13:53:59.290+0800 I NETWORK    [conn2] end connection 127.0.0.1:57270 (0 connections now open)

解决方案

1. 使用官方提供的 bi-connector（专门为BI工具提供的连接器）对数据库进行代理；
2. 报表工具通过 bi-connector暴露出来的地址进行连接（连接方式和SQL语法同Mysql）；
   mongoDB官方文档地址
   https://www.mongodb.com/docs/bi-connector/current/reference/mongosqld/
   重点参考资料: https://www.jianshu.com/p/ba656bf5df26

详细操作步骤

1. 下载安装对应版本 bi-connector ， 地址 https://www.mongodb.com/try/download/bi-connector ， 默认安装路径在 C:\Program Files\MongoDB\Connector for BI[版本号]
2. 如果数据库在本机且没有设置权限认证，直接启动 bin 目录下的 mongosqld.exe , 使用 127.0.0.1:3307 即可直接访问；如果有认证请继续往下看；
3. 下载安装 openssl, 这个链接是win版本的， 其他系统可自行百度 http://slproweb.com/download/Win64OpenSSL_Light-3_0_5.exe
4. 配置openssl 环境变量

新建变量（默认路径）
OPENSSL_HOME  
C:\Program Files\OpenSSL-Win64\bin
加入到Path
%OPENSSL_HOME%

5. 在 bi-connector 的bin 目录下执行 以下命令

1.创建证书
openssl req -nodes -newkey rsa:2048 -keyout mongodb-bi.key -out mongodb-bi.crt -x509 -days 365 -subj "/C=CN/ST=test/L=test/O=test Security/OU=IT Department/CN=jinggon.com"

2. 生成pem
copy .\mongodb-bi.key + .\mongodb-bi.crt mongodb-bi.pem

3. 启动 mongosqld 服务
mongosqld.exe --addr 192.168.101.234 --mongo-uri "mongodb://xxxx:27017/?connect=direct" --auth --mongo-username jgzn --mongo-password jg123456 --sslMode allowSSL --sslPEMKeyFile .\mongodb-bi.pem --mongo-authenticationSource admin
mongosqld.exe --addr 172.16.100.38 --mongo-uri "mongodb://xxxx:27017/?connect=direct" --auth --mongo-username [数据库username] --mongo-password [数据库password] --sslMode allowSSL --sslPEMKeyFile .\mongodb-bi.pem --mongo-authenticationSource admin

参数数释：
--addr 192.168.101.234 对外暴露ip
--mongo-uri ：MongoDB数据库地址
--mongo-username：MongoDB用户名
--mongo-password：MongoDB密码
--mongo-authenticationSource：身份验证数据库名称
--sslPEMKeyFile：ssl私钥绝对路径

目录应该是这样的

启动效果

5.1 也可以使用配置文件启动，示例如下, 带配置文件启动命令为 mongosqld.exe --config [mongosqld-config.conf文件路径]

# mongosqld-config.conf
net:
  bindIp: "192.168.101.234" # To bind to multiple IP addresses, enter a list of comma separated values.
  port: 3308
  # unixDomainSocket:
  #   enabled: false
  #   pathPrefix: "/var"
  #   filePermissions: "0600"
  ssl:
    mode: "requireSSL"
    # allowInvalidCertificates: false
    PEMKeyFile: 'C:\Program Files\MongoDB\Connector for BI\2\bin\mongodb-bi.pem' #这里要写成绝对路径，负责服务启动的时候会连不上
    # CAFile: 'C:\opt\certs\mdbca.crt'
    # PEMKeyPassword: <string>
    # CAFile: <string>
    # minimumTLSVersion: TLS1_1


mongodb:
  # versionCompatibility: <string>
  net:
    uri: "mongodb://xxxxx: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: "xxx"
      password: "xxx"
      source: "admin" # 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: "admin"
  # 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: 'C:\Program Files\MongoDB\Connector for BI\2\logs\log.txt' # 服务启动的时候这个要配置
  quiet: false
  ## 0|1|2 - Verbosity of the log output, this is overridden if `quiet` is true.
  verbosity: 1
  # logAppend: false
  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: <string>
  # maxVarcharLength: <integer>
  ## 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: "auto" # "auto"|"custom"
    source: "mongosqld_data" # the database where schemas are stored in stored-schema modes
    name: "admin" # 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: ["*.*"]
    # 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

5.3 注册为windows 服务方式可参考
5.3.2 服务方式启动需要配置日志路径
5.3.3 服务方式启动需要配置pem文件为绝对路径

"C:\Program Files\MongoDB\Connector for BI\2.4\bin\mongosqld.exe" install --config "<pathToConfigFile>\mongosqld.conf"

net start mongosql

https://www.mongodb.com/docs/bi-connector/current/launch/#std-label-msqld-as-a-system-service

6. 在报表工具中新建 数据源
   6.1 数据库类型选择 mysql;
   6.2 填写有数据库权限的账号信息；
   6.3 连接串后要跟?useSsl=true示例: jdbc:mysql://172.16.100.38:3307?useSsl=true

效果