DynamoDB PartiQL Setup and Examples
The primary advantage of using PartiQL with DynamoDB is that it enables you to use a single query language to access data in multiple data stores. This will make it easier to build applications that combine data from different sources and simplify the process of migrating data between data stores.
PartiQL in DynamoDB makes learning how to use the database more manageable, mainly if you are familiar with other SQL databases. You will also find making migrations a lot more convenient. Besides, it gives the option to choose between NoSQL or SQL-based languages.
How to Set Up DynamoDB PartiQL
You can easily set up DynamoDB PartiQL by performing the following steps:
Step 1: Install the PartiQL CLI
You can begin by installing PartiQL using the command below.
Step 2: Create an Amazon Web Services Account and Set Up Your Credentials
Your next step should be to create an AWS account. You can log in to your account if you already have one. The following steps should help you set up the required credentials:
- Open the AWS Management Console using your preferred browser.
- Click on the “Services” menu. Once there, select “IAM” under the “Security, Identity, & Compliance” category. You need an IAM account for this procedure.
- Click on the “Users” menu item, and then click the “Add user” tab to create a new IAM user.
- Provide a name for your user, and select the “Programmatic access” checkbox, as this will give you the correct privileges.
- Click the “Next: Permissions” button.
- Click the “Create group” tab and continue.
- Give your group a name, and then select the “AdministratorAccess” policy.
- Click the “Create group” button and proceed to select the “Next: Review” tab.
- Review the details of your new user and group to ensure that you have everything correctly, and then click the “Create user” button.
- Note the “Access key ID” and “Secret access key” displayed on the next page, as you will need them to configure your AWS CLI and PartiQL CLI.
Step 3: Configure the AWS CLI
Use the below command to configure AWS CLI:
Step 4: Create a DynamoDB Table
To create a DynamoDB table using the AWS CLI, you can use the aws dynamodb create-table command.
Here is an example of how to create a DynamoDB table using the AWS CLI:
--table-name MyTableName \
--attribute-definitions AttributeName=Id,AttributeType=S \
--key-schema AttributeName=Id,KeyType=HASH \
--provisioned-throughput ReadCapacityUnits=15,WriteCapacityUnits=15
This will create a DynamoDB table with the name “MyTableName”, a primary key attribute called “Id” of type string, and provisioned throughput of 15 read and 15 write capacity units.
With that done, you can use PartiQL on your tables.
DynamoDB PartiQL Examples
The following are some examples of how PartiQL is useable in DynamoDB:
INSERT INFO
Use the INSERT INTO statement to insert an item into a DynamoDB table using PartiQL.
The INSERT INFO statement syntax is as shown.
Consider the example below.
This statement will insert a new item into the “MyTableName” table with the primary key attribute “Id” set to the value ‘1’, and two additional attributes “Name” and “Age” set to the values ‘Darry’ and 34, respectively.
You can specify additional optional attributes into the table by including them in the INSERT INTO statement. For example:
QUERY DATA
You can use the following syntax to query data:
FROM table[.index-name]
WHERE condition
ORDER BY key [DESC|ASC] , ...];
Consider the command example shown below;
UPDATE DATA
Below is the update data syntax;
WHERE condition [RETURNING returnvalues];
An example is shown below.
SET isVerified=TRUE
SET metadata={'lastLoggedAt':xxx1234567890yyy}
WHERE email='kencity@doe.com' AND id='12345';
DELETE DATA
The DELETE DATA syntax is as follows:
An example command can be as follows:
Conclusion
PartiQL is a SQL-compatible query language that simplifies querying data in Amazon DynamoDB. It allows you to use familiar SQL syntax to filter, project, and manipulate data in DynamoDB tables. PartiQL supports SELECT, INSERT, UPDATE, DELETE, and CREATE VIEW statements and various functions and operators.
Source: linuxhint.com