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.
$ 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
