Get Started with MongoDB BI Connector in 4 Steps

Image for post
Image for post

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.

Image for post
Image for post

Install BI Connector

Refer to this link to install BI Connector.

  • 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

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

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.

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.

Original Source:

Written by

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.

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