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? >
