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.
|
1 2 3 4 5 6 |
## Create a dynamodb table aws dynamodb create-table \ --table-name mytable \ --attribute-definitions AttributeName=id,AttributeType=S \ --key-schema AttributeName=id,KeyType=HASH \ --provisioned-throughput ReadCapacityUnits=5,WriteCapacityUnits=5 |
PartiQL DynamoDB Insert Statement Example:
Insert a single row to a DynamoDB table using PartiQL.
|
1 2 3 4 |
## Insert a single item aws dynamodb execute-statement --statement "INSERT INTO mytable \ VALUE \ {'id':'A0001','Name':'Debjeet', 'Age':35, 'Email':'debjeet@gmail.com'}" |
Insert a single row with different data types to a DynamoDB table using PartiQL
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
## Insert a single item (different data type example) aws dynamodb execute-statement --statement \ "INSERT INTO mytable value {'id':'A0002', 'Name':'Matt', 'Age':39, 'Email':'matt@gmail.com', 'Address' : {'Country': 'India', 'State': 'WB', 'Pin': 700056}, 'Skills': ['AWS','Azure','DevOps'], 'Coordinate':<<88.22,22.88>>, 'NickNames':<<'Steve','Harry'>>, 'Available': false, 'Sex': null }" |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
## Insert multiple items (bulk insert) cat << EOF > data.json [ { "Statement": "INSERT INTO mytable value{'id':'A0003','Name':'Chandrima', 'Age':33, 'Email':'chandrima@gmail.com'}" }, { "Statement": "INSERT INTO mytable value{'id':'A0004','Name':'Avik', 'Age':39, 'Email':'avik@gmail.com'}" }, { "Statement": "INSERT INTO mytable value{'id':'A0005','Name':'Debarun', 'Age':51, 'Email':'debarun@gmail.com'}" } ] EOF aws dynamodb batch-execute-statement \ --statements file://data.json |
PartiQL DynamoDB Select Statement Example:
Select all items from a DynamoDB table using PartiQL
|
1 2 3 |
## Select statement aws dynamodb execute-statement \ --statement "SELECT * FROM mytable" ## all items |
Select specific items from a DynamoDB table using PartiQL
|
1 2 3 |
## Select statement aws dynamodb execute-statement \ --statement "SELECT id,Name,Email FROM mytable" ## specific items |
Conditionally select (WHERE) items from a DynamoDB table using PartiQL
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
## WHERE aws dynamodb execute-statement \ --statement "SELECT * FROM mytable WHERE id = 'A0001'" ## AND aws dynamodb execute-statement \ --statement "SELECT * FROM mytable WHERE Name = 'Matt' AND Age = 39" ## IN aws dynamodb execute-statement \ --statement "SELECT * FROM mytable WHERE id IN ['A0001','A0003']" ## OR aws dynamodb execute-statement \ --statement "SELECT * FROM mytable WHERE id = 'A0001' OR id = 'A0005'" ## IS aws dynamodb execute-statement \ --statement "SELECT id,Name,Sex FROM mytable WHERE Sex IS NULL" ## NOT aws dynamodb execute-statement \ --statement "SELECT * FROM mytable WHERE id NOT IN ['A0001','A0003']" ## > >= < <= aws dynamodb execute-statement \ --statement "SELECT Name,Age FROM mytable WHERE Age < 40 AND Age >= 30" ## BETWEEN aws dynamodb execute-statement \ --statement "SELECT Name,Age FROM mytable WHERE Age BETWEEN 30 AND 40" ## LIKE aws dynamodb execute-statement \ --statement "SELECT Email FROM mytable WHERE Age < 40 AND Contains(\"Email\", 'gmail.com')" aws dynamodb execute-statement \ --statement "SELECT * FROM mytable WHERE begins_with(\"Name\", 'Deb')" |
PartiQL DynamoDB Update Statement Example
Update multiple data types in DynamoDB based on Conditions
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
## Update aws dynamodb execute-statement --statement \ "UPDATE mytable \ SET Available = true WHERE id = 'A0002'" ## Update a list (add items) aws dynamodb execute-statement --statement \ "UPDATE mytable \ SET Skills = LIST_APPEND(Skills, ['Git', 'Ansible']) WHERE id = 'A0002'" ## Update a list (delete items) aws dynamodb execute-statement --statement \ "UPDATE mytable \ REMOVE Skills[4] WHERE id = 'A0002'" ## Update a set (add items) aws dynamodb execute-statement --statement \ "UPDATE mytable \ SET NickNames = SET_ADD(NickNames, <<'Toni'>>) WHERE id = 'A0002'" ## Update a set (remove items) aws dynamodb execute-statement --statement \ "UPDATE mytable \ SET NickNames = SET_DELETE(NickNames, <<'Steve'>>) WHERE id = 'A0002'" ## Update a map (add items) aws dynamodb execute-statement --statement \ "UPDATE mytable \ SET Address.City = 'Kolkata' WHERE id = 'A0002'" ## Update a map (remove items) aws dynamodb execute-statement --statement \ "UPDATE mytable \ REMOVE Address.Pin WHERE id = 'A0002'" |
PartiQL DynamoDB Delete Statement Example
|
1 2 3 4 5 6 7 |
## Delete an item aws dynamodb execute-statement --statement \ "DELETE FROM mytable WHERE id = 'A0002'" ## Delete the dynamodb table aws dynamodb delete-table \ --table-name mytable |
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
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?
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?