Using the MongoDB Connector for Business Intelligence, it is possible to interact with MongoDB using the MySQL Shell.
The BI Connector is a SQL Connector for MongoDB. It translates SQL queries into aggregation pipelines and then returns data as SQL query output. This makes it possible to use applications like Excel, Tableau, and PowerBI as consumers of data in MongoDB.
But there are times when it is more convenient to quickly log into the MySQL shell to query data in MongoDB.
To handle authentication, you need to use the MongoDB auth plugin. This MySQL plugin enables authentication credentials to be passed through the BI Connector to the underlying MongoDB cluster.
So, to connect to MongoDB using the MySQL shell, do the following:
- Download and install the BI Connector.
- Download the auth plugin. You can put this plugin anywhere you like. In most cases, it is best to place the file in the existing MySQL plugin directory.
Connect by issuing:
mysql -h <mongod host> --port 27015 -u <username> -p --default-auth=mongosql_auth
After entering you MongoDB password, you should see this:
Server version: 5.7.12 mongosqld v2.9.0 mongosqld v2.9.0 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>
Select a database (MongoDB collection) and take a look at the tables. What you will find is a parent table, representing the “top-level” fields in a MongoDB document, and several sub-tables. For example:
>+---------------------------+ | Tables_in_demo | +---------------------------+ | aa | | aa_conversions | | aa_defensive_players | | aa_drives | | aa_fgxp | | aa_fumbles | | aa_interceptions | | aa_kickers | | aa_kickoffs | | aa_offensive_players | | aa_passes | | aa_pbp | | aa_player_def_summary | | aa_player_off_summary | | aa_plays | | aa_punts | | aa_redzone_player_summary | | aa_rushes | | aa_sacks | | aa_safeties | | aa_tackles | | aa_teams | | aa_touchdowns | +---------------------------+ 23 rows in set (0.01 sec)
If you would like to manage your own custom SQL schema representative of underlying documents, you can do so using the mongodrdl tool shipped with the BI Connector.