Tag: ddbsh

Yesterday I posted a quick introduction to dynamodb-shell. Let’s go a little bit further today. ddbsh has quit a lot of bells and whistles for creating tables.

ap-south-1> help create table;
CREATE TABLE - Creates a DynamoDB table.

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

   table_class := TABLE CLASS STANDARD | TABLE CLASS STANDARD INFREQUENT ACCESS

   tags := TAGS ( tag [, tag ...] )
   tag := name : value

Let’s make a table with a GSI and with DynamoDB Streams enabled. Since the CREATE TABLE command included “nowait”, the command completed immediately, and a subsequent describe shows that the table is being created.

ap-south-1> create table if not exists nowait balances ( id string, accttype string, balance number )
ap-south-1> primary key ( id hash, accttype range )
ap-south-1> billing mode provisioned ( 20 rcu, 20 wcu )
ap-south-1> gsi (balancegsi on (accttype hash, balance range) projecting all
ap-south-1>      billing mode provisioned ( 20 rcu, 20 wcu ))
ap-south-1> stream (both images);
CREATE
ap-south-1> describe balances;
Name: balances (CREATING)
Key: HASH id, RANGE accttype
Attributes:  accttype, S,  balance, N,  id, S
Created at: 2023-01-26T05:01:41Z
Table ARN: arn:aws:dynamodb:ap-south-1:632195519165:table/balances
Table ID: d84f734e-10e9-4c2d-a946-ed8820b82430
Table size (bytes): 0
Item Count: 0
Billing Mode: Provisioned (20 RCU, 20 WCU)
PITR is Disabled.
GSI balancegsi: ( HASH accttype, RANGE balance ),  Provisioned (20 RCU, 20 WCU), Projecting (ALL), Status: CREATING, Backfilling: NO
LSI: None
Stream: NEW_AND_OLD_IMAGES
Table Class: STANDARD
SSE: Not set
ap-south-1> 

After a few minutes, the table is created and we can start loading up some data.

ap-south-1> insert into balances ( id, accttype, balance) 
ap-south-1> values ( "Alice", "Savings", 200 ),
ap-south-1> ("Alice", "Checking", 500 ),
ap-south-1> ("Bob", "Checking", 250 ),
ap-south-1> ("Charlie", "Savings", 500),
ap-south-1> ("Charlie", "Checking", 200),
ap-south-1> ("David", "Savings", 1000);
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT

Now, let’s assume that I want to see all the “Savings” account balances, I could do this:

ap-south-1> select * from balances where accttype = "Savings";
{accttype: Savings, balance: 200, id: Alice}
{accttype: Savings, balance: 500, id: Charlie}
{accttype: Savings, balance: 1000, id: David}
ap-south-1> 

Since the table has a primary key of (id, accttype), this becomes a scan. How do I know that, because I can explain the query.

ap-south-1> explain select * from balances where accttype = "Savings";
Scan({
   "TableName":   "balances",
   "ReturnConsumedCapacity":   "NONE",
   "FilterExpression":   "#ahaa1 = :vhaa1",
   "ExpressionAttributeNames":   {
      "#ahaa1":   "accttype"
   },
   "ExpressionAttributeValues":   {
      ":vhaa1":   {
         "S":   "Savings"
      }
   },
   "ConsistentRead":   false
})
ap-south-1> 

But, I do have a GSI on the account type (and balance), so I can do one better. I can attempt the query against the GSI (observe that where I had a table, I now have balances.balancegsi which is how I reference the GSI). This turns into a Query on the index instead of a table scan.

ap-south-1> select * from balances.balancegsi where accttype = "Savings";
{accttype: Savings, balance: 200, id: Alice}
{accttype: Savings, balance: 500, id: Charlie}
{accttype: Savings, balance: 1000, id: David}
ap-south-1> explain select * from balances.balancegsi where accttype = "Savings";
Query({
   "TableName":   "balances",
   "IndexName":   "balancegsi",
   "ConsistentRead":   false,
   "ReturnConsumedCapacity":   "NONE",
   "KeyConditionExpression":   "#akaa1 = :vkaa1",
   "ExpressionAttributeNames":   {
      "#akaa1":   "accttype"
   },
   "ExpressionAttributeValues":   {
      ":vkaa1":   {
         "S":   "Savings"
      }
   }
})

One other thing, let’s assume that I want to transfer some money from one account to another without the source account going negative – how would I do that?

Let’s say that Alice wants to transfer $100 from Checking to Savings. We could do this.

ap-south-1> select * from balances where id = "Alice";
{accttype: Checking, balance: 500, id: Alice}
{accttype: Savings, balance: 200, id: Alice}
ap-south-1> begin;
ap-south-1> update balances set balance = balance - 100 where id = "Alice" and accttype = "Checking" and balance >= 100;
ap-south-1> update balances set balance = balance + 100 where id = "Alice" and accttype = "Savings";
ap-south-1> commit;
COMMIT
ap-south-1> select * from balances where id = "Alice";
{accttype: Checking, balance: 400, id: Alice}
{accttype: Savings, balance: 300, id: Alice}
ap-south-1> 

For example, let’s say that Bob wants to give $300 to David. Should that be allowed?

ap-south-1> select * from balances where id = "Bob";
{accttype: Checking, balance: 250, id: Bob}
ap-south-1> 

Let’s see what happens if we try …

ap-south-1> begin;
ap-south-1> update balances set balance = balance - 300 where id = "Bob" and balance >= 300 and accttype = "Checking";
ap-south-1> update balances set balance = balance + 300 where id = "David" and accttype = "Savings";
ap-south-1> commit;
Transaction failed. TransactionCanceledException. 3D0KI0CHVS7UDTFDMAQ0E43EINVV4KQNSO5AEMVJF66Q9ASUAAJG. Transaction cancelled, please refer cancellation reasons for specific reasons [ConditionalCheckFailed, None]
ABORT
ap-south-1> select * from balances where id in ("Bob", "David");
{accttype: Checking, balance: 250, id: Bob}
{accttype: Savings, balance: 1000, id: David}
ap-south-1> 

The Condition Check Failure is a list of two statuses – the first one failed (ConditionCheckFailed) and the other produced no error. So there you have it,

• a table with a GSI and you can query either the table or the GSI (specify table.gsi)
• you can do multi-line transactions
• you can use functions like IN in the where clause.

In the next blog post I’ll dig further into all that you can do in a WHERE clause.

I’ll leave it up to you to run explain on the transaction and see what it does. Hint, just change “begin” to “explain begin”.