# Using Global Secondary Indexes in DynamoDB
Some applications might need to perform many kinds of queries, using a variety of different attributes as query criteria. To support these requirements, you can create one or more* global secondary indexes* and issue `Query` requests against these indexes in Amazon DynamoDB.
**Topics**
+ [Scenario: Using a Global Secondary Index](#GSI.scenario)
+ [Attribute projections](#GSI.Projections)
+ [Multi-attribute key schema](#GSI.MultiAttributeKeys)
+ [Reading data from a Global Secondary Index](#GSI.Reading)
+ [Data synchronization between tables and Global Secondary Indexes](#GSI.Writes)
+ [Table classes with Global Secondary Index](#GSI.tableclasses)
+ [Provisioned throughput considerations for Global Secondary Indexes](#GSI.ThroughputConsiderations)
+ [Storage considerations for Global Secondary Indexes](#GSI.StorageConsiderations)
+ [Design patterns](GSI.DesignPatterns.md)
+ [Managing Global Secondary Indexes in DynamoDB](GSI.OnlineOps.md)
+ [Detecting and correcting index key violations in DynamoDB](GSI.OnlineOps.ViolationDetection.md)
+ [Working with Global Secondary Indexes: Java](GSIJavaDocumentAPI.md)
+ [Working with Global Secondary Indexes: .NET](GSILowLevelDotNet.md)
+ [Working with Global Secondary Indexes in DynamoDB using AWS CLI](GCICli.md)
## Scenario: Using a Global Secondary Index
To illustrate, consider a table named `GameScores` that tracks users and scores for a mobile gaming application. Each item in `GameScores` is identified by a partition key (`UserId`) and a sort key (`GameTitle`). The following diagram shows how the items in the table would be organized. (Not all of the attributes are shown.)
![\[GameScores table containing a list of user id, title, score, date, and wins/losses.\]](http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/images/GSI_01.png)
Now suppose that you wanted to write a leaderboard application to display top scores for each game. A query that specified the key attributes (`UserId` and `GameTitle`) would be very efficient. However, if the application needed to retrieve data from `GameScores` based on `GameTitle` only, it would need to use a `Scan` operation. As more items are added to the table, scans of all the data would become slow and inefficient. This makes it difficult to answer questions such as the following:
+ What is the top score ever recorded for the game Meteor Blasters?
+ Which user had the highest score for Galaxy Invaders?
+ What was the highest ratio of wins vs. losses?
To speed up queries on non-key attributes, you can create a global secondary index. A global secondary index contains a selection of attributes from the base table, but they are organized by a primary key that is different from that of the table. The index key does not need to have any of the key attributes from the table. It doesn't even need to have the same key schema as a table.
For example, you could create a global secondary index named `GameTitleIndex`, with a partition key of `GameTitle` and a sort key of `TopScore`. The base table's primary key attributes are always projected into an index, so the `UserId` attribute is also present. The following diagram shows what `GameTitleIndex` index would look like.
![\[GameTitleIndex table containing a list of titles, scores, and user ids.\]](http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/images/GSI_02.png)
Now you can query `GameTitleIndex` and easily obtain the scores for Meteor Blasters. The results are ordered by the sort key values, `TopScore`. If you set the `ScanIndexForward` parameter to false, the results are returned in descending order, so the highest score is returned first.
Every global secondary index must have a partition key, and can have an optional sort key. The index key schema can be different from the base table schema. You could have a table with a simple primary key (partition key), and create a global secondary index with a composite primary key (partition key and sort key)—or vice versa. The index key attributes can consist of any top-level `String`, `Number`, or `Binary` attributes from the base table. Other scalar types, document types, and set types are not allowed.
You can project other base table attributes into the index if you want. When you query the index, DynamoDB can retrieve these projected attributes efficiently. However, global secondary index queries cannot fetch attributes from the base table. For example, if you query `GameTitleIndex` as shown in the previous diagram, the query could not access any non-key attributes other than `TopScore` (although the key attributes `GameTitle` and `UserId` would automatically be projected).
In a DynamoDB table, each key value must be unique. However, the key values in a global secondary index do not need to be unique. To illustrate, suppose that a game named Comet Quest is especially difficult, with many new users trying but failing to get a score above zero. The following is some data that could represent this.
****
| UserId | GameTitle | TopScore |
| --- | --- | --- |
| 123 | Comet Quest | 0 |
| 201 | Comet Quest | 0 |
| 301 | Comet Quest | 0 |
When this data is added to the `GameScores` table, DynamoDB propagates it to `GameTitleIndex`. If we then query the index using Comet Quest for `GameTitle` and 0 for `TopScore`, the following data is returned.
![\[Table containing a list of titles, top scores, and user ids.\]](http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/images/GSI_05.png)
Only the items with the specified key values appear in the response. Within that set of data, the items are in no particular order.
A global secondary index only tracks data items where its key attributes actually exist. For example, suppose that you added another new item to the `GameScores` table, but only provided the required primary key attributes.
****
| UserId | GameTitle |
| --- | --- |
| 400 | Comet Quest |
Because you didn't specify the `TopScore` attribute, DynamoDB would not propagate this item to `GameTitleIndex`. Thus, if you queried `GameScores` for all the Comet Quest items, you would get the following four items.
![\[Table containing a list of 4 titles, top scores, and user ids.\]](http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/images/GSI_04.png)
A similar query on `GameTitleIndex` would still return three items, rather than four. This is because the item with the nonexistent `TopScore` is not propagated to the index.
![\[Table containing a list of 3 titles, top scores, and user ids.\]](http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/images/GSI_05.png)
## Attribute projections
A *projection* is the set of attributes that is copied from a table into a secondary index. The partition key and sort key of the table are always projected into the index; you can project other attributes to support your application's query requirements. When you query an index, Amazon DynamoDB can access any attribute in the projection as if those attributes were in a table of their own.
When you create a secondary index, you need to specify the attributes that will be projected into the index. DynamoDB provides three different options for this:
+ *KEYS\$1ONLY* – Each item in the index consists only of the table partition key and sort key values, plus the index key values. The `KEYS_ONLY` option results in the smallest possible secondary index.
+ *INCLUDE* – In addition to the attributes described in `KEYS_ONLY`, the secondary index will include other non-key attributes that you specify.
+ *ALL* – The secondary index includes all of the attributes from the source table. Because all of the table data is duplicated in the index, an `ALL` projection results in the largest possible secondary index.
In the previous diagram, `GameTitleIndex` has only one projected attribute: `UserId`. So while an application can efficiently determine the `UserId` of the top scorers for each game using `GameTitle` and `TopScore` in queries, it can't efficiently determine the highest ratio of wins vs. losses for the top scorers. To do so, the application would have to perform an additional query on the base table to fetch the wins and losses for each of the top scorers. A more efficient way to support queries on this data would be to project these attributes from the base table into the global secondary index, as shown in this diagram.
![\[Depiction of projecting non-key attributes into a GSI to support efficient querying.\]](http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/images/GSI_06.png)
Because the non-key attributes `Wins` and `Losses` are projected into the index, an application can determine the wins vs. losses ratio for any game, or for any combination of game and user ID.
When you choose the attributes to project into a global secondary index, you must consider the tradeoff between provisioned throughput costs and storage costs:
+ If you need to access just a few attributes with the lowest possible latency, consider projecting only those attributes into a global secondary index. The smaller the index, the less that it costs to store it, and the less your write costs are.
+ If your application frequently accesses some non-key attributes, you should consider projecting those attributes into a global secondary index. The additional storage costs for the global secondary index offset the cost of performing frequent table scans.
+ If you need to access most of the non-key attributes on a frequent basis, you can project these attributes—or even the entire base table— into a global secondary index. This gives you maximum flexibility. However, your storage cost would increase, or even double.
+ If your application needs to query a table infrequently, but must perform many writes or updates against the data in the table, consider projecting `KEYS_ONLY`. The global secondary index would be of minimal size, but would still be available when needed for query activity.
## Multi-attribute key schema
Global Secondary Indexes support multi-attribute keys, allowing you to compose partition keys and sort keys from multiple attributes. With multi-attribute keys, you can create a partition key from up to four attributes and a sort key from up to four attributes, for a total of up to eight attributes per key schema.
Multi-attribute keys simplify your data model by eliminating the need to manually concatenate attributes into synthetic keys. Instead of creating composite strings like `TOURNAMENT#WINTER2024#REGION#NA-EAST`, you can use the natural attributes from your domain model directly. DynamoDB handles the composite key logic automatically, hashing multiple partition key attributes together for data distribution and maintaining hierarchical sort order across multiple sort key attributes.
For example, consider a gaming tournament system where you want to organize matches by tournament and region. With multi-attribute keys, you can define your partition key as two separate attributes: `tournamentId` and `region`. Similarly, you can define your sort key using multiple attributes like `round`, `bracket`, and `matchId` to create a natural hierarchy. This approach keeps your data typed and your code clean, without string manipulation or parsing.
When you query a global secondary index with multi-attribute keys, you must specify all partition key attributes using equality conditions. For sort key attributes, you can query them left-to-right in the order they're defined in the key schema. This means you can query the first sort key attribute alone, the first two attributes together, or all attributes together, but you cannot skip attributes in the middle. Inequality conditions such as `>`, `<`, `BETWEEN`, or `begins_with()` must be the last condition in your query.
Multi-attribute keys work particularly well when creating global secondary indexes on existing tables. You can use attributes that already exist in your table without backfilling synthetic keys across your data. This makes it straightforward to add new query patterns to your application by creating indexes that reorganize your data using different attribute combinations.
Each attribute in a multi-attribute key can have its own data type: `String` (S), `Number` (N), or `Binary` (B). When choosing data types, consider that `Number` attributes sort numerically without requiring zero-padding, while `String` attributes sort lexicographically. For example, if you use a `Number` type for a score attribute, the values 5, 50, 500, and 1000 sort in natural numeric order. The same values as `String` type would sort as "1000", "5", "50", "500" unless you pad them with leading zeros.
When designing multi-attribute keys, order your attributes from most general to most specific. For partition keys, combine attributes that are always queried together and that provide good data distribution. For sort keys, place frequently queried attributes first in the hierarchy to maximize query flexibility. This ordering allows you to query at any level of granularity that matches your access patterns.
See the [Multi-attribute keys](GSI.DesignPattern.MultiAttributeKeys.md) for implementation examples.
## Reading data from a Global Secondary Index
You can retrieve items from a global secondary index using the `Query` and `Scan` operations. The `GetItem` and `BatchGetItem` operations can't be used on a global secondary index.
### Querying a Global Secondary Index
You can use the `Query` operation to access one or more items in a global secondary index. The query must specify the name of the base table and the name of the index that you want to use, the attributes to be returned in the query results, and any query conditions that you want to apply. DynamoDB can return the results in ascending or descending order.
Consider the following data returned from a `Query` that requests gaming data for a leaderboard application.
```
{
"TableName": "GameScores",
"IndexName": "GameTitleIndex",
"KeyConditionExpression": "GameTitle = :v_title",
"ExpressionAttributeValues": {
":v_title": {"S": "Meteor Blasters"}
},
"ProjectionExpression": "UserId, TopScore",
"ScanIndexForward": false
}
```
In this query:
+ DynamoDB accesses *GameTitleIndex*, using the *GameTitle* partition key to locate the index items for Meteor Blasters. All of the index items with this key are stored adjacent to each other for rapid retrieval.
+ Within this game, DynamoDB uses the index to access all of the user IDs and top scores for this game.
+ The results are returned, sorted in descending order because the `ScanIndexForward` parameter is set to false.
### Scanning a Global Secondary Index
You can use the `Scan` operation to retrieve all of the data from a global secondary index. You must provide the base table name and the index name in the request. With a `Scan`, DynamoDB reads all of the data in the index and returns it to the application. You can also request that only some of the data be returned, and that the remaining data should be discarded. To do this, use the `FilterExpression` parameter of the `Scan` operation. For more information, see [Filter expressions for scan](Scan.md#Scan.FilterExpression).
## Data synchronization between tables and Global Secondary Indexes
DynamoDB automatically synchronizes each global secondary index with its base table. When an application writes or deletes items in a table, any global secondary indexes on that table are updated asynchronously, using an eventually consistent model. Applications never write directly to an index. However, it is important that you understand the implications of how DynamoDB maintains these indexes.
Global secondary indexes inherit the read/write capacity mode from the base table. For more information, see [Considerations when switching capacity modes in DynamoDB](bp-switching-capacity-modes.md).
When you create a global secondary index, you specify one or more index key attributes and their data types. This means that whenever you write an item to the base table, the data types for those attributes must match the index key schema's data types. In the case of `GameTitleIndex`, the `GameTitle` partition key in the index is defined as a `String` data type. The `TopScore` sort key in the index is of type `Number`. If you try to add an item to the `GameScores` table and specify a different data type for either `GameTitle` or `TopScore`, DynamoDB returns a `ValidationException` because of the data type mismatch.
When you put or delete items in a table, the global secondary indexes on that table are updated in an eventually consistent fashion. Changes to the table data are propagated to the global secondary indexes within a fraction of a second, under normal conditions. However, in some unlikely failure scenarios, longer propagation delays might occur. Because of this, your applications need to anticipate and handle situations where a query on a global secondary index returns results that are not up to date.
If you write an item to a table, you don't have to specify the attributes for any global secondary index sort key. Using `GameTitleIndex` as an example, you would not need to specify a value for the `TopScore` attribute to write a new item to the `GameScores` table. In this case, DynamoDB does not write any data to the index for this particular item.
A table with many global secondary indexes incurs higher costs for write activity than tables with fewer indexes. For more information, see [Provisioned throughput considerations for Global Secondary Indexes](#GSI.ThroughputConsiderations).
## Table classes with Global Secondary Index
A global secondary index will always use the same table class as its base table. Any time a new global secondary index is added for a table, the new index will use the same table class as its base table. When a table's table class is updated, all associated global secondary indexes are updated as well.
## Provisioned throughput considerations for Global Secondary Indexes
When you create a global secondary index on a provisioned mode table, you must specify read and write capacity units for the expected workload on that index. The provisioned throughput settings of a global secondary index are separate from those of its base table. A `Query` operation on a global secondary index consumes read capacity units from the index, not the base table. When you put, update or delete items in a table, the global secondary indexes on that table are also updated. These index updates consume write capacity units from the index, not from the base table.
For example, if you `Query` a global secondary index and exceed its provisioned read capacity, your request will be throttled. If you perform heavy write activity on the table, but a global secondary index on that table has insufficient write capacity, the write activity on the table will be throttled.
**Important**
To avoid potential throttling, the provisioned write capacity for a global secondary index should be equal or greater than the write capacity of the base table because new updates write to both the base table and global secondary index.
To view the provisioned throughput settings for a global secondary index, use the `DescribeTable` operation. Detailed information about all of the table's global secondary indexes is returned.
### Read capacity units
Global secondary indexes support eventually consistent reads, each of which consume one half of a read capacity unit. This means that a single global secondary index query can retrieve up to 2 × 4 KB = 8 KB per read capacity unit.
For global secondary index queries, DynamoDB calculates the provisioned read activity in the same way as it does for queries against tables. The only difference is that the calculation is based on the sizes of the index entries, rather than the size of the item in the base table. The number of read capacity units is the sum of all projected attribute sizes across all of the items returned. The result is then rounded up to the next 4 KB boundary. For more information about how DynamoDB calculates provisioned throughput usage, see [DynamoDB provisioned capacity mode](provisioned-capacity-mode.md).
The maximum size of the results returned by a `Query` operation is 1 MB. This includes the sizes of all the attribute names and values across all of the items returned.
For example, consider a global secondary index where each item contains 2,000 bytes of data. Now suppose that you `Query` this index and that the query's `KeyConditionExpression` matches eight items. The total size of the matching items is 2,000 bytes × 8 items = 16,000 bytes. This result is then rounded up to the nearest 4 KB boundary. Because global secondary index queries are eventually consistent, the total cost is 0.5 × (16 KB / 4 KB), or 2 read capacity units.
### Write capacity units
When an item in a table is added, updated, or deleted, and a global secondary index is affected by this, the global secondary index consumes provisioned write capacity units for the operation. The total provisioned throughput cost for a write consists of the sum of write capacity units consumed by writing to the base table and those consumed by updating the global secondary indexes. If a write to a table does not require a global secondary index update, no write capacity is consumed from the index.
For a table write to succeed, the provisioned throughput settings for the table and all of its global secondary indexes must have enough write capacity to accommodate the write. Otherwise, the write to the table is throttled.
**Important**
When creating a Global Secondary Index (GSI), write operations to the base table can be throttled if the GSI activity resulting from writes to the base table exceeds the GSI's provisioned write capacity. This throttling affects all write operations, from indexing process to potentially disrupting your production workloads. For more information, see [Troubleshooting throttling in Amazon DynamoDB](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/TroubleshootingThrottling.html).
The cost of writing an item to a global secondary index depends on several factors:
+ If you write a new item to the table that defines an indexed attribute, or you update an existing item to define a previously undefined indexed attribute, one write operation is required to put the item into the index.
+ If an update to the table changes the value of an indexed key attribute (from A to B), two writes are required, one to delete the previous item from the index and another write to put the new item into the index.
+ If an item was present in the index, but a write to the table caused the indexed attribute to be deleted, one write is required to delete the old item projection from the index.
+ If an item is not present in the index before or after the item is updated, there is no additional write cost for the index.
+ If an update to the table only changes the value of projected attributes in the index key schema, but does not change the value of any indexed key attribute, one write is required to update the values of the projected attributes into the index.
All of these factors assume that the size of each item in the index is less than or equal to the 1 KB item size for calculating write capacity units. Larger index entries require additional write capacity units. You can minimize your write costs by considering which attributes your queries will need to return and projecting only those attributes into the index.
## Storage considerations for Global Secondary Indexes
When an application writes an item to a table, DynamoDB automatically copies the correct subset of attributes to any global secondary indexes in which those attributes should appear. Your AWS account is charged for storage of the item in the base table and also for storage of attributes in any global secondary indexes on that table.
The amount of space used by an index item is the sum of the following:
+ The size in bytes of the base table primary key (partition key and sort key)
+ The size in bytes of the index key attribute
+ The size in bytes of the projected attributes (if any)
+ 100 bytes of overhead per index item
To estimate the storage requirements for a global secondary index, you can estimate the average size of an item in the index and then multiply by the number of items in the base table that have the global secondary index key attributes.
If a table contains an item where a particular attribute(s) is not defined, but that attribute is defined as an index partition key or sort key, DynamoDB doesn't write any data for that item to the index.
# Design patterns
Design patterns provide proven solutions to common challenges when working with global secondary indexes. These patterns help you build efficient, scalable applications by showing you how to structure your indexes for specific use cases.
Each pattern includes a complete implementation guide with code examples, best practices, and real-world use cases to help you apply the pattern to your own applications.
**Topics**
+ [Multi-attribute keys](GSI.DesignPattern.MultiAttributeKeys.md)
# Multi-attribute keys pattern
## Overview
Multi-attribute keys allow you to create Global Secondary Index (GSI) partition and sort keys composed of up to four attributes each. This reduces client-side code and makes it easier to initially model data and add new access patterns later.
Consider a common scenario: to create a GSI that queries items by multiple hierarchical attributes, you would traditionally need to create synthetic keys by concatenating values. For example, in a gaming app, to query tournament matches by tournament, region, and round, you might create a synthetic GSI partition key like TOURNAMENT\$1WINTER2024\$1REGION\$1NA-EAST and a synthetic sort key like ROUND\$1SEMIFINALS\$1BRACKET\$1UPPER. This approach works, but requires string concatenation when writing data, parsing when reading, and backfilling synthetic keys across all existing items if you're adding the GSI to an existing table. This makes code more cluttered and challenging to maintain type safety on individual key components.
Multi-attribute keys solve this problem for GSIs. You define your GSI partition key using multiple existing attributes like tournamentId and region. DynamoDB handles the composite key logic automatically, hashing them together for data distribution. You write items using natural attributes from your domain model, and the GSI automatically indexes them. No concatenation, no parsing, no backfilling. Your code stays clean, your data stays typed, and your queries stay simple. This approach is particularly useful when you have hierarchical data with natural attribute groupings (like tournament → region → round, or organization → department → team).
## Application example
This guide walks through building a tournament match tracking system for an esports platform. The platform needs to efficiently query matches across multiple dimensions: by tournament and region for bracket management, by player for match history, and by date for scheduling.
## Data model
In this walkthrough, the tournament match tracking system supports three primary access patterns, each requiring a different key structure:
**Access pattern 1:** Look up a specific match by its unique ID
+ **Solution:** Base table with `matchId` as partition key
**Access pattern 2:** Query all matches for a specific tournament and region, optionally filtering by round, bracket, or match
+ **Solution:** Global Secondary Index with multi-attribute partition key (`tournamentId` \$1 `region`) and multi-attribute sort key (`round` \$1 `bracket` \$1 `matchId`)
+ **Example queries:** "All WINTER2024 matches in NA-EAST region" or "All SEMIFINALS matches in UPPER bracket for WINTER2024/NA-EAST"
**Access pattern 3:** Query a player's match history, optionally filtering by date range or tournament round
+ **Solution:** Global Secondary Index with single partition key (`player1Id`) and multi-attribute sort key (`matchDate` \$1 `round`)
+ **Example queries:** "All matches for player 101" or "Player 101's matches in January 2024"
The key difference between traditional and multi-attribute approaches becomes clear when examining the item structure:
**Traditional Global Secondary Index approach (concatenated keys):**
```
// Manual concatenation required for GSI keys
const item = {
matchId: 'match-001', // Base table PK
tournamentId: 'WINTER2024',
region: 'NA-EAST',
round: 'SEMIFINALS',
bracket: 'UPPER',
player1Id: '101',
// Synthetic keys needed for GSI
GSI_PK: `TOURNAMENT#${tournamentId}#REGION#${region}`, // Must concatenate
GSI_SK: `${round}#${bracket}#${matchId}`, // Must concatenate
// ... other attributes
};
```
**Multi-attribute Global Secondary Index approach (native keys):**
```
// Use existing attributes directly - no concatenation needed
const item = {
matchId: 'match-001', // Base table PK
tournamentId: 'WINTER2024',
region: 'NA-EAST',
round: 'SEMIFINALS',
bracket: 'UPPER',
player1Id: '101',
matchDate: '2024-01-18',
// No synthetic keys needed - GSI uses existing attributes directly
// ... other attributes
};
```
With multi-attribute keys, you write items once with natural domain attributes. DynamoDB automatically indexes them across multiple GSIs without requiring synthetic concatenated keys.
**Base table schema:**
+ Partition key: `matchId` (1 attribute)
**Global Secondary Index Schema (TournamentRegionIndex with multi-attribute keys):**
+ Partition key: `tournamentId`, `region` (2 attributes)
+ Sort key: `round`, `bracket`, `matchId` (3 attributes)
**Global Secondary Index Schema (PlayerMatchHistoryIndex with multi-attribute keys):**
+ Partition key: `player1Id` (1 attribute)
+ Sort key: `matchDate`, `round` (2 attributes)
### Base table: TournamentMatches
| matchId (PK) | tournamentId | region | round | bracket | player1Id | player2Id | matchDate | winner | score |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| match-001 | WINTER2024 | NA-EAST | FINALS | CHAMPIONSHIP | 101 | 103 | 2024-01-20 | 101 | 3-1 |
| match-002 | WINTER2024 | NA-EAST | SEMIFINALS | UPPER | 101 | 105 | 2024-01-18 | 101 | 3-2 |
| match-003 | WINTER2024 | NA-EAST | SEMIFINALS | UPPER | 103 | 107 | 2024-01-18 | 103 | 3-0 |
| match-004 | WINTER2024 | NA-EAST | QUARTERFINALS | UPPER | 101 | 109 | 2024-01-15 | 101 | 3-1 |
| match-005 | WINTER2024 | NA-WEST | FINALS | CHAMPIONSHIP | 102 | 104 | 2024-01-20 | 102 | 3-2 |
| match-006 | WINTER2024 | NA-WEST | SEMIFINALS | UPPER | 102 | 106 | 2024-01-18 | 102 | 3-1 |
| match-007 | SPRING2024 | NA-EAST | QUARTERFINALS | UPPER | 101 | 108 | 2024-03-15 | 101 | 3-0 |
| match-008 | SPRING2024 | NA-EAST | QUARTERFINALS | LOWER | 103 | 110 | 2024-03-15 | 103 | 3-2 |
### GSI: TournamentRegionIndex (multi-attribute keys)
| tournamentId (PK) | region (PK) | round (SK) | bracket (SK) | matchId (SK) | player1Id | player2Id | matchDate | winner | score |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| WINTER2024 | NA-EAST | FINALS | CHAMPIONSHIP | match-001 | 101 | 103 | 2024-01-20 | 101 | 3-1 |
| WINTER2024 | NA-EAST | QUARTERFINALS | UPPER | match-004 | 101 | 109 | 2024-01-15 | 101 | 3-1 |
| WINTER2024 | NA-EAST | SEMIFINALS | UPPER | match-002 | 101 | 105 | 2024-01-18 | 101 | 3-2 |
| WINTER2024 | NA-EAST | SEMIFINALS | UPPER | match-003 | 103 | 107 | 2024-01-18 | 103 | 3-0 |
| WINTER2024 | NA-WEST | FINALS | CHAMPIONSHIP | match-005 | 102 | 104 | 2024-01-20 | 102 | 3-2 |
| WINTER2024 | NA-WEST | SEMIFINALS | UPPER | match-006 | 102 | 106 | 2024-01-18 | 102 | 3-1 |
| SPRING2024 | NA-EAST | QUARTERFINALS | LOWER | match-008 | 103 | 110 | 2024-03-15 | 103 | 3-2 |
| SPRING2024 | NA-EAST | QUARTERFINALS | UPPER | match-007 | 101 | 108 | 2024-03-15 | 101 | 3-0 |
### GSI: PlayerMatchHistoryIndex (multi-attribute keys)
| player1Id (PK) | matchDate (SK) | round (SK) | tournamentId | region | bracket | matchId | player2Id | winner | score |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| 101 | 2024-01-15 | QUARTERFINALS | WINTER2024 | NA-EAST | UPPER | match-004 | 109 | 101 | 3-1 |
| 101 | 2024-01-18 | SEMIFINALS | WINTER2024 | NA-EAST | UPPER | match-002 | 105 | 101 | 3-2 |
| 101 | 2024-01-20 | FINALS | WINTER2024 | NA-EAST | CHAMPIONSHIP | match-001 | 103 | 101 | 3-1 |
| 101 | 2024-03-15 | QUARTERFINALS | SPRING2024 | NA-EAST | UPPER | match-007 | 108 | 101 | 3-0 |
| 102 | 2024-01-18 | SEMIFINALS | WINTER2024 | NA-WEST | UPPER | match-006 | 106 | 102 | 3-1 |
| 102 | 2024-01-20 | FINALS | WINTER2024 | NA-WEST | CHAMPIONSHIP | match-005 | 104 | 102 | 3-2 |
| 103 | 2024-01-18 | SEMIFINALS | WINTER2024 | NA-EAST | UPPER | match-003 | 107 | 103 | 3-0 |
| 103 | 2024-03-15 | QUARTERFINALS | SPRING2024 | NA-EAST | LOWER | match-008 | 110 | 103 | 3-2 |
## Prerequisites
Before you begin, ensure you have:
### Account and permissions
+ An active AWS account ([create one here](https://aws.amazon.com/free/) if needed)
+ IAM permissions for DynamoDB operations:
+ `dynamodb:CreateTable`
+ `dynamodb:DeleteTable`
+ `dynamodb:DescribeTable`
+ `dynamodb:PutItem`
+ `dynamodb:Query`
+ `dynamodb:BatchWriteItem`
**Note**
**Security Note:** For production use, create a custom IAM policy with only the permissions you need. For this tutorial, you can use the AWS managed policy `AmazonDynamoDBFullAccessV2`.
### Development Environment
+ Node.js installed on your machine
+ AWS credentials configured using one of these methods:
**Option 1: AWS CLI**
```
aws configure
```
**Option 2: Environment Variables**
```
export AWS_ACCESS_KEY_ID=your_access_key_here
export AWS_SECRET_ACCESS_KEY=your_secret_key_here
export AWS_DEFAULT_REGION=us-east-1
```
### Install Required Packages
```
npm install @aws-sdk/client-dynamodb @aws-sdk/lib-dynamodb
```
## Implementation
### Step 1: Create table with GSIs using multi-attribute keys
Create a table with a simple base key structure and GSIs that use multi-attribute keys.
#### Code example
```
import { DynamoDBClient, CreateTableCommand } from "@aws-sdk/client-dynamodb";
const client = new DynamoDBClient({ region: 'us-west-2' });
const response = await client.send(new CreateTableCommand({
TableName: 'TournamentMatches',
// Base table: Simple partition key
KeySchema: [
{ AttributeName: 'matchId', KeyType: 'HASH' } // Simple PK
],
AttributeDefinitions: [
{ AttributeName: 'matchId', AttributeType: 'S' },
{ AttributeName: 'tournamentId', AttributeType: 'S' },
{ AttributeName: 'region', AttributeType: 'S' },
{ AttributeName: 'round', AttributeType: 'S' },
{ AttributeName: 'bracket', AttributeType: 'S' },
{ AttributeName: 'player1Id', AttributeType: 'S' },
{ AttributeName: 'matchDate', AttributeType: 'S' }
],
// GSIs with multi-attribute keys
GlobalSecondaryIndexes: [
{
IndexName: 'TournamentRegionIndex',
KeySchema: [
{ AttributeName: 'tournamentId', KeyType: 'HASH' }, // GSI PK attribute 1
{ AttributeName: 'region', KeyType: 'HASH' }, // GSI PK attribute 2
{ AttributeName: 'round', KeyType: 'RANGE' }, // GSI SK attribute 1
{ AttributeName: 'bracket', KeyType: 'RANGE' }, // GSI SK attribute 2
{ AttributeName: 'matchId', KeyType: 'RANGE' } // GSI SK attribute 3
],
Projection: { ProjectionType: 'ALL' }
},
{
IndexName: 'PlayerMatchHistoryIndex',
KeySchema: [
{ AttributeName: 'player1Id', KeyType: 'HASH' }, // GSI PK
{ AttributeName: 'matchDate', KeyType: 'RANGE' }, // GSI SK attribute 1
{ AttributeName: 'round', KeyType: 'RANGE' } // GSI SK attribute 2
],
Projection: { ProjectionType: 'ALL' }
}
],
BillingMode: 'PAY_PER_REQUEST'
}));
console.log("Table with multi-attribute GSI keys created successfully");
```
**Key design decisions:**
**Base table:** The base table uses a simple `matchId` partition key for direct match lookups, keeping the base table structure straightforward while the GSIs provide the complex query patterns.
**TournamentRegionIndex Global Secondary Index:** The `TournamentRegionIndex` Global Secondary Index uses `tournamentId` \$1 `region` as a multi-attribute partition key, creating tournament-region isolation where data is distributed by the hash of both attributes combined, enabling efficient queries within a specific tournament-region context. The multi-attribute sort key (`round` \$1 `bracket` \$1 `matchId`) provides hierarchical sorting that supports queries at any level of the hierarchy with natural ordering from general (round) to specific (match ID).
**PlayerMatchHistoryIndex Global Secondary Index:** The `PlayerMatchHistoryIndex` Global Secondary Index reorganizes data by player using `player1Id` as the partition key, enabling cross-tournament queries for a specific player. The multi-attribute sort key (`matchDate` \$1 `round`) provides chronological ordering with the ability to filter by date ranges or specific tournament rounds.
### Step 2: Insert data with native attributes
Add tournament match data using natural attributes. The GSI will automatically index these attributes without requiring synthetic keys.
#### Code example
```
import { DynamoDBClient } from "@aws-sdk/client-dynamodb";
import { DynamoDBDocumentClient, PutCommand } from "@aws-sdk/lib-dynamodb";
const client = new DynamoDBClient({ region: 'us-west-2' });
const docClient = DynamoDBDocumentClient.from(client);
// Tournament match data - no synthetic keys needed for GSIs
const matches = [
// Winter 2024 Tournament, NA-EAST region
{
matchId: 'match-001',
tournamentId: 'WINTER2024',
region: 'NA-EAST',
round: 'FINALS',
bracket: 'CHAMPIONSHIP',
player1Id: '101',
player2Id: '103',
matchDate: '2024-01-20',
winner: '101',
score: '3-1'
},
{
matchId: 'match-002',
tournamentId: 'WINTER2024',
region: 'NA-EAST',
round: 'SEMIFINALS',
bracket: 'UPPER',
player1Id: '101',
player2Id: '105',
matchDate: '2024-01-18',
winner: '101',
score: '3-2'
},
{
matchId: 'match-003',
tournamentId: 'WINTER2024',
region: 'NA-EAST',
round: 'SEMIFINALS',
bracket: 'UPPER',
player1Id: '103',
player2Id: '107',
matchDate: '2024-01-18',
winner: '103',
score: '3-0'
},
{
matchId: 'match-004',
tournamentId: 'WINTER2024',
region: 'NA-EAST',
round: 'QUARTERFINALS',
bracket: 'UPPER',
player1Id: '101',
player2Id: '109',
matchDate: '2024-01-15',
winner: '101',
score: '3-1'
},
// Winter 2024 Tournament, NA-WEST region
{
matchId: 'match-005',
tournamentId: 'WINTER2024',
region: 'NA-WEST',
round: 'FINALS',
bracket: 'CHAMPIONSHIP',
player1Id: '102',
player2Id: '104',
matchDate: '2024-01-20',
winner: '102',
score: '3-2'
},
{
matchId: 'match-006',
tournamentId: 'WINTER2024',
region: 'NA-WEST',
round: 'SEMIFINALS',
bracket: 'UPPER',
player1Id: '102',
player2Id: '106',
matchDate: '2024-01-18',
winner: '102',
score: '3-1'
},
// Spring 2024 Tournament, NA-EAST region
{
matchId: 'match-007',
tournamentId: 'SPRING2024',
region: 'NA-EAST',
round: 'QUARTERFINALS',
bracket: 'UPPER',
player1Id: '101',
player2Id: '108',
matchDate: '2024-03-15',
winner: '101',
score: '3-0'
},
{
matchId: 'match-008',
tournamentId: 'SPRING2024',
region: 'NA-EAST',
round: 'QUARTERFINALS',
bracket: 'LOWER',
player1Id: '103',
player2Id: '110',
matchDate: '2024-03-15',
winner: '103',
score: '3-2'
}
];
// Insert all matches
for (const match of matches) {
await docClient.send(new PutCommand({
TableName: 'TournamentMatches',
Item: match
}));
console.log(`Added: ${match.matchId} - ${match.tournamentId}/${match.region} - ${match.round} ${match.bracket}`);
}
console.log(`\nInserted ${matches.length} tournament matches`);
console.log("No synthetic keys created - GSIs use native attributes automatically");
```
**Data structure explained:**
**Natural attribute usage:** Each attribute represents a real tournament concept with no string concatenation or parsing required, providing direct mapping to the domain model.
**Automatic Global Secondary Index indexing:** The GSIs automatically index items using the existing attributes (`tournamentId`, `region`, `round`, `bracket`, `matchId` for TournamentRegionIndex and `player1Id`, `matchDate`, `round` for PlayerMatchHistoryIndex) without requiring synthetic concatenated keys.
**No backfilling needed:** When you add a new Global Secondary Index with multi-attribute keys to an existing table, DynamoDB automatically indexes all existing items using their natural attributes—no need to update items with synthetic keys.
### Step 3: Query TournamentRegionIndex Global Secondary Index with all partition key attributes
This example queries the TournamentRegionIndex Global Secondary Index which has a multi-attribute partition key (`tournamentId` \$1 `region`). All partition key attributes must be specified with equality conditions in queries—you cannot query with just `tournamentId` alone or use inequality operators on partition key attributes.
#### Code example
```
import { DynamoDBClient } from "@aws-sdk/client-dynamodb";
import { DynamoDBDocumentClient, QueryCommand } from "@aws-sdk/lib-dynamodb";
const client = new DynamoDBClient({ region: 'us-west-2' });
const docClient = DynamoDBDocumentClient.from(client);
// Query GSI: All matches for WINTER2024 tournament in NA-EAST region
const response = await docClient.send(new QueryCommand({
TableName: 'TournamentMatches',
IndexName: 'TournamentRegionIndex',
KeyConditionExpression: 'tournamentId = :tournament AND #region = :region',
ExpressionAttributeNames: {
'#region': 'region', // 'region' is a reserved keyword
'#tournament': 'tournament'
},
ExpressionAttributeValues: {
':tournament': 'WINTER2024',
':region': 'NA-EAST'
}
}));
console.log(`Found ${response.Items.length} matches for WINTER2024/NA-EAST:\n`);
response.Items.forEach(match => {
console.log(` ${match.round} | ${match.bracket} | ${match.matchId}`);
console.log(` Players: ${match.player1Id} vs ${match.player2Id}`);
console.log(` Winner: ${match.winner}, Score: ${match.score}\n`);
});
```
**Expected output:**
```
Found 4 matches for WINTER2024/NA-EAST:
FINALS | CHAMPIONSHIP | match-001
Players: 101 vs 103
Winner: 101, Score: 3-1
QUARTERFINALS | UPPER | match-004
Players: 101 vs 109
Winner: 101, Score: 3-1
SEMIFINALS | UPPER | match-002
Players: 101 vs 105
Winner: 101, Score: 3-2
SEMIFINALS | UPPER | match-003
Players: 103 vs 107
Winner: 103, Score: 3-0
```
**Invalid queries:**
```
// Missing region attribute
KeyConditionExpression: 'tournamentId = :tournament'
// Using inequality on partition key attribute
KeyConditionExpression: 'tournamentId = :tournament AND #region > :region'
```
**Performance:** Multi-attribute partition keys are hashed together, providing the same O(1) lookup performance as single-attribute keys.
### Step 4: Query Global Secondary Index sort keys left-to-right
Sort key attributes must be queried left-to-right in the order they're defined in the Global Secondary Index. This example demonstrates querying the TournamentRegionIndex at different hierarchy levels: filtering by just `round`, by `round` \$1 `bracket`, or by all three sort key attributes. You cannot skip attributes in the middle—for example, you cannot query by `round` and `matchId` while skipping `bracket`.
#### Code example
```
import { DynamoDBClient } from "@aws-sdk/client-dynamodb";
import { DynamoDBDocumentClient, QueryCommand } from "@aws-sdk/lib-dynamodb";
const client = new DynamoDBClient({ region: 'us-west-2' });
const docClient = DynamoDBDocumentClient.from(client);
// Query 1: Filter by first sort key attribute (round)
console.log("Query 1: All SEMIFINALS matches");
const query1 = await docClient.send(new QueryCommand({
TableName: 'TournamentMatches',
IndexName: 'TournamentRegionIndex',
KeyConditionExpression: 'tournamentId = :tournament AND #region = :region AND round = :round',
ExpressionAttributeNames: {
'#region': 'region' // 'region' is a reserved keyword
},
ExpressionAttributeValues: {
':tournament': 'WINTER2024',
':region': 'NA-EAST',
':round': 'SEMIFINALS'
}
}));
console.log(` Found ${query1.Items.length} matches\n`);
// Query 2: Filter by first two sort key attributes (round + bracket)
console.log("Query 2: SEMIFINALS UPPER bracket matches");
const query2 = await docClient.send(new QueryCommand({
TableName: 'TournamentMatches',
IndexName: 'TournamentRegionIndex',
KeyConditionExpression: 'tournamentId = :tournament AND #region = :region AND round = :round AND bracket = :bracket',
ExpressionAttributeNames: {
'#region': 'region' // 'region' is a reserved keyword
},
ExpressionAttributeValues: {
':tournament': 'WINTER2024',
':region': 'NA-EAST',
':round': 'SEMIFINALS',
':bracket': 'UPPER'
}
}));
console.log(` Found ${query2.Items.length} matches\n`);
// Query 3: Filter by all three sort key attributes (round + bracket + matchId)
console.log("Query 3: Specific match in SEMIFINALS UPPER bracket");
const query3 = await docClient.send(new QueryCommand({
TableName: 'TournamentMatches',
IndexName: 'TournamentRegionIndex',
KeyConditionExpression: 'tournamentId = :tournament AND #region = :region AND round = :round AND bracket = :bracket AND matchId = :matchId',
ExpressionAttributeNames: {
'#region': 'region' // 'region' is a reserved keyword
},
ExpressionAttributeValues: {
':tournament': 'WINTER2024',
':region': 'NA-EAST',
':round': 'SEMIFINALS',
':bracket': 'UPPER',
':matchId': 'match-002'
}
}));
console.log(` Found ${query3.Items.length} matches\n`);
// Query 4: INVALID - skipping round
console.log("Query 4: Attempting to skip first sort key attribute (WILL FAIL)");
try {
const query4 = await docClient.send(new QueryCommand({
TableName: 'TournamentMatches',
IndexName: 'TournamentRegionIndex',
KeyConditionExpression: 'tournamentId = :tournament AND #region = :region AND bracket = :bracket',
ExpressionAttributeNames: {
'#region': 'region' // 'region' is a reserved keyword
},
ExpressionAttributeValues: {
':tournament': 'WINTER2024',
':region': 'NA-EAST',
':bracket': 'UPPER'
}
}));
} catch (error) {
console.log(` Error: ${error.message}`);
console.log(` Cannot skip sort key attributes - must query left-to-right\n`);
}
```
**Expected output:**
```
Query 1: All SEMIFINALS matches
Found 2 matches
Query 2: SEMIFINALS UPPER bracket matches
Found 2 matches
Query 3: Specific match in SEMIFINALS UPPER bracket
Found 1 matches
Query 4: Attempting to skip first sort key attribute (WILL FAIL)
Error: Query key condition not supported
Cannot skip sort key attributes - must query left-to-right
```
**Left-to-right query rules:** You must query attributes in order from left to right, without skipping any.
**Valid patterns:**
+ First attribute only: `round = 'SEMIFINALS'`
+ First two attributes: `round = 'SEMIFINALS' AND bracket = 'UPPER'`
+ All three attributes: `round = 'SEMIFINALS' AND bracket = 'UPPER' AND matchId = 'match-002'`
**Invalid patterns:**
+ Skipping the first attribute: `bracket = 'UPPER'` (skips round)
+ Querying out of order: `matchId = 'match-002' AND round = 'SEMIFINALS'`
+ Leaving gaps: `round = 'SEMIFINALS' AND matchId = 'match-002'` (skips bracket)
**Note**
**Design tip:** Order sort key attributes from most general to most specific to maximize query flexibility.
### Step 5: Use inequality conditions on Global Secondary Index sort keys
Inequality conditions must be the last condition in your query. This example demonstrates using comparison operators (`>=`, `BETWEEN`) and prefix matching (`begins_with()`) on sort key attributes. Once you use an inequality operator, you cannot add any additional sort key conditions after it—the inequality must be the final condition in your key condition expression.
#### Code example
```
import { DynamoDBClient } from "@aws-sdk/client-dynamodb";
import { DynamoDBDocumentClient, QueryCommand } from "@aws-sdk/lib-dynamodb";
const client = new DynamoDBClient({ region: 'us-west-2' });
const docClient = DynamoDBDocumentClient.from(client);
// Query 1: Round comparison (inequality on first sort key attribute)
console.log("Query 1: Matches from QUARTERFINALS onwards");
const query1 = await docClient.send(new QueryCommand({
TableName: 'TournamentMatches',
IndexName: 'TournamentRegionIndex',
KeyConditionExpression: 'tournamentId = :tournament AND #region = :region AND round >= :round',
ExpressionAttributeNames: {
'#region': 'region' // 'region' is a reserved keyword
},
ExpressionAttributeValues: {
':tournament': 'WINTER2024',
':region': 'NA-EAST',
':round': 'QUARTERFINALS'
}
}));
console.log(` Found ${query1.Items.length} matches\n`);
// Query 2: Round range with BETWEEN
console.log("Query 2: Matches between QUARTERFINALS and SEMIFINALS");
const query2 = await docClient.send(new QueryCommand({
TableName: 'TournamentMatches',
IndexName: 'TournamentRegionIndex',
KeyConditionExpression: 'tournamentId = :tournament AND #region = :region AND round BETWEEN :start AND :end',
ExpressionAttributeNames: {
'#region': 'region' // 'region' is a reserved keyword
},
ExpressionAttributeValues: {
':tournament': 'WINTER2024',
':region': 'NA-EAST',
':start': 'QUARTERFINALS',
':end': 'SEMIFINALS'
}
}));
console.log(` Found ${query2.Items.length} matches\n`);
// Query 3: Prefix matching with begins_with (treated as inequality)
console.log("Query 3: Matches in brackets starting with 'U'");
const query3 = await docClient.send(new QueryCommand({
TableName: 'TournamentMatches',
IndexName: 'TournamentRegionIndex',
KeyConditionExpression: 'tournamentId = :tournament AND #region = :region AND round = :round AND begins_with(bracket, :prefix)',
ExpressionAttributeNames: {
'#region': 'region' // 'region' is a reserved keyword
},
ExpressionAttributeValues: {
':tournament': 'WINTER2024',
':region': 'NA-EAST',
':round': 'SEMIFINALS',
':prefix': 'U'
}
}));
console.log(` Found ${query3.Items.length} matches\n`);
// Query 4: INVALID - condition after inequality
console.log("Query 4: Attempting condition after inequality (WILL FAIL)");
try {
const query4 = await docClient.send(new QueryCommand({
TableName: 'TournamentMatches',
IndexName: 'TournamentRegionIndex',
KeyConditionExpression: 'tournamentId = :tournament AND #region = :region AND round > :round AND bracket = :bracket',
ExpressionAttributeNames: {
'#region': 'region' // 'region' is a reserved keyword
},
ExpressionAttributeValues: {
':tournament': 'WINTER2024',
':region': 'NA-EAST',
':round': 'QUARTERFINALS',
':bracket': 'UPPER'
}
}));
} catch (error) {
console.log(` Error: ${error.message}`);
console.log(` Cannot add conditions after inequality - it must be last\n`);
}
```
**Inequality operator rules:** You can use comparison operators (`>`, `>=`, `<`, `<=`), `BETWEEN` for range queries, and `begins_with()` for prefix matching. The inequality must be the last condition in your query.
**Valid patterns:**
+ Equality conditions followed by inequality: `round = 'SEMIFINALS' AND bracket = 'UPPER' AND matchId > 'match-001'`
+ Inequality on first attribute: `round BETWEEN 'QUARTERFINALS' AND 'SEMIFINALS'`
+ Prefix matching as final condition: `round = 'SEMIFINALS' AND begins_with(bracket, 'U')`
**Invalid patterns:**
+ Adding conditions after an inequality: `round > 'QUARTERFINALS' AND bracket = 'UPPER'`
+ Using multiple inequalities: `round > 'QUARTERFINALS' AND bracket > 'L'`
**Important**
`begins_with()` is treated as an inequality condition, so no additional sort key conditions can follow it.
### Step 6: Query PlayerMatchHistoryIndex Global Secondary Index with multi-attribute sort key
This example queries the PlayerMatchHistoryIndex which has a single partition key (`player1Id`) and a multi-attribute sort key (`matchDate` \$1 `round`). This enables cross-tournament analysis by querying all matches for a specific player without knowing tournament IDs—whereas the base table would require separate queries per tournament-region combination.
#### Code example
```
import { DynamoDBClient } from "@aws-sdk/client-dynamodb";
import { DynamoDBDocumentClient, QueryCommand } from "@aws-sdk/lib-dynamodb";
const client = new DynamoDBClient({ region: 'us-west-2' });
const docClient = DynamoDBDocumentClient.from(client);
// Query 1: All matches for Player 101 across all tournaments
console.log("Query 1: All matches for Player 101");
const query1 = await docClient.send(new QueryCommand({
TableName: 'TournamentMatches',
IndexName: 'PlayerMatchHistoryIndex',
KeyConditionExpression: 'player1Id = :player',
ExpressionAttributeValues: {
':player': '101'
}
}));
console.log(` Found ${query1.Items.length} matches for Player 101:`);
query1.Items.forEach(match => {
console.log(` ${match.tournamentId}/${match.region} - ${match.matchDate} - ${match.round}`);
});
console.log();
// Query 2: Player 101 matches on specific date
console.log("Query 2: Player 101 matches on 2024-01-18");
const query2 = await docClient.send(new QueryCommand({
TableName: 'TournamentMatches',
IndexName: 'PlayerMatchHistoryIndex',
KeyConditionExpression: 'player1Id = :player AND matchDate = :date',
ExpressionAttributeValues: {
':player': '101',
':date': '2024-01-18'
}
}));
console.log(` Found ${query2.Items.length} matches\n`);
// Query 3: Player 101 SEMIFINALS matches on specific date
console.log("Query 3: Player 101 SEMIFINALS matches on 2024-01-18");
const query3 = await docClient.send(new QueryCommand({
TableName: 'TournamentMatches',
IndexName: 'PlayerMatchHistoryIndex',
KeyConditionExpression: 'player1Id = :player AND matchDate = :date AND round = :round',
ExpressionAttributeValues: {
':player': '101',
':date': '2024-01-18',
':round': 'SEMIFINALS'
}
}));
console.log(` Found ${query3.Items.length} matches\n`);
// Query 4: Player 101 matches in date range
console.log("Query 4: Player 101 matches in January 2024");
const query4 = await docClient.send(new QueryCommand({
TableName: 'TournamentMatches',
IndexName: 'PlayerMatchHistoryIndex',
KeyConditionExpression: 'player1Id = :player AND matchDate BETWEEN :start AND :end',
ExpressionAttributeValues: {
':player': '101',
':start': '2024-01-01',
':end': '2024-01-31'
}
}));
console.log(` Found ${query4.Items.length} matches\n`);
```
## Pattern variations
### Time-series data with multi-attribute keys
Optimize for time-series queries with hierarchical time attributes
#### Code example
```
{
TableName: 'IoTReadings',
// Base table: Simple partition key
KeySchema: [
{ AttributeName: 'readingId', KeyType: 'HASH' }
],
AttributeDefinitions: [
{ AttributeName: 'readingId', AttributeType: 'S' },
{ AttributeName: 'deviceId', AttributeType: 'S' },
{ AttributeName: 'locationId', AttributeType: 'S' },
{ AttributeName: 'year', AttributeType: 'S' },
{ AttributeName: 'month', AttributeType: 'S' },
{ AttributeName: 'day', AttributeType: 'S' },
{ AttributeName: 'timestamp', AttributeType: 'S' }
],
// GSI with multi-attribute keys for time-series queries
GlobalSecondaryIndexes: [{
IndexName: 'DeviceLocationTimeIndex',
KeySchema: [
{ AttributeName: 'deviceId', KeyType: 'HASH' },
{ AttributeName: 'locationId', KeyType: 'HASH' },
{ AttributeName: 'year', KeyType: 'RANGE' },
{ AttributeName: 'month', KeyType: 'RANGE' },
{ AttributeName: 'day', KeyType: 'RANGE' },
{ AttributeName: 'timestamp', KeyType: 'RANGE' }
],
Projection: { ProjectionType: 'ALL' }
}],
BillingMode: 'PAY_PER_REQUEST'
}
// Query patterns enabled via GSI:
// - All readings for device in location
// - Readings for specific year
// - Readings for specific month in year
// - Readings for specific day
// - Readings in time range
```
**Benefits:** Natural time hierarchy (year → month → day → timestamp) enables efficient queries at any time granularity without date parsing or manipulation. Global Secondary Index automatically indexes all readings using their natural time attributes.
### E-commerce orders with multi-attribute keys
Track orders with multiple dimensions
#### Code example
```
{
TableName: 'Orders',
// Base table: Simple partition key
KeySchema: [
{ AttributeName: 'orderId', KeyType: 'HASH' }
],
AttributeDefinitions: [
{ AttributeName: 'orderId', AttributeType: 'S' },
{ AttributeName: 'sellerId', AttributeType: 'S' },
{ AttributeName: 'region', AttributeType: 'S' },
{ AttributeName: 'orderDate', AttributeType: 'S' },
{ AttributeName: 'category', AttributeType: 'S' },
{ AttributeName: 'customerId', AttributeType: 'S' },
{ AttributeName: 'orderStatus', AttributeType: 'S' }
],
GlobalSecondaryIndexes: [
{
IndexName: 'SellerRegionIndex',
KeySchema: [
{ AttributeName: 'sellerId', KeyType: 'HASH' },
{ AttributeName: 'region', KeyType: 'HASH' },
{ AttributeName: 'orderDate', KeyType: 'RANGE' },
{ AttributeName: 'category', KeyType: 'RANGE' },
{ AttributeName: 'orderId', KeyType: 'RANGE' }
],
Projection: { ProjectionType: 'ALL' }
},
{
IndexName: 'CustomerOrdersIndex',
KeySchema: [
{ AttributeName: 'customerId', KeyType: 'HASH' },
{ AttributeName: 'orderDate', KeyType: 'RANGE' },
{ AttributeName: 'orderStatus', KeyType: 'RANGE' }
],
Projection: { ProjectionType: 'ALL' }
}
],
BillingMode: 'PAY_PER_REQUEST'
}
// SellerRegionIndex GSI queries:
// - Orders by seller and region
// - Orders by seller, region, and date
// - Orders by seller, region, date, and category
// CustomerOrdersIndex GSI queries:
// - Customer's orders
// - Customer's orders by date
// - Customer's orders by date and status
```
### Hierarchical organization data
Model organizational hierarchies
#### Code example
```
{
TableName: 'Employees',
// Base table: Simple partition key
KeySchema: [
{ AttributeName: 'employeeId', KeyType: 'HASH' }
],
AttributeDefinitions: [
{ AttributeName: 'employeeId', AttributeType: 'S' },
{ AttributeName: 'companyId', AttributeType: 'S' },
{ AttributeName: 'divisionId', AttributeType: 'S' },
{ AttributeName: 'departmentId', AttributeType: 'S' },
{ AttributeName: 'teamId', AttributeType: 'S' },
{ AttributeName: 'skillCategory', AttributeType: 'S' },
{ AttributeName: 'skillLevel', AttributeType: 'S' },
{ AttributeName: 'yearsExperience', AttributeType: 'N' }
],
GlobalSecondaryIndexes: [
{
IndexName: 'OrganizationIndex',
KeySchema: [
{ AttributeName: 'companyId', KeyType: 'HASH' },
{ AttributeName: 'divisionId', KeyType: 'HASH' },
{ AttributeName: 'departmentId', KeyType: 'RANGE' },
{ AttributeName: 'teamId', KeyType: 'RANGE' },
{ AttributeName: 'employeeId', KeyType: 'RANGE' }
],
Projection: { ProjectionType: 'ALL' }
},
{
IndexName: 'SkillsIndex',
KeySchema: [
{ AttributeName: 'skillCategory', KeyType: 'HASH' },
{ AttributeName: 'skillLevel', KeyType: 'RANGE' },
{ AttributeName: 'yearsExperience', KeyType: 'RANGE' }
],
Projection: { ProjectionType: 'INCLUDE', NonKeyAttributes: ['employeeId', 'name'] }
}
],
BillingMode: 'PAY_PER_REQUEST'
}
// OrganizationIndex GSI query patterns:
// - All employees in company/division
// - Employees in specific department
// - Employees in specific team
// SkillsIndex GSI query patterns:
// - Employees by skill and experience level
```
### Sparse multi-attribute keys
Combine multi-attribute keys to make a sparse GSI
#### Code example
```
{
TableName: 'Products',
// Base table: Simple partition key
KeySchema: [
{ AttributeName: 'productId', KeyType: 'HASH' }
],
AttributeDefinitions: [
{ AttributeName: 'productId', AttributeType: 'S' },
{ AttributeName: 'categoryId', AttributeType: 'S' },
{ AttributeName: 'subcategoryId', AttributeType: 'S' },
{ AttributeName: 'averageRating', AttributeType: 'N' },
{ AttributeName: 'reviewCount', AttributeType: 'N' }
],
GlobalSecondaryIndexes: [
{
IndexName: 'CategoryIndex',
KeySchema: [
{ AttributeName: 'categoryId', KeyType: 'HASH' },
{ AttributeName: 'subcategoryId', KeyType: 'HASH' },
{ AttributeName: 'productId', KeyType: 'RANGE' }
],
Projection: { ProjectionType: 'ALL' }
},
{
IndexName: 'ReviewedProductsIndex',
KeySchema: [
{ AttributeName: 'categoryId', KeyType: 'HASH' },
{ AttributeName: 'averageRating', KeyType: 'RANGE' }, // Optional attribute
{ AttributeName: 'reviewCount', KeyType: 'RANGE' } // Optional attribute
],
Projection: { ProjectionType: 'ALL' }
}
],
BillingMode: 'PAY_PER_REQUEST'
}
// Only products with reviews appear in ReviewedProductsIndex GSI
// Automatic filtering without application logic
// Multi-attribute sort key enables rating and count queries
```
### SaaS multi-tenancy
Multi-tenant SaaS platform with customer isolation
#### Code example
```
// Table design
{
TableName: 'SaasData',
// Base table: Simple partition key
KeySchema: [
{ AttributeName: 'resourceId', KeyType: 'HASH' }
],
AttributeDefinitions: [
{ AttributeName: 'resourceId', AttributeType: 'S' },
{ AttributeName: 'tenantId', AttributeType: 'S' },
{ AttributeName: 'customerId', AttributeType: 'S' },
{ AttributeName: 'resourceType', AttributeType: 'S' }
],
// GSI with multi-attribute keys for tenant-customer isolation
GlobalSecondaryIndexes: [{
IndexName: 'TenantCustomerIndex',
KeySchema: [
{ AttributeName: 'tenantId', KeyType: 'HASH' },
{ AttributeName: 'customerId', KeyType: 'HASH' },
{ AttributeName: 'resourceType', KeyType: 'RANGE' },
{ AttributeName: 'resourceId', KeyType: 'RANGE' }
],
Projection: { ProjectionType: 'ALL' }
}],
BillingMode: 'PAY_PER_REQUEST'
}
// Query GSI: All resources for tenant T001, customer C001
const resources = await docClient.send(new QueryCommand({
TableName: 'SaasData',
IndexName: 'TenantCustomerIndex',
KeyConditionExpression: 'tenantId = :tenant AND customerId = :customer',
ExpressionAttributeValues: {
':tenant': 'T001',
':customer': 'C001'
}
}));
// Query GSI: Specific resource type for tenant/customer
const documents = await docClient.send(new QueryCommand({
TableName: 'SaasData',
IndexName: 'TenantCustomerIndex',
KeyConditionExpression: 'tenantId = :tenant AND customerId = :customer AND resourceType = :type',
ExpressionAttributeValues: {
':tenant': 'T001',
':customer': 'C001',
':type': 'document'
}
}));
```
**Benefits:** Efficient queries within tenant-customer context and natural data organization.
### Financial transactions
Banking system tracking account transactions using GSIs
#### Code example
```
// Table design
{
TableName: 'BankTransactions',
// Base table: Simple partition key
KeySchema: [
{ AttributeName: 'transactionId', KeyType: 'HASH' }
],
AttributeDefinitions: [
{ AttributeName: 'transactionId', AttributeType: 'S' },
{ AttributeName: 'accountId', AttributeType: 'S' },
{ AttributeName: 'year', AttributeType: 'S' },
{ AttributeName: 'month', AttributeType: 'S' },
{ AttributeName: 'day', AttributeType: 'S' },
{ AttributeName: 'transactionType', AttributeType: 'S' }
],
GlobalSecondaryIndexes: [
{
IndexName: 'AccountTimeIndex',
KeySchema: [
{ AttributeName: 'accountId', KeyType: 'HASH' },
{ AttributeName: 'year', KeyType: 'RANGE' },
{ AttributeName: 'month', KeyType: 'RANGE' },
{ AttributeName: 'day', KeyType: 'RANGE' },
{ AttributeName: 'transactionId', KeyType: 'RANGE' }
],
Projection: { ProjectionType: 'ALL' }
},
{
IndexName: 'TransactionTypeIndex',
KeySchema: [
{ AttributeName: 'accountId', KeyType: 'HASH' },
{ AttributeName: 'transactionType', KeyType: 'RANGE' },
{ AttributeName: 'year', KeyType: 'RANGE' },
{ AttributeName: 'month', KeyType: 'RANGE' }
],
Projection: { ProjectionType: 'ALL' }
}
],
BillingMode: 'PAY_PER_REQUEST'
}
// Query AccountTimeIndex GSI: All transactions for account in 2023
const yearTransactions = await docClient.send(new QueryCommand({
TableName: 'BankTransactions',
IndexName: 'AccountTimeIndex',
KeyConditionExpression: 'accountId = :account AND #year = :year',
ExpressionAttributeNames: { '#year': 'year' },
ExpressionAttributeValues: {
':account': 'ACC-12345',
':year': '2023'
}
}));
// Query AccountTimeIndex GSI: Transactions in specific month
const monthTransactions = await docClient.send(new QueryCommand({
TableName: 'BankTransactions',
IndexName: 'AccountTimeIndex',
KeyConditionExpression: 'accountId = :account AND #year = :year AND #month = :month',
ExpressionAttributeNames: { '#year': 'year', '#month': 'month' },
ExpressionAttributeValues: {
':account': 'ACC-12345',
':year': '2023',
':month': '11'
}
}));
// Query TransactionTypeIndex GSI: Deposits in 2023
const deposits = await docClient.send(new QueryCommand({
TableName: 'BankTransactions',
IndexName: 'TransactionTypeIndex',
KeyConditionExpression: 'accountId = :account AND transactionType = :type AND #year = :year',
ExpressionAttributeNames: { '#year': 'year' },
ExpressionAttributeValues: {
':account': 'ACC-12345',
':type': 'deposit',
':year': '2023'
}
}));
```
## Complete example
The following example demonstrates multi-attribute keys from setup to cleanup:
### Code example
```
import {
DynamoDBClient,
CreateTableCommand,
DeleteTableCommand,
waitUntilTableExists
} from "@aws-sdk/client-dynamodb";
import {
DynamoDBDocumentClient,
PutCommand,
QueryCommand
} from "@aws-sdk/lib-dynamodb";
const client = new DynamoDBClient({ region: 'us-west-2' });
const docClient = DynamoDBDocumentClient.from(client);
async function multiAttributeKeysDemo() {
console.log("Starting Multi-Attribute GSI Keys Demo\n");
// Step 1: Create table with GSIs using multi-attribute keys
console.log("1. Creating table with multi-attribute GSI keys...");
await client.send(new CreateTableCommand({
TableName: 'TournamentMatches',
KeySchema: [
{ AttributeName: 'matchId', KeyType: 'HASH' }
],
AttributeDefinitions: [
{ AttributeName: 'matchId', AttributeType: 'S' },
{ AttributeName: 'tournamentId', AttributeType: 'S' },
{ AttributeName: 'region', AttributeType: 'S' },
{ AttributeName: 'round', AttributeType: 'S' },
{ AttributeName: 'bracket', AttributeType: 'S' },
{ AttributeName: 'player1Id', AttributeType: 'S' },
{ AttributeName: 'matchDate', AttributeType: 'S' }
],
GlobalSecondaryIndexes: [
{
IndexName: 'TournamentRegionIndex',
KeySchema: [
{ AttributeName: 'tournamentId', KeyType: 'HASH' },
{ AttributeName: 'region', KeyType: 'HASH' },
{ AttributeName: 'round', KeyType: 'RANGE' },
{ AttributeName: 'bracket', KeyType: 'RANGE' },
{ AttributeName: 'matchId', KeyType: 'RANGE' }
],
Projection: { ProjectionType: 'ALL' }
},
{
IndexName: 'PlayerMatchHistoryIndex',
KeySchema: [
{ AttributeName: 'player1Id', KeyType: 'HASH' },
{ AttributeName: 'matchDate', KeyType: 'RANGE' },
{ AttributeName: 'round', KeyType: 'RANGE' }
],
Projection: { ProjectionType: 'ALL' }
}
],
BillingMode: 'PAY_PER_REQUEST'
}));
await waitUntilTableExists({ client, maxWaitTime: 120 }, { TableName: 'TournamentMatches' });
console.log("Table created\n");
// Step 2: Insert tournament matches
console.log("2. Inserting tournament matches...");
const matches = [
{ matchId: 'match-001', tournamentId: 'WINTER2024', region: 'NA-EAST', round: 'FINALS', bracket: 'CHAMPIONSHIP', player1Id: '101', player2Id: '103', matchDate: '2024-01-20', winner: '101', score: '3-1' },
{ matchId: 'match-002', tournamentId: 'WINTER2024', region: 'NA-EAST', round: 'SEMIFINALS', bracket: 'UPPER', player1Id: '101', player2Id: '105', matchDate: '2024-01-18', winner: '101', score: '3-2' },
{ matchId: 'match-003', tournamentId: 'WINTER2024', region: 'NA-WEST', round: 'FINALS', bracket: 'CHAMPIONSHIP', player1Id: '102', player2Id: '104', matchDate: '2024-01-20', winner: '102', score: '3-2' },
{ matchId: 'match-004', tournamentId: 'SPRING2024', region: 'NA-EAST', round: 'QUARTERFINALS', bracket: 'UPPER', player1Id: '101', player2Id: '108', matchDate: '2024-03-15', winner: '101', score: '3-0' }
];
for (const match of matches) {
await docClient.send(new PutCommand({ TableName: 'TournamentMatches', Item: match }));
}
console.log(`Inserted ${matches.length} tournament matches\n`);
// Step 3: Query GSI with multi-attribute partition key
console.log("3. Query TournamentRegionIndex GSI: WINTER2024/NA-EAST matches");
const gsiQuery1 = await docClient.send(new QueryCommand({
TableName: 'TournamentMatches',
IndexName: 'TournamentRegionIndex',
KeyConditionExpression: 'tournamentId = :tournament AND #region = :region',
ExpressionAttributeNames: { '#region': 'region' },
ExpressionAttributeValues: { ':tournament': 'WINTER2024', ':region': 'NA-EAST' }
}));
console.log(` Found ${gsiQuery1.Items.length} matches:`);
gsiQuery1.Items.forEach(match => {
console.log(` ${match.round} - ${match.bracket} - ${match.winner} won`);
});
// Step 4: Query GSI with multi-attribute sort key
console.log("\n4. Query PlayerMatchHistoryIndex GSI: All matches for Player 101");
const gsiQuery2 = await docClient.send(new QueryCommand({
TableName: 'TournamentMatches',
IndexName: 'PlayerMatchHistoryIndex',
KeyConditionExpression: 'player1Id = :player',
ExpressionAttributeValues: { ':player': '101' }
}));
console.log(` Found ${gsiQuery2.Items.length} matches for Player 101:`);
gsiQuery2.Items.forEach(match => {
console.log(` ${match.tournamentId}/${match.region} - ${match.matchDate} - ${match.round}`);
});
console.log("\nDemo complete");
console.log("No synthetic keys needed - GSIs use native attributes automatically");
}
async function cleanup() {
console.log("Deleting table...");
await client.send(new DeleteTableCommand({ TableName: 'TournamentMatches' }));
console.log("Table deleted");
}
// Run demo
multiAttributeKeysDemo().catch(console.error);
// Uncomment to cleanup:
// cleanup().catch(console.error);
```
**Minimal code scaffold**
### Code example
```
// 1. Create table with GSI using multi-attribute keys
await client.send(new CreateTableCommand({
TableName: 'MyTable',
KeySchema: [
{ AttributeName: 'id', KeyType: 'HASH' } // Simple base table PK
],
AttributeDefinitions: [
{ AttributeName: 'id', AttributeType: 'S' },
{ AttributeName: 'attr1', AttributeType: 'S' },
{ AttributeName: 'attr2', AttributeType: 'S' },
{ AttributeName: 'attr3', AttributeType: 'S' },
{ AttributeName: 'attr4', AttributeType: 'S' }
],
GlobalSecondaryIndexes: [{
IndexName: 'MyGSI',
KeySchema: [
{ AttributeName: 'attr1', KeyType: 'HASH' }, // GSI PK attribute 1
{ AttributeName: 'attr2', KeyType: 'HASH' }, // GSI PK attribute 2
{ AttributeName: 'attr3', KeyType: 'RANGE' }, // GSI SK attribute 1
{ AttributeName: 'attr4', KeyType: 'RANGE' } // GSI SK attribute 2
],
Projection: { ProjectionType: 'ALL' }
}],
BillingMode: 'PAY_PER_REQUEST'
}));
// 2. Insert items with native attributes (no concatenation needed for GSI)
await docClient.send(new PutCommand({
TableName: 'MyTable',
Item: {
id: 'item-001',
attr1: 'value1',
attr2: 'value2',
attr3: 'value3',
attr4: 'value4',
// ... other attributes
}
}));
// 3. Query GSI with all partition key attributes
await docClient.send(new QueryCommand({
TableName: 'MyTable',
IndexName: 'MyGSI',
KeyConditionExpression: 'attr1 = :v1 AND attr2 = :v2',
ExpressionAttributeValues: {
':v1': 'value1',
':v2': 'value2'
}
}));
// 4. Query GSI with sort key attributes (left-to-right)
await docClient.send(new QueryCommand({
TableName: 'MyTable',
IndexName: 'MyGSI',
KeyConditionExpression: 'attr1 = :v1 AND attr2 = :v2 AND attr3 = :v3',
ExpressionAttributeValues: {
':v1': 'value1',
':v2': 'value2',
':v3': 'value3'
}
}));
// Note: If any attribute name is a DynamoDB reserved keyword, use ExpressionAttributeNames:
// KeyConditionExpression: 'attr1 = :v1 AND #attr2 = :v2'
// ExpressionAttributeNames: { '#attr2': 'attr2' }
```
## Additional resources
+ [DynamoDB Best Practices](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/best-practices.html)
+ [Working with Tables and Data](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/WorkingWithTables.html)
+ [Global Secondary Indexes](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/GSI.html)
+ [Query and Scan Operations](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Query.html)
# Managing Global Secondary Indexes in DynamoDB
This section describes how to create, modify, and delete global secondary indexes in Amazon DynamoDB.
**Topics**
+ [Creating a table with Global Secondary Indexes](#GSI.Creating)
+ [Describing the Global Secondary Indexes on a table](#GSI.Describing)
+ [Adding a Global Secondary Index to an existing table](#GSI.OnlineOps.Creating)
+ [Deleting a Global Secondary Index](#GSI.OnlineOps.Deleting)
+ [Modifying a Global Secondary Index during creation](#GSI.OnlineOps.Creating.Modify)
## Creating a table with Global Secondary Indexes
To create a table with one or more global secondary indexes, use the `CreateTable` operation with the `GlobalSecondaryIndexes` parameter. For maximum query flexibility, you can create up to 20 global secondary indexes (default quota) per table.
You must specify one attribute to act as the index partition key. You can optionally specify another attribute for the index sort key. It is not necessary for either of these key attributes to be the same as a key attribute in the table. For example, in the *GameScores* table (see [Using Global Secondary Indexes in DynamoDB](GSI.md)), neither `TopScore` nor `TopScoreDateTime` are key attributes. You could create a global secondary index with a partition key of `TopScore` and a sort key of `TopScoreDateTime`. You might use such an index to determine whether there is a correlation between high scores and the time of day a game is played.
Each index key attribute must be a scalar of type `String`, `Number`, or `Binary`. (It cannot be a document or a set.) You can project attributes of any data type into a global secondary index. This includes scalars, documents, and sets. For a complete list of data types, see [Data types](HowItWorks.NamingRulesDataTypes.md#HowItWorks.DataTypes).
If using provisioned mode, you must provide `ProvisionedThroughput` settings for the index, consisting of `ReadCapacityUnits` and `WriteCapacityUnits`. These provisioned throughput settings are separate from those of the table, but behave in similar ways. For more information, see [Provisioned throughput considerations for Global Secondary Indexes](GSI.md#GSI.ThroughputConsiderations).
Global secondary indexes inherit the read/write capacity mode from the base table. For more information, see [Considerations when switching capacity modes in DynamoDB](bp-switching-capacity-modes.md).
**Note**
When creating a new GSI, it can be important to check if your choice of partition key is producing uneven or narrowed distribution of data or traffic across the new index’s partition key values. If this occurs, you could be seeing backfill and write operations occurring at the same time and throttling writes to the base table. The service takes measures to minimize the potential for this scenario, but has no insight into the shape of customer data with respect to the index partition key, the chosen projection, or the sparseness of the index primary key.
If you suspect that your new global secondary index might have narrow or skewed data or traffic distribution across partition key values, consider the following before adding new indexes to operationally important tables.
It might be safest to add the index at a time when your application is driving the least amount of traffic.
Consider enabling CloudWatch Contributor Insights on your base table and indexes. This will give you valuable insight into your traffic distribution.
Watch `WriteThrottleEvents`, `ThrottledRequests`, and `OnlineIndexPercentageProgress` CloudWatch metrics throughout the process. Adjust the provisioned write capacity as required to complete the backfill in a reasonable time without any significant throttling effects on your ongoing operations. `OnlineIndexConsumedWriteCapacity` and `OnlineThrottleEvents` are expected to show 0 during index backfill.
Be prepared to cancel the index creation if you experience operational impact due to write throttling.
## Describing the Global Secondary Indexes on a table
To view the status of all the global secondary indexes on a table, use the `DescribeTable` operation. The `GlobalSecondaryIndexes` portion of the response shows all of the indexes on the table, along with the current status of each ( `IndexStatus`).
The `IndexStatus` for a global secondary index will be one of the following:
+ `CREATING` — The index is currently being created, and is not yet available for use.
+ `ACTIVE` — The index is ready for use, and applications can perform `Query` operations on the index.
+ `UPDATING` — The provisioned throughput settings of the index are being changed.
+ `DELETING` — The index is currently being deleted, and can no longer be used.
When DynamoDB has finished building a global secondary index, the index status changes from `CREATING` to `ACTIVE`.
## Adding a Global Secondary Index to an existing table
To add a global secondary index to an existing table, use the `UpdateTable` operation with the `GlobalSecondaryIndexUpdates` parameter. You must provide the following:
+ An index name. The name must be unique among all the indexes on the table.
+ The key schema of the index. You must specify one attribute for the index partition key; you can optionally specify another attribute for the index sort key. It is not necessary for either of these key attributes to be the same as a key attribute in the table. The data types for each schema attribute must be scalar: `String`, `Number`, or `Binary`.
+ The attributes to be projected from the table into the index:
+ `KEYS_ONLY` — Each item in the index consists only of the table partition key and sort key values, plus the index key values.
+ `INCLUDE` — In addition to the attributes described in `KEYS_ONLY`, the secondary index includes other non-key attributes that you specify.
+ `ALL` — The index includes all of the attributes from the source table.
+ The provisioned throughput settings for the index, consisting of `ReadCapacityUnits` and `WriteCapacityUnits`. These provisioned throughput settings are separate from those of the table.
You can only create one global secondary index per `UpdateTable` operation.
### Phases of index creation
When you add a new global secondary index to an existing table, the table continues to be available while the index is being built. However, the new index is not available for Query operations until its status changes from `CREATING` to `ACTIVE`.
**Note**
Global secondary index creation does not use Application Auto Scaling. Increasing the `MIN` Application Auto Scaling capacity will not decrease the creation time of the global secondary index.
Behind the scenes, DynamoDB builds the index in two phases:
**Resource Allocation**
DynamoDB allocates the compute and storage resources that are needed for building the index.
During the resource allocation phase, the `IndexStatus` attribute is `CREATING` and the `Backfilling` attribute is false. Use the `DescribeTable` operation to retrieve the status of a table and all of its secondary indexes.
While the index is in the resource allocation phase, you can't delete the index or delete its parent table. You also can't modify the provisioned throughput of the index or the table. You cannot add or delete other indexes on the table. However, you can modify the provisioned throughput of these other indexes.
**Backfilling**
For each item in the table, DynamoDB determines which set of attributes to write to the index based on its projection (`KEYS_ONLY`, `INCLUDE`, or `ALL`). It then writes these attributes to the index. During the backfill phase, DynamoDB tracks the items that are being added, deleted, or updated in the table. The attributes from these items are also added, deleted, or updated in the index as appropriate.
During the backfilling phase, the `IndexStatus` attribute is set to `CREATING`, and the `Backfilling` attribute is true. Use the `DescribeTable` operation to retrieve the status of a table and all of its secondary indexes.
While the index is backfilling, you cannot delete its parent table. However, you can still delete the index or modify the provisioned throughput of the table and any of its global secondary indexes.
During the backfilling phase, some writes of violating items might succeed while others are rejected. After backfilling, all writes to items that violate the new index's key schema are rejected. We recommend that you run the Violation Detector tool after the backfill phase finishes to detect and resolve any key violations that might have occurred. For more information, see [Detecting and correcting index key violations in DynamoDB](GSI.OnlineOps.ViolationDetection.md).
While the resource allocation and backfilling phases are in progress, the index is in the `CREATING` state. During this time, DynamoDB performs read operations on the table. You are not charged for read operations from the base table to populate the global secondary index.
When the index build is complete, its status changes to `ACTIVE`. You can't `Query` or `Scan` the index until it is `ACTIVE`.
**Note**
In some cases, DynamoDB can't write data from the table to the index because of index key violations. This can occur if:
The data type of an attribute value does not match the data type of an index key schema data type.
The size of an attribute exceeds the maximum length for an index key attribute.
An index key attribute has an empty String or empty Binary attribute value.
Index key violations do not interfere with global secondary index creation. However, when the index becomes `ACTIVE`, the violating keys are not present in the index.
DynamoDB provides a standalone tool for finding and resolving these issues. For more information, see [Detecting and correcting index key violations in DynamoDB](GSI.OnlineOps.ViolationDetection.md).
### Adding a Global Secondary Index to a large table
The time required for building a global secondary index depends on several factors, such as the following:
+ The size of the table
+ The number of items in the table that qualify for inclusion in the index
+ The number of attributes projected into the index
+ Write activity on the main table during index builds
If you are adding a global secondary index to a very large table, it might take a long time for the creation process to complete. To monitor progress and determine whether the index has sufficient write capacity, consult the following Amazon CloudWatch metrics:
+ `OnlineIndexPercentageProgress`
For more information about CloudWatch metrics related to DynamoDB, see [DynamoDB metrics](metrics-dimensions.md#dynamodb-metrics).
**Important**
You may need to allowlist very large tables before creating or updating a Global Secondary Index. Please reach out to AWS Support to allowlist your tables.
While an index is being backfilled, DynamoDB uses internal system capacity to read from the table. This is to minimize the impact of the index creation and to assure that your table does not run out of read capacity.
## Deleting a Global Secondary Index
If you no longer need a global secondary index, you can delete it using the `UpdateTable` operation.
You can delete only one global secondary index per `UpdateTable` operation.
While the global secondary index is being deleted, there is no effect on any read or write activity in the parent table. While the deletion is in progress, you can still modify the provisioned throughput on other indexes.
**Note**
When you delete a table using the `DeleteTable` action, all of the global secondary indexes on that table are also deleted.
Your account will not be charged for the delete operation of the global secondary index.
## Modifying a Global Secondary Index during creation
While an index is being built, you can use the `DescribeTable` operation to determine what phase it is in. The description for the index includes a Boolean attribute, `Backfilling`, to indicate whether DynamoDB is currently loading the index with items from the table. If `Backfilling` is true, the resource allocation phase is complete and the index is now backfilling.
During the backfilling phase, you can delete the index that is being created. During this phase, you can't add or delete other indexes on the table.
**Note**
For indexes that were created as part of a `CreateTable` operation, the `Backfilling` attribute does not appear in the `DescribeTable` output. For more information, see [Phases of index creation](#GSI.OnlineOps.Creating.Phases).
# Detecting and correcting index key violations in DynamoDB
During the backfill phase of global secondary index creation, Amazon DynamoDB examines each item in the table to determine whether it is eligible for inclusion in the index. Some items might not be eligible because they would cause index key violations. In these cases, the items remain in the table, but the index doesn't have a corresponding entry for that item.
An *index key violation* occurs in the following situations:
+ There is a data type mismatch between an attribute value and the index key schema data type. For example, suppose that one of the items in the `GameScores` table had a `TopScore` value of type `String`. If you added a global secondary index with a partition key of `TopScore`, of type `Number`, the item from the table would violate the index key.
+ An attribute value from the table exceeds the maximum length for an index key attribute. The maximum length of a partition key is 2048 bytes, and the maximum length of a sort key is 1024 bytes. If any of the corresponding attribute values in the table exceed these limits, the item from the table would violate the index key.
**Note**
If a String or Binary attribute value is set for an attribute that is used as an index key, then the attribute value must have a length greater than zero;, otherwise, the item from the table would violate the index key.
This tool does not flag this index key violation, at this time.
If an index key violation occurs, the backfill phase continues without interruption. However, any violating items are not included in the index. After the backfill phase completes, all writes to items that violate the new index's key schema will be rejected.
To identify and fix attribute values in a table that violate an index key, use the Violation Detector tool. To run Violation Detector, you create a configuration file that specifies the name of a table to be scanned, the names and data types of the global secondary index partition key and sort key, and what actions to take if any index key violations are found. Violation Detector can run in one of two different modes:
+ **Detection mode** — Detect index key violations. Use detection mode to report the items in the table that would cause key violations in a global secondary index. (You can optionally request that these violating table items be deleted immediately when they are found.) The output from detection mode is written to a file, which you can use for further analysis.
+ **Correction mode** — Correct index key violations. In correction mode, Violation Detector reads an input file with the same format as the output file from detection mode. Correction mode reads the records from the input file and, for each record, it either deletes or updates the corresponding items in the table. (Note that if you choose to update the items, you must edit the input file and set appropriate values for these updates.)
## Downloading and running Violation Detector
Violation Detector is available as an executable Java Archive (`.jar` file), and runs on Windows, macOS, or Linux computers. Violation Detector requires Java 1.7 (or later) and Apache Maven.
+ [Download violation detector from GitHub](https://github.com/awslabs/dynamodb-online-index-violation-detector)
Follow the instructions in the `README.md` file to download and install Violation Detector using Maven.
To start Violation Detector, go to the directory where you have built `ViolationDetector.java` and enter the following command.
```
java -jar ViolationDetector.jar [options]
```
The Violation Detector command line accepts the following options:
+ `-h | --help` — Prints a usage summary and options for Violation Detector.
+ `-p | --configFilePath` `value` — The fully qualified name of a Violation Detector configuration file. For more information, see [The Violation Detector configuration file](#GSI.OnlineOps.ViolationDetection.ConfigFile).
+ `-t | --detect` `value` — Detect index key violations in the table, and write them to the Violation Detector output file. If the value of this parameter is set to `keep`, items with key violations are not modified. If the value is set to `delete`, items with key violations are deleted from the table.
+ `-c | --correct` `value` — Read index key violations from an input file, and take corrective actions on the items in the table. If the value of this parameter is set to `update`, items with key violations are updated with new, non-violating values. If the value is set to `delete`, items with key violations are deleted from the table.
## The Violation Detector configuration file
At runtime, the Violation Detector tool requires a configuration file. The parameters in this file determine which DynamoDB resources that Violation Detector can access, and how much provisioned throughput it can consume. The following table describes these parameters.
****
| Parameter name | Description | Required? |
| --- | --- | --- |
| `awsCredentialsFile` | The fully qualified name of a file containing your AWS credentials. The credentials file must be in the following format: accessKey = access_key_id_goes_here
secretKey = secret_key_goes_here
| Yes |
| `dynamoDBRegion` | The AWS Region in which the table resides. For example: `us-west-2`. | Yes |
| `tableName` | The name of the DynamoDB table to be scanned. | Yes |
| `gsiHashKeyName` | The name of the index partition key. | Yes |
| `gsiHashKeyType` | The data type of the index partition key—`String`, `Number`, or `Binary`: `S \| N \| B` | Yes |
| `gsiRangeKeyName` | The name of the index sort key. Do not specify this parameter if the index only has a simple primary key (partition key). | No |
| `gsiRangeKeyType` | The data type of the index sort key—`String`, `Number`, or `Binary`: `S \| N \| B` Do not specify this parameter if the index only has a simple primary key (partition key). | No |
| `recordDetails` | Whether to write the full details of index key violations to the output file. If set to `true` (the default), full information about the violating items is reported. If set to `false`, only the number of violations is reported. | No |
| `recordGsiValueInViolationRecord` | Whether to write the values of the violating index keys to the output file. If set to `true` (default), the key values are reported. If set to `false`, the key values are not reported. | No |
| `detectionOutputPath` | The full path of the Violation Detector output file. This parameter supports writing to a local directory or to Amazon Simple Storage Service (Amazon S3). The following are examples: `detectionOutputPath = ``//local/path/filename.csv` `detectionOutputPath = ``s3://bucket/filename.csv` Information in the output file appears in comma-separated values (CSV) format. If you don't set `detectionOutputPath`, the output file is named `violation_detection.csv` and is written to your current working directory. | No |
| `numOfSegments` | The number of parallel scan segments to be used when Violation Detector scans the table. The default value is 1, meaning that the table is scanned in a sequential manner. If the value is 2 or higher, then Violation Detector divides the table into that many logical segments and an equal number of scan threads. The maximum setting for `numOfSegments` is 4096.For larger tables, a parallel scan is generally faster than a sequential scan. In addition, if the table is large enough to span multiple partitions, a parallel scan distributes its read activity evenly across multiple partitions.For more information about parallel scans in DynamoDB, see [Parallel scan](Scan.md#Scan.ParallelScan). | No |
| `numOfViolations` | The upper limit of index key violations to write to the output file. If set to `-1` (the default), the entire table is scanned. If set to a positive integer, then Violation Detector stops after it encounters that number of violations. | No |
| `numOfRecords` | The number of items in the table to be scanned. If set to -1 (the default), the entire table is scanned. If set to a positive integer, Violation Detector stops after it scans that many items in the table. | No |
| `readWriteIOPSPercent` | Regulates the percentage of provisioned read capacity units that are consumed during the table scan. Valid values range from `1` to `100`. The default value (`25`) means that Violation Detector will consume no more than 25% of the table's provisioned read throughput. | No |
| `correctionInputPath` | The full path of the Violation Detector correction input file. If you run Violation Detector in correction mode, the contents of this file are used to modify or delete data items in the table that violate the global secondary index. The format of the `correctionInputPath` file is the same as that of the `detectionOutputPath` file. This lets you process the output from detection mode as input in correction mode. | No |
| `correctionOutputPath` | The full path of the Violation Detector correction output file. This file is created only if there are update errors. This parameter supports writing to a local directory or to Amazon S3. The following are examples: `correctionOutputPath = ``//local/path/filename.csv` `correctionOutputPath = ``s3://bucket/filename.csv` Information in the output file appears in CSV format. If you don't set `correctionOutputPath`, the output file is named `violation_update_errors.csv` and is written to your current working directory. | No |
## Detection
To detect index key violations, use Violation Detector with the `--detect` command line option. To show how this option works, consider the `ProductCatalog` table. The following is a list of items in the table. Only the primary key (`Id`) and the `Price` attribute are shown.
****
| Id (primary key) | Price |
| --- | --- |
| 101 | 5 |
| 102 | 20 |
| 103 | 200 |
| 201 | 100 |
| 202 | 200 |
| 203 | 300 |
| 204 | 400 |
| 205 | 500 |
All of the values for `Price` are of type `Number`. However, because DynamoDB is schemaless, it is possible to add an item with a non-numeric `Price`. For example, suppose that you add another item to the `ProductCatalog` table.
****
| Id (primary key) | Price |
| --- | --- |
| 999 | "Hello" |
The table now has a total of nine items.
Now you add a new global secondary index to the table: `PriceIndex`. The primary key for this index is a partition key, `Price`, which is of type `Number`. After the index has been built, it will contain eight items—but the `ProductCatalog` table has nine items. The reason for this discrepancy is that the value `"Hello"` is of type `String`, but `PriceIndex` has a primary key of type `Number`. The `String` value violates the global secondary index key, so it is not present in the index.
To use Violation Detector in this scenario, you first create a configuration file such as the following.
```
# Properties file for violation detection tool configuration.
# Parameters that are not specified will use default values.
awsCredentialsFile = /home/alice/credentials.txt
dynamoDBRegion = us-west-2
tableName = ProductCatalog
gsiHashKeyName = Price
gsiHashKeyType = N
recordDetails = true
recordGsiValueInViolationRecord = true
detectionOutputPath = ./gsi_violation_check.csv
correctionInputPath = ./gsi_violation_check.csv
numOfSegments = 1
readWriteIOPSPercent = 40
```
Next, you run Violation Detector as in the following example.
```
$ java -jar ViolationDetector.jar --configFilePath config.txt --detect keep
Violation detection started: sequential scan, Table name: ProductCatalog, GSI name: PriceIndex
Progress: Items scanned in total: 9, Items scanned by this thread: 9, Violations found by this thread: 1, Violations deleted by this thread: 0
Violation detection finished: Records scanned: 9, Violations found: 1, Violations deleted: 0, see results at: ./gsi_violation_check.csv
```
If the `recordDetails` config parameter is set to `true`, Violation Detector writes details of each violation to the output file, as in the following example.
```
Table Hash Key,GSI Hash Key Value,GSI Hash Key Violation Type,GSI Hash Key Violation Description,GSI Hash Key Update Value(FOR USER),Delete Blank Attributes When Updating?(Y/N)
999,"{""S"":""Hello""}",Type Violation,Expected: N Found: S,,
```
The output file is in CSV format. The first line in the file is a header, followed by one record per item that violates the index key. The fields of these violation records are as follows:
+ **Table hash key** — The partition key value of the item in the table.
+ **Table range key** — The sort key value of the item in the table.
+ **GSI hash key value** — The partition key value of the global secondary index.
+ **GSI hash key violation type** — Either `Type Violation` or `Size Violation`.
+ **GSI hash key violation description** — The cause of the violation.
+ **GSI hash key update Value(FOR USER)** — In correction mode, a new user-supplied value for the attribute.
+ **GSI range key value** — The sort key value of the global secondary index.
+ **GSI range key violation type** — Either `Type Violation` or `Size Violation`.
+ **GSI range key violation description** — The cause of the violation.
+ **GSI range key update Value(FOR USER)** — In correction mode, a new user-supplied value for the attribute.
+ **Delete blank attribute when Updating(Y/N)** — In correction mode, determines whether to delete (Y) or keep (N) the violating item in the table—but only if either of the following fields are blank:
+ `GSI Hash Key Update Value(FOR USER)`
+ `GSI Range Key Update Value(FOR USER)`
If either of these fields are non-blank, then `Delete Blank Attribute When Updating(Y/N)` has no effect.
**Note**
The output format might vary, depending on the configuration file and command line options. For example, if the table has a simple primary key (without a sort key), no sort key fields will be present in the output.
The violation records in the file might not be in sorted order.
## Correction
To correct index key violations, use Violation Detector with the `--correct` command line option. In correction mode, Violation Detector reads the input file specified by the `correctionInputPath` parameter. This file has the same format as the `detectionOutputPath` file, so that you can use the output from detection as input for correction.
Violation Detector provides two different ways to correct index key violations:
+ **Delete violations** — Delete the table items that have violating attribute values.
+ **Update violations** — Update the table items, replacing the violating attributes with non-violating values.
In either case, you can use the output file from detection mode as input for correction mode.
Continuing with the `ProductCatalog` example, suppose that you want to delete the violating item from the table. To do this, you use the following command line.
```
$ java -jar ViolationDetector.jar --configFilePath config.txt --correct delete
```
At this point, you are asked to confirm whether you want to delete the violating items.
```
Are you sure to delete all violations on the table?y/n
y
Confirmed, will delete violations on the table...
Violation correction from file started: Reading records from file: ./gsi_violation_check.csv, will delete these records from table.
Violation correction from file finished: Violations delete: 1, Violations Update: 0
```
Now both `ProductCatalog` and `PriceIndex` have the same number of items.
# Working with Global Secondary Indexes: Java
You can use the AWS SDK for Java Document API to create an Amazon DynamoDB table with one or more global secondary indexes, describe the indexes on the table, and perform queries using the indexes.
The following are the common steps for table operations.
1. Create an instance of the `DynamoDB` class.
1. Provide the required and optional parameters for the operation by creating the corresponding request objects.
1. Call the appropriate method provided by the client that you created in the preceding step.
**Topics**
+ [Create a table with a Global Secondary Index](#GSIJavaDocumentAPI.CreateTableWithIndex)
+ [Describe a table with a Global Secondary Index](#GSIJavaDocumentAPI.DescribeTableWithIndex)
+ [Query a Global Secondary Index](#GSIJavaDocumentAPI.QueryAnIndex)
+ [Example: Global Secondary Indexes using the AWS SDK for Java document API](GSIJavaDocumentAPI.Example.md)
## Create a table with a Global Secondary Index
You can create global secondary indexes at the same time that you create a table. To do this, use `CreateTable` and provide your specifications for one or more global secondary indexes. The following Java code example creates a table to hold information about weather data. The partition key is `Location` and the sort key is `Date`. A global secondary index named `PrecipIndex` allows fast access to precipitation data for various locations.
The following are the steps to create a table with a global secondary index, using the DynamoDB document API.
1. Create an instance of the `DynamoDB` class.
1. Create an instance of the `CreateTableRequest` class to provide the request information.
You must provide the table name, its primary key, and the provisioned throughput values. For the global secondary index, you must provide the index name, its provisioned throughput settings, the attribute definitions for the index sort key, the key schema for the index, and the attribute projection.
1. Call the `createTable` method by providing the request object as a parameter.
The following Java code example demonstrates the preceding steps. The code creates a table (`WeatherData`) with a global secondary index (`PrecipIndex`). The index partition key is `Date` and its sort key is `Precipitation`. All of the table attributes are projected into the index. Users can query this index to obtain weather data for a particular date, optionally sorting the data by precipitation amount.
Because `Precipitation` is not a key attribute for the table, it is not required. However, `WeatherData` items without `Precipitation` do not appear in `PrecipIndex`.
```
AmazonDynamoDB client = AmazonDynamoDBClientBuilder.standard().build();
DynamoDB dynamoDB = new DynamoDB(client);
// Attribute definitions
ArrayList attributeDefinitions = new ArrayList();
attributeDefinitions.add(new AttributeDefinition()
.withAttributeName("Location")
.withAttributeType("S"));
attributeDefinitions.add(new AttributeDefinition()
.withAttributeName("Date")
.withAttributeType("S"));
attributeDefinitions.add(new AttributeDefinition()
.withAttributeName("Precipitation")
.withAttributeType("N"));
// Table key schema
ArrayList tableKeySchema = new ArrayList();
tableKeySchema.add(new KeySchemaElement()
.withAttributeName("Location")
.withKeyType(KeyType.HASH)); //Partition key
tableKeySchema.add(new KeySchemaElement()
.withAttributeName("Date")
.withKeyType(KeyType.RANGE)); //Sort key
// PrecipIndex
GlobalSecondaryIndex precipIndex = new GlobalSecondaryIndex()
.withIndexName("PrecipIndex")
.withProvisionedThroughput(new ProvisionedThroughput()
.withReadCapacityUnits((long) 10)
.withWriteCapacityUnits((long) 1))
.withProjection(new Projection().withProjectionType(ProjectionType.ALL));
ArrayList indexKeySchema = new ArrayList();
indexKeySchema.add(new KeySchemaElement()
.withAttributeName("Date")
.withKeyType(KeyType.HASH)); //Partition key
indexKeySchema.add(new KeySchemaElement()
.withAttributeName("Precipitation")
.withKeyType(KeyType.RANGE)); //Sort key
precipIndex.setKeySchema(indexKeySchema);
CreateTableRequest createTableRequest = new CreateTableRequest()
.withTableName("WeatherData")
.withProvisionedThroughput(new ProvisionedThroughput()
.withReadCapacityUnits((long) 5)
.withWriteCapacityUnits((long) 1))
.withAttributeDefinitions(attributeDefinitions)
.withKeySchema(tableKeySchema)
.withGlobalSecondaryIndexes(precipIndex);
Table table = dynamoDB.createTable(createTableRequest);
System.out.println(table.getDescription());
```
You must wait until DynamoDB creates the table and sets the table status to `ACTIVE`. After that, you can begin putting data items into the table.
## Describe a table with a Global Secondary Index
To get information about global secondary indexes on a table, use `DescribeTable`. For each index, you can access its name, key schema, and projected attributes.
The following are the steps to access global secondary index information a table.
1. Create an instance of the `DynamoDB` class.
1. Create an instance of the `Table` class to represent the index you want to work with.
1. Call the `describe` method on the `Table` object.
The following Java code example demonstrates the preceding steps.
**Example**
```
AmazonDynamoDB client = AmazonDynamoDBClientBuilder.standard().build();
DynamoDB dynamoDB = new DynamoDB(client);
Table table = dynamoDB.getTable("WeatherData");
TableDescription tableDesc = table.describe();
Iterator gsiIter = tableDesc.getGlobalSecondaryIndexes().iterator();
while (gsiIter.hasNext()) {
GlobalSecondaryIndexDescription gsiDesc = gsiIter.next();
System.out.println("Info for index "
+ gsiDesc.getIndexName() + ":");
Iterator kseIter = gsiDesc.getKeySchema().iterator();
while (kseIter.hasNext()) {
KeySchemaElement kse = kseIter.next();
System.out.printf("\t%s: %s\n", kse.getAttributeName(), kse.getKeyType());
}
Projection projection = gsiDesc.getProjection();
System.out.println("\tThe projection type is: "
+ projection.getProjectionType());
if (projection.getProjectionType().toString().equals("INCLUDE")) {
System.out.println("\t\tThe non-key projected attributes are: "
+ projection.getNonKeyAttributes());
}
}
```
## Query a Global Secondary Index
You can use `Query` on a global secondary index, in much the same way you `Query` a table. You need to specify the index name, the query criteria for the index partition key and sort key (if present), and the attributes that you want to return. In this example, the index is `PrecipIndex`, which has a partition key of `Date` and a sort key of `Precipitation`. The index query returns all of the weather data for a particular date, where the precipitation is greater than zero.
The following are the steps to query a global secondary index using the AWS SDK for Java Document API.
1. Create an instance of the `DynamoDB` class.
1. Create an instance of the `Table` class to represent the index you want to work with.
1. Create an instance of the `Index` class for the index you want to query.
1. Call the `query` method on the `Index` object.
The attribute name `Date` is a DynamoDB reserved word. Therefore, you must use an expression attribute name as a placeholder in the `KeyConditionExpression`.
The following Java code example demonstrates the preceding steps.
**Example**
```
AmazonDynamoDB client = AmazonDynamoDBClientBuilder.standard().build();
DynamoDB dynamoDB = new DynamoDB(client);
Table table = dynamoDB.getTable("WeatherData");
Index index = table.getIndex("PrecipIndex");
QuerySpec spec = new QuerySpec()
.withKeyConditionExpression("#d = :v_date and Precipitation = :v_precip")
.withNameMap(new NameMap()
.with("#d", "Date"))
.withValueMap(new ValueMap()
.withString(":v_date","2013-08-10")
.withNumber(":v_precip",0));
ItemCollection items = index.query(spec);
Iterator- iter = items.iterator();
while (iter.hasNext()) {
System.out.println(iter.next().toJSONPretty());
}
```
# Example: Global Secondary Indexes using the AWS SDK for Java document API
The following Java 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 `Title` and the sort key is `IssueId`. 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. It then queries the data using the global secondary indexes. Finally, the program deletes the `Issues` table.
For step-by-step instructions for testing the following example, see [Java code examples](CodeSamples.Java.md).
**Example**
```
package com.amazonaws.codesamples.document;
import java.util.ArrayList;
import java.util.Iterator;
import com.amazonaws.services.dynamodbv2.AmazonDynamoDB;
import com.amazonaws.services.dynamodbv2.AmazonDynamoDBClientBuilder;
import com.amazonaws.services.dynamodbv2.document.DynamoDB;
import com.amazonaws.services.dynamodbv2.document.Index;
import com.amazonaws.services.dynamodbv2.document.Item;
import com.amazonaws.services.dynamodbv2.document.ItemCollection;
import com.amazonaws.services.dynamodbv2.document.QueryOutcome;
import com.amazonaws.services.dynamodbv2.document.Table;
import com.amazonaws.services.dynamodbv2.document.spec.QuerySpec;
import com.amazonaws.services.dynamodbv2.document.utils.ValueMap;
import com.amazonaws.services.dynamodbv2.model.AttributeDefinition;
import com.amazonaws.services.dynamodbv2.model.CreateTableRequest;
import com.amazonaws.services.dynamodbv2.model.GlobalSecondaryIndex;
import com.amazonaws.services.dynamodbv2.model.KeySchemaElement;
import com.amazonaws.services.dynamodbv2.model.KeyType;
import com.amazonaws.services.dynamodbv2.model.Projection;
import com.amazonaws.services.dynamodbv2.model.ProvisionedThroughput;
public class DocumentAPIGlobalSecondaryIndexExample {
static AmazonDynamoDB client = AmazonDynamoDBClientBuilder.standard().build();
static DynamoDB dynamoDB = new DynamoDB(client);
public static String tableName = "Issues";
public static void main(String[] args) throws Exception {
createTable();
loadData();
queryIndex("CreateDateIndex");
queryIndex("TitleIndex");
queryIndex("DueDateIndex");
deleteTable(tableName);
}
public static void createTable() {
// Attribute definitions
ArrayList attributeDefinitions = new ArrayList();
attributeDefinitions.add(new AttributeDefinition().withAttributeName("IssueId").withAttributeType("S"));
attributeDefinitions.add(new AttributeDefinition().withAttributeName("Title").withAttributeType("S"));
attributeDefinitions.add(new AttributeDefinition().withAttributeName("CreateDate").withAttributeType("S"));
attributeDefinitions.add(new AttributeDefinition().withAttributeName("DueDate").withAttributeType("S"));
// Key schema for table
ArrayList tableKeySchema = new ArrayList();
tableKeySchema.add(new KeySchemaElement().withAttributeName("IssueId").withKeyType(KeyType.HASH)); // Partition
// key
tableKeySchema.add(new KeySchemaElement().withAttributeName("Title").withKeyType(KeyType.RANGE)); // Sort
// key
// Initial provisioned throughput settings for the indexes
ProvisionedThroughput ptIndex = new ProvisionedThroughput().withReadCapacityUnits(1L)
.withWriteCapacityUnits(1L);
// CreateDateIndex
GlobalSecondaryIndex createDateIndex = new GlobalSecondaryIndex().withIndexName("CreateDateIndex")
.withProvisionedThroughput(ptIndex)
.withKeySchema(new KeySchemaElement().withAttributeName("CreateDate").withKeyType(KeyType.HASH), // Partition
// key
new KeySchemaElement().withAttributeName("IssueId").withKeyType(KeyType.RANGE)) // Sort
// key
.withProjection(
new Projection().withProjectionType("INCLUDE").withNonKeyAttributes("Description", "Status"));
// TitleIndex
GlobalSecondaryIndex titleIndex = new GlobalSecondaryIndex().withIndexName("TitleIndex")
.withProvisionedThroughput(ptIndex)
.withKeySchema(new KeySchemaElement().withAttributeName("Title").withKeyType(KeyType.HASH), // Partition
// key
new KeySchemaElement().withAttributeName("IssueId").withKeyType(KeyType.RANGE)) // Sort
// key
.withProjection(new Projection().withProjectionType("KEYS_ONLY"));
// DueDateIndex
GlobalSecondaryIndex dueDateIndex = new GlobalSecondaryIndex().withIndexName("DueDateIndex")
.withProvisionedThroughput(ptIndex)
.withKeySchema(new KeySchemaElement().withAttributeName("DueDate").withKeyType(KeyType.HASH)) // Partition
// key
.withProjection(new Projection().withProjectionType("ALL"));
CreateTableRequest createTableRequest = new CreateTableRequest().withTableName(tableName)
.withProvisionedThroughput(
new ProvisionedThroughput().withReadCapacityUnits((long) 1).withWriteCapacityUnits((long) 1))
.withAttributeDefinitions(attributeDefinitions).withKeySchema(tableKeySchema)
.withGlobalSecondaryIndexes(createDateIndex, titleIndex, dueDateIndex);
System.out.println("Creating table " + tableName + "...");
dynamoDB.createTable(createTableRequest);
// Wait for table to become active
System.out.println("Waiting for " + tableName + " to become ACTIVE...");
try {
Table table = dynamoDB.getTable(tableName);
table.waitForActive();
} catch (InterruptedException e) {
e.printStackTrace();
}
}
public static void queryIndex(String indexName) {
Table table = dynamoDB.getTable(tableName);
System.out.println("\n***********************************************************\n");
System.out.print("Querying index " + indexName + "...");
Index index = table.getIndex(indexName);
ItemCollection items = null;
QuerySpec querySpec = new QuerySpec();
if (indexName == "CreateDateIndex") {
System.out.println("Issues filed on 2013-11-01");
querySpec.withKeyConditionExpression("CreateDate = :v_date and begins_with(IssueId, :v_issue)")
.withValueMap(new ValueMap().withString(":v_date", "2013-11-01").withString(":v_issue", "A-"));
items = index.query(querySpec);
} else if (indexName == "TitleIndex") {
System.out.println("Compilation errors");
querySpec.withKeyConditionExpression("Title = :v_title and begins_with(IssueId, :v_issue)")
.withValueMap(
new ValueMap().withString(":v_title", "Compilation error").withString(":v_issue", "A-"));
items = index.query(querySpec);
} else if (indexName == "DueDateIndex") {
System.out.println("Items that are due on 2013-11-30");
querySpec.withKeyConditionExpression("DueDate = :v_date")
.withValueMap(new ValueMap().withString(":v_date", "2013-11-30"));
items = index.query(querySpec);
} else {
System.out.println("\nNo valid index name provided");
return;
}
Iterator
- iterator = items.iterator();
System.out.println("Query: printing results...");
while (iterator.hasNext()) {
System.out.println(iterator.next().toJSONPretty());
}
}
public static void deleteTable(String tableName) {
System.out.println("Deleting table " + tableName + "...");
Table table = dynamoDB.getTable(tableName);
table.delete();
// Wait for table to be deleted
System.out.println("Waiting for " + tableName + " to be deleted...");
try {
table.waitForDelete();
} catch (InterruptedException e) {
e.printStackTrace();
}
}
public static void loadData() {
System.out.println("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");
}
public static void putItem(
String issueId, String title, String description, String createDate, String lastUpdateDate, String dueDate,
Integer priority, String status) {
Table table = dynamoDB.getTable(tableName);
Item item = new Item().withPrimaryKey("IssueId", issueId).withString("Title", title)
.withString("Description", description).withString("CreateDate", createDate)
.withString("LastUpdateDate", lastUpdateDate).withString("DueDate", dueDate)
.withNumber("Priority", priority).withString("Status", status);
table.putItem(item);
}
}
```
# Working with Global Secondary Indexes: .NET
You can use the AWS SDK for .NET low-level API to create an Amazon DynamoDB table with one or more global secondary indexes, describe the indexes on the table, and perform queries using the indexes. These operations map to the corresponding DynamoDB operations. For more information, see the [Amazon DynamoDB API Reference](https://docs.aws.amazon.com/amazondynamodb/latest/APIReference/).
The following are the common steps for table operations using the .NET low-level API.
1. Create an instance of the `AmazonDynamoDBClient` class.
1. Provide the required and optional parameters for the operation by creating the corresponding request objects.
For example, create a `CreateTableRequest` object to create a table and `QueryRequest` object to query a table or an index.
1. Run the appropriate method provided by the client that you created in the preceding step.
**Topics**
+ [Create a table with a Global Secondary Index](#GSILowLevelDotNet.CreateTableWithIndex)
+ [Describe a table with a Global Secondary Index](#GSILowLevelDotNet.DescribeTableWithIndex)
+ [Query a Global Secondary Index](#GSILowLevelDotNet.QueryAnIndex)
+ [Example: Global Secondary Indexes using the AWS SDK for .NET low-level API](GSILowLevelDotNet.Example.md)
## Create a table with a Global Secondary Index
You can create global secondary indexes at the same time that you create a table. To do this, use `CreateTable` and provide your specifications for one or more global secondary indexes. The following C\$1 code example creates a table to hold information about weather data. The partition key is `Location` and the sort key is `Date`. A global secondary index named `PrecipIndex` allows fast access to precipitation data for various locations.
The following are the steps to create a table with a global secondary index, using the .NET low-level API.
1. Create an instance of the `AmazonDynamoDBClient` class.
1. Create an instance of the `CreateTableRequest` class to provide the request information.
You must provide the table name, its primary key, and the provisioned throughput values. For the global secondary index, you must provide the index name, its provisioned throughput settings, the attribute definitions for the index sort key, the key schema for the index, and the attribute projection.
1. Run the `CreateTable` method by providing the request object as a parameter.
The following C\$1 code example demonstrates the preceding steps. The code creates a table (`WeatherData`) with a global secondary index (`PrecipIndex`). The index partition key is `Date` and its sort key is `Precipitation`. All of the table attributes are projected into the index. Users can query this index to obtain weather data for a particular date, optionally sorting the data by precipitation amount.
Because `Precipitation` is not a key attribute for the table, it is not required. However, `WeatherData` items without `Precipitation` do not appear in `PrecipIndex`.
```
client = new AmazonDynamoDBClient();
string tableName = "WeatherData";
// Attribute definitions
var attributeDefinitions = new List()
{
{new AttributeDefinition{
AttributeName = "Location",
AttributeType = "S"}},
{new AttributeDefinition{
AttributeName = "Date",
AttributeType = "S"}},
{new AttributeDefinition(){
AttributeName = "Precipitation",
AttributeType = "N"}
}
};
// Table key schema
var tableKeySchema = new List()
{
{new KeySchemaElement {
AttributeName = "Location",
KeyType = "HASH"}}, //Partition key
{new KeySchemaElement {
AttributeName = "Date",
KeyType = "RANGE"} //Sort key
}
};
// PrecipIndex
var precipIndex = new GlobalSecondaryIndex
{
IndexName = "PrecipIndex",
ProvisionedThroughput = new ProvisionedThroughput
{
ReadCapacityUnits = (long)10,
WriteCapacityUnits = (long)1
},
Projection = new Projection { ProjectionType = "ALL" }
};
var indexKeySchema = new List {
{new KeySchemaElement { AttributeName = "Date", KeyType = "HASH"}}, //Partition key
{new KeySchemaElement{AttributeName = "Precipitation",KeyType = "RANGE"}} //Sort key
};
precipIndex.KeySchema = indexKeySchema;
CreateTableRequest createTableRequest = new CreateTableRequest
{
TableName = tableName,
ProvisionedThroughput = new ProvisionedThroughput
{
ReadCapacityUnits = (long)5,
WriteCapacityUnits = (long)1
},
AttributeDefinitions = attributeDefinitions,
KeySchema = tableKeySchema,
GlobalSecondaryIndexes = { precipIndex }
};
CreateTableResponse response = client.CreateTable(createTableRequest);
Console.WriteLine(response.CreateTableResult.TableDescription.TableName);
Console.WriteLine(response.CreateTableResult.TableDescription.TableStatus);
```
You must wait until DynamoDB creates the table and sets the table status to `ACTIVE`. After that, you can begin putting data items into the table.
## Describe a table with a Global Secondary Index
To get information about global secondary indexes on a table, use `DescribeTable`. For each index, you can access its name, key schema, and projected attributes.
The following are the steps to access global secondary index information for a table using the .NET low-level API.
1. Create an instance of the `AmazonDynamoDBClient` class.
1. Run the `describeTable` method by providing the request object as a parameter.
Create an instance of the `DescribeTableRequest` class to provide the request information. You must provide the table name.
The following C\$1 code example demonstrates the preceding steps.
**Example**
```
client = new AmazonDynamoDBClient();
string tableName = "WeatherData";
DescribeTableResponse response = client.DescribeTable(new DescribeTableRequest { TableName = tableName});
List globalSecondaryIndexes =
response.DescribeTableResult.Table.GlobalSecondaryIndexes;
// This code snippet will work for multiple indexes, even though
// there is only one index in this example.
foreach (GlobalSecondaryIndexDescription gsiDescription in globalSecondaryIndexes) {
Console.WriteLine("Info for index " + gsiDescription.IndexName + ":");
foreach (KeySchemaElement kse in gsiDescription.KeySchema) {
Console.WriteLine("\t" + kse.AttributeName + ": key type is " + kse.KeyType);
}
Projection projection = gsiDescription.Projection;
Console.WriteLine("\tThe projection type is: " + projection.ProjectionType);
if (projection.ProjectionType.ToString().Equals("INCLUDE")) {
Console.WriteLine("\t\tThe non-key projected attributes are: "
+ projection.NonKeyAttributes);
}
}
```
## Query a Global Secondary Index
You can use `Query` on a global secondary index, in much the same way you `Query` a table. You need to specify the index name, the query criteria for the index partition key and sort key (if present), and the attributes that you want to return. In this example, the index is `PrecipIndex`, which has a partition key of `Date` and a sort key of `Precipitation`. The index query returns all of the weather data for a particular date, where the precipitation is greater than zero.
The following are the steps to query a global secondary index using the .NET low-level API.
1. Create an instance of the `AmazonDynamoDBClient` class.
1. Create an instance of the `QueryRequest` class to provide the request information.
1. Run the `query` method by providing the request object as a parameter.
The attribute name `Date` is a DynamoDB reserved word. Therefore, you must use an expression attribute name as a placeholder in the `KeyConditionExpression`.
The following C\$1 code example demonstrates the preceding steps.
**Example**
```
client = new AmazonDynamoDBClient();
QueryRequest queryRequest = new QueryRequest
{
TableName = "WeatherData",
IndexName = "PrecipIndex",
KeyConditionExpression = "#dt = :v_date and Precipitation > :v_precip",
ExpressionAttributeNames = new Dictionary {
{"#dt", "Date"}
},
ExpressionAttributeValues = new Dictionary {
{":v_date", new AttributeValue { S = "2013-08-01" }},
{":v_precip", new AttributeValue { N = "0" }}
},
ScanIndexForward = true
};
var result = client.Query(queryRequest);
var items = result.Items;
foreach (var currentItem in items)
{
foreach (string attr in currentItem.Keys)
{
Console.Write(attr + "---> ");
if (attr == "Precipitation")
{
Console.WriteLine(currentItem[attr].N);
}
else
{
Console.WriteLine(currentItem[attr].S);
}
}
Console.WriteLine();
}
```
# Example: Global Secondary Indexes using the AWS SDK for .NET low-level API
The following C\$1 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 `Title` and the sort key is `IssueId`. 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. It then queries the data using the global secondary indexes. Finally, the program deletes the `Issues` table.
For step-by-step instructions for testing the following sample, see [.NET code examples](CodeSamples.DotNet.md).
**Example**
```
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()
{
{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() {
{
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 item = new Dictionary();
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 expressionAttributeValues = new Dictionary();
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;
}
}
}
}
}
```
# Working with Global Secondary Indexes in DynamoDB using AWS CLI
You can use the AWS CLI to create an Amazon DynamoDB table with one or more global secondary indexes, describe the indexes on the table, and perform queries using the indexes.
**Topics**
+ [Create a table with a Global Secondary Index](#GCICli.CreateTableWithIndex)
+ [Add a Global Secondary Index to an existing table](#GCICli.CreateIndexAfterTable)
+ [Describe a table with a Global Secondary Index](#GCICli.DescribeTableWithIndex)
+ [Query a Global Secondary Index](#GCICli.QueryAnIndex)
## Create a table with a Global Secondary Index
Global secondary indexes may be created at the same time you create a table. To do this, use the `create-table` parameter and provide your specifications for one or more global secondary indexes. The following example creates a table named `GameScores` with a global secondary index called `GameTitleIndex`. The base table has a partition key of `UserId` and a sort key of `GameTitle`, allowing you to find an individual user's best score for a specific game efficiently, whereas the GSI has a partition key of `GameTitle` and a sort key of `TopScore`, allowing you to quickly find the overall highest score for a particular game.
```
aws dynamodb create-table \
--table-name GameScores \
--attribute-definitions AttributeName=UserId,AttributeType=S \
AttributeName=GameTitle,AttributeType=S \
AttributeName=TopScore,AttributeType=N \
--key-schema AttributeName=UserId,KeyType=HASH \
AttributeName=GameTitle,KeyType=RANGE \
--provisioned-throughput ReadCapacityUnits=10,WriteCapacityUnits=5 \
--global-secondary-indexes \
"[
{
\"IndexName\": \"GameTitleIndex\",
\"KeySchema\": [{\"AttributeName\":\"GameTitle\",\"KeyType\":\"HASH\"},
{\"AttributeName\":\"TopScore\",\"KeyType\":\"RANGE\"}],
\"Projection\":{
\"ProjectionType\":\"INCLUDE\",
\"NonKeyAttributes\":[\"UserId\"]
},
\"ProvisionedThroughput\": {
\"ReadCapacityUnits\": 10,
\"WriteCapacityUnits\": 5
}
}
]"
```
You must wait until DynamoDB creates the table and sets the table status to `ACTIVE`. After that, you can begin putting data items into the table. You can use [describe-table](https://docs.aws.amazon.com/cli/latest/reference/dynamodb/describe-table.html) to determine the status of the table creation.
## Add a Global Secondary Index to an existing table
Global secondary indexes may also be added or modified after table creation. To do this, use the `update-table` parameter and provide your specifications for one or more global secondary indexes. The following example uses the same schema as the previous example, but assumes that the table has already been created and we're adding the GSI later.
```
aws dynamodb update-table \
--table-name GameScores \
--attribute-definitions AttributeName=TopScore,AttributeType=N \
--global-secondary-index-updates \
"[
{
\"Create\": {
\"IndexName\": \"GameTitleIndex\",
\"KeySchema\": [{\"AttributeName\":\"GameTitle\",\"KeyType\":\"HASH\"},
{\"AttributeName\":\"TopScore\",\"KeyType\":\"RANGE\"}],
\"Projection\":{
\"ProjectionType\":\"INCLUDE\",
\"NonKeyAttributes\":[\"UserId\"]
}
}
}
]"
```
## Describe a table with a Global Secondary Index
To get information about Global Secondary Indexes on a table, use the `describe-table` parameter. For each index, you can access its name, key schema, and projected attributes.
```
aws dynamodb describe-table --table-name GameScores
```
## Query a Global Secondary Index
You can use the `query` operation on a global secondary index in much the same way that you `query` a table. You must specify the index name, the query criteria for the index sort key, and the attributes that you want to return. In this example, the index is `GameTitleIndex` and the index sort key is `GameTitle`.
The only attributes returned are those that have been projected into the index. You could modify this query to select non-key attributes too, but this would require table fetch activity that is relatively expensive. For more information about table fetches, see [Attribute projections](GSI.md#GSI.Projections).
```
aws dynamodb query --table-name GameScores\
--index-name GameTitleIndex \
--key-condition-expression "GameTitle = :v_game" \
--expression-attribute-values '{":v_game":{"S":"Alien Adventure"} }'
```