How to filter columns in HBase Shell
Tags: database, nosql, bigdata
Published: 2022-07-08 20:27:59



One of the most useful functions available in HBase are filters. In this article, I will show you how to use filters to get lookup columns with specific values. Every example is shown in Hbase Shell, but they are also available in the API, so you can use it in your ETL application as well.

First, let's check which filters are available for us from Hbase Shell. To do this use `show_filters ` command in Hbase Shell:


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xb6g0cxte4bs00hpelhh.gif)


As you can see there are multiple filters available but we are focusing on Column Filters, so 3 that are useful for us are ColumnValueFilter, ValueFilter, and SingleColumnValueFilters. All of these filters have very similar properties and all of them can be used for filtering columns.

Let's assume we have a table named myTable with the following contents.

| Rowkey       | myColumnFamily:columnA | myColumnFamily:columnB |
|--------------|------------------------|------------------------|
| row1         | value1                 |                        |
| row2         | value2                 |                        |
| row3         | value3                 | valueX                 |
| row3_special | value4                 |                        |


```
hbase(main):003:0> scan 'myTable'
ROW                             COLUMN+CELL
 row1                           column=myColumnFamily:columnA, timestamp=1656785481871, value=value1
 row2                           column=myColumnFamily:columnA, timestamp=1656785500397, value=value2
 row3                           column=myColumnFamily:columnA, timestamp=1656785504786, value=value3
 special_row3                   column=myColumnFamily:columnA, timestamp=1656785539020, value=value4

```

## ColumnValueFilter

The first option to filter values is to use ColumnValueFilter. 
As an argument we pass column family and column name in which we want to search, and 
filtering argument in this example we search for a value that equals value1.

```
scan 'myTable', {FILTER => "ColumnValueFilter('myColumnFamily','columnA',=,'binary:value1')"}
```

ColumnValueFilter will return only columns that match our filtering statement.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nq3e9rw8eqsxpbu5ksvz.gif)

```
hbase(main):010:0> scan 'myTable', {FILTER => "ColumnValueFilter('myColumnFamily','columnA',=,'binary:value1')"}
ROW                             COLUMN+CELL\n
 row1                           column=myColumnFamily:columnA,\n timestamp=1656785481871, value=value1\n
1 row(s)\n
Took 0.0188 seconds\n
hbase(main):011:0>
```

## SingleColumnValueFilter

SingleColumnValueFilter returns an entire row when specified condition is matched. Besides that it works exactly like ColumnValueFilter.

This example also shows another form of filtering comparison - when only the substring of the value matches it returns this value.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4741qhty6bhmq6fwr9mw.gif)

```
hbase(main):022:0> scan 'myTable', {FILTER => "SingleColumnValueFilter('myColumnFamily','columnA',=,'substring:value')"}

ROW                             COLUMN+CELL\n
 row1                           column=myColumnFamily:columnA, timestamp=1656785481871, value=value1\n
 row1                           column=myColumnFamily:columnB, timestamp=1656802766312, value=valueX\n
 row2                           column=myColumnFamily:columnA, timestamp=1656785500397, value=value2\n
 row3                           column=myColumnFamily:columnA, timestamp=1656785504786, value=value3\n
 special_row3                   column=myColumnFamily:columnA, timestamp=1656785539020, value=value4\n
4 row(s)\n
Took 0.0253 seconds\n
hbase(main):023:0> scan 'myTable', {FILTER => "SingleColumnValueFilter('myColumnFamily','columnA',=,'substring:X')"}
ROW                             COLUMN+CELL
0 row(s)
Took 0.0047 seconds
```

⚠️**WARNING** ⚠️
If the column is not found every column from searched row will be filtered



## ValueFilter

In ValueFilter we don't provide the column family and column name, just the filter statement. Which makes it search in every column possible.  ValueFilter return only rows that match statement.

From ValueFilter documentation:
> To test the value of a single qualifier when scanning multiple qualifiers, use link SingleColumnValueFilter


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/l55ldh5mca4m9e5rw7yu.gif)

```
hbase(main):004:0> scan 'myTable', {FILTER => "ValueFilter(=,'binary:valueX')"}
ROW                                   COLUMN+CELL
 row1                                 column=myColumnFamily:columnB, timestamp=1656802766312, value=valueX
1 row(s)
Took 0.0106 seconds

```


## Conclusion

- ValueFilters or specific column values in every column
- SingleValueColumnFilter returns only matched row in a specified column
- ColumnValueFilter returns every row in case it matched the row in a specified column