MongoDB BI Connector - a more elaborative guide

-
It's been a while since I last wrote about MongoDB BI Connector but I felt it's still quite important as most people still feel more comfortable getting their information from using conventional tabular format. 

Let's get started!

0. Where to Download
Simple choose the platform you would like to host your MongoDB BI Connector process

1. How to install 

A. on windows
Simply download the .msi, keep everything as default, just click next when prompted, and hit Finish to close the window when installation is completed.


B. on linux, will try to point out the difference in the distribution if I encounter any
Here I am just using Redhat 7.x as an example


scp this onto my Redhat Linux Server, I have installed and configured mingw, therefore, I am able to run scp on my windows box, one of the alternatives would be to use winscp

scp mongodb-bi-linux-x86_64-rhel70-v2.13.1.tgz <user>@<target ip>:/tmp

Log onto the server

Untar the tgz file
[default@msdlvd-dsnavl02 tmp]$ tar -xzvf mongodb-bi-linux-x86_64-rhel70-v2.13.1.tgz

mongodb-bi-linux-x86_64-rhel70-v2.13.1/LICENSE
mongodb-bi-linux-x86_64-rhel70-v2.13.1/README
mongodb-bi-linux-x86_64-rhel70-v2.13.1/THIRD-PARTY-NOTICES
mongodb-bi-linux-x86_64-rhel70-v2.13.1/example-mongosqld-config.yml
mongodb-bi-linux-x86_64-rhel70-v2.13.1/bin/mongosqld
mongodb-bi-linux-x86_64-rhel70-v2.13.1/bin/mongodrdl
mongodb-bi-linux-x86_64-rhel70-v2.13.1/bin/mongotranslate

move the content of the bin onto the path for the user we are plan to initiate the process with

[default@msdlvd-dsnavl02 tmp]$ mv mongodb-bi-linux-x86_64-rhel70-v2.13.1/bin/* /export/local/default/bin

Adding the path into your ~/.bash_profile and source ~/.bash_profile, then verify

[default@msdlvd-dsnavl02 bin]$ mongosqld --version
mongosqld version: v2.13.1
git version: bae9ae2a9bff80642215b648d46312804fe62f2d

2. How to start a mongod (mongodb instance process)
prerequisite:
make sure /data/db is owned by the user you would like to start your mongod process with

[default@msdlvd-dsnavl02 ~]$ mongod --logpath /data/db --fork
about to fork child process, waiting until server is ready for connections.
forked process: 11976
child process started successfully, parent exiting

create a user in our mongod instance, note: we didn't specify a database, the user is created in the default db, test.

[default@msdlvd-dsnavl02 ~]$ mongo
MongoDB shell version v4.2.2
connecting to: mongodb://127.0.0.1:27017/?compressors=disabled&gssapiServiceName=mongodb
Implicit session: session { "id" : UUID("972178bd-38a5-4c14-98bd-0e312fc6537a") }
MongoDB server version: 4.2.2
Server has startup warnings: 
2019-12-11T12:34:30.847-0500 I  CONTROL  [initandlisten] 
2019-12-11T12:34:30.847-0500 I  CONTROL  [initandlisten] ** WARNING: Access control is not enabled for the database.
2019-12-11T12:34:30.847-0500 I  CONTROL  [initandlisten] **          Read and write access to data and configuration is unrestricted.
2019-12-11T12:34:30.847-0500 I  CONTROL  [initandlisten] 
2019-12-11T12:34:30.847-0500 I  CONTROL  [initandlisten] ** WARNING: This server is bound to localhost.
2019-12-11T12:34:30.847-0500 I  CONTROL  [initandlisten] **          Remote systems will be unable to connect to this server. 
2019-12-11T12:34:30.847-0500 I  CONTROL  [initandlisten] **          Start the server with --bind_ip <address> to specify which IP 
2019-12-11T12:34:30.847-0500 I  CONTROL  [initandlisten] **          addresses it should serve responses from, or with --bind_ip_all to
2019-12-11T12:34:30.847-0500 I  CONTROL  [initandlisten] **          bind to all interfaces. If this behavior is desired, start the
2019-12-11T12:34:30.847-0500 I  CONTROL  [initandlisten] **          server with --bind_ip 127.0.0.1 to disable this warning.
2019-12-11T12:34:30.847-0500 I  CONTROL  [initandlisten] 
MongoDB Enterprise > db.createUser(
...    {
...      user: "joe",
...      pwd: "pass",
...      roles: [
...        {role: "root", db: "admin"} ]
...    }
... )
Successfully added user: {
        "user" : "joe",
        "roles" : [
                {
                        "role" : "root",
                        "db" : "admin"
                }
        ]
}

Exit mongo shell by ctrl + c

relaunch our mongod instance with --auth to enable authentication 

MongoDB Enterprise > ^C
bye
# kill mongod, you can do ps -ef | grep mongod, then kill the pid 
[default@msdlvd-dsnavl02 ~]$ pkill -f mongod
# verify process been killed, can do ps -ef | grep mongod as well
[default@msdlvd-dsnavl02 ~]$ pgrep -lfa mongod
[default@msdlvd-dsnavl02 ~]$ mongod --logpath /data/db/mongod.log --auth --fork
about to fork child process, waiting until server is ready for connections.
forked process: 12668
child process started successfully, parent exiting

Let's import some data so we can be working with some sample data, it's pretty easy to search up open data that we can play with, I am using this data set  in this post.

[root@msdlvd-dsnavl02 tmp]# mongoimport -u joe -p pass --authenticationDatabase=test --file countries.json --db=countries --collection language
2019-12-11T16:04:40.967-0500    connected to: mongodb://localhost/
2019-12-11T16:04:41.414-0500    21640 document(s) imported successfully. 0 document(s) failed to import.

Let's query some record to see what we just imported

MongoDB Enterprise > use countries
switched to db countries
MongoDB Enterprise > db.language.findOne()
{
        "_id" : ObjectId("55a0f1d420a4d760b5fbdbd6"),
        "Country Name" : "Afrika",
        "Language" : "af",
        "ISO" : 0
}
MongoDB Enterprise > db.language.find({_id:ObjectId("55a0f1d420a4d760b5fbdbd6")})
{ "_id" : ObjectId("55a0f1d420a4d760b5fbdbd6"), "Country Name" : "Afrika", "Language" : "af", "ISO" : 0 }
MongoDB Enterprise > db.language.find({_id:ObjectId("55a0f1d420a4d760b5fc3043")})
{ "_id" : ObjectId("55a0f1d420a4d760b5fc3043"), "Country Name" : "अपरिचित वा अवैध क्षेत्र", "Language" : "ne", "ISO" : "ZZ" }


3. How to start a MongoDB BI Connector 
I am going to show the basic way to configure mongosqld (the backbone process of MongoDB BI Connector) to connect to data source that has authentication enabled (this is important as this adds some complexity to the configuration), also I would like to show two different ways of launch the same process, one is via yaml, the other is straight through command line by specifying the options needed/desired to use

Prerequisite
This has to be done in order to launch mongosqld to connect to mongod with authentication enabled

Create pem file

 
[root@msdlvd-dsnavl02 ~]# openssl req -nodes -newkey rsa:2048 -keyout test.key -out test.crt -x509 -days 365 -subj "/C=US/ST=NY/L=NY/O=TEST IT/OU=DEVOPS/CN=TEST"

[root@msdlvd-dsnavl02 ~]# cat test.crt test.key > test.pem
#make sure default owns the pem file that will be used by the mongosqld process which we will initiate with user default
[root@msdlvd-dsnavl02 ~]# chown default. test.pem

1. Start mongosqld using CLI
[root@msdlvd-dsnavl02 ~]# sudo -u default mongosqld --addr=<ip where your mongosqld sits on>:3307 --mongo-uri mongodb://127.0.0.1 --mongo-username=joe --mongo-password=pass --mongo-authenticationSource=test --auth --logPath=sql.log --sslMode=allowSSL --sslPEMKeyFile=test.pem --sslAllowInvalidCertificates &


 2. Start mongosqld using config file

 First we need to create the config file

 
[root@msdlvd-dsnavl02 ~]# vi /tmp/mongosqld.conf
##### sample config using the same parameter as the CLI way #####
mongodb:
  net:
    uri: "127.0.0.1:27017"
 auth:
   username: "joe"
      password: "pass"
   source: "test"
security:
  enabled: true   
systemLog:
  path: "sqld.log"
net:
  bindIp: <ip where your mongosqld sits on>
  port: 3307
  ssl:
    mode: "allowSSL"
 PEMKeyFile: "test.pem"
 allowInvalidCertificates: true
 
[root@msdlvd-dsnavl02 ~]# chown default. /tmp/mongosqld.conf

Start mongosqld with config

[root@msdlvd-dsnavl02 ~]# sudo -u default mongosqld --config mongosqld.conf


Let's verify if everything works properly 

Using windows, let's download MySQL Workbench here

Installation is extremely straight forward, set everything as default and click next all the way through.

Once the installation is completed, start it up and we should see the message of Welcome to MySQL Workbench.

Click on +





put in the information as shown, for hostname, use the ip of the host where your mongosqld sits on



Click on Advanced tab, make sure to check the option "Enable Cleartext Authentication Plugin"


Hit "Test Connection"


Connected and test it out by querying



Comments

Post a Comment

Popular posts from this blog

MongoDB tip: 4 ways to modify replica set configuration

-
This is just a quick guide on how to modify your replica set settings in 4 different ways. This guide is assuming you have a functional replica set ready, it doesn't matter if it's a single node or multi-node replica set. In each of the four method, I am looking to modify the priority of the _id=0 node Method 1: Via Mongo Shell Login to your Mongo shell,  simply type in mongo , assuming default port is being used and authentication is not enabled [in Mongo shell] cfg = rs.conf() cfg.members[0].priority=99 rs.reconfig(cfg) # "ok":1 indicates the new configuration has been set  MongoDB Enterprise minvera:PRIMARY> rs.reconfig(cfg) {         "ok" : 1,         "operationTime" : Timestamp(1528464949, 1),         "$clusterTime" : {                 "clusterTime" : Timestamp(1528464949, 1),                 "signature" : {                         "hash" : BinData(0
Read more

MongoDB Quick Note: BI Connector Issue

-
This is just a quick note of what can stop mongosqld from working properly that I wasn't aware of. Background: My work runs everything mongodb inside of docker containers, so sometimes it's not as straight forward to see immediately of what's preventing your container from working properly What happened: I ran into issue that my container does not stay up after I updated my drdl to reflect new fields introduced to the underlying collection. How to debug: Go to the host server, copy out entrypoint.sh and add sleep 100000000 to before mongosqld is executed. This force the container to stay up for us to look into issue. Cause of the issue: When I updated the drdl, I used tab instead of space, which is causing the mongsqld to error out Fix: Just replace tab with space and everything is back to normal
Read more

MongoDB Tips: Kill long running processes

-
This is just a quick and simple script to find all active opid that has been running over 10 seconds and they are running against the databases and collections you can specify in the "ns" filter. It's best to first run db.currentOp(    {      "active" : true,      "secs_running" : { "$gt" : 10 },      "ns" : {$in:["equipment.detachment","personnel.detail","personnel.unavailability","personnel.detachment"]}    } ).inprog.forEach(function (doc) { print(doc.opid); }) and verify your output before you execute the kill command in my experience to make sure you are killing the processes that are meant to be killed. Once verifed, simply run: db.currentOp(    {      "active" : true,      "secs_running" : { "$gt" : 10 },      "ns" : {$in:["<db1>.<col1>","<db2>.<col2>","<dbn>.<coln>
Read more