Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

DynamoDB : Pagination with "withExclusiveStartKey" on a Global Secondary Index

I have DynamoDB table called "product" with a Global Secondary Index on "userId".Primary Key is on "id". I am trying to implement Querying with pagination using "withExclusiveStartKey" on "userID" GSI. However, I get following exception when I pass a valid lastId:

Exclusive Start Key must have same size as table's key schema (Service: AmazonDynamoDBv2; Status Code: 400; Error Code: ValidationException; Request ID: 822db97e-04a3-4c36-8c72-6008e2693679)

What am I doing wrong here ?

public QueryResultPage<Product>  findPaged(String userId,int limit,String lastId) {
        DynamoDBMapper mapper = new DynamoDBMapper(dynamoDb);       
        Map<String, AttributeValue> vals = new HashMap<>();
        vals.put(":valUserId", new AttributeValue().withS(userId));
                DynamoDBQueryExpression<Product> queryExp = new         DynamoDBQueryExpression<Product>()
                .withKeyConditionExpression("userId = :valUserId")
                .withIndexName(ModelConsts.TBL_PRODUCT_GSI_USERID)
                .withExpressionAttributeValues(vals)
                .withScanIndexForward(false)
                .withConsistentRead(false)
                .withLimit(limit);  
           if (lastId != null) {//paging
            Map<String, AttributeValue> exclusiveStartKey = new HashMap<String, AttributeValue>();
                    exclusiveStartKey.put("id", new AttributeValue().withS(lastId));
               queryExp = queryExp.withExclusiveStartKey(exclusiveStartKey);
        }   
        QueryResultPage<Product> result = mapper.queryPage(Product.class, queryExp);
        return result;      
    }
like image 486
Ashika Umanga Umagiliya Avatar asked Dec 06 '16 05:12

Ashika Umanga Umagiliya


3 Answers

I'm writing this answer for those of you, who try to construct the exclusiveStartKey manually, for a GSI query. It appears that the exclusive start key is made up of 3 components:

  • GSI hash key,
  • GSI range key and
  • the table key

This doesn't seem to be documented anywhere as you are supposed to just use the returned lastEvaluatedKey by calling:

setLastEvaluatedKey(queryResult.getLastEvaluatedKey());

The accepted answer is correct but it leaves the reader with the impression that there are only 2 components to the key, which didn't help in my case. The solution described here was first mentioned in this GitHub issue.

Here's how the above is implemented using the AWS Java SDK v2:

QueryRequest.Builder queryBuilder = QueryRequest.builder();
Map<String, AttributeValue> startKey = new HashMap<>(3);
// HASH/PARTITION KEY
startKey.put("gsiHashKeyAttribute", AttributeValue.builder().s(gsiHashKey).build());
// RANGE/SORT KEY
startKey.put("gsiRangeKeyAttribute", AttributeValue.builder().s(gsiRangeKey).build());
// TABLE PRIMARY KEY
startKey.put("tablePrimaryKeyAttribute", AttributeValue.builder().s(tablePrimaryKey).build());
queryBuilder.exclusiveStartKey(startKey);
like image 166
albogdano Avatar answered Oct 19 '22 16:10

albogdano


All the key values of the original table of GSI should be set as start key. If the table has partition key and sort key, then both the key values should be set as start key values.

In the below example:-

1) The videos table has videoid as partition key and category as sort key

2) The GSI is defined with category as partition key and videoid as sort key

The below code queries the GSI by category value with start key set (i.e. both partition and sort key).

I can reproduce your error when I don't populate the partition or sort key.

Sample code:-

public QueryResultPage<VideoDynamoMappingAdapter> findVideosByCategoryUsingGSIAndMapperWithStartKey(
        String category) {
    DynamoDBMapper dynamoDBMapper = new DynamoDBMapper(dynamoDBClient);
    QueryResultPage<VideoDynamoMappingAdapter> queryResult = null;
    Map<String, AttributeValue> vals = new HashMap<>();
    vals.put(":val1", new AttributeValue().withS(category));
    DynamoDBQueryExpression<VideoDynamoMappingAdapter> queryExp = new DynamoDBQueryExpression<VideoDynamoMappingAdapter>()
            .withKeyConditionExpression("category = :val1").withIndexName("VideoCategoryGsi")
            .withExpressionAttributeValues(vals).withScanIndexForward(false).withConsistentRead(false).withLimit(1);

    Map<String, AttributeValue> startKey = new HashMap<>();

    startKey.put("videoid", new AttributeValue().withS("2"));
    startKey.put("category", new AttributeValue().withS("Thriller"));

    queryExp.setExclusiveStartKey(startKey);

    queryResult = dynamoDBMapper.queryPage(VideoDynamoMappingAdapter.class, queryExp);

    System.out.println("Result size ===>" + queryResult.getResults().size());
    System.out.println("Last evaluated key ===>" + queryResult.getLastEvaluatedKey());

    for (VideoDynamoMappingAdapter videoDynamoMappingAdapter : queryResult.getResults()) {
        System.out.println("Video data ===>" + videoDynamoMappingAdapter.toString());
    }

    return queryResult;

}
like image 28
notionquest Avatar answered Oct 19 '22 17:10

notionquest


When considering indexes last evaluated key consist of two things.

  1. table keys
  2. index keys

you can simply Sysout last evaluated key (EvaluatedKeyMap) from QueryResultPagelast and get the pattern.

in your case, when you are creating exclusiveStartKey add last evaluated "userId" too. exclusiveStartKey.put("userId", last evaluated user id attibute value);

ex.

exclusiveStartKey.put("id", new AttributeValue().withS(lastUserId));
like image 23
Naween Niroshan Avatar answered Oct 19 '22 16:10

Naween Niroshan