How to setup MongoDB Connector for BI Components

Here are the high-level steps on installing BI related rpms.

[root@mongobihost raj]# lsb_release -a

Distributor ID: RedHatEnterpriseServer

Description: Red Hat Enterprise Linux Server release 6.5 (Santiago)

Release: 6.5

[root@mongobihost raj]# cat /etc/redhat-release 

Red Hat Enterprise Linux Server release 6.5 (Santiago)

[root@mongobihost raj]# which python

/usr/bin/python

[root@mongobihost raj]# python -V

Python 2.6.6

root@mongobihost bin]# cd /tmp/

[root@mongobihost tmp]# ls -ltr

mongodb-bi-schematools-1.1.3-1.el6.x86_64.rpm

mongodb-bi-libs-1.1.3-1.el6.x86_64.rpm

mongodb-bi-1.1.3-1.el6.x86_64.rpm

mongodb-bi-server-1.1.3-1.el6.x86_64.rpm

mongodb-bi-contrib-1.1.3-1.el6.x86_64.rpm

mongodb-bi-devel-1.1.3-1.el6.x86_64.rpm

mongodb-bi-multicorn-1.1.3-1.el6.x86_64.rpm

mongodb-bi-pymongo-1.1.3-1.x86_64.rpm

mongodb-bi-fdw-1.1.3-1.noarch.rpm

mongodb-bi-1.1.3-1-centos6-rpms.tar.bz2

NOTE: python Version should be greater than 2.6 - Hence, upgrade it and then install RPMs.

One way to check is :  to start a Python2.6 shell, and confirm that the "collections" module includes the "OrderedDict()" methods. For example:

python

Python 2.6.6 (r266:84292, Sep  4 2013, 07:46:00) 

[GCC 4.4.7 20120313 (Red Hat 4.4.7-3)] on linux2

Type "help", "copyright", "credits" or "license" for more information.

>>> import collections

>>> od = collections.OrderedDict()

>>> od

OrderedDict()

Ctrl+D to exit..

[root@mongobihost tmp]# rpm 

[root@mongobihost tmp]# rpm -ivh *.rpm --nodeps

Preparing...                ########################################### [100%]

package mongodb-bi-libs-1.1.3-1.el6.x86_64 is already installed

package mongodb-bi-1.1.3-1.el6.x86_64 is already installed

package mongodb-bi-devel-1.1.3-1.el6.x86_64 is already installed

package mongodb-bi-server-1.1.3-1.el6.x86_64 is already installed

package mongodb-bi-contrib-1.1.3-1.el6.x86_64 is already installed

package mongodb-bi-schematools-1.1.3-1.el6.x86_64 is already installed

package mongodb-bi-pymongo-1.1.3-1.x86_64 is already installed

package mongodb-bi-fdw-1.1.3-1.noarch is already installed

[root@mongobihost tmp]# rpm -ivh mongodb-bi-multicorn-1.1.3-1.el6.x86_64 --nodeps

error: open of mongodb-bi-multicorn-1.1.3-1.el6.x86_64 failed: No such file or directory

[root@mongobihost tmp]# rpm -ivh mongodb-bi-multicorn-1.1.3-1.el6.x86_64.rpm --nodeps

Preparing...                ########################################### [100%]

   1:mongodb-bi-multicorn   ########################################### [100%]

[root@mongobihost tmp]#

#*** log into Mongo 

 mongo ${HOST}:${PORT}/admin -u mongoadmin -p $password

MongoDB shell version: 3.2.4

connecting to: mongobihost:27017/admin

Server has startup warnings: 

2016-04-01T16:49:54.454-0700 I CONTROL  [initandlisten] 

MongoDB Enterprise set01:PRIMARY> show dbs

admin              0.000GB

rajdb              1.210GB

abcdeconfig        0.015GB

abcdb              0.166GB

jiradb             0.026GB

local              1.199GB

exit;

mongobiuser create biuser mongodb://biuser:test@mongobihost.myhost.com:27017/admin

or

[root@mongobihost bin]# mongobiuser create biuser mongodb://mongobihost.myblog.com:27017/admin

Enter password:

2016-06-17T12:12:15.403-0700 creating user biuser

2016-06-17T12:12:15.408-0700 creating database buses

[root@mongobihost bin]# netstat -an|grep PG

Active Internet connections (servers and established)

Proto RefCnt Flags       Type       State         I-Node Path

unix  2      [ ACC ]     STREAM     LISTENING     1262987 /tmp/.s.PGSQL.27032

[root@mongobihost tmp]# find / -name postgresql.conf 

/var/lib/pgsql/9.4/data/postgresql.conf

[root@mongobihost bin]# vi /var/lib/pgsql/9.4/data/pg_hba.conf

#** Add below content :

# IPv4 local connections:

host    all             all             0.0.0.0/0               md5

NOTE: here db is : biuser, password is the one you provide and port is 27032

Test the connectivity :
Note the postgres port is 27032

mongodrdl ??
mongodrdl produces a schema based on contents of one or more mongod collections and writes them out into .drdl files understood by mongobischema.

mongodrdl -d rajdb -o rajdb.drdl -h mongobihost:27017 -u mongoadmin -p $password --authenticationDatabase admin

Note: 27017 is mongo port

2016-06-17T14:20:15.546-0700 Table "employee", column "sfg.sfgsf" has no types: ignoring column.

2016-06-17T14:20:15.546-0700 Table "employee", column "fgfs.gsdfgf" has no types: ignoring column.

2016-06-17T14:20:15.546-0700 Table "employee", column "fgsf.sgfgs" has no types: ignoring column.

2016-06-17T14:20:15.546-0700 Table "employee", column "sgss.srgs" has no types: ignoring column.

2016-06-17T14:20:16.123-0700 Table "emp_Pack_flat", column "rtgs.comments" has no types: ignoring column.

2016-06-17T14:20:16.972-0700 Table "customer_transaction", column "FValues" is an array that has no types: ignoring column.

2016-06-17T14:20:16.973-0700 Table "customer_transaction_Notes", column "Notes.enumValues" is an array that has no types: ignoring column.

2016-06-17T14:20:16.973-0700 Table "customer_transaction_SiteValues", column "F1z_v.fields.SiteAbbr.enumValues" is an array that has no types: ignoring column.

2016-06-17T14:20:16.973-0700 Table "customer_transaction_URL", column "URL.enumValues" is an array that has no types: ignoring column.

2016-06-17T14:20:16.974-0700 Table "customer_transaction_active", column "F1z_v.fields.active.enumValues" is an array that has no types: ignoring column.

2016-06-17T14:20:16.974-0700 Table "customer_transaction_active", column "colCur.enumValues" is an array that has no types: ignoring column.

2016-06-17T14:20:16.974-0700 Table "customer_transaction_active", column "colDiff.enumValues" is an array that has no types: ignoring column.

# To import data into BI schema

[root@mongobihost bin]# mongobischema import biuser ./rajdb.drdl
Enter password:

2016-06-17T14:55:02.541-0700 creating table employee
2016-06-17T14:55:02.572-0700 creating table emp_Pac_fla
2016-06-17T14:55:02.579-0700 creating table customer_transaction
2016-06-17T14:55:02.588-0700 creating table customer_transaction_Notes
2016-06-17T14:55:02.597-0700 creating table customer_transaction_SiteVa
2016-06-17T14:55:02.606-0700 creating table customer_transaction_URL
2016-06-17T14:55:02.614-0700 creating table customer_transaction_active

# to look at the tables in the BI schema, run below stmt.

[root@mongobihost]# mongobischema list biuser

Enter password:

employee

customer_transaction

customer_transaction_Notes

customer_transaction_SiteVa

customer_transaction_URL 

customer_transaction_active

If you need to restart the BI Connector, then
sudo service postgresql-9.4 stop
sudo service postgresql-9.4 start

mongobiuser list

to check if things are okay on postgre Sql..

 psql -h localhost -p 27032 -U biuser

Password for user biuser: 

psql (9.4.5 MongoDB BI Connector 1.1.3)

SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)

Type "help" for help.

biuser=> \d

                                                List of relations

 Schema |                                     Name                                      |     Type      | Owner  

--------+-------------------------------------------------------------------------------+---------------+--------

 public | customer_transaction                                                            | view          | biuser

 public | customer_transaction_Notes                                                        | foreign table | biuser

 public | customer_transaction_SiteVa                                               | view          | biuser

biuser=> select * from "customer_transaction" limit 1;

Form more info, please visit https://docs.mongodb.com/bi-connector/schema-configuration/