Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Threaded View
Drill, mail # user - (How) does SQL work?


Copy link to this message
-
Re: (How) does SQL work?
Jacques Nadeau 2013-09-02, 00:26
Here are a selection of example queries that should work with the tip of
master.  I think Ted is working on putting together an updated quickstart
for the newer stuff.

Note that these all expect you're source code is in /src/drill.

You should be able to run these by:

1) checking out the code.
2) cd sandbox/prototype
3) mvn clean install
4) rm .classpath
5) ./sqlline -u jdbc:drill:schema=parquet-local -n admin -p admin

Then run the queries.

// Json Files
SELECT * FROM
"jsonl"."/src/drill/sandbox/prototype/exec/java-exec/src/test/resources/join/merge_single_batch.left.json";

// Nested data
SELECT * FROM
"jsonl"."/src/drill/sandbox/prototype/exec/java-exec/src/test/resources/scan_json_test_3.json";

SELECT
  _MAP['a.a.d'],
  _MAP['a.b'],
  _MAP['test']
FROM
"jsonl"."/src/drill/sandbox/prototype/exec/java-exec/src/test/resources/scan_json_test_3.json"
order by _MAP['test'] DESC, _MAP['a.b'] ASC;

SELECT
  _MAP['a.a.d'],
  _MAP['a.b'],
  _MAP['test']
FROM
"jsonl"."/src/drill/sandbox/prototype/exec/java-exec/src/test/resources/scan_json_test_3.json";

// repeated value.
SELECT
  _MAP['test'],
  repeated_count(_MAP['test2'])
FROM
"jsonl"."/src/drill/sandbox/prototype/exec/java-exec/src/test/resources/scan_json_test_4.json";
// repeated value with a repeated function, *NOT WORKING* need optiq to
pass through
SELECT
  _MAP['test'],
  repeated_count(_MAP['test2'])
FROM
"jsonl"."/src/drill/sandbox/prototype/exec/java-exec/src/test/resources/scan_json_test_4.json";
// Inner Join (json)
SELECT a, aa, b, bb FROM
(SELECT cast(_MAP['a'] as integer) as a, cast(_MAP['b'] as int) as b FROM
"jsonl"."/src/drill/sandbox/prototype/exec/java-exec/src/test/resources/join/merge_single_batch.left.json")tbl1
join (SELECT cast(_MAP['aa'] as integer) as aa, cast(_MAP['bb'] as int) as
bb FROM
"jsonl"."/src/drill/sandbox/prototype/exec/java-exec/src/test/resources/join/merge_single_batch.right.json")tbl2
on tbl1.a = tbl2.aa;

// Left Join
SELECT a, aa, b, bb FROM
(SELECT cast(_MAP['a'] as integer) as a, cast(_MAP['b'] as int) as b FROM
"jsonl"."/src/drill/sandbox/prototype/exec/java-exec/src/test/resources/join/merge_single_batch.left.json")tbl1
left join (SELECT cast(_MAP['aa'] as integer) as aa, cast(_MAP['bb'] as
int) as bb FROM
"jsonl"."/src/drill/sandbox/prototype/exec/java-exec/src/test/resources/join/merge_single_batch.right.json")tbl2
on tbl1.a = tbl2.aa;

// Right Join
SELECT a, aa, b, bb FROM
(SELECT cast(_MAP['aa'] as integer) as aa, cast(_MAP['bb'] as int) as bb
FROM
"jsonl"."/src/drill/sandbox/prototype/exec/java-exec/src/test/resources/join/merge_single_batch.right.json")tbl2
right join
(SELECT cast(_MAP['a'] as integer) as a, cast(_MAP['b'] as int) as b FROM
"jsonl"."/src/drill/sandbox/prototype/exec/java-exec/src/test/resources/join/merge_single_batch.left.json")tbl1
on tbl1.a = tbl2.aa;
On Thu, Aug 29, 2013 at 4:15 AM, <[EMAIL PROTECTED]> wrote:

>
> Hi Drill users,
>
> i am trying to get started with drill, basically by following the demo
> howto in
> the wiki (https://cwiki.apache.org/confluence/display/DRILL/Demo+HowTo).
> But
> the interactive queries do not seem to work (the SELECT * FROM DONUTS). I
> also
> noticed that the unit tests involving SQL are @ignored and will not work
> when un@ignored. So is the SQL in any usable state at all, or what is
> needed
> to make them usable?
>
> wbr - Rasmus
>
>