Hi Drillers,
I've just logged https://issues.apache.org/jira/browse/DRILL-72, a set of
patches to implement the SQL front-end. It also includes a command-line based
on the sqlline utility.
It's easy to use. Just run the 'sqlline' script in the sandbox/prototype
directory, like this:
$ cd sandbox/prototype/
$ mvn install
$ ./sqlline
Loaded singnal handler: SunSignalHandler
/home/jhyde/.sqlline/sqlline.properties (No such file or directory)
sqlline version ??? by Marc Prud'hommeaux
sqlline> !connect jdbc:optiq:model=common/target/test-classes/donuts-model.json
admin admin
Connecting to jdbc:optiq:model=common/target/test-classes/donuts-model.json
Connected to: Optiq (version 0.4.2)
Driver: Optiq JDBC Driver (version 0.4.2)
Autocommit status: true
Transaction isolation: TRANSACTION_REPEATABLE_READ
sqlline> !tables
+------------+--------------+-------------+---------------+----------+---------+
| TABLE_CAT | TABLE_SCHEM | TABLE_NAME | TABLE_TYPE | REMARKS | TYPE_CA |
+------------+--------------+-------------+---------------+----------+---------+
| null | DONUTS | DONUTS | TABLE | null | null |
| null | metadata | COLUMNS | SYSTEM_TABLE | null | null |
| null | metadata | TABLES | SYSTEM_TABLE | null | null |
+------------+--------------+-------------+---------------+----------+---------+
sqlline> !set outputformat csv
sqlline> select * from donuts;
============
'_MAP'
'{batters={batter=[{id=1001, type=Regular}, {id=1002, type=Chocolate},
{id=1003, type=Blueberry}, {id=1004, type=Devil's Food}]}, id=0001, name=Cake,
ppu=0.55, sales=35, topping=[{id=5001, type=None}, {id=5002, type=Glazed},
{id=5005, type=Sugar}, {id=5007, type=Powdered Sugar}, {id=5006, type=Chocolate
with Sprinkles}, {id=5003, type=Chocolate}, {id=5004, type=Maple}], type=donut}'
'{batters={batter=[{id=1001, type=Regular}]}, id=0002, name=Raised, ppu=0.69,
sales=145, topping=[{id=5001, type=None}, {id=5002, type=Glazed}, {id=5005,
type=Sugar}, {id=5003, type=Chocolate}, {id=5004, type=Maple}], type=donut}'
'{batters={batter=[{id=1001, type=Regular}, {id=1002, type=Chocolate}]},
id=0003, name=Old Fashioned, ppu=0.55, sales=300, topping=[{id=5001,
type=None}, {id=5002, type=Glazed}, {id=5003, type=Chocolate}, {id=5004,
type=Maple}], type=donut}'
'{batters={batter=[{id=1001, type=Regular}, {id=1002, type=Chocolate},
{id=1003, type=Blueberry}, {id=1004, type=Devil's Food}]}, filling=[{id=6001,
type=None}, {id=6002, type=Raspberry}, {id=6003, type=Lemon}, {id=6004,
type=Chocolate}, {id=6005, type=Kreme}], id=0004, name=Filled, ppu=0.69,
sales=14, topping=[{id=5001, type=None}, {id=5002, type=Glazed}, {id=5005,
type=Sugar}, {id=5007, type=Powdered Sugar}, {id=5006, type=Chocolate with
Sprinkles}, {id=5003, type=Chocolate}, {id=5004, type=Maple}], type=donut}'
'{batters={batter=[{id=1001, type=Regular}]}, id=0005, name=Apple Fritter,
ppu=1.0, sales=700, topping=[{id=5002, type=Glazed}], type=donut}'
5 rows selected (1.027 seconds)
sqlline> explain plan for select * from donuts;
'PLAN'
'EnumerableDrillRel
DrillProjectRel(_MAP=[$0])
DrillScan(table=[[DONUTS, DONUTS]])
'
1 row selected (0.053 seconds)
sqlline> select _map['ppu'] as ppu, _map['name'] as name from donuts;
'PPU','NAME'
'0.55','Cake'
'0.69','Raised'
'0.55','Old Fashioned'
'0.69','Filled'
'1.0','Apple Fritter'
5 rows selected (0.119 seconds)
sqlline> !quit
Closing: net.hydromatic.optiq.jdbc.FactoryJdbc41$OptiqConnectionJdbc41
$
Note that each Drill table has a single column in SQL, called _MAP. You can
access fields using "_MAP['fieldName']", and you can also use "CAST(... AS
<datatype>)" to convert to the desired type.
Julian
