Query Dynamo table with only the secondary global index

Question

Im trying to query a Dynamodb table using a secondary global index and I'm getting java.lang.IllegalArgumentException: Illegal query expression: No hash key condition is found in the query. All I'm trying to do is to get all items that have a timestamp greater than a value without considering the key. The timestamp is not part of a key or range key, so i created a global index for it.

Does anyone have a clue what i might be missing?

Table Definition:

{
   AttributeDefinitions:[
      {
         AttributeName:timestamp,
         AttributeType:N
      },
      {
         AttributeName:url,
         AttributeType:S
      }
   ],
   TableName:SitePageIndexed,
   KeySchema:[
      {
         AttributeName:url,
         KeyType:HASH
      }
   ],
   TableStatus:ACTIVE,
   CreationDateTime:   Mon May 12 18:45:57   EDT 2014,
   ProvisionedThroughput:{
      NumberOfDecreasesToday:0,
      ReadCapacityUnits:8,
      WriteCapacityUnits:4
   },
   TableSizeBytes:0,
   ItemCount:0,
   GlobalSecondaryIndexes:[
      {
         IndexName:TimestampIndex,
         KeySchema:[
            {
               AttributeName:timestamp,
               KeyType:HASH
            }
         ],
         Projection:{
            ProjectionType:ALL,

         },
         IndexStatus:ACTIVE,
         ProvisionedThroughput:{
            NumberOfDecreasesToday:0,
            ReadCapacityUnits:8,
            WriteCapacityUnits:4
         },
         IndexSizeBytes:0,
         ItemCount:0
      }
   ]
}

Code

Condition condition1 = new Condition().withComparisonOperator(ComparisonOperator.GE).withAttributeValueList(new AttributeValue().withN(Long.toString(start)));      
DynamoDBQueryExpression<SitePageIndexed> exp = new DynamoDBQueryExpression<SitePageIndexed>().withRangeKeyCondition("timestamp", condition1);
exp.setScanIndexForward(true);
exp.setLimit(100);
exp.setIndexName("TimestampIndex");

PaginatedQueryList<SitePageIndexed> queryList = client.query(SitePageIndexed.class,exp);

score 8 · Accepted Answer · answered May 13 '14 at 06:18

All I'm trying to do is to get all items that have a timestamp greater than a value without considering the key.

This is not how Global Secondary Indexes (GSI) on Amazon DynamoDB work. To query a GSI you must specify a value for its hash key and then you may filter/sort by the range key -- just like you'd do with the primary key. This is exactly what the exception is trying to tell you, and also what you will find on the documentation page for the Query API:

A Query operation directly accesses items from a table using the table primary key, or from an index using the index key. You must provide a specific hash key value.

Think of a GSI as just another key that behaves almost exactly like the primary key (the main differences being that it is updated asynchronously, and you can only perform eventually consistent reads on GSIs).

Please refer to the Amazon DynamoDB Global Secondary Index documentation page for guidelines and best practices when creating GSIs: http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/GSI.html

One possible way to achieve what you want would be to have a dummy attribute constrained to a finite, small set of possible values, create a GSI with hash key on that dummy attribute and range key on your timestamp. When querying, you would need to issue one Query API call for each possible value on your dummy hash key attribute, and then consolidate the results on your application. By constraining the dummy attribute to a singleton (i.e., a Set with a single element, i.e., a constant value), you can send only one Query API call and you get your result dataset directly -- but keep in mind that this will cause you problems related to hot partitions and you might have performance issues! Again, refer to the document linked above to learn the best practices and some patterns.

so eventually NoSql Sucks , considering how easy this would be with Sql — vishal dharankar, Dec 28 '16 at 16:34
@vishal - like every other technology, there are trade offs. For certain types of applications, I would totally agree with you, "NoSQL sucks". For others, I would definitely say "SQL sucks". It's just a matter of having a big repertoire of technologies you know about and being able to chose the right one for the job. Unfortunately, so far humanity has not been capable of creating one single database technology that has all the "pros" and none of the "cons" of all the others... — Bruno Reis, Dec 28 '16 at 22:39
NoSQL doesn't suck generally but imho dynamodb does, A LOT. In mongodb for instance this would be trivial. — Samantha Atkins, Sep 18 '19 at 23:12

score 6 · Answer 2 · edited May 23 '17 at 11:46

It is possible to query DynamoDb with only the GSI; could be confirmed by going to the web interaface Query/Index.

Programatically the way it is done is as following:

DynamoDB dynamoDB = new DynamoDB(new AmazonDynamoDBClient(
    new ProfileCredentialsProvider()));

Table table = dynamoDB.getTable("WeatherData");
Index index = table.getIndex("PrecipIndex");

QuerySpec spec = new QuerySpec()
    .withKeyConditionExpression("#d = :v_date and Precipitation = :v_precip")
    .withNameMap(new NameMap()
        .with("#d", "Date"))
    .withValueMap(new ValueMap()
        .withString(":v_date","2013-08-10")
        .withNumber(":v_precip",0));

ItemCollection<QueryOutcome> items = index.query(spec);
Iterator<Item> iter = items.iterator(); 
while (iter.hasNext()) {
    System.out.println(iter.next().toJSONPretty());
}

http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/GSIJavaDocumentAPI.html#GSIJavaDocumentAPI.QueryAnIndex

For doing it with DynamoDBMapper see: How to query a Dynamo DB having a GSI with only hashKeys using DynamoDBMapper

score 2 · Answer 3 · answered Dec 02 '20 at 10:49

Here is how you can query in java with only GSI

Map<String, AttributeValue> eav = new HashMap<String, AttributeValue>();
        eav.put(":val1", new AttributeValue().withS("PROCESSED"));

DynamoDBQueryExpression<Package> queryExpression = new DynamoDBQueryExpression<Package>()
                .withIndexName("<your globalsecondaryindex key name>")
                .withKeyConditionExpression("your_gsi_column_name= :val1").
                withExpressionAttributeValues(eav).withConsistentRead(false).withLimit(2);

QueryResultPage<T> scanPage = dbMapper.queryPage(T.class, queryExpression);

score 1 · Answer 4 · answered Jan 09 '17 at 13:43

1

While this is not the correct answer per say, could you possible accomplish this with a scan vs. a query? It's much more expensive, but could be a solution.

answered Jan 09 '17 at 13:43

James Parker

2,095
3
27
48

Query Dynamo table with only the secondary global index

4 Answers4

Linked