MapR Drill SandBox Exploration

Start your MapR Sandbox and use putty to connect to your MapR SandBox:

 The password is the same as your username.

After you login successfully, type “sqlline” to access to your Drill

Type ” show databases;” and you will see a list of available databases as below

 One thing to note, there are some reserved words like month, default etc, you need to use back tick ` in front of those reserved words to escape, otherwise you will encounter error.

Select one database and start.

command as follow:
use maprdb;
show tables;

and you will see a list of tables inside the database.

 To know more about the table:

describe customers;

 do a simple select:

select personal from customers limit 5;

 do a count:

select count(*) from customers;

 Change to another database:

use hive.`default`         Note: use back tick ` in front and behind,  the same button normally with ~

Do a slightly difficult operation:

select `month`, state, sum(order_total) as sales from orders group by `month`, state order by 3 desc limit 10;

In the dfs.logs database, the storage structure as below, to extract a certain file within the dir0, do the following:

select * from dfs.logs where dir0=’2013′  limit 3;

Another demonstration of what MapR drill is capable of is to extract click stream data. It is a good demonstration for our company’s proof of concept of the Big Data business case.

change to dfs.clicks database

select * from `clicks/clicks.json` limit 5;

will demonstrate how to extract MapR data directly from MicroStrategy to build visualization when I am free.