Make big data analysis easier, 4 steps to teach you to play with mongodb Bi connector

Time:2020-10-25

Mongodb uses Bi connector to support Bi components to access mongodb directly using SQL or ODBC data source. In the early days, mongodb directly used PostgreSQL FDW to realize the conversion from SQL to MQL. Later, mongosqld, which is more lightweight, supports the connection of Bi tools.

Make big data analysis easier, 4 steps to teach you to play with mongodb Bi connector

Install Bi connector

ReferenceInstall BI Connector
https://docs.mongodb.com/bi-connector/master/installation/

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
  • Mongosqld accepts SQL queries and sends requests to mongodb server, which is the core of Bi connector
  • The mongodrddl tool generates database schema information to serve Bi SQL queries
  • The mongotranslate tool converts SQL queries into mongodb aggregation pipeline

Start Mongo sqld

Refer to lauch Bi connector
https://docs.mongodb.com/bi-connector/current/launch/

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 mongosqld listens to
–Mongo URI specifies the mongodb server address of the connection
By default, mongosqld will automatically analyze the schema of the data in the target mongodb server and cache it in memory. We can also specify the schema mapping relationship directly at startup. Schema can also be generated directly by mongodrddl tool to specify the collection, and the field schema information in the collection can be exported.

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

Using MySQL client to connect mongosqld

Mongosqld can directly support MySQL client access, and can also be connected through Bi tools such as Excel, access, tableau, etc
https://docs.mongodb.com/bi-connector/current/client-applications/

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)

//Compare the original data in mongodb database

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 }

SQL to aggregation

For example, we should focus on test.coll01 To convert SQL query of to mongodb aggregation pipeline, you need to analyze the schema through mongodrddl, and then use mongotranslate tool to convert

//Export Shema file for analysis
$./bin/mongodrdl --uri=mongodb://127.0.0.1:9555/test -c coll01 > coll01.schema  

//SQL to 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")}},
]

Double 12! 500 yuan Taobao red envelope, iPhone 11, etc https://www.aliyun.com/1212/2019/home?utm_ content=g_ one billion ninety-two thousand six hundred and eleven


Author: roin123

Read the original

This article is the original content of yunqi community, which can not be reproduced without permission.

Recommended Today

MVC and Vue

MVC and Vue This article was written on July 27, 2020 The first question is: is Vue an MVC or an MVVM framework? Wikipedia tells us: MVVM is a variant of PM, and PM is a variant of MVC. So to a certain extent, whether Vue is MVC or MVVM or not, its ideological direction […]