Fetch all Columns from Dynamo db based on GSI index and range key

Question

i have only two projections defined at the dynamo db GSI index level. but to create the expected response i need to get other columns as well from dynamo db .

Lets say there are 20 columns in my table and only two mentioned in global secondary index.How can i achieve this using GSI and loading data from master table .

Do i need to user Query Requests or another approach i think of is pull data from index and then search on primary table . This is my existing code :

    public List<DynamoDBObject> getData(String gsiHashKey) {


        DynamoDBObject dynamoDBObject= new DynamoDBObject();

        command.setgsiHashKey(gsiHashKey);

     final DynamoDBQueryExpression<DynamoDBObject> queryExpression = 
new DynamoDBQueryExpression<>();

  queryExpression.setIndexName("gsi_index_name");

      queryExpression.setHashKeyValues(dynamoDBObject);
return mapper.query(DynamoDBObject.class,queryExpression)
}

Please suggest best way to achieve this.

Nadav Har'El · Accepted Answer · 2019-07-09 07:04:55Z

2

As you noted, in GSI if you chose not to project all the base table's columns onto the index table, then these other columns are not available when querying the index. The reason for this isn't laziness by the implementators, is efficiency: In GSI, the index table is distributed throughout the DynamoDB cluster in a different way from the base table, so when reading index table data there is no efficient way to also read from the base table at the same time. By the way, this is exactly where LSI differs from GSI - in LSI the index and base tables are co-located, and can be read together, so DynamoDB does give you a way to request also unprojected columns.

So I think you are left with two options. One is to use the BatchGetItem request to read the base-table data after having read the index data. Note that when your query on the index, you can always get back the base-table key attributes, so you can use those to read the complete items from the base table. BatchGetItem is probably the most efficient way to do those reads, instead of retrieving items one by one with GetItem.

The second option is, of course, to project more base attributes - or even all of them - onto the index table. This will increase your storage and possibly read and write costs, so whether you want to do this or not depends on your application. In some specific cases it even makes sense to have two indexes of the same attribute, with different amount of projected attributes.

answered Jul 9, 2019 at 7:04

Nadav Har'El

14.1k2 gold badges36 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

divyanayan awasthi Over a year ago

Thanks. I will try the BatchGetItem Request. But one more query regarding the same as I have only one projection column in the GSI i.e let's say orderID but its not a hash key in base table .I am not sure if dynamo DB base table get operation will work, because I don't have base table hash or range key to fetch Record.is my understanding right?

Nadav Har'El Over a year ago

The base table's key attributes are always projected into the index table, whether you asked for this or not, in addition to the specific attributes you asked to project. Please check.

Nadav Har'El Over a year ago

What do you mean it works for LSI, not GSI? I just checked, created a table with hash key "p", and global secondary index with hash key "x" and projection "ProjectionType: 'INCLUDE', NonKeyAttributes: ['a', 'b']", and sure enough it wasn't just attributes a and b in the index, both p and x were there too. And since in particular "p" is there, I can read now items from the original table.

divyanayan awasthi Over a year ago

what you are saying is right...but for me, there are other columns as well and PROJECTION LEVEL is only keys .so let us say I have base table hash key "p" and columns "a", "b", "c".then if I query on GSI i.e on x, I should get p as well, but will I get other columns as well . else I think as u suggested I need to query again on the base table.

Nadav Har'El Over a year ago

Yes, please reread my answer. The GSI's query will include p, but not "a", "b" or "c". If you need those you either need to project them into the GSI (with Projection of ALL or INCLUDE those specific attributes), or, if you don't want to do that - you also need to read also from the base table (I gave a few hints how).

Collectives™ on Stack Overflow

Fetch all Columns from Dynamo db based on GSI index and range key

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related