I read through the instructor's journal here to be able to complete this week.
Implemented a couple of scripts for loading schema, dropping tables and seed data into dynamodb locally. For a complete reference see the dynamodb scripts to test locally here
To test out the scripts and access patterns locally, I ran the scritps with the commands below.
To create a sample table cruddur-messages, I load the schema using the script below
./bin/ddb/schema-loadAfter loading the schema, I can confirm if the table has been created.
./bin/ddb/list-tablesOutput
gitpod /workspace/aws-bootcamp-cruddur-2023/backend-flask (week-5) $ ./bin/ddb/list-tables
----------------------
| ListTables |
+--------------------+
| Music |
| cruddur-messages |
+--------------------+
gitpod /workspace/aws-bootcamp-cruddur-2023/backend-flask (week-5) $
To be able to seed some data, I need to make sure I have some users loaded/seeded in my local postgresql database. I first perform a database setup. Set some local environment variables.
echo $LOCAL_CONNECTION_URLOutput
postgresql://postgres:password@localhost:5432/cruddur
Set DATABASE_URL which is referenced in the db script
export DATABASE_URL=$LOCAL_CONNECTION_URLConnect, load schema and seed data into postgresql database
./bin/db/setupgitpod /workspace/aws-bootcamp-cruddur-2023/backend-flask (week-5) $ ./bin/db/connect
psql (13.10 (Ubuntu 13.10-1.pgdg20.04+1))
Type "help" for help.
cruddur=# \d
List of relations
Schema | Name | Type | Owner
--------+------------+-------+----------
public | activities | table | postgres
public | users | table | postgres
(2 rows)
cruddur=# \x on
Expanded display is on.
cruddur=# SELECT * FROM users;
gitpod /workspace/aws-bootcamp-cruddur-2023/backend-flask (week-5) $ ./bin/db/connect
psql (13.10 (Ubuntu 13.10-1.pgdg20.04+1))
Type "help" for help.
cruddur=# \d
List of relations
Schema | Name | Type | Owner
--------+------------+-------+----------
public | activities | table | postgres
public | users | table | postgres
(2 rows)
cruddur=# \x on
Expanded display is on.
cruddur=# SELECT * FROM users;
-[ RECORD 1 ]---+-------------------------------------
uuid | ea76091e-1a0d-4b57-a57a-5c9c0965c061
display_name | Andrew Brown
handle | andrewbrown
email | [email protected]
cognito_user_id | MOCK
created_at | 2023-03-23 22:20:57.088515
-[ RECORD 2 ]---+-------------------------------------
uuid | 6fddb47e-6739-46b0-ac13-e7f3e78aa2e0
display_name | Andrew Bayko
handle | bayko
email | [email protected]
cognito_user_id | MOCK
created_at | 2023-03-23 22:20:57.088515
-[ RECORD 3 ]---+-------------------------------------
uuid | 618e67a7-336f-4578-97b8-4740f7c93aa3
display_name | Patrick Walukagga
handle | patrickcmd
email | [email protected]
cognito_user_id | MOCK
created_at | 2023-03-23 22:20:57.088515
-[ RECORD 4 ]---+-------------------------------------
uuid | 1b2a36b5-3b56-4c15-91c7-65df403c62e4
display_name | Telnet Cmd
handle | telnetcmd
email | [email protected]
cognito_user_id | MOCK
created_at | 2023-03-23 22:20:57.088515
Now that some mock users have been added to our database, we can use them to seed conversation messages into the cruddur-messages table in dynamoDB.
./bin/ddb/seedTo confirm, I perform a table scan on cruddur-messages
./bin/ddb/scanSample Output
{'user_uuid': '1b2a36b5-3b56-4c15-91c7-65df403c62e4', 'message_group_uuid': '5ae290ed-55d1-47a0-bc6d-fe2bc2700399', 'user_handle': 'telnetcmd', 'sk': '2023-03-23T22:39:12.160498+00:00', 'pk': 'GRP#618e67a7-336f-4578-97b8-4740f7c93aa3', 'message': 'this is a filler message', 'user_display_name': 'Telnet Cmd'}
{'user_uuid': '618e67a7-336f-4578-97b8-4740f7c93aa3', 'message_group_uuid': '5ae290ed-55d1-47a0-bc6d-fe2bc2700399', 'user_handle': 'patrickcmd', 'sk': '2023-03-23T22:39:12.160498+00:00', 'pk': 'GRP#1b2a36b5-3b56-4c15-91c7-65df403c62e4', 'message': 'this is a filler message', 'user_display_name': 'Patrick Walukagga'}
{'user_uuid': '618e67a7-336f-4578-97b8-4740f7c93aa3', 'user_handle': 'patrickcmd', 'sk': '2023-03-23T22:39:12.160498+00:00', 'pk': 'MSG#5ae290ed-55d1-47a0-bc6d-fe2bc2700399', 'message_uuid': 'a0c23064-216e-4f47-bf56-a33c586caa56', 'message': "Have you ever watched Babylon 5? It's one of my favorite TV shows!", 'user_display_name': 'Patrick Walukagga'}
{'user_uuid': '1b2a36b5-3b56-4c15-91c7-65df403c62e4', 'user_handle': 'telnetcmd', 'sk': '2023-03-23T22:40:12.160498+00:00', 'pk': 'MSG#5ae290ed-55d1-47a0-bc6d-fe2bc2700399', 'message_uuid': 'bb2765be-0ecf-4878-a83b-1b217a0bd9db', 'message': "Yes, I have! I love it too. What's your favorite season?", 'user_display_name': 'Telnet Cmd'}
{'user_uuid': '618e67a7-336f-4578-97b8-4740f7c93aa3', 'user_handle': 'patrickcmd', 'sk': '2023-03-23T22:41:12.160498+00:00', 'pk': 'MSG#5ae290ed-55d1-47a0-bc6d-fe2bc2700399', 'message_uuid': 'f75c2756-4ec7-4657-9e50-7e680cbe8df6', 'message': 'I think my favorite season has to be season 3. So many great episodes, like "Severed Dreams" and "War Without End."', 'user_display_name': 'Patrick Walukagga'}
{'user_uuid': '1b2a36b5-3b56-4c15-91c7-65df403c62e4', 'user_handle': 'telnetcmd', 'sk': '2023-03-23T22:42:12.160498+00:00', 'pk': 'MSG#5ae290ed-55d1-47a0-bc6d-fe2bc2700399', 'message_uuid': 'cb4a828d-4093-464e-af82-6eb198a2e8f1', 'message': 'Yeah, season 3 was amazing! I also loved season 4, especially with the Shadow War heating up and the introduction of the White Star.', 'user_display_name': 'Telnet Cmd'}
...
A sample of the access patterns can be referenced here
Showing a single conversion
./bin/ddb/get-conversationSample Output
{
"ConsumedCapacity": {
"CapacityUnits": 1.5,
"TableName": "cruddur-messages"
},
"Count": 20,
"Items": [
{
"message": {
"S": "Definitely. I think his character is a great example of the show's ability to balance humor and heart, and to create memorable and beloved characters that fans will cherish for years to come."
},
"message_uuid": {
"S": "69d85141-24cf-4dfe-9980-827c193be12f"
},
"pk": {
"S": "MSG#5ae290ed-55d1-47a0-bc6d-fe2bc2700399"
},
"sk": {
"S": "2023-03-24T00:22:12.160498+00:00"
},
"user_display_name": {
"S": "Telnet Cmd"
},
"user_handle": {
"S": "telnetcmd"
},
"user_uuid": {
"S": "1b2a36b5-3b56-4c15-91c7-65df403c62e4"
}
},
{
"message": {
"S": "And Zathras was just one example of that. He was a small but important part of the show's legacy, and he's still remembered fondly by fans today."
},
"message_uuid": {
"S": "99c64a27-566a-47f3-8e26-6c11c291232a"
},
"pk": {
"S": "MSG#5ae290ed-55d1-47a0-bc6d-fe2bc2700399"
},
"sk": {
"S": "2023-03-24T00:21:12.160498+00:00"
},
"user_display_name": {
"S": "Patrick Walukagga"
},
"user_handle": {
"S": "patrickcmd"
},
"user_uuid": {
"S": "618e67a7-336f-4578-97b8-4740f7c93aa3"
}
},
...
"ScannedCount": 20
}
{
"CapacityUnits": 1.5,
"TableName": "cruddur-messages"
}
patrickcmd 2023-03-24 12:03 AM One thing that really stands out about B...
telnetcmd 2023-03-24 12:04 AM I thought the special effects in Babylon...
patrickcmd 2023-03-24 12:05 AM Yes, I was really blown away by the leve...
telnetcmd 2023-03-24 12:06 AM And I also appreciated the way the show ...
patrickcmd 2023-03-24 12:07 AM Absolutely. The show had a great balance...
telnetcmd 2023-03-24 12:08 AM And it's also worth noting the way the s...
patrickcmd 2023-03-24 12:09 AM Yes, I agree. And it's impressive how th...
telnetcmd 2023-03-24 12:10 AM Definitely. And it's one of the reasons ...
patrickcmd 2023-03-24 12:11 AM Agreed. And it's also worth noting the w...
telnetcmd 2023-03-24 12:12 AM Yes, it definitely had a big impact on t...
patrickcmd 2023-03-24 12:13 AM Another character I wanted to discuss is...
telnetcmd 2023-03-24 12:14 AM Zathras was a really unique and memorabl...
patrickcmd 2023-03-24 12:15 AM Yes, I thought he was a great addition t...
List of conversations
./bin/ddb/patterns/list-conversationsSample Output
connection_url: postgresql://postgres:password@localhost:5432/cruddur
SQL STATEMENT-[value]------
SELECT
users.uuid
FROM users
WHERE
users.handle =%(handle)s
{'handle': 'patrickcmd'}
my-uuid: 618e67a7-336f-4578-97b8-4740f7c93aa3
{
"ConsumedCapacity": {
"CapacityUnits": 0.5,
"TableName": "cruddur-messages"
},
"Count": 1,
"Items": [
{
"message": {
"S": "this is a filler message"
},
"message_group_uuid": {
"S": "5ae290ed-55d1-47a0-bc6d-fe2bc2700399"
},
"pk": {
"S": "GRP#618e67a7-336f-4578-97b8-4740f7c93aa3"
},
"sk": {
"S": "2023-03-23T22:39:12.160498+00:00"
},
"user_display_name": {
"S": "Telnet Cmd"
},
"user_handle": {
"S": "telnetcmd"
},
"user_uuid": {
"S": "1b2a36b5-3b56-4c15-91c7-65df403c62e4"
}
}
],
"ResponseMetadata": {
"HTTPHeaders": {
"content-length": "449",
"content-type": "application/x-amz-json-1.0",
"date": "Fri, 24 Mar 2023 00:26:53 GMT",
"server": "Jetty(9.4.48.v20220622)",
"x-amz-crc32": "287447233",
"x-amzn-requestid": "c954f085-92fc-4070-99cc-2be3a6aa0c17"
},
"HTTPStatusCode": 200,
"RequestId": "c954f085-92fc-4070-99cc-2be3a6aa0c17",
"RetryAttempts": 0
},
"ScannedCount": 1
}
- create a VPC endpoint for dynamoDB service on your VPC
- create a Python lambda function in your vpc
- enable streams on the table with 'new image' attributes included
- add your function as a trigger on the stream
- grant the lambda IAM role permission to read the DynamoDB stream events
AWSLambdaInvocation-DynamoDB
- grant the lambda IAM role permission to update table items
See full scripts here
cd ${THEIA_WORKSPACE_ROOT}/backend-flask
./bin/ddb/schema-load prod
import json
import boto3
from boto3.dynamodb.conditions import Key, Attr
dynamodb = boto3.resource(
'dynamodb',
region_name='us-east-1',
endpoint_url="http://dynamodb.us-east-1.amazonaws.com"
)
def lambda_handler(event, context):
pk = event['Records'][0]['dynamodb']['Keys']['pk']['S']
sk = event['Records'][0]['dynamodb']['Keys']['sk']['S']
if pk.startswith('MSG#'):
group_uuid = pk.replace("MSG#","")
message = event['Records'][0]['dynamodb']['NewImage']['message']['S']
print("GRUP ===>",group_uuid,message)
table_name = 'cruddur-messages'
index_name = 'message-group-sk-index'
table = dynamodb.Table(table_name)
data = table.query(
IndexName=index_name,
KeyConditionExpression=Key('message_group_uuid').eq(group_uuid)
)
print("RESP ===>",data['Items'])
# recreate the message group rows with new SK value
for i in data['Items']:
delete_item = table.delete_item(Key={'pk': i['pk'], 'sk': i['sk']})
print("DELETE ===>",delete_item)
response = table.put_item(
Item={
'pk': i['pk'],
'sk': sk,
'message_group_uuid':i['message_group_uuid'],
'message':message,
'user_display_name': i['user_display_name'],
'user_handle': i['user_handle'],
'user_uuid': i['user_uuid']
}
)
print("CREATE ===>",response)- Enable streams on the table with 'new image' attributes included
- Also remember to attach
AmazonDynamoDBFullAccesspolicy in case of any errors.
- Found that adding
DynamoDB Full Accesspolicy doesn't resolve the permission errors. So went ahead and created/attached an inline plocy with the permissions below
{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "VisualEditor0",
"Effect": "Allow",
"Action": [
"dynamodb:PutItem",
"dynamodb:DeleteItem",
"dynamodb:Query"
],
"Resource": [
"arn:aws:dynamodb:us-east-1:476313879638:table/cruddur-messages",
"arn:aws:dynamodb:us-east-1:476313879638:table/cruddur-messages/index/message-group-sk-index"
]
}
]
}
- What format is postgres database is being store for datetimes?
- How does psycopg3 do to datetimes when inputing or outputing?
- What format is DynamoDB table is being stored for datetimes?
- Does the system machine timezone matter?
- What does flask set as the timezone?
- Do we need to translate the datetime for python before serving?
- What does python do the datetimes converted to string for the api calls?
- What format do we need to serve the datetime to the endpoint?
- What does luxon library expect for datetime format with timezones?
- timestamp format:
2023-04-05 12:30:45 - timestampz format:
2023-04-05 12:30:45+00
The following format currently stored in postgres:
2023-04-15 13:15:19.922515O
'2023-04-15 13:15:19.922515O' represents a timestamp of April 15th, 2023 at 1:15:19.922515 PM with the microseconds (.922515) and is stored as UTC time since there is no timezone included.
What postgres says about timezones:
we recommend using date/time types that contain both date and time when using time zones. We do not recommend using the type time with time zone (though it is supported by PostgreSQL for legacy applications and for compliance with the SQL standard). PostgreSQL assumes your local time zone for any type containing only date or time.
All timezone-aware dates and times are stored internally in UTC. They are converted to local time in the zone specified by the TimeZone configuration parameter before being displayed to the client.
We can see what timezone postgres is using by running:
show timezone;
This will output:
-[ RECORD 1 ]-
TimeZone | UTC
Python datetime objects are converted to PostgreSQL timestamp (if they don’t have a tzinfo set) or timestamptz (if they do).
PostgreSQL timestamptz values are returned with a timezone set to the connection TimeZone setting, which is available as a Python ZoneInfo object in the Connection.info.timezone attribute:
conn.info.timezone
# zoneinfo.ZoneInfo(key='Europe/London')
conn.execute("select '2048-07-08 12:00'::timestamptz").fetchone()[0]
# datetime.datetime(2048, 7, 8, 12, 0, tzinfo=zoneinfo.ZoneInfo(key='Europe/London'))We don't plan to use timestamptz based on postgres recommendation and we are not so we probably don't have to worry about checking the timezone for hte connection. The underlying connection might matter.
https://docs.python.org/3/library/datetime.html
How to find the timezone in chrome inspector:
Intl.DateTimeFormat().resolvedOptions().timeZone- Andrew’s OmenKing Repository – Week 5 Notes
- Jason Paul - Week 5: Unofficial Homework Guide
- Alex DeBrie – The DynamoDB Book
- AWS DynamoDB Docs
- AWS DynamoDB Guides - Youtube playlist
- Single-table vs. multi-table design in Amazon DynamoDB
- DigitalCloud - Amazon DynamoDB
- The Ultimate DynamoDB Cheatsheet
- Commander Docs
- DynamoDB examples using SDK for Python (Boto3)
- Boto3 DynamoDB – Complete Tutorial
- W3Schools - AWS Cloud Tutorial
- Working with Amazon DynamoDB using Python & Boto3
- ExamPro - AWS Developer Associate 2020 Youtube Playlist
I have not been writing blogs, though I have found Josh Hargett's medium blogs as a good motivator to start thinking also putting out my work out their as simple contribution to the tech community.
Youtube resources:
- AWS re:Invent 2021 - Data modeling with Amazon DynamoDB [REPEAT]
- 7 Common DynamoDB Patterns for Modeling and Building an App with Alex De Brie
- Designing a DynamoDB Table in 4 Steps: From Entities to Access Patterns
See transcript here
-
Product: it represents a product in the application
- Id
- Name
- Description
- Price
- Category
- Images
- Amount sold
- Writer Id (the user that created it)
-
Users: A user is a registered user in the application
- Email(used as an id)
- First name
- Last name
-
Order: A user can purcahse a set of items on a date for a total amount.
- Id
- User
- Purchase date
- Status - finalized / active (this is for the shopping cart)
- Total amount
- Total items
- [Item list]
-
OrderItems: Items the user purchased or want purchase
- OrderId
- ProductId
- UserId
- Quantity
- Title
- Image
- Total amount
