AWS DynamoDB Shell - ddbsh

DynamoDB Shell (ddbsh) commands in AWS

An open-source command line interface (CLI) and interactive shell to run SQL-like DDL and DML commands on Amazon DynamoDB (DDB). And these SQL commands will be automatically translated to DynamoDB queries. DynamoDB Shell is written in C++ and uses the DynamoDB API through the AWS DynamoDB SDK.

For installation and building the DynamoDB Shell tool, please refer to the project’s GitHub page.

https://github.com/awslabs/dynamodb-shell/blob/main/README.md

$ ddbsh

help update;

help;

HELP - provide help in ddbsh

   HELP <keyword> [keyword [keyword ...]]

      Provides help about the specified keyword, or statement.

      HELP ALTER TABLE

      HELP BACKUP

      HELP BEGIN

      HELP COMMIT

      HELP CONNECT

      HELP CREATE

      HELP DELETE

      HELP DESCRIBE BACKUP

      HELP DESCRIBE

      HELP DROP BACKUP

      HELP DROP TABLE

      HELP DROP

      HELP EXPLAIN

      HELP INSERT

      HELP REPLACE

      HELP RESTORE

      HELP ROLLBACK

      HELP SELECT

      HELP SHOW BACKUPS

      HELP SHOW CREATE TABLE

      HELP SHOW LIMITS

      HELP SHOW VERSION

      HELP SHOW

      HELP UPDATE

      HELP UPSERT

connect us-east-2;

show version;

show limits;

show tables;

begin;

commit;

rollback;

describe dynamodb_tab;

show create table ddbsh_demo;

CREATE TABLE commands in DynamoDB shell

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] ;

create table ddbsh_demo (id number) primary key (id hash);

create table ddbsh_demo2 (pk number, rk number) primary key (pk hash, rk range);

create table ddb_tab ( a number, b number ) primary key ( a hash ) gsi ( gsione on (b hash) projecting all);

create table ddb_table ( id string, name string ) primary key ( id hash ) billing mode provisioned ( 5 rcu, 5 wcu ) gsi ( namegsi on (name hash) projecting all billing mode provisioned ( 5 rcu, 5 wcu ));

create table if not exists nowait ddbsh_demo3 (id string, accttype string, balance number) primary key (id hash, accttype range) billing mode provisioned (20 rcu, 20 wcu) gsi (balancegsi on (accttype hash, balance range) projecting all billing mode provisioned (20 rcu, 20 wcu)) stream (both images);

ALTER TABLE commands on DynamoDB tables using DDB shell

alter table dynamodb_tab set billing mode on demand;

alter table dynamodb_tab set billing mode provisioned (200 RCU, 300 WCU);

alter table dynamodb_tab set table class standard infrequent access;

alter table dynamodb_tab set table class standard;

alter table dynamodb_tab set stream (new image);

alter table dynamodb_tab set stream disabled;

alter table dynamodb_tab set pitr enabled;

alter table dynamodb_tab set pitr disabled;

alter table dynamodb_tab set deletion protection disabled;

alter table dynamodb_tab set deletion protection enabled;

alter table ddb_tab (x number, y number) create gsi xygsi on (x hash, y range) projecting all billing mode provisioned (20 RCU, 40 WCU);

alter table ddb_tab (x number, y number) create gsi xykeysgsi on (x hash, y range) projecting keys only billing mode provisioned (22 RCU, 41 WCU);

alter table ddb_tab (x number, y number, a number) create gsi xyagsi on (x hash) projecting include (y, a) billing mode provisioned (25 RCU, 50 WCU);

alter table ddb_tab set billing mode provisioned (20 rcu, 30 wcu) update gsi (pqgsi set billing mode provisioned (20 rcu, 30 wcu), xykeysgsi set billing mode provisioned (3 rcu, 5 wcu));

alter table ddb_tab drop gsi xygsi;

alter table ddbsh_demo add replica us-east-2;

alter table ddbsh_demo add replica us-west-2 table class standard infrequent access;

alter table ddbsh_demo drop replica us-east-2;

alter table ddbsh_demo set ttl (created_epoch_time);

alter table ddbsh_demo set ttl disabled;

If you want to add new column(s), there is NO command like alter table table-name add column column-name, you have to use INSERT or UPDATE or UPSERT commands with values for new columns (along with existing columns).

DROP TABLE commands on Amazon DynamoDB tables

drop table ddbsh_demo;

drop table if exists ddb_tab;

SELECT queries/commands on DynamoDB tables using DynamoDB shell

SELECT [CONSISTENT] attribute_list | * FROM <table>[.<index>] [WHERE where_clause] [return_clause | ratelimit] ]

select * from ddbsh_demo;

select * from ddbsh_demo where string_col = "available";

select * from ddbsh_demo where a = 5;

select id, a from ddbsh_demo where a > 3 and a < 5;

select * from ddbsh_demo where a between 2 and 5;

select * from ddbsh_demo where rangekey in (3, 9, 4, 7);

select consistent * from ddbsh_demo where attribute_exists(c) and a != 8;

select * from ddbsh_demo where attribute_exists(c) or a = 33;

select * from ddbsh_demo where attribute_exists(c);

select * from ddbsh_demo where not attribute_exists(amount);

select * from ddbsh_demo where attribute_type(b, string);

select * from ddbsh_demo where attribute_type(id, number) return total;

select * from ddbsh_demo where begins_with(b, "co");

select * from ddbsh_demo where contains(pk, "at");

select a, b, c from ddbsh_demo where attribute_type(b, string) or (a = 5 and c = "coffee");

select * from ddbsh_demo where size(id) < 5;

select * from ddbsh_demo where v.c = true;

select * from ddbsh_demo where v.b[1] = 11;

select * from ddbsh_demo.gsi1;

select * from ddb_tab.gsi2 where name = "Brutus";

select * from ddbsh_demo where a between 2 and 5 WITH RATELIMIT (5 RCU);

select * from ddbsh_demo where a between 2 and 5 WITH RATELIMIT (2 RCU, 2 WCU);

INSERTing records INTO DDB tables with DynamoDB shell

insert into ddbshell (custid, name) values (103, "Charlie");

insert into ddbshell (custid, name) values (101, "Alice"), (102, "Bob");

insert into ddbshell (pk, rk, x, y, z) values (4, "three", 11, 12, 13);

insert into ddbshell (id, v) values (3, 4), (4, "a string value"), (5, {a: 4, b: [10, 11, 12], c: true, d: {x: 10, y: 10}});

insert into ddb_tab (a, b) values ( 1, 2 ), (2, 3), (3, 4) with ratelimit ( 2 wcu );

REPLACE INTO <table> ( column [, column ...] ) VALUES ( values ) [ratelimit]

replace into ddbshell (pk, rk, ins) values (12, "nonexistant", "inserted");

UPDATE commands on Amazon DynamoDB tables using DDBShell

update ddbsh_demo set b = 15 where a = 5;

update ddb_tab age= 14 where name = "Brutus";

update ddbsh_demo set active = True where custid = 111;

update ddbsh_demo set newattr = 14 where pk = 1 and rk = "one";

update ddbsh_demo set newattr = if_not_exists(y, 3) + 6 where pk = 1;

update ddb_tab set balance = balance + 100 where id = "Satya" and accttype = "Savings";

update ddbsh_demo set z = 14, v.b[1] = 13 where id = 5;

update ddb_tab set updated = true with ratelimit ( 10 rcu, 5 wcu );

update ddbsh_demo remove c where a = 5; (works like alter table table-name drop column column-name)

UPSERT <name> SET <upsert_set> [where clause] [ratelimit]

upsert ddbsh_demo set op = "upsert" where pk = 2;

Deleting records from DynamoDB tables using DDB shell

delete from ddb_tabble where name = "Brutus";

delete from ddbsh_demo where pk = 1 and rk = "one";

Backup and Restore of AWS DynamoDB tables

backup table dynamodb_tab called dynamodb-table-backup;

show backups;

describe backup "arn:aws:dynamodb:us-east-2:1234567890:table/dynamodb_tab/backup/1681480720-2a11c134";

drop backup "arn:aws:dynamodb:us-west-2:1234567890:table/backup_test/ddbsh_demo/1681480720-2a11c134";

restore table "dynamodb_tab" from backup "arn:aws:dynamodb:us-east-2:1234567890:table/dynamodb_tab/backup/1681480720-2a11c134";

RESTORE TABLE new table name FROM old table name TO PITR "YYYY-MM-DDTHH:MM:SSZ";

Getting EXPLAIN plan for DynamoDB (shell) queries

explain select * from ddbsh_demo2;

explain select * from dynamodb_tab where attribute_type(b, string) or (a = 5 and c = "coffee");

explain create table ddbsh_demo (id string, name string) primary key (id hash) billing mode provisioned (5 rcu, 5 wcu) gsi (namegsi on (name hash) projecting all billing mode provisioned (5 rcu, 5 wcu));

explain alter table ddb_tab add replica us-west-2;

explain alter table ddbsh_demo (v number) create gsi gsi_v on (v hash) projecting all billing mode provisioned (10 rcu, 20 wcu);

explain update ddbsh_demo set z = 14, v.b[6] = 13 where id = 5;

explain update ddb_table.zipgsi set state = "CA" where zip = "90210" with ratelimit (10 rcu, 20 wcu);

explain delete from ddb_tab where rk = "two";

Related AWS Cloud Articles:  AWS Cloud Database/DBA Interview Questions