incubator-drill-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Hao Zhu <h...@maprtech.com>
Subject Re: Doubt on Querying JSON
Date Sat, 15 Nov 2014 20:43:27 GMT
Correct. In this case, flatten() has to be in a subquery.

with tmp as
(select `timestamp` as newtime,flatten(data) as newdata from
dfs.root.`/Users/hzu/Documents/sharefolder/test2/*.json`)
SELECT newtime, tmp.newdata.volumename AS volumename from tmp;



On Sat, Nov 15, 2014 at 12:46 AM, Steven Phillips <sphillips@maprtech.com>
wrote:

> You should try something like this:
>
> SELECT x.newtime,
>        x.newdata.volumename AS volumename
> FROM   (SELECT `timestamp`   AS newtime,
>                flatten(data) AS newdata
>         FROM   dfs.root.`/users/hzu/documents/sharefolder/test2/*.json`)x;
>
> On Fri, Nov 14, 2014 at 9:59 PM, mufy <mufeed.usman@gmail.com> wrote:
>
> > Hao,
> >
> > In the above, how should the query be modified to extract the info as
> > below?
> >
> > +------------+------------+
> > |  newtime   |  volumename   |
> > +------------+------------+
> > | 1415688106710 | a |
> > | 1415688106710 | b |
> > | 1415688106710 | c |
> > +------------+------------+
> >
> >
> >
> > ---
> > Mufeed Usman
> > My LinkedIn <http://www.linkedin.com/pub/mufeed-usman/28/254/400> | My
> > Social Cause <http://www.vision2016.org.in/> | My Blogs : LiveJournal
> > <http://mufeed.livejournal.com>
> >
> >
> >
> >
> > On Sat, Nov 15, 2014 at 2:35 AM, Hao Zhu <hzhu@maprtech.com> wrote:
> >
> > > Hi Jim,
> > >
> > > I verified this issue you mentioned on latest 0.7 build and it is
> fixed.
> > >
> > > 0: jdbc:drill:zk=local> select `timestamp` as newtime,flatten(data) as
> > > > newdata from
> dfs.root.`/Users/hzu/Documents/sharefolder/test2/*.json`;
> > > > +------------+------------+
> > > > |  newtime   |  newdata   |
> > > > +------------+------------+
> > > > | 1415688106710 |
> > > >
> > >
> >
> {"volumename":"a","actualreplication":[0,0,0],"InodesExceededAlarm":0,"ContainersNonLocalAlarm":0}
> > > > |
> > > > | 1415688106710 |
> > > >
> > >
> >
> {"volumename":"b","actualreplication":[0,0,0],"InodesExceededAlarm":0,"ContainersNonLocalAlarm":0}
> > > > |
> > > > | 1415688106710 |
> > > >
> > >
> >
> {"volumename":"c","actualreplication":[0,0,0],"InodesExceededAlarm":0,"ContainersNonLocalAlarm":0}
> > > > |
> > > > | 1415688106710 |
> > > >
> > >
> >
> {"volumename":"a","actualreplication":[0,0,0],"InodesExceededAlarm":0,"ContainersNonLocalAlarm":0}
> > > > |
> > > > | 1415688106710 |
> > > >
> > >
> >
> {"volumename":"b","actualreplication":[0,0,0],"InodesExceededAlarm":0,"ContainersNonLocalAlarm":0}
> > > > |
> > > > | 1415688106710 |
> > > >
> > >
> >
> {"volumename":"c","actualreplication":[0,0,0],"InodesExceededAlarm":0,"ContainersNonLocalAlarm":0}
> > > > |
> > > > +------------+------------+
> > > > 6 rows selected (0.242 seconds)
> > >
> > > Thanks,
> > >
> > > Hao
> > >
> > > On Tue, Nov 11, 2014 at 3:06 PM, Jacques Nadeau <jacques@apache.org>
> > > wrote:
> > >
> > > > On Tue, Nov 11, 2014 at 12:21 PM, Jim Bates <jbates@maprtech.com>
> > wrote:
> > > >
> > > > > I had trouble with flatten extending past one wile. If you include
> > more
> > > > > than 1 json file in your data set it never seams to finish the
> > command.
> > > > In
> > > > > my case it returned in 20 sec with one file but when the data set
> had
> > > two
> > > > > files it never finiahed after 5 min so I gave up till a later
> release
> > > > comes
> > > > > along.
> > > > >
> > > >
> > > > Yeah, that'll happen with nightly builds.  Give the build a few days.
> > > > We're testing fixes now to deal with a lot of these issues.
> > > >
> > >
> >
>
>
>
> --
>  Steven Phillips
>  Software Engineer
>
>  mapr.com
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message