You can filter Tablestore results on the server side to return only rows that match the filter conditions This feature reduces the volume of transferred data and shortens the response time because only matched rows are returned.

Scenarios

An Internet of things (IoT)-based smart electric meter writes voltage, current, usage, and other information to a Tablestore table every 15 seconds. You must query abnormal voltage data and other related status data for daily analysis to determine whether to inspect cables.

You can use GetRange to read the monitoring data generated by the electric meter and filter the data (5,760 records) to obtain the records that are collected when the voltage is unstable.

By using a filter, only the ten records that need to be analyzed are returned. A filter reduces the volume of returned data and removes the need for preliminary data processing. This reduces development costs.

Limits

  • Filters support relational operator-based operations including =, ! =, >, >=, <, and <= and logical operations including NOT, AND, and OR. You can use a combination of up to 10 filter conditions for a filter.
  • The reference columns used by a filter must be included in the read data. If you specify columns from which data is read that do not include the reference columns, the filters cannot obtain the reference column values.
  • The native semantics and limits of the GetRow, BatchGetRow, and GetRange operations are not affected when you use filters. For more information, see Single-row operations and Multi-row operations.

    When you use the GetRange operation, the number of rows scanned at a time is up to 5,000, and the size of data returned at a time is up to 4 MB.

    If the scanned 5,000 rows or 4 MB of data does not match the filter conditions, the rows in the response are empty. However, NextStartPrimaryKey may not be empty. In this case, you must use NextStartPrimaryKey to continue reading the data until NextStartPrimaryKey is empty.

Operations

Filters can be used for the GetRow, BatchGetRow, and GetRange operations.

Tablestore supports the SingleColumnValueFilter and CompositeColumnValueFilter filters.

  • SingleColumnValueFilter: determines whether to filter a row based on the value of only one reference column.

    SingleColumnValueFilter uses the PassIfMissing parameter to determine whether the filter conditions are met if a reference column does not exist. You can select an action when no reference column exists.

  • CompositeColumnValueFilter: determines whether to filter a row based on a logical combination of the check results for the values of multiple reference columns.

Use Tablestore SDKs

You can use the following Tablestore SDKs to implement filter:

  • Tablestore SDK for Java: Filter
  • Tablestore SDK for Go: Filter
  • Tablestore SDK for Python: Filter
  • Tablestore SDK for Node.js: Filter
  • Tablestore SDK for .NET: Filter
  • Tablestore SDK for PHP: Filter

Parameters

Parameter Description
ColumnName The name of the reference column used by the filter.
ColumnValue The value of the reference column used by the filter.
CompareOperator The relational operator used by the filter.

Relational operators include EQUAL (=), NOT_EQUAL (!=), GREATER_THAN (>), GREATER_EQUAL (>=), LESS_THAN (<), and LESS_EQUAL (<=).

LogicOperator The logical operator used by the filter.

Logical operators include NOT, AND, and OR.

PassIfMissing Determines whether to return a row when a reference column does not exist in the row. The data type of the parameter value is bool. The default value is true, which indicates that if a reference column does not exist in a row, the row is returned.

If you set the value of PassIfMissing to false, a row is not returned if a reference column does not exist in the row.

LatestVersionsOnly Determines whether to use only the latest versions of data in the reference columns for comparison when each reference column contains data of multiple versions. The data type of the parameter value is bool. The default value is true, which indicates that the latest versions of data are used for comparison when each reference column contains data of multiple versions.

If you set the value of LatestVersionsOnly to false, all versions of data in a reference column are used for comparison. The row is returned if one version of data in the reference column meets the conditions.

Examples

  • The following code provides an example on how to construct a SingleColumnValueFilter:
      // Configure the filter so that when the value of Col0 is 0, the row is returned.
      SingleColumnValueFilter singleColumnValueFilter = new SingleColumnValueFilter("Col0",
              SingleColumnValueFilter.CompareOperator.EQUAL, ColumnValue.fromLong(0));
      // If Col0 does not exist, the row is not returned.
      singleColumnValueFilter.setPassIfMissing(false);
      // Only the latest version of data in the column is used for comparison.
      singleColumnValueFilter.setLatestVersionsOnly(true);
                        
  • The following code provides an example on how to construct a CompositeColumnValueFilter:
      // Condition composite1: (Col0 == 0) AND (Col1 > 100).
      CompositeColumnValueFilter composite1 = new CompositeColumnValueFilter(CompositeColumnValueFilter.LogicOperator.AND);
      SingleColumnValueFilter single1 = new SingleColumnValueFilter("Col0",
              SingleColumnValueFilter.CompareOperator.EQUAL, ColumnValue.fromLong(0));
      SingleColumnValueFilter single2 = new SingleColumnValueFilter("Col1",
              SingleColumnValueFilter.CompareOperator.GREATER_THAN, ColumnValue.fromLong(100));
      composite1.addFilter(single1);
      composite1.addFilter(single2);
    
      // Condition composite2: ( (Col0 == 0) AND (Col1 > 100) ) OR (Col2 <= 10).
      CompositeColumnValueFilter composite2 = new CompositeColumnValueFilter(CompositeColumnValueFilter.LogicOperator.OR);
      SingleColumnValueFilter single3 = new SingleColumnValueFilter("Col2",
              SingleColumnValueFilter.CompareOperator.LESS_EQUAL, ColumnValue.fromLong(10));
      composite2.addFilter(composite1);
      composite2.addFilter(single3);
                        

Billing methods

The implementation of filters does not affect existing billing methods.

Although filters reduce the volume of returned data, the disk I/O usage remain unchanged because filtering is performed at the server side before data is returned. Therefore, the same number of read CUs are consumed regardless of whether the filters are used or not. For example, when you use GetRange to read 100 records (200 KB in size) of data and then filter these records to obtain 10 records (20 KB in size), 50 read CUs are consumed (where 1 CU is equal to 4 KB).

Error codes

For more information, see t20552.html#topic23679.