Link correction: http://apache.github.io/drill/docs/json-data-model#handling-type-differences
Kristine Hahn Sr. Technical Writer 415-497-8107 @krishahn On Fri, Apr 3, 2015 at 6:57 AM, Vince Gonzalez <[email protected]> wrote: > Yep: > > 0: jdbc:drill:zk=local> select jblob['v'] from (select > convert_from(columns[1], 'JSON') as jblob from `AncestrySample100.tsv`) > limit 1; > +------------+ > | EXPR$0 | > +------------+ > | 3.0 | > +------------+ > 1 row selected (0.136 seconds) > > Thanks Carol! > > > On Fri, Apr 3, 2015 at 9:28 AM, Carol McDonald <[email protected]> > wrote: > > > maybe something like > > > > select convert_from(t.columns[1], 'JSON') from AncestrySample100.tsv t > > > > On Fri, Apr 3, 2015 at 9:06 AM, Vince Gonzalez <[email protected] > > > > wrote: > > > > > Can I tell Drill to parse the JSON in a column of a TSV? > > > > > > cd /tmp > > > curl -L --output AncestrySample100.tsv > > > > > > > > > https://raw.githubusercontent.com/ThinkBigAnalytics/ThinkBigChallenge2014/master/data/AncestrySample100 > > > > > > ... > > > > > > 0: jdbc:drill:zk=local> use dfs.tmp; > > > 0: jdbc:drill:zk=local> select columns[1] from `AncestrySample100.tsv` > > > limit 1; > > > +------------+ > > > | EXPR$0 | > > > +------------+ > > > | {"v":"3.0","Persons":{"Genders":[{"id":"2168621643","q":381,<snip> > > > +------------+ > > > 1 row selected (0.135 seconds) > > > 0: jdbc:drill:zk=local> select columns[1]['v'] from > > `AncestrySample100.tsv` > > > limit 1; > > > +------------+ > > > | EXPR$0 | > > > +------------+ > > > | null | > > > +------------+ > > > 1 row selected (0.117 seconds) > > > > > > The query that attempts to look inside the JSON blob in columns[1] > > returns > > > null for all rows, presumably because drill doesn't try to parse JSON > at > > > all given the .tsv extension? > > > > > >
