This is the 2nd article in the series for Data Pane 1 (read-write) operations.
In this article, I am going to show you such operations on DynamoDB item collections using Query. Some of the criteria which can be used in the Query operations are - Key condition expression and Filter Expression, with options, such as max items, scan-index-forward, no-scan-index-forward, Count and ScannedCount.
In this article, I am not explaining the terminology and the concepts used in DynamoDB Tables. Please visit my previous articles for this - DynamoDB and its Control Pane Operations - 1 and DynamoDB and its Control Pane Operations - 2.
Item Collections
They are groups of Items that share a same Partition Key.
They can only exist in tables that have both a Partition Key and a Sort Key.
We can read all or part of an Item Collection using the Query.
The word “query” is generally used to mean “reading data from a database”, but in
DynamoDB “query”has a specific meaning: to read all or part of an Item Collection.
Key Condition Expression
Specify a
Key Condition Expressionto get the items to be read from the table or index.This can be compared to SQL, where we would say
this is the part of the WHERE clause that acts on the Partition Key and Sort Key attributes.With just the Partition Key value of from a Key Condition Expression, gets
ALLthe items in the item collection.With the Partition and Sort Key Condition from a Key Condition Expression, gets a
subsetof the rows in item collection.Possible sort key conditions are
=, <, <=, >, >=, BETWEEN, and BEGINS_WITH.
Filter Expression
It removes some items from the Result Set returned by the Query.
This can be compared in SQL to
this is the part of the WHERE clause that acts on the non-Key attributes.
Max items
- If we want to limit the number of items then we use the
--max-itemsoption.
Scan index forward
If we want to order items in ascending order of the sort key, then we would use the
--scan-index-forwardoption.This can be compared in SQL to
ORDER BY Name ASC LIMIT 1, when --max-items limits to 1.
No Scan index forward
If we want DynamoDB to order items in descending order of the sort key, then we would use the
--no-scan-index-forwardoption.This can be compared in SQL to
ORDER BY Name DESC LIMIT 1, when --max-items limits to 1.
ScannedCount and Count
Counts the items in the results
In the ScannedCount - When the Key Condition Expression matches 2 items, then it is 2.
In the Count - When the Filter Expression reduces the result set to 1 item, then it is 1.
If you don't use a filter expression, ScannedCount and Count have the same value.
Let’s get started!
Please visit my GitHub Repository for DynamoDB articles on various topics being updated on constant basis.
Pre-requisites:
- AWS user account with admin access, not a root account.
- Cloud9 IDE with AWS CLI.
Objectives:
1. Create a DynamoDB table
2. Add 3 items to the table
3. Query the table with Key Condition Expression with just the Partition Key
4. Query the table with Key Condition Expression with Partition Key and Sort key, using "=" option
5. Query the table to limit the number of items with Max items and Scan index forward
6. Query the table to limit the number of items with Max items and Scan index forward
7. Query the table to limit the number of items with Max items and No Scan index forward
8. Query the table to count the items in the result set
Resources Used:
Amazon DynamoDB Developer Guide
Steps for implementation to this project:
- Copy the and paste the code in your AWS Cloud9 command prompt.
1. Create a DynamoDB table
aws dynamodb create-table \
--table-name Movies \
--attribute-definitions \
AttributeName=Title,AttributeType=S \
AttributeName=Review,AttributeType=S \
--key-schema \
AttributeName=Title,KeyType=HASH \
AttributeName=Review,KeyType=RANGE \
--provisioned-throughput \
ReadCapacityUnits=10,WriteCapacityUnits=5
aws dynamodb wait table-exists --table-name Movies
2. Add 3 items to the table
aws dynamodb put-item \
--table-name Movies \
--item '{
"Title": {"S": "Abhimaan"},
"Review": {"S": "Excellent"},
"Actor": {"S": "Big B"},
"PostedBy": {"S": "User A"},
"Songs": {"L": [ {"S": "Song1"},
{"S": "Song2"}
]
}
}'
aws dynamodb put-item \
--table-name Movies \
--item '{
"Title": {"S": "Abhimaan"},
"Review": {"S": "Terrific"},
"Actor": {"S": "Jaya"},
"PostedBy": {"S": "User B"},
"Songs": {"L": [ {"S": "Song1"},
{"S": "Song2"}
]
}
}'
aws dynamodb put-item \
--table-name Movies \
--item '{
"Title": {"S": "Abhimaan"},
"Review": {"S": "Too Good"},
"Actor": {"S": "Bindu"},
"PostedBy": {"S": "User A"},
"Songs": {"L": [ {"S": "Song1"},
{"S": "Song2"}
]
}
}'
3. Query the table with Key Condition Expression with just the Partition Key
You should see 3 items in the result set, as the primary key
Titlereferences 3 items in Movies tableRefer the Movies table to check
--item '{
"Title": {"S": "Abhimaan"}
aws dynamodb query \
--table-name Movies \
--key-condition-expression 'Title = :Title' \
--expression-attribute-values '{
":Title" : {"S": "Abhimaan"}
}' \
--return-consumed-capacity TOTAL
4. Query the table with Key Condition Expression with Partition Key and Sort key, using "=" option
- You should see only 1 item in the result set, as the primary key
Titleand sort keyReviewreferences 1 item in Movies table Refer the Movies table to check
--item '{
"Title": {"S": "Abhimaan"},
"Review": {"S": "Too Good"}
aws dynamodb query \
--table-name Movies \
--key-condition-expression 'Title = :Title and Review = :rv' \
--expression-attribute-values '{
":Title" : {"S": "Abhimaan"},
":rv" : {"S": "Too Good"}
}' \
--return-consumed-capacity TOTAL
5. Query the table with Filter Expression to limit our results based on non-key attributes
In the table
Movies,Titlefrom non-key attributeActorBindu:--item '{
"Title": {"S": "Abhimaan"},
"Actor": {"S": "Bindu:}
aws dynamodb query \
--table-name Movies \
--key-condition-expression 'Title = :Title' \
--filter-expression 'Actor = :person' \
--expression-attribute-values '{
":Title" : {"S": "Abhimaan"},
":person" : {"S": "Bindu"}
}' \
--return-consumed-capacity TOTAL
6. Query the table to limit the number of items with Max items and Scan index forward
It gets the 1st item from the table
--item '{
"Title": {"S": "Abhimaan"},
"Actor": {"S": "Big B:}
aws dynamodb query \
--table-name Movies \
--key-condition-expression 'Title = :Title' \
--expression-attribute-values '{
":Title" : {"S": "Abhimaan"}
}' \
--max-items 3 \
--scan-index-forward \
--return-consumed-capacity TOTAL
7. Query the table to limit the number of items with Max items and No Scan index forward
It gets the last item from the table
--item '{
"Title": {"S": "Abhimaan"},
"Actor": {"S": "Bindu:}
aws dynamodb query \
--table-name Movies \
--key-condition-expression 'Title = :Title' \
--expression-attribute-values '{
":Title" : {"S": "Abhimaan"}
}' \
--max-items 1 \
--no-scan-index-forward \
--return-consumed-capacity TOTAL
8. Query the table to count the items in the result set
** Showing ScannedCount** When the Key Condition Expression matches 3 items, then it is 3.
- --item '{ "Title": {"S": "Abhimaan"}
- Count 3
- ScannedCount 3
aws dynamodb query \
--table-name Movies \
--key-condition-expression 'Title = :Title' \
--expression-attribute-values '{
":Title" : {"S": "Abhimaan"}
}' \
--return-consumed-capacity TOTAL
** Showing Count** When the Filter Expression reduces the result set to 1 item, then it is 1.
- --item '{ "Title": {"S": "Abhimaan"}, "Actor": {"S": "Bindu:}
- Count 1
- ScannedCount 3
aws dynamodb query \
--table-name Movies \
--key-condition-expression 'Title = :Title' \
--filter-expression 'Actor = :person' \
--expression-attribute-values '{
":Title" : {"S": "Abhimaan"},
":person" : {"S": "Bindu"}
}' \
--return-consumed-capacity TOTAL
Cleanup
- Delete the table
Movies
What we have done so far
We have successfully demonstrated Data Pane operations on DynamoDB item collections using Query. We used the criteria - Key condition expression and Filter Expression, with options, such as max items, scan-index-forward, no-scan-index-forward, Count and ScannedCount.
Top comments (0)