简述

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-optionsnet: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: # PEMKeyPassword: # CAFile: # 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-optionsmongodb:# versionCompatibility: net:uri: "mongodb://localhost:27017" # https://docs.mongodb.com/manual/reference/connection-string/#mongodb-urissl:enabled: false## https://docs.mongodb.com/bi-connector/master/reference/mongosqld/#mongodb-tls-ssl-options# allowInvalidCertificates: false# allowInvalidHostnames: false# PEMKeyFile: # PEMKeyPassword: # CAFile: # CRLFile: # FIPSMode: false# minimumTLSVersion: TLSv1_1# auth:# username: # password: # source:  # 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: # defaultMechanism: "SCRAM-SHA-1"# defaultSource: "admin"# gssapi:# hostname: # serviceName: "mongosql"## Logging options## https://docs.mongodb.com/bi-connector/master/reference/mongosqld/#logging-optionssystemLog:## The path to the file where log output will be written to.## Defaults to stderr.# path: quiet: false## 0|1|2 - Verbosity of the log output, this is overridden if `quiet` is true.verbosity: 1# logAppend: falselogRotate: "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-optionsschema:## If you've generated a DRDL schema file using mongodrdl, you can supply the## path for mongosqld to use that schema DRDL file.# path: # maxVarcharLength: ## 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: 0stored:mode: "auto" # "auto"|"custom"source: "mongosqld_data" # the database where schemas are stored in stored-schema modesname: "mySchema" # the named schema to read/write to in stored-schema modessample:size: 1000 # The amount of random documents we sample from each collection.namespaces: ["*.*"]# prejoin: false# uuidSubtype3Encoding: "old" # ## Process management options## https://docs.mongodb.com/bi-connector/master/reference/mongosqld/#process-management-optionsprocessManagement: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操作即可

注意事项

• 不支持insert
• mysql jdbc driver version :5.1.47

使用mongodrdl转储schma

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

schema文件内容示例

schema:- db: my_test_databasetables:- table: my_test_colectioncollection: my_test_colectionpipeline: []columns:- Name: _idMongoType: bson.ObjectIdSqlName: _idSqlType: objectid- Name: ageMongoType: intSqlName: ageSqlType: int- Name: nameMongoType: stringSqlName: nameSqlType: varchar- table: my_test_colection_remarkcollection: my_test_colectionpipeline:- $unwind:includeArrayIndex: remark_idxpath: $remarkpreserveNullAndEmptyArrays: falsecolumns:- Name: _idMongoType: bson.ObjectIdSqlName: _idSqlType: objectid- Name: remarkMongoType: stringSqlName: remarkSqlType: varchar- Name: remark_idxMongoType: intSqlName: remark_idxSqlType: 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")}},])

查询结果

MongoDB Database Tools

下载地址：https://www.mongodb.com/try/download/database-tools