Interesting Tip: Run query against MongoDB using SQL Server

-
Disclaimer: This is just a quick setup I had to do to expose MongoDB to SQL Server user, this is still a work in progress because not everything works, only non string fields work so far, such as number and date, at least I haven't found a way to get around it. 
Update: So far the major types that we typically query all work for me, such as varchar, int, and datetime.


Components:
SSMS, SQL Server v2008 (It should also work for 2012 or 2016)


I am going to jump directly to the setup on SQL Server as I have done a little bit of bi connector setups in the post1, post2, Please refer to them for setting that up.

First, let's download the mysql-connector-odbc, here I have downloaded the latest version which is available to me, the 5.3.9 version. Then we simply need to install that on the server where we are going to set up our odbc connection.

After the setup is complete, we can go to 

Control Panel -> ODBC Data Sources 

Click onto System DSN tab > Add...




When prompt to create New Data Source, find and select MySQL ODBC 5.3 drive,



Put in a name for Data Source Name, fill in the IP and port which your bi connector resides and uses, user and password on the database if it's authenticated. 
If it's not authenticated, I believe you put the username as root, and leave password blank.

Make sure to check Enable Cleartext Authentication as shown below, and you have successfully completed odbc connection. 



Now, let's create Linked Server on SQL Server, simply right click on Linked Server and Add Linked Server, fill in the blank as shown below and specify your Data Source name with the name you used in System DSN


Make sure to specify your login and password under security page as well.


Test the connection:


Now let's run the query using openquery


As seen in the screenshot below, we can query both date and int field, however, string type is still not working for me at the moment. 



Update: varchar does work for BI Connector v2.2, all we need to do is specify --maxVarcharLength 8000 upon starting BI Connector and the conversion of varchar via linked server is good. 8000 is the limit imposed by MongoDB, and this flag is specifically designed to handle the varchar conversion issue. 

Comments

  1. TejutejuOctober 30, 2018 at 3:00 AM

    It was really a nice post and i was really impressed by reading this Big Data Hadoop Online Training Bangalore

    ReplyDelete
  2. AnonymousJune 6, 2019 at 12:36 AM

    this is a very good post very helpful.
    is there a way to access the data in an array under the document?

    ReplyDelete
    Replies
    1. JDAAugust 25, 2019 at 10:04 AM

      Yes, depends on how you want the data to look, but bi connector actually takes care of that for you by performing $unwind to the collection, so information in arrays are presented as separate table

      Delete
  3. alexisMay 6, 2020 at 3:59 PM

    Put " --maxVarcharLength 8000 " at the top!!!

    ReplyDelete

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