获取DynamoDB分区中的最后一个数值。

huangapple go评论60阅读模式
英文:

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;
    }

huangapple
  • 本文由 发表于 2023年3月9日 23:41:12
  • 转载请务必保留本文链接:https://go.coder-hub.com/75686879.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定