(How) does SQL work?

[ra...@googlemail.com]

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

Re: (How) does SQL work?

[Jacques Nadeau <ja...@apache.org>]

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, <ra...@googlemail.com> 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
>
>