drill-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jinfeng Ni <...@apache.org>
Subject Re: TSV with a JSON column?
Date Fri, 03 Apr 2015 14:07:17 GMT
That's right.  columns[1] from tsv file is regarded as a VARCHAR column.
You have to use convert_from function.

0: jdbc:drill:zk=local> select t.colA.v from (select
convert_from(columns[1], 'JSON') from `AncestrySample100.tsv` limit 1) as
t(colA);
+------------+
|   EXPR$0   |
+------------+
| 3.0        |
+------------+
1 row selected (0.2 seconds)



On Fri, Apr 3, 2015 at 6:28 AM, Carol McDonald <cmcdonald@maprtech.com>
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 <vince.gonzalez@gmail.com>
> 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?
> >
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message