Querying your DynamoDB Data

DynamoDB is a great developer NoSQL database. It let's you have much more flexibility that SQL in terms of structuring your data. But, a drawback is often that it can be hard to query. That is until Commandeer came around. Our Query Runner provides a very powerful way to access all your data in an easy, efficient, and intuitive way.

---

Query Runner Breakdown

Below is a list of features in the Query Runner.

Table Options

• Add Record - (button) add a new JSON record to the table
• Add GSI - (button) add a new Global Secondary Index to the table (this allows you to perform a query against the index)
• Seed - (button) seed the table with data
• Truncate - (button) truncate or delete all the data in the table but it does not delete the table or indexes
• Delete - (button) deletes the table and all it's data
• Refresh - (button) refresh the table, retriieving and new indexes and the first 100 records or 1MB of data in the table
• Docs - (button) go to the DynamoDB docs
• Open in AWS Console - (button) open DynamoDB in the AWS Console for the currently selected account (you will need to be logged in to the current account on AWS to view it
• IaC Snippets - (button) view the IaC snippets for creating the DynamoDB table and it's Global Secondary Indexes in Ansible, Serverless Framework, and Terraform
• Table - (combobox) allows you to select which table you want to query
• Column - (combobox) allows you to select which column to query on. If the column is a GSI, then it will allow you to query, otherwise it will be a scan (more on this later)
• Operation - (combobox) what operation to perform on the column
  • contains - contains certain characters
  • between - between two items you specify
  • begins with - begins with certain characters
  • in - in a list of items you specify
  • = - exactly matches the characters or number you specify
  • <> - does not match the characters or number you specify
  • "<" - is less than the number you specify
  • "<" - is greater than the number you specify
  • "<=" - is less than or equal to the number you specify
  • ">=" - is greater than or equal to the number you specify
  • attribute exists - returns only records that the field exists
  • attribute not exists - returns only records that the field does not exist
  • attribute type - returns records where the attribute is the type you specify
• Scan - (button) or Query (button) - this button changes depending on if you select a column that is a GSI or not
• Load More - (button) all queries return up to 100 records or 1MB in size of the data, this button allows you to page through the data
• Clear - (button) clear the currently selected search criteria
• Filter - (textbox) allows you to filter data in the currently returned data set in the table (Note: this does not filter in the actual DynamoDB table, it just filters all records that are currently in the table on the page
• Format Data - (toggle) format data in the table. This will show checkboxes for true false, format dates, etc.
• Download - (button) allows you to download the data in the current table or just a single page of data in the table as either json or csv
• Share - (button) allows you to share the data in the table as json in email, slack, or sms (Note: you must connect a Sendgrid,Slack, and/or Twilio to the current account for it to send)

Record Options

Each row of data in the table also has options that you can select.

• View - Go to the selected record
• Edit - Edit the JSON record
• Copy - copy the record to your clipboard
• Share - share the record in email, slack, or sms
• Delete - delete the record

---

Scan vs. Query

To actually query your data, you have two options. Scanning means that the query is not using an index to fetch results. Query means that it uses the Primary Key index or a Global Secondary Index to perform the action.

Scanning

Below you can see that we are scanning the User table for any record that the company column begins with Acme.

The caveat here, is that the scan does not work exactly like you might think. It actually will grab either the first 100 records or 1MB of data and then apply the filter of company to that. This is how DynamoDB works, so it is unfortunately a nuance that can be difficult to deal with. The solution in Commandeer, is the Load More button. You will have to continue to press this button until your record is found, or it has scanned the whole table.Below you can see the Load More button. You can press this to continue scanning your table.

Querying

If you select the primary key in the table, or a column that has a Global Secondary Index, then the scan button will change to the query button.

This will hit the index on the table and return back the matching result(s).

---

Resulting Data

The data is returned into a table where you can view the records. We attempt to format many of the columns for you such as dates, booleans, and AWS regions. You can always turn this off by toggling the Format Data button. Below you can see records in the table with a local filter as well.

The resulting data will also show up in the side navigation, which can be handy if you are bouncing around between tables.

---

Conclusion

DynamoDB is our go to NoSQL tool. We built the query runner to solve issues we had with using it as a data source. We hope you find it as useful as we do for all your data needs.