MongoDB --- 常用工具集_mongodb客户端工具-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_42321034/article/details/127001255

本文介绍了MongoDB的BI Connector工具，包括mongosqld、mongodrdl和mongotranslate的使用。mongosqld允许通过SQL查询MongoDB，而mongodrdl用于生成数据库schema，mongotranslate则将SQL转换为MongoDB的聚合查询。内容涵盖了启动配置、查询操作以及转换示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

简述

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操作即可

注意事项

不支持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_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")}},
    ])

查询结果

在这里插入图片描述

MongoDB Database Tools

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

文件名称	作用
mongostat	数据库性能监控工具
mongotop	热点表监控工具
mongodump	数据库逻辑备份工具
mongorestore	数据库逻辑恢复工具
mongoexport	数据导出工具
mongoimport	数据导入工具
bsondump	BSON格式转换工具
mongofiles	GridFS文件工具