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

Oracle Goldengate Extract, Pump, and Replicat

-
This is just a quick demonstration of setting up extract, pump, and replicate. I prefer to edit the prm files at OS level, but we can also do GGSCI> edit param esource to enter the vi mode to create the prm file. Here are the sample prm files used in the demonstration: prm files: -------------------------------- Source: (2 processes) ~~~~~~~~~~~~~~~~ esource.prm ~~~~~~~~~ EXTRACT ESOURCE SETENV (ORACLE_SID=source) SETENV (ORACLE_HOME="/source/app/oracle/product/11.2.0.3") userid ggg, password ggg EXTTRAIL /ggSource/app/goldengate/product/12.1.0/dirdat/es --Resolves the TABLES to be replicated when they first encounter a dml operation WILDCARDRESOLVE DYNAMIC --Reduce the system I/O overhead of redolog reads EOFDELAY 5 TABLE mine.table; psource.prm ~~~~~~~~~ EXTRACT PDWDSPRD SETENV (ORACLE_SID=source) SETENV (ORACLE_HOME="/source/app/oracle/product/11.2.0.3") userid ggg, password ggg RMTHOST targetServer.example.net, MGRPO...
Read more

MongoDB Ops Manager Basic Installation and Configuration

-
Image
This post is just a quick guide of MongoDB Ops Manager basic installation and configuration. The bare minimum of getting an instance of MongoDB Ops Manager up and running. We will be working with the latest version of the OpsManager, release version 3.4.7. Prerequisite: To start fresh, we remove any mongodb related RPM. I had Mongod installed as well, therefore, I have extras to be removed. [root@msdlva-dsnops01 ~]# yum autoremove $(rpm -qva | grep mongo) Loaded plugins: langpacks, product-id, rhnplugin, search-disabled-repos, subscription-manager This system is receiving updates from RHN Classic or Red Hat Satellite. Resolving Dependencies --> Running transaction check ---> Package mongodb-enterprise.x86_64 0:3.4.6-1.el7 will be erased ---> Package mongodb-enterprise-mongos.x86_64 0:3.4.6-1.el7 will be erased ---> Package mongodb-enterprise-server.x86_64 0:3.4.6-1.el7 will be erased ---> Package mongodb-enterprise-shell.x86_64 0:3.4.6-1.el7 will be erased...
Read more