Docs Home → MongoDB Connector for BI
Note
The MongoDB Connector for BI and associated utilities are compatible with all currently supported MongoDB server versions.
The MongoDB Connector for BI can be installed on premises or hosted in MongoDB Atlas. Follow the guide for your platform below to install the BI Connector, or see Enable BI Connector in Atlas.
To update your BI Connector, use the installation guides above to install the latest version of BI Connector for your platform, replacing your existing installation.
In this post, we will learn to install the MongoDB BI connector in Ubuntu.
Prerequisites
- MongoDB installed and Running
- User must have role read/write if auth is enabled in MongoDB
- Install MySQL ODBC Driver. [It Requires]
Download MongoDb BI Connector from following Link
//www.mongodb.com/download-center/bi-connector
Choose the version of you OS
Extract the recently downloaded file
Command:
tar -xvzf mongodb-bi-linux_.tgz
Go to the recently extracted directory
Command:
cd mongodb-bi-linux_*
To install this connector use following script:
sudo install -m755 bin/mongo* /usr/local/bin/
Copy Data from MongoDb
mongodrdl --host mongo.myhost.com -d mydatabase -o /opdirectory/schema-.drdl
Start mongosqld for testing
mongosqld --schema /opdirectory/schema.drdl --mongo-uri mongo.myhost.com
Install MySQL Client to work smoothly.
sudo apt install mysql-client-core-5.7
Start mongosqld for Prod with configuration
- Save config file in
/etc/mongosqld.conf
- Config file sample
systemLog:
quiet: false
logAppend: false
path: "/var/log/mongosqld/mongosqld.log"
verbosity: 1
logRotate: "rename" # "rename"|"reopen"
security:
enabled: true
# datasource: "datasource_name"
mongodb:
net:
uri: "mongo.myhost.com:27017"
auth:
username: "mongodb_username"
password: "mongodb_password"
net:
bindIp: 0.0.0.0
port: 3307
# ssl:
# mode: "allowSSL"
# PEMKeyFile: "/vagrant/certificates/mongosqld-server.pem"
# CAFile: "/vagrant/certificates/ca.crt"
schema:
path: "/etc/mongosqld/schema/"
processManagement:
service:
name: mongosqld
displayName: mongosqld
description: "BI Connector SQL proxy server"
Install mongosqld as a service with the above configuration
sudo mongosqld install --config /etc/mongosqld.conf
Note: You may not be able to install or start the service if the directory for
log is not created already. We have to create it manually. Like: sudo mkdir logdir
Enable mongosqld service and your service will auto start when the system reboot
sudo systemctl enable mongosqld.service
Start mongosqld service if you want
sudo systemctl start mongosqld.service
Get Started with MongoDB BI Connector in 4 Steps
By Zhang Youdong [Linqing]
MongoDB uses BI Connector to enable BI components for directly accessing MongoDB using SQL or ODBC data sources. At first, MongoDB directly used PostgreSQL Foreign Data Wrapper [FDW] to convert SQL statements to MQL. Later, the lighter-weighted mongosqld was implemented to support connections with BI tools.
Install BI Connector
Refer to this link to install BI Connector.
wget //info-mongodb-com.s3.amazonaws.com/mongodb-bi/v2/mongodb-bi-linux-x86_64-rhel70-v2.12.0.tgz$tar xvf mongodb-bi-linux-x86_64-rhel70-v2.12.0.tgz
mongodb-bi-linux-x86_64-rhel70-v2.12.0/LICENSE
mongodb-bi-linux-x86_64-rhel70-v2.12.0/README
mongodb-bi-linux-x86_64-rhel70-v2.12.0/THIRD-PARTY-NOTICES
mongodb-bi-linux-x86_64-rhel70-v2.12.0/example-mongosqld-config.yml
mongodb-bi-linux-x86_64-rhel70-v2.12.0/bin/mongosqld
mongodb-bi-linux-x86_64-rhel70-v2.12.0/bin/mongodrdl
mongodb-bi-linux-x86_64-rhel70-v2.12.0/bin/mongotranslate
- The mongosqld tool receives SQL query requests and forwards the requests to MongoDB Server, which is the core of BI Connector.
- The mongodrdl tool generates database schema information that is used for BI SQL queries.
- The mongotranslate tool converts SQL queries to the MongoDB Aggregation Pipeline format.
Start mongosqld
Refer to this link to launch BI Connector
mongodb-bi-linux-x86_64-rhel70-v2.12.0/bin/mongosqld --addr 127.0.0.1:3307 --mongo-uri 127.0.0.1:9555
- addr: specifies the address of the mongosqld listener.
- mongo-uri: specifies the address of the connected MongoDB Server.
By default, mongosqld automatically analyzes the schema of the data in the destination MongoDB Server and caches it to the memory. Specify the schema mapping during startup. Use the mongodrdl tool to generate a schema, specify a collection, and export the schema information for the fields in the collection.
$./bin/mongodrdl --uri=mongodb://127.0.0.1:9555/test -c coll01
schema:
- db: test
tables:
- table: coll01
collection: coll01
pipeline: []
columns:
- Name: _id
MongoType: float64
SqlName: _id
SqlType: float
- Name: qty
MongoType: float64
SqlName: qty
SqlType: float
- Name: type
MongoType: string
SqlName: type
SqlType: varchar
Use a MySQL Client to Connect to mongosqld
The mongosqld tool is directly accessed from a MySQL client. Connect to the tool by using a BI tool such as Excel, Access, or Tableau. Refer to this page for more information.
mysql --protocol=tcp --port=3307mysql> use test
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| coll |
| coll01 |
| coll02 |
| inventory |
| myCollection |
| yourCollection |
+----------------+
6 rows in set [0.00 sec]mysql> select * from coll01;
+------+------+--------+
| _id | qty | type |
+------+------+--------+
| 1 | 5 | apple |
| 2 | 10 | orange |
| 3 | 15 | banana |
+------+------+--------+
3 rows in set [0.00 sec]// 对照 MongoDB 数据库里的原始数据mongo --port
mymongo:PRIMARY> use test
switched to db test
mymongo:PRIMARY> show tables;
coll
coll01
coll02
inventory
myCollection
yourCollection
mymongo:PRIMARY> db.coll01.find[]
{ "_id" : 1, "type" : "apple", "qty" : 5 }
{ "_id" : 2, "type" : "orange", "qty" : 10 }
{ "_id" : 3, "type" : "banana", "qty" : 15 }
Convert SQL to Aggregation
For
example, to convert an SQL query for test.coll01
to the MongoDB Aggregation Pipeline format, analyze the schema by using mongodrdl and then use the mongotranslate tool to convert the schema.
// 导出分析的 shema 文件
$./bin/mongodrdl --uri=mongodb://127.0.0.1:9555/test -c coll01 > coll01.schema // SQL 转换为 Aggregation
$./bin/mongotranslate --query "select * from test.coll01" --schema coll01.schema
[
{"$project": {"test_DOT_coll01_DOT__id": "$_id","test_DOT_coll01_DOT_qty": "$qty","test_DOT_coll01_DOT_type": "$type","_id": NumberInt["0"]}},
]