Query items in DynamoDB with PartiQL Statements

​

Overview

​

Lab Details

1. This lab walks you through Amazon DynamoDB features. We will create a table in Amazon DynamoDB and query, modify insert and delete data to using PartiQL.
2. Duration: 1 hour
3. AWS Region: US East (N. Virginia) us-east-1.

​

Introduction

What is AWS DynamoDB?

• DynamoDB is a fast and flexible NoSQL database designed for applications that need consistent, single-digit millisecond latency at any scale. It is a fully managed database, and it supports both document and key value data models.
• It has a very flexible data model. This means that you don’t need to define your database schema upfront. It also has reliable performance.
• DynamoDB is a good fit for mobile gaming, ad-tech, IoT and many other applications.

What is DynamoDB PartiQL?

• DynamoDB PartiQL is a query language designed for Amazon DynamoDB, a fully managed NoSQL database service. PartiQL provides a SQL-compatible language for querying and interacting with DynamoDB tables.
• PartiQL enables expressive and flexible queries, allowing users to perform complex queries with ease.
• PartiQL supports DynamoDB data types, such as strings, numbers, and binary data, making it well-integrated with the underlying DynamoDB storage.
• PartiQL can be used with the AWS SDKs (Software Development Kits), providing seamless integration with various programming languages and platforms.

PartiQL Statements:

• Inserting Data: You can use PartiQL to add new items to a DynamoDB table. It Provide values for the attributes you want to include in the new item.
• Querying Data: PartiQL allows you to retrieve data from DynamoDB tables using SQL-like queries. In this you will specify conditions to filter the data you want to retrieve.
• Updating Data: PartiQL supports updating existing data in DynamoDB. In this you will specify the attribute you want to update and the new value, along with conditions for the update.
• Deleting Data: Use PartiQL to delete items from DynamoDB tables based on specified conditions. It Removes specific items that match the criteria.

​

​

Architecture Diagram

​

Tasks Details

1. Sign in to AWS Management Console.
2. Create a DynamoDB table.
3. Insert data into DynamoDB table.
4. Modify data in DynamoDB table
5. Delete data in DynamoDb table.

​

Launching Lab Environment

1. To launch the lab environment, Click on the Start Lab button.
2. Please wait until the cloud environment is provisioned. It will take less than a minute to provision.
3. Once the Lab is started, you will be provided with IAM username, Password, Access Key, and Secret Access Key.

​

Lab guide

​

Task 1: Sign in to AWS Management Console

1. Click on the Open Console button, and you will get redirected to AWS Console in a new browser tab.
2. On the AWS sign-in page,

• Leave the Account ID as default. Never edit/remove the 12-digit Account ID present in the AWS Console. Otherwise, you cannot proceed with the lab.
• Now copy your Username and Password in the Lab Console to the IAM Username and Password in AWS Console and click on the Sign-in button.

3. Once Signed In to the AWS Management Console, make the default AWS Region as US East (N. Virginia) us-east-1.

Note: If you face any issues, please go through FAQs and Troubleshooting for Labs.

​

Task 2: Create DynamoDB Table

1. Make sure you are in the US East (N. Virginia) us-east-1 Region.
2. Navigate to DynamoDB page by clicking on the Services menu at the top. DynamoDB is available under the Database section.
3. Click on Create table button

• Table Name: Enter mytable
• Primary key: Enter id and select String

• Leave all the other settings as default and then click on Create table.

4. Your table will be created within 2–3 minutes.

​

Task 3 : Inserting Data into DynamoDB Table

1. Next, we are inserting data in the table we created.
2. Click on PartiQL Editor form the left navigation menu.
3. You can see mytable listed and a Query editor

4. Now, to insert items in DynamoDB table copy paste below command in query editor.

INSERT INTO mytable value {'id': '1', 'name': 'John Doe'};

5. Now, click on Run button to run the query

6. Similarly, for testing purposes, add 4–5 items more using below commands.

INSERT INTO mytable value {'id': '2', 'name': 'Kane'};

INSERT INTO mytable value {'id': '3', 'name': 'Sarah'};

INSERT INTO mytable value {'id': '4', 'name': 'Will'};

INSERT INTO mytable value {'id': '5', 'name': 'Kyle'};

7. Use below command to verify the inserted data.

SELECT * FROM "mytable"

​

Optional step:

1. You can also use CLI to access PartiQL for DynamoDB.
2. Copy paste the below command in cloudshell to view items in dynamodb using cli method.

aws dynamodb execute-statement --statement "select * from \\"mytable\\""

​

Task 4 : Modify data in the Table

1. Copy paste below command in editor to modify item name of data with id = 4.

UPDATE "mytable" SET "name" = 'Rome' WHERE "id" = '4'

2. Now, run below command to verify the updated name

SELECT * FROM "mytable" WHERE "id" = '4'

3. Here you can see the modified name of id = 4.

​

Task 5 : Delete Data in DynamoDB Table

1. Now, to delete an item in DynamoDB table copy paste below command in query editor. This command will delete the item with id = 5

DELETE FROM "mytable" WHERE "id" = '5'

2. Now, click on Run button to run the query

3. Use below command to verify the deleted data.

SELECT * FROM "mytable"

4. Here you can verify, the item deleted with id = 5.

​

Do You Know?

PartiQL is a SQL-compatible query language supported by DynamoDB, enabling developers to query, insert, update, and delete data in DynamoDB tables using familiar SQL syntax, thus simplifying data manipulation tasks for those accustomed to SQL-based systems

​

Completion and Conclusion

1. You have successfully created an Amazon DynamoDB table.
2. You have inserted items into the table using PartiQL.
3. You have modified an item in the table using PartiQL.
4. You have deleted an item in the table using PartiQL.

​

End Lab

1. Sign out from the AWS Management Console.
2. Click on End lab button from IP Lab Portal Labs console and wait till the process gets complete

​

What gets checked

• Create Amazon DynamoDB Table — Check whether a DynamoDB Table is created or not.
• Create Items in DynamoDB Table — Check whether items in the DynamoDB table are created or not.

​

Related help

• FAQs and Troubleshooting