Filtering large amounts of data on the client is inefficient. Tablestore lets you configure filters to screen data on the server based on specified conditions. This reduces network traffic and improves query performance.
How to use
Tablestore provides three types of filters: single-column value checks, regular expression matching, and multi-condition combinations. Set a filter instance using the setFilter method of a query request to filter data on the server. This reduces network overhead and improves query efficiency.
SingleColumnValueFilter: A filter that determines whether the value of a single attribute column meets a specified condition.
SingleColumnValueRegexFilter: A filter that uses a regular expression to match a substring in a String attribute column. The filter then converts the substring to a specified data type and checks whether the value meets a condition.
CompositeColumnValueFilter: A filter that combines multiple conditions to filter data.
Single-column value filter
public class SingleColumnValueFilter extends ColumnValueFilterSingle-column value regular expression filter
Only String attribute columns support regular expression filters.
public class SingleColumnValueRegexFilter extends ColumnValueFilterComposite filter
This filter supports a combination of up to 32 conditions.
public class CompositeColumnValueFilter extends ColumnValueFilterSample code
Before you run the code, you must configure your access credentials by setting the TABLESTORE_ACCESS_KEY_ID and TABLESTORE_ACCESS_KEY_SECRET environment variables.
Single-column value filter
The following sample code shows how to perform a range query on rows whose primary key values are in the [row1, row3) range. The code then filters the results to return only the rows where the col1 attribute is equal to val1.
import com.alicloud.openservices.tablestore.SyncClient;
import com.alicloud.openservices.tablestore.core.ResourceManager;
import com.alicloud.openservices.tablestore.core.auth.CredentialsProvider;
import com.alicloud.openservices.tablestore.core.auth.DefaultCredentialProvider;
import com.alicloud.openservices.tablestore.core.auth.DefaultCredentials;
import com.alicloud.openservices.tablestore.core.auth.V4Credentials;
import com.alicloud.openservices.tablestore.model.Column;
import com.alicloud.openservices.tablestore.model.ColumnValue;
import com.alicloud.openservices.tablestore.model.GetRangeRequest;
import com.alicloud.openservices.tablestore.model.GetRangeResponse;
import com.alicloud.openservices.tablestore.model.PrimaryKeyBuilder;
import com.alicloud.openservices.tablestore.model.PrimaryKeyValue;
import com.alicloud.openservices.tablestore.model.RangeRowQueryCriteria;
import com.alicloud.openservices.tablestore.model.Row;
import com.alicloud.openservices.tablestore.model.filter.SingleColumnValueFilter;
public class SingleValueFilter {
public static void main(String[] args) {
// Obtain access credentials from environment variables. You must configure TABLESTORE_ACCESS_KEY_ID and TABLESTORE_ACCESS_KEY_SECRET.
final String accessKeyId = System.getenv("TABLESTORE_ACCESS_KEY_ID");
final String accessKeySecret = System.getenv("TABLESTORE_ACCESS_KEY_SECRET");
// TODO: Modify the following configurations based on your instance information.
final String region = "cn-hangzhou"; // The ID of the region where the instance resides, for example, "cn-hangzhou".
final String instanceName = "your_instance_name"; // The name of the instance.
final String endpoint = "your_endpoint"; // The endpoint of the instance.
SyncClient client = null;
try {
// Construct the credentials.
DefaultCredentials credentials = new DefaultCredentials(accessKeyId, accessKeySecret);
V4Credentials credentialsV4 = V4Credentials.createByServiceCredentials(credentials, region);
CredentialsProvider provider = new DefaultCredentialProvider(credentialsV4);
// Create a client instance.
client = new SyncClient(endpoint, provider, instanceName, null, new ResourceManager(null, null));
// Construct the query criteria.
// TODO: Change the table name.
RangeRowQueryCriteria rangeRowQueryCriteria = new RangeRowQueryCriteria("test_table");
// Set the start primary key.
// TODO: Change the primary key name and value.
PrimaryKeyBuilder startKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
startKeyBuilder.addPrimaryKeyColumn("id", PrimaryKeyValue.fromString("row1"));
rangeRowQueryCriteria.setInclusiveStartPrimaryKey(startKeyBuilder.build());
// Set the end primary key. The result does not include the end primary key.
// TODO: Change the primary key name and value.
PrimaryKeyBuilder endKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
endKeyBuilder.addPrimaryKeyColumn("id", PrimaryKeyValue.fromString("row3"));
rangeRowQueryCriteria.setExclusiveEndPrimaryKey(endKeyBuilder.build());
// Set the number of versions to query.
rangeRowQueryCriteria.setMaxVersions(1);
// Construct a filter with the condition col1 == "val1".
// TODO: Change the filter field and value.
SingleColumnValueFilter singleColumnValueFilter = new SingleColumnValueFilter("col1", SingleColumnValueFilter.CompareOperator.EQUAL, ColumnValue.fromString("val1"));
rangeRowQueryCriteria.setFilter(singleColumnValueFilter);
// Call the getRange method to query data.
GetRangeRequest getRangeRequest = new GetRangeRequest(rangeRowQueryCriteria);
GetRangeResponse getRangeResponse = client.getRange(getRangeRequest);
// Process the result.
System.out.println("Query completed. Result statistics:");
System.out.println("RequestId: " + getRangeResponse.getRequestId());
System.out.println("Read CU Cost: " + getRangeResponse.getConsumedCapacity().getCapacityUnit().getReadCapacityUnit());
System.out.println("Write CU Cost: " + getRangeResponse.getConsumedCapacity().getCapacityUnit().getWriteCapacityUnit());
// Print the query result.
if (getRangeResponse.getRows() != null && !getRangeResponse.getRows().isEmpty()) {
System.out.println("Found " + getRangeResponse.getRows().size() + " rows that meet the condition:");
for (Row row : getRangeResponse.getRows()) {
// Get and print the primary key information.
// TODO: Modify this based on the primary key data type.
String primaryKeyInfo = row.getPrimaryKey().toString();
System.out.println("- Primary Key: " + primaryKeyInfo);
// Traverse and print all attribute columns of the row. If MaxVersions is 1, each column is the latest version.
if (row.getColumns() != null && row.getColumns().length > 0) {
System.out.println(" Attribute Columns:");
for (Column column : row.getColumns()) {
// Print the column name, value, and timestamp.
String columnName = column.getName();
// TODO: Modify this based on the attribute column data type.
String columnValue = column.getValue().toString();
long timestamp = column.getTimestamp();
System.out.println(
String.format(" - %s: %s (Version Timestamp: %d)", columnName, columnValue, timestamp)
);
}
} else {
System.out.println(" (This row has no attribute columns)");
}
}
} else {
System.out.println("No data found that meets the filter condition.");
}
System.out.println("Filter query operation completed.");
} catch (Exception e) {
System.err.println("Query failed. Details:");
e.printStackTrace();
} finally {
// Shut down the client.
if (client != null) {
client.shutdown();
}
}
}
}Single-column value regular expression filter
The following sample code shows how to perform a range query on rows whose primary key values are in the [row1, row3) range. The code then uses a regular expression to filter the results and return rows where the col1 attribute matches the regular expression 1([a-z]+)5 and the matched substring is aaa.
import com.alicloud.openservices.tablestore.SyncClient;
import com.alicloud.openservices.tablestore.core.ResourceManager;
import com.alicloud.openservices.tablestore.core.auth.CredentialsProvider;
import com.alicloud.openservices.tablestore.core.auth.DefaultCredentialProvider;
import com.alicloud.openservices.tablestore.core.auth.DefaultCredentials;
import com.alicloud.openservices.tablestore.core.auth.V4Credentials;
import com.alicloud.openservices.tablestore.model.Column;
import com.alicloud.openservices.tablestore.model.ColumnValue;
import com.alicloud.openservices.tablestore.model.GetRangeRequest;
import com.alicloud.openservices.tablestore.model.GetRangeResponse;
import com.alicloud.openservices.tablestore.model.PrimaryKeyBuilder;
import com.alicloud.openservices.tablestore.model.PrimaryKeyValue;
import com.alicloud.openservices.tablestore.model.RangeRowQueryCriteria;
import com.alicloud.openservices.tablestore.model.Row;
import com.alicloud.openservices.tablestore.model.filter.RegexRule;
import com.alicloud.openservices.tablestore.model.filter.SingleColumnValueRegexFilter;
public class SingleValueRegexFilter {
public static void main(String[] args) {
// Obtain access credentials from environment variables. You must configure TABLESTORE_ACCESS_KEY_ID and TABLESTORE_ACCESS_KEY_SECRET.
final String accessKeyId = System.getenv("TABLESTORE_ACCESS_KEY_ID");
final String accessKeySecret = System.getenv("TABLESTORE_ACCESS_KEY_SECRET");
// TODO: Modify the following configurations based on your instance information.
final String region = "cn-hangzhou"; // The ID of the region where the instance resides, for example, "cn-hangzhou".
final String instanceName = "your_instance_name"; // The name of the instance.
final String endpoint = "your_endpoint"; // The endpoint of the instance.
SyncClient client = null;
try {
// Construct the credentials.
DefaultCredentials credentials = new DefaultCredentials(accessKeyId, accessKeySecret);
V4Credentials credentialsV4 = V4Credentials.createByServiceCredentials(credentials, region);
CredentialsProvider provider = new DefaultCredentialProvider(credentialsV4);
// Create a client instance.
client = new SyncClient(endpoint, provider, instanceName, null, new ResourceManager(null, null));
// Construct the query criteria.
// TODO: Change the table name.
RangeRowQueryCriteria rangeRowQueryCriteria = new RangeRowQueryCriteria("test_table");
// Set the start primary key.
// TODO: Change the primary key name and value.
PrimaryKeyBuilder startKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
startKeyBuilder.addPrimaryKeyColumn("id", PrimaryKeyValue.fromString("row1"));
rangeRowQueryCriteria.setInclusiveStartPrimaryKey(startKeyBuilder.build());
// Set the end primary key. The result does not include the end primary key.
// TODO: Change the primary key name and value.
PrimaryKeyBuilder endKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
endKeyBuilder.addPrimaryKeyColumn("id", PrimaryKeyValue.fromString("row3"));
rangeRowQueryCriteria.setExclusiveEndPrimaryKey(endKeyBuilder.build());
// Set the number of versions to query.
rangeRowQueryCriteria.setMaxVersions(1);
// Construct a regular expression filter with the condition cast<String>(reg(col1)) == "aaa".
// TODO: Change the regular expression, filter field, and matched value.
RegexRule regexRule = new RegexRule("1([a-z]+)5", RegexRule.CastType.VT_STRING);
SingleColumnValueRegexFilter singleColumnValueRegexFilter = new SingleColumnValueRegexFilter("col1", regexRule, SingleColumnValueRegexFilter.CompareOperator.EQUAL, ColumnValue.fromString("aaa"));
rangeRowQueryCriteria.setFilter(singleColumnValueRegexFilter);
// Call the getRange method to query row data.
GetRangeRequest getRangeRequest = new GetRangeRequest(rangeRowQueryCriteria);
GetRangeResponse getRangeResponse = client.getRange(getRangeRequest);
// Process the result.
System.out.println("Regex filter query completed. Result statistics:");
System.out.println("RequestId: " + getRangeResponse.getRequestId());
System.out.println("Read CU Cost: " + getRangeResponse.getConsumedCapacity().getCapacityUnit().getReadCapacityUnit());
System.out.println("Write CU Cost: " + getRangeResponse.getConsumedCapacity().getCapacityUnit().getWriteCapacityUnit());
// Print the query result.
if (getRangeResponse.getRows() != null && !getRangeResponse.getRows().isEmpty()) {
System.out.println("Found " + getRangeResponse.getRows().size() + " rows that meet the regex filter condition:");
for (Row row : getRangeResponse.getRows()) {
// Get and print the primary key information.
// TODO: Modify this based on the primary key data type.
String primaryKeyInfo = row.getPrimaryKey().toString();
System.out.println("- Primary Key: " + primaryKeyInfo);
// Traverse and print all attribute columns of the row. If MaxVersions is 1, each column is the latest version.
if (row.getColumns() != null && row.getColumns().length > 0) {
System.out.println(" Attribute Columns:");
for (Column column : row.getColumns()) {
// Print the column name, value, and timestamp.
String columnName = column.getName();
// TODO: Modify this based on the attribute column data type.
String columnValue = column.getValue().toString();
long timestamp = column.getTimestamp();
System.out.println(
String.format(" - %s: %s (Version Timestamp: %d)", columnName, columnValue, timestamp)
);
}
} else {
System.out.println(" (This row has no attribute columns)");
}
}
} else {
System.out.println("No data found that meets the regex filter condition.");
}
System.out.println("Regex filter query operation completed.");
} catch (Exception e) {
System.err.println("Regex filter query failed. Details:");
e.printStackTrace();
} finally {
// Shut down the client.
if (client != null) {
client.shutdown();
}
}
}
}Composite filter
The following sample code shows how to perform a range query on rows whose primary key values are in the [row1, row3) range and then filter the data using a composite filter.
import com.alicloud.openservices.tablestore.SyncClient;
import com.alicloud.openservices.tablestore.core.ResourceManager;
import com.alicloud.openservices.tablestore.core.auth.CredentialsProvider;
import com.alicloud.openservices.tablestore.core.auth.DefaultCredentialProvider;
import com.alicloud.openservices.tablestore.core.auth.DefaultCredentials;
import com.alicloud.openservices.tablestore.core.auth.V4Credentials;
import com.alicloud.openservices.tablestore.model.Column;
import com.alicloud.openservices.tablestore.model.ColumnValue;
import com.alicloud.openservices.tablestore.model.GetRangeRequest;
import com.alicloud.openservices.tablestore.model.GetRangeResponse;
import com.alicloud.openservices.tablestore.model.PrimaryKeyBuilder;
import com.alicloud.openservices.tablestore.model.PrimaryKeyValue;
import com.alicloud.openservices.tablestore.model.RangeRowQueryCriteria;
import com.alicloud.openservices.tablestore.model.Row;
import com.alicloud.openservices.tablestore.model.filter.CompositeColumnValueFilter;
import com.alicloud.openservices.tablestore.model.filter.RegexRule;
import com.alicloud.openservices.tablestore.model.filter.SingleColumnValueFilter;
import com.alicloud.openservices.tablestore.model.filter.SingleColumnValueRegexFilter;
public class CompositeFilter {
public static void main(String[] args) {
// Obtain access credentials from environment variables. You must configure TABLESTORE_ACCESS_KEY_ID and TABLESTORE_ACCESS_KEY_SECRET.
final String accessKeyId = System.getenv("TABLESTORE_ACCESS_KEY_ID");
final String accessKeySecret = System.getenv("TABLESTORE_ACCESS_KEY_SECRET");
// TODO: Modify the following configurations based on your instance information.
final String region = "cn-hangzhou"; // The ID of the region where the instance resides, for example, "cn-hangzhou".
final String instanceName = "your_instance_name"; // The name of the instance.
final String endpoint = "your_endpoint"; // The endpoint of the instance.
SyncClient client = null;
try {
// Construct the credentials.
DefaultCredentials credentials = new DefaultCredentials(accessKeyId, accessKeySecret);
V4Credentials credentialsV4 = V4Credentials.createByServiceCredentials(credentials, region);
CredentialsProvider provider = new DefaultCredentialProvider(credentialsV4);
// Create a client instance.
client = new SyncClient(endpoint, provider, instanceName, null, new ResourceManager(null, null));
// Construct the query criteria.
// TODO: Change the table name.
RangeRowQueryCriteria rangeRowQueryCriteria = new RangeRowQueryCriteria("test_table");
// Set the start primary key.
// TODO: Change the primary key name and value.
PrimaryKeyBuilder startKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
startKeyBuilder.addPrimaryKeyColumn("id", PrimaryKeyValue.fromString("row1"));
rangeRowQueryCriteria.setInclusiveStartPrimaryKey(startKeyBuilder.build());
// Set the end primary key. The result does not include the end primary key.
// TODO: Change the primary key name and value.
PrimaryKeyBuilder endKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
endKeyBuilder.addPrimaryKeyColumn("id", PrimaryKeyValue.fromString("row3"));
rangeRowQueryCriteria.setExclusiveEndPrimaryKey(endKeyBuilder.build());
// Set the number of versions to query.
rangeRowQueryCriteria.setMaxVersions(1);
// Construct SingleColumnValueFilter 1 with the condition col1 == "val1".
// TODO: Change the filter field and value.
SingleColumnValueFilter singleColumnValueFilter1 = new SingleColumnValueFilter("col1", SingleColumnValueFilter.CompareOperator.EQUAL, ColumnValue.fromString("val1"));
// Construct SingleColumnValueRegexFilter 1 with the condition cast<String>(reg(col2)) >= "aaa".
// TODO: Change the regular expression, field name, and comparison value.
RegexRule regexRule = new RegexRule("1([a-z]+)5", RegexRule.CastType.VT_STRING);
SingleColumnValueRegexFilter singleColumnValueRegexFilter = new SingleColumnValueRegexFilter("col2", regexRule, SingleColumnValueRegexFilter.CompareOperator.GREATER_EQUAL, ColumnValue.fromString("aaa"));
// Construct CompositeColumnValueFilter 1 with the condition col1 == "val1" OR cast<String>(reg(col2)) >= "aaa".
CompositeColumnValueFilter compositeColumnValueFilter1 = new CompositeColumnValueFilter(CompositeColumnValueFilter.LogicOperator.OR);
compositeColumnValueFilter1.addFilter(singleColumnValueFilter1);
compositeColumnValueFilter1.addFilter(singleColumnValueRegexFilter);
// Construct SingleColumnValueFilter 2 with the condition col3 == "val3".
// TODO: Change the filter field and value.
SingleColumnValueFilter singleColumnValueFilter2 = new SingleColumnValueFilter("col3", SingleColumnValueFilter.CompareOperator.EQUAL, ColumnValue.fromString("val3"));
// Construct CompositeColumnValueFilter 2 with the condition CompositeColumnValueFilter 1 AND SingleColumnValueFilter 2.
// The condition is (col1 == "val1" OR cast<String>(reg(col2)) >= "aaa") AND col3 == "val3".
CompositeColumnValueFilter compositeColumnValueFilter2 = new CompositeColumnValueFilter(CompositeColumnValueFilter.LogicOperator.AND);
compositeColumnValueFilter2.addFilter(compositeColumnValueFilter1);
compositeColumnValueFilter2.addFilter(singleColumnValueFilter2);
// Add the filter to the query.
rangeRowQueryCriteria.setFilter(compositeColumnValueFilter2);
// Call the getRange method to query data.
GetRangeRequest getRangeRequest = new GetRangeRequest(rangeRowQueryCriteria);
GetRangeResponse getRangeResponse = client.getRange(getRangeRequest);
// Process the result.
System.out.println("Query completed. Result statistics:");
System.out.println("RequestId: " + getRangeResponse.getRequestId());
System.out.println("Read CU Cost: " + getRangeResponse.getConsumedCapacity().getCapacityUnit().getReadCapacityUnit());
System.out.println("Write CU Cost: " + getRangeResponse.getConsumedCapacity().getCapacityUnit().getWriteCapacityUnit());
// Print the query result.
if (getRangeResponse.getRows() != null && !getRangeResponse.getRows().isEmpty()) {
System.out.println("Found " + getRangeResponse.getRows().size() + " rows that meet the condition:");
for (Row row : getRangeResponse.getRows()) {
// Get and print the primary key information.
// TODO: Modify this based on the primary key data type.
String primaryKeyInfo = row.getPrimaryKey().toString();
System.out.println("- Primary Key: " + primaryKeyInfo);
// Traverse and print all attribute columns of the row. If MaxVersions is 1, each column is the latest version.
if (row.getColumns() != null && row.getColumns().length > 0) {
System.out.println(" Attribute Columns:");
for (Column column : row.getColumns()) {
// Print the column name, value, and timestamp.
String columnName = column.getName();
// TODO: Modify this based on the attribute column data type.
String columnValue = column.getValue().toString();
long timestamp = column.getTimestamp();
System.out.println(
String.format(" - %s: %s (Version Timestamp: %d)", columnName, columnValue, timestamp)
);
}
} else {
System.out.println(" (This row has no attribute columns)");
}
}
} else {
System.out.println("No data found that meets the filter condition.");
}
System.out.println("Composite filter query operation completed.");
} catch (Exception e) {
System.err.println("Query failed. Details:");
e.printStackTrace();
} finally {
// Shut down the client.
if (client != null) {
client.shutdown();
}
}
}
}Filtering behavior when a column does not exist
Use the setPassIfMissing method to specify whether to return a row if the row does not contain the attribute column to be evaluated.
// Do not return the row if it does not contain the attribute column to evaluate.
singleColumnValueFilter.setPassIfMissing(false);Filtering historical version data
Use the setLatestVersionsOnly method to specify whether to evaluate all data versions. If you choose to evaluate all versions, the row is returned if any version meets the condition.
// Evaluate all data versions.
singleColumnValueFilter.setLatestVersionsOnly(false);