Amazon DynamoDB on Mobile – Part 4: Local Secondary Indexes

In our previous posts (Part 1, Part 2, and Part 3), we discussed how we can read from and write to an Amazon DynamoDB table using the AWS Mobile SDKs. In this post, we discuss some additional options for our data model.

A User Data Table Revisited

In Part 1, we discussed a user data table where we had multiple users’ data existing in the same table. Our model looked like the following:

• UserId – our Hash Key to uniquely identify the user, stored as a numeric value.
• RecordId – our Range Key to identify a given record for the user, stored as a string.
• Data – the data for the given record and user. As it is not being indexed, we can use any type supported by DynamoDB.

This model works well if we have a persistent connection to the Internet, and we can always query the table for fresh content. But what if we want to also access this data offline and from multiple devices? In order to see if we have any new data, we’d have to load the entirety of the user’s record via a Query operation and compare with the local data. We can make some minor modifications to our model and add the following columns to solve this issue:

• Version – A counter for recording how many times the record has been updated.
• LastModified – A date field for recording the time the data was last modified by a user.
• LastModifiedBy – An identifier for the device that was used to modify the record.
• LastWritten – A date field for recording the time the record was last written to DynamoDB.

A slice of our table might look like the following:

Optimizing Table Queries

By default, we can only query against our tables composite key, UserId and RecordId. This would limit our ability to load data to only the entirety of the user’s record. We can modify our table and add a local secondary index on our table to optimize the queries. We can add a couple of indexes to our table that can help optimize queries we might be interested in:

• By adding an index on LastWritten, we can allow queries for records written by our user since a last known sync time.
• By adding an index on LastModifiedBy, we can allow queries for records written by certain devices.

Adding Local Secondary Indexes

Indexes must be added at table creation time. We recommend that you use the AWS Console, which will walk you through adding indexes.

Querying for New Records

When querying with local secondary indexes, we continue to use the same Query API, but now when creating the request, we need to specify the index we want to query against as well as additional query parameters. In order to query for new records for a user, we will continue to supply the UserId, but additionally we will supply a greater-than condition (GT) for the LastWritten column, using the LastWritten-index index.

iOS

// Create our dictionary of values
NSDictionary *conditions = [NSMutableDictionary new];

// Specify our key conditions ("UserId" == 1234)
DynamoDBCondition *userIdCondition = [DynamoDBCondition new];
condition.comparisonOperator = @"EQ";
DynamoDBAttributeValue *userId = [[DynamoDBAttributeValue alloc] initWithN:@"1234"];
[userIdCondition addAttributeValueList:userId];
[conditions setObject:userIdCondition forKey:@"UserId"];

// Specify we want new records (after 1373313800 epoch seconds)
DynamoDBCondition *lastWrittenCondition = [DynamoDBCondition new];
lastWrittenCondition.comparisonOperator = @"GT";
DynamoDBAttributeValue *time = [[DynamoDBAttributeValue alloc] initWithN:@"1373313800"];
[lastWrittenCondition addAttributeValueList:time];
[conditions setObject:lastWrittenCondition forKey:@"LastWritten"];

NSMutableDictionary *queryStartKey = nil;
do {
    DynamoDBQueryRequest *queryRequest = [DynamoDBQueryRequest new];
    queryRequest.tableName = @"UserTableExample";
    queryRequest.exclusiveStartKey = queryStartKey;

    // supply our conditions and specify the index to use
    queryRequest.keyConditions = conditions;
    queryRequest.indexName = @"LastWritten-index";

    // process the query as normal
    DynamoDBQueryResponse *queryResponse = [[Constants ddb] query:queryRequest];

    // Each item in the result set is a NSDictionary of DynamoDBAttributeValue
    for (NSDictionary *item in queryResponse.items) {
        DynamoDBAttributeValue *recordId = [item objectForKey:@"RecordId"];
        NSLog(@"record id = '%@'", recordId.s);
    }
    
    // If the response lastEvaluatedKey has contents, that means there are more results
    queryStartKey = queryResponse.lastEvaluatedKey;

} while ([queryStartKey count] != 0);

Android

// Create our map of values
Map keyConditions = new HashMap();

// Specify our key conditions ("UserId" == 1234)
Condition hashKeyCondition = new Condition()
    .withComparisonOperator(ComparisonOperator.EQ.toString())
    .withAttributeValueList(new AttributeValue().withN("1234"));
keyConditions.put("UserId", hashKeyCondition);

// Specify we want new records (after 1373313800 epoch seconds)
Condition timeCondition = new Condition()
    .withComparisonOperator(ComparisonOperator.GT.toString())
    .withAttributeValueList(new AttributeValue().withN("1373313800"));
keyConditions.put("LastWritten", timeCondition);
 
Map lastEvaluatedKey = null;
do {
    QueryRequest queryRequest = new QueryRequest()
            .withTableName("UserTableExample")
            .withKeyConditions(keyConditions)
            .withExclusiveStartKey(lastEvaluatedKey)
            .withIndexName("LastWritten-index");
 
    QueryResult queryResult = client.query(queryRequest);
    for (Map item : queryResult.getItems()) {
        // name is a string, so it's stored value will be in the S field
        Log.i(LOG_TAG, "record id = '" + item.get("RecordId").getS() + "'");
    }
 
    // If the response lastEvaluatedKey has contents, that means there are more results
    lastEvaluatedKey = queryResult.getLastEvaluatedKey();
} while (lastEvaluatedKey != null);

Querying for Records Modified by Specific Devices

Once we have our local secondary indexes in place, we can create queries against them just as we would for our range key, using any of the supported comparison operators. To get records modified by a specific device, we simply make use of the equals (EQ) operator on LastModifiedBy column, using the LastModifiedBy-index index.

iOS

// Create our dictionary of values
NSDictionary *conditions = [NSMutableDictionary new];

// Specify our key conditions ("UserId" == 1234)
DynamoDBCondition *userIdCondition = [DynamoDBCondition new];
condition.comparisonOperator = @"EQ";
DynamoDBAttributeValue *userId = [[DynamoDBAttributeValue alloc] initWithN:@"1234"];
[userIdCondition addAttributeValueList:userId];
[conditions setObject:userIdCondition forKey:@"UserId"];

// Specify we want records modified by other devices ("LastModifiedBy" == "Bob's iPhone")
DynamoDBCondition *lastWrittenCondition = [DynamoDBCondition new];
lastWrittenCondition.comparisonOperator = @"EQ";
DynamoDBAttributeValue *deviceName = [[DynamoDBAttributeValue alloc] initWithS:@"Bob's iPhone"];
[lastWrittenCondition addAttributeValueList:deviceName];
[conditions setObject:lastWrittenCondition forKey:@"LastModifiedBy"];

NSMutableDictionary *queryStartKey = nil;
do {
    DynamoDBQueryRequest *queryRequest = [DynamoDBQueryRequest new];
    queryRequest.tableName = @"UserTableExample";
    queryRequest.exclusiveStartKey = queryStartKey;

    // supply our conditions and specify the index to use
    queryRequest.keyConditions = conditions;
    queryRequest.indexName = @"LastModifiedBy-index";

    // process the query as normal
    DynamoDBQueryResponse *queryResponse = [[Constants ddb] query:queryRequest];

    // Each item in the result set is a NSDictionary of DynamoDBAttributeValue
    for (NSDictionary *item in queryResponse.items) {
        DynamoDBAttributeValue *recordId = [item objectForKey:@"RecordId"];
        NSLog(@"record id = '%@'", recordId.s);
    }
    
    // If the response lastEvaluatedKey has contents, that means there are more results
    queryStartKey = queryResponse.lastEvaluatedKey;

} while ([queryStartKey count] != 0);

Android

// Create our map of values
Map keyConditions = new HashMap();

// Specify our key conditions ("UserId" == 1234)
Condition hashKeyCondition = new Condition()
    .withComparisonOperator(ComparisonOperator.EQ.toString())
    .withAttributeValueList(new AttributeValue().withN("1234"));
keyConditions.put("UserId", hashKeyCondition);

// Specify we want records modified by other devices ("LastModifiedBy" == "Bob's iPhone")
Condition lastModCondition = new Condition()
    .withComparisonOperator(ComparisonOperator.EQ.toString())
    .withAttributeValueList(new AttributeValue().withS("Bob's iPhone"));
keyConditions.put("LastModifiedBy", lastModCondition);
 
Map lastEvaluatedKey = null;
do {
    QueryRequest queryRequest = new QueryRequest()
            .withTableName("UserTableExample")
            .withKeyConditions(keyConditions)
            .withExclusiveStartKey(lastEvaluatedKey)
            .withIndexName("LastModifiedBy-index");
 
    QueryResult queryResult = client.query(queryRequest);
    for (Map item : queryResult.getItems()) {
        // name is a string, so it's stored value will be in the S field
        Log.i(LOG_TAG, "record id = '" + item.get("RecordId").getS() + "'");
    }
 
    // If the response lastEvaluatedKey has contents, that means there are more results
    lastEvaluatedKey = queryResult.getLastEvaluatedKey();
} while (lastEvaluatedKey != null);

Conclusion

This concludes our series covering Amazon DynamoDB on Mobile. As more features are added to DynamoDB and/or to the AWS Mobile SDKs, we will continue to post how you can leverage these features in your mobile apps. If you have suggestions for new features or posts you’d like to see, please consider leaving a comment here or in our forum.

Further Reading

• Amazon DynamoDB on Mobile – Part 1: Loading Data
• Amazon DynamoDB on Mobile – Part 2: Advanced Data Loading Options
• Amazon DynamoDB on Mobile – Part 3: Writing Data