Run PartiQL on Dynamodb via boto3

Run PartiQL on Dynamodb via boto3

Introduction

In November of last year, DynamoDB became able to execute the SQL-compatible query language ** PartiQL **. https://aws.amazon.com/jp/about-aws/whats-new/2020/11/you-now-can-use-a-sql-compatible-query-language-to-query-insert-update-and-delete-table-data-in-amazon-dynamodb/

I'm a note when running with boto3, not cli.

environment

Run

I'm not sure how to index at the time of CREATE TABLE via boto3, so for the time being, when I executed only CREATE TABLE, I got Unsupported.

import boto3

clinet = boto3.client('dynamodb')
clinet = clinet.execute_statement(Statement='CREATE TABLE Questions')
botocore.exceptions.ClientError: An error occurred (ValidationException) when calling the ExecuteStatement operation: Unsupported operation: CreateTable

So create a table in the AWS console.

Let's insert the data for the time being.

>>> import boto3

>>> clinet = boto3.client('dynamodb')
>>> clinet.execute_statement(Statement="INSERT INTO Questions VALUE {'questionId': 'hogehoge', 'title':'fugafuga'}")
{'Items': [], 'ResponseMetadata': {'RequestId': 'STSLJF4VO9CA61MU1SKCOVLGMRVV4KQNSO5AEMVJF66Q9ASUAAJG', 'HTTPStatusCode': 200, 'HTTPHeaders': {'server': 'Server', 'date': 'Sat, 09 Jan 2021 08:13:13 GMT', 'content-type': 'application/x-amz-json-1.0', 'content-length': '12', 'connection': 'keep-alive', 'x-amzn-requestid': 'STSLJF4VO9CA61MU1SKCOVLGMRVV4KQNSO5AEMVJF66Q9ASUAAJG', 'x-amz-crc32': '2770214093'}, 'RetryAttempts': 0}}

If you check the console, you can see that the data has been created.

You can also check via boto3.

>>> clinet.execute_statement(Statement="SELECT * FROM Questions")
{'Items': [{'questionId': {'S': 'hogehoge'}, 'title': {'S': 'fugafuga'}}], 'ResponseMetadata': {'RequestId': 'I6S1NDUU93R9O4SE8T9657AH77VV4KQNSO5AEMVJF66Q9ASUAAJG', 'HTTPStatusCode': 200, 'HTTPHeaders': {'server': 'Server', 'date': 'Sat, 09 Jan 2021 08:15:12 GMT', 'content-type': 'application/x-amz-json-1.0', 'content-length': '68', 'connection': 'keep-alive', 'x-amzn-requestid': 'I6S1NDUU93R9O4SE8T9657AH77VV4KQNSO5AEMVJF66Q9ASUAAJG', 'x-amz-crc32': '2555902000'}, 'RetryAttempts': 0}}

I will UPDATE and check it.

>>> clinet.execute_statement(Statement="UPDATE Questions SET title='foofoo' WHERE questionId='hogehoge'")
{'Items': [], 'ResponseMetadata': {'RequestId': 'C5TMDKMTT9NM2RPTVVK0GBOTKFVV4KQNSO5AEMVJF66Q9ASUAAJG', 'HTTPStatusCode': 200, 'HTTPHeaders': {'server': 'Server', 'date': 'Sat, 09 Jan 2021 08:18:58 GMT', 'content-type': 'application/x-amz-json-1.0', 'content-length': '12', 'connection': 'keep-alive', 'x-amzn-requestid': 'C5TMDKMTT9NM2RPTVVK0GBOTKFVV4KQNSO5AEMVJF66Q9ASUAAJG', 'x-amz-crc32': '2770214093'}, 'RetryAttempts': 0}}
>>> clinet.execute_statement(Statement="SELECT * FROM Questions")
{'Items': [{'questionId': {'S': 'hogehoge'}, 'title': {'S': 'foofoo'}}], 'ResponseMetadata': {'RequestId': '898QE0R431PLKG3QJRGH26EUN7VV4KQNSO5AEMVJF66Q9ASUAAJG', 'HTTPStatusCode': 200, 'HTTPHeaders': {'server': 'Server', 'date': 'Sat, 09 Jan 2021 08:19:15 GMT', 'content-type': 'application/x-amz-json-1.0', 'content-length': '66', 'connection': 'keep-alive', 'x-amzn-requestid': '898QE0R431PLKG3QJRGH26EUN7VV4KQNSO5AEMVJF66Q9ASUAAJG', 'x-amz-crc32': '379791711'}, 'RetryAttempts': 0}}

I was able to update. However, if you UPDATE without specifying the Key, an error will occur.

>>> clinet.execute_statement(Statement="UPDATE Questions SET title='foofoo'")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/kohei/.local/lib/python3.8/site-packages/botocore/client.py", line 357, in _api_call
    return self._make_api_call(operation_name, kwargs)
  File "/home/kohei/.local/lib/python3.8/site-packages/botocore/client.py", line 676, in _make_api_call
    raise error_class(parsed_response, operation_name)
botocore.exceptions.ClientError: An error occurred (ValidationException) when calling the ExecuteStatement operation: Where clause does not contain a mandatory equality on all key attributes

I was able to DELETE.

>>> clinet.execute_statement(Statement="DELETE FROM Questions WHERE questionId='hogehoge'")
{'Items': [], 'ResponseMetadata': {'RequestId': 'AQOONAN30T0NHC0OAPS1QLFH3RVV4KQNSO5AEMVJF66Q9ASUAAJG', 'HTTPStatusCode': 200, 'HTTPHeaders': {'server': 'Server', 'date': 'Sat, 09 Jan 2021 08:21:54 GMT', 'content-type': 'application/x-amz-json-1.0', 'content-length': '12', 'connection': 'keep-alive', 'x-amzn-requestid': 'AQOONAN30T0NHC0OAPS1QLFH3RVV4KQNSO5AEMVJF66Q9ASUAAJG', 'x-amz-crc32': '2770214093'}, 'RetryAttempts': 0}}
>>> clinet.execute_statement(Statement="SELECT * FROM Questions")
{'Items': [], 'ResponseMetadata': {'RequestId': 'AV8FBJ2FU4KUDEI3J0R184RJ47VV4KQNSO5AEMVJF66Q9ASUAAJG', 'HTTPStatusCode': 200, 'HTTPHeaders': {'server': 'Server', 'date': 'Sat, 09 Jan 2021 08:21:57 GMT', 'content-type': 'application/x-amz-json-1.0', 'content-length': '12', 'connection': 'keep-alive', 'x-amzn-requestid': 'AV8FBJ2FU4KUDEI3J0R184RJ47VV4KQNSO5AEMVJF66Q9ASUAAJG', 'x-amz-crc32': '2770214093'}, 'RetryAttempts': 0}}

Summary

I'm a little worried because I can execute Query without being aware of Index, but it's nice to have the SQL interface so far.

reference

boto3 documentation