SELECT¶
Synopsis¶
SELECT
[ CONSISTENT ]
attributes
FROM tablename
[ KEYS IN primary_keys | WHERE expression ]
[ USING index ]
[ LIMIT limit ]
[ SCAN LIMIT scan_limit ]
[ ORDER BY field ]
[ ASC | DESC ]
[ THROTTLE throughput ]
[ SAVE filename]
Examples¶
SELECT * FROM foobars SAVE out.p;
SELECT * FROM foobars WHERE foo = 'bar';
SELECT count(*) FROM foobars WHERE foo = 'bar';
SELECT id, TIMESTAMP(updated) FROM foobars KEYS IN 'id1', 'id2';
SELECT * FROM foobars KEYS IN ('hkey', 'rkey1'), ('hkey', 'rkey2');
SELECT CONSISTENT * foobars WHERE foo = 'bar' AND baz >= 3;
SELECT * foobars WHERE foo = 'bar' AND attribute_exists(baz);
SELECT * foobars WHERE foo = 1 AND NOT (attribute_exists(bar) OR contains(baz, 'qux'));
SELECT 10 * (foo - bar) FROM foobars WHERE id = 'a' AND ts < 100 USING ts-index;
SELECT * FROM foobars WHERE foo = 'bar' LIMIT 50 DESC;
SELECT * FROM foobars THROTTLE (50%, *);
Description¶
Query a table for items.
Parameters¶
- CONSISTENT
- If this is present, perform a strongly consistent read
- attributes
- Comma-separated list of attributes to fetch or expressions. You can use the
TIMESTAMP
andDATE
functions, as well as performing simple, arbitrarily nested arithmetic (foo + (bar - 3) / 100
).SELECT *
is a special case meaning ‘all attributes’.SELECT count(*)
is a special case that will return the number of results, rather than the results themselves. - tablename
- The name of the table
- index
- When the WHERE expression uses an indexed attribute, this allows you to manually specify which index name to use for the query. You will only need this if the constraints provided match more than one index.
- limit
- The maximum number of items to return.
- scan_limit
- The maximum number of items for DynamoDB to scan (not necessarily the number of matching items returned).
- ORDER BY
- Sort the results by a field.
Warning
Using ORDER BY with LIMIT may produce unexpected results. If you use ORDER BY on the range key of the index you are querying on, it will work as expected. Otherwise, DQL will fetch the number of results specified by the LIMIT and then sort them.
- ASC | DESC
- Sort the results in ASCending (the default) or DESCending order.
- THROTTLE
- Limit the amount of throughput this query can consume. This is a pair of
values for
(read_throughput, write_throughput)
. You can use a flat number or a percentage (e.g.20
or50%
). Using*
means no limit (typically useless unless you have set a default throttle in the Options). - SAVE
- Save the results to a file. By default the items will be encoded with pickle, but the ‘.json’ and ‘.csv’ extensions will use the proper format. You may also append a ‘.gz’ or ‘.gzip’ afterwards to gzip the results. Note that the JSON and CSV formats will be lossy because they cannot properly encode some data structures, such as sets.
Where Clause¶
If provided, the SELECT operation will use these constraints as the
KeyConditionExpression
if possible, and if not (or if there are constraints
left over), the FilterExpression
. All query syntax is pulled directly from
the AWS docs:
http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/QueryAndScan.html
In general, you may use any syntax mentioned in the docs, but you don’t need to
worry about reserved words or passing in data as variables like :var1
. DQL
will handle that for you.