June 5, 2023

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.

$ 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

1 comment:

  1. Amazon QuickSight is a cloud-based business intelligence (BI) and analytics service offered by Amazon Web Services (AWS). It allows users to create and share interactive dashboards, visualizations, and reports from a variety of data sources. QuickSight is designed to be fast, scalable, and cost-effective, making it an attractive option for organizations of all sizes.One of the key benefits of Amazon QuickSight is its ease of use. For more information about the Amazon QuickSight consulting services, call us @ 1-289-952-8845.

    ReplyDelete