Categories
DynamoDB tutorial

DynamoDB table Index – Global and local

DynamoDB provides an option to either scan or query your items in the table. We can scan all the attributes in the table but query are only available on the primary key. The primary key is a partition key or a combination of the partition key and a sort key. The scan is not efficient as query, and what if we need to query other fields efficiently. For this case, we can add an index to the DynamoDB table. In this tutorial, we will be covering all the essentials for the DynamoDB table index.

Based on the data access pattern of your application we can add an index on the DynamoDB table. DynamoDB index is generally called a secondary index. They are called secondary because the primary data access is done based on the primary key. These index help access the data efficiently based on the application need.

A secondary index can be created or associated with a base table.

There are two types of indexes in DynamoDB:

  • Global Secondary index
  • Local Secondary index

DynamoDB Global Secondary index

Global secondary index(GSI) can be created in any table. GSI can have a different partition key than that of the base table. Since it does not use the same partition it is called a global secondary index. GSI can be created at any time, event after a table is created without the need to have read/write interruption to the table.

DynamoDB Local Secondary index

Local secondary index(LSI) also can be created on any table. These indexes can only be created at the creation time of the table because the local secondary index needs to be defined on the same partition key. While creating a table we specify the partition key for the table. We also can define a sort key. In cases where we want to have alternative value to search the table with we need to define a local secondary index.

Difference between DynamoDB local and global secondary indexes

LSI

  • Needs to be created at the time of table creation
  • Need to be specified along with the partition key
  • Uses the same provisioned throughput of the base table for read/.write
  • You can choose between eventual/string consistency for read
  • You can get all the attributes in the table while querying LSI
  • LSI can only be compound and need to be specified along with the partition key
  • Cannot delete this index

GSI

  • can be created at any time, without interruption
  • Can be specified independent of the partition key
  • Need to specify separate provisioned throughput and scales independently
  • Read consistency is always eventual
  • You can only get the attributes that are projected while the index was created.
  • The GSI can be single or composite index
  • Can be deleted

An index can be created using one of the following ways:

  • AWS console UI
  • AWS CLI
  • AWS SDK
  • AWS Cloudformation – We will look at this in chapters ahead.

AWS console UI

Adding an index can be done from the AWS console. For the local secondary index, we need to add them during the time of creation. In the case of the global secondary index, we can add it to an existing table.

Let us take a look at the existing table books by adding a Global secondary index:

Global Secondary index

DynamoDB Global secondary index
GSI

Here we can add the attribute of the primary key for the GSI. In the above case, we are adding attribute Title. We can select either of the one bellows for the projected attribute:

  • All – The GSI will contain all the attributes and will be returned during the query.
  • Keys – Only the primary key/sort key will be projected
  • Include – Attributes specified will only be projected.

While creating the index we can specify a separate read/write capacity. The index will not use the capacity of its base table. Also, autoscaling configurations can be separately specified.

Local Secondary Index

An LSI can only be created at the time of table creation.

While creating a table, unselect the ‘Use Default settings’. This will provide the option to create a secondary index:

In the secondary index create add index. This will provide an option to create an index at the time of table creation. The following option will help you create a secondary index:

DynamoDB local secondary index
LSI

Here we can specify the Primary key. Note that the primary key must be as same as the base table which in our case is the Author. Select Add sort key option, and add the name of the attribute you want to create the index for. After that select the Create as Local Secondary Index option. This will create a local secondary index for the table. Once a local secondary index is created it cannot be removed.

AWS CLI

DynamoDB table index can be created using AWS CLI as well. Let us take a look at some examples:

We can create a local secondary index at the time of creation. So let us create a table with LSI included:

aws dynamodb create-table \
     --table-name Book \
     --attribute-definitions \
         AttributeName=Author,AttributeType=S \
         AttributeName=Category,AttributeType=S \
     --key-schema AttributeName=Author,KeyType=HASH AttributeName=Category,KeyType=RANGE \
     --provisioned-throughput ReadCapacityUnits=5,WriteCapacityUnits=5
     --local-secondary-indexes \
       IndexName=TitleIndex, \
       KeySchema=[{AttributeName=Author,KeyType=HASH}, \
                 {AttributeName=Title,KeyType=RANGE}], \
       Projection={ProjectionType=ALL}

To create an LSI, add an option –local-secondary-indexes with IndexName the name of the index, KeySchema an array of objects with a maximum of 5 items. The KeySchema object should contain AttributeName(name of the attribute to add index) and KeyType(The type of index). For LSI you will need to make sure that the HASH key type is a partition key and the attribute you want to add is a RANGE.

While defining ProjectionType include you will need to define the NonKeyAttributes as the values you want to project to the index.

Note that while using include projection the query you run will need to go to the main table to get the attribute that is not projected in the local secondary index.

AWS SDK

The third option to create a Dynamodb table with a secondary index to use an AWS SDK. In this tutorial, we will be using Golang to create a DynamoDB table index.

package main

import (
    "fmt"
    "github.com/aws/aws-sdk-go/service/dynamodb"
)

func main() {
    // AWS session
    mySession := session.Must(session.NewSession())
    // A DynamoDB client
    svc := dynamodb.New(mySession)
    // Create a table
    attr := dynamodb.AttributeDefinition{
        "AttributeName": "Author",
        "AttributeType": "S",
    }
    attributeDefinitions := []dynamodb.AttributeDefinition{attr}
    schema := dynamodb.KeySchemaElement{
        "AttributeName": "Author",
        "KeyType": "HASH",
    }
    ischema := dynamodb.KeySchemaElement{
        "AttributeName": "Title", 
        "KeyType": "RANGE",
    }
    keySchema := []dynamodb.KeySchemaElement{&schema}
    iKeySchemas := []dynamodb.KeySchemaElement{&schema, &ischema}
    index := dynamodb.LocalSecondaryIndex{
        IndexName: "TitleIndex",
        KeySchema: iKeySchemas,
    }
    indexes := []dynamodb.LocalSecondaryIndex{&index}
    input := dynamodb.CreateTableInput{
        AttributeDefinitions: &attributeDefinitions,
        KeySchema: &keySchema,
        LocalSecondaryIndexes: &indexes,
    }
    out, err := svc.CreateTable(&input)
    if err != nil { 
        fmt.Println(err)
    }
    fmt.Println(out)
}

Querying an index in DynamoDB

Similar to querying a table you can query a DynamoDB secondary index. Unlike other databases where the database automatically selects the index for you, in DynamoDB you need to specify what index you want to query the data from.

Let us look at an example of querying the index Title from the books table using AWS CLI:

aws dynamodb query \
--table-name books \
--index-name Title-index \
--key-condition-expression "Title = :title" \
--expression-attribute-values '{":title":{"S":"Power of habbit"}}'

This is similar to querying a table, you just need to specify what index you want to use. Here we are specifying the index name as Title-index to query the data.

The query index can be done with the AWS management console as well as any AWS SDK as well.

Leave a Reply

Your email address will not be published. Required fields are marked *