Hbase multiple ValueFilter's to filter multiple columns of the same row

Question

I would like to achieve something like this SQL query using the HBase API

SELECT * FROM customer_table WHERE firstname = "Joe" AND lastname = "Bloggs" AND email = "[email protected]"

HBase Table:

1                column=p:firstname, timestamp=<t>, value=Joe                                                                         
1                column=p:lastname, timestamp=<t>, value=Bloggs                                                                            
1                column=p:email, timestamp=<t>, [email protected]                                                                            
2                column=p:firstname, timestamp=<t>, value=Joe                                                                         
2                column=p:lastname, timestamp=<t>, value=Bloggs                                                                            
2                column=p:email, timestamp=<t>, [email protected]
3                column=p:firstname, timestamp=<t>, value=Joe                                                                         
3                column=p:lastname, timestamp=<t>, value=Bloggs                                                                            
3                column=p:email, timestamp=<t>, [email protected]

Currently I have this:

val filters = Array("Joe", "Bloggs", "[email protected]")

// AND operator
val filterList = new FilterList(FilterList.Operator.MUST_PASS_ALL) 

filters.foreach(f => {
  filterList.addFilter(new ValueFilter(CompareOp.EQUAL, new BinaryComparator(Bytes.toBytes(f))))
})

val scan = new Scan().setFilter(filterList)
val resultScanner = table.getScanner(scan)

But, this returns no results. I would expect it to return all 3 rows. Is there another filter/function to achieve this?

bp2010 · Answer 1 · 2018-02-05T15:03:11.593

This answer helped me. I needed to use a SingleColumnValueFilter instead of the ValueFilter, because it is an AND operation, you need to create a column value filter for each field you need to filter on, which SingleColumnValueFilter does nicely.

val filterMap = Map("firstname" -> "Joe", "lastname" -> "Bloggs", "email" -> "[email protected]")

// AND operator
val filterList = new FilterList(FilterList.Operator.MUST_PASS_ALL) 

filterMap.foreach(kv => {
  filterList.addFilter(new SingleColumnValueFilter
    (
      Bytes.toBytes(columnFamily),
      Bytes.toBytes(kv._1),
      CompareOp.EQUAL,
      new BinaryComparator(Bytes.toBytes(kv._2))
    )
  )
})

val scan = new Scan().setFilter(filterList)
val resultScanner = table.getScanner(scan)

This returns the 3 rows as expected.

Hbase multiple ValueFilter's to filter multiple columns of the same row

1 Answers1