AWS CLI DynamoDB Query Example

Tutorial: AWS CLI DynamoDB Query Example

A database is simply a collection of data. This data can range from people’s names to the physical properties of matter.

On the other hand, a database management system (i.e. MySql, MongoDB…) enable users to access and manipulate data via queries. A good database management systems enables an administrator to grant granular permissions to access the data contained within the database.

There are two kinds of databases, relational databases and non relational databases. Relational databases are tabular. That is to say, all entries must follow a predefined schema.

Suppose we had the following table.

If we wanted to add another attribute for Michelle such as income, it would have to be added for all the users within the table.

Now imagine we kept track of all the comments made by the different users in a separate table.

In order to relate data, we must use a join table. A join table maps the relationship between two or more distinct tables.

On the other hand, non relational databases are more flexible. Rather than storing data as a set of tables, it’s stored as an object. The most common notation for non relational databases is JSON.

Amazon Web Services provides a slew of database services.

In the proceeding tutorial, we’ll take a look at how to use a non relational database known as DynamoDB. DynamoDB stores data as groups of attributes, known as items. Items are distributed across 10-GB storage units, called partitions. Each table has one or more partitions.

All items with the same partition key are stored together, and are ordered by the sort key value. DynamoDB uses the partition key’s value as an input to a hash function. The output from the hash function is used to determine the item’s location.

That’s enough background information, let’s create a table to store information about cats. Every cat will have a name and an age.

You want to avoid using any of the reserved words in DynamoDB as attribute names. Visit the proceeding link for a complete list of the reserved words.

Reserved Words in DynamoDB - Amazon DynamoDB
_Learn about reserved words in Amazon DynamoDB_docs.aws.amazon.com

There are two key types :

• HASH — partition key
• RANGE — sort key

When you issue HiveQL statements against the external DynamoDB table, if theReadCapacityUnits and WriteCapacityUnits are too low the request will be throttled, resulting in slow HiveQL performance.

For example, suppose that you have provisioned 100 read capacity units for your DynamoDB table. This will let you read 409,600 bytes per second (100 × 4 KB read capacity unit size). Now suppose that the table contains 20 GB of data (21,474,836,480 bytes) and you want to use the SELECT statement to select all of the data using HiveQL. You can estimate how long the query will take to run like this:

21,474,836,480 / 409,600 = 52,429 seconds = 14.56 hours

aws dynamodb create-table --table-name Cats --attribute-definitions AttributeName=Age,AttributeType=N AttributeName=CatName,AttributeType=S --key-schema AttributeName=Age,KeyType=HASH AttributeName=CatName,KeyType=RANGE --provisioned-throughput ReadCapacityUnits=5,WriteCapacityUnits=5

We can have multiple tables for a given database. Run the following command to list all the tables within our database.

aws dynamodb list-tables

Next, we’ll add an entry to our Cats table.

aws dynamodb put-item --table-name Cats --item file://cat.json

Where cat.json contains the following:

{

 "CatName": {"S": "Mimi"},

 "Age": {"N": "5"}

}

The scan command enables us to view all the data stored in a table.

aws dynamodb scan --table-name Cats

Let’s add a couple of more entries to our table of cats using the same method as before.

{

 "CatName": {"S": "Joe"},

 "Age": {"N": "1"}

}

{

 "CatName": {"S": "Bob"},

 "Age": {"N": "2"}

}

To query a table for a specific entry, run:

aws dynamodb query --table-name Cats --key-condition-expression "CatName=:name and Age= :age" --expression-attribute-values file://expression_attributes.json

Where expression_attributes_.json_ contains the following:

{

 ":name": {"S": "Mimi"},

 ":age": {"N": "5"}

}

We can delete entries in similar fashion to how they were created.

aws dynamodb delete-item --table-name Cats --key file://cat.json

Run aws dynamodb delete-table --table-name Cats to delete the table and all of its entries.

Cory Maklin
_Sign in now to see your channels and recommendations!_www.youtube.com