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

item version
Python 3.8.5
boto3 1.16.51
botocore 1.19.51

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. Screenshot from 2021-01-09 12-09-16.png

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. image.png

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

Recommended Posts

Run PartiQL on Dynamodb via boto3
Run Django on PythonAnywhere
Run OpenMVG on Mac
Run Jupyter on Ubuntu on Windows
Run Openpose on Python (Windows)
Run Tensorflow 2.x on Python 3.7
Run Python CGI on CORESERVER
Run unix command on python
Run IPython Notebook on Docker
Run YOLO v3 on AWS v2
Run CircuitPython on Seeeduino XIAO
Run Jupyter Notebook on windows
Run FreeBSD on Linux + qemu
Run OpenVino on macOS Catalina
Run YOLO v3 on AWS