查詢大量資料時用戶端過濾效率低下,Table Store支援配置過濾器在服務端按條件式篩選資料,減少網路傳輸提升查詢效能。
使用方式
Table Store提供三種過濾器類型,分別用於單列值判斷、正則匹配和多條件組合。通過查詢請求的setFilter方法設定過濾器執行個體,即可在服務端進行資料過濾,減少網路傳輸開銷並提升查詢效率。
SingleColumnValueFilter:單屬性列值過濾器。判斷單個屬性列的值是否符合條件。
SingleColumnValueRegexFilter:單屬性列正則過濾器。將 String 類型的屬性列按照指定的Regex匹配子字串後轉換成指定的資料類型,再判斷是否符合條件。
CompositeColumnValueFilter:組合過濾器。將多個條件組合進行資料過濾。
單屬性列值過濾器
public class SingleColumnValueFilter extends ColumnValueFilter單屬性列正則過濾器
只有 String 類型的屬性列支援使用正則過濾器。
public class SingleColumnValueRegexFilter extends ColumnValueFilter組合過濾器
最多支援 32 個條件的組合。
public class CompositeColumnValueFilter extends ColumnValueFilter範例程式碼
運行代碼前,請先在環境變數中配置訪問憑證TABLESTORE_ACCESS_KEY_ID和TABLESTORE_ACCESS_KEY_SECRET。
單屬性列值過濾器
以下範例程式碼以範圍查詢為例查詢主索引值為[row1, row3)的行資料,並在查詢後進行過濾,返回col1屬性列的值等於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) {
// 從環境變數中擷取訪問憑證(需要配置TABLESTORE_ACCESS_KEY_ID和TABLESTORE_ACCESS_KEY_SECRET)
final String accessKeyId = System.getenv("TABLESTORE_ACCESS_KEY_ID");
final String accessKeySecret = System.getenv("TABLESTORE_ACCESS_KEY_SECRET");
// TODO: 根據執行個體資訊修改以下配置
final String region = "cn-hangzhou"; // 填寫執行個體所屬的地區ID,例如 "cn-hangzhou"
final String instanceName = "your_instance_name"; // 填寫執行個體名稱
final String endpoint = "your_endpoint"; // 填寫執行個體訪問地址
SyncClient client = null;
try {
// 構造憑證
DefaultCredentials credentials = new DefaultCredentials(accessKeyId, accessKeySecret);
V4Credentials credentialsV4 = V4Credentials.createByServiceCredentials(credentials, region);
CredentialsProvider provider = new DefaultCredentialProvider(credentialsV4);
// 建立用戶端執行個體
client = new SyncClient(endpoint, provider, instanceName, null, new ResourceManager(null, null));
// 構造查詢條件
// TODO: 修改表名稱
RangeRowQueryCriteria rangeRowQueryCriteria = new RangeRowQueryCriteria("test_table");
// 設定查詢起始主鍵
// TODO: 修改主鍵名和值
PrimaryKeyBuilder startKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
startKeyBuilder.addPrimaryKeyColumn("id", PrimaryKeyValue.fromString("row1"));
rangeRowQueryCriteria.setInclusiveStartPrimaryKey(startKeyBuilder.build());
// 設定查詢結束主鍵,返回結果不包含結束主鍵
// TODO: 修改主鍵名和值
PrimaryKeyBuilder endKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
endKeyBuilder.addPrimaryKeyColumn("id", PrimaryKeyValue.fromString("row3"));
rangeRowQueryCriteria.setExclusiveEndPrimaryKey(endKeyBuilder.build());
// 設定查詢版本
rangeRowQueryCriteria.setMaxVersions(1);
// 構造過濾器,條件為 col1 == "val1"
// TODO: 修改過濾欄位和值
SingleColumnValueFilter singleColumnValueFilter = new SingleColumnValueFilter("col1", SingleColumnValueFilter.CompareOperator.EQUAL, ColumnValue.fromString("val1"));
rangeRowQueryCriteria.setFilter(singleColumnValueFilter);
// 調用getRange方法查詢資料
GetRangeRequest getRangeRequest = new GetRangeRequest(rangeRowQueryCriteria);
GetRangeResponse getRangeResponse = client.getRange(getRangeRequest);
// 返回結果處理
System.out.println("查詢完成,結果統計:");
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());
// 輸出查詢結果
if (getRangeResponse.getRows() != null && !getRangeResponse.getRows().isEmpty()) {
System.out.println("共找到 " + getRangeResponse.getRows().size() + " 行合格資料:");
for (Row row : getRangeResponse.getRows()) {
// 擷取並列印主鍵資訊
// TODO: 根據主鍵資料類型進行修改
String primaryKeyInfo = row.getPrimaryKey().toString();
System.out.println("- 主鍵: " + primaryKeyInfo);
// 遍曆並列印該行的所有屬性列 (當MaxVersions=1時,每個Column即為最新版本)
if (row.getColumns() != null && row.getColumns().length > 0) {
System.out.println(" 屬性列:");
for (Column column : row.getColumns()) {
// 列印列的名稱、值和時間戳記
String columnName = column.getName();
// TODO: 根據屬性列資料類型進行修改
String columnValue = column.getValue().toString();
long timestamp = column.getTimestamp();
System.out.println(
String.format(" - %s: %s (版本時間戳記: %d)", columnName, columnValue, timestamp)
);
}
} else {
System.out.println(" (該行沒有屬性列)");
}
}
} else {
System.out.println("沒有找到符合過濾條件的資料");
}
System.out.println("過濾查詢操作執行完成");
} catch (Exception e) {
System.err.println("查詢失敗,詳細資料如下:");
e.printStackTrace();
} finally {
// 關閉用戶端
if (client != null) {
client.shutdown();
}
}
}
}單屬性列正則過濾器
以下範例程式碼以範圍查詢為例查詢主索引值為[row1, row3)的行資料,並在查詢後進行正則匹配過濾,返回col1屬性列的值滿足Regex1([a-z]+)5且匹配到的子字串為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) {
// 從環境變數中擷取訪問憑證(需要配置TABLESTORE_ACCESS_KEY_ID和TABLESTORE_ACCESS_KEY_SECRET)
final String accessKeyId = System.getenv("TABLESTORE_ACCESS_KEY_ID");
final String accessKeySecret = System.getenv("TABLESTORE_ACCESS_KEY_SECRET");
// TODO: 根據執行個體資訊修改以下配置
final String region = "cn-hangzhou"; // 填寫執行個體所屬的地區ID,例如 "cn-hangzhou"
final String instanceName = "your_instance_name"; // 填寫執行個體名稱
final String endpoint = "your_endpoint"; // 填寫執行個體訪問地址
SyncClient client = null;
try {
// 構造憑證
DefaultCredentials credentials = new DefaultCredentials(accessKeyId, accessKeySecret);
V4Credentials credentialsV4 = V4Credentials.createByServiceCredentials(credentials, region);
CredentialsProvider provider = new DefaultCredentialProvider(credentialsV4);
// 建立用戶端執行個體
client = new SyncClient(endpoint, provider, instanceName, null, new ResourceManager(null, null));
// 構造查詢條件
// TODO: 修改表名稱
RangeRowQueryCriteria rangeRowQueryCriteria = new RangeRowQueryCriteria("test_table");
// 設定查詢起始主鍵
// TODO: 修改主鍵名和值
PrimaryKeyBuilder startKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
startKeyBuilder.addPrimaryKeyColumn("id", PrimaryKeyValue.fromString("row1"));
rangeRowQueryCriteria.setInclusiveStartPrimaryKey(startKeyBuilder.build());
// 設定查詢結束主鍵,返回結果不包含結束主鍵
// TODO: 修改主鍵名和值
PrimaryKeyBuilder endKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
endKeyBuilder.addPrimaryKeyColumn("id", PrimaryKeyValue.fromString("row3"));
rangeRowQueryCriteria.setExclusiveEndPrimaryKey(endKeyBuilder.build());
// 設定查詢版本
rangeRowQueryCriteria.setMaxVersions(1);
// 構造正則過濾器,條件為 cast<String>(reg(col1)) == "aaa"
// TODO: 修改Regex、過濾欄位和匹配值
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);
// 調用getRange方法查詢行資料
GetRangeRequest getRangeRequest = new GetRangeRequest(rangeRowQueryCriteria);
GetRangeResponse getRangeResponse = client.getRange(getRangeRequest);
// 返回結果處理
System.out.println("正則過濾查詢完成,結果統計:");
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());
// 輸出查詢結果
if (getRangeResponse.getRows() != null && !getRangeResponse.getRows().isEmpty()) {
System.out.println("共找到 " + getRangeResponse.getRows().size() + " 行符合正則過濾條件的資料:");
for (Row row : getRangeResponse.getRows()) {
// 擷取並列印主鍵資訊
// TODO: 根據主鍵資料類型進行修改
String primaryKeyInfo = row.getPrimaryKey().toString();
System.out.println("- 主鍵: " + primaryKeyInfo);
// 遍曆並列印該行的所有屬性列 (當MaxVersions=1時,每個Column即為最新版本)
if (row.getColumns() != null && row.getColumns().length > 0) {
System.out.println(" 屬性列:");
for (Column column : row.getColumns()) {
// 列印列的名稱、值和時間戳記
String columnName = column.getName();
// TODO: 根據屬性列資料類型進行修改
String columnValue = column.getValue().toString();
long timestamp = column.getTimestamp();
System.out.println(
String.format(" - %s: %s (版本時間戳記: %d)", columnName, columnValue, timestamp)
);
}
} else {
System.out.println(" (該行沒有屬性列)");
}
}
} else {
System.out.println("沒有找到符合正則過濾條件的資料");
}
System.out.println("正則過濾查詢操作執行完成");
} catch (Exception e) {
System.err.println("正則過濾查詢失敗,詳細資料如下:");
e.printStackTrace();
} finally {
// 關閉用戶端
if (client != null) {
client.shutdown();
}
}
}
}組合過濾器
以下範例程式碼以範圍查詢為例查詢主索引值為[row1, row3)的行資料,並使用組合過濾器進行資料過濾。
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) {
// 從環境變數中擷取訪問憑證(需要配置TABLESTORE_ACCESS_KEY_ID和TABLESTORE_ACCESS_KEY_SECRET)
final String accessKeyId = System.getenv("TABLESTORE_ACCESS_KEY_ID");
final String accessKeySecret = System.getenv("TABLESTORE_ACCESS_KEY_SECRET");
// TODO: 根據執行個體資訊修改以下配置
final String region = "cn-hangzhou"; // 填寫執行個體所屬的地區ID,例如 "cn-hangzhou"
final String instanceName = "your_instance_name"; // 填寫執行個體名稱
final String endpoint = "your_endpoint"; // 填寫執行個體訪問地址
SyncClient client = null;
try {
// 構造憑證
DefaultCredentials credentials = new DefaultCredentials(accessKeyId, accessKeySecret);
V4Credentials credentialsV4 = V4Credentials.createByServiceCredentials(credentials, region);
CredentialsProvider provider = new DefaultCredentialProvider(credentialsV4);
// 建立用戶端執行個體
client = new SyncClient(endpoint, provider, instanceName, null, new ResourceManager(null, null));
// 構造查詢條件
// TODO: 修改表名稱
RangeRowQueryCriteria rangeRowQueryCriteria = new RangeRowQueryCriteria("test_table");
// 設定查詢起始主鍵
// TODO: 修改主鍵名和值
PrimaryKeyBuilder startKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
startKeyBuilder.addPrimaryKeyColumn("id", PrimaryKeyValue.fromString("row1"));
rangeRowQueryCriteria.setInclusiveStartPrimaryKey(startKeyBuilder.build());
// 設定查詢結束主鍵,返回結果不包含結束主鍵
// TODO: 修改主鍵名和值
PrimaryKeyBuilder endKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
endKeyBuilder.addPrimaryKeyColumn("id", PrimaryKeyValue.fromString("row3"));
rangeRowQueryCriteria.setExclusiveEndPrimaryKey(endKeyBuilder.build());
// 設定查詢版本
rangeRowQueryCriteria.setMaxVersions(1);
// 構造單屬性列值過濾器1,條件為 col1 == "val1"
// TODO: 修改過濾欄位和值
SingleColumnValueFilter singleColumnValueFilter1 = new SingleColumnValueFilter("col1", SingleColumnValueFilter.CompareOperator.EQUAL, ColumnValue.fromString("val1"));
// 構造單屬性列正則過濾器1,條件為 cast<String>(reg(col2)) >= "aaa"
// TODO: 修改Regex、欄位名和比較值
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"));
// 構造組合過濾器1,條件為 col1 == "val1" OR cast<String>(reg(col2)) >= "aaa"
CompositeColumnValueFilter compositeColumnValueFilter1 = new CompositeColumnValueFilter(CompositeColumnValueFilter.LogicOperator.OR);
compositeColumnValueFilter1.addFilter(singleColumnValueFilter1);
compositeColumnValueFilter1.addFilter(singleColumnValueRegexFilter);
// 構造單屬性列值過濾器2,條件為 col3 == "val3"
// TODO: 修改過濾欄位和值
SingleColumnValueFilter singleColumnValueFilter2 = new SingleColumnValueFilter("col3", SingleColumnValueFilter.CompareOperator.EQUAL, ColumnValue.fromString("val3"));
// 構造組合過濾器2,條件為 組合過濾器1 AND 單屬性列值過濾器2
// 即 (col1 == "val1" OR cast<String>(reg(col2)) >= "aaa") AND col3 == "val3"
CompositeColumnValueFilter compositeColumnValueFilter2 = new CompositeColumnValueFilter(CompositeColumnValueFilter.LogicOperator.AND);
compositeColumnValueFilter2.addFilter(compositeColumnValueFilter1);
compositeColumnValueFilter2.addFilter(singleColumnValueFilter2);
// 查詢內容添加過濾器
rangeRowQueryCriteria.setFilter(compositeColumnValueFilter2);
// 調用getRange方法查詢資料
GetRangeRequest getRangeRequest = new GetRangeRequest(rangeRowQueryCriteria);
GetRangeResponse getRangeResponse = client.getRange(getRangeRequest);
// 返回結果處理
System.out.println("查詢完成,結果統計:");
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());
// 輸出查詢結果
if (getRangeResponse.getRows() != null && !getRangeResponse.getRows().isEmpty()) {
System.out.println("共找到 " + getRangeResponse.getRows().size() + " 行合格資料:");
for (Row row : getRangeResponse.getRows()) {
// 擷取並列印主鍵資訊
// TODO: 根據主鍵資料類型進行修改
String primaryKeyInfo = row.getPrimaryKey().toString();
System.out.println("- 主鍵: " + primaryKeyInfo);
// 遍曆並列印該行的所有屬性列 (當MaxVersions=1時,每個Column即為最新版本)
if (row.getColumns() != null && row.getColumns().length > 0) {
System.out.println(" 屬性列:");
for (Column column : row.getColumns()) {
// 列印列的名稱、值和時間戳記
String columnName = column.getName();
// TODO: 根據屬性列資料類型進行修改
String columnValue = column.getValue().toString();
long timestamp = column.getTimestamp();
System.out.println(
String.format(" - %s: %s (版本時間戳記: %d)", columnName, columnValue, timestamp)
);
}
} else {
System.out.println(" (該行沒有屬性列)");
}
}
} else {
System.out.println("沒有找到符合過濾條件的資料");
}
System.out.println("組合過濾查詢操作執行完成");
} catch (Exception e) {
System.err.println("查詢失敗,詳細資料如下:");
e.printStackTrace();
} finally {
// 關閉用戶端
if (client != null) {
client.shutdown();
}
}
}
}列不存在時的過濾行為
通過setPassIfMissing方法設定行資料不包含判斷的屬性列時,是否返回該行。
// 行資料不包含判斷的屬性列時,不返回該行
singleColumnValueFilter.setPassIfMissing(false);過濾歷史版本資料
通過setLatestVersionsOnly方法設定是否判斷所有版本的資料。此時只要有一個版本的資料符合條件,即返回該行資料。
// 判斷所有版本的資料
singleColumnValueFilter.setLatestVersionsOnly(false);