How to query dynamo db without primary or sort key?

user_10075643 0
Viewed 5199

I want to query a dynamo db table without a Primary partition key Primary sort key

Those are in my table

Primary partition key   userid (String)
Primary sort key    jobcodeid (String)

This table also contain a key jobid.

So i want to make a query which looks like this.

  var opts  = {
          'ConsistentRead': true,
          TableName : 'interviews',
          KeyConditionExpression: "jobid = :jobid",
          ExpressionAttributeValues: { ":jobid": {"S":jobid} },
        }

I am using this code 

  dynamodb.query(opts, function(err, data) {
  if(err){
   //somthing
   }else{
   //Somthing
   }
 }

But the above query returning an error like this.

ValidationException: Query condition missed key schema element

How can I execute this query?

amazon-dynamodb node.js amazon-web-services javascript
1 Answers

This is a very popular question, with over 5000 people having read it, so I think it's time that it has an answer for the benefit of future readers - and not just comments.

DynamoDB has two distinct operations: Scan and Query. Scan scans the entire table, whereas Query can efficiently narrow down on one specific partition key (it can still return a huge number of items, because there can be a huge number of items with the same partition key).

Because you don't have a specific partition key you want to scan (jobid is your sort key, not your partition key), you'll need to use the Scan operation and read the entire table.

This also means that the operation will be very expensive: A Scan reads, and charges you for reading, every single item in your database. Even if you pass a FilterExpression and eliminate most of the items as non interesting, DynamoDB still reads each one and filters it - and therefore charges you for it.

One of the reasons why this scan will be expensive is that the Scan operation does not support the KeyConditionExpression option that a Query has which could have, in some cases, reduced the cost of this scan: Your query involves looking for a specific sort key (jobid = :jobid) in each partition. If partitions are each fairly long, DynamoDB could have looked up jobid = :jobid in each partition without reading the entire partition - and not charge you for reading the entire partition.

Finally, a general piece of advice: The key to designing applications on DynamoDB is to first consider the types of queries you have, and then design the data layout to make these queries efficient. In your case, it seems the data model is inappropriate for this type of queries - so if they are common, you should probably rethink your data layout. For example making jobid a partition key, or adding a GSI for jobid, or other changes depending on what your data actually has and what other queries you need to do on it.

user_88912240
Related