英文:
Get last value in DynamoDB Partition
问题
我要获取DDB分区中按课程排序键排序的最后一个值。
我的表压缩如下:
===============================
学生编号 | 打卡时间 | 课程编号 | 地点名称
===============================
分区键 = 学生编号
排序键 = 打卡时间
我正在使用AWS Enhanced Java客户端,我得到了这个可以工作的查询,但问题是我受到了节流(虽然预留的容量不是很高,但仅仅针对这个查询不应该受到节流)。
以下是代码:
@Override
public AttendanceTracking findFirstByStudentNrOrderByPunchTimeDesc(String studentNr) {
DynamoDbTable<AttendanceTracking> attendanceTable = getTable();
//仅获取按打卡时间排序的最后一个数据库条目
String today = LocalDateTime.now().toString();
Key fromKey = Key.builder().partitionValue(studentNr).sortValue(today).build();
QueryConditional queryConditional = QueryConditional
.sortLessThan(fromKey);
Iterable<Page<AttendanceTracking>> results =
attendanceTable.query(QueryEnhancedRequest.builder()
.queryConditional(queryConditional)
.scanIndexForward(false)
.limit(1)
.build());
List<AttendanceTracking> entries = new ArrayList<>();
results.forEach(page -> entries.addAll(page.items()));
if (entries.size() > 0) {
return entries.get(0);
}
return null;
}
此外,这个查询平均需要6秒来执行?然而,这个特定分区中应该只有几百个条目。不确定是否因为节流而导致,客户端内部是否构建了自动重试。
我认为这可能是因为我使用了"sortLessThan",但我无法想象它应该扫描分区中的所有值。
英文:
I want to get only the last value in the DDB partition sorted by the sort key of
course.
My Table minified looks as follows:
===============================
studentNr | punchTime | classId | locationName
===============================
Partition Key = StudentNr
SortyKey = PunchTime
I am using the AWS Enhanced client for java and I got this query that is working, but the problem is I am getting throttled, (not very high capacity provisioned but still should not get throttled for just this query).
The code looks as follows:
@Override
public AttendanceTracking findFirstByStudentNrOrderByPunchTimeDesc(String studentNr) {
DynamoDbTable<AttendanceTracking> attendanceTable = getTable();
//Only get last db entry sorted by punchTime
String today = LocalDateTime.now().toString();
Key fromKey = Key.builder().partitionValue(studentNr).sortValue(today).build();
QueryConditional queryConditional = QueryConditional
.sortLessThan(fromKey);
Iterable<Page<AttendanceTracking>> results =
attendanceTable.query(QueryEnhancedRequest.builder()
.queryConditional(queryConditional)
.scanIndexForward(false)
.limit(1)
.build());
List<AttendanceTracking> entries = new ArrayList<>();
results.forEach(page -> entries.addAll(page.items()));
if (entries.size() > 0) {
return entries.get(0);
}
return null;
}
Also, this query takes 6 seconds avg to execute? Yet there should only be a few 100 entries in this specific parition. Not sure if this is because of throttling and the client has automatic retries build in I dont know.
I am thinking its because I do a sortLess than, but I can't imagine it should scan all values in the partition?
Please help
答案1
得分: 0
评论中有人指出了答案。在这种情况下,您不需要涉及SortKey。只需按照分区键搜索并进行反向扫描,将扫描索引限制为1,因为DDB已经对您的数据进行了排序,应该是正确的顺序。这还将查询时间从6秒减少到60毫秒。
@Override
public AttendanceTracking findFirstByStudentNrOrderByPunchTimeDesc(String studentNr) {
DynamoDbTable<AttendanceTracking> attendanceTable = getTable();
//只获取最后一条按punchTime排序的数据库条目
String today = LocalDateTime.now().toString();
QueryConditional queryConditional = QueryConditional
.keyEqualTo(Key.builder().partitionValue(studentNr).build());
Iterable<Page<AttendanceTracking>> results =
attendanceTable.query(QueryEnhancedRequest.builder()
.queryConditional(queryConditional)
.scanIndexForward(false)
.limit(1)
.build());
List<AttendanceTracking> entries = new ArrayList<>();
results.forEach(page -> entries.addAll(page.items()));
if (entries.size() > 0) {
return entries.get(0);
}
return null;
}
英文:
Someone in the comments pointed out the answer. You dont need to have the SortKey involved in this case. Simply search by Partition key and reverse scanIndex limmitting to 1, because DDB already sorts your data is should be in correct order. It also cut the query time down from 6 seconds to 60ms.
@Override
public AttendanceTracking findFirstByStudentNrOrderByPunchTimeDesc(String studentNr) {
DynamoDbTable<AttendanceTracking> attendanceTable = getTable();
//Only get last db entry sorted by punchTime
String today = LocalDateTime.now().toString();
QueryConditional queryConditional = QueryConditional
.keyEqualTo(Key.builder().partitionValue(studentNr).build());
Iterable<Page<AttendanceTracking>> results =
attendanceTable.query(QueryEnhancedRequest.builder()
.queryConditional(queryConditional)
.scanIndexForward(false)
.limit(1)
.build());
List<AttendanceTracking> entries = new ArrayList<>();
results.forEach(page -> entries.addAll(page.items()));
if (entries.size() > 0) {
return entries.get(0);
}
return null;
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论