使用IN子句查询DynamoDB [英] Query DynamoDB with IN Clause

问题描述

我是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

这篇关于使用IN子句查询DynamoDB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！