Running the MongoDB BI Connector Locally

NOTE: I’m assuming you have MongoDB running on your local machine without authentication.

The BI Connector is a simple binary (mongosqld) that can connect to a MongoDB cluster, generate schema, and allow SQL-based clients, such as Tableau and Microsoft Excel to access MongoDB data directly. The BI Connector presents itself as MySQL database. Any client application that can use JDBC, ODBC, or the MySQL wire protocol, can connect to the BI Connector.

BIC can be deployed as a daemon or service but in this example, I want to show you how simple it is to run mongosqld locally. I’ll be using MacOS but the same, or very similar steps, apply to both Linux and Windows.

To keep things organized I suggest creating a new directory called mongosqld. It keeps things simple to have config files, log output, etc. in this one directory.

The first thing is to download BIC from the MongoDB download center. Move the file to your mongosqld directory,expand the archive and navigate to the expanded directory.

The binaries we want to work with are found in the bin directory. The mongosqld binary is the BI Connector. When executed, it will connect to your local mongod and scan your database collection documents to build an equivalent tabular structure. In this example I want the BIC to sample the “ads” collection in the “sports” database.

./mongosqld –sampleNamespaces=”sports.tds”

Your output should look something like this:

2018-11-20T13:41:21.587-0800 I CONTROL    [initandlisten] mongosqld starting: version=v2.8.1 pid=11074 host=Seths-iMac.local
2018-11-20T13:41:21.587-0800 I CONTROL    [initandlisten] git version: d1c0422d9a0f6b79621c9d49a0daf985f527f622
2018-11-20T13:41:21.587-0800 I CONTROL    [initandlisten] OpenSSL version OpenSSL 1.0.2p  14 Aug 2018 (built with OpenSSL 1.0.2p  14 Aug 2018)
2018-11-20T13:41:21.587-0800 I CONTROL    [initandlisten] options: {schema: {sample: {namespaces: [sports.tds]}}}
2018-11-20T13:41:21.587-0800 I CONTROL    [initandlisten] ** WARNING: Access control is not enabled for mongosqld.
2018-11-20T13:41:21.587-0800 I CONTROL    [initandlisten]
2018-11-20T13:41:21.594-0800 I NETWORK    [initandlisten] waiting for connections at 127.0.0.1:3307
2018-11-20T13:41:21.594-0800 I NETWORK    [initandlisten] waiting for connections at /tmp/mysql.sock
2018-11-20T13:41:21.594-0800 I SAMPLER    [schemaDiscovery] initializing schema
2018-11-20T13:41:21.600-0800 I SAMPLER    [schemaDiscovery] sampling MongoDB for schema...
2018-11-20T13:41:22.549-0800 I SAMPLER    [schemaDiscovery] mapped schema for 1 namespace: "sports" (1): ["tds"]

If mongosqld fails due to a socket error, you most likely have a MySQL database running on your local machine. If this is the case, start mongosqld like this:

./bin/mongosqld --noUnixSocket

That’s it. The BI Connector is connected to MongoDB and ready to accept incoming client connections on port 3307.

For example, you can connect using the MySQL shell:

mysql --port 3307

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.12 mongosqld v2.8.1 mongosqld v2.8.1

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| sports             |
+--------------------+
3 rows in set (0.01 sec)

mysql> use sports;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+------------------+
| Tables_in_sports |
+------------------+
| tds              |
+------------------+
1 row in set (0.00 sec)

mysql>