ya that definitely works (as per my note above). I wonder though, is this case 'special' (where you have to alias the table to pull out this reserved word from a json file)?
The drill documentation @ https://drill.apache.org/docs/reserved-keywords/ merely says to use backticks (``), not to do any table aliasing.. On Tue, Jun 30, 2015 at 4:11 PM, Andries Engelbrecht < [email protected]> wrote: > Try using an alias on the profile > > select p.`user` from > maprfs.profiles.`profiles/2a77fbb8-c1d6-8266-619f-537892b35fe1.sys.drill` p; > > +---------+ > | user | > +---------+ > | cmatta | > +————+ > > > > On Jun 30, 2015, at 1:03 PM, Andy Pernsteiner <[email protected]> > wrote: > > > Ya I tried that: > > > > select `user` from > > `profiles/2aa32e9e-bdae-8949-8461-c14dafe63ee0.sys.drill` ; > > +---------------+ > > | user | > > +---------------+ > > | root | > > +---------------+ > > > > but this works: > > > > select t.`user` from > > `profiles/2aa32e9e-bdae-8949-8461-c14dafe63ee0.sys.drill` t ; > > +---------+ > > | user | > > +---------+ > > | cmatta | > > +---------+ > > > > > > > > On Tue, Jun 30, 2015 at 3:50 PM, Christopher Matta <[email protected]> > wrote: > > > >> It would seem that user is a protected word that Drill uses to return > your > >> username. Try specifying user without backticks on any dataset: > >> > >> 0: jdbc:drill:zk=sen11:5181,sen12:5181> select user from sys.version > >> . . . . . . . . . . . . . . . . . . . > ; > >> +---------+ > >> | user | > >> +---------+ > >> | cmatta | > >> +---------+ > >> 1 row selected (0.093 seconds) > >> 0: jdbc:drill:zk=sen11:5181,sen12:5181> select user from tweets_view > limit > >> 10; > >> +---------+ > >> | user | > >> +---------+ > >> | cmatta | > >> | cmatta | > >> | cmatta | > >> | cmatta | > >> | cmatta | > >> | cmatta | > >> | cmatta | > >> | cmatta | > >> | cmatta | > >> | cmatta | > >> +---------+ > >> 10 rows selected (1.5 seconds) > >> > >> Re-run your first query with the user keyword surrounded by back-ticks. > >> > >> > >> Chris Matta > >> [email protected] > >> 215-701-3146 > >> > >> On Tue, Jun 30, 2015 at 3:41 PM, Andy Pernsteiner < > >> [email protected] > >>> wrote: > >> > >>> I decided to embark on a short journey into querying drill profiles to > >> get > >>> interesting statistics from a shared cluster (where I have root priv's) > >>> > >>> What I noticed was that if I attempt to query on the 'user' field > within > >>> the JSON file (which appears as the element), that drill returns to me > >> the > >>> user who I am currently impersonating...not the user who originally ran > >> the > >>> query: > >>> > >>> (drill 1.0) > >>> > >>> /usr/bin/sqlline -u jdbc:drill: -n root > >>> > >>> then: > >>> > >>> select user from > >> `profiles/2aa32e9e-bdae-8949-8461-c14dafe63ee0.sys.drill` > >>> ; > >>> +-------+ > >>> | user | > >>> +-------+ > >>> | *root* | > >>> +-------+ > >>> > >>> cat 2aa32e9e-bdae-8949-8461-c14dafe63ee0.sys.drill|egrep -o > >>> 'user\":\"[a-z]+\"' > >>> > >>> user":"apernsteiner" > >>> > >>> > >>> > >>> Now, I stumbled across a workaround, which is to alias the table (t) > and > >>> prefix the 'user' column in the resultset w/ the table alias : > >>> > >>> 0: jdbc:drill:> select t.`user` from > >>> `profiles/2aa32e9e-bdae-8949-8461-c14dafe63ee0.sys.drill` t ; > >>> +---------+ > >>> | user | > >>> +---------+ > >>> | apernsteiner | > >>> +---------+ > >>> > >>> > >>> Is this expected? I could understand if Drill would only return valid > >> data > >>> if I properly aliased and subscripted to get to the element...however: > >> why > >>> would it return the logged in username in this query if I neglected to > do > >>> so? > >>> > >>> > >>> > >>> > >>> -- > >>> Andy Pernsteiner > >>> Manager, Field Enablement > >>> ph: 206.228.0737 > >>> > >>> 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 > >>>> > >>> > >> > > > > > > > > -- > > Andy Pernsteiner > > Manager, Field Enablement > > ph: 206.228.0737 > > > > 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 > > > > -- Andy Pernsteiner Manager, Field Enablement ph: 206.228.0737 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>
