2

I have a table in DynamoDB and have partition key and a sort key. For example, the table structure is as follows.

// Primary Keys
id -> PK
name -> SK
Info -> json Formatted Attributes

So let's say there are two items in the table,

// Item 1
{
    id: 10245-25-45,
    name: "Name 1",
    info: {
        userId: 1012, // PK
        userName: "ABC", // SK
        type: "type 1",
        date: "2020-01-03 04:05:12"
    }
}

// Item 2
{
    id: 10245-65-70, // pK
    name: "Name 2", // SK
    info: {
        userId: 1013,
        userName: "ABCD",
        type: "type 2",
        date: "2020-01-03 04:10:12"
    }
}

I am using Java as the language and using DynamoDBMapper to do CRUD operations. So I know I can use DynamoDBMapper.load() to get one item by providing PK and SK, but I was wondering how to get data by searching non-key attributes. For example, I need to get One Item at a time by providing,

// userId, userName, type, data are unique values so it will give only one record if exists
find item by userId;
find item by userId and userName;
find item by type and date;

So above are some of my access patterns and basically, I need to get data without providing PK and SK and also I can't use global secondary indexes. So I know there is Query. but I am wondering without that is there any way to do what I need? I really appreciate it if anybody can help me. Thanks in advance.

Abraham Arnold
  • 301
  • 4
  • 20
  • 1
    You can't, that is not how dynamodb works, at least not with your current setup. You can add GSIs and LSIs to support those queries, read up on them. Other than that you can only get ALL data and filter in java, which is a VERY bad idea. – luk2302 Jan 04 '21 at 10:02
  • @luk2302 Yes I got it. So there are no good way to do that right? Always need to Query and then use FilterExpression? – Abraham Arnold Jan 04 '21 at 16:29
  • 1
    You are basically running a full table scan every time, at least you will pay for a full table scan - a filter expression runs after retrieving the data from the dynamo. `find item by userId` is not doable with that table structure and without additional indices - without doing a full table scan for every query. – luk2302 Jan 04 '21 at 16:32
  • 1
    To be clear: this is a very bad idea from a performance perspective AND from a billing perspective. – luk2302 Jan 04 '21 at 16:34
  • @luk2302 Thank you very much for the information. Is dynamo loads all the results to our server memory and then filter or dynamo loads and filter in their way and provide us the final results? – Abraham Arnold Jan 04 '21 at 17:05
  • 1
    https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Query.html it is filtered on AWS' side, but you still pay for the retrieval of the data from the dynamodb storage. Basically dynamo executes the query, retrieves all the data, then filters it in memory and then sends the result to you. – luk2302 Jan 04 '21 at 17:06
  • @luk2302 I got it. Thank you very much for these valuable information and for your time also. I really appreciate that. – Abraham Arnold Jan 05 '21 at 09:01

1 Answers1

1

This is not possible to do in dynamoDB , because of the distributed nature of the data and a full table scan negates the use of this storage option .

if data needs to be searchable a search DB should be used , if the amount of data is small then a SQL type DB can be used.

for a robust solution I would use elastic search (AWS has a managed solution) , in elastic populate only the fields that are searchable , in your case userID, name, date etc and store the id of the document in dynamoDB

so a search API , would query elastic retrieve the item id's , and then retrieve the item from dynamoDB

Shachaf.Gortler
  • 5,655
  • 14
  • 43
  • 71