Query data with DynamoDB Shell – a command line interface for Amazon DynamoDB

For newcomers to DynamoDB, the learning curve can be steep, so we developed DynamoDB Shell (ddbsh), an open-source command line interface for Amazon DynamoDB. One of the objectives of ddbsh is to provide a simple and intuitive environment for newcomers to DynamoDB that allows them to get started by running familiar SQL-like commands. The query language in ddbsh is derived from SQL and has DynamoDB-specific extensions.

For example, you can use ddbsh to create a table by running a command such as:

create table ddbsh_demo (id number) primary key (id hash);

You can get a quick introduction to ddbsh from Hello DynamoDB shell, and dive a little deeper into creating and querying global secondary indexes in Getting started with dynamodb-shell. The ddbsh README.md file has detailed command usage and examples.

This post covers two topics. First, you learn how connect to DynamoDB and then to DynamoDB local. Then, you learn a method to filter data using the SQL WHERE clause.

To get started, download the ddbsh source code from AWS Labs on Github and follow the instructions to build it.

Connect to an AWS Region

ddbsh is uses the AWS C++ SDK. You can configure connection parameters as documented for the AWS Command Line Interface. See Environment variables to configure the AWS CLI to learn more.

To connect to a Region

Review the default Region, AWS Access Key, and AWS Secret Key in the AWS configuration file. Refer to the documentation for the exact location of the file on your operating system. The documentation provides specific guidance for DynamoDB local and DynamoDB (web service). You can also use the aws configure command or set environment variables to determine how to connect to DynamoDB.
Check whether you are able to connect to DynamoDB using the AWS CLI. For example, you could run:
```
aws dynamodb list-tables
```
Resolve any errors. After you’ve run the preceding command without any errors, you will be able to connect to your default Region as shown in the interactive session that follows.
```
% ddbsh
ddbsh - version 0.2
us-east-1>
```
At the prompt, you can run commands. For a list of commands, run the HELP command. Commands are terminated with a semicolon (;)
```
us-east-1> help;
HELP - provide help in ddbsh

   HELP <keyword> [keyword [keyword ...]]
[…]
```

Connect to DynamoDB local.

Before you start this step, make sure that you have ddbsh version 0.2 or higher and AWS SDK version 1.11 or higher.

To connect to DynamoDB local

Follow the instructions to install DynamoDB local on your computer.

In a terminal session, set up the environment variables to connect to the DynamoDB local instance.

% export AWS_ACCESS_KEY_ID="fakeMyKeyId"
% export AWS_SECRET_ACCESS_KEY="fakeSecretAccessKey"
% export AWS_DEFAULT_REGION="dynamodb-local"
% export DDBSH_ENDPOINT_OVERRIDE="http://localhost:8000"

In a second terminal session, launch DynamoDB local.

% java -Djava.library.path=./DynamoDBLocal_lib -jar DynamoDBLocal.jar

Return to the first terminal session. You should be able to connect with DynamoDB local by using an AWS CLI command similar to the following:
```
% aws dynamodb  --endpoint-url http://localhost:8000  list-tables
{
    "TableNames": []
}
```
If you’re able to do this, then you can connect to the local instance with ddbsh.
```
% ddbsh
ddbsh - version 0.2
dynamodb-local (*)>
```

The asterisk in parentheses (*) after the region name indicates that a non-standard endpoint is being used. You’re now connected to DynamoDB local. Note that when connected to DynamoDB local, the unit tests will not succeed.

Filter data with the WHERE clause

You can use a WHERE clause with SELECT, UPDATE, DELETE, and UPSERT. In the WHERE clause, you can include multiple logical expressions that can be joined with the logical AND and logical OR. In addition, you can negate a logical expression with the NOT operator.

The following logical expressions are supported.

Logical comparison of the form attribute op value where attribute is a table attribute, op is one of <, >, <=, >=, =, or !=.
attribute_exists(attr) evaluates to true if attr exists in an item.
begins_with(attr, prefix) evaluates to true if the attr begins with prefix.
attr between value and value evaluates to true if attr is between the two values provided.
attribute_type (attr, type) evaluates to true if attr is of the specified type.

Consider this sample data:

dynamodb-local (*)> select * from exprtest;
{a: 2, b: 2}
{a: 1, b: 2}
{a: 3, b: cooperate}
{a: 5, c: coffee}
{a: 4, b: coffee}
dynamodb-local (*)>

Here are some sample queries:

dynamodb-local (*)> select * from exprtest where a > 3 and a < 5;
{a: 4, b: coffee}

dynamodb-local (*)> select * from exprtest where a between 2 and 5;
{a: 2, b: 2}
{a: 3, b: cooperate}
{a: 5, c: coffee}
{a: 4, b: coffee}

dynamodb-local (*)> select * from exprtest where begins_with(b, "co");
{a: 3, b: cooperate}
{a: 4, b: coffee}

dynamodb-local (*)> select * from exprtest where attribute_exists(c);
{a: 5, c: coffee}

dynamodb-local (*)> select * from exprtest where attribute_exists(c) and a != 3;
{a: 5, c: coffee}

dynamodb-local (*)> select * from exprtest where attribute_exists(c) or a = 3;
{a: 3, b: cooperate}
{a: 5, c: coffee}

dynamodb-local (*)> select * from exprtest where attribute_type(b, string);
{a: 3, b: cooperate}
{a: 4, b: coffee}
dynamodb-local (*)>

You can write more complex queries by using using parenthesis to group items.

dynamodb-local (*)> select * from exprtest where attribute_type(b, string) or ( a = 5 and c = "coffee");
{a: 3, b: cooperate}
{a: 5, c: coffee}
{a: 4, b: coffee}

You can use the EXPLAIN command to see how ddbsh will run your query.

dynamodb-local (*)> explain select * from exprtest where attribute_type(b, string) or ( a = 5 and c = "coffee");
Scan({
   "TableName":   "exprtest",
   "ReturnConsumedCapacity":   "NONE",
   "FilterExpression":   "attribute_type(#apaa1, :vpaa1) OR (#apaa2 = :vpaa2 AND #apaa3 = :vpaa3)",
   "ExpressionAttributeNames":   {
      "#apaa1":   "b",
      "#apaa2":   "a",
      "#apaa3":   "c"
   },
   "ExpressionAttributeValues":   {
      ":vpaa1":   {
         "S":   "S"
      },
      ":vpaa2":   {
         "N":   "5"
      },
      ":vpaa3":   {
         "S":   "coffee"
      }
   },
   "ConsistentRead":   false
})
dynamodb-local (*)>

You can use a WHERE clause in an UPDATE as well.

dynamodb-local (*)> help update;
UPDATE - Update items in a table

   UPDATE <name> SET <update_set> [where clause]

   UPDATE <name> REMOVE <remove list> [where clause]

dynamodb-local (*)>

dynamodb-local (*)> update exprtest set b = 15 where a = 5;
UPDATE (0 read, 1 modified, 0 ccf)
dynamodb-local (*)> select * from exprtest where a = 5;
{a: 5, b: 15, c: coffee}
dynamodb-local (*)>

You can delete an attribute. The following example deletes the attribute c.

dynamodb-local (*)> update exprtest remove c where a = 5; 
UPDATE (0 read, 1 modified, 0 ccf)
dynamodb-local (*)> select * from exprtest where a = 5;
{a: 5, b: 15}
dynamodb-local (*)>

Cleanup

Once you’re completed your experiments, remember to remove any tables you might have created to avoid incurring costs. You can use DROP TABLE in ddbsh to do that. You can stop DynamoDB local by going to the terminal where you launched it (the second session), and shut it down with Ctrl-C.

Conclusion

So, there you have it—with ddbsh you can read, modify, or delete data from a table and you can filter which items are acted on by using the WHERE clause, just as you would in standard SQL.

We’ve heard from customers and prospects that they want resources that will help them learn the DynamoDB API. We’re experimenting with some solutions to this such as the EXPLAIN command in ddbsh. We would appreciate your feedback on this tool. What can we add to ddbsh that would make it easier for you to use DynamoDB?

If there are specific functions or functionality that you would like to see implemented in ddbsh, let us know by entering a support ticket or by emailing me directly at amrithie (at) amazon (dot) com. We’re also looking for contributors who are interested in helping extend and maintain ddbsh. Specifically, if you would like to submit pull requests that will make it easier for users to install and use ddbsh (such as by providing packaging scripts) that would be most appreciated. A pull request for a contribution to homebrew has already been submitted. For any other pull requests please feel free to contact me directly at amrithie (at) amazon (dot) com.

A final call to action for all of you. If you would like a homebrew solution to install ddbsh on MacOS, help us by forking, starring, and watching the ddbsh GitHub repository. The pull request to the homebrew repository has been submitted but the ddbsh GitHub repository isn’t well enough known. Therefore, the request is currently blocked. Please help move this request forward.

About the author

Amrith Kumar is a Senior Principal Engineer in Amazon Web Services and works on Amazon DynamoDB.

AWS Database Blog