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:

1. native DynamoDB JSON specification or

2. 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.

Special Query Extensions

Qarbine provides a few special query extensions for DynamoDB as described below.

Syntax	Description
describe tables	Return a listing with the details of all of the DynamoDB tables.
describe table XXX	Return a listing with the details of the given DynamoDB table.

The folder “example/AWS/DynamoDB/DBA” has several examples of these queries along with associated templates which use them. See the “DBA Productivity” area for sample output.

Qarbine Virtual Queries

There are a few convenience queries which are mainly DBA oriented. These queries are recognized by the Qarbine driver and provide common database information.

Query	Description
list databases	Return a list of databases and their current status.
list tables	Return a list of tables.
describe tables	Provide details on all of the tables. This may take a while depending on your database structure.
describe table TABLE	Provide details on the given table.

See the “DBA Productivity” section of the online documentation for more details.

Troubleshooting

Native JSON Interface

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.

PartiQL Interactions

THe home page for the DynamoDB console for the us-east-1 region is at
https://us-east-1.console.aws.amazon.com/dynamodbv2/home?region=us-east-1#dashboard

Navigate to your DynamoDB region. The home page includes the options shown below.

  

. . .

Click on the PartiQL editor option.
Enter your query to test.

  

Click

  

Review the results in the different tabs.

  

As appropriate, adjust your Qarbine query and save the associated components. To run choose the control-Run click so that no cached components are used.

References

More information on DynamoDB and PartiQL can be found at the link below.
https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Introduction.html.