Query MongoDB using Apache Drill

Hi guys,

Today, we will see how to query the mongodb using apache drill sql interface. Apache drill provides ANSI SQL language for query data from any data source. We will use it to query mongodb.

If you don’t have mongodb and apache drill on your machine, then follow the below link before going further.

a) https://ranafaisal.wordpress.com/2015/05/13/install-mongodb-on-ubuntu-14-04/

b) https://ranafaisal.wordpress.com/2015/05/13/install-apache-drill-on-ubuntu-14-04/


Now lets start

1) Run apache drill shell “bin/sqlline -u jdbc:drill:zk=local

2) Open WEB UI to enable MongoDB driver, URL of web UI is “http://localhost:8047

3) Now click on storage link

4) In storage , find mongodb and click on update

5) Add the following info in the textbox

“type”: “mongo”,
“connection”: “mongodb://localhost:27017/”,
“enabled”: true

6) Then click Update button to enable this driver

7) On apache drill shell, run “show databases;“, this will also display the mongodb databases too.

8) Now lets query zips collection, “select * from mongo.mydb.zips;“, it will display all zips codes save in zips collection.


Install MongoDB on Ubuntu 14.04

Hi guys,

Today, we will see how to install mongodb on your machine. Please follow the below steps

1) run this command “sudo apt-key adv –keyserver hkp://keyserver.ubuntu.com:80 –recv 7F0CEB10

2) Now run this command “echo “deb http://repo.mongodb.org/apt/ubuntu “$(lsb_release -sc)”/mongodb-org/3.0 multiverse” | sudo tee /etc/apt/sources.list.d/mongodb.list

3) We added the mongodb repository URL to our ubuntu repository links.

4) Now update it using “sudo apt-get update

5) Install mongodb using “sudo apt-get install mongodb-org

6) Start service “sudo service mongod start

7) Congratulations, mongodb is installed, now open the shell using “mongo

8) For shell commands read the following tutorial [http://docs.mongodb.org/manual/tutorial/getting-started-with-the-mongo-shell/]

9) Load dummy data for your experiments, download json file  [http://media.mongodb.org/zips.json?_ga=1.139282992.2048111731.1429111258]

10) Import it using “mongoimport –db mydb –collection zips –file zips.json