dynamodb-shell

ddbsh

ddbsh is a simple CLI for DynamoDB modeled on isql, and the MySQL CLIs. ddbsh is open source. For more information about this, see the Licensing section below.

ddbsh presents the user with a simple command line interface. Here the user can enter SQL-like commands to DynamoDB. The output is presented in the same window. ddbsh supports many Data Definition Language (DDL) and Data Manipulation Language (DML) commands.

WARNING ddbsh is provided for your use on an AS-IS basis, is not supported for production use cases, and should only be used for non-production and experimental use cases. Please refer to section 7 of the License for more details.

ddbsh can operate directly against your dynamodb tables and thus delete/drop will impact your tables and the operations are irreversible. ddbsh can perform scans and queries against your data and the operations you perform count against your tables capacity, and could incur significant costs.

For details on how to restrict access to specific APIs, refer to the documentation on Fine Grained Access Control in DynamoDB.

It is strongly advised that you understand what ddbsh is doing, and experiment with DynamoDB Local first.

Reimplementation (C++23 library + shell)

This repository is being reimplemented as a consumable engine library (libddbsh) with the ddbsh command as a thin line-reader wrapper around it. The engine — lexer, parser, command model, expression engine, SQL→API planner, execution behind an injected client, and output/EXPLAIN formatting — is a library with an SDK-free, terminal-free public API (headers under include/ddbsh/). The CLI owns only argument parsing, the prompt, line editing and history, config-file loading, signal handling, and wiring a concrete AWS SDK client into the library.

Current version: 0.9.19. The reimplementation drives the full standard ddbsh command surface (every non-global-tables command) end to end against real DynamoDB; global tables are the sole deferred family. The command surface below lists what the engine drives today. The full command reference further down this document describes the complete ddbsh behavior.

The “Building the software” section below describes the original upstream source layout (cmake ../dynamodb-shell/ddbsh). For the reimplementation, use the top-level build in this section instead.

Building the library and shell

Prerequisites: a C++23 compiler (GCC ≥ 12 / Clang ≥ 16 / MSVC 19.3x), CMake ≥ 3.21, flex and bison (reentrant scanner + lalr1.cc parser), GNU readline, zlib, and the AWS SDK for C++ (dynamodb, iam, sts components).

cmake -S . -B build
cmake --build build

This produces, from one build: the engine library (ddbsh, SDK-free), the optional AWS-backed client component (ddbsh-aws, alias ddbsh::aws — the ready-made AwsDDBClient plus AwsApiGuard), the CLI (CMake target ddbsh-cli, installed as the executable ddbsh), and the native unit tests (ddbsh-tests). The AWS component (and the CLI, which needs it) is built when DDBSH_BUILD_AWS is ON (the default); set it OFF for a pure SDK-free engine-only build/install that requires no AWS SDK at all. If the AWS SDK is installed to a non-default prefix, point CMake at it:

cmake -S . -B build -DCMAKE_PREFIX_PATH=/path/to/aws-sdk-cpp

Install the library (headers + lib), the AWS component, and the CLI to a prefix:

cmake --install build --prefix /path/to/prefix

The install ships a find_package(ddbsh) CMake package (under lib/cmake/ddbsh) exporting ddbsh::ddbsh and — when the AWS component was built — ddbsh::aws. A consumer links either target with no hand-written include/lib wiring; see Embedding the library.

macOS (Homebrew)

Install the toolchain and dependencies with Homebrew:

brew install cmake bison flex readline zlib

Homebrew’s bison and flex are keg-only: they are not symlinked onto your PATH because macOS ships its own (ancient) copies in /usr/bin. The system bison is version 2.3, which is too old for the C++ lalr1.cc parser skeleton this project uses, so you must make the Homebrew versions take precedence.

Prepend them to your PATH for the current shell (and add to ~/.zshrc to make it permanent):

# Apple Silicon (Homebrew prefix /opt/homebrew)
export PATH="/opt/homebrew/opt/bison/bin:/opt/homebrew/opt/flex/bin:$PATH"

# Intel (Homebrew prefix /usr/local)
export PATH="/usr/local/opt/bison/bin:/usr/local/opt/flex/bin:$PATH"

Verify you are getting the Homebrew tools (bison ≥ 3.8, not 2.3):

which bison && bison --version
which flex  && flex  --version

Alternatively, point CMake directly at the Homebrew binaries instead of changing your PATH (this also stops CMake from autodetecting /usr/bin/bison):

cmake -S . -B build \
  -DBISON_EXECUTABLE="$(brew --prefix bison)/bin/bison" \
  -DFLEX_EXECUTABLE="$(brew --prefix flex)/bin/flex"
cmake --build build

If you installed the AWS SDK for C++ via Homebrew or to a custom prefix, add it to CMAKE_PREFIX_PATH as shown above (you can combine the flags).

Avoiding AWS SDK version conflicts

The vector-search commands require the AWS SDK for C++ ≥ 1.11.916 (the first release exposing the SearchVectors API). A common macOS pitfall is having more than one SDK installed at once — for example a Homebrew aws-sdk-cpp under /opt/homebrew and a manual build installed into /usr/local. When find_package(AWSSDK) resolves one version but the compiler’s default include path (/usr/local/include) pulls aws/core/VersionConfig.h from another, the build fails with:

error: 'AWS_SDK_VERSION_PATCH' macro redefined [-Werror,-Wmacro-redefined]

Fix it by ensuring exactly one SDK is discoverable, and point CMake at it:

# list every AWS SDK VersionConfig.h and its version
for f in $(find /usr/local /opt/homebrew $HOME -path '*aws/core/VersionConfig.h' 2>/dev/null); do
  echo "$f"; grep AWS_SDK_VERSION_PATCH "$f"; done

# remove the unwanted install (e.g. an older Homebrew SDK), then reconfigure clean
# (the CMake cache pins the previously found SDK, so delete the build dir)
rm -rf build
cmake -S . -B build -DCMAKE_PREFIX_PATH=/prefix/of/sdk-1.11.916
cmake --build build

CMAKE_PREFIX_PATH is searched ahead of system defaults, so both the SDK CMake package and its headers resolve to the same install. Do not silence -Wmacro-redefined to work around this — the clashing version macros mean you would be compiling against one SDK’s headers while linking another’s libraries.

Supported commands

The engine drives this command set end to end against real DynamoDB:

Extensions (opt-in, additive):

Deferred to a later phase: global tables (ALTER TABLE ... REPLICA multi-region provisioning and the ddbsh-gt-* suite), which require a live multi-region setup. The parser may recognize other unimplemented syntax and return a clear “not implemented in this build” error rather than a parse error.

isql-mode input

The CLI assembles input in the Sybase isql style: it buffers physical lines until it reads a terminator line, then hands the whole batch to the engine, which parses every ;-terminated statement in order (a missing trailing ; on the last statement is tolerated). Statements inside a batch stay ;-terminated; the terminator word itself is never sent to the engine.

us-east-1> SELECT * FROM Movies
       ...> WHERE year = 2014;
       ...> go

To terminate with a blank line instead of the word go, start the shell with -t (no argument), or run set terminator with no value mid-session:

$ ddbsh -t
us-east-1> SELECT * FROM Movies WHERE year = 2014;
       ...>

A custom terminator word is set with -t STR (e.g. -t ;;) or set terminator STR.

This isql batch input is a deliberate, documented divergence (tenets 2 and 8) from the original ddbsh, which executed each statement as soon as it read a bare unquoted ; interactively. The divergence is additive and lives only at the CLI layer: the engine still parses ;-terminated statements, so the golden .exp command strings (all ;-terminated and flushed by EOF) produce byte-identical output.

Syntax error diagnostics

A syntax error reports the [line, column] location and the offending token, and — below the offending input line — a compiler-style caret (^) pointing at the exact error column:

ddbsh$ create table foo ( a number ) primary key ( a hash ) gsi ( g on (a, b) hash, (c) range) projecting all ))
[1, 68] syntax error at ,
create table foo ( a number ) primary key ( a hash ) gsi ( g on (a, b) hash, (c) range) projecting all ))
                                                                  ^

(Here the GSI key schema needs its own outer parens around the role list: g ON ( (a, b) hash, (c) range ).) The caret is an additive diagnostic; the [line, column] syntax error at <token> line is unchanged.

CLI flags, config, and environment

Usage: ddbsh [-q] [-e] [-d] [-t[TERM]] [-c "<cmds>" | -f <file>]

At startup the CLI loads a config file (DDBSH_CONFIG_FILE, else ~/.ddbsh_config) if present and reads/writes interactive history at ~/.ddbsh_history. The region resolves from AWS_REGION, then AWS_DEFAULT_REGION, then us-east-1; DDBSH_ENDPOINT_OVERRIDE sets an endpoint override, which marks the prompt with a (*) suffix.

Embedding the library

The embedding API is a simple, ODBC / Sybase-DB-Library-style C++ surface: open a connection, submit a statement, walk result sets, walk rows, read a typed column. You open one handle and work through it — no four-object ceremony, no variant switching, no separate renderer in your code.

Embedding against real DynamoDB (ddbsh::aws::open)

ddbsh::aws::open(region) returns a ready-to-use Database: it resolves region/endpoint/credentials through the standard AWS chain, manages the AWS SDK lifecycle internally (you never write AwsApiGuard), builds the shared AwsDDBClient, and owns a ddbsh::Connection over it. Consume it via find_package(ddbsh):

find_package(ddbsh CONFIG REQUIRED)        # defines ddbsh::ddbsh and ddbsh::aws
find_package(AWSSDK REQUIRED COMPONENTS dynamodb)
target_link_libraries(myapp PRIVATE ddbsh::aws)   # pulls ddbsh::ddbsh + the SDK

#include "ddbsh/aws/database.hpp"

auto db = ddbsh::aws::open("us-east-1");             // ONE object
if (!db) { std::fputs(db.error().message.c_str(), stderr); return 1; }

// The single-SELECT shortcut: iterate typed, null-aware rows directly.
auto rows = db->query("SELECT * FROM Movies WHERE id = \"m1\";");
if (!rows) { std::fputs(rows.error().message.c_str(), stderr); return 1; }
for (const ddbsh::Row& row : *rows) {
    std::optional<std::string> title = row.get_string("title");
    std::optional<long long>   year  = row.get_int("year");
    // ...
}

Any statement (or a batch, or a multi-row write) returns a Results you walk DB-Library-style — next_result() steps result sets, next_row() steps rows, column() reads a typed Cell:

auto res = db->exec("INSERT INTO t (id) VALUES (1),(2); SELECT * FROM t;");
if (!res) { std::fputs(res.error().message.c_str(), stderr); return 1; }
ddbsh::Results& r = *res;
do {
    switch (r.kind()) {
    case ddbsh::ResultKind::rows:
        while (r.next_row()) use(r.column("id").as_string());
        break;
    case ddbsh::ResultKind::status: note(r.status_token()); break;   // "INSERT"
    case ddbsh::ResultKind::write:  account(r.read(), r.modified(), r.ccf()); break;
    case ddbsh::ResultKind::report: for (auto& ln : r.lines()) show(ln); break;
    case ddbsh::ResultKind::explain: show(r.explain_text()); break;
    case ddbsh::ResultKind::empty: break;
    }
} while (r.next_result());
if (auto e = r.error()) std::fputs(e->message.c_str(), stderr);  // per-statement failure

A Cell separates the three DynamoDB states (missing() / is_null() / a typed value) and offers as_string / as_int / as_double / as_bool. To reproduce byte-exact ddbsh output (what the shell prints), call r.render() (current result set) or r.render_all() (the whole batch).

Parameterized statements (injection-safe). Build statements from untrusted input with PreparedStatement and positional ? / named :name placeholders; the API substitutes correctly-escaped ddbsh literals, so you never concatenate:

ddbsh::PreparedStatement stmt("SELECT * FROM Movies WHERE id = ? AND year = ?;");
stmt.bind(1, std::string("m1")).bind(2, 2010LL);
auto rows = db->query(stmt);

Client configuration (timeouts, retry). The OpenOptions struct form of open() passes a full Aws::Client::ClientConfiguration through (timeouts, retry strategy, max connections, proxy); ddbsh overlays region/endpoint and its own user-agent. A separate waiter_timeout bounds the CREATE/DROP waiters.

Header boundary: ddbsh/*.hpp (including ddbsh/connection.hpp) are SDK-free (compile with only a C++23 toolchain); ddbsh/aws/*.hpp require the AWS SDK for C++. Link ddbsh::ddbsh for the engine alone, ddbsh::aws for the ready-made AWS client and open().

examples/ddbshlib_sample/ is a complete, runnable live program: open(), CREATE a uniquified on-demand table, INSERT, SELECT three ways (GetItem / Query / Scan), DROP, self-cleaning on every exit path via an RAII guard. It builds as an external find_package(ddbsh) consumer via its own CMakeLists.txt. Because it spends, its CTest (ddbshlib_sample) and the real-DynamoDB functional test (ddbsh_live_api) are OFF by default; enable them with -DDDBSH_TEST_LIVE_SAMPLE=ON where credentials are present.

Embedding offline (ddbsh::testing::MemoryClient)

A ddbsh::Connection works over ANY IDDBClient, so you can drive the exact same embedding API with no AWS account, no credentials, no network, zero cost against the in-memory ddbsh::testing::MemoryClient — ideal for unit-testing your own ddbsh usage:

#include "ddbsh/connection.hpp"
#include "ddbsh/testing/memory_client.hpp"

ddbsh::testing::MemoryClient client;                 // genuine in-memory store
ddbsh::Connection db(ddbsh::SessionConfig{"local", ""}, client);
db.exec("create table t ( id string ) primary key ( id hash );");
db.exec("insert into t ( id, v ) values ( \"a\", 1 );");
for (const ddbsh::Row& r : *db.query("select * from t where id = \"a\";"))
    use(r.get_string("v"));

MemoryClient is SDK-free and links ddbsh::ddbsh alone. It faithfully models the primary-key data plane (GetItem / Query / Scan / Put / Update / Delete); FilterExpressions and the full control-plane reports are not evaluated (see the header for the exact scope). examples/ddbshlib_inmemory_sample/ is a complete runnable version, built as a default-on, credential-less CTest (ddbshlib_inmemory_sample). See examples/README.md.

Advanced / injection interface. The lower-level engine SPI — Session, IDDBClient, ExecOutcome, OutcomeSink, and ddbsh::render::Renderer — remains public for embedders who need the raw fragment stream or who inject their own backend. Most applications want the Connection/Database surface above.

Getting the AWS SDK for C++

The ddbsh::aws component and the CLI need the AWS SDK for C++ (dynamodb, sts, iam components, version ≥ 1.11.916 for vector search):

The engine library alone (ddbsh::ddbsh, DDBSH_BUILD_AWS=OFF) needs no AWS SDK — the offline ddbshlib_inmemory_sample builds against just that.

Tests

Run the test suite via devtools/run-tests from the orchestration repo. Since the Step-2 exit gate, every non-global-tables golden family runs against the new engine (the shipping ddbsh binary driven through the external harness in tests/golden/); the gate exit propagates. The original C++11 engine was removed from the tree during the phase-2i oracle retirement (preserved in git history under the oracle-archive-v0.8.1 tag); its golden contract was relocated to a single command-family tree at tests/golden/ and remains the regression source. The native GoogleTest unit tests (offline, FakeDDBClient) build with the project (ddbsh-tests) and run alongside as the deterministic backend-free fidelity signal.

Installation

Using pre-built binaries

Installing on Mac via Homebrew

On a macintosh, you can install DynamoDB shell from homebrew with the following two commands.

brew tap aws/tap
brew install aws-ddbsh

You need to create the AWS credentials and configuration files. For instructions read this.

Installing on ubuntu

Binaries for ubuntu-latest are built by github. To find the latest binaries navigate to Actions and find the latest run of the “Build DynamoDB Shell” action. When you click on that link, you fill find (at the very bottom) a section of Artifacts. There you will find a file with binaries that you can run on an ubuntu machine.

$ unzip binaries-linux.zip
Archive:  binaries-linux.zip
  inflating: ddbsh
  inflating: ddbshtest
$

You need to create the AWS credentials and configuration files. For instructions read this.

Building the software

Download the software

Clone the source code repository from github.

$ git clone https://github.com/awslabs/dynamodb-shell.git

Build the software

Pre-requisites

  1. You need to get cmake version >= 3.21

  2. You need a C++ compiler and make

  3. You need the AWS SDK installed

Building on Ubuntu

To get the latest version of cmake, follow instructions on kitware’s website, or in [this](https://askubuntu.com/questions/355565/how-do-i-install-the-latest-version-of-cmake-from-the-command-line} answer.

To get the AWS SDK installed follow instructions here. Detailed setup information can be found here.

Building on Mac

To get the latest version of cmake, follow instructions here.

Common build steps

Once all pre-requisites are installed and available, the build is performed with cmake.

% git clone git@github.com:awslabs/dynamodb-shell.git
Cloning into 'dynamodb-shell'...
[...]

% mkdir build

% cd build

% cmake ../dynamodb-shell/ddbsh

% cmake --build . --parallel $(nproc)

More build steps

  1. Building the SDK. I have found the following to work reliably.

AWS SDK version. ddbsh requires the AWS SDK for C++ dynamodb, iam, and sts components. Use a recent release (the project is developed against 1.11.916 or newer).

Generator-agnostic commands. The steps below use CMake’s build/install drivers (cmake --build / cmake --install) instead of make / make install, so they work whether CMake generated Makefiles or Ninja. On a box whose default generator is Ninja, no Makefile is produced and make fails with “No targets specified and no makefile found.”

Install prefix. Pick a prefix and keep it consistent with where ddbsh’s find_package(AWSSDK) later resolves (its CMAKE_PREFIX_PATH). sudo is needed only for a root-owned prefix such as /usr/local; a user-owned prefix like $HOME/.local/aws-sdk-cpp needs no sudo. Verify the cache took the prefix before installing: cmake -LA . | grep CMAKE_INSTALL_PREFIX — the AWS SDK’s top-level CMake has been observed to retain a stale CMAKE_INSTALL_PREFIX=/usr/local from the first configure; re-run cmake . -DCMAKE_INSTALL_PREFIX=<prefix> to make it stick.

% git clone https://github.com/aws/aws-sdk-cpp.git

% mkdir aws-sdk-build

% cd aws-sdk-cpp

% git submodule update --init --recursive

% cd ../aws-sdk-build

% cmake ../aws-sdk-cpp -DCMAKE_BUILD_TYPE=Release \
        -DCMAKE_PREFIX_PATH=/usr/local/ \
        -DCMAKE_INSTALL_PREFIX=/usr/local/ \
        -DBUILD_ONLY="dynamodb;iam;sts" \
        -DBUILD_SHARED_LIBS=OFF \
        -DENABLE_TESTING=OFF \
        -DFORCE_SHARED_CRT=OFF

% cmake --build . --parallel $(nproc)

% sudo cmake --install .

1a. Rebuilding the SDK.

% cd <aws-sdk-dir>

% git pull --recurse-submodules=yes

% cd ../aws-sdk-build

% cmake ../aws-sdk-cpp -DCMAKE_BUILD_TYPE=Release \
        -DCMAKE_PREFIX_PATH=/usr/local/ \
        -DCMAKE_INSTALL_PREFIX=/usr/local/ \
        -DBUILD_ONLY="dynamodb;iam;sts" \
        -DBUILD_SHARED_LIBS=OFF \
        -DENABLE_TESTING=OFF \
        -DFORCE_SHARED_CRT=OFF

% cmake --build . --parallel $(nproc)

% sudo cmake --install .
  1. Building ddbsh. I have found the following to work reliably
% git clone https://github.com/awslabs/dynamodb-shell.git

% mkdir build && cd build

% cmake ../dynamodb-shell/ddbsh -DCMAKE_BUILD_TYPE=Release

% cmake --build . --parallel $(nproc)

% sudo cmake --install .

Running tests

Once you complete a build you can run the unit tests as shown below.

% ctest --test-dir .
Running tests...
Test project /Users/amrithie/source/github/dynamodb-shell/build
        Start   1: ddbsh-basic-cli
[...]

Getting Started

Running ddbsh is as simple as executing the ddbsh command. ddbsh is written in C++ and uses the DynamoDB API through the AWS DynamoDB SDK. Assuming that you built it as above, and completed the install, then you should be able to run it with just this.

$ ddbsh
ddbsh - version 0.2
us-east-1> _

When you connect to ddbsh, you are automatically connected to an AWS region. In the illustration above, the default region was configured in the AWS configuration file ~/.aws/config

$ more ~/.aws/config
[default]
region=us-east-1
output=text

For more information visit AWS configuration and credentials files.

Environment Variables

ddbsh respects the default AWS environment variables, see How to set environment variables.

DDBSH_ENDPOINT_OVERRIDE

Used to specify an alternate endpoint, such as when using DynamoDB Local.

Using DynamoDB Local

WARNING: To connect with DynamoDB Local you must be running ddbsh version 0.2 or higher and using the AWS C++ SDK version 1.11 or higher.

If you want to use ddbsh with DynamoDB Local, follow these steps.

First, install DynamoDB Local as described here.

Set the environment variables as shown below.

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"

You will then be able to connect to DynamoDB Local.

ddbsh
ddbsh - version 0.2
dynamodb-local (*)>

The (*) at the end of the prompt indicates a non-standard endpoint is in use.

Cross Account Access

In DynamoDB, a user can access resources (tables, indices) created and owned by another user using IAM policies. This is called a “Role Based Policy”. You can find information about how to do this in the DynamoDB documentation [here][https://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/configure-cross-account-access-to-amazon-dynamodb.html].

The steps are described in brief below. Two accounts are involved here, one called the “ACTOR”, and the other called “OWNER”. The “OWNER” is the one who owns the table or index. The ACTOR is another account that is permitted access by the OWNER.

  1. As the OWNER, create a table
  2. As the OWNER, create a role with an AWS account as the trusted entity. Specify the account id for the ACTOR here. For the purpose of testing, I used the policy AmazonDynamoDBFullAccess. This is available in the web interface and a bit harder with the CLI.
  3. Find the ARN for the newly created policy.
  4. As the ACTOR, permit the user to Assume the role based on its ARN.
  5. Configure the ~/.aws/credentials file

If you get this configured right, you can set the environment variable AWS_DEFAULT_PROFILE and specify the ARN in that profile section.

In my test setup, and for the purposes of running tests, I have a profile called cross-acct in my ~/.aws/credentials file, and it looks like this.

[default]
aws_access_key_id=<access-key-id-here>
aws_secret_access_key=<secret-key-here>

[cross-acct]
role_arn=arn:aws:iam::OWNER:role/<role-name>
source_profile=default

where OWNER is the account ID of the owner account, and ACTOR is the account ID of the actor account.

When using DynamoDB Shell, you have to set AWS_DEFAULT_PROFILE like this.

$ export AWS_DEFAULT_PROFILE=cross-acct
$ ddbsh

Note that if you put the configuration of the profile in the config file, the format is slightly different. You must prefix the profile name with the word “profile”.

The same cross-acct setting in the config file would look like this.

[profile cross-acct]
role_arn=arn:aws:iam::OWNER:role/<role-name>
source_profile=default

Interface

ddbsh exposes a readline based interface. The UP-ARROW and DOWN-ARROW keys allow you to view previous commands, and pick a previous command to reexecute.

Commands are terminated with the ‘;’ character.

Control Commands

CONNECT

Purpose: Connect to a different region or endpoint

Syntax: CONNECT region [WITH ENDPOINT endpoint] ;

$ ./ddbsh
ddbsh - version 0.2
us-east-1> connect us-west-1;
CONNECT
us-west-1>

The prompt shows the region name, and the additional (*) if you are connected to a non-standard endpoint.

SHOW VERSION

Purpose: The SHOW VERSION command displays the version of DynamoDB Shell.

us-east-1> show version;
ddbsh - version 0.5.1
us-east-1>

Note: The SHOW VERSION command was added in DynamoDB Shell version 0.5.1.

QUIT

Purpose: You have had a good day, and you want to do something else.

Syntax: QUIT

Data Definition Language

ALTER TABLE

Purpose: Make changes to a tables settings including billing mode, table class, streams, and TTL. Also create and drop Global Secondary Indexes (GSI).

Syntax: ALTER TABLE table [alter-table-options]

SET billing_mode_and_throughput

Sets table and index billing mode and throughput.

BILLING MODE PROVISIONED ( NN RCU, MM WCU )

Set the table to provisioned mode with the specified throughput.

BILLING MODE ON DEMAND [throughput]

Set the table to on demand billing mode.

throughput: THROUGHPUT ( NN RCU, MM WCU )

Example 1

us-east-1> alter table singlekey set billing mode on demand;
ALTER
us-east-1> describe singlekey;
Name: singlekey (ACTIVE)
[...]
Billing Mode: On Demand
[...]
us-east-1>

Example 2

us-east-1> alter table singlekey set billing mode provisioned ( 200 RCU, 300 WCU );
ALTER
us-east-1> describe singlekey;
[...]
Billing Mode: Provisioned (200 RCU, 300 WCU)
[...]
us-east-1>

SET TABLE CLASS class

Alters the table class.

SET TABLE CLASS STANDARD INFREQUENT ACCESS

Set the table to table class standard infrequent access.

SET TABLE CLASS STANDARD

Set the table to standard table class.

Example 1

us-east-1> alter table singlekey set table class standard infrequent access;
ALTER
us-east-1> describe singlekey;
[...]
Table Class: STANDARD_INFREQUENT_ACCESS
[...]
us-east-1>

Example 2

us-east-1> alter table singlekey set table class standard;
ALTER
us-east-1> describe singlekey;
[...]
Table Class: STANDARD_INFREQUENT_ACCESS
[...]
us-east-1>

Warning There is a limit on the number of times you can change a table class. If you do it too often, you will get an error like this.

us-east-1> alter table singlekey set table class standard infrequent access;
Error: Subscriber limit exceeded: Updates to TableClass are limited to 2 times in 30 day(s).
us-east-1>

Example 3

Specify an on-demand throughput limit

us-east-1> alter table icdthroughput set billing mode on demand throughput (200 wcu, 10 rcu );
ALTER
us-east-1> describe icdthroughput;
[...]
Billing Mode: On Demand
On Demand Throughput: (10 RCU, 200 WCU)
[...]

Clear the on-demand throughput limit for reads, but not writes.

us-east-1> alter table icdthroughput set billing mode on demand throughput (200 wcu, -1 rcu );
ALTER
us-east-1> describe icdthroughput;
[...]
Billing Mode: On Demand
On Demand Throughput: (-1 RCU, 200 WCU)
[...]

Clear all on-demand throghput limits.

us-east-1> alter table icdthroughput set billing mode on demand throughput unlimited;
ALTER

SET STREAM

Sets options related to DynamoDB Streams.

SET STREAM DISABLED

Disables DynamoDB Streams on the table.

SET STREAM ( KEYS ONLY )

The DynamoDB Stream will contain only key fields.

SET STREAM ( NEW IMAGE )

The DynamoDB Stream will contain the new item.

SET STREAM ( OLD IMAGE )

The DynamoDB Stream will contain the old item.

SET STREAM ( BOTH IMAGES )

The DynamoDB Stream will contain both the old and new item images.

Example 1

us-east-1> alter table singlekey set stream (new image);
ALTER
us-east-1> describe singlekey;
[...]
Stream: NEW_IMAGE
[...]
us-east-1>

Example 2

us-east-1> alter table singlekey set stream disabled;
ALTER
us-east-1> describe singlekey;
[...]
Stream: Disabled
[...]
us-east-1>

CREATE GSI

CREATE GSI name ON key_schema projection optional_billing_mode_and_throughput

Allows you to create a GSI on a table that already exists. A GSI has a key schema (those are the item attributes that form part of the index) and a projection (the attributes in the item that are part of the index).

The projection can be one of all, keys only, or specify which keys to include.

DROP GSI name

Example 1

us-east-1> alter table singlekey (x number, y number) create gsi xygsi on (x hash, y range) projecting all billing mode provisioned (22 RCU, 41 WCU);
ALTER
us-east-1> describe singlekey;
[...]
GSI xygsi: ( HASH x, RANGE y ),  Provisioned (22 RCU, 41 WCU), Projecting (ALL), Status: CREATING, Backfilling: YES
[...]
us-east-1>

The index created above projects all attributes in the item.

Example 2

us-east-1> alter table table1 (x number, y number) create gsi xykeysgsi on (x hash, y range) projecting keys only billing mode provisioned (22 RCU, 41 WCU);
ALTER
us-east-1> describe table1;
[...]
GSI xykeysgsi: ( HASH x, RANGE y ),  Provisioned (22 RCU, 41 WCU), Projecting (KEYS_ONLY), Status: CREATING, Backfilling: NO
[...]us-east-1>

This index only projects the keys.

Example 3

us-east-1> alter table singlekey (x number, y number, a number)
us-east-1> create gsi xyagsi on (x hash) projecting include (y, a)
us-east-1> billing mode provisioned (20 RCU, 30 WCU);
ALTER
us-east-1>

This index will project the keys as well as the attributes y and a.

Example 4

us-east-1> alter table singlekey drop gsi xygsi;
ALTER
us-east-1>

SET billing_mode_and_throughput

You can change the billing mode and throughput on a table, and its associated GSIs, all in one command.

Example 5

us-east-1> describe table1;
[...]
Billing Mode: Provisioned (10 RCU, 20 WCU)
GSI pqgsi: ( HASH p ),  Provisioned (20 RCU, 30 WCU), Projecting (INCLUDE) (q), Status: CREATING, Backfilling: YES
GSI xykeysgsi: ( HASH x, RANGE y ),  Provisioned (22 RCU, 41 WCU), Projecting (KEYS_ONLY), Status: ACTIVE, Backfilling: NO
[...]
us-east-1>

To change this table to on demand billing mode, we could do this.

us-east-1> alter table table1 set billing mode on demand;
ALTER
us-east-1> describe table1;
[...]
Billing Mode: On Demand
GSI pqgsi: ( HASH p ), Billing Mode: On Demand (mirrors table), Projecting (INCLUDE) (q), Status: UPDATING, Backfilling: YES
GSI xykeysgsi: ( HASH x, RANGE y ), Billing Mode: On Demand (mirrors table), Projecting (KEYS_ONLY), Status: UPDATING, Backfilling: NO
[...]
us-east-1>

Now we convert it back to a provisioned table.

us-east-1> us-east-1> alter table table1
us-east-1> set billing mode provisioned (20 rcu, 30 wcu)
us-east-1> update gsi (pqgsi set billing mode provisioned (20 rcu, 30 wcu), xykeysgsi set billing mode provisioned (3 rcu, 5 wcu));
ALTER

SET PITR [ENABLED|DISABLED]

Enables or disables the setting for PITR (Point In Time Recovery) for a table.

us-east-1> describe ipt1;
Name: ipt1 (ACTIVE)
[...]
PITR is Disabled.
[...]
us-east-1> alter table ipt1 set pitr enabled;
ALTER
us-east-1> describe ipt1;
Name: ipt1 (ACTIVE)
[...]
PITR is Enabled: [Mon, 21 Nov 2022 21:29:49 GMT to Mon, 21 Nov 2022 21:29:49 GMT]
[...]
us-east-1>  alter table ipt1 set pitr disabled;
ALTER
us-east-1> describe ipt1;
Name: ipt1 (ACTIVE)
[...]
PITR is Disabled.
[...]
us-east-1>

ADD REPLICA, UPDATE REPLICA, and DROP REPLICA

Enables creation, update and drop of a Global Table replica. ddbsh implements Global Tables (2019).

Make a table and add a replica.


us-east-2> create table global_customers ( custid number ) primary key (custid hash);
us-east-2> alter table global_customers add replica us-west-1;
ALTER
us-east-2>

Describe the table.


us-east-2> describe global_customers;
[...]
Replica Region: us-east-2 (Status: ACTIVE)
Replica Region: us-west-1 (Status: ACTIVE)

Now add some data to the table.


us-east-2> insert into global_customers (custid, name) values ( 101, "Alice" ), (102, "Bob");
INSERT
INSERT
us-east-2> select * from global_customers;
{custid: 102, name: Bob}
{custid: 101, name: Alice}
us-east-2>

us-east-2> connect us-west-1;
CONNECT
us-west-1> select * from global_customers;
{custid: 102, name: Bob}
{custid: 101, name: Alice}
us-west-1>

The data is propagated from us-east-2 to us-west-1 by Global Tables. Similar updates and another replica with Standard Infrequent Access table class below.


us-west-1> update global_customers set active = True where custid = 101;
UPDATE (0 read, 1 modified, 0 ccf)
us-west-1> select * from global_customers;
{custid: 102, name: Bob}
{active: TRUE, custid: 101, name: Alice}
us-west-1> connect us-east-2;
CONNECT
us-east-2> select * from global_customers;
{custid: 102, name: Bob}
{active: TRUE, custid: 101, name: Alice}
us-east-2>

us-east-2> alter table global_customers add replica us-west-2 table class standard infrequent access;
ALTER
us-east-2> connect us-west-2;
CONNECT
us-west-2>

...

us-west-2> describe global_customers;
Name: global_customers (CREATING)
Key: HASH custid
[...]
Table Class: STANDARD_INFREQUENT_ACCESS
[...]
Replica Region: us-east-2 (Status: ACTIVE)
Replica Region: us-west-1 (Status: ACTIVE)
Replica Region: us-west-2 (Status: ACTIVE)
us-west-2>

us-west-2> select * from global_customers;
{custid: 102, name: Bob}
{active: TRUE, custid: 101, name: Alice}
us-west-2>

us-west-2> insert into global_customers (custid, name) values ( 103, "Charlie");
INSERT
us-west-2> connect us-east-2;
CONNECT
us-east-2> select * from global_customers;
{custid: 102, name: Bob}
{custid: 103, name: Charlie}
{active: TRUE, custid: 101, name: Alice}
us-east-2>

SET DELETION PROTECTION [ENABLED|DISABLED]

Enables the setting and disabling of deletion protection on tables.

us-east-1> create table dpe ( pk number ) primary key ( pk hash ) deletion protection enabled;
CREATE
us-east-1> describe dpe;
Name: dpe (ACTIVE)
[...]
Deletion Protection: Enabled
[...]

us-east-1> alter table dpe set deletion protection disabled;
ALTER
us-east-1> describe dpe;
[...]
Deletion Protection: Disabled
[...]
us-east-1>

us-east-1> alter table dpe set deletion protection enabled;
ALTER
us-east-1> describe dpe;
Name: dpe (ACTIVE)
[...]
Deletion Protection: Enabled
[...]
us-east-1>

BACKUP TABLE

Purpose: Take a backup of a table.

Syntax: BACKUP TABLE name CALLED backup name

Takes a backup of a table’s data.

Example:

us-east-1> backup table lsitest called lsitest-backup;
BACKUP
us-east-1>

The backup can be seen in the list of backups.

us-east-1> show backups;
[...]
Table: lsitest, Backup: lsitest-backup, Status: AVAILABLE, ARN: arn:aws:dynamodb:us-east-1:3221123459165:table/lsitest/backup/01667964345110-f260cfff, On: 2022-11-09T03:25:45Z, Expires: 1970-01-01T00:00:00Z
us-east-1>

CREATE TABLE

Purpose: Creates a table (and GSIs, LSIs, and sets other initial parameters).

Syntax:

   CREATE TABLE [IF NOT EXISTS][NOWAIT] <name>
         ( attribute_name, attribute_type [,...] )
   primary_key billing_mode_and_throughput
   [gsi_list] [lsi_list] [streams] [table_class] [tags] ;

   attribute_type := NUMBER|STRING|BINARY
   primary_key := PRIMARY KEY key_schema
   key_schema := ( attribute_name HASH [, attribute_name RANGE])

   billing_mode_and_throughput := (BILLING MODE ON DEMAND [throughput])|BILLING MODE provisioned)
   provisioned := ( RR RCU, WW WCU )

   gsi_list := GSI ( gsi_spec )
   gsi_spec := gsi [, gsi ...]
   gsi := gsi_name ON key_schema index_projection [billing_mode_and_throughput]
   index_projection := (PROJECTING ALL) | (PROJECTING KEYS ONLY) | (PROJECTING INCLUDE projection_list)
   projection_list := ( attribute [, attribute ...] )

   lsi_list := LSI ( lsi_spec )
   lsi_spec := lsi [, lsi ...]
   lsi := lsi_name ON key_schema index_projection

   streams := STREAM ( stream_type ) | STREAM DISABLED
   stream_type := KEYS ONLY | NEW IMAGE | OLD IMAGE | BOTH IMAGES

   throughput := THROUGHPUT [( RR RCU, WW WCU ) | UNLIMITED]
   table_class := TABLE CLASS STANDARD | TABLE CLASS STANDARD INFREQUENT ACCESS
   deletion_protection := DELETION PROTECTION [ENABLED|DISABLED]
   tags := TAGS ( tag [, tag ...] )
   tag := name : value

SHOW CREATE TABLE

Purpose: The SHOW CREATE TABLE command will generate the CREATE TABLE command required to generate a table.

Syntax:

SHOW CREATE TABLE [IF NOT EXISTS][NOWAIT] <name>

Note: The SHOW CREATE TABLE command was added in DynamoDB Shell version 0.5.1.

The “IF NOT EXISTS” and “NOWAIT” options are transferred into the CREATE TABLE command.

Example

us-east-1> show create table icd3;
CREATE TABLE "icd3" ("k1" number, "k2" number, "pk" number, "rk" number) PRIMARY KEY ("pk" HASH, "rk" RANGE) BILLING MODE ON DEMAND GSI ("gsi1" ON ( "k1" HASH, "k2" RANGE ) PROJECTING INCLUDE (k3) BILLING MODE ON DEMAND ) TABLE CLASS STANDARD DELETION PROTECTION DISABLED
us-east-1>

DESCRIBE

Purpose: Provides a description of a table.

Syntax: DESCRIBE name

Provides a description of a table.

DESCRIBE BACKUPS

Purpose: Describes a backup.

Syntax: DESCRIBE BACKUP arn

Example

us-east-1> describe backup "arn:aws:dynamodb:us-east-1:3221123459165:table/singlekey/backup/01661373083721-5d1c1146";
Table Name: singlekey
Tabld Id: 2435eaef-96cf-495a-a4ed-589966d77ba7
Table Arn: arn:aws:dynamodb:us-east-1:3221123459165:table/foo
Table Size (bytes): 0
Item Count: 0
Table Key: ( HASH a, RANGE b )
Table Creation Date/Time: 2022-08-24T20:04:08Z
Billing Mode: PROVISIONED
Provisioned Throughput: 20 RCU, 30 WCU
Backup Name: backup71
Backup Arn: arn:aws:dynamodb:us-east-1:3221123459165:table/foo/backup/01661373083721-5d1c1146
Backup Size (bytes): 0
Backup Status: AVAILABLE
Backup Type: USER
Backup Creation Date/Time: 2022-08-24T20:31:23Z
Backup Expiry Date/Time: 1970-01-01T00:00:00Z
us-east-1>

DROP BACKUP

Purpose: Deletes a previously created backup.

Syntax: DROP BACKUP arn

Deletes a backup.

Example

us-east-1> drop backup "arn:aws:dynamodb:us-east-1: 3221123459165:table/backuptest/backup/01661373083721-5d1c1146";
DROP BACKUP
us-east-1>

DROP TABLE

Purpose: Drops a table.

Syntax: DROP TABLE [if exists] [no wait] name

Drops a table. If a table by the name doesn’t exist, an error is generated. If the “if exists” clause is provided, no error is generated if the table does not exist. By default, the command waits for the table to be dropped. Optionally, the “no wait” clause can be provided, and the command returns immediately. The table is dropped in the background.

Example 1

us-east-1> drop table unknown;
Drop Table Failed.
	Requested resource not found: Table: unknown not found
us-east-1>

us-east-1> drop table if exists unknown;
us-east-1>

Example 2

us-east-1> drop table sample13;
..
DROP
us-east-1>

RESTORE TABLE

Purpose: Restores a backup and creates a new table.

Syntax: RESTORE TABLE new table name FROM BACKUP arn

Creates a new table from a backup.

Example

us-east-1> restore table "new-lsitest" from backup "arn:aws:dynamodb:us-east-1: 3221123459165:table/lsitest/backup/01667964345110-f260cfff";
RESTORE
us-east-1>

This will create a new table called “new-lsitest” which will have the indexes and billing mode that was stored in the backup.

us-east-1> describe new-lsitest;
Name: new-lsitest (CREATING)
Key: HASH a, RANGE d
Attributes:  b, N,  a, N,  d, N,  c, N
Created at: 2022-11-09T03:32:15Z
Table ARN: arn:aws:dynamodb:us-east-1:3221123459165:table/new-lsitest
Table ID: ddfab172-1007-457e-b0bc-922480f03f61
Table size (bytes): 0
Item Count: 0
Billing Mode: On Demand
GSI gsi1: ( HASH c ), Billing Mode: On Demand (mirrors table), Projecting (ALL), Status: , Backfilling: NO
LSI lsi1: ( HASH a, RANGE b ),
Stream: Disabled
Table Class: Has not been set.
SSE: Not set
us-east-1>

Note that restores are run asynchronously and the table above is in the CREATING state.

Syntax: RESTORE TABLE new table name FROM old table name TO PITR “YYYY-MM-DDTHH:MM:SSZ”

Restores a table to a specific point in time, and creates a new table with that version.

us-east-1> describe ipt1;
Name: ipt1 (ACTIVE)
Key: HASH a, RANGE b
Attributes:  a, N,  b, N
Created at: 2022-11-21T20:05:22Z
Table ARN: arn:aws:dynamodb:us-east-1:123456789012:table/ipt1
Table ID: 6b1078cb-e81d-419f-99e8-e6c28e071a47
Table size (bytes): 0
Item Count: 0
Billing Mode: Provisioned (10 RCU, 10 WCU)
PITR is Enabled: [Tue, 22 Nov 2022 01:50:34 GMT to Tue, 22 Nov 2022 01:51:23 GMT]
GSI: None
LSI: None
Stream: Disabled
Table Class: STANDARD
SSE: Not set
us-east-1> restore table cloneipt1 from ipt1 to pitr "2022-11-22T01:50:34Z";
RESTORE
us-east-1> describe cloneipt1;
Name: cloneipt1 (CREATING)
Key: HASH a, RANGE b
Attributes:  b, N,  a, N
Created at: 2022-11-22T01:57:11Z
Table ARN: arn:aws:dynamodb:us-east-1:123456789012:table/cloneipt1
Table ID: 514794c5-bc60-4c54-9fdf-e2c1c670074e
Table size (bytes): 0
Item Count: 0
Billing Mode: Provisioned (10 RCU, 10 WCU)
PITR is Disabled.
GSI: None
LSI: None
Stream: Disabled
Table Class: Has not been set.
SSE: Not set
us-east-1>

SHOW BACKUPS

Purpose: Lists backups.

Syntax: SHOW BACKUPS

Shows the backups available to the connected user.

Example

us-east-1> show backups;
Table: singlekey, Backup: backup71, Status: AVAILABLE, ARN: arn:aws:dynamodb:us-east-1:3221123459165:table/singlekey/backup/01661373083721-5d1c1146, On: 2022-08-24T20:31:23Z, Expires: 1970-01-01T00:00:00Z
[...]

The output lists the table name, the backup name, status, ARN, and the timestamps (creation, and expiry).

SHOW LIMITS

Purpose: Shows limits for the connected user.

Syntax: SHOW LIMITS

Shows the limits and consumption of RCU and WCU for the connected user.

Example

us-east-1> show limits;
Region: us-east-1
Account Max (RCU, WCU): 580000, 580000
Table Max (RCU, WCU):  540000, 540000
Total Tables (RCU, WCU): 520, 830
Total GSI (RCU, WCU): 23, 35
Total (RCU, WCU): 543, 865
us-east-1>

SHOW TABLES

Purpose: Lists tables.

Syntax: SHOW TABLES

Lists tables, and provides some basic information about each.

Example

us-east-1> show tables;
 dml_test | ACTIVE | PAY_PER_REQUEST | STANDARD | cd6257e9-5947-45d1-9b1c-2fbaf06d59e0 | arn:aws:dynamodb:us-east-1:3221123459165:table/dml_test | TTL DISABLED | GSI: 0 | LSI : 0 |

The output lists the table name, its current status, billing mode, table class, table-id, table-arn, TTL status, and counts of GSIs and LSIs.

EXPLAIN

Purpose: Explain the API calls performed to execute a DDL or DML command.

Syntax: EXPLAIN

The EXPLAIN command returns the API request that must be executed in order to perform the command provided.

Example

us-east-1> explain create table sampletable ( pk number, rk string, zip number, ht number ) primary key ( pk hash, rk range) billing mode provisioned ( 20 wcu, 30 rcu) gsi ( gsi1 on (zip hash, ht range) projecting include ( rk ) billing mode provisioned (  10 rcu, 20 wcu )) stream ( new image ) table class standard infrequent access tags ( name : sampletable, type : demonstration );
CreateTable({
   "AttributeDefinitions":   [{
         "AttributeName":   "pk",
         "AttributeType":   "N"
      }, {
         "AttributeName":   "rk",
         "AttributeType":   "S"
      }, {
         "AttributeName":   "zip",
         "AttributeType":   "N"
      }, {
         "AttributeName":   "ht",
         "AttributeType":   "N"
      }],
   "TableName":   "sampletable",
   "KeySchema":   [{
         "AttributeName":   "pk",
         "KeyType":   "HASH"
      }, {
         "AttributeName":   "rk",
         "KeyType":   "RANGE"
      }],
   "GlobalSecondaryIndexes":   [{
         "IndexName":   "gsi1",
         "KeySchema":   [{
               "AttributeName":   "zip",
               "KeyType":   "HASH"
            }, {
               "AttributeName":   "ht",
               "KeyType":   "RANGE"
            }],
         "Projection":   {
            "ProjectionType":   "INCLUDE",
            "NonKeyAttributes":   ["rk"]
         },
         "ProvisionedThroughput":   {
            "ReadCapacityUnits":   10,
            "WriteCapacityUnits":   20
         }
      }],
   "BillingMode":   "PROVISIONED",
   "ProvisionedThroughput":   {
      "ReadCapacityUnits":   30,
      "WriteCapacityUnits":   20
   },
   "StreamSpecification":   {
      "StreamEnabled":   true,
      "StreamViewType":   "NEW_IMAGE"
   },
   "Tags":   [{
         "Key":   "name",
         "Value":   "sampletable"
      }, {
         "Key":   "type",
         "Value":   "demonstration"
      }],
   "TableClass":   "STANDARD_INFREQUENT_ACCESS"
})
CREATE

Data Manipulation Language

For the examples that follow, we use a table (sampletable) created as shown below.

create table sampletable ( pk number, rk string, zip number, ht number )
primary key ( pk hash, rk range)
billing mode provisioned ( 20 wcu, 30 rcu)
gsi ( gsi1 on (zip hash, ht range) projecting include ( rk ) billing mode provisioned (  10 rcu, 20 wcu ))
stream ( new image )
table class standard infrequent access
tags ( name : sampletable, type : demonstration );

Some sample data is loaded into this table.

{pk: 3, rk: three, x: 61}
{pk: 2, rk: one, x: 21, y: 43}
{pk: 4, rk: three, x: 11, y: 12, z: 13}
{pk: 4, rk: two, x: 11, y: 32}
{pk: 1, rk: one, x: 11}
{pk: 1, rk: three, y: 13}
{pk: 1, rk: two, y: 12}

DELETE

Purpose: Delete data from a table.

Syntax: DELETE FROM table_or_index [WHERE condition] [ratelimit] [return_values]

Deletes data from a table. When a WHERE clause is provided, items matching the condition are identified and deleted. If no WHERE clause is provided, all items are deleted.

DynamoDB only supports item at a time deletes (or batches which delete batches of items). Every item to be deleted must be identified by its primary key. Therefore, if the DELETE includes a where clause that completely specifies the primary key elements (hash key, and a range key if the table has one) then the item identified is deleted. If the WHERE clause does not completely specify the primary key, an appropriate SELECT is first executed to identify all items that match the WHERE clause, and then those items are deleted one at a time.

In standard SQL, a DELETE statement is an implicit transaction. In ddbsh, DELETE is NOT an implicit transaction. Each item is deleted individually. It is possible that an item is updated in the time between the SELECT and the subsequent DELETE. The item is only deleted if it matches the delete condition at the time when the DeleteItem() API is executed.

For information about rate limiting see the section on rate limiting. You can specify either a table or an index name in the UPDATE. If this is the update of a single item based on the PK and FK of the table, specifying an index name is immaterial. If a direct update of an item in the table based on a PK/FK is not possible, an index name will cause the query or scan to be performed on the index.

For information about return values see the section Return Values.

Example: 1

us-east-1> delete from sampletable where pk = 1 and rk = "one";
DELETE
us-east-1> select * from sampletable;
{pk: 3, rk: three, x: 61}
{pk: 2, rk: one, x: 21, y: 43}
{pk: 4, rk: three, x: 11, y: 12, z: 13}
{pk: 4, rk: two, x: 11, y: 32}
{pk: 1, rk: three, y: 13}
{pk: 1, rk: two, y: 12}
us-east-1>

Example: 2

Continuing from the previous example, consider this delete with a partial primary key specified. It would be executed as shown below.

us-east-1> explain delete from sampletable where rk = "two";
Scan({
   "TableName":   "sampletable",
   "ReturnConsumedCapacity":   "NONE",
   "ProjectionExpression":   "#a1, #a2",
   "FilterExpression":   "#a3 = :v1",
   "ExpressionAttributeNames":   {
      "#a1":   "pk",
      "#a2":   "rk",
      "#a3":   "rk"
   },
   "ExpressionAttributeValues":   {
      ":v1":   {
         "S":   "two"
      }
   },
   "ConsistentRead":   false
})
DeleteItem({
   "TableName":   "sampletable",
   "Key":   {
      "pk":   {
         "N":   "4"
      },
      "rk":   {
         "S":   "two"
      }
   },
   "ConditionExpression":   "#a1 = :v1",
   "ExpressionAttributeNames":   {
      "#a1":   "rk"
   },
   "ExpressionAttributeValues":   {
      ":v1":   {
         "S":   "two"
      }
   }
})
DeleteItem({
   "TableName":   "sampletable",
   "Key":   {
      "pk":   {
         "N":   "1"
      },
      "rk":   {
         "S":   "two"
      }
   },
   "ConditionExpression":   "#a1 = :v1",
   "ExpressionAttributeNames":   {
      "#a1":   "rk"
   },
   "ExpressionAttributeValues":   {
      ":v1":   {
         "S":   "two"
      }
   }
})

First the table is scanned (as the PK was not specified, a Scan() is the only option). The Scan returned one row which is then deleted.

Now consider another example of a partial primary key, this time specifying the PK.

Example: 3

us-east-1> explain delete from sampletable where pk = 4;
Query({
   "TableName":   "sampletable",
   "ConsistentRead":   false,
   "ReturnConsumedCapacity":   "NONE",
   "ProjectionExpression":   "#a1, #a2",
   "KeyConditionExpression":   "#a3 = :v1",
   "ExpressionAttributeNames":   {
      "#a1":   "pk",
      "#a2":   "rk",
      "#a3":   "pk"
   },
   "ExpressionAttributeValues":   {
      ":v1":   {
         "N":   "4"
      }
   }
})
DeleteItem({
   "TableName":   "sampletable",
   "Key":   {
      "pk":   {
         "N":   "4"
      },
      "rk":   {
         "S":   "three"
      }
   }
})
DeleteItem({
   "TableName":   "sampletable",
   "Key":   {
      "pk":   {
         "N":   "4"
      },
      "rk":   {
         "S":   "two"
      }
   }
})
DELETE
us-east-1>

The PK is specified, so we are able to perform a Query() which returns two rows which are then deleted one at a time.

INSERT

Purpose: Inserts an item into a table.

Syntax: INSERT INTO table ( ) VALUES [ratelimit]

Inserts an item into a table with the specified attribute values. All primary key attributes must be specified. If an item with the same primary key already exists, an error is generated.

For information about rate limiting see the section on rate limiting.

Example: 1

insert into sampletable ( pk, rk, x ) values ( 1, "one", 11 );
insert into sampletable ( pk, rk, y ) values ( 1, "two", 12 );
insert into sampletable ( pk, rk, y ) values ( 1, "three", 13 );
insert into sampletable ( pk, rk, x, y ) values ( 2, "one", 21, 43 );
insert into sampletable ( pk, rk, x ) values ( 3, "three",61 );
insert into sampletable ( pk, rk, x, y, z ) values ( 4, "three", 11, 12, 13 );
insert into sampletable ( pk, rk, x, y ) values ( 4, "two", 11, 32 );

Example: 2

us-east-1> insert into sampletable (pk, rk, conflict) values ( 3, "three", "conflict" );
[run, 83] INSERT failed. The conditional request failed
us-east-1>

REPLACE

Purpose: Replaces an item in a table, if one exists. Creates a new one if one does not exist.

Syntax: REPLACE INTO table ( ) VALUES [ratelimit] [return_values]

Inserts or replaces an item with the specified primary key. All primary key attributes must be specified. If an item already exists with that primary key, it is replaced.

For information about rate limiting see the section on rate limiting.

For information about return values see the section Return Values.

Example:

us-east-1> select * from sampletable where pk = 3 and rk = "three";
{pk: 3, rk: three, x: 61}
us-east-1> replace into sampletable ( pk, rk, rpl ) values ( 3, "three", "replaced" );
REPLACE
us-east-1> select * from sampletable where pk = 3 and rk = "three";
{pk: 3, rk: three, rpl: replaced}
us-east-1> select * from sampletable where pk = 12 and rk = "nonexistant";
us-east-1> replace into sampletable (pk, rk, ins ) values ( 12, "nonexistant", "inserted" );
REPLACE
us-east-1> select * from sampletable where pk = 12 and rk = "nonexistant";
{ins: inserted, pk: 12, rk: nonexistant}
us-east-1>

SELECT

Purpose: Selects items from a table or index.

Syntax: SELECT [CONSISTENT] * FROM *table*\[.*index*\] \[WHERE *condition*\] [consumed_capacity] [ratelimit]

Selects data from a table or index. If the optional CONSISTENT flag is specified, a consistent read is executed.

You can reference all scalar types (number, string and binary) as well as boolean, null, list and map in the projection list (columns) and in the where condition.

List elements are indexed with a 0 base. Map elements use ‘.’ as the separator.

For information about rate limiting see the section on rate limiting.

SELECT can return the consumed capacity during the operation.

consumed_capacity := RETURN CONSUMED CAPACITY INDEXES |
    RETURN CONSUMED CAPACITY TOTAL

Example: 1

us-east-1> select * from sampletable where pk = 12;
{ins: inserted, pk: 12, rk: nonexistant}
us-east-1>

Example: 2

The table has a GSI whose key definition is

gsi ( gsi1 on (zip hash, ht range) projecting include ( rk ) billing mode provisioned ( 10 rcu, 20 wcu ))

us-east-1> select * from sampletable.gsi1;
us-east-1> insert into sampletable ( pk, rk, zip, ht) values ( 71, "somewhere", 12345, 45);
INSERT
us-east-1> select * from sampletable.gsi1;
{ht: 45, pk: 71, rk: somewhere, zip: 12345}
us-east-1>

SELECT will execute either a GetItem(), Query() or Scan() depending on whether the request specifies a complete primary key (or not) and whether the request targets a table or index.

UPDATE

Purpose: Update items in a table.

Syntax: UPDATE table_or_index SET assignments [WHERE condition] [ratelimit] [return_values]

For information about rate limiting see the section on rate limiting. You can specify either a table or an index name in the UPDATE. If this is the update of a single item based on the PK and FK of the table, specifying an index name is immaterial. If a direct update of an item in the table based on a PK/FK is not possible, an index name will cause the query or scan to be performed on the index.

For information about return values see the section Return Values.

Updates items in the table with assignments as specified. The assignments can be of the form:

<column> = <value>

<column> = <column> +/- <value>

<column> = <column>

or

<column> = if_not_exists(*attribute*, *default*) \[+/- <value>\]

Example: 1

us-east-1> select * from sampletable where pk = 1;
{pk: 1, rk: one, x: 11}
{pk: 1, rk: three, y: 13}
{pk: 1, rk: two, y: 12}
us-east-1> update sampletable set newattr = 14 where pk = 1 and rk = "one";
UPDATE
us-east-1> select * from sampletable where pk = 1;
{newattr: 14, pk: 1, rk: one, x: 11}
{pk: 1, rk: three, y: 13}
{pk: 1, rk: two, y: 12}
us-east-1>

Example: 2

us-east-1> select * from sampletable where pk = 1;
{pk: 1, rk: one, x: 11}
{pk: 1, rk: three, y: 13}
{pk: 1, rk: two, y: 12}
us-east-1> update sampletable set newattr = if_not_exists(y, 3) + 6 where pk = 1;
UPDATE
us-east-1> select * from sampletable where pk = 1;
{newattr: 9, pk: 1, rk: one, x: 11}
{newattr: 19, pk: 1, rk: three, y: 13}
{newattr: 18, pk: 1, rk: two, y: 12}
us-east-1>

UPSERT

Purpose: Performs an UPDATE or INSERT

Syntax: UPSERT table_or_index SET assignments [WHERE condition] [ratelimit] [return_values]

If an item is matched by the WHERE clause, it is updated. If one does not match the where clause then the assignments are converted are used to construct a new item.

For information about rate limiting see the section on rate limiting. You can specify either a table or an index name in the UPSERT. If this is the update of a single item based on the PK and FK of the table, specifying an index name is immaterial. If a direct update of an item in the table based on a PK/FK is not possible, an index name will cause the query or scan to be performed on the index.

For information about return values see the section Return Values.

Example: 1

us-east-1> select * from sampletable where pk = 2 and rk = "one";
{pk: 2, rk: one, x: 21, y: 43}
us-east-1> upsert sampletable set op = "upsert" where pk = 2;
UPSERT
us-east-1> select * from sampletable where pk = 2 and rk = "one";
{op: upsert, pk: 2, rk: one, x: 21, y: 43}
us-east-1>

Example: 2

When using an UPSERT it is important to specify the complete primary key as that specification is used to create the new item.

us-east-1> select * from sampletable where pk = 7;
us-east-1> upsert sampletable set op = "upsert" where pk = 7 and rk = "seven";
UPSERT
us-east-1> select * from sampletable where pk = 7;
{op: upsert, pk: 7, rk: seven}
us-east-1>

EXPLAIN

Purpose: Explain the API calls performed to execute a DDL or DML command.

Syntax: EXPLAIN

The EXPLAIN command returns the API request that must be executed in order to perform the command provided.

Example

A table called sampletable was created with a primary key of (pk HASH, and rk RANGE).

us-east-1> explain select * from sampletable where pk = 13 and rk = "abc";
GetItem({
   "TableName":   "sampletable",
   "Key":   {
      "pk":   {
         "N":   "13"
      },
      "rk":   {
         "S":   "abc"
      }
   },
   "ConsistentRead":   false,
   "ReturnConsumedCapacity":   "NONE"
})

us-east-1> explain select * from sampletable where pk = 13 ;
Query({
   "TableName":   "sampletable",
   "ConsistentRead":   false,
   "ReturnConsumedCapacity":   "NONE",
   "KeyConditionExpression":   "#a1 = :v1",
   "ExpressionAttributeNames":   {
      "#a1":   "pk"
   },
   "ExpressionAttributeValues":   {
      ":v1":   {
         "N":   "13"
      }
   }
})

us-east-1> explain select * from sampletable where rk = "abc" ;
Scan({
   "TableName":   "sampletable",
   "ReturnConsumedCapacity":   "NONE",
   "FilterExpression":   "#a1 = :v1",
   "ExpressionAttributeNames":   {
      "#a1":   "rk"
   },
   "ExpressionAttributeValues":   {
      ":v1":   {
         "S":   "abc"
      }
   },
   "ConsistentRead":   false
})

Note that some commands (updated, delete, replace, and upsert) can return more than one command. For example, to do a range update on the table above, we would see.

us-east-1> explain update sampletable set something = 4 where rk = "abc";
Scan({
   "TableName":   "sampletable",
   "ReturnConsumedCapacity":   "NONE",
   "ProjectionExpression":   "#a1, #a2",
   "FilterExpression":   "#a3 = :v1",
   "ExpressionAttributeNames":   {
      "#a1":   "pk",
      "#a2":   "rk",
      "#a3":   "rk"
   },
   "ExpressionAttributeValues":   {
      ":v1":   {
         "S":   "abc"
      }
   },
   "ConsistentRead":   false
})
UpdateItem({
   "TableName":   "sampletable",
   "Key":   {
      "pk":   {
         "N":   "12"
      },
      "rk":   {
         "S":   "abc"
      }
   },
   "UpdateExpression":   "SET #a1 = :v1",
   "ConditionExpression":   "attribute_exists(#a2) AND attribute_exists(#a3) AND #a4 = :v2",
   "ExpressionAttributeNames":   {
      "#a1":   "something",
      "#a2":   "pk",
      "#a3":   "rk",
      "#a4":   "rk"
   },
   "ExpressionAttributeValues":   {
      ":v1":   {
         "N":   "4"
      },
      ":v2":   {
         "S":   "abc"
      }
   }
})
UPDATE

Data Manipulation Language - Special Topics

WHERE Clause

You can use a WHERE clause with SELECT, UPDATE, DELETE and UPSERT. In the WHERE clause, you can include multiple logical expressions which 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.

Examples: First some 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 (*)>

Inequality comparison with AND

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

The BETWEEN operator

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}

The BEGINS_WITH operator

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

The ATTRIBUTE_EXISTS operator

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}

The ATTRIBUTE_TYPE operator

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

Using parenthesis to group items in a WHERE clause


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 always see how ddbsh executes any command with the EXPLAIN command.

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 (*)>

Now delete 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 (*)>

Rate Limiting

Note: Rate Limiting was added in DynamoDB Shell version 0.5.

DML operations like SELECT, INSERT, UPDATE, DELETE, UPSERT, and REPLACE can operate on a single item, or many items. When a SELECT has a where clause that targets many items, it is translated into a Query() or Scan() operation. An INSERT may be used to insert a single item, or many items. Each of these activities consumes a certain number of RCUs and WCUs. An operation that operates on many items is called a bulk operation.

Bulk operations can interfere with foreground applications and it is therefore useful to be able to rate limit these operations. This can be accomplished by providing a rate limiting clause.

The rate limiting clause takes the form.

ratelimit := WITH RATELIMIT ( RR RCU, WW WCU ) |
             WITH RATELIMIT ( RR RCU ) |
             WITH RATELIMIT ( WW WCU )

Consider this example.

us-east-1> create table rate_limit_test ( a number, b number ) primary key ( a hash ) gsi ( gsione on (b hash) projecting all);
CREATE
us-east-1> insert into rate_limit_test (a, b) values ( 1, 2 ), (2, 3), (3, 4) with ratelimit ( 1 wcu );
INSERT
INSERT
INSERT
us-east-1>

When this insert is performed, each write to the table consumes 2 WCUs (one for the base table and one for the index). Since the operation is issued with a ratelimit of 1 WCU, a delay is added automatically after each INSERT.

Rate limiting is implemented with token buckets. An operation can specify a read and a write limit, each gets its own token bucket. All RCUs consumed by operations count against the read limit (read token bucket) and all writes to base tables and indices count against the write limit (write token bucket).

Consider this example.

us-east-1> update rate_limit_test set updated = true with ratelimit ( 1 rcu, 1 wcu );
UPDATE (3 read, 3 modified, 0 ccf)
us-east-1>

Each of these updates will consume 2 WCU and therefore a similar delay will be inserted after each to bring the write limit down to 1 WCU. As this is a bulk opeartion, it is performed as a read (in this case, a Scan) followed by writes. The Scan() will be rate limited to the read limit.

Rate limiting works by performing read and write operations, and looking at the consumed capacity of these. If a read rate is specified, a token bucket is created for that. If a write rate is specified, a token bucket is created for that. Each token bucket accumulates tokens at the specified rate. When there are read tokens available, the operation can be performed.

So, assume that this query is issued:

us-east-1> update customers set state = "CA" where zip = "90210" with ratelimit ( 100 rcu, 500 wcu );

Reads against the customers table are issued at a read limit of up to 100 RCU/s and updates are done at a limit of up to 500 WCU/s. Assume that the Scan() on the customers table is attempted when there are 2 tokens in the read bucket, and the Scan() consumes 60 RCU. The read token bucket now goes to -58 and it will take about 0.6s before it has a positive number of tokens. Within 0.6s, any attempt to read will block. This same mechanism is also applied to writes. For long running operations, we ensure that neither token bucket accumulates more than one second’s credit of tokens.

Finally, if a table (customers) has a GSI on the column zip called zipgsi.

The query

us-east-1> explain update customers set state = "CA" where zip = "90210" with ratelimit (10 rcu, 20 wcu);
Scan({
   "TableName":   "customers",
   "ReturnConsumedCapacity":   "TOTAL",
   "ProjectionExpression":   "#abaa1",
   "FilterExpression":   "#abaa2 = :vbaa1",
   "ExpressionAttributeNames":   {
      "#abaa1":   "custid",
      "#abaa2":   "zip"
   },
   "ExpressionAttributeValues":   {
      ":vbaa1":   {
         "S":   "90210"
      }
   },
   "ConsistentRead":   false
})
[...]

would result in a table scan on the table customers.

However, the query

us-east-1> explain update customers.zipgsi set state = "CA" where zip = "90210" with ratelimit (10 rcu, 20 wcu);
Query({
   "TableName":   "customers",
   "IndexName":   "zipgsi",
   "ConsistentRead":   false,
   "ReturnConsumedCapacity":   "TOTAL",
   "ProjectionExpression":   "#adaa1",
   "KeyConditionExpression":   "#adaa2 = :vdaa1",
   "ExpressionAttributeNames":   {
      "#adaa1":   "custid",
      "#adaa2":   "zip"
   },
   "ExpressionAttributeValues":   {
      ":vdaa1":   {
         "S":   "90210"
      }
   }
})
[...]

would be processed as a query on the GSI and an update to the table. Performing an update/delete/upsert in this way reduces the cost by leveraging an index where available.

Return Values

Support for Return values was added in ddbsh version 0.6.1.

One of the very cool features of DynamoDB is that mutating APIs allow you to return an item either before or after the mutation (at no extra cost). This has some very interesting applications. First, what are return values?

This description relates to mutations that are not part of a transaction.

The mutating APIs are PutItem, DeleteItem and UpdateItem. You can read more about return values here.

In DynamoDB Shell, you can specify return values in UPDATE, UPSERT, REPLACE, and DELETE. For obvious reasons, it is not supported on INSERT.

Consider the following example that illustrates the implementation of a simple counter.

First, we create a counter table as shown below.

us-east-1> create table counter (id string) primary key (id hash);
CREATE
us-east-1> insert into counter (id, value) values ("ticketId", 1);
INSERT
us-east-1> select * from counter;
{id: ticketId, value: 1}
us-east-1>

To get the next available counter value and update the counter at the same time, we can perform an update like this.

update counter set value = value + 1 where id = "ticketId" return values updated old;
{value: 1}
UPDATE (0 read, 1 modified, 0 ccf)
us-east-1> select * from counter;
{id: ticketId, value: 2}
us-east-1>

The UPDATE above did three things:

  1. It captured the value of the “value” attribute for the item where id = “ticketId”, and
  2. It incremented the value, and
  3. It returned the value before the UPDATE.

Instead of UPDATED OLD, we could fetch the whole OLD item.

us-east-1> update counter set value = value + 1 where id = "ticketId" return values all old;
{id: ticketId, value: 2}
UPDATE (0 read, 1 modified, 0 ccf)
us-east-1> select * from counter where id = "ticketId";
{id: ticketId, value: 3}
us-east-1>

Return Values can also be used in bulk operations. Assume that you had a table with many counters, you could reinitialize them all in this way.

us-east-1> select * from counter;
{id: ticketId, value: 3}
{id: pageId, value: 27}
{id: linkId, value: 403}
{id: customerId, value: 20071}
us-east-1> update counter set value = 1 return values all old;
{id: ticketId, value: 3}
{id: pageId, value: 27}
{id: linkId, value: 403}
{id: customerId, value: 20071}
UPDATE (4 read, 4 modified, 0 ccf)
us-east-1> select * from counter;
{id: ticketId, value: 1}
{id: pageId, value: 1}
{id: linkId, value: 1}
{id: customerId, value: 1}
us-east-1>

You can similarly use return values with UPSERT, if there is an old item, it will be returned.

us-east-1> select * from counter where id = "streetId";
us-east-1> upsert counter set value = 5 where id = "streetId" return values all old;

UPSERT (0 read, 1 modified, 0 ccf)
us-east-1>

us-east-1> select * from counter where id = "streetId";
{id: streetId, value: 5}
us-east-1> upsert counter set value = 16 where id = "streetId" return values all old;
{id: streetId, value: 5}
UPSERT (0 read, 1 modified, 0 ccf)
us-east-1> select * from counter where id = "streetId";
{id: streetId, value: 16}
us-east-1>

And when you delete, you can get the item being deleted.

us-east-1> delete from counter return values all old;
{id: streetId, value: 16}
{id: ticketId, value: 1}
{id: pageId, value: 1}
{id: linkId, value: 1}
{id: customerId, value: 2067}
DELETE (5 read, 5 modified, 0 ccf)
us-east-1>

Note: It is cheaper to implement a monotonically increasing counter using return values than it is to do it with a read followed by a conditional write. The return of values is free so all you pay for is a single write with no condition.

On-Demand Throughput

DynamoDB supports throughput limiting on On-Demand tables.

On-Demand throughput can be set on tables and indexes. The current settings can be displayed with the describe command.

For example, here is the creation of a table with throughput limits specified.

create table customer ( pk number, a number ) primary key (pk hash) billing mode on demand throughput (40 rcu, 30 wcu)
          gsi (gsipka on ( a hash ) projecting all billing mode on demand throughput (50 rcu, 70 wcu))

You can observe these settings as shown below.

us-east-1> describe customer;
Name: customer (ACTIVE)
Billing Mode: On Demand
On Demand Throughput: (40 RCU, 30 WCU)
[...]
GSI gsipka: ( HASH a ), Billing Mode: On Demand (mirrors table), Throughput (50 RCU, 70 WCU), Projecting (ALL), Status: ACTIVE, Backfilling: NO
[...]
us-east-1>

You can update the on demand throughput like this, here it resets the table to have unlimited WCUs.

us-east-1> alter table customer set billing mode on demand throughput (50 rcu, -1 wcu);
ALTER
us-east-1> describe customer;
[...]
Billing Mode: On Demand
On Demand Throughput: (50 RCU, -1 WCU)

And here the throughput limit is removed altogether.

us-east-1> alter table customer set billing mode on demand throughput unlimited;
ALTER
us-east-1> describe customer;
[...]
Billing Mode: On Demand
PITR is Disabled.

The index throughputs can also be updated.

us-east-1> alter table customer update gsi ( gsipka set billing mode on demand throughput (-1 rcu, 4 wcu));
ALTER
us-east-1> describe customer;
[...]
GSI gsipka: ( HASH a ), Billing Mode: On Demand (mirrors table), Throughput (-1 RCU, 4 WCU), Projecting (ALL), Status: ACTIVE, Backfilling: NO

Or removed altogether.

us-east-1> alter table customer update gsi ( gsipka set billing mode on demand throughput unlimited);
ALTER
us-east-1> describe customer;
[...]
GSI gsipka: ( HASH a ), Billing Mode: On Demand (mirrors table), Projecting (ALL), Status: ACTIVE, Backfilling: NO
[...]

Warm Throughput

DynamoDB added support for warm throughput for tables and indexes. You can now specify warm throughput during table/index creation, or subsequently using ALTER TABLE.

For example, you could create a table with a large warm throughput as an on demand table, without having to switch to provisioned and such.

us-east-1> explain create table customers (custid number) primary key (custid hash) warm throughput (2000000 rcu, 1000000 wcu);
CreateTable({
   "AttributeDefinitions":   [{
         "AttributeName":   "custid",
         "AttributeType":   "N"
      }],
   "TableName":   "customers",
   "KeySchema":   [{
         "AttributeName":   "custid",
         "KeyType":   "HASH"
      }],
   "BillingMode":   "PAY_PER_REQUEST",
   "TableClass":   "STANDARD",
   "DeletionProtectionEnabled":   false,
   "WarmThroughput":   {
      "ReadUnitsPerSecond":   2000000,
      "WriteUnitsPerSecond":   1000000
   }
})
CREATE

The above would create an on demand table with 2MM RCU and 1MM WCU (if your account permits this).

Similarly, you could add a new index to a table called customertxns this way.

us-east-1> alter table customertxns (custid string, txid string) create gsi txngsi on (txid hash, custid range) projecting all warm throughput (15000 rcu, 4000 wcu);
ALTER
us-east-1>

You could see the warm throughput of the table in the output of DESCRIBE like this.

us-east-1> describe customertxns;
Name: customertxns (ACTIVE)
Key: HASH custid
Attributes:  custid, S,  txid, S
Created at: 2024-09-30T00:57:12Z
...
Table size (bytes): 0
Item Count: 0
Deletion Protection: Disabled
Billing Mode: On Demand
Warm Throughput: (12000 RCU, 4000 WCU, Status ACTIVE)
PITR is Disabled.
GSI txngsi: ( HASH txid, RANGE custid ), Billing Mode: On Demand (mirrors table), Projecting (ALL), Status: CREATING, Backfilling: YES, Warm Throughput: (15000 RCU, 4000 WCU, Status UPDATING)
LSI: None
Stream: Disabled
Table Class: STANDARD
SSE: Not set
us-east-1>

Contributing

If you find bugs or errors of any kind, please do let the maintainers know. If you are so inclined, please let the maintainers know that you would like to submit pull-requests. We will do our best to respond in a timely manner and where possible accept pull-requests and changes.

Direction

Here are some things that need to be done:

  1. Complete support for AM-CMK and CM-CMK. There is code in CREATE TABLE and ALTER TABLE to handle this but it has not been exhaustively tested (and that is the reason it does not show up in the HELP or this README). More work is required to ensure that it is performing as expected.

  2. Update online help and README.

  3. Add support for additional language constructs. This is a delicate balancing act between attempting to build all of SQL against DynamoDB (which we don’t want to do) and providing some more SQL constructs which will help users. For example, we do intend to add support for client side implementations of COUNT and COUNT DISTINCT. Once COUNT DISTINCT is implemented, it is a small matter of providing language support for GROUP BY which is something that I do want to do. However, I explicitly do not want to add support for client-side joins - they are not a construct that is natively supported by DynamoDB. This is, however, a matter of some heated discussions among the development team.

  4. Add support for DynamoDB DAX.

  5. Add more tests.

  6. Distribute signed binaries.

Licensing

ddbsh is licensed under the terms of the Apache Version 2.0 (January 2004) license. See the License file here.

This site is open source. Improve this page.