On a side note, convert_from is used to convert a set of bytes encoded in a given format to a Drill type (mostly it is used while reading from HBase). You shouldn't have to use this with JSON because Drill knows how to read JSON and convert it to a Drill type. However you may have to use cast to go from one Drill type to another.
Thanks Mehant On Thu, Jul 30, 2015 at 3:48 PM, Aman Sinha <[email protected]> wrote: > Hi John, > you cannot use aliases in the WHERE condition. Drill is not unique in this > restriction...since the WHERE condition is evaluated before the alias is > done in the SELECT clause. Did you try WHERE t.app.hcc.event_name IN > ('logout') ? > > Aman > > On Thu, Jul 30, 2015 at 3:42 PM, John Schneider <[email protected]> > wrote: > > > select t.app.hcc.event_name as en > > from dfs.`user`.`/logmaster/production/hcc/2015-07-30/*.json` t > > where en in ('logout'); > > > > this yields the error: > > Error: SYSTEM ERROR: NumberFormatException: logout > > > > ok, so let's explicitly cast > > > > select cast(convert_from(t.app.hcc.event_name, 'UTF8') as varchar(30)) as > > en > > from dfs.`user`.`/logmaster/production/hcc/2015-07-30/*.json` t > > where en in ('logout'); > > > > now, just to humor drill > > > > select cast(convert_from(t.app.hcc.event_name, 'UTF8') as varchar(30)) as > > en > > from dfs.`user`.`/logmaster/production/hcc/2015-07-30/*.json` t > > where en in ('123'); > > > > runs, but returns no results - as would be expected because we don't use > #s > > as event names > > > > Am I misunderstanding how drill types data in a schema less record? > > I would have thought the explicit cast would have been enough > > > > > > P.S. I ran another query like this one on a months worth of logs (a lot > of > > json in HDFS) and it chewed through it in less time than it takes my > > current Hive query to actually start, and all of this on a single aws > > m3.xlarge - this drill sucker is fast, we really want to use it. > > > > john o schneider > > [email protected] > > 408-203-7891 > > >
