HBase "between" Filters

Question

I'm trying to retrieve rows with in range, using Filter List but I'm not successful. Below is my code snippet.

I want to retrieve data between 1000 and 2000.

HTable table = new HTable(conf, "TRAN_DATA");

    List<Filter> filters = new ArrayList<Filter>();

    SingleColumnValueFilter filter1 = new SingleColumnValueFilter(Bytes.toBytes("TRAN"),
              Bytes.toBytes("TRAN_ID"),
              CompareFilter.CompareOp.GREATER, new BinaryComparator(Bytes.toBytes("1000")));
    filter1.setFilterIfMissing(true);
    filters.add(filter1);

    SingleColumnValueFilter filter2 = new SingleColumnValueFilter(Bytes.toBytes("TRAN"),
              Bytes.toBytes("TRAN_ID"),
              CompareFilter.CompareOp.LESS,new BinaryComparator(Bytes.toBytes("2000")));

    filters.add(filter2);

    FilterList filterList = new FilterList(filters);

    Scan scan = new Scan();
    scan.setFilter(filterList);
    ResultScanner scanner1 = table.getScanner(scan);

    System.out.println("Results of scan #1 - MUST_PASS_ALL:");
    int n = 0;

    for (Result result : scanner1) {
        for (KeyValue kv : result.raw()) {
            System.out.println("KV: " + kv + ", Value: "
                    + Bytes.toString(kv.getValue()));
        {
            n++;

        }
    }
    scanner1.close();

Tried with all possible ways using
1. SingleColumnValueFilter filter2 = new SingleColumnValueFilter(Bytes.toBytes("TRANSACTIONS"), Bytes.toBytes("TRANS_ID"), CompareFilter.CompareOp.LESS, new SubstringComparator("5000"));

SingleColumnValueFilter filter2 = new SingleColumnValueFilter(Bytes.toBytes("TRANSACTIONS"), Bytes.toBytes("TRANS_ID"), CompareFilter.CompareOp.LESS, Bytes.toBytes("5000")); None of above approaches work :(

I'm not getting correct results, not sure if there is another way of implementing the same, as most of filters in hbase are row filters, how to add filters on specific columns to retrieve data — shah sanket, May 03 '12 at 11:35
Are you storing the keys as byte values of the string or bytes value of Int/Long? — Arnon Rotem-Gal-Oz, May 03 '12 at 15:51
@Arnon - Everything I'm storing as bytes,using HBase Put Api to call add method to store values which only allows bytes[] to be set against a column, is there other way to store Int/Long values — shah sanket, May 07 '12 at 10:52
Everything is bytes in HBase but numbers can be stored as characters or values e.g. one can be stored as the character "1" or as the byte 0x01 (like hbase does with Increment qualifiers) — Arnon Rotem-Gal-Oz, May 07 '12 at 12:23
@Arnon yes I'm aware of it, but if you see, org.apache.hadoop.hbase.client.Increment api it allows to addColumn with Long value, but there is no way to do the same using org.apache.hadoop.hbase.client.Put api and when I tried adding Long values using Put api it stores in\x0 format but when I try to retrieve it, it prints as unknown character. — shah sanket, May 08 '12 at 06:45
@shahsanket what have you gotten the solution for your above problem? — Rahul, Mar 31 '14 at 07:05

score 2 · Answer 1 · answered May 03 '12 at 18:45

One thing which is certainly off here is when creating the FILTERLIST you also have to specify FilterList.Operator, otherwise not sure how filterlist would handle multiple filters. In your case it should be something like :-

FilterList filterList = new FilterList(FilterList.Operator.MUST_PASS_ALL, filters);

See if this helps.

score 0 · Answer 2 · answered May 03 '12 at 12:34

0

Looks OK. Check if you have persisted them as "1000" and "2000" not as byte[] for 1000 and 2000.

Rest looks fine to me.

answered May 03 '12 at 12:34

vivek mishra

1,162
8
16

Tried with all possible ways using 1. new SingleColumnValueFilter(Bytes.toBytes("TRANSACTIONS"), Bytes.toBytes("TRANS_ID"), CompareFilter.CompareOp.LESS, new BinaryComparator(Bytes.toBytes("5000"))); – shah sanket May 03 '12 at 13:20

score 0 · Answer 3 · answered Apr 02 '14 at 16:28

1 FilterList default operator is Operator.MUST_PASS_ALL. It's ok of your code about this.
2 If you put the string as bytes in hbase, your choice is wrong.
because "1000" < "2" < "5000"

    Put put = new Put(rowKey_ForTest);
    put.add(ColumnFamilyName, QName1, Bytes.toBytes("2"));
    table.put(put);

    List<Filter> filters = new ArrayList<Filter>();
    SingleColumnValueFilter filter1 = new SingleColumnValueFilter(
            ColumnFamilyName, QName1, CompareOp.GREATER,
            new BinaryComparator(Bytes.toBytes("1000")));
    filters.add(filter1);

    SingleColumnValueFilter filter2 = new SingleColumnValueFilter(
            ColumnFamilyName, QName1, CompareOp.LESS, new BinaryComparator(
                    Bytes.toBytes("5000")));

    filters.add(filter2);

    FilterList filterList = new FilterList(filters);

    Scan scan = new Scan();
    scan.setFilter(filterList);

    List<String> resultRowKeys = new ArrayList<String>();
    ResultScanner resultScanner = table.getScanner(scan);
    for (Result result = resultScanner.next(); result != null; result = resultScanner
            .next()) {
        resultRowKeys.add(Bytes.toString(result.getRow()));
    }
    Util.close(resultScanner);

    Assert.assertEquals(1, resultRowKeys.size());

3 If you put the int as bytes, your code is wrong. You should use Bytes.toBytes(int), not Bytes.toBytes(String).

My test code is on https://github.com/zhang-xzhi/simplehbase
There is a lot of test about hbase.

Or you can post your put code, so we can check how did you save your data to hbase.
Or you can debug it, check your value's format.

See if it can help.

HBase "between" Filters

3 Answers3

Linked