How to use Drill SQL in dbKoda

dbKoda 0.9 includes Drill SQL support, allowing you to issue SQL queries against your MongoDB databases.  Here's how you use it.

First, Choose the "Query Database with Drill" from the Database node you want to work with.  

If this is your first time using Drill,  dbKoda will start to download the drill libraries.  When the download is complete, you'll see a Drill SQL editor window which will look like this:

 

Now you are ready to issue Drill SQL commands.  Most Drill SQL commands are simple and familiar.  For instance we can SELECT a bunch of movies that are "PG" or "Family" rated:

In this collection, Actors are an array - if we want to get the actors for a particular film or films, we can use FLATTEN, to retrieve then one row per Actor.  This is the equivalent of UNWIND in the aggregation framework:

With a bit of fiddling, we can format the elements in the nested document as individual columns:

We can of course, join between tables and use GROUP BY and other SQL syntax.  So in this example, we join between the Sakila_films table and the Sakila_actors table to product a list of names and phone numbers of actors who have starred in R rated films, sorted by the number of films they have appeared in:

I hope that helps you get started with our Apache Drill support.  You can find the sample data I used here on our github repo - see here for details. 

Is article helpful?