Using SQL like Language (PartiQL) to Manipulate DynamoDB Data
Amazon DynamoDB is a key-value and document database that delivers high performance at scale. Amazon DynamoDB supports PartiQL, a SQL-compatible query language, to select, insert, update, and delete data in Amazon DynamoDB. PartiQL operations provide the same availability, latency, and performance as the other DynamoDB data plane operations.
In this exercise, you learn to use PartiQL with DynamoDB table.
The AWS Resource consumption for the exercise falls under AWS Free Tier.
Step1: Pre-Requisite
You need to have an AWS account with administrative access to complete the exercise. If you don’t have an AWS account, kindly use the link to create free trial account for AWS.
Step2: Create DynamoDB Table
You create a DynamoDB table dojotable which is used for different types of queries to manipulate data.
-
Login to AWS Management Console and select Ireland as the region.
-
The exercise is using the new UI console for DynamoDB. Goto DynamoDB console. Select Tables menu in the left and then click on the Create table button.
-
On the next screen, type in dojotable as the table name. Type in id as the partition key with data type selected as String. Keep rest of the configuration to the default and click on the Create table button.
-
The table is created in no time. Next, you create Cloud9 environment which is used to write python code.
Step3: Create Cloud9 Environment
In this step, you configure AWS Cloud9 environment with AWS SDK for Python Boto3 in order to program with Amazon DynamoDB.
-
Goto the AWS Cloud9 console and click on the Create environment button.
-
On the next screen, enter dojoenvironment as the name and click on the Next step button.
-
On the next screen, select Environment type as Create a new EC2 instance for environment (direct access). Select Instance type as t2.micro (1 GiB RAM + 1 vCPU). Select Ubuntu Server 18.04 LTS for the Platform. The development environment will have Ubuntu as the operating system. Keep rest of the fields with the default values and click on the Next step button.
-
On the next screen, click on the Create environment button.
-
It will take couple of minutes to create the environment. Wait for the environment to be ready. Once it is ready, you can see bash console window in the bottom part of the screen. It provides console based access to the development machine.
-
You will now configure the environment for the Python Boto3 SDK. Run the sudo apt-get update command in the console to update the environment.
-
Python3 is already installed in the environment. You can check it by running the command python3 –version in the console.
-
AWS SDK for Python Boto3 is not installed in the environment. Run the command sudo apt install python-boto3 to install Boto3 package in the environment. When it asks for the confirmation; you type in Y and enter.
-
It is always a good practice to upgrade botocore and boto3. Run the command pip install botocore –upgrade to upgrade botocore.
-
Next run the command pip install boto3 –upgrade to upgrade boto3.
-
The Cloud9 environment is ready for the Amazon DynamoDB programming with AWS SDK Boto3.
Step4: Use PartiQL with Python
In this task, you use SQL like queries (PartiQL) with Python to manipulate data in the DynamoDB table.
-
In the AWS Cloud9 console, in dojoenvironment, create a file insert.py and copy-paste the following code.
import boto3 client = boto3.client('dynamodb') stmt = "INSERT INTO dojotable value {'id' : '1', 'firstname' : 'John', 'lastname' : 'Smith'}" response = client.execute_statement(Statement= stmt) print(response)`
-
The code is simple. You create dynamodb client and then call execute_statement method passing INSERT SQL statement. The SQL statement is inserting three fields id, firstname and lastname for the record. The code then prints the response.
-
Run command python insert.py to run the code. It will insert the record in the DynamoDB table. You can navigate to DynamoDB table dojotable to verify it.
-
In Cloud9 dojoenvironment, create another file update.py and copy-paste the following code.
import boto3 client = boto3.client('dynamodb') stmt = "UPDATE dojotable SET firstname='Robert' where id='1'" response = client.execute_statement(Statement= stmt) print(response)`
-
In the code above, you call execute_statement method passing UPDATE SQL statement. The SQL statement is updating the firstname for id = 1 record. The code then prints the response.
-
Run command python update.py to run the code. It will update the record in the DynamoDB table. You can navigate to DynamoDB table dojotable to verify it.
-
In Cloud9 dojoenvironment, create another file select.py and copy-paste the following code.
import boto3 client = boto3.client('dynamodb') stmt = "SELECT * FROM dojotable where id = '1'" response = client.execute_statement(Statement= stmt) print(response["Items"])`
-
In the code above, you call execute_statement method passing SELECT SQL statement for id = 1 record. The code then prints the records from the DynamoDB table.
-
Run command python select.py to run the code and see the records printed.
-
In Cloud9 dojoenvironment, create another file delete.py and copy-paste the following code.
import boto3 client = boto3.client('dynamodb') stmt = "DELETE FROM dojotable WHERE id = '1'" response = client.execute_statement(Statement= stmt) print(response) -
In the code above, you call execute_statement method passing Delete SQL statement. The SQL statement is deleting the record where id = 1. The code then prints the response.
-
Run command python delete.py to run the code. It will delete the record in the DynamoDB table. You can navigate to DynamoDB table dojotable to verify it.
-
This finishes the exercise. Please follow the next step to clean-up the resources so that you don’t incur any cost post the exercise.
Step5: Clean up
Delete dojoenvironment Cloud9 environment.
Delete dojotable DynamoDB Table.
Thanks and hope you enjoyed the exercise.