You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by st...@thomsonreuters.com on 2015/09/04 21:06:58 UTC
query hbase table with json in column?
Hello,
I¹ve got an hbase table with some json data in one of the columns. I¹m
able to query the table and get the column but I¹m having trouble drilling
into the json document in the column. Is it possible to do it?
select CONVERT_FROM(my_table.my_column.tweet, ŒUTF8¹) as tweet from
`hbase`.`my_table` as my_table
Returns:
tweet:
{"created_at":"Fri Oct 31 11:12:28 +0000 2014², ³text²:²hello², Š}
select T.tweet.text from (select CONVERT_FROM(my_table.my_column.tweet,
ŒUTF8¹) as tweet from `hbase`.`my_table` as my_table) as T
Returns:
EXPR$0:
null
Is it possible to do what I¹m attempting or do I need to redefine the
table?
Thanks for any tips,
Steve
Re: query hbase table with json in column?
Posted by Jacques Nadeau <ja...@dremio.com>.
He Steve,
This is most likely due to the fact that you have a field in your data that
is changing from a map type to a boolean type. For example:
record0: {name: {first: jacques, last: nadeau}}
record1: {name: false}
In this example the name column is changing type. The current master of
Drill doesn't yet support column redefinition. We're working on that right
now. If you can share some example data, we can see where we are hitting
this problem.
thanks,
Jacques
--
Jacques Nadeau
CTO and Co-Founder, Dremio
On Fri, Sep 4, 2015 at 1:14 PM, <st...@thomsonreuters.com>
wrote:
> Hey - thanks for the very quick reply!
>
> I tried out the CONVERT_FROM JSON:
>
> Select CONVERT_FROM(my_table.my_column.tweet, ‘JSON’) as tweet from
> `hbase`.`my_table` as my_table
>
> But I’m getting a new issue:
>
> org.apache.drill.common.exceptions.UserRemoteException: SYSTEM ERROR:
> java.lang.IllegalArgumentException: You tried to write a Bit type when you
> are using a ValueWriter of type SingleMapWriter. Fragment 1:0
>
>
>
> Any idea what I might look at to diagnose?
>
> -Steve
>
> On 9/4/15, 3:14 PM, "Jacques Nadeau" <ja...@dremio.com> wrote:
>
> >You should use CONVERT_FROM(my_table.my_column.tweet, 'JSON'). Your data
> >is expected to be UTF8 encoded JSON. Your email was slightly corrupted but
> >it looks like you had 'UTF8' instead of 'JSON'.
> >
> >--
> >Jacques Nadeau
> >CTO and Co-Founder, Dremio
> >
> >On Fri, Sep 4, 2015 at 12:06 PM, <st...@thomsonreuters.com>
> >wrote:
> >
> >> Hello,
> >>
> >> I¹ve got an hbase table with some json data in one of the columns. I¹m
> >> able to query the table and get the column but I¹m having trouble
> >>drilling
> >> into the json document in the column. Is it possible to do it?
> >>
> >>
> >> select CONVERT_FROM(my_table.my_column.tweet, ŒUTF8¹) as tweet from
> >> `hbase`.`my_table` as my_table
> >>
> >>
> >> Returns:
> >> tweet:
> >> {"created_at":"Fri Oct 31 11:12:28 +0000 2014², ³text²:²hello², Š}
> >>
> >>
> >>
> >> select T.tweet.text from (select CONVERT_FROM(my_table.my_column.tweet,
> >> ŒUTF8¹) as tweet from `hbase`.`my_table` as my_table) as T
> >>
> >> Returns:
> >> EXPR$0:
> >> null
> >>
> >> Is it possible to do what I¹m attempting or do I need to redefine the
> >> table?
> >>
> >> Thanks for any tips,
> >> Steve
> >>
> >>
>
>
Re: query hbase table with json in column?
Posted by st...@thomsonreuters.com.
Hey - thanks for the very quick reply!
I tried out the CONVERT_FROM JSON:
Select CONVERT_FROM(my_table.my_column.tweet, ‘JSON’) as tweet from
`hbase`.`my_table` as my_table
But I’m getting a new issue:
org.apache.drill.common.exceptions.UserRemoteException: SYSTEM ERROR:
java.lang.IllegalArgumentException: You tried to write a Bit type when you
are using a ValueWriter of type SingleMapWriter. Fragment 1:0
Any idea what I might look at to diagnose?
-Steve
On 9/4/15, 3:14 PM, "Jacques Nadeau" <ja...@dremio.com> wrote:
>You should use CONVERT_FROM(my_table.my_column.tweet, 'JSON'). Your data
>is expected to be UTF8 encoded JSON. Your email was slightly corrupted but
>it looks like you had 'UTF8' instead of 'JSON'.
>
>--
>Jacques Nadeau
>CTO and Co-Founder, Dremio
>
>On Fri, Sep 4, 2015 at 12:06 PM, <st...@thomsonreuters.com>
>wrote:
>
>> Hello,
>>
>> I¹ve got an hbase table with some json data in one of the columns. I¹m
>> able to query the table and get the column but I¹m having trouble
>>drilling
>> into the json document in the column. Is it possible to do it?
>>
>>
>> select CONVERT_FROM(my_table.my_column.tweet, ŒUTF8¹) as tweet from
>> `hbase`.`my_table` as my_table
>>
>>
>> Returns:
>> tweet:
>> {"created_at":"Fri Oct 31 11:12:28 +0000 2014², ³text²:²hello², Š}
>>
>>
>>
>> select T.tweet.text from (select CONVERT_FROM(my_table.my_column.tweet,
>> ŒUTF8¹) as tweet from `hbase`.`my_table` as my_table) as T
>>
>> Returns:
>> EXPR$0:
>> null
>>
>> Is it possible to do what I¹m attempting or do I need to redefine the
>> table?
>>
>> Thanks for any tips,
>> Steve
>>
>>
Re: query hbase table with json in column?
Posted by Jacques Nadeau <ja...@dremio.com>.
You should use CONVERT_FROM(my_table.my_column.tweet, 'JSON'). Your data
is expected to be UTF8 encoded JSON. Your email was slightly corrupted but
it looks like you had 'UTF8' instead of 'JSON'.
--
Jacques Nadeau
CTO and Co-Founder, Dremio
On Fri, Sep 4, 2015 at 12:06 PM, <st...@thomsonreuters.com>
wrote:
> Hello,
>
> I¹ve got an hbase table with some json data in one of the columns. I¹m
> able to query the table and get the column but I¹m having trouble drilling
> into the json document in the column. Is it possible to do it?
>
>
> select CONVERT_FROM(my_table.my_column.tweet, ŒUTF8¹) as tweet from
> `hbase`.`my_table` as my_table
>
>
> Returns:
> tweet:
> {"created_at":"Fri Oct 31 11:12:28 +0000 2014², ³text²:²hello², Š}
>
>
>
> select T.tweet.text from (select CONVERT_FROM(my_table.my_column.tweet,
> ŒUTF8¹) as tweet from `hbase`.`my_table` as my_table) as T
>
> Returns:
> EXPR$0:
> null
>
> Is it possible to do what I¹m attempting or do I need to redefine the
> table?
>
> Thanks for any tips,
> Steve
>
>