Using SQL Functions, Clauses, and Joins

Nov 2, 2018

You can use standard SQL clauses, such as WHERE and ORDER BY, to elaborate on this kind of simple query:

0: jdbc:drill:zk=local> select id, type from dfs.`/Users/brumsby/drill/donuts.json`
where id>0
order by id limit 1;

+------------+------------+
|     id     |    type    |
+------------+------------+
| 0001       | donut      |
+------------+------------+

1 row selected (0.318 seconds)

You can also join files (or tables, or files and tables) by using standard syntax:

0: jdbc:drill:zk=local> select tbl1.id, tbl1.type from dfs.`/Users/brumsby/drill/donuts.json` as tbl1
join
dfs.`/Users/brumsby/drill/moredonuts.json` as tbl2
on tbl1.id=tbl2.id;

+------------+------------+
|     id     |    type    |
+------------+------------+
| 0001       | donut      |
+------------+------------+

1 row selected (0.395 seconds)

Equivalent USING syntax and joins in the WHERE clause are also supported.

Standard aggregate functions work against JSON data. For example:

0: jdbc:drill:zk=local> select type, avg(ppu) as ppu_sum from dfs.`/Users/brumsby/drill/donuts.json` group by type;

+------------+------------+
|    type    |  ppu_sum   |
+------------+------------+
| donut      | 0.55       |
+------------+------------+

1 row selected (0.216 seconds)

0: jdbc:drill:zk=local> select type, sum(sales) as sum_by_type from dfs.`/Users/brumsby/drill/moredonuts.json` group by type;

+------------+-------------+
|    type    | sum_by_type |
+------------+-------------+
| donut      | 1194        |
+------------+-------------+

1 row selected (0.389 seconds)