dynq is an analytic query and data processing CLI tool for DynamoDB. It uses jq
filters to target, transform and/or aggregate items in a given table, and has a number of QoL features including
automatic pagination, table segmentation and index expansion.
- Installation
- Starting out
- Options
- Examples
- Find a film with a G rating
- Calculate total amount of payments in 2007-03
- Calculate average payment amount
- Find the film with the longest length
- Find the three films with the shortest length
- Count films by rating
- Scan from rental 1
- Get films up to film 100
- Find all staff, customers and actors named Jon
- Save each item locally
- Decode a binary attribute
Linux binaries can be downloaded from https://github.com/benward2301/dynq/releases.
docker run -i --rm \
--network=host \
-v ~/.aws:/root/.aws:ro \
-e AWS_ACCESS_KEY_ID=$AWS_ACCESS_KEY_ID \
-e AWS_SECRET_ACCESS_KEY=$AWS_SECRET_ACCESS_KEY \
-e AWS_REGION=$AWS_REGION \
benward2301/dynq --versionYou may wish to alias this command (omitting --version), or copy the docker/dynq script to
somewhere on your path (e.g. /usr/local/bin).
docker run -i --rm `
--network=host `
-v $Env:USERPROFILE\.aws:/root/.aws:ro `
-e AWS_ACCESS_KEY_ID=$Env:AWS_ACCESS_KEY_ID `
-e AWS_SECRET_ACCESS_KEY=$Env:AWS_SECRET_ACCESS_KEY `
-e AWS_REGION=$Env:AWS_REGION `
benward2301/dynq --versionA Linux binary can be built at target/dynq using Docker:
git clone https://github.com/benward2301/dynq.git
cd dynq
docker compose up -d
docker/build/mvn verify
docker compose down --rmi all
target/dynq --versionThen a Docker image can be built:
docker build -t benward2301/dynq -f docker/local/Dockerfile .You can try dynq out using a local single-table conversion of the PostgreSQL DVD rental sample database:
docker run -d -p 8000:8000 benward2301/dynamodb-local-dvd-rental
sleep 2
# Can be omitted if you have AWS configured
export AWS_ACCESS_KEY_ID=local
export AWS_SECRET_ACCESS_KEY=local
export AWS_REGION=eu-west-2
dynq -E http://localhost:8000 -f dvd_rental -L 1You can view the database schema with the AWS CLI:
aws dynamodb --endpoint-url http://localhost:8000 describe-table --table-name dvd_rental- Use
--partition-keyand--sort-keywherever possible - Use
--selectto improve performance of high-volume queries - Keep
jqfilters simple - Consider using
jiqor a similar interactivejqtool to model queries - Bear in mind memory usage when processing large datasets
(table name)
Required
The name of the table containing the requested items; or, if you provide --index, the name of the
table to which that index belongs.
(integer)
The number of coroutines to launch when reading from DynamoDB. Defaults to 1.
For scan operations, this option is equivalent to the DynamoDB --total-segments option. The optimal number will
depend on the size and composition of the table.
For non-scan operations, this option is only applicable if multiple keys are passed.
Incompatible with --scan-limit and --start-key.
(index name)
The name of a global secondary index to query.
Requires --partition-key.
Incompatible with --consistent-read.
Retrieve non-projected attributes from the primary table when querying a global secondary index.
Requires --partition-key and --index.
(projection expression)
A comma-separated set of attribute names to retrieve. Equivalent to the DynamoDB --projection-expression
option.
Can improve performance of queries.
Incrementally write items to stdout.
Incompatible with --aggregate, --reduce, --prune and --meta-only.
Guarantees that all writes completed before the query began will be processable.
(url)
Send DynamoDB requests to the given URL.
(aws profile)
Profile to use from your AWS credentials file.
(aws region)
The AWS region to use. Overrides config/env settings.
(jq filter)
jq predicate filter to select items. Equivalent to jq select(f)
function.
(jq filter)
jq filter producing one or more partition keys to query.
The output must be an object containing a single key (the partition key attribute name), the value of which must be a string, number, or array thereof.
Binary keys are not currently supported.
(jq filter)
jq filter producing one or more sort keys or a sort key range to query.
The output must be an object containing a single key (the sort key attribute name).
To target specific items, the value must be a string, number, or array thereof.
To target a range of items, the value must be an object with exactly one of the following operator keys:
ltorless_thanlteorless_than_or_equalsgtorgreater_thangteorgreater_than_or_equalsbegins_withbetween
Operand values must be a string or number. Each operator expects a single operand, except between which expects an
array containing a lower and upper bound (both inclusive).
Binary keys are not currently supported.
Requires --partition-key.
Incompatible with --start-key.
(jq filter)
jq filter producing the last evaluated key from a previous DynamoDB scan or query operation. When applicable,
dynq will return the last evaluated key of any such operations via the meta.lastEvaluatedKey field.
If a partition key has already been passed via the --partition-key option, then this filter does not need to output
one.
Binary keys are not currently supported.
Incompatible with --sort-key and --concurrency.
(jq filter)
jq filter to transform individual items. Executes after the --where selection filter.
Incompatible with --meta-only.
(jq filter)
jq filter to transform individual items. Executes before the --where selection filter.
(jq filter)
jq filter to transform the complete query result set, after all other transformations and exclusions have been
applied. The output of this filter is returned to the user via the content field.
The total hit count of the query can be accessed in this filter using the $count variable. This is useful when the
result set has been transformed by the --reduce filter.
Incompatible with --stream and --meta-only.
(jq filter)
jq filter to transform the cumulative result set, executed after each request to DynamoDB. Must return an array.
This filter can be used to find the least/greatest n values according to some comparator, or find distinct values.
Where possible, it should be used over --aggregate for high-volume queries to reduce memory usage.
Incompatible with --meta-only.
(starting value) (jq filter)
Reduce items using the given starting value and jq filter, with items assigned to $item.
Equivalent to jq
reduce .[] as $item (<starting value>; <jq filter>)
Incompatible with --stream, --prune and --meta-only.
(integer)
The maximum number of DynamoDB items to retain after selection.
Note that meta.lastEvaluatedKey will not be returned when this option is given.
(integer)
The maximum number of DynamoDB items to scan across one or more requests.
Unlike --limit, meta.lastEvaluatedKey will be returned when this option is given unless all items have been scanned.
(integer)
The maximum number of requests to send to DynamoDB per coroutine.
(integer)
The maximum number of items scanned per DynamoDB request.
Return only the content of the query output.
Incompatible with --meta-only.
Return only the metadata of the query output.
Incompatible with --content-only, --transform, --aggregate, --prune, --reduce, --rearrange-keys
and stream.
Only write to stderr when an error is encountered.
Colorize JSON output. Enabled by default when destination is a TTY.
Incompatible with --monochrome.
Do not colorize JSON output. Enabled by default if destination is not a TTY.
Incompatible with --colorize.
Compact instead of pretty-printed output.
Sort keys of objects on output.
Incompatible with --meta-only.
The examples below are run against the DVD rental sample database.
For brevity, these queries do not use the --select option, but its use is recommended for high-volume queries.
dynq --from dvd_rental \
--partition-key '.entity = "film"' \
--where '.rating == "G"' \
--limit 1Tip
The --partition-key filter above uses assignment to produce an object, however a JSON or JSON5 object literal may
be used instead:
--partition-key: '{ entity: "film" }'Using --transform and --aggregate
dynq --from dvd_rental \
--partition-key '.entity = "payment"' \
--where '.payment_date | startswith("2007-03")' \
--transform '.amount' \
--aggregate 'add'Using --reduce
dynq --from dvd_rental \
--partition-key '.entity = "payment"' \
--where '.payment_date | startswith("2007-03")' \
--reduce 0 '. + $item.amount'Using --transform and --aggregate
dynq --from dvd_rental \
--partition-key '.entity = "payment"' \
--transform '.amount' \
--aggregate 'add / length'Using --reduce and --aggregate
dynq --from dvd_rental \
--partition-key '.entity = "payment"' \
--reduce 0 '. + $item.amount' \
--aggregate '. / $count'dynq --from dvd_rental \
--partition-key '.entity = "film"' \
--prune '[max_by(.length)]'dynq --from dvd_rental \
--partition-key '.entity = "film"' \
--prune 'sort_by(.length)[:3]'dynq --from dvd_rental \
--partition-key '.entity = "film"' \
--reduce '{}' '.[$item.rating] += 1'dynq --from dvd_rental \
--partition-key '.film_id = 1' \
--where '.entity == "inventory"' \
--index 'film_id' \
--expanddynq --from dvd_rental \
--start-key '{ entity: "rental", id: 1 }'dynq --from dvd_rental \
--partition-key '.entity = "film"' \
--sort-key '.id.less_than = 100'dynq --from dvd_rental \
--partition-key '.entity = ["staff", "customer", "actor"]' \
--where '.first_name == "Jon"'dynq -em -f dvd_rental | while read item; do echo "$item" > $(uuid).json; donedynq -e -f dvd_rental -P '.entity = "staff"' -S '.id = 1' -t '.picture' \
| tr -d \" \
| base64 -d \
> staff_1.png