Menu
Amazon DynamoDB
Developer Guide (API Version 2012-08-10)

Example: Global Secondary Indexes Using the AWS SDK for .NET Low-Level API

The following C# code example shows how to work with global secondary indexes. The example creates a table named Issues, which might be used in a simple bug tracking system for software development. The partition key is IssueId and the sort key is Title. There are three global secondary indexes on this table:

  • CreateDateIndex—the partition key is CreateDate and the sort key is IssueId. In addition to the table keys, the attributes Description and Status are projected into the index.

  • TitleIndex—the partition key is IssueId and the sort key is Title. No attributes other than the table keys are projected into the index.

  • DueDateIndex—the partition key is DueDate, and there is no sort key. All of the table attributes are projected into the index.

After the Issues table is created, the program loads the table with data representing software bug reports, and then queries the data using the global secondary indexes. Finally, the program deletes the Issues table.

For step-by-step instructions to test the following sample, see .NET Code Samples.

using System;
using System.Collections.Generic;
using System.Linq;
using Amazon.DynamoDBv2;
using Amazon.DynamoDBv2.DataModel;
using Amazon.DynamoDBv2.DocumentModel;
using Amazon.DynamoDBv2.Model;
using Amazon.Runtime;
using Amazon.SecurityToken;

namespace com.amazonaws.codesamples
    {
    class LowLevelGlobalSecondaryIndexExample
        {

        private static AmazonDynamoDBClient client = new AmazonDynamoDBClient();
        public static String tableName = "Issues";

        public static void Main(string[] args)
            {

            CreateTable();
            LoadData();

            QueryIndex("CreateDateIndex");
            QueryIndex("TitleIndex");
            QueryIndex("DueDateIndex");

            DeleteTable(tableName);

            Console.WriteLine("To continue, press enter");
            Console.Read();
            }

        private static void CreateTable()
            {

            // Attribute definitions
            var attributeDefinitions = new List<AttributeDefinition>() 
            { 
                {new AttributeDefinition {AttributeName = "IssueId", AttributeType = "S"}},
                {new AttributeDefinition {AttributeName = "Title", AttributeType = "S"}},
                {new AttributeDefinition {AttributeName = "CreateDate", AttributeType = "S"}},
                {new AttributeDefinition {AttributeName = "DueDate", AttributeType = "S"}}
            };

            // Key schema for table
            var tableKeySchema = new List<KeySchemaElement>() {
                {
                    new KeySchemaElement {
                        AttributeName= "IssueId",
                        KeyType = "HASH"  //Partition key
                    }
                },
                {
                    new KeySchemaElement {
                        AttributeName = "Title",
                        KeyType = "RANGE"  //Sort key
                    }
                }
            };

            // Initial provisioned throughput settings for the indexes
            var ptIndex = new ProvisionedThroughput
            {
                ReadCapacityUnits = 1L,
                WriteCapacityUnits = 1L
            };

            // CreateDateIndex
            var createDateIndex = new GlobalSecondaryIndex()
            {
                IndexName = "CreateDateIndex",
                ProvisionedThroughput = ptIndex,
                KeySchema = {
                    new KeySchemaElement {
                        AttributeName = "CreateDate", KeyType = "HASH"  //Partition key
                    },
                    new KeySchemaElement {
                        AttributeName = "IssueId", KeyType = "RANGE"  //Sort key
                    }
                },
                Projection = new Projection
                {
                    ProjectionType = "INCLUDE",
                    NonKeyAttributes = { "Description", "Status" }
                }
            };

            // TitleIndex
            var titleIndex = new GlobalSecondaryIndex()
            {
                IndexName = "TitleIndex",
                ProvisionedThroughput = ptIndex,
                KeySchema = {
                    new KeySchemaElement {
                        AttributeName = "Title", KeyType = "HASH"  //Partition key
                    },
                    new KeySchemaElement {
                        AttributeName = "IssueId", KeyType = "RANGE"  //Sort key
                    }
                },
                Projection = new Projection
                {
                    ProjectionType = "KEYS_ONLY"
                }
            };

            // DueDateIndex
            var dueDateIndex = new GlobalSecondaryIndex()
            {
                IndexName = "DueDateIndex",
                ProvisionedThroughput = ptIndex,
                KeySchema = {
                    new KeySchemaElement {
                        AttributeName = "DueDate", 
                        KeyType = "HASH"  //Partition key
                    }
                },
                Projection = new Projection
                {
                    ProjectionType = "ALL"
                }
            };



            var createTableRequest = new CreateTableRequest
            {
                TableName = tableName,
                ProvisionedThroughput = new ProvisionedThroughput
                {
                    ReadCapacityUnits = (long)1,
                    WriteCapacityUnits = (long)1
                },
                AttributeDefinitions = attributeDefinitions,
                KeySchema = tableKeySchema,
                GlobalSecondaryIndexes = { createDateIndex, titleIndex, dueDateIndex }
            };

            Console.WriteLine("Creating table " + tableName + "...");
            client.CreateTable(createTableRequest);

            WaitUntilTableReady(tableName);

            }

        private static void LoadData()
            {

            Console.WriteLine("Loading data into table " + tableName + "...");

            // IssueId, Title, 
            // Description,
            // CreateDate, LastUpdateDate, DueDate,
            // Priority, Status

            putItem("A-101", "Compilation error",
            "Can't compile Project X - bad version number. What does this mean?",
            "2013-11-01", "2013-11-02", "2013-11-10",
            1, "Assigned");

            putItem("A-102", "Can't read data file",
                "The main data file is missing, or the permissions are incorrect",
                "2013-11-01", "2013-11-04", "2013-11-30",
                2, "In progress");

            putItem("A-103", "Test failure",
                "Functional test of Project X produces errors",
                "2013-11-01", "2013-11-02", "2013-11-10",
                1, "In progress");

            putItem("A-104", "Compilation error",
                "Variable 'messageCount' was not initialized.",
                "2013-11-15", "2013-11-16", "2013-11-30",
                3, "Assigned");

            putItem("A-105", "Network issue",
                "Can't ping IP address 127.0.0.1. Please fix this.",
                "2013-11-15", "2013-11-16", "2013-11-19",
                5, "Assigned");

            }

        private static void putItem(
            String issueId, String title,
            String description,
            String createDate, String lastUpdateDate, String dueDate,
            Int32 priority, String status)
            {

            Dictionary<String, AttributeValue> item = new Dictionary<string, AttributeValue>();

            item.Add("IssueId", new AttributeValue { S = issueId });
            item.Add("Title", new AttributeValue { S = title });
            item.Add("Description", new AttributeValue { S = description });
            item.Add("CreateDate", new AttributeValue { S = createDate });
            item.Add("LastUpdateDate", new AttributeValue { S = lastUpdateDate });
            item.Add("DueDate", new AttributeValue { S = dueDate });
            item.Add("Priority", new AttributeValue { N = priority.ToString() });
            item.Add("Status", new AttributeValue { S = status });

            try
                {

                client.PutItem(new PutItemRequest
                {
                    TableName = tableName,
                    Item = item
                });

                }
            catch (Exception e)
                {
                Console.WriteLine(e.ToString());
                }
            }

        private static void QueryIndex(string indexName)
            {
            Console.WriteLine
     ("\n***********************************************************\n");
            Console.WriteLine("Querying index " + indexName + "...");

            QueryRequest queryRequest = new QueryRequest
            {
                TableName = tableName,
                IndexName = indexName,
                ScanIndexForward = true
            };


            String keyConditionExpression;
            Dictionary<string, AttributeValue> expressionAttributeValues = new Dictionary<string, AttributeValue>();
 
            if (indexName == "CreateDateIndex")
                {
                Console.WriteLine("Issues filed on 2013-11-01\n");
                
                keyConditionExpression = "CreateDate = :v_date and begins_with(IssueId, :v_issue)";
                expressionAttributeValues.Add(":v_date", new AttributeValue { S = "2013-11-01" });
                expressionAttributeValues.Add(":v_issue", new AttributeValue { S = "A-" });

                }
            else if (indexName == "TitleIndex")
                {
                Console.WriteLine("Compilation errors\n");

                keyConditionExpression = "Title = :v_title and begins_with(IssueId, :v_issue)";
                expressionAttributeValues.Add(":v_title", new AttributeValue { S = "Compilation error" });
                expressionAttributeValues.Add(":v_issue", new AttributeValue { S = "A-" });

                // Select
                queryRequest.Select = "ALL_PROJECTED_ATTRIBUTES";

                }
            else if (indexName == "DueDateIndex")
                {
                Console.WriteLine("Items that are due on 2013-11-30\n");

                keyConditionExpression = "DueDate = :v_date";
                expressionAttributeValues.Add(":v_date", new AttributeValue { S = "2013-11-30" });

                // Select
                queryRequest.Select = "ALL_PROJECTED_ATTRIBUTES";

                }
            else
                {
                Console.WriteLine("\nNo valid index name provided");
                return;
                }

            queryRequest.KeyConditionExpression = keyConditionExpression;
            queryRequest.ExpressionAttributeValues = expressionAttributeValues;

            var result = client.Query(queryRequest);
            var items = result.Items;
            foreach (var currentItem in items)
                {
                foreach (string attr in currentItem.Keys)
                    {
                    if (attr == "Priority")
                        {
                        Console.WriteLine(attr + "---> " + currentItem[attr].N);
                        }
                    else
                        {
                        Console.WriteLine(attr + "---> " + currentItem[attr].S);

                        }
                    }
                Console.WriteLine();
                }


            }

        private static void DeleteTable(string tableName)
            {
            Console.WriteLine("Deleting table " + tableName + "...");
            client.DeleteTable(new DeleteTableRequest { TableName = tableName });
            WaitForTableToBeDeleted(tableName);
            }

        private static void WaitUntilTableReady(string tableName)
            {
            string status = null;
            // Let us wait until table is created. Call DescribeTable.
            do
                {
                System.Threading.Thread.Sleep(5000); // Wait 5 seconds.
                try
                    {
                    var res = client.DescribeTable(new DescribeTableRequest
                    {
                        TableName = tableName
                    });

                    Console.WriteLine("Table name: {0}, status: {1}",
                      res.Table.TableName,
                      res.Table.TableStatus);
                    status = res.Table.TableStatus;
                    }
                catch (ResourceNotFoundException)
                    {
                    // DescribeTable is eventually consistent. So you might
                    // get resource not found. So we handle the potential exception.
                    }
                } while (status != "ACTIVE");
            }

        private static void WaitForTableToBeDeleted(string tableName)
            {
            bool tablePresent = true;

            while (tablePresent)
                {
                System.Threading.Thread.Sleep(5000); // Wait 5 seconds.
                try
                    {
                    var res = client.DescribeTable(new DescribeTableRequest
                    {
                        TableName = tableName
                    });

                    Console.WriteLine("Table name: {0}, status: {1}",
                      res.Table.TableName,
                      res.Table.TableStatus);
                    }
                catch (ResourceNotFoundException)
                    {
                    tablePresent = false;
                    }
                }
            }

        }
    }