Equivalent of insert or update in DynamoDB for counting

Question

I'm trying to keep track of hits from IP addresses.

Typically, in a SQL database, you could do something along the lines of:

CREATE TABLE website_hits 
(
    ip VARCHAR(45) PRIMARY KEY, 
    hits INTEGER NOT NULL
);

INSERT INTO website_hits 
VALUES ("1.2.3.4", 1) 
ON DUPLICATE KEY UPDATE hits = hits + 1;

DynamoDB provides functionality to perform atomic updates where an update expression is provided as part of the UpdateItem request, which means it cannot be used to create an entry similar to the INSERT query shown above:

$ aws dynamodb update-item --table-name WebsiteHits --key '{"ip":{"S":"1.2.3.4"}}' --update-expression 'SET hits = hits + :incr' --expression-attribute-values '{":incr":{"N":"1"}}' --return-values UPDATED_NEW
An error occurred (ValidationException) when calling the UpdateItem operation: The provided expression refers to an attribute that does not exist in the item

The apparent lack of this feature would leave me with having to do a GetItem followed by an UpdateItem, which will cause an incorrect value to be written back to the database if there are multiple processes trying to write to the database at the same time.

How would I go about implementing an insert or update similar to the ON DUPLICATE KEY UPDATE statement shown above?

Answer 1

The reason you are seeing this exception is due to the fact no hits attribute exists in your item currently, causing the exception:

The provided expression refers to an attribute that does not exist in the item

---

1. Use ADD instead of SET

This is the recommended approach. It automatically sets the value to 0 should it not exist, then adds your provided value. The only time you would use option #2 is when the attribute is stored within a nested value, for which ADD will not work.

Use the ADD action in an update expression to add a new attribute and its values to an item. DOCS

• ADD path value

python

table.update_item(
    Key={'ip': '1.2.3.4'},
    UpdateExpression="ADD #hits :incr",
    ExpressionAttributeNames={'#hits': 'hits'},
    ExpressionAttributeValues={':incr': 1}
)

bash

$ aws dynamodb update-item \
--table-name WebsiteHits \ 
--key '{"ip":{"S":"1.2.3.4"}}' \
--update-expression 'ADD #hits :incr' \
--expression-attribute-values '{":incr":{"N":"1"}}' \
--return-values UPDATED_NEW

2. Use if_not_exists Function

If the item does not contain an attribute at the specified path, if_not_exists evaluates to value; otherwise, it evaluates to path.

This approach requires you to set a value which is initially set if the attribute should not exist: :empty=0. This is more of a workaround than how you are recommended to add values in DynamoDB.

• if_not_exists (path, value)

python

table.update_item(
    Key={'voter': 'default_voter'},
    UpdateExpression="set #hits = if_not_exists(#hits, :empty) + :incr",
    ExpressionAttributeNames={'#hits': 'hits'},
    ExpressionAttributeValues={':incr': 1, ':empty': 0}
)

bash

$ aws dynamodb update-item \
--table-name WebsiteHits \ 
--key '{"ip":{"S":"1.2.3.4"}}' \
--update-expression 'set #hits = if_not_exists(#hits, :empty) + :incr' \
--expression-attribute-values '{":incr":{"N":"1"}}' \
--return-values UPDATED_NEW