You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by Vince Gonzalez <vi...@gmail.com> on 2015/04/03 15:06:22 UTC
TSV with a JSON column?
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?
Re: TSV with a JSON column?
Posted by Kristine Hahn <kh...@maprtech.com>.
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 <vi...@gmail.com>
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 <cm...@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?
> > >
> >
>
Re: TSV with a JSON column?
Posted by Vince Gonzalez <vi...@gmail.com>.
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 <cm...@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 <vi...@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?
> >
>
Re: TSV with a JSON column?
Posted by Jinfeng Ni <jn...@apache.org>.
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 <cm...@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 <vi...@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?
> >
>
Re: TSV with a JSON column?
Posted by Carol McDonald <cm...@maprtech.com>.
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 <vi...@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?
>