'user' is a SQL reserved word.
When it's used alone, it is a system function, just like CURRENT_USER. See
http://calcite.incubator.apache.org/docs/reference.html (System functions
section).
When 'user' is qualified with a table alias, it becomes a column
identifier. That probably explains why you would see the different results.
In postgres, using 'user' alone would also produce the same behavior on
Drill.
mydb=# select * from dept2;
dept_id | dept_name
---------+-----------
1 | ABC
| EFG
(2 rows)
mydb=# select user, * from dept2;
current_user | dept_id | dept_name
--------------+---------+-----------
postgres | 1 | ABC
postgres | | EFG
(2 rows)
On Tue, Jun 30, 2015 at 1:32 PM, Andy Pernsteiner <[email protected]
> wrote:
> 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
> >
>
