drill-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andries Engelbrecht <aengelbre...@maprtech.com>
Subject Re: Reg: FLATTEN
Date Tue, 03 Mar 2015 18:05:34 GMT
Thanks for showing that it is always a good idea to use the table alias for column fields and
functions when working with complex data sets. 

I’m not sure if the query produces the desired result.

It may be best to use a subquery to evaluate the predicate against each record of the flattened
data. This way all records of the flatten set can be evaluated against the predicate.

JSON file
{"name":"classic","fillings":[ {"name":"sugar","cal":500} , {"name":"flour","cal":300} ] }


select flat.fill from (select flatten(t.fillings) as fill from dfs.flatten.`test.json` t)
flat where flat.fill.cal >300;

+------------+
|    fill    |
+------------+
| {"name":"sugar","cal":500} |
+------------+
1 row selected (0.421 seconds)

select flat.fill from (select flatten(t.fillings) as fill from dfs.flatten.`test.json` t)
flat where flat.fill.cal >= 300;
+------------+
|    fill    |
+------------+
| {"name":"sugar","cal":500} |
| {"name":"flour","cal":300} |
+------------+
2 rows selected (0.125 seconds)

—Andries

On Mar 3, 2015, at 9:49 AM, Kristine Hahn <khahn@maprtech.com> wrote:

> select flatten(t.fillings) as f from
> dfs.`/Users/khahn/Documents/test.json` t where t.fillings[0].cal>300 ;
> 
> +------------+
> |     f      |
> +------------+
> | {"name":"sugar","cal":500} |
> | {"name":"flour","cal":300} |
> +------------+
> 2 rows selected (0.113 seconds)
> 
> Kristine Hahn
> Sr. Technical Writer
> 415-497-8107 @krishahn
> 
>  <http://www.mapr.com/>
> 
> 
> Now Available - Free Hadoop On-Demand Training
> <http://www.mapr.com/training?utm_source=Email&utm_medium=Signature&utm_campaign=Free%20available>
> 
> 
> On Tue, Mar 3, 2015 at 5:51 AM, Jahagirdar, Madhu <
> madhu.jahagirdar@philips.com> wrote:
> 
>> I am getting the below error
>> 
>> select flatten(fillings) as f
>> . . . . . . . . . . . >   from
>> dfs.`/Users/philips/Development/BigData/RS/json/test.json` t
>> . . . . . . . . . . . >   where t.f.cal>300;
>> Query failed: Query failed: Failure while running fragment., index: -4,
>> length: 4 (expected: range(0, 16384)) [
>> dc720323-2716-4fa4-bdd7-414115d555d4 on 161.85.89.59:31010 ]
>> [ dc720323-2716-4fa4-bdd7-414115d555d4 on 161.85.89.59:31010 ]
>> 
>> 
>> ________________________________________
>> From: Aditya [adityakishore@gmail.com]
>> Sent: Tuesday, March 03, 2015 2:17 PM
>> To: user
>> Cc: ted.dunning@gmail.com; Jacques Nadeau
>> Subject: Re: Reg: FLATTEN
>> 
>> Try
>> 
>> select flatten(fillings) as f
>>  from dfs.`/Users/philips/Development/BigData/RS/json/test.json` t
>>  where t.f.cal>300 ;
>> 
>> On Tue, Mar 3, 2015 at 12:25 AM, Jahagirdar, Madhu <
>> madhu.jahagirdar@philips.com> wrote:
>> 
>>> Ted,
>>> 
>>> I am getting below error just executing simple statement on flatten, let
>>> me know if its bug or something I am doing wrong
>>> 
>>> select flatten(fillings) as f
>>>  from dfs.`/Users/philips/Development/BigData/RS/json/test.json`
>>>  where f.cal>300
>>>  ;
>>> 
>>> JSON:
>>> {"name":"classic","fillings":[{"name":"sugar","cal":500}]}
>>> 
>>> ERROR:
>>> 
>>> Error: exception while executing query: Failure while executing query.
>>> (state=,code=0)
>>> 
>>> 0: jdbc:drill:zk=local> select flatten(fillings) as f
>>> 
>>> . . . . . . . . . . . >   from
>>> dfs.`/Users/Development/BigData/RS/json/test.json`
>>> 
>>> . . . . . . . . . . . >   where f.name='sugar'
>>> 
>>> . . . . . . . . . . . >   ;
>>> 
>>> Mar 03, 2015 7:47:03 AM org.eigenbase.sql.validate.SqlValidatorException
>>> <init>
>>> 
>>> SEVERE: org.eigenbase.sql.validate.SqlValidatorException: Table 'f' not
>>> found
>>> 
>>> Mar 03, 2015 7:47:03 AM org.eigenbase.util.EigenbaseException <init>
>>> 
>>> SEVERE: org.eigenbase.util.EigenbaseContextException: At line 3, column
>> 9:
>>> Table 'f' not found
>>> 
>>> Query failed: Query failed: Failure validating SQL.
>>> org.eigenbase.util.EigenbaseContextException: At line 3, column 9: Table
>>> 'f' not found
>>> 
>>> 
>>> Error: exception while executing query: Failure while executing query.
>>> (state=,code=0)
>>> 
>>> Regards,
>>> Madhu Jahagirdar
>>> 
>>> ________________________________
>>> The information contained in this message may be confidential and legally
>>> protected under applicable law. The message is intended solely for the
>>> addressee(s). If you are not the intended recipient, you are hereby
>>> notified that any use, forwarding, dissemination, or reproduction of this
>>> message is strictly prohibited and may be unlawful. If you are not the
>>> intended recipient, please contact the sender by return e-mail and
>> destroy
>>> all copies of the original message.
>>> 
>> 
>> ________________________________
>> The information contained in this message may be confidential and legally
>> protected under applicable law. The message is intended solely for the
>> addressee(s). If you are not the intended recipient, you are hereby
>> notified that any use, forwarding, dissemination, or reproduction of this
>> message is strictly prohibited and may be unlawful. If you are not the
>> intended recipient, please contact the sender by return e-mail and destroy
>> all copies of the original message.
>> 


Mime
View raw message