Secondary Indexes In DynamoDB

Secondary Indexes In DynamoDB

Secondary Indexes in DynamoDB

Hello Everyone

Welcome to CloudAffaire and this is Debjeet

In the last blog post, we have discuss Primary Key in DynamoDB

In this blog post we are going to discuss Secondary Indexes in dynamodb.

Secondary Indexes in DynamoDB

You can create one or more secondary indexes on a table. A secondary index lets you query the data in the table using an alternate key, in addition to queries against the primary key. DynamoDB doesn’t require that you use indexes, but they give your applications more flexibility when querying your data. After you create a secondary index on a table, you can read data from the index in much the same way as you do from the table.

DynamoDB supports two kinds of indexes:

  • Local secondary index
  • Global secondary index

Local secondary index:

An index that has the same partition key as the table, but a different sort key.

Global secondary index:

An index with a partition key and sort key that can be different from those on the table.

Each table in DynamoDB has a limit of 20 global secondary indexes (default limit) and 5 local secondary indexes per table. Every index belongs to a table, which is called the base table for the index. DynamoDB maintains indexes automatically. When you add, update, or delete an item in the base table, DynamoDB adds, updates, or deletes the corresponding item in any indexes that belong to that table. When you create an index, you specify which attributes will be copied, or projected, from the base table to the index. At a minimum, DynamoDB projects the key attributes from the base table into the index.

Next, we are going to create both local secondary index and global secondary index in our local DynamoDB instance.

Step 1: Create a table with local secondary index

aws dynamodb create-table ^
--table-name CloudAffaire ^
--attribute-definitions AttributeName=company,AttributeType=S AttributeName=id,AttributeType=N AttributeName=country,AttributeType=S ^
--key-schema AttributeName=company,KeyType=HASH AttributeName=id,KeyType=RANGE ^
--local-secondary-indexes "[{""IndexName"": ""Local_Secondary_Index"", ""KeySchema"":[{""AttributeName"":""company"",""KeyType"":""HASH""},{""AttributeName"":""country"",""KeyType"":""RANGE""}], ""Projection"":{""ProjectionType"":""ALL""}}]" ^
--provisioned-throughput ReadCapacityUnits=1,WriteCapacityUnits=1 ^
--endpoint-url http://localhost:8000 ^
--output table

Secondary Indexes in DynamoDB

Note: Our composite primary key is on company and id. We have created the local secondary index on country. Hence we will be able to query the table using [company and id] or [company and country]. Local secondary index needs to be created at the time of table creation and cannot add once the table is created.

Step 2: Insert some data in the ClodAffaire table

aws dynamodb batch-write-item ^
--request-items file://employee.json ^
--endpoint-url http://localhost:8000

Secondary Indexes in DynamoDB

Note: You can download the employee.json file from github

https://github.com/CloudAffaire/DynamoDB

Step 3: Query the table data with company and country

aws dynamodb query ^
--table-name CloudAffaire ^
--index-name Local_Secondary_Index ^
--projection-expression "ename" ^
--key-condition-expression "company = :comp AND country = :cntry" ^
--expression-attribute-values "{"":comp"": {""S"": ""Amazon""},"":cntry"": {""S"": ""India""}}" ^
--endpoint-url http://localhost:8000 ^
--output text

Secondary Indexes in DynamoDB

Note: In order to query using index, you have to provide the index name in the query.

Next, let’s try to query on a different element (did) which is not the table partition key.

aws dynamodb query ^
--table-name CloudAffaire ^
--projection-expression "ename" ^
--key-condition-expression "did = :dptid AND country = :cntry" ^
--expression-attribute-values "{"":dptid"": {""N"": ""10""},"":cntry"": {""S"": ""India""}}" ^
--endpoint-url http://localhost:8000 ^
--output text

Secondary Indexes in DynamoDB

Note: We are unable to query the table as did is not part of partition key. To query using a non partition key, we need to create global secondary index on the table with the desired element (did in this case)

Step 4: Create a global secondary index (did and country) on existing table

aws dynamodb update-table ^
--table-name CloudAffaire ^
--attribute-definitions AttributeName=company,AttributeType=S AttributeName=id,AttributeType=N AttributeName=country,AttributeType=S AttributeName=did,AttributeType=N ^
--global-secondary-index-updates "[{""Create"":{""IndexName"":""GlobalSecondaryIndex"",""KeySchema"":[{""AttributeName"":""did"",""KeyType"":""HASH""},{""AttributeName"":""country"",""KeyType"":""RANGE""}],""Projection"":{""ProjectionType"":""ALL""},""ProvisionedThroughput"":{""ReadCapacityUnits"":1,""WriteCapacityUnits"":1}}}]" ^
--provisioned-throughput ReadCapacityUnits=2,WriteCapacityUnits=2 ^
--endpoint-url http://localhost:8000 ^
--output table

Secondary Indexes in DynamoDB

Step 5: Query the table data where did=10 and country=India

aws dynamodb query ^
--table-name CloudAffaire ^
--index-name GlobalSecondaryIndex ^
--projection-expression "ename" ^
--key-condition-expression "did = :dptid AND country = :cntry" ^
--expression-attribute-values "{"":dptid"": {""N"": ""10""},"":cntry"": {""S"": ""India""}}" ^
--endpoint-url http://localhost:8000 ^
--output text

Secondary Indexes in DynamoDB

Note: In order to query global secondary index, the index named needs to be given in the query statement.

Delete the table:

aws dynamodb delete-table --table-name CloudAffaire ^
--endpoint-url http://localhost:8000

Hope you have enjoyed this article. In the next blog post we will discuss Streams in DynamoDB

To get more details on DynamoDB, please refer below AWS documentation

https://docs.aws.amazon.com/dynamodb/index.html

 

Leave a Reply

Close Menu