AWS DynamoDB – Working with Queries

 Amazon DynamoDB is a NoSQL managed database service provided by Amazon that stores semi-structured data like key-value pairs. A DynamoDB table consists of items. Each item consists of one partition key and one or more attributes. An example of an item is given below:

Example:
{
  "MovieID": 101,
  "Name": "The Shawshank Redemption",
  "Rating": 9.2,
  "Year": 1994
}

In the above example, MovieID is the partition key. 

A partition key is used to differentiate between items. A query operation in DynamoDB finds items based on primary key values. The name of the partition key attribute and a single value for that attribute must be provided. The query returns all items searched against that partition key value.

Create a table and add values :

A table say, Movies, with partition key as MovieID has been created already. No two items can have the same partition key. Add few items to query upon. A table has already been created with items in it. See the below image:

Query on the table :

To query upon items in the table, select Query from the dropdown in the items tab. By default, a query will always have a partition key as one of the search filters. We can add one or more attributes to refine the search. See the below image:

In the above image we see that when we enter 50 for primary key MovieID, we obtain 1 record which is shown above in the image.

Adding Filters to Query:

To refine our search we can add one or more filters. In filter, we select an attribute and provide value against it. The query results returned is determined by a filter expression. All of the other results are discarded. A filter expression is applied before the results are returned, but after a Query finishes. Hence, a Query consumes the same amount of Read capacity, regardless of whether a filter expression is present or not. See the below image:

In the above image, we see that in the filter, Rating has been provided and the condition is that it should be greater than or equal to 8. Thus, we get one search result.

Limiting the number of items in ResultSet:

The Query operation allows you to limit the number of items that it reads. To do this, set the Limit parameter to the desired number of items that you want. For example, suppose that you Query a table, with a limit value of 8, and without a filter expression. The Query result contains the first eight items from the table that match the key condition expression from the request. The limit can only be used in Amazon Command Line Interface (CLI).

Paging:

This feature is available on Amazon CLI (Command Line Interface). When data retrieved is larger than 1 MB in the result set, then the result set is divided into pages, each page containing up to 1 MB. For instance, if 3 MB data is retrieved then there will be at least 3 pages.

Counting Item in ResultSet:

If the size of the Query result set is larger than 1 MB, ScannedCount and Count represent only a partial count of the total items. You need to perform multiple Query operations to retrieve all items in the result set. 

Capacity Units Consumed:

A read capacity unit speaks for one strongly consistent read per second, or two eventually consistent reads per second, for an item up to the size of 4 KB. A scan operation does not return any data on how much the read capacity units are consumed. However, by specifying the ReturnConsumedCapacity parameter in a Scan request we can obtain this information or change the read capacity unit in the capacity tab of the table. See the below image:

Read Consistency: 

By default, a scan operation performs eventually consistent reads. Meaning, the scan results might not include changes due to the recently completed PutItem or UpdateItem request. If required strong consistent reads, as of the time that the Scan begins, then set ConsistentRead parameter to true in the Scan request. By doing so, it ensures that all the write operations that completed before the Scan began are included in the Scan result set.

How to query the table using attributes other than the primary key.:

Using concept of “secondary indexes” DynamoDB allows you to create one or more secondary indexes on a table, which can allow you to query the table using attributes other than the primary key. This can be useful if you want to perform queries on the table based on attributes other than the primary key, or if you want to support multiple query patterns on the same table. There are two types of secondary indexes in DynamoDB: global secondary indexes and local secondary indexes. Global secondary indexes allow you to query the table using an alternate key, while local secondary indexes allow you to query the table using an alternate key that is defined within the primary key. Both types of secondary indexes have their own provisioned throughput, which you can set when you create the index.