How to Use the Console to Perform a Database Query in AWS
Finding data in AWS that you need can become problematic. A few records, or even a few hundred records, might not prove to be much of a problem. However, hundreds of thousands of records would be a nightmare to search individually, so you need to have some method of finding the data quickly. This assistance comes in the form of a query. DynamoDB actually supports two query types:
- Scan: Uses a filtering approach to find entries that match your criteria.
- Query: Looks for specific attribute entries.
The examples here employ two test entries in the TestDB table. The essential entries are the EmployeeID, EmployeeName, and EmploymentDate attributes, shown here:
The two methods of querying data have advantages and disadvantages, but what you use normally comes down to a matter of personal preference.
Using a scan
Scans have the advantage of being a bit more free-form than queries. You filter data based on any field you want to use. To scan the data, you choose either a [Table] entry that contains the primary key and sort key, or an [Index] entry that sorts the data based on a secondary index that you create.
Using a scan means deciding on what kind of filtering to use to get a desired result. The following steps give you a template for a quick method of performing a scan. (Your steps will vary because you need to provide specific information to make the procedure work.)
- Choose Scan in the first field.
- Select either a [Table] or [Index] entry in the second field. The entry you choose determines the output’s sort order. In addition, using the correct entry speeds the search because DynamoDB will have a quick method of finding the data.
- Click Add Filter (if necessary) to add a new filter entry. You can remove filters by clicking the X on the right side of the filter’s entry.
- Choose an attribute, such as in the first Filter field.
- Select the attribute’s type in the second Filter field.
- Specify a logical relationship in the third Filter field.
This entry can be tricky, especially when working with strings. For example, if you want to find all the entries that begin with the name George, you choose the Begins With entry in this field. However, if you want to find all the employees hired after 11/08/2016, use the > entry instead.
- Type a value for the fourth Filter field, such as George.
- Click Start Search.
You see the entries that match your filter criteria. You can use as many filters as desired to whittle the data down to just those items you really want to see. Simply repeat Steps 3 through 8 to achieve the desired result.
Using a query
Queries are stricter and more precise than scans. When you perform a query, you look for specific values. Notice that the key value is precise. You can’t look for a range of employment dates; instead, you must look for a specific employment date. In addition, the employment date is a mandatory entry; you can’t perform the query without it. However, you can also choose an optional sort key and add filtering (as found with scans) as well.
Using a query is like asking a specific question. You can ask which employees, who were hired on 02/28/2015, have a name that begins with Hal. In this case, you see just one record. Scans can produce the same result by employing multiple filters. The advantage of a query is that it forces you to think in a particular way; also, because you use attributes that are indexed, using a query is faster than a scan.