使用 IN 子句查询 DynamoDB

2022-01-15 00:00:00 amazon-dynamodb java

我是 DynamoDB 的新手，想知道如何使用 Java 在带有 IN 子句的 DynamoDB 中的表上进行查询.

I am new to DynamoDB and wanted to know how can we query on a table in DynamoDB with IN clause using Java.

我有一个名为 items 的表.它的架构是

I have a table named items. It`s schema is

1. Product (Partition Key of type String)
2. ID (Sort Key of type int)
3. Date ( attribute of type String)

我想查询类似

SELECT ID FROM items WHERE Product IN ("apple","orange").

或

SELECT Product FROM items WHERE ID IN (100,200).

推荐答案

由于要求是获取没有分区键的产品，有两种选择.

As the requirement is to get the products without the partition key, there are two options available.

1) 在排序键属性 Id 上创建 GSI(全局二级索引)以使用查询 API

1) Create GSI (Global Secondary Index) on sort key attribute Id to use the Query API

2) 扫描整个表格以获取产品 - 因为它扫描整个表格效率不高

2) Scan the entire table to get the products - not very efficient as it scans the full table

这两个选项都使用 DynamoDB 上可用的 IN 运算符.

Both the options use the IN operator available on DynamoDB.

使用 ID 进行扫描的示例代码:-

Map<String, AttributeValue> attributeValues = new HashMap<>();
attributeValues.put(":id1", new AttributeValue().withN("100"));
attributeValues.put(":id2", new AttributeValue().withN("200"));

DynamoDBScanExpression dynamoDBScanExpression = new DynamoDBScanExpression()
                                                    .withFilterExpression("ID IN (:id1, :id2)")
                                                    .withExpressionAttributeValues(attributeValues);


PaginatedScanList<yourModelClass> scanList = dynamoDBMapper.scan(yourModelClass.class, dynamoDBScanExpression,
        new DynamoDBMapperConfig(DynamoDBMapperConfig.ConsistentReads.CONSISTENT));

Iterator<yourModelClass> iterator = scanList.iterator();

while (iterator.hasNext()) {
    System.out.println(iterator.next().getTitle());
}

按产品查询:-

1) 不能使用 IN 运算符 作为分区键

1) Cannot use IN operator for partition key

2) 在不知道排序键的情况下无法使用 batchLoad API

2) Cannot use batchLoad API without knowing the sort key

结论:-

有效的解决方案是在属性 Id 上创建 GSI(不带排序键)并使用 batchLoad API.

The efficient solution is to create GSI (without sort key) on attribute Id and use batchLoad API.

注意: GSI 的分区键不必唯一

Note: The partition key of GSI need not be unique

相关文章