You are currently viewing How to use SQL-like query in AWS DynamoDB using PartiQL?

How to use SQL-like query in AWS DynamoDB using PartiQL?

How to use SQL like query in AWS DynamoDB using PartiQL?

Hello Everyone

Welcome to CloudAffaire and this is Debjeet.

You can use PatriQL, a SQL compatible query language to select, insert, update or delete an AWS DynamoDB table. Using PartiQL, you can easily interact with DynamoDB tables and run ad hoc queries using the AWS Management Console, NoSQL Workbench, AWS Command Line Interface, and DynamoDB APIs for PartiQL.

What is PartiQL?

PartiQL provides SQL-compatible query access across multiple data stores containing structured data, semistructured data, and nested data. It is widely used within Amazon and is now available as part of many AWS services, including DynamoDB.

Let’s dig down a bit with some PartiQL examples for DynamoDB. We will use AWS CLI for this demo.

How to use SQL like query in AWS DynamoDB using PartiQL?

Prerequisites:

AWS CLI installed and configured with proper access.

You can use below link to install and configure AWS CLI.

https://cloudaffaire.com/how-to-install-aws-cli/

https://cloudaffaire.com/how-to-configure-aws-cli/

Let us first create two DynamoDB tables. The second one will be used later to explain a table JOIN in DynamoDB.

PartiQL DynamoDB Insert Statement Example:

Insert a single row to a DynamoDB table using PartiQL.

Insert a single row with different data types to a DynamoDB table using PartiQL

In the above example id, Name and Email are of data type string (S), Age, Pin are of data type number (N), NickNames is of data type string set (SS), Skills is of data type list (L), Address is of data type map (M), Available is of datatype boolean (BOOL), Sex is of data type null (NULL) and Coordinate is of data type number set (NS).

Note: PariQL does not support Binary data type.

Bulk insert into DynaboDB table using PariQL

PartiQL DynamoDB Select Statement Example:

Select all items from a DynamoDB table using PartiQL

Select specific items from a DynamoDB table using PartiQL

Conditionally select (WHERE) items from a DynamoDB table using PartiQL

PartiQL DynamoDB Update Statement Example

Update multiple data types in DynamoDB based on Conditions

PartiQL DynamoDB Delete Statement Example

Hope you have enjoyed this article, to get more details on DynamoDB table, please follow below link.

https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.html

https://docs.aws.amazon.com/dynamodb/index.html

This Post Has 2 Comments

  1. Avatar
    Edu

    How can I do a PartiQL INSERT INTO SELECT Statement for DynamoDB?. So, Instead of specifying a list of values, copy data from a table to another table. I was trying something like this.
    <>
    but it doesn’t works. Any suggestions?

    1. Avatar
      Edu

      I was trying something like this.

      INSERT INTO “mytable-copy” (id, Email, Age, Name) SELECT id, Email, Age, Name FROM “mytable”

      but it doesn’t works. Any suggestions?

Leave a Reply