Tablestore:Filters

Use cases

• Filtering results directly

  Consider a smart meter in an Internet of Things (IoT) scenario. The meter writes data such as voltage, amperage, and usage to Tablestore at a fixed frequency, for example, every 15 seconds. For daily analysis, you need to identify whether a meter experienced any voltage anomalies and retrieve other status data from those times to decide if a power line inspection is necessary.

  Without a filter, you would use a GetRange operation to read all 5,760 monitoring records for a single meter for one day. You would then need to process these 5,760 records on the client side to find the 10 records that indicate voltage instability.

  With a filter, the server returns only the 10 required records. This approach significantly reduces data transfer and eliminates client-side filtering, saving development costs.

• Filtering with regular expressions and type conversion

  If a column stores data in a custom format, such as a JSON-formatted string, you can filter on a subfield within that string. To do this, use a regular expression to match and extract the subfield value, convert it to the desired data type, and then apply a filter.

  For example, a column stores data in the format {cluster_name:name1,lastupdatetime:12345}. To query for rows where lastupdatetime>12345, you can use the regular expression lastupdatetime:([0-9]+)} to extract the subfield's value. You can then convert the extracted value to a numeric type and perform a comparison to find the desired rows.

Overview

You can use a filter with the GetRow, BatchGetRow, or GetRange operations to return only the rows that meet your specified conditions. A filter does not change the native semantics or limits of these operations. For more information, see Read data.

Filters provide the following configuration options:

• Use the PassIfMissing parameter to specify the filter's behavior when a referenced column does not exist in a row.

• If a referenced column has multiple versions, you can configure the filter to compare only the value of the latest version.

• Use a regular expression to match subfield values.

• Construct composite filter conditions by using relational operators and logical operators.

Tablestore provides the SingleColumnValueFilter, SingleColumnValueRegexFilter, and CompositeColumnValueFilter. These filters determine whether to return a row based on the values of one or more reference columns.

Usage notes

• Filter conditions support relational operators (=, !=, >, >=, <, <=) and logical operators (NOT, AND, OR). You can combine up to 10 conditions.

• Columns referenced by a filter must be included in the read request. Otherwise, the filter cannot access their values.

• When you use the GetRange operation, a single scan cannot exceed 5,000 rows or 4 MB of data.

  If no rows within the scanned 5,000 rows or 4 MB of data match the filter conditions, the Rows field in the response is empty, but NextStartPrimaryKey may not be empty. In this case, you must continue reading by using the returned NextStartPrimaryKey until it is empty.

Usage

You can use filters with the Tablestore SDK for Java, Tablestore SDK for Go, Tablestore SDK for Python, Tablestore SDK for Node.js, Tablestore SDK for .NET, and Tablestore SDK for PHP. The following examples use the Tablestore SDK for Java.

private static void getRow(SyncClient client, String pkValue) {
    // Construct the primary key. 
    PrimaryKeyBuilder primaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
    primaryKeyBuilder.addPrimaryKeyColumn("pk", PrimaryKeyValue.fromString(pkValue));
    PrimaryKey primaryKey = primaryKeyBuilder.build();

    // Specify the table name and primary key to read a row of data. 
    SingleRowQueryCriteria criteria = new SingleRowQueryCriteria("<TABLE_NAME>", primaryKey);
    // Set the MaxVersions parameter to 1 to read the latest version of data. 
    criteria.setMaxVersions(1);

    // Configure a filter to return a row in which the value of the Col0 column is 0. 
    SingleColumnValueFilter singleColumnValueFilter = new SingleColumnValueFilter("Col0",
            SingleColumnValueFilter.CompareOperator.EQUAL, ColumnValue.fromLong(0));
    // If the Col0 column does not exist, the row is not returned. 
    singleColumnValueFilter.setPassIfMissing(false);
    criteria.setFilter(singleColumnValueFilter);

    GetRowResponse getRowResponse = client.getRow(new GetRowRequest(criteria));
    Row row = getRowResponse.getRow();

    System.out.println("Read complete. Result:");
    System.out.println(row);
}

private static void getRange(SyncClient client) {
    // Specify the name of the data table. 
    RangeRowQueryCriteria criteria = new RangeRowQueryCriteria("<TABLE_NAME>");
 
    // Specify ["pk:2020-01-01.log", "pk:2021-01-01.log") as the range of the primary key of the data that you want to read. The range is a left-closed, right-open interval. 
    PrimaryKey pk0 = PrimaryKeyBuilder.createPrimaryKeyBuilder()
        .addPrimaryKeyColumn("pk", PrimaryKeyValue.fromString("2020-01-01.log"))
        .build();
    PrimaryKey pk1 = PrimaryKeyBuilder.createPrimaryKeyBuilder()
        .addPrimaryKeyColumn("pk", PrimaryKeyValue.fromString("2021-01-01.log"))
        .build();
    criteria.setInclusiveStartPrimaryKey(pk0);
    criteria.setExclusiveEndPrimaryKey(pk1);
 
    // Set the MaxVersions parameter to 1 to read the latest version of data. 
    criteria.setMaxVersions(1);
 
    // Configure a filter. A row is returned when cast<int>(regex(Col1)) is greater than 100. 
    RegexRule regexRule = new RegexRule("t1:([0-9]+),", RegexRule.CastType.VT_INTEGER);
    SingleColumnValueRegexFilter filter =  new SingleColumnValueRegexFilter("Col1",
        regexRule,SingleColumnValueRegexFilter.CompareOperator.GREATER_THAN,ColumnValue.fromLong(100));
    criteria.setFilter(filter);

    while (true) {
        GetRangeResponse resp = client.getRange(new GetRangeRequest(criteria));
        for (Row row : resp.getRows()) {
            // do something
            System.out.println(row);
        }
        if (resp.getNextStartPrimaryKey() != null) {
            criteria.setInclusiveStartPrimaryKey(resp.getNextStartPrimaryKey());
        } else {
            break;
        }
   }
}

private static void getRange(SyncClient client) {
    // Specify the table name.
    RangeRowQueryCriteria criteria = new RangeRowQueryCriteria("<TABLE_NAME>");

    // Set the primary key range to a left-closed, right-open interval.
    PrimaryKey pk0 = PrimaryKeyBuilder.createPrimaryKeyBuilder()
            .addPrimaryKeyColumn("pk", PrimaryKeyValue.fromString("a"))
            .build();
    PrimaryKey pk1 = PrimaryKeyBuilder.createPrimaryKeyBuilder()
            .addPrimaryKeyColumn("pk", PrimaryKeyValue.fromString("h"))
            .build();
    criteria.setInclusiveStartPrimaryKey(pk0);
    criteria.setExclusiveEndPrimaryKey(pk1);

    // Set the query to read only the latest version.
    criteria.setMaxVersions(1);

    // The condition for composite1 is (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);

    // The condition for composite2 is ( (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);
    criteria.setFilter(composite2);

    while (true) {
        GetRangeResponse resp = client.getRange(new GetRangeRequest(criteria));
        for (Row row : resp.getRows()) {
            // do something
            System.out.println(row);
        }
        if (resp.getNextStartPrimaryKey() != null) {
            criteria.setInclusiveStartPrimaryKey(resp.getNextStartPrimaryKey());
        } else {
            break;
        }
    }
}

Billing

Filters do not change the existing billing rules.

Filters reduce the amount of data returned, but not the read capacity units (CUs) consumed. This is because filtering occurs on the server after data is read from disk, so the number of disk I/O operations remains unchanged. For example, if a GetRange operation reads 100 rows totaling 200 KB and consumes 50 read CUs, applying a filter that returns only 10 rows (20 KB) still consumes 50 read CUs.

FAQ

Choosing between a secondary index and a search index

References

• If your application needs to query data based on different attributes, you can define these attributes as primary key columns of a secondary index to enable fast lookups. For more information, see Secondary index.

• For business scenarios that require multi-dimensional queries (such as queries on non-primary key columns, composite queries, or fuzzy searches) or data analysis (such as finding aggregates, counting rows, or grouping data), you can use a search index. Define the required attributes as search index fields to query and analyze your data. For more information, see Search index.