Using SQL with MongoDB – the “Aqua Data Studio” from www.aquafold.com

MongoDB is the most popular and highly ranked in the NoSQL database world. It has a flexible architecture and a very fast query execution mechanism with the support of in-memory computations. Mongo storage follows a JSON based architecture and JavaScript based programing language.

Aqua Data Studio for MongoDB:

There are a number of commercial and non-commercial tools available for MongoDB. The “Aqua Data Studio” from the developer of www.aquafold.com is one of the most versatile tools that provide numerous flexibilities and has many intuitive features with a very easy to use GUI mechanism which helps to perform various kinds of database related activities. It natively supports more than 28 different heterogeneous databases and runs on Windows, Mac and Linux. Therefore, it becomes easily possible to administer, analyze and visualize data from a single application.

SQL vs JavaScript in MongoDB:

Those who have gotten their hands dirty from writing SQL and can’t think of getting rid of SQL but still want to use SQL with MongoDB will appreciate utilizing “Aqua Data Studio”. MongoDB uses JavaScript programming language and JSON to perform data analysis as well as administering database servers, therefore, there is a very little learning curve. However, many of us like to utilize SQL like syntax instead of JavaScript against MongoDB databases.

Using both SQL and JavaScript against MongoDB with Aqua Data Studio:

 “Aqua Data Studio” gives us the flexibility to use either pure JavaScript based query or SQL like syntax. Let’s say we have a MongoDB collection named “master_data_sql” in which we store various SQL Server events collected from hundreds of database servers. Now the next step is to analyze the collection to answer any specific questions that we may have either on a daily basis or ad-hoc basis.

MongoDB Sample collection:

From the above MongoDB schema, following are some simple queries  from both JavaScript and SQL to demonstrate the similarity. All queries were executed and tested with MongoDB V3.4 and “Aqua Data Studio v17.0”.

Query Comparison #1:

Find all entries if the message column contains ‘Login failed’.

MongoDB: db.master_data_sql.find({message: /Login failed/i })

SQL: SELECT * FROM master_data_sql WHERE     message LIKE '%Login failed%'

Query Comparison #2:

Find all entries if the “event_type” column contains ‘Login failed’ and DBCC.

MongoDB: db.master_data_sql.find({event_type: /Login Failed|DBCC/i })

SQL: SELECT * FROM master_data_sql WHERE     event_type LIKE 'Login Failed' OR event_type LIKE 'DBCC'

Query Comparison #3:

Find all entries if the event_type column contains ‘Login failed’ and DBCC, display only three columns “event_date”, ”event_type” and “message”.

MongoDB: db.master_data_sql.find({event_type: /Login Failed|DBCC/i},

                        {event_date:1, event_type:1, message:1,_id:0} )

SQL: SELECT event_date, event_type, message FROM master_data_sql WHERE  event_type LIKE 'Login Failed' OR event_type LIKE 'DBCC'

Query Comparison #4:

Find all entries if the “event_type” column contains ‘Login failed’ and DBCC; event_date is equal to ‘11/23/2016’, display only three columns “event_date”, ”event_type” and “message”.

MongoDB: db.master_data_sql.find({event_date:'11/23/2016', event_type: /Deadlock|DBCC/i},

                        {event_date:1, event_type:1, message:1,_id:0} )

SQL: SELECT event_date, event_type, message FROM master_data_sql

                WHERE event_date ='11/23/2016'

   AND (event_type LIKE 'Deadlock' OR event_type LIKE 'DBCC')

  

Query Comparison #5:

A group by example.

MongoDB:

db.master_data_sql.aggregate

([

   { $match: { event_type: {$in: ["Login Failed"]}, event_date: {$gte: "11/12/2016", $lte: "11/18/2016"} } },

   { $group: { _id: { event_date: "$event_date",  event_type: "$event_type" }, total: {$sum: 1} }},

   { $project: { event_date: "$_id.event_date", event_type: "$_id.event_type", total: "$total", _id: 0} },

   { $sort: { event_date: 1, total: -1  }}

])

SQL:

SELECT

        ms.event_date,

        ms.event_type,

        COUNT(event_type) AS total

FROM

        master_data_sql AS ms

GROUP BY

        ms.event_date,

        ms.event_type

HAVING

        (ms.event_date >= '11/12/2016' AND

        ms.event_date <= '11/18/2016') AND

        event_type IN ('Login Failed')

ORDER BY

        ms.event_date,

        ms.total DESC

Figure Side by side output from the Aqua Data Studio:

Query Comparison #6:

Another group by example.

MongoDB:

db.master_data_sql.aggregate

([

   { $match: {event_date:  "12/07/2016"} },

   { $group: {_id: { event_date: "$event_date", host_server: "$host_server",

                       host_description: "$host_description",

                       event_type: "$event_type" }, total: { $sum: 1 }} },

   { $project: { event_date: "$_id.event_date", host_server: "$_id.host_server",

                       host_description: "$_id.host_description",

                       event_type: "$_id.event_type", total: "$total", _id: 0 }},

   { $sort: {  total: -1, event_type: 1} }  

])

SQL:

SELECT

        ms.event_date ,

        ms.host_server,

        ms.host_description,

        ms.event_type,

        COUNT(ms.event_type) AS total

FROM

        logdb.master_data_sql ms

GROUP BY

        ms.event_date,

        ms.host_server,

        ms.host_description,

        ms.event_type

HAVING

        (ms.event_date = '12/07/2016')

ORDER BY

        ms.total DESC,

        ms.event_type ASC

Figure Side by side output from the Aqua Data Studio:

Dashboard for data visualization:

To learn more visit:

Aqua Data Studio: http://www.aquafold.com/