Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 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?
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
>
>
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB