Skip to main content

DynamoDB Interactions

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:

  1. native DynamoDB JSON specification or
  1. 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 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/

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.