MongoDB-BI安装

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

安装

先决条件

Macos

• OpenSSL已安装

要验证是否安装，运行以下命令

 % brew list | grep openssl
openssl@3

要通过 Homebrew 安装 OpenSSL，请运行以下命令：

 % brew update
 % brew install openssl

Linux

• OpenSSL已安装 要验证系统上是否安装了 OpenSSL，请运行以下命令：

rpm -qa | grep -i openssl

注意：mongosql 依赖的ssl 版本是 ssl1.0。

sudo yum install -y compat-openssl10

Windows

• Visual C++ Redistributable jeecgSslKeyfor Visual Studio2015 已安装

Centos下安装

准备ssl证书

mkdir -p /opt/mongo-bi-connector/crt/
cd /opt/mongo-bi-connector/crt/
openssl req -nodes -newkey rsa:2048 -keyout jeecgSslKey.key -out jeecgSslKey.crt -x509 -days 365 -subj "/C=US/ST=jeecgSslKey/L=jeecgSslKey/O=jeecgSslKey Security/OU=IT Department/CN=kayakwise.com"
cat jeecgSslKey.crt jeecgSslKey.key > jeecgSslKey.pem

下载BI Connector

从下载中心下载与本机版本对应的BI Connector

将下载好的文件上传到服务器/opt/mongo-bi-connector/下mongodb-bi-linux-x86_64-rhel70-<version>.tgz

cd opt/mongo-bi-connector
tar -zxvf mongodb-bi-linux-x86_64-rhel70-<version>.tgz
cd mongodb-bi-linux-x86_64-rhel70-<version>
install -m755 bin/mongo* /usr/bin/

生成schema

mongodrdl --host <host>:<port> --username <username> --password <pass> --db <db> --authenticationDatabase <authDB> --authenticationMechanism SCRAM-SHA-256 --out /opt/mongo-bi-connector/schemas/schemas.drdl

编辑配置文件

mkdir -p /opt/mongo-bi-connector/conf/
mkdir -p /opt/mongo-bi-connector/logs/
mkdir -p /opt/mongo-bi-connector/schemas
cat > /opt/mongo-bi-connector/conf/mongosqld-config.yml  << EOF
net:
  bindIp: "0.0.0.0"
  port: 3307
  ssl:
    mode: allowSSL
    PEMKeyFile: '/opt/mongo-bi-connector/crt/jeecgSslKey.pem'
    allowInvalidCertificates: true
    minimumTLSVersion: TLS1_0
mongodb:
  net:
    uri: "mongodb://<host>:<port>"
    ssl:
      enabled: false
    auth:
      username: <username>
      password: <password>
      source: <database>
      mechanism: SCRAM-SHA-1
security:
  enabled: true
  defaultMechanism: "SCRAM-SHA-1"
  defaultSource: "<database>"
systemLog:
  path:  /opt/mongo-bi-connector/logs/mongosqld.log
  verbosity: 2
  logAppend: true
schema:
  path:  /opt/mongo-bi-connector/schemas
  maxVarcharLength: 65535
processManagement:
  service:
    name: "mongosql"
    displayName: "MongoSQL Service"
    description: "MongoSQL accesses MongoDB data with SQL"
EOF

将mongosql设置成系统服务

mongosqld install --config /opt/mongo-bi-connector/conf/mongosqld-config.yml
#重新加载
systemctl daemon-reload

启动服务

systemctl start mongosql.service

客户端连接

使用mysql客户端连接

mysql --enable-cleartext-plugin --protocol tcp --port 3307 \
  --user='admin' \
  --ssl-ca=/opt/mongo-bi-connector/crt/jeecgSslKey.crt \
  --ssl-key=/opt/mongo-bi-connector/crt/jeecgSslKey.key \
  --ssl-cert=/opt/mongo-bi-connector/crt/jeecgSslKey.crt \
  -p

使用sql语句查询MongoDB数据

使jdbc连接

package test.mongosql;

import com.zaxxer.hikari.HikariDataSource;
import org.jeecg.modules.jmreport.dyndb.util.JmreportSqlUtils;
import org.jeecg.modules.jmreport.dyndb.vo.JmreportDynamicDataSourceVo;
import org.junit.jupiter.api.Test;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;
import java.util.Map;

/**
 * 测试mongodb-bi-connector
 *
 * @author chenrui
 * @date 2024/12/31 18:01
 */
public class TestMongosql {


    @Test
    public void testMethodCodeLarge() throws ClassNotFoundException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        JmreportDynamicDataSourceVo dbSource = new JmreportDynamicDataSourceVo();
        dbSource.setDbUrl("jdbc:mysql://host:port/dbname?useUnicode=true&zeroDateTimeBehavior=convertToNull&characterEncoding=UTF-8&serverTimeZone=Asia/Shanghai&useSSL=true&verifyServerCertificate=false");
        dbSource.setDbDriver("com.mysql.cj.jdbc.Driver");
        dbSource.setDbUsername("username");
        dbSource.setDbPassword("password");
        HikariDataSource hikariDataSource = getHikariDataSource(dbSource);
        JdbcTemplate jdbcTemplate = new JdbcTemplate(hikariDataSource);
        List<Map<String, Object>> maps = jdbcTemplate.queryForList("select * from tablename");
        maps.forEach(System.out::println);

    }


    private HikariDataSource getHikariDataSource(JmreportDynamicDataSourceVo dbSource) {
        HikariDataSource dataSource = new HikariDataSource();
        String driverClassName = dbSource.getDbDriver();
        String url = dbSource.getDbUrl();
        String dbUser = dbSource.getDbUsername();
        String dbPassword = dbSource.getDbPassword();
        dataSource.setDriverClassName(driverClassName);
        url = JmreportSqlUtils.resetJdbcUrlOption(url);
        dataSource.setJdbcUrl(url);
        dataSource.setUsername(dbUser);
        dataSource.setPassword(dbPassword);
        dataSource.setAutoCommit(true);
        dataSource.setMaximumPoolSize(15);
        dataSource.setMinimumIdle(5);
        //设置超时时间 60秒
        dataSource.setConnectionTimeout(60000);
        dataSource.setIdleTimeout(30000);
        dataSource.setMaxLifetime(30000);
        dataSource.setPoolName("HicariCP");
        return dataSource;
    }

}

填坑：

1. 目前【2024-12-31】测试不支持macOS 15+
2. mongosql 依赖的ssl 版本是 ssl1.0,需要检查ssl的版本。

sudo yum install -y compat-openssl10

1. 不要使用admin库，在生成schema时会因为权限不足无法正常扫描到tables
2. 当mongodb-bi的ssl设置了allowSSL时，使用jdbc连接的时候一定要启用SSL(useSSL=true)，并且不验证服务端证书（verifyServerCertificate=false）。

jdbc:mysql://host:port/dbname?useUnicode=true&zeroDateTimeBehavior=convertToNull&characterEncoding=UTF-8&serverTimeZone=Asia/Shanghai&useSSL=true&verifyServerCertificate=false