Secondary Index-based Query

Function

In user tables with secondary indexes, you can use Filter to query data. The data query performance is higher than that in user tables without secondary indexes.

• HIndex supports three Filter types: SingleColumnValueFilter, SingleColumnValueExcludeFilter, and SingleColumnValuePartitionFilter.
• HIndex supports the following Comparator types: binary comparator, bit comparator, long comparator, decimal comparator, double comparator, float comparator, int comparator, and null comparator.

The secondary index usage rules are as follows:

• For scenarios in which a single index is created for one or multiple columns:
  • When you use this column for AND or OR query filtering, the index is used to improve the query performance.

    For example, Filter_Condition(IndexCol1) AND/OR Filter_Condition(IndexCol2).

  • When you use "Index Column AND Non-Index Column" for filtering in the query, this index is used to improve the query performance.

    For example, Filter_Condition(IndexCol1) AND Filter_Condition(IndexCol2) AND Filter_Condition(NonIndexCol1).

  • When you use "Index Column OR Non-Index Column" for filtering in the query, the index is not used and the query performance is not improved.

    For example, Filter_Condition(IndexCol1) AND/OR Filter_Condition(IndexCol2) OR Filter_Condition(NonIndexCol1).

• For scenarios in which a combination index is created for multiple columns:
  • When the columns used for query are all or part of the columns of the combination index and are in the same sequence with the combination index, the index is used to improve the query performance.

    For example, a combination index is created for C1, C2, and C3. The index takes effect in the following scenarios:

    Filter_Condition(IndexCol1) AND Filter_Condition(IndexCol2) AND Filter_Condition(IndexCol3)

    Filter_Condition(IndexCol1) AND Filter_Condition(IndexCol2)

    Filter_Condition(IndexCol1)

    The index does not take effect in the following scenarios:

    Filter_Condition(IndexCol2) AND Filter_Condition(IndexCol3)

    Filter_Condition(IndexCol1) AND Filter_Condition(IndexCol3)

    Filter_Condition(IndexCol2)

    Filter_Condition(IndexCol3)

  • When you use "Index Column AND Non-Index Column" for filtering in the query, this index is used to improve the query performance.

    For example:

    Filter_Condition(IndexCol1) AND Filter_Condition(NonIndexCol1)

    Filter_Condition(IndexCol1) AND Filter_Condition(IndexCol2) AND Filter_Condition(NonIndexCol1)

  • When you use "Index Column OR Non-Index Column" for filtering in the query, the index is not used and the query performance is not improved.

    For example:

    Filter_Condition(IndexCol1) OR Filter_Condition(NonIndexCol1)

    (Filter_Condition(IndexCol1) AND Filter_Condition(IndexCol2))OR ( Filter_Condition(NonIndexCol1))

  • When multiple columns are used for query, a value range can be specified only for the last column in the combination index and the other columns can only be set to a specified value.

    For example, a combination index is created for C1, C2, and C3. In range query, a value range can be set only for C3 and the filter criterion is "C1 = XXX, C2 = XXX, and C3 = value range".

• For scenarios in which secondary index is created in a user table, you can use Filter to query data. The query results of the single and combination index with filter are the same as those in the table without secondary index. The data query performance is higher than that in user tables without secondary indexes.

Example Code

The following code snippet belongs to the testScanDataByIndex method in the HbaseSample class of the com.huawei.hadoop.hbase.example package.

Example: Query data using secondary indexes.

  public void testScanDataByIndex() {
    LOG.info("Entering testScanDataByIndex.");
    Table table = null;
    ResultScanner scanner = null;
    try {
      table = conn.getTable(tableName);
      
      // Create a filter for indexed column.
      Filter filter = new SingleColumnValueFilter(Bytes.toBytes("info"), Bytes.toBytes("name"),
          CompareOperator.EQUAL, "Li Gang".getBytes());
      Scan scan = new Scan();
      scan.setFilter(filter);
      scanner = table.getScanner(scan);
      LOG.info("Scan indexed data.");
      
      for (Result result : scanner) {
        for (Cell cell : result.rawCells()) {
           LOG.info("{}:{},{},{}", Bytes.toString(CellUtil.cloneRow(cell)),
              Bytes.toString(CellUtil.cloneFamily(cell)), Bytes.toString(CellUtil.cloneQualifier(cell)),
              Bytes.toString(CellUtil.cloneValue(cell)));
        }
      }
      LOG.info("Scan data by index successfully.");
    } catch (IOException e) {
      LOG.error("Scan data by index failed.");
    } finally {
      if (scanner != null) {
        // Close the scanner object.
        scanner.close();
      }
      try {
        if (table != null) {
          table.close();
        }
      } catch (IOException e) {
        LOG.error("Close table failed.");
      }
    }
    
    LOG.info("Exiting testScanDataByIndex.");
  }

Precaution

Create secondary indexes for the name field first.

Related Operations

Query a table using a secondary index.

The following provides an example:

Add an index to the name column of the info column family in hbase_sample_table. Run the following command on the client:

hbase org.apache.hadoop.hbase.hindex.mapreduce.TableIndexer -Dtablename.to.index=hbase_sample_table -Dindexspecs.to.add='IDX1=>info:[name->String]' -Dindexnames.to.build='IDX1' 

Query info:name. Run the following command on the HBase shell client:

>scan 'hbase_sample_table',{FILTER=>"SingleColumnValueFilter(family,qualifier,compareOp,comparator,filterIfMissing,latestVersionOnly)"}

Use APIs to perform complex query on the HBase shell client.

The parameters are described as follows:

• family: indicates the column family where the column to be queried locates, such as info.
• qualifier: indicates the column to be queried, such as name.
• compareOp: indicates the comparison operator, such as = and >.
• comparator: indicates the target value to be queried, such as binary:Zhang San.
• filterIfMissing: indicates whether a row is filtered if the column does not exist in this row. The default value is false.
• latestVersionOnly: indicates whether only values of the latest version are to be queried. The default value is false.

For example:

>scan 'hbase_sample_table',{FILTER=>"SingleColumnValueFilter('info','name',=,'binary:Zhang San',true,true)"}