需要使用的组件 MongoDB BI Connector

简述

MongoDB 使用 BI Connector 来支持 BI 组件直接使用 SQL 或 ODBC 数据源方式直接访问 MongoDB，在早期 MongoDB 直接使用 Postgresql FDW 来实现 SQL 到 MQL 的转换，后来实现更加轻量级的 mongosqld 来支持 BI 工具的连接。

下载地址

https://www.mongodb.com/try/download/bi-connector

目录结构

• mongodrdl ：工具生成数据库 schema 信息，用于服务 BI SQL 查询
• mongosqld ：接受 SQL 查询，并将请求发到 MongoDB Server，是 BI Connector 的核心
• mongotranslate ：工具将 SQL 查询转换为 MongoDB Aggregation Pipeline

使用步骤

使用Mybatis查询MongoDB

启动 mongosqld

方式一：命令行启动

# –-addr 指定 mongosqld 监听的地址
# –-mongo-uri 指定连接的 MongoDB Server 地址
mongosqld --addr 127.0.0.1:3307 --mongo-uri 127.0.0.1:9555

默认情况下，mongosqld 自动会分析目标 MongoDB Server 里数据的 Schema，并缓存在内存，我们也可以直接在启动时指定 schema 影射关系。schema 也可以直接 mongodrdl 工具来生成，指定集合，可以将集合里的字段 shema 信息导出。

方式二，使用配置文件启动

修改配置文件

## 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: "disabled"
    # allowInvalidCertificates: false
    # PEMKeyFile: <string>
    # PEMKeyPassword: <string>
    # CAFile: <string>
    # minimumTLSVersion: TLS1_1

## 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://localhost: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: <string>
    #   password: <string>
    #   source: <string> # 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: <boolean>
#   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: <string>
  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: "mySchema" # 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

执行启动命令

./mongosqld --config mongsqld-config.yml

mybatis 按照常规SQL操作即可

spring:
  application:
    name: test
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://172.18.106.53:3307/my_test_database?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC

mongoDB 实体类

package net.jlxxw.temp.domain;

import java.util.List;

/**
 * @author chunyang.leng
 * @date 2022-03-17 5:16 PM
 */
public class TestMongo {

    private String _id;

    private Integer age;

    private String name;

    private List<String> remark;

    public String get_id() {
        return _id;
    }

    public void set_id(String _id) {
        this._id = _id;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public List<String> getRemark() {
        return remark;
    }

    public void setRemark(List<String> remark) {
        this.remark = remark;
    }
}

Mapper文件

package net.jlxxw.temp.mapper;

import net.jlxxw.temp.domain.IpInfoDO;
import net.jlxxw.temp.domain.TestMongo;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author chunyang.leng
 * @date 2021-12-06 10:24 上午
 */
@Mapper
public interface InfoMapper {



    @Select("select _id as _id ,age as age,name as name from my_test_database.my_test_colection group by name having age > 10")
    List<TestMongo> findAllBy();

}

注意事项

• 不支持insert
• Mysql driver version版本 ：5.1.49

使用mongodrdl转储schma

# mongodb://服务器ip:服务器端口/数据库名称 > 转储到某文件
./mongodrdl --uri=mongodb://172.17.0.2:27017/my_test_database > my_test_database.schema

schema文件内容示例

schema:
- db: my_test_database
  tables:
  - table: my_test_colection
    collection: my_test_colection
    pipeline: []
    columns:
    - Name: _id
      MongoType: bson.ObjectId
      SqlName: _id
      SqlType: objectid
    - Name: age
      MongoType: int
      SqlName: age
      SqlType: int
    - Name: name
      MongoType: string
      SqlName: name
      SqlType: varchar
  - table: my_test_colection_remark
    collection: my_test_colection
    pipeline:
    - $unwind:
        includeArrayIndex: remark_idx
        path: $remark
        preserveNullAndEmptyArrays: false
    columns:
    - Name: _id
      MongoType: bson.ObjectId
      SqlName: _id
      SqlType: objectid
    - Name: remark
      MongoType: string
      SqlName: remark
      SqlType: varchar
    - Name: remark_idx
      MongoType: int
      SqlName: remark_idx
      SqlType: int

使用mongotranslate，将SQL转译成Aggregation

./mongotranslate --query "select * from my_test_database.my_test_colection group by name" --schema /Users/lcy/Desktop/my_test_database.schema

转换示例

mongoDB查询

db.my_test_colection.aggregate(
  [
    {"$group": {"_id": "$name","my_test_database_DOT_my_test_colection_DOT__id": {"$first": "$_id"},"my_test_database_DOT_my_test_colection_DOT_age": {"$first": "$age"}}},
    {"$addFields": {"_id": {"group_key_0": "$_id"}}},
    {"$project": {"my_test_database_DOT_my_test_colection_DOT__id": "$my_test_database_DOT_my_test_colection_DOT__id","my_test_database_DOT_my_test_colection_DOT_age": "$my_test_database_DOT_my_test_colection_DOT_age","my_test_database_DOT_my_test_colection_DOT_name": "$_id.group_key_0","_id": NumberInt("0")}},
    ])

查询结果