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

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