Fully support for the naming rules for DynamoDB

[zephyrosjyd]

Hi ✋🏼

• OS: Ubuntu 24.x (AWS EC2)
• Python 3.11
• Apache Superset 4.1.1
• PyDynamoDB 0.7.1
• boto3/botocore 1.37.10

I'm running Superset for test. I tried to setup Amazon DynamoDB connector but got the below messages.

// SQLAlchemy URIs that I tried
dynamodb://aws_access_key_id:aws_secret_access_key@dynamodb.region_name.amazonaws.com:443?connector=superset
dynamodb://aws_access_key_id:aws_secret_access_key@dynamodb.region_name.amazonaws.com:443?verify=false
dynamodb://aws_access_key_id:aws_secret_access_key@dynamodb.region_name.amazonaws.com:443?verify=false&connector=superset

...
Mar 17 02:09:01 ip-10-x-x-x gunicorn[1885]:   File "/home/ubuntu/.superset/lib/python3.11/site-packages/superset/commands/database/test_connection.py", line 199, in run
Mar 17 02:09:01 ip-10-x-x-x gunicorn[1885]:     raise SupersetErrorsException(errors) from ex
Mar 17 02:09:01 ip-10-x-x-x gunicorn[1885]: superset.exceptions.SupersetErrorsException: [SupersetError(message='(builtins.NoneType) None\n[SQL: An error occurred (SignatureDoesNotMatch) when calling the AssumeRole operation: The request signature we calculated does not match the signature you provided. Check your AWS Secret Access Key and signing method. Consult the service documentation for details.]\n(Background on this error at: https://sqlalche.me/e/14/dbapi)', error_type=<SupersetErrorType.GENERIC_DB_ENGINE_ERROR: 'GENERIC_DB_ENGINE_ERROR'>, level=<ErrorLevel.ERROR: 'error'>, extra={'engine_name': 'Amazon DynamoDB', 'issue_codes': [{'code': 1002, 'message': 'Issue 1002 - The database returned an unexpected error.'}]})]

So, I investigated this problem and then I got some tests for PyDynamoDB and boto3.

import boto3

dynamodb_client = boto3.client('dynamodb', region_name='region_name',
                               aws_access_key_id='aws_access_key_id',
                               aws_secret_access_key='aws_secret_access_key')
response = dynamodb_client.execute_statement(
    Statement='SELECT * FROM "this.is.table"'
)
print(response['Items'])

is successful, but

from pydynamodb import connect
import logging

logging.basicConfig(level=logging.DEBUG)

cursor = connect(aws_access_key_id="aws_access_key_id",
                aws_secret_access_key="aws_secret_access_key",
                region_name="region_name").cursor()
cursor.execute('SELECT * FROM "this.is.table"')
print(cursor.fetchall())

is failed. This below is the log messages.

...

DEBUG:botocore.endpoint:Making request for OperationModel(name=ExecuteStatement) with params: {'url_path': '/', 'query_string': '', 'method': 'POST', 'headers': {'X-Amz-Target': 'DynamoDB_20120810.ExecuteStatement', 'Content-Type': 'application/x-amz-json-1.0', 'User-Agent': 'Boto3/1.37.10 md/Botocore#1.37.10 ua/2.1 os/linux#6.8.0-1024-aws md/arch#aarch64 lang/python#3.11.11 md/pyimpl#CPython cfg/retry-mode#legacy Botocore/1.37.10'}, 'body': b'{"Statement": "SELECT * FROM \\"this\\".\\"is\\""}', 'url': 'https://dynamodb.region_name.amazonaws.com/', 'context': {'client_region': 'region_name', 'client_config': <botocore.config.Config object at 0xe08b2a2b7b90>, 'has_streaming_input': False, 'auth_type': None, 'unsigned_payload': None}}

...

DEBUG:urllib3.connectionpool:https://dynamodb.region_name.amazonaws.com:443 "POST / HTTP/1.1" 400 112
DEBUG:botocore.parsers:Response headers: {'Server': 'Server', 'Date': 'Mon, 17 Mar 2025 05:25:35 GMT', 'Content-Type': 'application/x-amz-json-1.0', 'Content-Length': '112', 'Connection': 'keep-alive', 'x-amzn-RequestId': 'PRCVM4S3MOAQSO5AEMVJF66Q9ASUAAJG', 'x-amz-crc32': '3737639027'}
DEBUG:botocore.parsers:Response body:
b'{"__type":"com.amazonaws.dynamodb.v20120810#ResourceNotFoundException","message":"Requested resource not found"}'
DEBUG:botocore.parsers:Response headers: {'Server': 'Server', 'Date': 'Mon, 17 Mar 2025 05:25:35 GMT', 'Content-Type': 'application/x-amz-json-1.0', 'Content-Length': '112', 'Connection': 'keep-alive', 'x-amzn-RequestId': 'PRCVM4SVV4KQNSO5AEMVJF66Q9ASUAAJG', 'x-amz-crc32': '3737639027'}
DEBUG:botocore.parsers:Response body:
b'{"__type":"com.amazonaws.dynamodb.v20120810#ResourceNotFoundException","message":"Requested resource not found"}'

...
  File "/home/ubuntu/.superset/lib/python3.11/site-packages/botocore/client.py", line 1031, in _make_api_call
    raise error_class(parsed_response, operation_name)
botocore.errorfactory.ResourceNotFoundException: An error occurred (ResourceNotFoundException) when calling the ExecuteStatement operation: Requested resource not found

...

  File "/home/ubuntu/.superset/lib/python3.11/site-packages/pydynamodb/executor.py", line 93, in _dispatch_api_call
    raise OperationalError(*e.args) from e
pydynamodb.error.OperationalError: An error occurred (ResourceNotFoundException) when calling the ExecuteStatement operation: Requested resource not found

I guess that this issue is a handling problem for DynamoDB table name with dot(.).
https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/HowItWorks.NamingRulesDataTypes.html

Or let me know how I can fix it if it comes from my misunderstanding or my mistakes.
Thanks for reading. I appreciate it.

[passren]

@zephyrosjyd Thank you for bringing this issue. The first thought comes to mind is the dot in table name. I didn't realize table or index names are able to contain dot ({table}.{index} was considered). Let me run few test cases to verify this.

[passren]

@zephyrosjyd I would not suggest using . (dot) in the table or index name. I also excluded this character in the sql parser since it confused the usage of this pattern: {table}.{index}. In terms of pydynamodb I don't have better idea how to distinguish dot in the table or in the index or the dot between table & index.

[zephyrosjyd]

OK. I understand you.
But I have used the pattern (table name with dot) in production, so I would not be able to use PyDynamodb in Superset.
I'll find any other solutions.
Thank you for your response.

[zephyrosjyd]

@passren After further investigation, I found that PartiQL requires table names and index names to be enclosed in double quotes when querying indexes.
https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.select.html#ql-reference.select.parameters

Based on this, I think the issue can be resolved if PyDynamodb handles table names by enclosing them in quotes. From reviewing the PyDynamodb source code, it appears that all quotes are currently removed during processing.

This suggestion is merely my idea. Considering that boto3 supports table names with dots well, I kindly request improving this to support such names as well.

[passren]

@zephyrosjyd Thank you for the deep investigation. You are right. I removed the quotes in sql parser because I intended to use sql92 standard on PyDynamodb to avoid confusion of difference of SQL and PartiQL. In most of cases, it won't have issue until this dot case appears ;-(. Let me check if I can make quotes supported in SQL parser.

[passren]

@zephyrosjyd Already added support for the quotes in table/index name. Please update to 0.7.2. Please let me know if any issues. Thank you.