CODEWITHSUNDEEP
javascriptnode.jsamazon-web-servicesamazon-dynamodb
Arun VM
Arun VM

Reputation: 447

How to query dynamo db without primary or sort key?

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?

Upvotes: 5

Views: 7350

Answers (2)

Nadav Har'El
Nadav Har'El

Reputation: 13801

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.

Upvotes: 2

RJA
RJA

Reputation: 482

I know a little late to the races, but will add this anyways.

You'd want to create a Global Secondary Index. This will allow you to do queries based off of any item attribute.

Scans do not scale well, and as your table grows...it will greatly increase your back end's fetch time as it goes through every record.

GSI's create a secondary 'lookup table' which operates much faster. The link below will get you started.

https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/GSI.html

Upvotes: 0

Related Questions