CODEWITHSUNDEEP
hadoophbase
Monica Shiralkar
Monica Shiralkar

Reputation: 293

Unable to fetch data from Hbase based on query parameters

How to get data from HBase? I have a table with empId, name, startDate, endDate and other columns. Now I want to get data from an HBase table based upon empId, startDate and endDate.In normal SQL I can use: 

select * from tableName where empId=val and date>=startDate and date<=endDate

How can I do this in HBase as it stores data as key value pairs? The key is empId.

Upvotes: 1

Views: 667

Answers (1)

Lorand Bendig
Lorand Bendig

Reputation: 10650

Getting filtered rows in HBase shell is tricky. Since the shell is JRuby-based you can have here Ruby commands as well:

import org.apache.hadoop.hbase.filter.CompareFilter
import org.apache.hadoop.hbase.filter.SingleColumnValueFilter
import org.apache.hadoop.hbase.filter.BinaryComparator
import org.apache.hadoop.hbase.filter.FilterList
import java.text.SimpleDateFormat
import java.lang.Long

def dateToBytes(val)
  Long.toString(
    SimpleDateFormat.new("yyyy/MM/dd").parse(val).getTime()).to_java_bytes
end

# table properties
colfam='c'.to_java_bytes;
col_name='name';
col_start='startDate';
col_end='endDate';

# query params
q_name='name2';
q_start='2012/08/14';
q_end='2012/08/24';

# filters
f_name=SingleColumnValueFilter.new(
         colfam, col_name.to_java_bytes, 
         CompareFilter::CompareOp::EQUAL, 
         BinaryComparator.new(q_name.to_java_bytes));

f_start=SingleColumnValueFilter.new(
          colfam, col_start.to_java_bytes, 
          CompareFilter::CompareOp::GREATER_OR_EQUAL,
          BinaryComparator.new(dateToBytes(q_start)));

f_end=SingleColumnValueFilter.new(
          colfam, col_end.to_java_bytes, 
          CompareFilter::CompareOp::LESS_OR_EQUAL,
          BinaryComparator.new(dateToBytes(q_end)));

filterlist= FilterList.new([f_name, f_start, f_end]);

# get the result
scan 'mytable', {"FILTER"=>filterlist}

Similarly in Java construct a FilterList :

// Query params
String nameParam = "name2";
String startDateParam = "2012/08/14";
String endDateParam = "2012/08/24";

Filter nameFilter = 
  new SingleColumnValueFilter(colFam, nameQual, CompareOp.EQUAL,
        Bytes.toBytes(nameParam));

//getBytesFromDate(): parses startDateParam and create a byte array out of it
Filter startDateFilter = 
  new SingleColumnValueFilter(colFam, startDateQual,
        CompareOp.GREATER_OR_EQUAL, getBytesFromDate(startDateParam));

Filter endDateFilter = 
  new SingleColumnValueFilter(colFam, endDateQual,
        CompareOp.LESS_OR_EQUAL, getBytesFromDate(endDateParam));

FilterList filters = new FilterList();
filters.addFilter(nameFilter);
filters.addFilter(startDateFilter);
filters.addFilter(endDateFilter);

HTable htable = new HTable(conf, tableName);

Scan scan = new Scan();
scan.setFilter(filters);
ResultScanner rs = htable.getScanner(scan);
//process your result...

Upvotes: 2

Related Questions