DynamoDB JavaScript SDK v2 v3: scan table data with pagination – 5

by · Published · Updated

Spread the love
           

Querying is the basis of database management, DynamoDB offers various constructs to be able to query our tables.

DynamoDB JavaScript SDK v2 and v3 scan table data with pagination
DynamoDB JavaScript SDK v2 and v3 scan table data with pagination

The Query operation on the Amazon DynamoDB allows you to extract data based on the various elements but above all partitioned based on the primary key.

You must provide the name of the partition key attribute and a single value for that attribute. Query returns all items with that partition key value. Optionally, you can provide a sort key attribute and use a comparison operator to refine the search results.

But first let’s remember the structure of the table we created:

  • ItemId:
    • HASH Key in String format;
    • Discriminating bringing together all the url for that site;
    • ES: mischianti, github etc.
  • ItemName:
    • RANGE Key in String format;
    • Full name with all subdomain of the url;
    • ES: home.mischianti.org, mischianti.org, github.com, docs.github.com etc.
  • ValueNum:
    • Numeric value;
    • Subdomain level.
  • ValueStr:
    • String value;
    • Description.

Normally the name of the column don’t have a meaningful name, but for this test is important only the type.

Create table and put some testing data inside.

First we must create an environment to do some query, so we are going to create the table and add some data with a simple script.

You can do this operation directly from console at (change region with your):

https://eu-west-1.console.aws.amazon.com/dynamodb/home?region=eu-west-1#tables:

Than click on create table

you must add the table name TestTableMischianti, a partition key ItemId and a sort key ItemName.

SDK v2

I add again the create table script from the previous article.

To execute this script go in the folder dynamodb-examples\jsv2 and launch node table_create.js.

/*
 * DynamoDB Script Examples
 * Create table in
 * DB of selected region in AWS.config.update
 *
 * AUTHOR:  Renzo Mischianti https://mischianti.org/
 *
 * The MIT License (MIT)
 *
 * Copyright (c) 2020 Renzo Mischianti www.mischianti.org All right reserved.
 *
 * You may copy, alter and reuse this code in any way you like, but please leave
 * reference to www.mischianti.org in your comments if you redistribute this code.
 *
 */

var AWS = require("aws-sdk");

AWS.config.update({
    apiVersion: '2012-08-10',
    region: "eu-west-1",
    // endpoint: "http://localhost:8000",
    // // accessKeyId default can be used while using the downloadable version of DynamoDB.
    // // For security reasons, do not store AWS Credentials in your files. Use Amazon Cognito instead.
    // accessKeyId: "9oiaf7",
    // // secretAccessKey default can be used while using the downloadable version of DynamoDB.
    // // For security reasons, do not store AWS Credentials in your files. Use Amazon Cognito instead.
    // secretAccessKey: "yz5i9"
});

var ddb = new AWS.DynamoDB();

console.log("Start script!");

var params = {
    TableName: 'TestTableMischianti',
    KeySchema: [ // The type of of schema.  Must start with a HASH type, with an optional second RANGE.
        { // Required HASH type attribute
            AttributeName: 'ItemId',
            KeyType: 'HASH',
        },
        {
            AttributeName: 'ItemName',
            KeyType: 'RANGE'
        }
    ],
    AttributeDefinitions: [ // The names and types of all primary and index key attributes only
        { // Type attribute
            AttributeName: 'ItemId',
            AttributeType: 'S',
        },
        {
            AttributeName: 'ItemName',
            AttributeType: 'S'
        }
        // ... more attributes ...
    ],
    ProvisionedThroughput: { // required provisioned throughput for the table
        ReadCapacityUnits: 1,
        WriteCapacityUnits: 1,
    },
};

ddb.createTable(params, function(err, data) {
    if (err) {
        console.log('Full error response', JSON.stringify(err,null,2)); // an error occurred

        console.log('message --> ', err.message);
    } else {
        console.log('Full success response', JSON.stringify(data,null,2)); // successful response
    }
});
console.log("End script!");

Now a simple script that grab data from a file in json format and fill the table.

node preload_table.js

/*
 * DynamoDB Script Examples
 * Preload example table
 * DB of selected region in AWS.config.update
 *
 * AUTHOR:  Renzo Mischianti
 *
 * https://mischianti.org/
 *
 * The MIT License (MIT)
 *
 * Copyright (c) 2017 Renzo Mischianti www.mischianti.org All right reserved.
 *
 * You may copy, alter and reuse this code in any way you like, but please leave
 * reference to www.mischianti.org in your comments if you redistribute this code.
 */

var AWS = require("aws-sdk");
var fs = require('fs');

AWS.config.update({
    apiVersion: '2012-08-10',
    region: "eu-west-1",
    // endpoint: "http://localhost:8000",
    // // accessKeyId default can be used while using the downloadable version of DynamoDB.
    // // For security reasons, do not store AWS Credentials in your files. Use Amazon Cognito instead.
    // accessKeyId: "9oiaf7",
    // // secretAccessKey default can be used while using the downloadable version of DynamoDB.
    // // For security reasons, do not store AWS Credentials in your files. Use Amazon Cognito instead.
    // secretAccessKey: "yz5i9"
});

var ddb = new AWS.DynamoDB();

console.log("Start script!");

var allURLs = JSON.parse(fs.readFileSync('data/urls.json', 'utf8'));
allURLs.forEach(function(item) {
    var params = {
        TableName: "TestTableMischianti",
        Item: {
            "ItemId": {S: item.ItemId},
            "ItemName": {S: item.ItemName},
            "ValueStr": {S: item.ValueStr},
            "ValueNum": {N: item.ValueNum.toString()},
        }
    };

    ddb.putItem(params, function(err, data) {
        if (err) {
            console.error("Unable add item", params.ItemName, ". Error JSON:", JSON.stringify(err, null, 2));
        } else {
            console.log("PutItem succeeded: ", item.ItemName);
        }
    });
});

the json file has this content.

[
  {
    "ItemId": "mischianti",
    "ItemName": "www.mischianti.org",
    "ValueStr": "Main url mischianti",
    "ValueNum": 1
  },
  {
    "ItemId": "mischianti",
    "ItemName": "home.mischianti.org",
    "ValueStr": "Test url mischianti",
    "ValueNum": 2
  },
  {
    "ItemId": "mischianti",
    "ItemName": "mqtt.mischianti.org",
    "ValueStr": "MQTT url mischianti",
    "ValueNum": 2
  },
  {
    "ItemId": "mischianti",
    "ItemName": "game.home.mischianti.org",
    "ValueStr": "Game test url mischianti",
    "ValueNum": 3
  },
  {
    "ItemId": "mischianti",
    "ItemName": "mqtt.home.mischianti.org",
    "ValueStr": "MQTT test url mischianti",
    "ValueNum": 3
  },
  {
    "ItemId": "git",
    "ItemName": "www.github.com",
    "ValueStr": "Main url github",
    "ValueNum": 1
  },
  {
    "ItemId": "git",
    "ItemName": "docs.github.com",
    "ValueStr": "Docs url github",
    "ValueNum": 2
  }
]

SDK v3

For SDK v3 we are going to launch the relative script.

To execute this script go in the folder dynamodb-examples\jsv3 and launch node table_create_async_await.js.

/*
 * DynamoDB Script Examples v3
 * Create table with DynamoDB async await
 * DB of selected region in configDynamoDB
 *
 * AUTHOR:  Renzo Mischianti
 *
 * https://mischianti.org/
 *
 * The MIT License (MIT)
 *
 * Copyright (c) 2017 Renzo Mischianti www.mischianti.org All right reserved.
 *
 * You may copy, alter and reuse this code in any way you like, but please leave
 * reference to www.mischianti.org in your comments if you redistribute this code.
 *
 */

const { DynamoDBClient, CreateTableCommand  } = require("@aws-sdk/client-dynamodb");
const configDynamoDB = {
    version: 'latest',
    region: "eu-west-1",
    // endpoint: "http://localhost:8000",
    // credentials: {
    //     // accessKeyId default can be used while using the downloadable version of DynamoDB.
    //     // For security reasons, do not store AWS Credentials in your files. Use Amazon Cognito instead.
    //     accessKeyId: "9oiaf7",
    //     // secretAccessKey default can be used while using the downloadable version of DynamoDB.
    //     // For security reasons, do not store AWS Credentials in your files. Use Amazon Cognito instead.
    //     secretAccessKey: "yz5i9"
    //
    // }
};

const dbClient = new DynamoDBClient(configDynamoDB);

(async function () {
    console.log("Start script!");

    var params = {
        TableName: 'TestTableMischianti',
        KeySchema: [ // The type of of schema.  Must start with a HASH type, with an optional second RANGE.
            { // Required HASH type attribute
                AttributeName: 'ItemId',
                KeyType: 'HASH',
            },
            {
                AttributeName: 'ItemName',
                KeyType: 'RANGE'
            }
        ],
        AttributeDefinitions: [ // The names and types of all primary and index key attributes only
            { // Type attribute
                AttributeName: 'ItemId',
                AttributeType: 'S',
            },
            {
                AttributeName: 'ItemName',
                AttributeType: 'S'
            }
            // ... more attributes ...
        ],
        ProvisionedThroughput: { // required provisioned throughput for the table
            ReadCapacityUnits: 1,
            WriteCapacityUnits: 1,
        },
    };

    try {
        const command = new CreateTableCommand(params);
        const data = await dbClient.send(command);
        console.log('Full success response', JSON.stringify(data,null,2)); // successful response
    }catch (err) {
        console.log('Full error response', JSON.stringify(err,null,2)); // an error occurred
    }

    console.log("End script!");
})();

and then the preload script to fill the table.

node preload_table.js

/*
 * DynamoDB Script Examples v3
 * Preload example table
 * DB of selected region in configDynamoDB
 *
 * AUTHOR:  Renzo Mischianti
 *
 * https://mischianti.org/
 *
 * The MIT License (MIT)
 *
 * Copyright (c) 2017 Renzo Mischianti www.mischianti.org All right reserved.
 *
 * You may copy, alter and reuse this code in any way you like, but please leave
 * reference to www.mischianti.org in your comments if you redistribute this code.
 */

const { DynamoDBClient, PutItemCommand } = require("@aws-sdk/client-dynamodb");
var fs = require('fs');

const configDynamoDB = {
    version: 'latest',
    region: "eu-west-1",
    // endpoint: "http://localhost:8000",
    // credentials: {
    //     // accessKeyId default can be used while using the downloadable version of DynamoDB.
    //     // For security reasons, do not store AWS Credentials in your files. Use Amazon Cognito instead.
    //     accessKeyId: "9oiaf7",
    //     // secretAccessKey default can be used while using the downloadable version of DynamoDB.
    //     // For security reasons, do not store AWS Credentials in your files. Use Amazon Cognito instead.
    //     secretAccessKey: "yz5i9"
    //
    // }
};

const dbClient = new DynamoDBClient(configDynamoDB);

console.log("Start script!");

var allURLs = JSON.parse(fs.readFileSync('data/urls.json', 'utf8'));
allURLs.forEach(function(item) {
    var params = {
        TableName: "TestTableMischianti",
        Item: {
            "ItemId": {S: item.ItemId},
            "ItemName": {S: item.ItemName},
            "ValueStr": {S: item.ValueStr},
            "ValueNum": {N: item.ValueNum.toString()},
        }
    };
    const command = new PutItemCommand(params);
    dbClient.send(command).then(
        data => console.log("PutItem succeeded: ", item.ItemName)
    ).catch(
        err => console.error("Unable add item", params.ItemName, ". Error JSON:", JSON.stringify(err, null, 2))
    );
});

console.log("End script!");

Now you must have this situation.

DynamoDB Table items from AWS console with preload
DynamoDB Table items from AWS console with preload

Now we can start to do some query test.

Scan table

A Scan operation in Amazon DynamoDB reads every item in a table or a secondary index. By default, returns all of the data attributes for every item in the table or index. You can use the ProjectionExpression parameter so that Scan only returns some of the attributes, rather than all of them.

Scan always returns a result set. If no matching items are found, the result set is empty.

A single Scan request can retrieve a maximum of 1 MB of data. Optionally, DynamoDB can apply a filter expression to this data, narrowing the results before they are returned to the user.

Now we are going to do a simple scan with only one filter to ItemId (formally the category) equal to ‘mischianti‘.

var params = {
    TableName: "TestTableMischianti",
    ExpressionAttributeValues: {
        ':endpoint_category' : {S: 'mischianti'}
    },
    ProjectionExpression: 'ItemName, ValueStr',
    FilterExpression: 'ItemId = :endpoint_category'
}

We are going to explain better the FilterExpression in the next article about query.

SDK v2

Now a simple scan script with a basic condition.

To execute this script go in the folder dynamodb-examples\jsv2 and launch node items_scan.js.

/*
 * DynamoDB Script Examples
 * Scan items with DynamoDB
 * DB of selected region in AWS.config.update
 *
 * AUTHOR:  Renzo Mischianti
 *
 * https://mischianti.org/
 *
 * The MIT License (MIT)
 *
 * Copyright (c) 2017 Renzo Mischianti www.mischianti.org All right reserved.
 *
 * You may copy, alter and reuse this code in any way you like, but please leave
 * reference to www.mischianti.org in your comments if you redistribute this code.
 */

var AWS = require("aws-sdk");

AWS.config.update({
    apiVersion: '2012-08-10',
    region: "eu-west-1",
    // endpoint: "http://localhost:8000",
    // // accessKeyId default can be used while using the downloadable version of DynamoDB.
    // // For security reasons, do not store AWS Credentials in your files. Use Amazon Cognito instead.
    // accessKeyId: "9oiaf7",
    // // secretAccessKey default can be used while using the downloadable version of DynamoDB.
    // // For security reasons, do not store AWS Credentials in your files. Use Amazon Cognito instead.
    // secretAccessKey: "yz5i9"
});

var ddb = new AWS.DynamoDB();

console.log("Start script!");

var params = {
    TableName: "TestTableMischianti",
    ExpressionAttributeValues: {
        ':endpoint_category' : {S: 'mischianti'}
    },
    ProjectionExpression: 'ItemName, ValueStr',
    FilterExpression: 'ItemId = :endpoint_category'
};

ddb.scan(params, function (err, data) {
    if (err) {
        console.error("Unable ti get item", JSON.stringify(params, null, 2), ". Error JSON:", JSON.stringify(err, null, 2));
    } else {
        console.log("Get Item succeeded:", JSON.stringify(data, null, 2));
    }
});

console.log("End script!");

the result in console is quite interesting

D:\Projects\AlexaProjects\dynamodb-management\dynamodb-examples\jsv2>node items_scan.js
Start script!
End script!
Get Item succeeded: {
  "Items": [
    {
      "ItemName": {
        "S": "game.home.mischianti.org"
      },
      "ValueStr": {
        "S": "Game test url mischianti"
      }
    },
    {
      "ItemName": {
        "S": "home.mischianti.org"
      },
      "ValueStr": {
        "S": "Test url mischianti"
      }
    },
    {
      "ItemName": {
        "S": "mqtt.home.mischianti.org"
      },
      "ValueStr": {
        "S": "MQTT test url mischianti"
      }
    },
    {
      "ItemName": {
        "S": "mqtt.mischianti.org"
      },
      "ValueStr": {
        "S": "MQTT url mischianti"
      }
    },
    {
      "ItemName": {
        "S": "www.mischianti.org"
      },
      "ValueStr": {
        "S": "Main url mischianti"
      }
    }
  ],
  "Count": 5,
  "ScannedCount": 7
}

In particular you can check Count and ScannedCount, the first is the number of result, the second is the line scanned to retrieve data, memorize this data, when we go to analyze the query we discover an important feature that affects performance.

The scan read all the lines and give us the result.

SDK v3

Now the same script with v3 SDK.

To execute this script go in the folder dynamodb-examples\jsv3 and launch node items_scan.js.

/*
 * DynamoDB Script Examples v3
 * Scan items with DynamoDB async await
 * DB of selected region in configDynamoDB
 *
 * AUTHOR:  Renzo Mischianti
 *
 * https://mischianti.org/
 *
 * The MIT License (MIT)
 *
 * Copyright (c) 2017 Renzo Mischianti www.mischianti.org All right reserved.
 *
 * You may copy, alter and reuse this code in any way you like, but please leave
 * reference to www.mischianti.org in your comments if you redistribute this code.
 *
 */

const { DynamoDBClient, ScanCommand  } = require("@aws-sdk/client-dynamodb");
const configDynamoDB = {
    version: 'latest',
    region: "eu-west-1",
    // endpoint: "http://localhost:8000",
    // credentials: {
    //     // accessKeyId default can be used while using the downloadable version of DynamoDB.
    //     // For security reasons, do not store AWS Credentials in your files. Use Amazon Cognito instead.
    //     accessKeyId: "9oiaf7",
    //     // secretAccessKey default can be used while using the downloadable version of DynamoDB.
    //     // For security reasons, do not store AWS Credentials in your files. Use Amazon Cognito instead.
    //     secretAccessKey: "yz5i9"
    //
    // }
};

const dbClient = new DynamoDBClient(configDynamoDB);

(async function () {
    console.log("Start script!");

// var params = {
//     TableName: 'TestTableMischianti',
//     Select: 'ALL_ATTRIBUTES', // optional (ALL_ATTRIBUTES | ALL_PROJECTED_ATTRIBUTES | SPECIFIC_ATTRIBUTES | COUNT)
// };
    var params = {
        TableName: "TestTableMischianti",
        ExpressionAttributeValues: {
            ':endpoint_category' : {S: 'mischianti'}
        },
        ProjectionExpression: 'ItemName, ValueStr',
        FilterExpression: 'ItemId = :endpoint_category',
    };

    try {
        const command = new ScanCommand(params);
        const data = await dbClient.send(command);
        console.log("PutItem succeeded:", JSON.stringify(params, null, 2), JSON.stringify( data, null, 2),);
    }catch (err) {
        console.error("Unable add item", JSON.stringify(params, null, 2), ". Error JSON:", JSON.stringify(err, null, 2));
    }

    console.log("End script!");
})();

and the console result

D:\Projects\AlexaProjects\dynamodb-management\dynamodb-examples\jsv3>node items_scan_async_await.js
Start script!
PutItem succeeded: {
  "TableName": "TestTableMischianti",
  "ExpressionAttributeValues": {
    ":endpoint_category": {
      "S": "mischianti"
    }
  },
  "ProjectionExpression": "ItemName, ValueStr",
  "FilterExpression": "ItemId = :endpoint_category"
} {
  "$metadata": {
    "httpStatusCode": 200,
    "httpHeaders": {
      "server": "Server",
      "date": "Wed, 03 Feb 2021 21:10:05 GMT",
      "content-type": "application/x-amz-json-1.0",
      "content-length": "457",
      "connection": "keep-alive",
      "x-amzn-requestid": "HBHG7J1HPVV22RS6VIK9MPB4K7VV4KQNSO5AEMVJF66Q9ASUAAJG",
      "x-amz-crc32": "962662631"
    },
    "requestId": "HBHG7J1HPVV22RS6VIK9MPB4K7VV4KQNSO5AEMVJF66Q9ASUAAJG",
    "attempts": 1,
    "totalRetryDelay": 0
  },
  "Count": 5,
  "Items": [
    {
      "ItemName": {
        "S": "game.home.mischianti.org"
      },
      "ValueStr": {
        "S": "Game test url mischianti"
      }
    },
    {
      "ItemName": {
        "S": "home.mischianti.org"
      },
      "ValueStr": {
        "S": "Test url mischianti"
      }
    },
    {
      "ItemName": {
        "S": "mqtt.home.mischianti.org"
      },
      "ValueStr": {
        "S": "MQTT test url mischianti"
      }
    },
    {
      "ItemName": {
        "S": "mqtt.mischianti.org"
      },
      "ValueStr": {
        "S": "MQTT url mischianti"
      }
    },
    {
      "ItemName": {
        "S": "www.mischianti.org"
      },
      "ValueStr": {
        "S": "Main url mischianti"
      }
    }
  ],
  "ScannedCount": 7
}
End script!

Scan: pagination

Now we are going to add an additional data to the script parameter, the Limit as you can guess, this parameter limits the number of results.

So with this parameter

    var params = {
        TableName: "TestTableMischianti",
        ExpressionAttributeValues: {
            ':endpoint_category' : {S: 'mischianti'}
        },
        ProjectionExpression: 'ItemName, ValueStr',
        FilterExpression: 'ItemId = :endpoint_category',
        Limit: 2,
    };

You obtain this console output.

{
  "$metadata": {
    "httpStatusCode": 200,
    "httpHeaders": {
      "server": "Server",
      "date": "Wed, 03 Feb 2021 21:16:20 GMT",
      "content-type": "application/x-amz-json-1.0",
      "content-length": "296",
      "connection": "keep-alive",
      "x-amzn-requestid": "CPKH4513E21CAL47678MMUDNA3VV4KQNSO5AEMVJF66Q9ASUAAJG",
      "x-amz-crc32": "1953278109"
    },
    "requestId": "CPKH4513E21CAL47678MMUDNA3VV4KQNSO5AEMVJF66Q9ASUAAJG",
    "attempts": 1,
    "totalRetryDelay": 0
  },
  "Items": [
    {
      "ItemName": {
        "S": "game.home.mischianti.org"
      },
      "ValueStr": {
        "S": "Game test url mischianti"
      }
    },
    {
      "ItemName": {
        "S": "home.mischianti.org"
      },
      "ValueStr": {
        "S": "Test url mischianti"
      }
    }
  ],
  "LastEvaluatedKey": {
    "ItemName": {
      "S": "home.mischianti.org"
    },
    "ItemId": {
      "S": "mischianti"
    }
  },
  "ScannedCount": 2,
  "Count": 2
}

As you can read 2 line is scanned and 2 result is given, but there is another important information LastEvaluatedKey.

"LastEvaluatedKey": {
    "ItemName": {
      "S": "home.mischianti.org"
    },
    "ItemId": {
      "S": "mischianti"
    }
  }

you can use this parameter as scan starter point, so if you set an additional data ExclusiveStartKey to the params whit this value

    var params = {
        TableName: "TestTableMischianti",
        ExpressionAttributeValues: {
            ':endpoint_category' : {S: 'mischianti'}
        },
        ProjectionExpression: 'ItemName, ValueStr',
        FilterExpression: 'ItemId = :endpoint_category',
        Limit: 2,
        ExclusiveStartKey: {
            "ItemName": {
                "S": "www.mischianti.org"
            },
            "ItemId": {
                "S": "mischianti"
            }
        }
    };

you obtain this result

{
  "$metadata": {
    "httpStatusCode": 200,
    "httpHeaders": {
      "server": "Server",
      "date": "Wed, 03 Feb 2021 21:21:07 GMT",
      "content-type": "application/x-amz-json-1.0",
      "content-length": "296",
      "connection": "keep-alive",
      "x-amzn-requestid": "CJH4EFQ4VHKQ2UL11S4BHHBR0BVV4KQNSO5AEMVJF66Q9ASUAAJG",
      "x-amz-crc32": "3887146499"
    },
    "requestId": "CJH4EFQ4VHKQ2UL11S4BHHBR0BVV4KQNSO5AEMVJF66Q9ASUAAJG",
    "attempts": 1,
    "totalRetryDelay": 0
  },
  "Items": [
    {
      "ItemName": {
        "S": "mqtt.home.mischianti.org"
      },
      "ValueStr": {
        "S": "MQTT test url mischianti"
      }
    },
    {
      "ItemName": {
        "S": "mqtt.mischianti.org"
      },
      "ValueStr": {
        "S": "MQTT url mischianti"
      }
    }
  ],
  "LastEvaluatedKey": {
    "ItemName": {
      "S": "mqtt.mischianti.org"
    },
    "ItemId": {
      "S": "mischianti"
    }
  },
  "Count": 2,
  "ScannedCount": 2
}

so the scan read 2 line and return next 2 elements, and It continue to return LastEvaluatedKey until the data arrive to the end.

Thanks

  1. DynamoDB JavaScript SDK v2 v3: prerequisite and SDK v2 v3 introduction
  2. DynamoDB JavaScript SDK v2 v3: manage tables
  3. DynamoDB JavaScript SDK v2 v3: add items with DB or DocumentClient
  4. DynamoDB JavaScript SDK v2 v3: manage items
  5. DynamoDB JavaScript SDK v2 v3: scan table data and pagination
  6. DynamoDB JavaScript SDK v2 v3: query
AWS Support forum

Spread the love
           

Tags:

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions - Privacy Policy latest - Privacy Policy with company name - Privacy Policy