Cool, I wasn't aware of SIMILAR to (I learned something) However, that doesn't work because my data is accurate i.e. '____-__-__' 2015-04-02 and 2015-00-23 but 00 doesn't work (bad data) .
UDFs scare me in that the only Java I've conquered is evident from my empty french press... I know I've brought it up in the past, but has anyone seen any community around UDFs start? I'd love to have a community that follows Apache like rules, and allows us to create and track UDFs to share... that would be pretty neat. I guess if we were to do something like that, should one of us (I can volunteer) just start a Github project and encourage folks to come to the table or is there better way via Apache to do something like that? On Wed, May 25, 2016 at 10:27 AM, Veera Naranammalpuram < [email protected]> wrote: > You could write a UDF. Or you could do something like this: > > cat data.csv > 05/25/2016 > 20160525 > May 25th 2016 > > 0: jdbc:drill:> select case when columns[0] similar to '__/__/____' then > to_date(columns[0],'MM/dd/yyyy') when columns[0] similar to '________' then > to_date(columns[0],'yyyyMMdd') else NULL end from `data.csv`; > +-------------+ > | EXPR$0 | > +-------------+ > | 2016-05-25 | > | 2016-05-25 | > | null | > +-------------+ > 3 rows selected (0.4 seconds) > 0: jdbc:drill:> > > -Veera > > On Wed, May 25, 2016 at 11:12 AM, Vince Gonzalez <[email protected]> > wrote: > > > Sounds like a job for a UDF? > > > > You could do the try/catch inside the UDF. > > > > ---- > > Vince Gonzalez > > Systems Engineer > > 212.694.3879 > > > > mapr.com > > > > On Wed, May 25, 2016 at 11:05 AM, John Omernik <[email protected]> wrote: > > > > > I have some DOBs, and some fields are empty others apparently were > filled > > > by trained monkeys, but while most data is accurate, some data is not. > > > > > > As you saw from my other post, I am trying to get the age for those > DOBs > > > that are valid... > > > > > > My function works, until I get to a record that is not valid and I get > > > something like this: > > > > > > Error: SYSTEM ERROR: IllegalFieldValueException: Value 0 for > monthOfYear > > > must be in the range [1,12] > > > > > > > > > Is there a good "Try -> Except" type solution that will grant me the > > valid > > > data if things worked, and just return 0 or whatever I specify if there > > is > > > an error? > > > > > > I could try casting the data, but if it fails won't it kill my query? > > > Basically I want it to keep going if it fails... not sure if Drill has > > this > > > ability, but thought I would ask. > > > > > > > > > -- > Veera Naranammalpuram > Product Specialist - SQL on Hadoop > *MapR Technologies (www.mapr.com <http://www.mapr.com>)* > *(Email) [email protected] <[email protected]>* > *(Mobile) 917 683 8116 - can text * > *Timezone: ET (UTC -5:00 / -4:00)* >
