How to query array in Hbase

[GameboyNO1 <73...@qq.com>]

Hi,


I put an array in hbase column (now in Json string, but is not necessary if drill requires the change), is it possible I can query whether an item is in the array?


For example, I put string "["192.168.0.1","192.168.0.2",...]" in a hbase column, and want to query out rows contains 192.168.0.1 in the array.


Thanks!


Alfie

Re: How to query array in Hbase

[GameboyNO1 <73...@qq.com>]

Hi Neeraja,

Thanks for the guide!

Some strange error I see here.

The first two queries report the same error, but the third one works. 

The only difference between #2 and #3 is "LIMIT X". And it does not matter what's the number of X, it can be larger than row number of the table, and the query still works.

#1

> SELECT * FROM hbase.`test_table` t WHERE REPEATED_CONTAINS(CONVERT_FROM(COALESCE(t.c.x, ''), 'JSON'), '192.168.0.1') = TRUE;

Error: SYSTEM ERROR: SchemaChangeException: Failure while trying to materialize incoming schema.  Errors:

Error in expression at index 4.  Error: Only ProjectRecordBatch could have complex writer function. You are using complex writer fun a non-project operation!.  Full expression: null.

Error in expression at index 2.  Error: Missing function implementation: [repeated_contains(LATE-OPTIONAL, VARCHAR-REQUIRED)].  Full

Fragment 1:1

#2

> SELECT  * FROM (SELECT CONVERT_FROM(COALESCE(t.c.x, ''), 'JSON') x FROM hbase.`test_table` t) WHERE REPEATED_CONTAINS(x, '192.168.0.1') = TRUE;

Error: SYSTEM ERROR: SchemaChangeException: Failure while trying to materialize incoming schema.  Errors:

Error in expression at index 18.  Error: Only ProjectRecordBatch could have complex writer function. You are using complex writer function convert_fromjson in a non-project operation!.  Full expression: null.

Error in expression at index 16.  Error: Missing function implementation: [repeated_contains(LATE-OPTIONAL, VARCHAR-REQUIRED)].  Full expression: null.. 

Fragment 1:1

#3

> SELECT * FROM (SELECT CONVERT_FROM(COALESCE(t.c.x, ''), 'JSON') x FROM hbase.`test_table` t LIMIT 3) WHERE REPEATED_CONTAINS(x, '192.168.0.1') = TRUE;

Alfie



------------------ Original ------------------
From: Neeraja Rentachintala <nr...@maprtech.com>
Date: 周二,7月 5,2016 0:43 下午
To: user@drill.apache.org <us...@drill.apache.org>
Subject: Re: How to query array in Hbase



You can use Convert_to/Convert_from functions with JSON encoding if it is
JSON data.

Once you can get hold of the JSON string, you can use repeated_contains to
check for the existence of an element.

On Sun, Jul 3, 2016 at 9:47 PM, GameboyNO1 <73...@qq.com> wrote:

> Hi,
>
>
> I put an array in hbase column (now in Json string, but is not necessary
> if drill requires the change), is it possible I can query whether an item
> is in the array?
>
>
> For example, I put string "["192.168.0.1","192.168.0.2",...]" in a hbase
> column, and want to query out rows contains 192.168.0.1 in the array.
>
>
> Thanks!
>
>
> Alfie

Re: How to query array in Hbase

[Neeraja Rentachintala <nr...@maprtech.com>]

You can use Convert_to/Convert_from functions with JSON encoding if it is
JSON data.

Once you can get hold of the JSON string, you can use repeated_contains to
check for the existence of an element.

On Sun, Jul 3, 2016 at 9:47 PM, GameboyNO1 <73...@qq.com> wrote:

> Hi,
>
>
> I put an array in hbase column (now in Json string, but is not necessary
> if drill requires the change), is it possible I can query whether an item
> is in the array?
>
>
> For example, I put string "["192.168.0.1","192.168.0.2",...]" in a hbase
> column, and want to query out rows contains 192.168.0.1 in the array.
>
>
> Thanks!
>
>
> Alfie