edit-icon download-icon

Filter

Last Updated: Apr 11, 2018

The filter of Table Store is used to sort results on the server side so that the server only returns rows matching the filter conditions. The feature effectively reduces the volume of transferred data and shortens the response time as only matching rows are returned.

The filter conditions of Table Store can be based on a combination of up to 10 conditions. Supported conditions include arithmetic operations (=, !=, >, >=, <, and <=) and logical operations (NOT, AND, and OR). The filter conditions are applicable to GetRow, BatchGetRow, and GetRange.

Procedure

Table Store supports two filters: SingleColumnValueFilter and CompositeColumnValueFilter.

  • SingleColumnValueFilter only checks the value of a reference column.

  • CompositeColumnValueFilter checks the values of multiple reference columns and forms a logical combination of the check results to determine whether to filter the data of a row.

The following examples illustrate these processes:

API Reference: CompositeColumnValueFilter and SingleColumnValueFilter

  • Construct SingleColumnValueFilter

    1. // set condition Col0==0.
    2. SingleColumnValueFilter singleColumnValueFilter = new SingleColumnValueFilter("Col0",
    3. SingleColumnValueFilter.CompareOperator.EQUAL, ColumnValue.fromLong(0));
    4. // If column Col0 does not exist, the condition check fails.
    5. singleColumnValueFilter.setPassIfMissing(false);
  • Construct CompositeColumnValueFilter

    1. // Condition composite1 is (Col0 == 0) AND (Col1 > 100)
    2. CompositeColumnValueFilter composite1 = new CompositeColumnValueFilter(CompositeColumnValueFilter.LogicOperator.AND);
    3. SingleColumnValueFilter single1 = new SingleColumnValueFilter("Col0",
    4. SingleColumnValueFilter.CompareOperator.EQUAL, ColumnValue.fromLong(0));
    5. SingleColumnValueFilter single2 = new SingleColumnValueFilter("Col1",
    6. SingleColumnValueFilter.CompareOperator.GREATER_THAN, ColumnValue.fromLong(100));
    7. composite1.addFilter(single1);
    8. composite1.addFilter(single2);
    9. // Condition composite2 is ( (Col0 == 0) AND (Col1 > 100) ) OR (Col2 <= 10)
    10. CompositeColumnValueFilter composite2 = new CompositeColumnValueFilter(CompositeColumnValueFilter.LogicOperator.OR);
    11. SingleColumnValueFilter single3 = new SingleColumnValueFilter("Col2",
    12. SingleColumnValueFilter.CompareOperator.LESS_EQUAL, ColumnValue.fromLong(10));
    13. composite2.addFilter(composite1);
    14. composite2.addFilter(single3);

Filters are used to filter data that has been read. Therefore, the reference columns used by SingleColumnValueFilter or CompositeColumnValueFilter must be included in the read data. If you specify columns from which data is read, but these columns do not include the reference columns, the filters cannot obtain the reference column values.

When a reference column does not exist, SingleColumnValueFilter uses the passIfMissing parameter to determine whether the filter conditions are met. That is, you can select an action when no reference column exists.

Example

Assume an IoT-based smart electric meter writes voltage, current, usage, and other information to Table Store at a frequency of every 15 seconds. Daily analysis is conducted and the electric meter is checked for any abnormal voltage readings. If abnormal voltage is detected, other status data needs to be evaluated to determine whether to conduct a cable inspection.

To check status data, 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 filter, only the records needed for analysis are returned. Filter helps reduce the volume of returned data and removes the need for preliminary data processing resulting in lower development costs.

Billing

Although filters reduce the volume of returned data, the disk read/write throughput times remain unchanged as 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, using GetRange to read 100 records (200 KB in size) by GetRange and then filter these records to get 10 records (20 KB in size) consumes 50 read CUs per action. (4 KB equivalent to 1 CU).

Additional notes

  • Using filters on the GetRow, BatchGetRow, and GetRange operations does not affect the native semantics or restriction settings of the interface.

  • The GetRange interface limits the number of rows returned at a time (maximum of 5,000) or the size of data returned at a time (maximum of 4 MB). If none of the maximum capacity rows or data records meets the filter conditions, the rows in Response is null, but next_start_primary_key may not be null. If next_start_primary_key is not null, it is used to read the remaining data until it becomes null. For more information, GetRange.

Thank you! We've received your feedback.