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"));

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

Answer 1

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

Rest looks fine to me.

Answer 2

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.

Answer 3

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.