top of page

Tutorial blog to learn a SQL-compatible query language (PartiQL) with Amazon DynamoDB.

Learning Objectives:

1. To learn a SQL-compatible query language (PartiQL) with Amazon DynamoDB.

2. To learn PartiQL Statements to SELECT, INSERT, UPDATE and DELETE in DynamoDB.

Step 1: Follow Step 1, 2, 3 and 4 from this blog to create Cloud9 environment.

Step 2: Copy the below code in your “create_table.py” file.

CODE

import boto3

from decimal import Decimal

# Initialize the DynamoDB resource and client

dynamodb = boto3.resource('dynamodb')

client = boto3.client('dynamodb')

# Define the table name

table_name = 'Movies'

# Create the DynamoDB table

try:

table = dynamodb.create_table(

TableName=table_name,

KeySchema=[

{

'AttributeName': 'year',

'KeyType': 'HASH' # Partition key

},

{

'AttributeName': 'title',

'KeyType': 'RANGE' # Sort key

}

],

AttributeDefinitions=[

{

'AttributeName': 'year',

'AttributeType': 'N' # Numeric

},

{

'AttributeName': 'title',

'AttributeType': 'S' # String

}

],

ProvisionedThroughput={

'ReadCapacityUnits': 5,

'WriteCapacityUnits': 5

}

)

# Wait for the table to be created

table.meta.client.get_waiter('table_exists').wait(TableName=table_name)

print(f"Table '{table_name}' created successfully.")

except client.exceptions.ResourceInUseException:

print(f"Table '{table_name}' already exists.")

# Add items to the table

movies_data = [

{

'year': 1994,

'title': 'The Shawshank Redemption',

'info': {

'plot': 'Two imprisoned men bond over a number of years, finding solace and eventual redemption through acts of common decency.',

'rating': Decimal('9.3') # Use Decimal for rating

}

},

{

'year': 1972,

'title': 'The Godfather',

'info': {

'plot': 'The aging patriarch of an organized crime dynasty transfers control of his clandestine empire to his reluctant son.',

'rating': Decimal('9.2') # Use Decimal for rating

}

},

{

'year': 2008,

'title': 'The Dark Knight',

'info': {

'plot': 'When the menace known as The Joker emerges from his mysterious past, he wreaks havoc and chaos on the people of Gotham.',

'rating': Decimal('9.0') # Use Decimal for rating

}

}

]

table = dynamodb.Table(table_name)

for movie in movies_data:

table.put_item(Item=movie)

print("Items added to the table.")

After copying the code save the file using Ctrl+S.

In the terminal execute the following command.

python create_table.py

You can go to DynamoDB service and check your Table is created and in Explore Items you can see the Items are added.

Step 3: In the left panel click on PartiQL editor, in the query box copy the following query and paste it and then click on Run.

SELECT * FROM Movies WHERE year = 1994;

You can after running the query, you get the item returned for the year given.

Step 4: Click on + icon.

In the Query 2, write the following query to insert an item into the table.

INSERT INTO "Movies" value {'year' : 2023,'title' : 'Movie 1','info':{'plot':'Thriller movie must watch','rating':4.5}};

Click on Run.

In Explore Items, you can see the item is added in the table.

Step 5: Similarly now copy the below query in Query Box.

UPDATE Movies

SET info = 'ABC', rating = 6.7

WHERE year = 1994 AND title = 'The Shawshank Redemption';

Then Click on Run.

You can see the updated item in the Explore Items section.

Step 6: Go back to PartiQL editor and copy the below command in Query box

DELETE FROM "Movies" WHERE "year" = 1994 AND "title" = 'The Shawshank Redemption';

Then click on Run.

You can the item is deleted from the table.


Note: Delete the Table from DynamoDB and also delete the Cloud9 environment, if no longer in use.



dynamodb_partiql
.pdf
Download PDF • 369KB

Was this document helpful? How can we make this document better. Please provide your insights. You can download PDF version for reference.


For your aws certification needs or for aws learning contact us.

Recent Posts

See All

10 Comments


Easy to understand sir

Like

Atchaya B
Atchaya B
Apr 08

Easy blog

Like

Gokul M
Gokul M
Apr 08

It is very easy to learn the partiql and dynamoDB

Like

Very useful sir

Like

Easy to understand sir

Like
bottom of page