Sorting not supported for scan expressions and no HASH key for GSI for DynamoDBPagingAndSortingRepository

user_4643632 0
Viewed 847

I think I have tried all kind of combinations of annotation but I am getting either Sorting not supported for scan expressions or no HASH key for GSI or Both the Hash Key and the Range Key element in the KeySchema have the same name error.

Expectation: Get values with pagination and sort by createdAt field descending.

Repository:

@Repository
@EnableScan
@EnableScanCount
interface StatementRepository : DynamoDBPagingAndSortingRepository<Statement?, String?> {
    override fun findById(id: String): Optional<Statement?>
}

Entity:

@DynamoDBTable(tableName = "statements")
class Statement {
    @get:DynamoDBAutoGeneratedKey
    @get:DynamoDBHashKey
    var id: String? = null

    @get:DynamoDBAttribute
    var statementText: String? = null

    @get:DynamoDBAttribute
    var source: String? = null

    @get:DynamoDBAttribute
    @get: DynamoDBIndexHashKey(globalSecondaryIndexName = "id")
    @get: DynamoDBIndexRangeKey(globalSecondaryIndexNames = ["id-createdAt-index"])
    var createdAt: Long? = null
}

Service:

statementRepository.findAll(PageRequest.of(page, size, Sort.by("createdAt").descending())).forEach {...

I tried almost every solution on the internet and all combination of DynamoDBIndexHashKey and DynamoDBIndexRangeKey annotations but getting diffrent errors.

Note: Paging was working, the problem started when applied sorting as Sort.by("createdAt").descending()

Index on the table (created myself during hit-and-trail):

Snapshot of index created with Partition Key id and Sort Key createdAt

Please check the annotations and help me to place them right or let me know if I am missing anything.

I am also new to Kotlin but I have knowledge of Java.

amazon-dynamodb-index amazon-dynamodb spring-boot kotlin pagination
1 Answers

Learn DynamoDB. I think this is an issue a lot of developers have especially coming from JPA Spring background, they simply find the DynamoDB implementation to a JPA repository and have at it. But DynamoDB is more of a Key Value store than a SQL database.

In DynamoDB you partition your data, (Wether it is in a GSI, LSI etc doesn't matter). Within a partition data can be sorted (optionally). So you can see if you wanted to find all Statements by oldest to newest globally, well then you are out of luck my friend, you cannot do this. You can only do this within a partition.

So this seemingly trivial thing you want to do is not so trivial in DynamoDB. If you wanted for example the oldest 10 Statements, the correct way to do this is that when data comes in to Dynamo, you have a Lambda in a Stream which pre-calculates anything you want to read later, such as the oldest/newest/average etc.

If this sounds like a massive pain, then probably you don't want to use DynamoDB. The reason you would want to use it is its performance is off the charts amazing and it's cheap. It isn't a swappable SQL database.

user_40332920
Related