How to get item count from DynamoDB?
DatabaseAmazon DynamodbDatabase Problem Overview
I want to know item count with DynamoDB querying.
I can querying for DynamoDB, but I only want to know 'total count of item'.
For example, 'SELECT COUNT(*) FROM ... WHERE ...' in MySQL
$result = $aws->query(array(
'TableName' => 'game_table',
'IndexName' => 'week-point-index',
'KeyConditions' => array(
'week' => array(
'ComparisonOperator' => 'EQ',
'AttributeValueList' => array(
array(Type::STRING => $week)
)
),
'point' => array(
'ComparisonOperator' => 'GE',
'AttributeValueList' => array(
array(Type::NUMBER => $my_point)
)
)
),
));
echo Count($result['Items']);
this code gets the all users data higher than my point.
If count of $result is 100,000, $result is too much big. And it would exceed the limits of the query size.
I need help.
Database Solutions
Solution 1 - Database
With the aws dynamodb cli you can get it via scan as follows:
aws dynamodb scan --table-name <TABLE_NAME> --select "COUNT"
The response will look similar to this:
{
"Count": 123,
"ScannedCount": 123,
"ConsumedCapacity": null
}
notice that this information is in real time in contrast to the describe-table api
Solution 2 - Database
You can use the Select
parameter and use COUNT
in the request. It "returns the number of matching items, rather than the matching items themselves". Important, as brought up by Saumitra R. Bhave in a comment, "If the size of the Query result set is larger than 1 MB, then ScannedCount and Count will represent only a partial count of the total items. You will need to perform multiple Query operations in order to retrieve all of the results".
I'm Not familiar with PHP but here is how you could use it with Java. And then instead of using Count
(which I am guessing is a function in PHP) on the 'Items'
you can use the Count
value from the response - $result['Count']
:
final String week = "whatever";
final Integer myPoint = 1337;
Condition weekCondition = new Condition()
.withComparisonOperator(ComparisonOperator.EQ)
.withAttributeValueList(new AttributeValue().withS(week));
Condition myPointCondition = new Condition()
.withComparisonOperator(ComparisonOperator.GE)
.withAttributeValueList(new AttributeValue().withN(myPoint.toString()))
Map<String, Condition> keyConditions = new HashMap<>();
keyConditions.put("week", weekCondition);
keyConditions.put("point", myPointCondition);
QueryRequest request = new QueryRequest("game_table");
request.setIndexName("week-point-index");
request.setSelect(Select.COUNT);
request.setKeyConditions(keyConditions);
QueryResult result = dynamoDBClient.query(request);
Integer count = result.getCount();
If you don't need to emulate the WHERE
clause, you can use a DescribeTable
request and use the resulting item count to get an estimate.
> The number of items in the specified table. DynamoDB updates this value approximately every six hours. Recent changes might not be reflected in this value.
Also, an important note from the documentation as noted by Saumitra R. Bhave in the comments on this answer:
> If the size of the Query
result set is larger than 1 MB, ScannedCount
and Count
represent only a partial count of the total items. You need to perform multiple Query
operations to retrieve all the results (see Paginating Table Query Results).
Solution 3 - Database
Can be seen from UI as well. Go to overview tab on table, you will see item count. Hope it helps someone.
Solution 4 - Database
Replace the table name and use the below query to get the data on your local environment:
aws dynamodb scan --table-name <TABLE_NAME> --select "COUNT" --endpoint-url http://localhost:8000
Replace the table name and remove the endpoint url to get the data on production environment
aws dynamodb scan --table-name <TABLE_NAME> --select "COUNT"
Solution 5 - Database
I'm too late here but like to extend Daniel's answer about using aws cli
to include filter expression.
Running
aws dynamodb scan \
--table-name <tableName> \
--filter-expression "#v = :num" \
--expression-attribute-names '{"#v": "fieldName"}' \
--expression-attribute-values '{":num": {"N": "123"}}' \
--select "COUNT"
would give
{
"Count": 2945,
"ScannedCount": 7874,
"ConsumedCapacity": null
}
That is, ScannedCount
is total count and Count
is the number of items which are filtered by given expression (fieldName=123).
Solution 6 - Database
If you happen to reach here, and you are working with C#, here is the code:
var cancellationToken = new CancellationToken();
var request = new ScanRequest("TableName") {Select = Select.COUNT};
var result = context.Client.ScanAsync(request, cancellationToken).Result;
totalCount = result.Count;
Solution 7 - Database
If anyone is looking for a straight forward NodeJS Lambda count solution:
const data = await dynamo.scan({ Select: "COUNT", TableName: "table" }).promise();
// data.Count -> number of elements in table.
Solution 8 - Database
I'm posting this answer for anyone using C# that wants a fully functional, well-tested answer that demonstrates using query instead of scan. In particular, this answer handles more than 1MB size of items to count.
public async Task<int> GetAvailableCount(string pool_type, string pool_key)
{
var queryRequest = new QueryRequest
{
TableName = PoolsDb.TableName,
ConsistentRead = true,
Select = Select.COUNT,
KeyConditionExpression = "pool_type_plus_pool_key = :type_plus_key",
ExpressionAttributeValues = new Dictionary<string, AttributeValue> {
{":type_plus_key", new AttributeValue { S = pool_type + pool_key }}
},
};
var t0 = DateTime.UtcNow;
var result = await Client.QueryAsync(queryRequest);
var count = result.Count;
var iter = 0;
while ( result.LastEvaluatedKey != null && result.LastEvaluatedKey.Values.Count > 0)
{
iter++;
var lastkey = result.LastEvaluatedKey.Values.ToList()[0].S;
_logger.LogDebug($"GetAvailableCount {pool_type}-{pool_key} iteration {iter} instance key {lastkey}");
queryRequest.ExclusiveStartKey = result.LastEvaluatedKey;
result = await Client.QueryAsync(queryRequest);
count += result.Count;
}
_logger.LogDebug($"GetAvailableCount {pool_type}-{pool_key} returned {count} after {iter} iterations in {(DateTime.UtcNow - t0).TotalMilliseconds} ms.");
return count;
}
}
Solution 9 - Database
Similar to Java in PHP only set Select PARAMETER with value 'COUNT'
$result = $aws->query(array(
'TableName' => 'game_table',
'IndexName' => 'week-point-index',
'KeyConditions' => array(
'week' => array(
'ComparisonOperator' => 'EQ',
'AttributeValueList' => array(
array(Type::STRING => $week)
)
),
'point' => array(
'ComparisonOperator' => 'GE',
'AttributeValueList' => array(
array(Type::NUMBER => $my_point)
)
)
),
'Select' => 'COUNT'
));
and acces it just like this :
echo $result['Count'];
but as Saumitra mentioned above be careful with resultsets largers than 1 MB, in that case use LastEvaluatedKey til it returns null to get the last updated count value.
Solution 10 - Database
In Scala:
import com.amazonaws.services.dynamodbv2.AmazonDynamoDBClientBuilder
import com.amazonaws.services.dynamodbv2.document.DynamoDB
val client = AmazonDynamoDBClientBuilder.standard().build()
val dynamoDB = new DynamoDB(client)
val tableDescription = dynamoDB.getTable("table name").describe().getItemCount()
Solution 11 - Database
DynamoDB now has a 'Get Live Item Count' button in the UI. Please note the production caveat if you have a large table that will consume read capacity.
Solution 12 - Database
You could use dynamodb mapper query.
PaginatedQueryList<YourModel> list = DymamoDBMapper.query(YourModel.class, queryExpression);
int count = list.size();
it calls loadAllResults()
that would lazily load next available result until allResultsLoaded.
Solution 13 - Database
len(response['Items'])
will give you the count of the filtered rows
where,
fe = Key('entity').eq('tesla')
response = table.scan(FilterExpression=fe)
Solution 14 - Database
I used scan to get total count of the required tableName.Following is a Java code snippet for same
Long totalItemCount = 0;
do{
ScanRequest req = new ScanRequest();
req.setTableName(tableName);
if(result != null){
req.setExclusiveStartKey(result.getLastEvaluatedKey());
}
result = client.scan(req);
totalItemCount += result.getItems().size();
} while(result.getLastEvaluatedKey() != null);
System.out.println("Result size: " + totalItemCount);
Solution 15 - Database
This is solution for AWS JavaScript SDK users, it is almost same for other languages.
Result.data.Count will give you what you are looking for
apigClient.getitemPost({}, body, {})
.then(function(result){
var dataoutput = result.data.Items[0];
console.log(result.data.Count);
}).catch( function(result){
});