Amazon Web Services Question posted in
The official Amazon Web Services documentation can be found here.

Amazon web services – How to Design a DynamoDB Table with Composite Primary Key for Property_ID, Category_ID, and Subcategory_ID?

enter image description here

I’m designing a DynamoDB table for storing property-related data where each property belongs to a category and subcategory.
I have three combination of primary key in sql server.

I need assistance in designing the table schema to efficiently query properties based on their Property_ID, categories_ID, and subcategories_ID.

Here are the attributes I have:

Property_Id (bigint)
Category_Id (int)
SubCategory_Id (int)

command which I have used for DynamoDB

aws dynamodb create-table 
--table-name orders_table 
--attribute-definitions AttributeName=Property_Id,AttributeType=N AttributeName=Category_Id,AttributeType=N AttributeName=country_code,AttributeType=N AttributeName=SubCategory_Id,AttributeType=N AttributeName=order_date,AttributeType=S 
--key-schema AttributeName=Property_Id,KeyType=HASH AttributeName=Category_Id,KeyType=HASH AttributeName=SubCategory_Id,KeyType=RANGE 
--provisioned-throughput ReadCapacityUnits=5,WriteCapacityUnits=5 
--global-secondary-indexes IndexName=OrderDateIndex,KeySchema=[{AttributeName=order_date,KeyType=HASH}],Projection={ProjectionType=ALL},ProvisionedThroughput={ReadCapacityUnits=5,WriteCapacityUnits=5} 
--region us-east-1

An error occurred (ValidationException) when calling the CreateTable operation: 1 validation error detected: Value ‘[KeySchemaElement(attributeName=Property_Id, keyType=HASH), KeySchemaElement(attributeName=Category_Id, keyType=HASH), KeySchemaElement(attributeName=SubCategory_Id, keyType=RANGE)]’ at ‘keySchema’ failed to satisfy constraint: Member must have length less than or equal to 2

Tags:

2

Answers


  1. 0

    DynamoDB does not make composite keys for you, you must do so on the client side. That means you must declare your partition/sort keys as strings.

    If you wanted a single composite partition key containing these 3 attributes Property_ID, categories_ID, and subcategories_ID then you would need to do the following:

    PK Property_ID categories_ID subcategories_ID
    101#201#301 101 201 301 
    aws dynamodb create-table 
--table-name orders_table 
--attribute-definitions AttributeName=PK,AttributeType=S AttributeName=order_date,AttributeType=S 
--key-schema AttributeName=PK,KeyType=HASH 
--provisioned-throughput ReadCapacityUnits=5,WriteCapacityUnits=5 
--global-secondary-indexes IndexName=OrderDateIndex,KeySchema=[{AttributeName=order_date,KeyType=HASH}],Projection={ProjectionType=ALL},ProvisionedThroughput={ReadCapacityUnits=5,WriteCapacityUnits=5} 
--region us-east-1

    Now its up to you to create the value PK for each of your items, by combining the 3 attributes you require as your compound key.

    Login or Signup to reply.
  2. 0

    In DynamoDB you can have a single property of your object designated as the partition (or hash) key and a single property designed as a sort (or range) key. This means you must choose 2 properties that will play these roles. The error is raised because you are trying to use 3 properties.

    One way you could handle this situation is to combine 2 of your 3 properties into one e.g. combine Category_Id and SubCategory_Id into a Category_Id_And_SubCategory_Id property (by joining these 2 values into a string, separated by - for example) then use this property as the sort key.

    You could also save the Category_Id and SubCategory_Id as separate fields of your object so you can access them easily, without having to do any sort of string parsing to get your ids. In the end the schema of an object in your DynamoDB database would look similar to:

    Property_Id: number
Category_Id_And_SubCategory_Id: string (of shape '2-3' where 2 is the category id and 3 is the subcategory id)
Category_Id: number
SubCategory_Id: number
...

    This kind of schema will allow you to query your database in the following way:

    1. By property id (as you can query with only the partition key)
    2. By property id and category id (as you can query the partition key by value and use a startswith condition on the sort key)
    3. By property id, category id, and subcategory id (by specifying as partition key the value and as sort key the value as well [which you can get by concatenating the category id and property id]

    If you need to query the table in different ways you can change around which values you use as keys e.g. you could use as the partition key the category id which would allow you to query all items for a category and then have the sort key be the concatenation between subcategory id and property id.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search