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 <[email protected]> Date: ????,7?? 5,2016 0:43 ???? To: [email protected] <[email protected]> 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 <[email protected]> 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
