I would have a big +1 to a community repo of Drill UDFs. John, How do you think the best way to host that would be? — C
> On May 25, 2016, at 12:55, John Omernik <[email protected]> wrote: > > 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)* >>
