DynamoDB
Overview
Amazon DynamoDB is a fully managed NoSQL database service offered by Amazon Web Services (AWS). It supports key-value and document data structures and is designed to handle large-scale, high-traffic applications. For more details see https://aws.amazon.com/dynamodb/.
Query Language Options
Qarbine can query DynamoDB in 2 formats:
- native DynamoDB JSON specification or
- PartiQL (SQL’ish).
For information on the JSON specification details see https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.html, https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.select.html
For information on the AWS PartiQL query language see https://partiql.org/.
General Querying
Below is a sample JSON specification query.
{
TableName : "Movies",
ProjectionExpression: "#yr, title, info.rating",
FilterExpression: "#info.#rating = :rating ",
ExpressionAttributeNames: {
"#yr": "year",
"#info": "info", "#rating" : "rating"
},
ExpressionAttributeValues: {
":rating" : 8
}
}
The equivalent PartiQL query is
select * from Movies
where info.rating = 8
PartiQL is a subset of SQL. See the Tutorials area for the one describing an example using a DynamoDB data source, template, and prompt.
To directly access DynamoDB features use its native query syntax which is structured as a JSON object. Below is an example
{
TableName : "Movies",
ProjectionExpression: "#yr, title, info.rating",
FilterExpression: "#yr > :minYear AND #info.#rating >= :rating ",
ExpressionAttributeNames: {
"#yr": "year"
, "#info": "info", "#rating" : "rating"
},
ExpressionAttributeValues: {
":rating" : @minRating,
":minYear" : @minYear
}
}
This specification uses 2 runtime variables which can be filled in via a prompt. The above is equivalent to
select year, title, info.rating from Movies
where year > @minYear and info,rating >= @minRating
Sometimes the native syntax is needed though. An example is the flag
ConsistentRead: true,
which forces strongly consistent reads vs eventually consistent ones. Another SQL difference example is
select year, title, info from Movies
where year = 2012 and info.rating = 8
limit 20
which results in
Unsupported clause: LIMIT at 3:7:2
For complete details on the DynamoDB query specification structure see
https://docs.aws.amazon.com/AWSJavaScriptSDK/v3/latest/client/dynamodb/command/QueryCommand/
Reviewing the Generated Specification
You can enter criteria of the form “EXPLAIN SELECT ….” to have the SQL statement processed and have the returned answer set be the underlying query specification. When you want to see the effective query specification simply precede your SELECT statement with the “explain ” text. Another way to get the specification is to press ALT and click . Any “explain SELECT” or “explain: true” takes precedence over the ALT-click interaction.
Troubleshooting
Occasionally the feedback that Qarbine propagates back from DynamoDB is insufficient to resolve your querying issue. In this case it is best to simplify the situation and go as barebone as possible. This can help you directly resolve the syntax issue or provide your AWS support team the necessary simple test case.
If granted authority, SSH into the Qarbine host to run a simple node.js program containing your query specification. Alternatively you can create a local folder on your laptop and “npm install aws”.
The Qarbine administrator previously configured access to DynamoDB using AWS credentials. These same credentials are needed for the test program code shown below. The source code resides in ˜/qarbine.service/sample/aws/dynamodb as testQuery.js. Edit it accordingly.
const { DynamoDB} = require("aws-sdk");
const config = {
endpoint: "yourEndPoint",
accessKeyId: "yourAccessKeyId",
secretAccessKey: "yourSecretAccessKey",
region: "yourRegion",
}
async function test()
{
var client = new DynamoDB.DocumentClient(config);
const input = {
TableName : "Movies",
Select: 'ALL_ATTRIBUTES',
FilterExpression: "#info.#rating = :rating ",
ExpressionAttributeNames: {
"#info": "info", "#rating" : "rating"
},
ExpressionAttributeValues: {
":rating" : 8
}
};
var myPromise = new Promise( function(resolve, reject) {
client.scan(input, function(errorObject, result)
{
if (errorObject)
console.log('ERROR', errorObject);
else
{
var answerSet = result.Items || [ ];
console.log(answerSet);
}
} );
});
}
test();
To perform the testing run
node testQuery.js
Note that this code only returns the first page of the answer set. Once your query specification issue has been resolved, update the Qarbine world accordingly.
References
More information on DynamoDB and PartiQL can be found at the link below.
https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Introduction.html.