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 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

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=3307

mysql> 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")}},
]

Community

Get Started with MongoDB BI Connector in 4 Steps

Install BI Connector

Start mongosqld

Use a MySQL Client to Connect to mongosqld

Convert SQL to Aggregation

Read previous post:

Read next post:

ApsaraDB

You may also like

Comments

ApsaraDB

Related Products

ApsaraDB for MongoDB

Big Data Consulting for Data Technology Solution

Big Data Consulting Services for Retail Solution

ApsaraDB for HBase