HBase Between Filters

[sanky999]

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 :(

[Yifeng Jiang-2]

Can you try adding this to your code:
scan.addColumn(Bytes.toBytes("TRAN"), Bytes.toBytes("TRAN_ID"))

-Yifeng

On May 4, 2012, at 10:04 PM, sanky999 wrote:

> 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 :(
>
> --
> View this message in context: http://apache-hbase.679495.n3.nabble.com/HBase-Between-Filters-tp3962242.html
> Sent from the HBase - Developer mailing list archive at Nabble.com.

[bijieshan]

I think BinaryComparator will not give any help to achieve that goal(Because it's not a number comparison).
You can try to customize your own comparator(Extend the class of WritableByteArrayComparable), and write the rules of how to do that comparison. And then using this comparator to do the scanning.

Jieshan

-----Original Message-----
From: Yifeng Jiang [mailto:[hidden email]]
Sent: Monday, May 07, 2012 7:37 PM
To: [hidden email]
Subject: Re: HBase Between Filters

Can you try adding this to your code:
scan.addColumn(Bytes.toBytes("TRAN"), Bytes.toBytes("TRAN_ID"))

-Yifeng

On May 4, 2012, at 10:04 PM, sanky999 wrote:

> 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 :(
>
> --
> View this message in context: http://apache-hbase.679495.n3.nabble.com/HBase-Between-Filters-tp3962242.html
> Sent from the HBase - Developer mailing list archive at Nabble.com.

[sanky999]

@Yifeng - Nope no success. I tried both ways:
BinaryComparator as well as directly passing bytes none worked.
It still picks up record 120, 117... when given range is 1000-2000.

[bijieshan]

I just raised one issue regarding on this: HBASE-5950

-----Original Message-----
From: sanky999 [mailto:[hidden email]]
Sent: Monday, May 07, 2012 9:24 PM
To: [hidden email]
Subject: Re: HBase Between Filters

@Yifeng - Nope no success. I tried both ways:
BinaryComparator as well as directly passing bytes none worked.
It still picks up record 120, 117... when given range is 1000-2000.

--
View this message in context: http://apache-hbase.679495.n3.nabble.com/HBase-Between-Filters-tp3962242p3968518.html
Sent from the HBase - Developer mailing list archive at Nabble.com.

[sanky999]

When  using 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.

[Anoop Sam John]

@sanky

"It still picks up record 120, 117... when given range is 1000-2000."

How you have saved the int data value into HBase column?  Have you not used Bytes.toBytes(int) ??   If you have used the same,  the BinaryComparator would have worked fine I guess..
________________________________________
From: Bijieshan [[hidden email]]
Sent: Monday, May 07, 2012 8:31 PM
To: [hidden email]; [hidden email]
Cc: Chenjian
Subject: RE: HBase Between Filters

I just raised one issue regarding on this: HBASE-5950

-----Original Message-----
From: sanky999 [mailto:[hidden email]]
Sent: Monday, May 07, 2012 9:24 PM
To: [hidden email]
Subject: Re: HBase Between Filters

@Yifeng - Nope no success. I tried both ways:
BinaryComparator as well as directly passing bytes none worked.
It still picks up record 120, 117... when given range is 1000-2000.

--
View this message in context: http://apache-hbase.679495.n3.nabble.com/HBase-Between-Filters-tp3962242p3968518.html
Sent from the HBase - Developer mailing list archive at Nabble.com.

[sanky999]

@Anoop - Tried your suggestions too, no success. I mean stored values as bytes but int format now \x00\x00\x00\x17 but while filtering it goofs up and picks up incorrect data.

[Ramkrishna.S.Vasudevan]

Just adding on to Anoop's reply
    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)));

If you had inserted the value as Integer and then retrieve it as integer
using Bytes.toBytes then you can get the intended result.

Regards
Ram

> -----Original Message-----
> From: Anoop Sam John [mailto:[hidden email]]
> Sent: Tuesday, May 08, 2012 4:04 PM
> To: [hidden email]; [hidden email]
> Subject: RE: HBase Between Filters
>
> @sanky
>
> "It still picks up record 120, 117... when given range is 1000-2000."
>
> How you have saved the int data value into HBase column?  Have you not
> used Bytes.toBytes(int) ??   If you have used the same,  the
> BinaryComparator would have worked fine I guess..
> ________________________________________
> From: Bijieshan [[hidden email]]
> Sent: Monday, May 07, 2012 8:31 PM
> To: [hidden email]; [hidden email]
> Cc: Chenjian
> Subject: RE: HBase Between Filters
>
> I just raised one issue regarding on this: HBASE-5950
>
> -----Original Message-----
> From: sanky999 [mailto:[hidden email]]
> Sent: Monday, May 07, 2012 9:24 PM
> To: [hidden email]
> Subject: Re: HBase Between Filters
>
> @Yifeng - Nope no success. I tried both ways:
> BinaryComparator as well as directly passing bytes none worked.
> It still picks up record 120, 117... when given range is 1000-2000.
>
> --
> View this message in context: http://apache-
> hbase.679495.n3.nabble.com/HBase-Between-Filters-tp3962242p3968518.html
> Sent from the HBase - Developer mailing list archive at Nabble.com.

[sanky999]

@RamKrishna - I have inserted records as integer but when I go for filtering them using filters it still gives wrong data, I just apply filters as mentioned that's how the records should be picked, but the filter goes for toss.

[Anoop Sam John]

@Sanky

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

Are you sure you have used Bytes.toBytes(int) for storing?  Here when you created the filter atleast I can see you passed 1000 as String to create the bytes. Pls make sure to use int and create the bytes both while storing the [Creation of Put] and when u create the filter and see once....  I have checked the BinaryComparator and it should work as per your expectation if you have used int in all places....

Bytes.toBytes(int) to be used every where when your column type is int...

-Anoop-
________________________________________
From: sanky999 [[hidden email]]
Sent: Tuesday, May 08, 2012 12:16 PM
To: [hidden email]
Subject: RE: HBase Between Filters

When  using 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.

--
View this message in context: http://apache-hbase.679495.n3.nabble.com/HBase-Between-Filters-tp3962242p3970550.html
Sent from the HBase - Developer mailing list archive at Nabble.com.

[Ramkrishna.S.Vasudevan]

In my mail I had actually used int rather than String as how you had used
inside the Filter
    SingleColumnValueFilter filter1 = new
SingleColumnValueFilter(Bytes.toBytes("TRAN"),
              Bytes.toBytes("TRAN_ID"),
              CompareFilter.CompareOp.GREATER, new
BinaryComparator(Bytes.toBytes(1000)));

There is no '"' in the Bytes.toBytes

Regards
Ram

> -----Original Message-----
> From: sanky999 [mailto:[hidden email]]
> Sent: Tuesday, May 08, 2012 6:15 PM
> To: [hidden email]
> Subject: RE: HBase Between Filters
>
> @RamKrishna - I have inserted records as integer but when I go for
> filtering
> them using filters it still gives wrong data, I just apply filters as
> mentioned that's how the records should be picked, but the filter goes
> for
> toss.
>
> --
> View this message in context: http://apache-
> hbase.679495.n3.nabble.com/HBase-Between-Filters-tp3962242p3971220.html
> Sent from the HBase - Developer mailing list archive at Nabble.com.

[sanky999]

@Anoop - thanks I got it working now.