Photo Credit European Space Agency Astronaut Samantha Cristoforetti. https://www.nasa.gov/mission_pages/station/expeditions/expedition42/gallery.html
Photo Credit European Space Agency Astronaut Samantha Cristoforetti. https://www.nasa.gov/mission_pages/station/expeditions/expedition42/gallery.html

Interacting with AWS Aurora Serverless

mysql -u admin cluster-name-east-1.rds.amazonaws.com

Aurora Serverless

The Aurora Web Data API

Enabling the Data API

Allowing Access to the Data API

{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "SecretsManagerDbCredentialsAccess",
"Effect": "Allow",
"Action": [
"secretsmanager:GetSecretValue"
],
"Resource": "arn:aws:secretsmanager:*:*:secret:rds-db-credentials/*"
},
{
"Sid": "RDSDataServiceAccess",
"Effect": "Allow",
"Action": [
"rds-data:BatchExecuteStatement",
"rds-data:BeginTransaction",
"rds-data:CommitTransaction",
"rds-data:ExecuteStatement",
"rds-data:RollbackTransaction"
],
"Resource": "arn:aws:rds:us-east-2:111122223333:cluster:prod"
}
]
}

Using the CLI to verify Access

aws rds-data execute-statement  \
--resource-arn "arn:aws:rds:us-east-1:ACCOUNT-ID:cluster:CLUSTER-NAME" \
--database "DATABASE-NAME" \
--secret-arn "arn:aws:secretsmanager:us-east-1:ACCOUNT-ID:secret:SECRET-NAME" \
--sql "show tables;"
{
"numberOfRecordsUpdated": 0,
"records": [
[
{
"stringValue": "assessments"
}
]
]
}

Retrieving data using CuRL and the Data API

$ cat sql.json  
{
"database": "DATABASE-NAME",
"includeResultMetadata": "true",
"resourceArn": "RESOURCE-ARN",
"secretArn": "SECRET-MANAGER-ARN",
"sql": "show tables;"
}
$

Using the Data API from a Python Function

import boto3 
rdsData = boto3.client('rds-data')
database = "DATABASE"
cluster_arn = "CLUSTER-ARN"
secret_arn = "SECRET-ARN"

response1 = rdsData.execute_statement(
resourceArn = cluster_arn,
secretArn = secret_arn,
database = database,
sql = 'show tables')
print (response1)
$ python3 example.py
Traceback (most recent call last):
File "example.py", line 14, in <module>
sql = 'show tables')
File "/usr/local/lib/python3.7/dist-packages/botocore/client.py", line 357, in _api_
call
return self._make_api_call(operation_name, kwargs)
File "/usr/local/lib/python3.7/dist-packages/botocore/client.py", line 676, in _make
_api_call
raise error_class(parsed_response, operation_name)
botocore.errorfactory.BadRequestException: An error occurred (BadRequestException) when calling the ExecuteStatement operation: Communications link failure
The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
$
$ python3 example.py
{'ResponseMetadata': {'RequestId': 'ed206a85-2cb1-4a3c-8abd-2096405da1a3', 'HTTPStatus
Code': 200, 'HTTPHeaders': {'x-amzn-requestid': 'ed206a85-2cb1-4a3c-8abd-2096405da1a3'
, 'content-type': 'application/json', 'content-length': '72', 'date': 'Sat, 23 Jan 202
1 17:21:27 GMT'}, 'RetryAttempts': 0}, 'numberOfRecordsUpdated': 0, 'records': [[{'str
ingValue': 'assessments'}]]}
$
import boto3 
rdsData = boto3.client('rds-data')
database = "paei"
cluster_arn = "arn:aws:rds:us-east-1:ACCOUNT:cluster:paei"
secret_arn = "arn:aws:secretsmanager:us-east-1:ACCOUNT:secret:rds"
c = 1
while True:
print(f"request #{c} ", end='' )
try:
response1 = rdsData.execute_statement(
resourceArn = cluster_arn,
secretArn = secret_arn,
database = database,
sql = 'show tables')
except Exception as error:
print(f"execution failure: {error}")
else:
print("execution success")
break
c += 1
print (response1)
$ python3 example-try.py
request #1 execution failure: An error occurred (BadRequestException) when calling the ExecuteStatement operation: Communications link failure
...
request #9 execution success
{'ResponseMetadata': {'RequestId': '920c176f-6933-4854-ad98-4fde2c791fef', 'HTTPStatusCode': 200, 'HTTPHeaders': {'x-amzn-requestid': '920c176f-6933-4854-ad98-4fde2c791fef', 'content-type': 'application/json', 'content-length': '72', 'date': 'Sat, 30 Jan 2021 15:15:20 GMT'}, 'RetryAttempts': 0}, 'numberOfRecordsUpdated': 0, 're

Conclusion

References

About the Author

Copyright

Chris is the co-author of seven books and author of more than 70 articles and book chapters in technical, management, and information security publications.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store