Category: MongoDB

Article originally posted on InfoQ. Visit InfoQ

The ability for users to directly query and explore data has long been a hallmark of professional databases. With a vendor supplied tool and a basic understanding of SQL, users can query any type of data without extensive training in that particular product. The main exception to this is the so-called NoSQL databases. With each NoSQL database needing its own specialized syntax, training costs can skyrocket.

Studio 3T seeks to address this by providing an SQL-based user interface. This user interface looks remarkably similar to what you would find in SQL Server Management Studio or PosgreSQL’s pgAdmin. It has the standard thee-panel layout with the list of the collections on the left, an SQL editor on the top-right, and the results pane on the bottom-right.

Like many SQL-based tools, you can directly edit records in Studio 3T. While this can be useful for manual data correction, normally one would set the tool into “read-only mode” when connecting to a production database to reduce the risk of making a mistake.

SQL to JavaScript (node.JS), Java, Python, and C# Code

A unique feature of Studio 3T is the ability to convert SQL to language specific code. After verifying the query is working, you can switch the to “Query Code” tab to see what it would look like in JavaScript (node.JS), Java, Python, or C#. Besides being a useful training tool, this can save a lot of time that would otherwise be spent running lengthy integration tests.

Developers using this feature should also take advantage of the “explain query” support. As in other database engines, this will give you important information about the expected performance characteristics of the query.

Joins for MongoDB

Studio 3T adds new features fairly frequently and among the more recent updates is the ability to perform SQL-style inner and left joins against MongoDB data sets​.

These joins are expressed in MongoDB’s native query language using the $lookup operator. This creates some limitations some limitations in the SQL implementation. While multiple joins are supported, each join can only reference tables to the left of it. Or in other words, right joins are not supported. Likewise full joins cannot be used.

Other SQL Features

Studio 3T’s SQL support also includes:

• GROUP BY, ORDER BY and Aggregate Functions
• Limit and Offset (i.e. data pagination)
• Wildcards using LIKE
• Testing If a Value Is a Member of a Set using IN
• Testing If a Value Lies within a Range using BETWEEN
• Accessing Embedded Fields Using Dotted Names

This last one allows you to use standard OOP style dot-notation to read child fields. For example, to read a zip code you would use "address.zip_code". As with many database engines, field names may be surrounded with brackets or double quotes. Single quotes are used for string literals.

The SQL Query and Query Code generation features require the Studio 3T Pro version.

This is a quick blogpost from notes that i have gathered over time.  There may be situations where support staff might be restricted from logging directly into a Mongodb instance.  But there may also be a requirement for support staff to get information on the status of Mongodb environments, especially users of the Community version of Mongodb.  These commands could also be run via a shell script as well that would allow a sysadmin to automate these commands into a daily routine.

You can quickly obtain information by executing the following commands:

mongo –port –username <username> –password <password> –authenticationDatabase admin –eval “mongo command”

Of course you will need to add the required authentication parameters relevant to your installation.

Obtaining host information – you can execute the mongo shell command along with some configuration options such as database, authenticatioen, and the –eval command followed by the actual mongo command you want to execute.  The command is broken into the following parameters:

I setup a quick test environment, so no need to use any authentication parameters, but you can see how to execute the command and the associated output.  You can see the version along with the host as well as os information.

$ mongo –eval “db.hostInfo()”

MongoDB shell version v3.4.9

connecting to: mongodb://127.0.0.1:27017/test_db

MongoDB server version: 3.4.9

{

“system” : {

“currentTime” : ISODate(“2017-10-23T20:50:43.959Z”),

“hostname” : “MacBook.local”,

“cpuAddrSize” : 64,

“memSizeMB” : 16384,

“numCores” : 8,

“cpuArch” : “x86_64”,

“numaEnabled” : false

},

“os” : {

“type” : “Darwin”,

“name” : “Mac OS X”,

“version” : “16.7.0”

},

“extra” : {

“versionString” : “Darwin Kernel Version 16.7.0: T4”,

“alwaysFullSync” : 0,

“nfsAsync” : 0,

“model” : “MacBook,3”,

“physicalCores” : 4,

“cpuFrequencyMHz” : 2900,

“cpuString” : “Intel(R) Core(TM) i7-7820HQ CPU @ 2.90GHz”,

“cpuFeatures” : “FPU VME DE PSE TSC MSR PAE MCE CX8 APIC SEP MTRR PGE MCA CMOV PAT PSE36 CLFSH DS ACPI MMX FXSR SSE SSE2 SS HTT TM PBE SSE3 PCLMULQDQ DTES64 MON DSCPL VMX SMX EST TM2 SSSE3 FMA CX16 TPR PDCM SSE4.1 SSE4.2 x2APIC MOVBE POPCNT AES PCID XSAVE OSXSAVE SEGLIM64 TSCTMR AVX1.0 RDRAND F16C SYSCALL XD 1GBPAGE EM64T LAHF LZCNT PREFETCHW RDTSCP TSCI”,

“pageSize” : 4096,

“scheduler” : “multiq”

},

“ok” : 1

}

Now, you would like to see what databases reside on this particular host with the listDatabases command. 

$ mongo –eval  “printjson(db.adminCommand( { listDatabases: 1 } ))”

MongoDB shell version v3.4.9

connecting to: mongodb://127.0.0.1:27017

MongoDB server version: 3.4.9

{

“databases” : [

{

“name” : “admin”,

“sizeOnDisk” : 49152,

“empty” : false

},

{

“name” : “local”,

“sizeOnDisk” : 65536,

“empty” : false

},

{

“name” : “test_db”,

“sizeOnDisk” : 65536,

“empty” : false

}

],

“totalSize” : 180224,

“ok” : 1

}

If you would like to see the collections within the database test_db, you can issue the following command to get a list of collections in json format.

$ mongo test_db –eval “printjson(db.getCollectionNames())”

MongoDB shell version v3.4.9

connecting to: mongodb://127.0.0.1:27017/test_db

MongoDB server version: 3.4.9

[ “cities”, “names” ]

 

That’s a quick overview of what can be done at the command line. For a more comprehensive list of commands, you can use the db.help() option to get a list of commands

As you can see it’s pretty comprehensive and is valuable for a sys admin and/or database administrator to get a quick picture of your Mongodb environment.

$ mongo test_db –eval “db.help()”

MongoDB shell version v3.4.9

connecting to: mongodb://127.0.0.1:27017/test_db

MongoDB server version: 3.4.9

DB methods:

db.adminCommand(nameOrDocument) – switches to ‘admin’ db, and runs command [ just calls db.runCommand(…) ]

db.auth(username, password)

db.cloneDatabase(fromhost)

db.commandHelp(name) returns the help for the command

db.copyDatabase(fromdb, todb, fromhost)

db.createCollection(name, { size : …, capped : …, max : … } )

db.createView(name, viewOn, [ { $operator: {…}}, … ], { viewOptions } )

db.createUser(userDocument)

db.currentOp() displays currently executing operations in the db

db.dropDatabase()

db.eval() – deprecated

db.fsyncLock() flush data to disk and lock server for backups

db.fsyncUnlock() unlocks server following a db.fsyncLock()

db.getCollection(cname) same as db[‘cname’] or db.cname

db.getCollectionInfos([filter]) – returns a list that contains the names and options of the db’s collections

db.getCollectionNames()

db.getLastError() – just returns the err msg string

db.getLastErrorObj() – return full status object

db.getLogComponents()

db.getMongo() get the server connection object

db.getMongo().setSlaveOk() allow queries on a replication slave server

db.getName()

db.getPrevError()

db.getProfilingLevel() – deprecated

db.getProfilingStatus() – returns if profiling is on and slow threshold

db.getReplicationInfo()

db.getSiblingDB(name) get the db at the same server as this one

db.getWriteConcern() – returns the write concern used for any operations on this db, inherited from server object if set

db.hostInfo() get details about the server’s host

db.isMaster() check replica primary status

db.killOp(opid) kills the current operation in the db

db.listCommands() lists all the db commands

db.loadServerScripts() loads all the scripts in db.system.js

db.logout()

db.printCollectionStats()

db.printReplicationInfo()

db.printShardingStatus()

db.printSlaveReplicationInfo()

db.dropUser(username)

db.repairDatabase()

db.resetError()

db.runCommand(cmdObj) run a database command.  if cmdObj is a string, turns it into { cmdObj : 1 }

db.serverStatus()

db.setLogLevel(level,<component>)

db.setProfilingLevel(level,<slowms>) 0=off 1=slow 2=all

db.setWriteConcern( <write concern doc> ) – sets the write concern for writes to the db

db.unsetWriteConcern( <write concern doc> ) – unsets the write concern for writes to the db

db.setVerboseShell(flag) display extra information in shell output

db.shutdownServer()

db.stats()

db.version() current version of the server

Command line execution of Mongodb commands allows support staff to quickly obtain information on the Mongodb environment without running the risk of accidentally causing disruption of service

RAUL SALAS Raul@mobilemonitoringsolutions.com