Get Started with MongoDB BI Connector in 4 Steps

Install BI Connector

wget https://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

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.
$./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

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

// 导出分析的 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")}},
]

Original Source:

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Alibaba Cloud

Alibaba Cloud

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com