# Amazon DynamoDB: How it works The following sections provide an overview of Amazon DynamoDB service components and how they interact. **Topics** + [ # Cheat sheet for DynamoDB ](CheatSheet.md) + [ # Core components of Amazon DynamoDB ](HowItWorks.CoreComponents.md) + [ # DynamoDB API ](HowItWorks.API.md) + [ # Supported data types and naming rules in Amazon DynamoDB ](HowItWorks.NamingRulesDataTypes.md) + [ # DynamoDB table classes ](HowItWorks.TableClasses.md) + [ # Partitions and data distribution in DynamoDB ](HowItWorks.Partitions.md) + [ # Learn how to go from SQL to NoSQL ](SQLtoNoSQL.md) + [ # Amazon DynamoDB learning resources and tools ](AdditionalResources.md) # Cheat sheet for DynamoDB This cheat sheet provides a quick reference for working with Amazon DynamoDB and its various AWS SDKs. ## Initial setup 1. [Sign up for AWS](SettingUp.DynamoWebService.html#SettingUp.DynamoWebService.SignUpForAWS). 1. [Get an AWS access key](SettingUp.DynamoWebService.html#SettingUp.DynamoWebService.GetCredentials) to access DynamoDB programmatically. 1. [Configure your DynamoDB credentials](SettingUp.DynamoWebService.html#SettingUp.DynamoWebService.ConfigureCredentials). **See also:** + [Setting up DynamoDB (web service)](SettingUp.DynamoWebService.html) + [Getting started with DynamoDB](GettingStartedDynamoDB.html) + [Basic overview of core components](HowItWorks.CoreComponents.html) ## SDK or CLI Choose your preferred [SDK](sdk-general-information-section.html), or set up the [AWS CLI](/cli/latest/index.html). **Note** When you use the AWS CLI on Windows, a backslash (\$1) that is not inside a quote is treated as a carriage return. Also, you must escape any quotes and braces inside other quotes. For an example, see the **Windows** tab in "Create a table" in the next section. **See also:** + [AWS CLI with DynamoDB](Tools.CLI.html) + [Getting started with DynamoDB - step 2](getting-started-step-2.html) ## Basic actions This section provides code for basic DynamoDB tasks. For more information about these tasks, see [Getting started with DynamoDB and the AWS SDKs](GettingStarted.html). ### Create a table ------ #### [ Default ] ``` aws dynamodb create-table \ --table-name Music \ --attribute-definitions \ AttributeName=Artist,AttributeType=S \ AttributeName=SongTitle,AttributeType=S \ --key-schema AttributeName=Artist,KeyType=HASH AttributeName=SongTitle,KeyType=RANGE \ --billing-mode PAY_PER_REQUEST \ --table-class STANDARD ``` ------ #### [ Windows ] ``` aws dynamodb create-table ^ --table-name Music ^ --attribute-definitions ^ AttributeName=Artist,AttributeType=S ^ AttributeName=SongTitle,AttributeType=S ^ --key-schema AttributeName=Artist,KeyType=HASH AttributeName=SongTitle,KeyType=RANGE ^ --billing-mode PAY_PER_REQUEST ^ --table-class STANDARD ``` ------ ### Write item to a table ``` aws dynamodb put-item \ --table-name Music \ --item file://item.json ``` ### Read item from a table ``` aws dynamodb get-item \ --table-name Music \ --item file://item.json ``` ### Delete item from a table ``` aws dynamodb delete-item --table-name Music --key file://key.json ``` ### Query a table ``` aws dynamodb query --table-name Music --key-condition-expression "ArtistName=:Artist and SongName=:Songtitle" ``` ### Delete a table ``` aws dynamodb delete-table --table-name Music ``` ### List table names ``` aws dynamodb list-tables ``` ## Naming rules + All names must be encoded using UTF-8 and are case sensitive. + Table names and index names must be between 3 and 255 characters long, and can contain only the following characters: + `a-z` + `A-Z` + `0-9` + `_`(underscore) + `-`(hyphen) + `.`(dot) + Attribute names must be at least one character long, and less than 64 KB in size. For more information, see [Naming rules](HowItWorks.NamingRulesDataTypes.html). ## Service quota basics **Read and write units** + **Read capacity unit (RCU)** – One strongly consistent read per second, or two eventually consistent reads per second, for items up to 4 KB in size. + **Write capacity unit (WCU)** – One write per second, for items up to 1 KB in size. **Table limits** + **Table size** – There is no practical limit on table size. Tables are unconstrained in terms of the number of items or the number of bytes. + **Number of tables** – For any AWS account, there is an initial quota of 2,500 tables per AWS Region. + **Page size limit for query and scan** – There is a limit of 1 MB per page, per query or scan. If your query parameters or scan operation on a table result in more than 1 MB of data, DynamoDB returns the initial matching items. It also returns a `LastEvaluatedKey` property that you can use in a new request to read the next page. **Indexes** + **Local secondary indexes (LSIs)** – You can define a maximum of five local secondary indexes. LSIs are primarily useful when an index must have strong consistency with the base table. + **Global secondary indexes (GSIs)** – There is a default quota of 20 global secondary indexes per table. + **Projected secondary index attributes per table** – You can project a total of up to 100 attributes into all of a table's local and global secondary indexes. This only applies to user-specified projected attributes. **Partition keys** + The minimum length of a partition key value is 1 byte. The maximum length is 2048 bytes. + There is no practical limit on the number of distinct partition key values, for tables or for secondary indexes. + The minimum length of a sort key value is 1 byte. The maximum length is 1024 bytes. + In general, there is no practical limit on the number of distinct sort key values per partition key value. The exception is for tables with secondary indexes. For more information on secondary indexes, partition key design, and sort key design, see [Best practices](best-practices.html). **Limits for commonly used data types** + **String** – The length of a string is constrained by the maximum item size of 400 KB. Strings are Unicode with UTF-8 binary encoding. + **Number** – A number can have up to 38 digits of precision, and can be positive, negative, or zero. + **Binary** – The length of a binary is constrained by the maximum item size of 400 KB. Applications that work with binary attributes must encode the data in base64 encoding before sending it to DynamoDB. For a full list of supported data types, see [Data types](HowItWorks.NamingRulesDataTypes.html#HowItWorks.DataTypes). For more information, also see [Service quotas](ServiceQuotas.html#limits-items). ### Items, attributes, and expression parameters The maximum item size in DynamoDB is 400 KB, which includes both attribute name binary length (UTF-8 length) and attribute value binary lengths (UTF-8 length). The attribute name counts towards the size limit. There is no limit on the number of values in a list, map, or set, as long as the item that contains the values fits within the 400-KB item size limit. For expression parameters, the maximum length of any expression string is 4 KB. For more information about item size, attributes, and expression parameters, see [Service quotas](ServiceQuotas.html#limits-items). ## More information + [Security](security.html) + [Monitoring and logging](monitoring.html) + [Working with streams](streamsmain.html) + [Backups](Backup-and-Restore.html) and [Point-in-time recovery](Point-in-time-recovery.html) + [Integrating with other AWS services](OtherServices.html) + [API reference](/amazondynamodb/latest/APIReference/Welcome.html) + [Architecture Center: Database Best Practices](https://aws.amazon.com/architecture/databases/) + [Video tutorials](https://youtu.be/Mw8wCj0gkRc) + [DynamoDB forum](https://repost.aws/search/questions?globalSearch=dynamodb) # Core components of Amazon DynamoDB In DynamoDB, tables, items, and attributes are the core components that you work with. A *table* is a collection of *items*, and each item is a collection of *attributes*. DynamoDB uses primary keys to uniquely identify each item in a table. You can use DynamoDB Streams to capture data modification events in DynamoDB tables. There are limits in DynamoDB. For more information, see [Quotas in Amazon DynamoDB](ServiceQuotas.md). The following video will give you an introductory look at tables, items, and attributes. [Tables, items, and attributes](https://www.youtube.com/embed/Mw8wCj0gkRc) ## Tables, items, and attributes ![\[Each DynamoDB table contains zero or more items that are made of one or more attributes.\]](http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/images/HowItWorksTables-2024.png) The following are the basic DynamoDB components: + **Tables** – Similar to other database systems, DynamoDB stores data in tables. A *table* is a collection of data. For example, see the example table called *People* that you could use to store personal contact information about friends, family, or anyone else of interest. You could also have a *Cars* table to store information about vehicles that people drive. + **Items** – Each table contains zero or more items. An *item* is a group of attributes that is uniquely identifiable among all of the other items. In a *People* table, each item represents a person. For a *Cars* table, each item represents one vehicle. Items in DynamoDB are similar in many ways to rows, records, or tuples in other database systems. In DynamoDB, there is no limit to the number of items you can store in a table. + **Attributes** – Each item is composed of one or more attributes. An *attribute* is a fundamental data element, something that does not need to be broken down any further. For example, an item in a *People* table contains attributes called *PersonID*, *LastName*, *FirstName*, and so on. For a *Department* table, an item might have attributes such as *DepartmentID*, *Name*, *Manager*, and so on. Attributes in DynamoDB are similar in many ways to fields or columns in other database systems. The following diagram shows a table named *People* with some example items and attributes. ``` People { "PersonID": 101, "LastName": "Smith", "FirstName": "Fred", "Phone": "555-4321" } { "PersonID": 102, "LastName": "Jones", "FirstName": "Mary", "Address": { "Street": "123 Main", "City": "Anytown", "State": "OH", "ZIPCode": 12345 } } { "PersonID": 103, "LastName": "Stephens", "FirstName": "Howard", "Address": { "Street": "123 Main", "City": "London", "PostalCode": "ER3 5K8" }, "FavoriteColor": "Blue" } ``` Note the following about the *People* table: + Each item in the table has a unique identifier, or primary key, that distinguishes the item from all of the others in the table. In the *People* table, the primary key consists of one attribute (*PersonID*). + Other than the primary key, the *People* table is schemaless, which means that neither the attributes nor their data types need to be defined beforehand. Each item can have its own distinct attributes. + Most of the attributes are *scalar*, which means that they can have only one value. Strings and numbers are common examples of scalars. + Some of the items have a nested attribute (*Address*). DynamoDB supports nested attributes up to 32 levels deep. The following is another example table named *Music* that you could use to keep track of your music collection. ``` Music { "Artist": "No One You Know", "SongTitle": "My Dog Spot", "AlbumTitle": "Hey Now", "Price": 1.98, "Genre": "Country", "CriticRating": 8.4 } { "Artist": "No One You Know", "SongTitle": "Somewhere Down The Road", "AlbumTitle": "Somewhat Famous", "Genre": "Country", "CriticRating": 8.4, "Year": 1984 } { "Artist": "The Acme Band", "SongTitle": "Still in Love", "AlbumTitle": "The Buck Starts Here", "Price": 2.47, "Genre": "Rock", "PromotionInfo": { "RadioStationsPlaying": [ "KHCR", "KQBX", "WTNR", "WJJH" ], "TourDates": { "Seattle": "20150622", "Cleveland": "20150630" }, "Rotation": "Heavy" } } { "Artist": "The Acme Band", "SongTitle": "Look Out, World", "AlbumTitle": "The Buck Starts Here", "Price": 0.99, "Genre": "Rock" } ``` Note the following about the *Music* table: + The primary key for *Music* consists of two attributes (*Artist* and *SongTitle*). Each item in the table must have these two attributes. The combination of *Artist* and *SongTitle* distinguishes each item in the table from all of the others. + Other than the primary key, the *Music* table is schemaless, which means that neither the attributes nor their data types need to be defined beforehand. Each item can have its own distinct attributes. + One of the items has a nested attribute (*PromotionInfo*), which contains other nested attributes. DynamoDB supports nested attributes up to 32 levels deep. For more information, see [Working with tables and data in DynamoDB](WorkingWithTables.md). ## Primary key When you create a table, in addition to the table name, you must specify the primary key of the table. The primary key uniquely identifies each item in the table, so that no two items can have the same key. DynamoDB supports two different kinds of primary keys: + **Partition key** – A simple primary key, composed of one attribute known as the *partition key*. DynamoDB uses the partition key's value as input to an internal hash function. The output from the hash function determines the partition (physical storage internal to DynamoDB) in which the item will be stored. In a table that has only a partition key, no two items can have the same partition key value. The *People* table described in [Tables, items, and attributes](#HowItWorks.CoreComponents.TablesItemsAttributes) is an example of a table with a simple primary key (*PersonID*). You can access any item in the *People* table directly by providing the *PersonId* value for that item. + **Partition key and sort key** – Referred to as a *composite primary key*, this type of key is composed of two attributes. The first attribute is the *partition key*, and the second attribute is the *sort key*. DynamoDB uses the partition key value as input to an internal hash function. The output from the hash function determines the partition (physical storage internal to DynamoDB) in which the item will be stored. All items with the same partition key value are stored together, in sorted order by sort key value. In a table that has a partition key and a sort key, it's possible for multiple items to have the same partition key value. However, those items must have different sort key values. The *Music* table described in [Tables, items, and attributes](#HowItWorks.CoreComponents.TablesItemsAttributes) is an example of a table with a composite primary key (*Artist* and *SongTitle*). You can access any item in the *Music* table directly, if you provide the *Artist* and *SongTitle* values for that item. A composite primary key gives you additional flexibility when querying data. For example, if you provide only the value for *Artist*, DynamoDB retrieves all of the songs by that artist. To retrieve only a subset of songs by a particular artist, you can provide a value for *Artist* along with a range of values for *SongTitle*. **Note** The partition key of an item is also known as its *hash attribute*. The term *hash attribute* derives from the use of an internal hash function in DynamoDB that evenly distributes data items across partitions, based on their partition key values. The sort key of an item is also known as its *range attribute*. The term *range attribute* derives from the way DynamoDB stores items with the same partition key physically close together, in sorted order by the sort key value. Each primary key attribute must be a scalar (meaning that it can hold only a single value). The only data types allowed for primary key attributes are string, number, or binary. There are no such restrictions for other, non-key attributes. ## Secondary indexes You can create one or more secondary indexes on a table. A *secondary index* lets you query the data in the table using an alternate key, in addition to queries against the primary key. DynamoDB doesn't require that you use indexes, but they give your applications more flexibility when querying your data. After you create a secondary index on a table, you can read data from the index in much the same way as you do from the table. DynamoDB supports two kinds of indexes: + Global secondary index – An index with a partition key and sort key that can be different from those on the table. The primary key values in global secondary indexes don't need to be unique. + Local secondary index – An index that has the same partition key as the table, but a different sort key. In DynamoDB, global secondary indexes (GSIs) are indexes that span the entire table, allowing you to query across all partition keys. Local secondary indexes (LSIs) are indexes that have the same partition key as the base table but a different sort key. Each table in DynamoDB has a quota of 20 global secondary indexes (default quota) and 5 local secondary indexes. In the example *Music* table shown previously, you can query data items by *Artist* (partition key) or by *Artist* and *SongTitle* (partition key and sort key). What if you also wanted to query the data by *Genre* and *AlbumTitle*? To do this, you could create an index on *Genre* and *AlbumTitle*, and then query the index in much the same way as you'd query the *Music* table. The following diagram shows the example *Music* table, with a new index called *GenreAlbumTitle*. In the index, *Genre* is the partition key and *AlbumTitle* is the sort key. | Music Table | *GenreAlbumTitle* | | --- | --- | | 

{
    "Artist": "No One You Know",
    "SongTitle": "My Dog Spot",
    "AlbumTitle": "Hey Now",
    "Price": 1.98,
    "Genre": "Country",
    "CriticRating": 8.4
}
| 

{
    "Genre": "Country",
    "AlbumTitle": "Hey Now",
    "Artist": "No One You Know",
    "SongTitle": "My Dog Spot"
}
| | 

{
    "Artist": "No One You Know",
    "SongTitle": "Somewhere Down The Road",
    "AlbumTitle": "Somewhat Famous",
    "Genre": "Country",
    "CriticRating": 8.4,
    "Year": 1984
}
| 

{
    "Genre": "Country",
    "AlbumTitle": "Somewhat Famous",
    "Artist": "No One You Know",
    "SongTitle": "Somewhere Down The Road"
}
| | 

{
    "Artist": "The Acme Band",
    "SongTitle": "Still in Love",
    "AlbumTitle": "The Buck Starts Here",
    "Price": 2.47,
    "Genre": "Rock",
    "PromotionInfo": {
        "RadioStationsPlaying": {
            "KHCR",
            "KQBX",
            "WTNR",
            "WJJH"
        },
        "TourDates": {
            "Seattle": "20150622",
            "Cleveland": "20150630"
        },
        "Rotation": "Heavy"
    }
}
| 

{
    "Genre": "Rock",
    "AlbumTitle": "The Buck Starts Here",
    "Artist": "The Acme Band",
    "SongTitle": "Still In Love"
}
| | 

{
    "Artist": "The Acme Band",
    "SongTitle": "Look Out, World",
    "AlbumTitle": "The Buck Starts Here",
    "Price": 0.99,
    "Genre": "Rock"
}
| 

{
    "Genre": "Rock",
    "AlbumTitle": "The Buck Starts Here",
    "Artist": "The Acme Band",
    "SongTitle": "Look Out, World"
}
| Note the following about the *GenreAlbumTitle* index: + Every index belongs to a table, which is called the *base table* for the index. In the preceding example, *Music* is the base table for the *GenreAlbumTitle* index. + DynamoDB maintains indexes automatically. When you add, update, or delete an item in the base table, DynamoDB adds, updates, or deletes the corresponding item in any indexes that belong to that table. + When you create an index, you specify which attributes will be copied, or *projected*, from the base table to the index. At a minimum, DynamoDB projects the key attributes from the base table into the index. This is the case with `GenreAlbumTitle`, where only the key attributes from the `Music` table are projected into the index. You can query the *GenreAlbumTitle* index to find all albums of a particular genre (for example, all *Rock* albums). You can also query the index to find all albums within a particular genre that have certain album titles (for example, all *Country* albums with titles that start with the letter H). For more information, see [Improving data access with secondary indexes in DynamoDB](SecondaryIndexes.md). ## DynamoDB Streams DynamoDB Streams is an optional feature that captures data modification events in DynamoDB tables. The data about these events appear in the stream in near-real time, and in the order that the events occurred. Each event is represented by a *stream record*. If you enable a stream on a table, DynamoDB Streams writes a stream record whenever one of the following events occurs: + A new item is added to the table: The stream captures an image of the entire item, including all of its attributes. + An item is updated: The stream captures the "before" and "after" image of any attributes that were modified in the item. + An item is deleted from the table: The stream captures an image of the entire item before it was deleted. Each stream record also contains the name of the table, the event timestamp, and other metadata. Stream records have a lifetime of 24 hours; after that, they are automatically removed from the stream. You can use DynamoDB Streams together with AWS Lambda to create a *trigger*—code that runs automatically whenever an event of interest appears in a stream. For example, consider a *Customers* table that contains customer information for a company. Suppose that you want to send a "welcome" email to each new customer. You could enable a stream on that table, and then associate the stream with a Lambda function. The Lambda function would run whenever a new stream record appears, but only process new items added to the *Customers* table. For any item that has an `EmailAddress` attribute, the Lambda function would invoke Amazon Simple Email Service (Amazon SES) to send an email to that address. ![\[Integration of DynamoDB Streams and Lambda to automatically send a welcome email to new customers.\]](http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/images/HowItWorksStreams.png) **Note** In this example, the last customer, Craig Roe, will not receive an email because he doesn't have an `EmailAddress`. In addition to triggers, DynamoDB Streams enables powerful solutions such as data replication within and across AWS Regions, materialized views of data in DynamoDB tables, data analysis using Kinesis materialized views, and much more. For more information, see [Change data capture for DynamoDB Streams](Streams.md). # DynamoDB API To work with Amazon DynamoDB, your application must use a few simple API operations. The following is a summary of these operations, organized by category. **Note** For a full list of the API operations, see the [Amazon DynamoDB API Reference](https://docs.aws.amazon.com/amazondynamodb/latest/APIReference/Welcome.html). **Topics** + [ ## Control plane ](#HowItWorks.API.ControlPlane) + [ ## Data plane ](#HowItWorks.API.DataPlane) + [ ## DynamoDB Streams ](#HowItWorks.API.Streams) + [ ## Transactions ](#HowItWorks.API.Transactions) ## Control plane *Control plane* operations let you create and manage DynamoDB tables. They also let you work with indexes, streams, and other objects that are dependent on tables. + `CreateTable` – Creates a new table. Optionally, you can create one or more secondary indexes, and enable DynamoDB Streams for the table. + `DescribeTable`– Returns information about a table, such as its primary key schema, throughput settings, and index information. + `ListTables` – Returns the names of all of your tables in a list. + `UpdateTable` – Modifies the settings of a table or its indexes, creates or removes new indexes on a table, or modifies DynamoDB Streams settings for a table. + `DeleteTable` – Removes a table and all of its dependent objects from DynamoDB. ## Data plane *Data plane* operations let you perform create, read, update, and delete (also called *CRUD*) actions on data in a table. Some of the data plane operations also let you read data from a secondary index. You can use [PartiQL - a SQL-compatible query language for Amazon DynamoDB](ql-reference.md), to perform these CRUD operations or you can use DynamoDB’s classic CRUD APIs that separates each operation into a distinct API call. ### PartiQL - A SQL-compatible query language + `ExecuteStatement` – Reads multiple items from a table. You can also write or update a single item from a table. When writing or updating a single item, you must specify the primary key attributes. + `BatchExecuteStatement` – Writes, updates or reads multiple items from a table. This is more efficient than `ExecuteStatement` because your application only needs a single network round trip to write or read the items. ### Classic APIs #### Creating data + `PutItem` – Writes a single item to a table. You must specify the primary key attributes, but you don't have to specify other attributes. + `BatchWriteItem` – Writes up to 25 items to a table. This is more efficient than calling `PutItem` multiple times because your application only needs a single network round trip to write the items. #### Reading data + `GetItem` – Retrieves a single item from a table. You must specify the primary key for the item that you want. You can retrieve the entire item, or just a subset of its attributes. + `BatchGetItem` – Retrieves up to 100 items from one or more tables. This is more efficient than calling `GetItem` multiple times because your application only needs a single network round trip to read the items. + `Query` – Retrieves all items that have a specific partition key. You must specify the partition key value. You can retrieve entire items, or just a subset of their attributes. Optionally, you can apply a condition to the sort key values so that you only retrieve a subset of the data that has the same partition key. You can use this operation on a table, provided that the table has both a partition key and a sort key. You can also use this operation on an index, provided that the index has both a partition key and a sort key. + `Scan` – Retrieves all items in the specified table or index. You can retrieve entire items, or just a subset of their attributes. Optionally, you can apply a filtering condition to return only the values that you are interested in and discard the rest. #### Updating data + `UpdateItem` – Modifies one or more attributes in an item. You must specify the primary key for the item that you want to modify. You can add new attributes and modify or remove existing attributes. You can also perform conditional updates, so that the update is only successful when a user-defined condition is met. Optionally, you can implement an atomic counter, which increments or decrements a numeric attribute without interfering with other write requests. #### Deleting data + `DeleteItem` – Deletes a single item from a table. You must specify the primary key for the item that you want to delete. + `BatchWriteItem` – Deletes up to 25 items from one or more tables. This is more efficient than calling `DeleteItem` multiple times because your application only needs a single network round trip to delete the items. **Note** You can use `BatchWriteItem` to both create data and delete data. ## DynamoDB Streams *DynamoDB Streams* operations let you enable or disable a stream on a table, and allow access to the data modification records contained in a stream. + `ListStreams` – Returns a list of all your streams, or just the stream for a specific table. + `DescribeStream` – Returns information about a stream, such as its Amazon Resource Name (ARN) and where your application can begin reading the first few stream records. + `GetShardIterator` – Returns a *shard iterator*, which is a data structure that your application uses to retrieve the records from the stream. + `GetRecords` – Retrieves one or more stream records, using a given shard iterator. ## Transactions *Transactions* provide atomicity, consistency, isolation, and durability (ACID) enabling you to maintain data correctness in your applications more easily. You can use [PartiQL - a SQL-compatible query language for Amazon DynamoDB](ql-reference.md), to perform transactional operations or you can use DynamoDB’s classic CRUD APIs that separates each operation into a distinct API call. ### PartiQL - A SQL-compatible query language + `ExecuteTransaction` – A batch operation that allows CRUD operations to multiple items both within and across tables with a guaranteed all-or-nothing result. ### Classic APIs + `TransactWriteItems` – A batch operation that allows `Put`, `Update`, and `Delete` operations to multiple items both within and across tables with a guaranteed all-or-nothing result. + `TransactGetItems` – A batch operation that allows `Get` operations to retrieve multiple items from one or more tables. # Supported data types and naming rules in Amazon DynamoDB This section describes the Amazon DynamoDB naming rules and the various data types that DynamoDB supports. There are limits that apply to data types. For more information, see [Data types](Constraints.md#limits-data-types). **Topics** + [ ## Naming rules ](#HowItWorks.NamingRules) + [ ## Data types ](#HowItWorks.DataTypes) + [ ## Data type descriptors ](#HowItWorks.DataTypeDescriptors) ## Naming rules Tables, attributes, and other objects in DynamoDB must have names. Names should be meaningful and concise—for example, names such as *Products*, *Books*, and *Authors* are self-explanatory. The following are the naming rules for DynamoDB: + All names must be encoded using UTF-8, and are case-sensitive. + Table names and index names must be between 3 and 255 characters long, and can contain only the following characters: + `a-z` + `A-Z ` + ` 0-9 ` + `_` (underscore) + `-` (dash) + `.` (dot) + Attribute names must be at least one character long and less than 64 KB in size. It is considered best practice to keep your attribute names as short as possible. This helps reduce read request units consumed, as attribute names are included in metering of storage and throughput usage. The following are the exceptions. These attribute names must be no greater than 255 characters long: + Secondary index partition key names + Secondary index sort key names + The names of any user-specified projected attributes (applicable only to local secondary indexes) ### Reserved words and special characters DynamoDB has a list of reserved words and special characters. For a complete list, see [Reserved words in DynamoDB](ReservedWords.md). Also, the following characters have special meaning in DynamoDB: **\$1** (hash) and **:** (colon). Although DynamoDB allows you to use these reserved words and special characters for names, we recommend that you avoid doing so because you have to define placeholder variables whenever you use these names in an expression. For more information, see [Expression attribute names (aliases) in DynamoDB](Expressions.ExpressionAttributeNames.md). ## Data types DynamoDB supports many different data types for attributes within a table. They can be categorized as follows: + **Scalar Types** – A scalar type can represent exactly one value. The scalar types are number, string, binary, Boolean, and null. + **Document Types** – A document type can represent a complex structure with nested attributes, such as what you would find in a JSON document. The document types are list and map. + **Set Types** – A set type can represent multiple scalar values. The set types are string set, number set, and binary set. When you create a table or a secondary index, you must specify the names and data types of each primary key attribute (partition key and sort key). Furthermore, each primary key attribute must be defined as type string, number, or binary. DynamoDB is a NoSQL database and is *schemaless*. This means that other than the primary key attributes, you don't have to define any attributes or data types when you create tables. By comparison, relational databases require you to define the names and data types of each column when you create a table. The following are descriptions of each data type, along with examples in JSON format. ### Scalar types The scalar types are number, string, binary, Boolean, and null. #### Number Numbers can be positive, negative, or zero. Numbers can have up to 38 digits of precision. Exceeding this results in an exception. If you need greater precision than 38 digits, you can use strings. + Positive range: 1E-130 to 9.9999999999999999999999999999999999999E\$1125 + Negative range: -9.9999999999999999999999999999999999999E\$1125 to -1E-130 In DynamoDB, numbers are represented as variable length. Leading and trailing zeroes are trimmed. All numbers are sent across the network to DynamoDB as strings to maximize compatibility across languages and libraries. However, DynamoDB treats them as number type attributes for mathematical operations. You can use the number data type to represent a date or a timestamp. One way to do this is by using epoch time—the number of seconds since 00:00:00 UTC on 1 January 1970. For example, the epoch time `1437136300` represents 12:31:40 PM UTC on 17 July 2015. For more information, see [http://en.wikipedia.org/wiki/Unix\$1time](http://en.wikipedia.org/wiki/Unix_time). #### String Strings are Unicode with UTF-8 binary encoding. The minimum length of a string can be zero, if the attribute is not used as a key for an index or table, and is constrained by the maximum DynamoDB item size limit of 400 KB. The following additional constraints apply to primary key attributes that are defined as type string: + For a simple primary key, the maximum length of the first attribute value (the partition key) is 2048 bytes. + For a composite primary key, the maximum length of the second attribute value (the sort key) is 1024 bytes. DynamoDB collates and compares strings using the bytes of the underlying UTF-8 string encoding. For example, "`a`" (0x61) is greater than "`A`" (0x41), and "`¿`" (0xC2BF) is greater than "`z`" (0x7A). You can use the string data type to represent a date or a timestamp. One way to do this is by using ISO 8601 strings, as shown in these examples: + `2016-02-15` + `2015-12-21T17:42:34Z` + `20150311T122706Z` For more information, see [http://en.wikipedia.org/wiki/ISO\$18601](http://en.wikipedia.org/wiki/ISO_8601). **Note** Unlike conventional relational databases, DynamoDB does not natively support a date and time data type. It can be useful instead to store date and time data as a number data type, using Unix epoch time. #### Binary Binary type attributes can store any binary data, such as compressed text, encrypted data, or images. Whenever DynamoDB compares binary values, it treats each byte of the binary data as unsigned. The length of a binary attribute can be zero, if the attribute is not used as a key for an index or table, and is constrained by the maximum DynamoDB item size limit of 400 KB. If you define a primary key attribute as a binary type attribute, the following additional constraints apply: + For a simple primary key, the maximum length of the first attribute value (the partition key) is 2048 bytes. + For a composite primary key, the maximum length of the second attribute value (the sort key) is 1024 bytes. Your applications must encode binary values in base64-encoded format before sending them to DynamoDB. Upon receipt of these values, DynamoDB decodes the data into an unsigned byte array and uses that as the length of the binary attribute. The following example is a binary attribute, using base64-encoded text. ``` dGhpcyB0ZXh0IGlzIGJhc2U2NC1lbmNvZGVk ``` #### Boolean A Boolean type attribute can store either `true` or `false`. #### Null Null represents an attribute with an unknown or undefined state. ### Document types The document types are list and map. These data types can be nested within each other, to represent complex data structures up to 32 levels deep. There is no limit on the number of values in a list or a map, as long as the item containing the values fits within the DynamoDB item size limit (400 KB). An attribute value can be an empty string or empty binary value if the attribute is not used for a table or index key. An attribute value cannot be an empty set (string set, number set, or binary set), however, empty lists and maps are allowed. Empty string and binary values are allowed within lists and maps. For more information, see [Attributes](Constraints.md#limits-attributes). #### List A list type attribute can store an ordered collection of values. Lists are enclosed in square brackets: `[ ... ]` A list is similar to a JSON array. There are no restrictions on the data types that can be stored in a list element, and the elements in a list element do not have to be of the same type. The following example shows a list that contains two strings and a number. ``` FavoriteThings: ["Cookies", "Coffee", 3.14159] ``` **Note** DynamoDB lets you work with individual elements within lists, even if those elements are deeply nested. For more information, see [Using expressions in DynamoDB](Expressions.md). #### Map A map type attribute can store an unordered collection of name-value pairs. Maps are enclosed in curly braces: `{ ... }` A map is similar to a JSON object. There are no restrictions on the data types that can be stored in a map element, and the elements in a map do not have to be of the same type. Maps are ideal for storing JSON documents in DynamoDB. The following example shows a map that contains a string, a number, and a nested list that contains another map. ``` { Day: "Monday", UnreadEmails: 42, ItemsOnMyDesk: [ "Coffee Cup", "Telephone", { Pens: { Quantity : 3}, Pencils: { Quantity : 2}, Erasers: { Quantity : 1} } ] } ``` **Note** DynamoDB lets you work with individual elements within maps, even if those elements are deeply nested. For more information, see [Using expressions in DynamoDB](Expressions.md). ### Sets DynamoDB supports types that represent sets of number, string, or binary values. All the elements within a set must be of the same type. For example, a Number Set can only contain numbers and a String Set can only contain strings. There is no limit on the number of values in a set, as long as the item containing the values fits within the DynamoDB item size limit (400 KB). Each value within a set must be unique. The order of the values within a set is not preserved. Therefore, your applications must not rely on any particular order of elements within the set. DynamoDB does not support empty sets, however, empty string and binary values are allowed within a set. The following example shows a string set, a number set, and a binary set: ``` ["Black", "Green", "Red"] [42.2, -19, 7.5, 3.14] ["U3Vubnk=", "UmFpbnk=", "U25vd3k="] ``` ## Data type descriptors The low-level DynamoDB API protocol uses *Data type descriptors* as tokens that tell DynamoDB how to interpret each attribute. The following is a complete list of DynamoDB data type descriptors: + **`S`** – String + **`N`** – Number + **`B`** – Binary + **`BOOL`** – Boolean + **`NULL`** – Null + **`M`** – Map + **`L`** – List + **`SS`** – String Set + **`NS`** – Number Set + **`BS`** – Binary Set # DynamoDB table classes DynamoDB offers two table classes designed to help you optimize for cost. The DynamoDB Standard table class is the default, and is recommended for the vast majority of workloads. The DynamoDB Standard-Infrequent Access (DynamoDB Standard-IA) table class is optimized for tables where storage is the dominant cost. For example, tables that store infrequently accessed data, such as application logs, old social media posts, e-commerce order history, and past gaming achievements, are good candidates for the Standard-IA table class. See [Amazon DynamoDB Pricing](https://aws.amazon.com/dynamodb/pricing/on-demand/) for pricing details. Every DynamoDB table is associated with a table class (DynamoDB Standard by default). All secondary indexes associated with the table use the same table class. Each table class offers different pricing for data storage as well as for read and write requests. You can select the most cost-effective table class for your table based on its storage and throughput usage patterns. The choice of a table class is not permanent—you can change this setting using the AWS Management Console, AWS CLI, or AWS SDK. DynamoDB also supports managing your table class using AWS CloudFormation for single-Region tables and global tables. To learn more about selecting your table class, see [Considerations when choosing a table class in DynamoDB](WorkingWithTables.tableclasses.md). # Partitions and data distribution in DynamoDB Amazon DynamoDB stores data in partitions. A *partition* is an allocation of storage for a table, backed by solid state drives (SSDs) and automatically replicated across multiple Availability Zones within an AWS Region. Partition management is handled entirely by DynamoDB—you never have to manage partitions yourself. When you create a table, the initial status of the table is `CREATING`. During this phase, DynamoDB allocates sufficient partitions to the table so that it can handle your provisioned throughput requirements. You can begin writing and reading table data after the table status changes to `ACTIVE`. DynamoDB allocates additional partitions to a table in the following situations: + If you increase the table's provisioned throughput settings beyond what the existing partitions can support. + If an existing partition fills to capacity and more storage space is required. Partition management occurs automatically in the background and is transparent to your applications. Your table remains available throughout and fully supports your provisioned throughput requirements. For more details, see [Partition key design](bp-partition-key-design.md). Global secondary indexes in DynamoDB are also composed of partitions. The data in a global secondary index is stored separately from the data in its base table, but index partitions behave in much the same way as table partitions. ## Data distribution: Partition key If your table has a simple primary key (partition key only), DynamoDB stores and retrieves each item based on its partition key value. To write an item to the table, DynamoDB uses the value of the partition key as input to an internal hash function. The output value from the hash function determines the partition in which the item will be stored. To read an item from the table, you must specify the partition key value for the item. DynamoDB uses this value as input to its hash function, yielding the partition in which the item can be found. The following diagram shows a table named *Pets*, which spans multiple partitions. The table's primary key is *AnimalType* (only this key attribute is shown). DynamoDB uses its hash function to determine where to store a new item, in this case based on the hash value of the string *Dog*. Note that the items are not stored in sorted order. Each item's location is determined by the hash value of its partition key. ![\[DynamoDB's distribution of table items across partitions based on the partition key's hash value.\]](http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/images/HowItWorksPartitionKey.png) **Note** DynamoDB is optimized for uniform distribution of items across a table's partitions, no matter how many partitions there may be. We recommend that you choose a partition key that can have a large number of distinct values relative to the number of items in the table. ## Data distribution: Partition key and sort key If the table has a composite primary key (partition key and sort key), DynamoDB calculates the hash value of the partition key in the same way as described in [Data distribution: Partition key](#HowItWorks.Partitions.SimpleKey). However, it tends to keep items which have the same value of partition key close together and in sorted order by the sort key attribute's value. The set of items which have the same value of partition key is called an item collection. Item collections are optimized for efficient retrieval of ranges of the items within the collection. If your table doesn't have local secondary indexes, DynamoDB will automatically split your item collection over as many partitions as required to store the data and to serve read and write throughput. To write an item to the table, DynamoDB calculates the hash value of the partition key to determine which partition should contain the item. In that partition, several items could have the same partition key value. So DynamoDB stores the item among the others with the same partition key, in ascending order by sort key. To read an item from the table, you must specify its partition key value and sort key value. DynamoDB calculates the partition key's hash value, yielding the partition in which the item can be found. You can read multiple items from the table in a single operation (`Query`) if the items you want have the same partition key value. DynamoDB returns all of the items with that partition key value. Optionally, you can apply a condition to the sort key so that it returns only the items within a certain range of values. Suppose that the *Pets* table has a composite primary key consisting of *AnimalType* (partition key) and *Name* (sort key). The following diagram shows DynamoDB writing an item with a partition key value of *Dog* and a sort key value of *Fido*. ![\[DynamoDB stores an item with a composite partition key and sorts the item using the sort key attribute's value.\]](http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/images/HowItWorksPartitionKeySortKey.png) To read that same item from the *Pets* table, DynamoDB calculates the hash value of *Dog*, yielding the partition in which these items are stored. DynamoDB then scans the sort key attribute values until it finds *Fido*. To read all of the items with an *AnimalType* of *Dog*, you can issue a `Query` operation without specifying a sort key condition. By default, the items are returned in the order that they are stored (that is, in ascending order by sort key). Optionally, you can request descending order instead. To query only some of the *Dog* items, you can apply a condition to the sort key (for example, only the *Dog* items where *Name* begins with a letter that is within the range `A` through `K`). **Note** In a DynamoDB table, there is no upper limit on the number of distinct sort key values per partition key value. If you needed to store many billions of *Dog* items in the *Pets* table, DynamoDB would allocate enough storage to handle this requirement automatically. # Learn how to go from SQL to NoSQL If you are an application developer, you might have some experience using a relational database management system (RDBMS) and Structured Query Language (SQL). As you begin working with Amazon DynamoDB, you will encounter many similarities, but also many things that are different. *NoSQL* is a term used to describe nonrelational database systems that are highly available, scalable, and optimized for high performance. Instead of the relational model, NoSQL databases (like DynamoDB) use alternate models for data management, such as key-value pairs or document storage. For more information, see [What is NoSQL?](http://aws.amazon.com/nosql). Amazon DynamoDB supports [PartiQL](https://partiql.org/), an open-source, SQL-compatible query language that makes it easy for you to efficiently query data, regardless of where or in what format it is stored. With PartiQL, you can easily process structured data from relational databases, semi-structured and nested data in open data formats, and even schema-less data in NoSQL or document databases that allow different attributes for different rows. For more information, see [PartiQL query language](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.html). The following sections describe common database tasks, comparing and contrasting SQL statements with their equivalent DynamoDB operations. **Note** The SQL examples in this section are compatible with the MySQL RDBMS. The DynamoDB examples in this section show the name of the DynamoDB operation, along with the parameters for that operation in JSON format. **Topics** + [ # Choosing between relational (SQL) and NoSQL ](SQLtoNoSQL.WhyDynamoDB.md) + [ # Differences in accessing a relational (SQL) database and DynamoDB ](SQLtoNoSQL.Accessing.md) + [ # Differences between a relational (SQL) database and DynamoDB when creating a table ](SQLtoNoSQL.CreateTable.md) + [ # Differences between getting table information from a relational (SQL) database and DynamoDB ](SQLtoNoSQL.GetTableInfo.md) + [ # Differences between a relational (SQL) database and DynamoDB when writing data to a table ](SQLtoNoSQL.WriteData.md) + [ # Differences between a relational (SQL) database and DynamoDB when reading data from a table ](SQLtoNoSQL.ReadData.md) + [ # Differences between a relational (SQL) database and DynamoDB when managing indexes ](SQLtoNoSQL.Indexes.md) + [ # Differences between a relational (SQL) database and DynamoDB when modifying data in a table ](SQLtoNoSQL.UpdateData.md) + [ # Differences between a relational (SQL) database and DynamoDB when deleting data from a table ](SQLtoNoSQL.DeleteData.md) + [ # Differences between a relational (SQL) database and DynamoDB when removing a table ](SQLtoNoSQL.RemoveTable.md) # Choosing between relational (SQL) and NoSQL Today's applications have more demanding requirements than ever before. For example, an online game might start out with just a few users and a very small amount of data. However, if the game becomes successful, it can easily outstrip the resources of the underlying database management system. It is common for web-based applications to have hundreds, thousands, or millions of concurrent users, with terabytes or more of new data generated per day. Databases for such applications must handle tens (or hundreds) of thousands of reads and writes per second. Amazon DynamoDB is well-suited for these kinds of workloads. As a developer, you can start small and gradually increase your utilization as your application becomes more popular. DynamoDB scales seamlessly to handle very large amounts of data and very large numbers of users. For more information on traditional relational database modeling and how to adapt it for DynamoDB, see [Best practices for modeling relational data in DynamoDB](bp-relational-modeling.md). The following table shows some high-level differences between a relational database management system (RDBMS) and DynamoDB. **** | Characteristic | Relational database management system (RDBMS) | Amazon DynamoDB | | --- | --- | --- | | Optimal Workloads | Ad hoc queries; data warehousing; OLAP (online analytical processing). | Web-scale applications, including social networks, gaming, media sharing, and Internet of Things (IoT). | | Data Model | The relational model requires a well-defined schema, where data is normalized into tables, rows, and columns. In addition, all of the relationships are defined among tables, columns, indexes, and other database elements. | DynamoDB is schemaless. Every table must have a primary key to uniquely identify each data item, but there are no similar constraints on other non-key attributes. DynamoDB can manage structured or semistructured data, including JSON documents. | | Data Access | SQL is the standard for storing and retrieving data. Relational databases offer a rich set of tools for simplifying the development of database-driven applications, but all of these tools use SQL. | You can use the AWS Management Console, the AWS CLI, or NoSQL WorkBench to work with DynamoDB and perform ad hoc tasks. [PartiQL](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.html), a SQL-compatible query language, lets you select, insert, update, and delete data in DynamoDB. Applications can use the AWS software development kits (SDKs) to work with DynamoDB using object-based, document-centric, or low-level interfaces. | | Performance | Relational databases are optimized for storage, so performance generally depends on the disk subsystem. Developers and database administrators must optimize queries, indexes, and table structures in order to achieve peak performance. | DynamoDB is optimized for compute, so performance is mainly a function of the underlying hardware and network latency. As a managed service, DynamoDB insulates you and your applications from these implementation details, so that you can focus on designing and building robust, high-performance applications. | | Scaling | It is easiest to scale up with faster hardware. It is also possible for database tables to span across multiple hosts in a distributed system, but this requires additional investment. Relational databases have maximum sizes for the number and size of files, which imposes upper limits on scalability. | DynamoDB is designed to scale out using distributed clusters of hardware. This design allows increased throughput without increased latency. Customers specify their throughput requirements, and DynamoDB allocates sufficient resources to meet those requirements. There are no upper limits on the number of items per table, nor the total size of that table. | # Differences in accessing a relational (SQL) database and DynamoDB Before your application can access a database, it must be *authenticated* to ensure that the application is allowed to use the database. It must be *authorized* so that the application can perform only the actions for which it has permissions. The following diagram shows a client's interaction with a relational database and with Amazon DynamoDB. ![\[Interaction with relational and NoSQL databases.\]](http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/images/SQLtoNoSQL.png) The following table has more details about client interaction tasks. **** | Characteristic | Relational database management system (RDBMS) | Amazon DynamoDB | | --- | --- | --- | | Tools for Accessing the Database | Most relational databases provide a command line interface (CLI) so that you can enter ad hoc SQL statements and see the results immediately. | In most cases, you write application code. You can also use the AWS Management Console, the AWS Command Line Interface (AWS CLI), or NoSQL Workbench to send ad hoc requests to DynamoDB and view the results. [PartiQL](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.html), a SQL-compatible query language, lets you select, insert, update, and delete data in DynamoDB. | | Connecting to the Database | An application program establishes and maintains a network connection with the database. When the application is finished, it terminates the connection. | DynamoDB is a web service, and interactions with it are stateless. Applications do not need to maintain persistent network connections. Instead, interaction with DynamoDB occurs using HTTP(S) requests and responses. | | Authentication | An application cannot connect to the database until it is authenticated. The RDBMS can perform the authentication itself, or it can offload this task to the host operating system or a directory service. | Every request to DynamoDB must be accompanied by a cryptographic signature, which authenticates that particular request. The AWS SDKs provide all of the logic necessary for creating signatures and signing requests. For more information, see [Signing AWS API requests](https://docs.aws.amazon.com/general/latest/gr/signing_aws_api_requests.html) in the AWS General Reference. | | Authorization | Applications can perform only the actions for which they have been authorized. Database administrators or application owners can use the SQL GRANT and REVOKE statements to control access to database objects (such as tables), data (such as rows within a table), or the ability to issue certain SQL statements. | In DynamoDB, authorization is handled by AWS Identity and Access Management (IAM). You can write an IAM policy to grant permissions on a DynamoDB resource (such as a table), and then allow users and roles to use that policy. IAM also features fine-grained access control for individual data items in DynamoDB tables. For more information, see [Identity and Access Management for Amazon DynamoDB](security-iam.md). | | Sending a Request | The application issues a SQL statement for every database operation that it wants to perform. Upon receipt of the SQL statement, the RDBMS checks its syntax, creates a plan for performing the operation, and then runs the plan. | The application sends HTTP(S) requests to DynamoDB. The requests contain the name of the DynamoDB operation to perform, along with parameters. DynamoDB runs the request immediately. | | Receiving a Response | The RDBMS returns the results from the SQL statement. If there is an error, the RDBMS returns an error status and message. | DynamoDB returns an HTTP(S) response containing the results of the operation. If there is an error, DynamoDB returns an HTTP error status and messages. | # Differences between a relational (SQL) database and DynamoDB when creating a table Tables are the fundamental data structures in relational databases and in Amazon DynamoDB. A relational database management system (RDBMS) requires you to define the table's schema when you create it. In contrast, DynamoDB tables are schemaless—other than the primary key, you do not need to define any extra attributes or data types when you create a table. The following section compares how you would create a table with SQL to how you would create it with DynamoDB. **Topics** + [ ## Creating a table with SQL ](#SQLtoNoSQL.CreateTable.SQL) + [ ## Creating a table with DynamoDB ](#SQLtoNoSQL.CreateTable.DynamoDB) ## Creating a table with SQL With SQL you would use the `CREATE TABLE` statement to create a table, as shown in the following example. ``` CREATE TABLE Music ( Artist VARCHAR(20) NOT NULL, SongTitle VARCHAR(30) NOT NULL, AlbumTitle VARCHAR(25), Year INT, Price FLOAT, Genre VARCHAR(10), Tags TEXT, PRIMARY KEY(Artist, SongTitle) ); ``` The primary key for this table consists of *Artist* and *SongTitle*. You must define all of the table's columns and data types, and the table's primary key. (You can use the `ALTER TABLE` statement to change these definitions later, if necessary.) Many SQL implementations let you define storage specifications for your table, as part of the `CREATE TABLE` statement. Unless you indicate otherwise, the table is created with default storage settings. In a production environment, a database administrator can help determine the optimal storage parameters. ## Creating a table with DynamoDB Use the `CreateTable` operation to create a provisioned mode table, specifying parameters as shown following: ``` { TableName : "Music", KeySchema: [ { AttributeName: "Artist", KeyType: "HASH" //Partition key }, { AttributeName: "SongTitle", KeyType: "RANGE" //Sort key } ], AttributeDefinitions: [ { AttributeName: "Artist", AttributeType: "S" }, { AttributeName: "SongTitle", AttributeType: "S" } ], ProvisionedThroughput: { // Only specified if using provisioned mode ReadCapacityUnits: 1, WriteCapacityUnits: 1 } } ``` The primary key for this table consists of *Artist* (partition key) and *SongTitle* (sort key). You must provide the following parameters to `CreateTable`: + `TableName` – Name of the table. + `KeySchema` – Attributes that are used for the primary key. For more information, see [Tables, items, and attributes](HowItWorks.CoreComponents.md#HowItWorks.CoreComponents.TablesItemsAttributes) and [Primary key](HowItWorks.CoreComponents.md#HowItWorks.CoreComponents.PrimaryKey). + `AttributeDefinitions` – Data types for the key schema attributes. + `ProvisionedThroughput (for provisioned tables)` – Number of reads and writes per second that you need for this table. DynamoDB reserves sufficient storage and system resources so that your throughput requirements are always met. You can use the `UpdateTable` operation to change these later, if necessary. You do not need to specify a table's storage requirements because storage allocation is managed entirely by DynamoDB. # Differences between getting table information from a relational (SQL) database and DynamoDB You can verify that a table has been created according to your specifications. In a relational database, all of the table's schema is shown. Amazon DynamoDB tables are schemaless, so only the primary key attributes are shown. **Topics** + [ ## Getting information about a table with SQL ](#SQLtoNoSQL.GetTableInfo.SQL) + [ ## Getting information about a table in DynamoDB ](#SQLtoNoSQL.GetTableInfo.DynamoDB) ## Getting information about a table with SQL Most relational database management systems (RDBMS) allow you to describe a table's structure—columns, data types, primary key definition, and so on. There is no standard way to do this in SQL. However, many database systems provide a `DESCRIBE` command. The following is an example from MySQL. ``` DESCRIBE Music; ``` This returns the structure of your table, with all of the column names, data types, and sizes. ``` +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | Artist | varchar(20) | NO | PRI | NULL | | | SongTitle | varchar(30) | NO | PRI | NULL | | | AlbumTitle | varchar(25) | YES | | NULL | | | Year | int(11) | YES | | NULL | | | Price | float | YES | | NULL | | | Genre | varchar(10) | YES | | NULL | | | Tags | text | YES | | NULL | | +------------+-------------+------+-----+---------+-------+ ``` The primary key for this table consists of *Artist* and *SongTitle*. ## Getting information about a table in DynamoDB DynamoDB has a `DescribeTable` operation, which is similar. The only parameter is the table name. ``` { TableName : "Music" } ``` The reply from `DescribeTable` looks like the following. ``` { "Table": { "AttributeDefinitions": [ { "AttributeName": "Artist", "AttributeType": "S" }, { "AttributeName": "SongTitle", "AttributeType": "S" } ], "TableName": "Music", "KeySchema": [ { "AttributeName": "Artist", "KeyType": "HASH" //Partition key }, { "AttributeName": "SongTitle", "KeyType": "RANGE" //Sort key } ], ... ``` `DescribeTable` also returns information about indexes on the table, provisioned throughput settings, an approximate item count, and other metadata. # Differences between a relational (SQL) database and DynamoDB when writing data to a table Relational database tables contain *rows* of data. Rows are composed of *columns*. Amazon DynamoDB tables contain *items*. Items are composed of *attributes*. This section describes how to write one row (or item) to a table. **Topics** + [ ## Writing data to a table with SQL ](#SQLtoNoSQL.WriteData.SQL) + [ ## Writing data to a table in DynamoDB ](#SQLtoNoSQL.WriteData.DynamoDB) ## Writing data to a table with SQL A table in a relational database is a two-dimensional data structure composed of rows and columns. Some database management systems also provide support for semistructured data, usually with native JSON or XML data types. However, the implementation details vary among vendors. In SQL, you would use the `INSERT` statement to add a row to a table. ``` INSERT INTO Music (Artist, SongTitle, AlbumTitle, Year, Price, Genre, Tags) VALUES( 'No One You Know', 'Call Me Today', 'Somewhat Famous', 2015, 2.14, 'Country', '{"Composers": ["Smith", "Jones", "Davis"],"LengthInSeconds": 214}' ); ``` The primary key for this table consists of *Artist* and *SongTitle*. You must specify values for these columns. **Note** This example uses the *Tags* column to store semistructured data about the songs in the *Music* table. The *Tags* column is defined as type TEXT, which can store up to 65,535 characters in MySQL. ## Writing data to a table in DynamoDB In Amazon DynamoDB, you can use either the DynamoDB API or [PartiQL](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.html) (a SQL-compatible query language) to add an item to a table. ------ #### [ DynamoDB API ] With the DynamoDB API, you use the `PutItem` operation to add an item to a table. ``` { TableName: "Music", Item: { "Artist":"No One You Know", "SongTitle":"Call Me Today", "AlbumTitle":"Somewhat Famous", "Year": 2015, "Price": 2.14, "Genre": "Country", "Tags": { "Composers": [ "Smith", "Jones", "Davis" ], "LengthInSeconds": 214 } } } ``` The primary key for this table consists of *Artist* and *SongTitle*. You must specify values for these attributes. Here are some key things to know about this `PutItem` example: + DynamoDB provides native support for documents, using JSON. This makes DynamoDB ideal for storing semistructured data, such as *Tags*. You can also retrieve and manipulate data from within JSON documents. + The *Music* table does not have any predefined attributes, other than the primary key (*Artist* and *SongTitle*). + Most SQL databases are transaction oriented. When you issue an `INSERT` statement, the data modifications are not permanent until you issue a `COMMIT` statement. With Amazon DynamoDB, the effects of a `PutItem` operation are permanent when DynamoDB replies with an HTTP 200 status code (`OK`). The following are some other `PutItem` examples. ``` { TableName: "Music", Item: { "Artist": "No One You Know", "SongTitle": "My Dog Spot", "AlbumTitle":"Hey Now", "Price": 1.98, "Genre": "Country", "CriticRating": 8.4 } } ``` ``` { TableName: "Music", Item: { "Artist": "No One You Know", "SongTitle": "Somewhere Down The Road", "AlbumTitle":"Somewhat Famous", "Genre": "Country", "CriticRating": 8.4, "Year": 1984 } } ``` ``` { TableName: "Music", Item: { "Artist": "The Acme Band", "SongTitle": "Still In Love", "AlbumTitle":"The Buck Starts Here", "Price": 2.47, "Genre": "Rock", "PromotionInfo": { "RadioStationsPlaying":[ "KHCR", "KBQX", "WTNR", "WJJH" ], "TourDates": { "Seattle": "20150625", "Cleveland": "20150630" }, "Rotation": "Heavy" } } } ``` ``` { TableName: "Music", Item: { "Artist": "The Acme Band", "SongTitle": "Look Out, World", "AlbumTitle":"The Buck Starts Here", "Price": 0.99, "Genre": "Rock" } } ``` **Note** In addition to `PutItem`, DynamoDB supports a `BatchWriteItem` operation for writing multiple items at the same time. ------ #### [ PartiQL for DynamoDB ] With PartiQL, you use the `ExecuteStatement` operation to add an item to a table, using the PartiQL `Insert` statement. ``` INSERT into Music value { 'Artist': 'No One You Know', 'SongTitle': 'Call Me Today', 'AlbumTitle': 'Somewhat Famous', 'Year' : '2015', 'Genre' : 'Acme' } ``` The primary key for this table consists of *Artist* and *SongTitle*. You must specify values for these attributes. **Note** For code examples using `Insert` and `ExecuteStatement`, see [PartiQL insert statements for DynamoDB](ql-reference.insert.md). ------ # Differences between a relational (SQL) database and DynamoDB when reading data from a table With SQL, you use the `SELECT` statement to retrieve one or more rows from a table. You use the `WHERE` clause to determine the data that is returned to you. This is different than using Amazon DynamoDB which provides the following operations for reading data: + `ExecuteStatement` retrieves a single or multiple items from a table. `BatchExecuteStatement` retrieves multiple items from different tables in a single operation. Both of these operations use [PartiQL](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.html), a SQL-compatible query language. + `GetItem` – Retrieves a single item from a table. This is the most efficient way to read a single item because it provides direct access to the physical location of the item. (DynamoDB also provides the `BatchGetItem` operation, allowing you to perform up to 100 `GetItem` calls in a single operation.) + `Query` – Retrieves all of the items that have a specific partition key. Within those items, you can apply a condition to the sort key and retrieve only a subset of the data. `Query` provides quick, efficient access to the partitions where the data is stored. (For more information, see [Partitions and data distribution in DynamoDB](HowItWorks.Partitions.md).) + `Scan` – Retrieves all of the items in the specified table. (This operation should not be used with large tables because it can consume large amounts of system resources.) **Note** With a relational database, you can use the `SELECT` statement to join data from multiple tables and return the results. Joins are fundamental to the relational model. To ensure that joins run efficiently, the database and its applications should be performance-tuned on an ongoing basis. DynamoDB is a non-relational NoSQL database that does not support table joins. Instead, applications read data from one table at a time. The following sections describe different use cases for reading data, and how to perform these tasks with a relational database and with DynamoDB. **Topics** + [ # Differences in reading an item using its primary key ](SQLtoNoSQL.ReadData.SingleItem.md) + [ # Differences in querying a table ](SQLtoNoSQL.ReadData.Query.md) + [ # Differences in scanning a table ](SQLtoNoSQL.ReadData.Scan.md) # Differences in reading an item using its primary key One common access pattern for databases is to read a single item from a table. You have to specify the primary key of the item you want. **Topics** + [ ## Reading an item using its primary key with SQL ](#SQLtoNoSQL.ReadData.SingleItem.SQL) + [ ## Reading an item using its primary key in DynamoDB ](#SQLtoNoSQL.ReadData.SingleItem.DynamoDB) ## Reading an item using its primary key with SQL In SQL, you would use the `SELECT` statement to retrieve data from a table. You can request one or more columns in the result (or all of them, if you use the `*` operator). The `WHERE` clause determines which rows to return. The following is a `SELECT` statement to retrieve a single row from the *Music* table. The `WHERE` clause specifies the primary key values. ``` SELECT * FROM Music WHERE Artist='No One You Know' AND SongTitle = 'Call Me Today' ``` You can modify this query to retrieve only a subset of the columns. ``` SELECT AlbumTitle, Year, Price FROM Music WHERE Artist='No One You Know' AND SongTitle = 'Call Me Today' ``` Note that the primary key for this table consists of *Artist* and *SongTitle*. ## Reading an item using its primary key in DynamoDB In Amazon DynamoDB, you can use either the DynamoDB API or [PartiQL](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.html) (a SQL-compatible query language) to read an item from a table. ------ #### [ DynamoDB API ] With the DynamoDB API, you use the `PutItem` operation to add an item to a table. DynamoDB provides the `GetItem` operation for retrieving an item by its primary key. `GetItem` is highly efficient because it provides direct access to the physical location of the item. (For more information, see [Partitions and data distribution in DynamoDB](HowItWorks.Partitions.md).) By default, `GetItem` returns the entire item with all of its attributes. ``` { TableName: "Music", Key: { "Artist": "No One You Know", "SongTitle": "Call Me Today" } } ``` You can add a `ProjectionExpression` parameter to return only some of the attributes. ``` { TableName: "Music", Key: { "Artist": "No One You Know", "SongTitle": "Call Me Today" }, "ProjectionExpression": "AlbumTitle, Year, Price" } ``` Note that the primary key for this table consists of *Artist* and *SongTitle*. The DynamoDB `GetItem` operation is very efficient. It uses the primary key values to determine the exact storage location of the item in question, and retrieves it directly from there. The SQL `SELECT` statement is similarly efficient, in the case of retrieving items by primary key values. The SQL `SELECT` statement supports many kinds of queries and table scans. DynamoDB provides similar functionality with its `Query` and `Scan` operations, which are described in [Differences in querying a table](SQLtoNoSQL.ReadData.Query.md) and [Differences in scanning a table](SQLtoNoSQL.ReadData.Scan.md). The SQL `SELECT` statement can perform table joins, allowing you to retrieve data from multiple tables at the same time. Joins are most effective where the database tables are normalized and the relationships among the tables are clear. However, if you join too many tables in one `SELECT` statement application performance can be affected. You can work around such issues by using database replication, materialized views, or query rewrites. DynamoDB is a nonrelational database and doesn't support table joins. If you are migrating an existing application from a relational database to DynamoDB, you need to denormalize your data model to eliminate the need for joins. ------ #### [ PartiQL for DynamoDB ] With PartiQL, you use the `ExecuteStatement` operation to read an item from a table, using the PartiQL `Select` statement. ``` SELECT AlbumTitle, Year, Price FROM Music WHERE Artist='No One You Know' AND SongTitle = 'Call Me Today' ``` Note that the primary key for this table consists of Artist and SongTitle. **Note** The select PartiQL statement can also be used to Query or Scan a DynamoDB table For code examples using `Select` and `ExecuteStatement`, see [PartiQL select statements for DynamoDB](ql-reference.select.md). ------ # Differences in querying a table Another common access pattern is reading multiple items from a table, based on your query criteria. **Topics** + [ ## Querying a table with SQL ](#SQLtoNoSQL.ReadData.Query.SQL) + [ ## Querying a table in DynamoDB ](#SQLtoNoSQL.ReadData.Query.DynamoDB) ## Querying a table with SQL When using SQL the `SELECT` statement lets you query on key columns, non-key columns, or any combination. The `WHERE` clause determines which rows are returned, as shown in the following examples. ``` /* Return a single song, by primary key */ SELECT * FROM Music WHERE Artist='No One You Know' AND SongTitle = 'Call Me Today'; ``` ``` /* Return all of the songs by an artist */ SELECT * FROM Music WHERE Artist='No One You Know'; ``` ``` /* Return all of the songs by an artist, matching first part of title */ SELECT * FROM Music WHERE Artist='No One You Know' AND SongTitle LIKE 'Call%'; ``` ``` /* Return all of the songs by an artist, only if the price is less than 1.00 */ SELECT * FROM Music WHERE Artist='No One You Know' AND Price < 1.00; ``` Note that the primary key for this table consists of *Artist* and *SongTitle*. ## Querying a table in DynamoDB In Amazon DynamoDB, you can use either the DynamoDB API or [PartiQL](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.html) (a SQL-compatible query language) to query an item from a table. ------ #### [ DynamoDB API ] With Amazon DynamoDB, you can use the `Query` operation to retrieve data in a similar fashion. The `Query` operation provides quick, efficient access to the physical locations where the data is stored. For more information, see [Partitions and data distribution in DynamoDB](HowItWorks.Partitions.md). You can use `Query` with any table or secondary index. You must specify an equality condition for the partition key's value, and you can optionally provide another condition for the sort key attribute if it is defined. The `KeyConditionExpression` parameter specifies the key values that you want to query. You can use an optional `FilterExpression` to remove certain items from the results before they are returned to you. In DynamoDB, you must use `ExpressionAttributeValues` as placeholders in expression parameters (such as `KeyConditionExpression` and `FilterExpression`). This is analogous to the use of *bind variables* in relational databases, where you substitute the actual values into the `SELECT` statement at runtime. Note that the primary key for this table consists of *Artist* and *SongTitle*. The following are some DynamoDB `Query` examples. ``` // Return a single song, by primary key { TableName: "Music", KeyConditionExpression: "Artist = :a and SongTitle = :t", ExpressionAttributeValues: { ":a": "No One You Know", ":t": "Call Me Today" } } ``` ``` // Return all of the songs by an artist { TableName: "Music", KeyConditionExpression: "Artist = :a", ExpressionAttributeValues: { ":a": "No One You Know" } } ``` ``` // Return all of the songs by an artist, matching first part of title { TableName: "Music", KeyConditionExpression: "Artist = :a and begins_with(SongTitle, :t)", ExpressionAttributeValues: { ":a": "No One You Know", ":t": "Call" } } ``` ``` // Return all of the songs by an artist, only if the price is less than 1.00 { TableName: "Music", KeyConditionExpression: "Artist = :a", FilterExpression: "Price < :p", ExpressionAttributeValues: { ":a": "No One You Know", ":p": 1.00 } } ``` **Note** A `FilterExpression` is applied after the `Query` reads matching items, so it does not reduce the read capacity consumed. Where possible, model your data so that range conditions use `KeyConditionExpression` on the sort key for efficient queries. For more information, see [Querying tables in DynamoDB](Query.md). ------ #### [ PartiQL for DynamoDB ] With PartiQL, you can perform a query by using the `ExecuteStatement` operation and the `Select` statement on the partition key. ``` SELECT AlbumTitle, Year, Price FROM Music WHERE Artist='No One You Know' ``` Using the `SELECT` statement in this way returns all the songs associated with this particular `Artist`. For code examples using `Select` and `ExecuteStatement`, see [PartiQL select statements for DynamoDB](ql-reference.select.md). ------ # Differences in scanning a table In SQL, a `SELECT` statement without a `WHERE` clause will return every row in a table. In Amazon DynamoDB, the `Scan` operation does the same thing. In both cases, you can retrieve all of the items or just some of them. Whether you are using a SQL or a NoSQL database, scans should be used sparingly because they can consume large amounts of system resources. Sometimes a scan is appropriate (such as scanning a small table) or unavoidable (such as performing a bulk export of data). However, as a general rule, you should design your applications to avoid performing scans. For more information, see [Querying tables in DynamoDB](Query.md). **Note** Doing a bulk export also creates at least 1 file per partition. All of the items in each file are from that particular partition's hashed keyspace. **Topics** + [ ## Scanning a table with SQL ](#SQLtoNoSQL.ReadData.Scan.SQL) + [ ## Scanning a table in DynamoDB ](#SQLtoNoSQL.ReadData.Scan.DynamoDB) ## Scanning a table with SQL When using SQL you can scan a table and retrieve all of its data by using a `SELECT` statement without specifying a `WHERE` clause. You can request one or more columns in the result. Or you can request all of them if you use the wildcard character (\$1). The following are examples of using a `SELECT` statement. ``` /* Return all of the data in the table */ SELECT * FROM Music; ``` ``` /* Return all of the values for Artist and Title */ SELECT Artist, Title FROM Music; ``` ## Scanning a table in DynamoDB In Amazon DynamoDB, you can use either the DynamoDB API or [PartiQL](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.html) (a SQL-compatible query language) to perform a scan on a table. ------ #### [ DynamoDB API ] With the DynamoDB API, you use the `Scan` operation to return one or more items and item attributes by accessing every item in a table or a secondary index. ``` // Return all of the data in the table { TableName: "Music" } ``` ``` // Return all of the values for Artist and Title { TableName: "Music", ProjectionExpression: "Artist, Title" } ``` The `Scan` operation also provides a `FilterExpression` parameter, which you can use to discard items that you do not want to appear in the results. A `FilterExpression` is applied after the scan is performed, but before the results are returned to you. (This is not recommended with large tables. You are still charged for the entire `Scan`, even if only a few matching items are returned.) ------ #### [ PartiQL for DynamoDB ] With PartiQL, you perform a scan by using the `ExecuteStatement` operation to return all the contents for a table using the `Select` statement. ``` SELECT AlbumTitle, Year, Price FROM Music ``` Note that this statement will return all items for in the Music table. For code examples using `Select` and `ExecuteStatement`, see [PartiQL select statements for DynamoDB](ql-reference.select.md). ------ # Differences between a relational (SQL) database and DynamoDB when managing indexes Indexes give you access to alternate query patterns, and can speed up queries. This section compares and contrasts index creation and usage in SQL and Amazon DynamoDB. Whether you are using a relational database or DynamoDB, you should be judicious with index creation. Whenever a write occurs on a table, all of the table's indexes must be updated. In a write-heavy environment with large tables, this can consume large amounts of system resources. In a read-only or read-mostly environment, this is not as much of a concern. However, you should ensure that the indexes are actually being used by your application, and not simply taking up space. **Topics** + [ ## Differences between a relational (SQL) database and DynamoDB when creating an index ](#SQLtoNoSQL.Indexes.Creating) + [ ## Differences between a relational (SQL) database and DynamoDB when querying and scanning an index ](#SQLtoNoSQL.Indexes.QueryAndScan) ## Differences between a relational (SQL) database and DynamoDB when creating an index Compare the `CREATE INDEX` statement in SQL with the `UpdateTable` operation in Amazon DynamoDB. **Topics** + [ ### Creating an index with SQL ](#SQLtoNoSQL.Indexes.Creating.SQL) + [ ### Creating an index in DynamoDB ](#SQLtoNoSQL.Indexes.Creating.DynamoDB) ### Creating an index with SQL In a relational database, an index is a data structure that lets you perform fast queries on different columns in a table. You can use the `CREATE INDEX` SQL statement to add an index to an existing table, specifying the columns to be indexed. After the index has been created, you can query the data in the table as usual, but now the database can use the index to quickly find the specified rows in the table instead of scanning the entire table. After you create an index, the database maintains it for you. Whenever you modify data in the table, the index is automatically modified to reflect changes in the table. In MySQL, you would create an index like the following. ``` CREATE INDEX GenreAndPriceIndex ON Music (genre, price); ``` ### Creating an index in DynamoDB In DynamoDB, you can create and use a *secondary index* for similar purposes. Indexes in DynamoDB are different from their relational counterparts. When you create a secondary index, you must specify its key attributes—a partition key and a sort key. After you create the secondary index, you can `Query` it or `Scan` it just as you would with a table. DynamoDB does not have a query optimizer, so a secondary index is only used when you `Query` it or `Scan` it. DynamoDB supports two different kinds of indexes: + Global secondary indexes – The primary key of the index can be any two attributes from its table. + Local secondary indexes – The partition key of the index must be the same as the partition key of its table. However, the sort key can be any other attribute. DynamoDB ensures that the data in a secondary index is eventually consistent with its table. You can request strongly consistent `Query` or `Scan` operations on a table or a local secondary index. However, global secondary indexes support only eventual consistency. You can add a global secondary index to an existing table, using the `UpdateTable` operation and specifying `GlobalSecondaryIndexUpdates`. ``` { TableName: "Music", AttributeDefinitions:[ {AttributeName: "Genre", AttributeType: "S"}, {AttributeName: "Price", AttributeType: "N"} ], GlobalSecondaryIndexUpdates: [ { Create: { IndexName: "GenreAndPriceIndex", KeySchema: [ {AttributeName: "Genre", KeyType: "HASH"}, //Partition key {AttributeName: "Price", KeyType: "RANGE"}, //Sort key ], Projection: { "ProjectionType": "ALL" }, ProvisionedThroughput: { // Only specified if using provisioned mode "ReadCapacityUnits": 1,"WriteCapacityUnits": 1 } } } ] } ``` You must provide the following parameters to `UpdateTable`: + `TableName` – The table that the index will be associated with. + `AttributeDefinitions` – The data types for the key schema attributes of the index. + `GlobalSecondaryIndexUpdates` – Details about the index you want to create: + `IndexName` – A name for the index. + `KeySchema` – The attributes that are used for the index's primary key. + `Projection` – Attributes from the table that are copied to the index. In this case, `ALL` means that all of the attributes are copied. + `ProvisionedThroughput (for provisioned tables)` – The number of reads and writes per second that you need for this index. (This is separate from the provisioned throughput settings of the table.) Part of this operation involves backfilling data from the table into the new index. During backfilling, the table remains available. However, the index is not ready until its `Backfilling` attribute changes from true to false. You can use the `DescribeTable` operation to view this attribute. ## Differences between a relational (SQL) database and DynamoDB when querying and scanning an index Compare querying and scanning an index using the SELECT statement in SQL with the `Query` and `Scan` operations in Amazon DynamoDB. **Topics** + [ ### Querying and scanning an index with SQL ](#SQLtoNoSQL.Indexes.QueryAndScan.SQL) + [ ### Querying and scanning an index in DynamoDB ](#SQLtoNoSQL.Indexes.QueryAndScan.DynamoDB) ### Querying and scanning an index with SQL In a relational database, you do not work directly with indexes. Instead, you query tables by issuing `SELECT` statements, and the query optimizer can make use of any indexes. A *query optimizer* is a relational database management system (RDBMS) component that evaluates the available indexes and determines whether they can be used to speed up a query. If the indexes can be used to speed up a query, the RDBMS accesses the index first and then uses it to locate the data in the table. Here are some SQL statements that can use *GenreAndPriceIndex* to improve performance. We assume that the *Music* table has enough data in it that the query optimizer decides to use this index, rather than simply scanning the entire table. ``` /* All of the rock songs */ SELECT * FROM Music WHERE Genre = 'Rock'; ``` ``` /* All of the cheap country songs */ SELECT Artist, SongTitle, Price FROM Music WHERE Genre = 'Country' AND Price < 0.50; ``` ### Querying and scanning an index in DynamoDB In DynamoDB, you perform `Query` and `Scan` operations directly on the index, in the same way that you would on a table. You can use either the DynamoDB API or [PartiQL](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.html) (a SQL-compatible query language) to query or scan the index. You must specify both `TableName` and `IndexName`. The following are some queries on *GenreAndPriceIndex* in DynamoDB. (The key schema for this index consists of *Genre* and *Price*.) ------ #### [ DynamoDB API ] ``` // All of the rock songs { TableName: "Music", IndexName: "GenreAndPriceIndex", KeyConditionExpression: "Genre = :genre", ExpressionAttributeValues: { ":genre": "Rock" }, }; ``` This example uses a `ProjectionExpression` to indicate that you only want some of the attributes, rather than all of them, to appear in the results. ``` // All of the cheap country songs { TableName: "Music", IndexName: "GenreAndPriceIndex", KeyConditionExpression: "Genre = :genre and Price < :price", ExpressionAttributeValues: { ":genre": "Country", ":price": 0.50 }, ProjectionExpression: "Artist, SongTitle, Price" }; ``` The following is a scan on *GenreAndPriceIndex*. ``` // Return all of the data in the index { TableName: "Music", IndexName: "GenreAndPriceIndex" } ``` ------ #### [ PartiQL for DynamoDB ] With PartiQL, you use the PartiQL `Select` statement to perform queries and scans on the index. ``` // All of the rock songs SELECT * FROM Music.GenreAndPriceIndex WHERE Genre = 'Rock' ``` ``` // All of the cheap country songs SELECT * FROM Music.GenreAndPriceIndex WHERE Genre = 'Rock' AND Price < 0.50 ``` The following is a scan on *GenreAndPriceIndex*. ``` // Return all of the data in the index SELECT * FROM Music.GenreAndPriceIndex ``` **Note** For code examples using `Select`, see [PartiQL select statements for DynamoDB](ql-reference.select.md). ------ # Differences between a relational (SQL) database and DynamoDB when modifying data in a table The SQL language provides the `UPDATE` statement for modifying data. Amazon DynamoDB uses the `UpdateItem` operation to accomplish similar tasks. **Topics** + [ ## Modifying data in a table with SQL ](#SQLtoNoSQL.UpdateData.SQL) + [ ## Modifying data in a table in DynamoDB ](#SQLtoNoSQL.UpdateData.DynamoDB) ## Modifying data in a table with SQL In SQL, you would use the `UPDATE` statement to modify one or more rows. The `SET` clause specifies new values for one or more columns, and the `WHERE` clause determines which rows are modified. The following is an example. ``` UPDATE Music SET RecordLabel = 'Global Records' WHERE Artist = 'No One You Know' AND SongTitle = 'Call Me Today'; ``` If no rows match the `WHERE` clause, the `UPDATE` statement has no effect. ## Modifying data in a table in DynamoDB In DynamoDB, you can use either the DynamoDB API or [PartiQL](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.html) (a SQL-compatible query language) to modify a single item. If you want to modify multiple items, you must use multiple operations. ------ #### [ DynamoDB API ] With the DynamoDB API, you use the `UpdateItem` operation to modify a single item. ``` { TableName: "Music", Key: { "Artist":"No One You Know", "SongTitle":"Call Me Today" }, UpdateExpression: "SET RecordLabel = :label", ExpressionAttributeValues: { ":label": "Global Records" } } ``` You must specify the `Key` attributes of the item to be modified and an `UpdateExpression` to specify attribute values. `UpdateItem` behaves like an "upsert" operation. The item is updated if it exists in the table, but if not, a new item is added (inserted). `UpdateItem` supports *conditional writes*, where the operation succeeds only if a specific `ConditionExpression` evaluates to true. For example, the following `UpdateItem` operation does not perform the update unless the price of the song is greater than or equal to 2.00. ``` { TableName: "Music", Key: { "Artist":"No One You Know", "SongTitle":"Call Me Today" }, UpdateExpression: "SET RecordLabel = :label", ConditionExpression: "Price >= :p", ExpressionAttributeValues: { ":label": "Global Records", ":p": 2.00 } } ``` `UpdateItem` also supports *atomic counters*, or attributes of type `Number` that can be incremented or decremented. Atomic counters are similar in many ways to sequence generators, identity columns, or autoincrement fields in SQL databases. The following is an example of an `UpdateItem` operation to initialize a new attribute (*Plays*) to keep track of the number of times a song has been played. ``` { TableName: "Music", Key: { "Artist":"No One You Know", "SongTitle":"Call Me Today" }, UpdateExpression: "SET Plays = :val", ExpressionAttributeValues: { ":val": 0 }, ReturnValues: "UPDATED_NEW" } ``` The `ReturnValues` parameter is set to `UPDATED_NEW`, which returns the new values of any attributes that were updated. In this case, it returns 0 (zero). Whenever someone plays this song, we can use the following `UpdateItem` operation to increment *Plays* by one. ``` { TableName: "Music", Key: { "Artist":"No One You Know", "SongTitle":"Call Me Today" }, UpdateExpression: "SET Plays = Plays + :incr", ExpressionAttributeValues: { ":incr": 1 }, ReturnValues: "UPDATED_NEW" } ``` ------ #### [ PartiQL for DynamoDB ] With PartiQL, you use the `ExecuteStatement` operation to modify an item in a table, using the PartiQL `Update` statement. The primary key for this table consists of *Artist* and *SongTitle*. You must specify values for these attributes. ``` UPDATE Music SET RecordLabel ='Global Records' WHERE Artist='No One You Know' AND SongTitle='Call Me Today' ``` You can also modify multiple fields at once, such as in the following example. ``` UPDATE Music SET RecordLabel = 'Global Records' SET AwardsWon = 10 WHERE Artist ='No One You Know' AND SongTitle='Call Me Today' ``` `Update` also supports *atomic counters*, or attributes of type `Number` that can be incremented or decremented. Atomic counters are similar in many ways to sequence generators, identity columns, or autoincrement fields in SQL databases. The following is an example of an `Update` statement to initialize a new attribute (*Plays*) to keep track of the number of times a song has been played. ``` UPDATE Music SET Plays = 0 WHERE Artist='No One You Know' AND SongTitle='Call Me Today' ``` Whenever someone plays this song, we can use the following `Update` statement to increment *Plays* by one. ``` UPDATE Music SET Plays = Plays + 1 WHERE Artist='No One You Know' AND SongTitle='Call Me Today' ``` **Note** For code examples using `Update` and `ExecuteStatement`, see [PartiQL update statements for DynamoDB](ql-reference.update.md). ------ # Differences between a relational (SQL) database and DynamoDB when deleting data from a table In SQL, the `DELETE` statement removes one or more rows from a table. Amazon DynamoDB uses the `DeleteItem` operation to delete one item at a time. **Topics** + [ ## Deleting data from a table with SQL ](#SQLtoNoSQL.DeleteData.SQL) + [ ## Deleting data from a table in DynamoDB ](#SQLtoNoSQL.DeleteData.DynamoDB) ## Deleting data from a table with SQL In SQL, you use the `DELETE` statement to delete one or more rows. The `WHERE` clause determines the rows that you want to modify. The following is an example. ``` DELETE FROM Music WHERE Artist = 'The Acme Band' AND SongTitle = 'Look Out, World'; ``` You can modify the `WHERE` clause to delete multiple rows. For example, you could delete all of the songs by a particular artist, as shown in the following example. ``` DELETE FROM Music WHERE Artist = 'The Acme Band' ``` ## Deleting data from a table in DynamoDB In DynamoDB, you can use either the DynamoDB API or [PartiQL](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.html) (a SQL-compatible query language) to delete a single item. If you want to modify multiple items, you must use multiple operations. ------ #### [ DynamoDB API ] With the DynamoDB API, you use the `DeleteItem` operation to delete data from a table, one item at a time. You must specify the item's primary key values. ``` { TableName: "Music", Key: { Artist: "The Acme Band", SongTitle: "Look Out, World" } } ``` **Note** In addition to `DeleteItem`, Amazon DynamoDB supports a `BatchWriteItem` operation for deleting multiple items at the same time. `DeleteItem` supports *conditional writes*, where the operation succeeds only if a specific `ConditionExpression` evaluates to true. For example, the following `DeleteItem` operation deletes the item only if it has a *RecordLabel* attribute. ``` { TableName: "Music", Key: { Artist: "The Acme Band", SongTitle: "Look Out, World" }, ConditionExpression: "attribute_exists(RecordLabel)" } ``` ------ #### [ PartiQL for DynamoDB ] With PartiQL, you use the `Delete` statement through the `ExecuteStatement` operation to delete data from a table, one item at a time. You must specify the item's primary key values. The primary key for this table consists of *Artist* and *SongTitle*. You must specify values for these attributes. ``` DELETE FROM Music WHERE Artist = 'Acme Band' AND SongTitle = 'PartiQL Rocks' ``` You can also specify additional conditions for the operation. The following `DELETE` operation only deletes the item if it has more than 11 *Awards*. ``` DELETE FROM Music WHERE Artist = 'Acme Band' AND SongTitle = 'PartiQL Rocks' AND Awards > 11 ``` **Note** For code examples using `DELETE` and `ExecuteStatement`, see [PartiQL delete statements for DynamoDB](ql-reference.delete.md). ------ # Differences between a relational (SQL) database and DynamoDB when removing a table In SQL, you use the `DROP TABLE` statement to remove a table. In Amazon DynamoDB, you use the `DeleteTable` operation. **Topics** + [ ## Removing a table with SQL ](#SQLtoNoSQL.RemoveTable.SQL) + [ ## Removing a table in DynamoDB ](#SQLtoNoSQL.RemoveTable.DynamoDB) ## Removing a table with SQL When you no longer need a table and want to discard it permanently, you would use the `DROP TABLE` statement in SQL. ``` DROP TABLE Music; ``` After a table is dropped, it cannot be recovered. (Some relational databases do allow you to undo a `DROP TABLE` operation, but this is vendor-specific functionality and it is not widely implemented.) ## Removing a table in DynamoDB In DynamoDB, `DeleteTable` is a similar operation. In the following example, the table is permanently deleted. ``` { TableName: "Music" } ``` # Amazon DynamoDB learning resources and tools You can use the following additional resources to understand and work with DynamoDB. **Topics** + [ ## Tools for coding and visualization ](#AdditionalResources.Tools) + [ ## Prescriptive Guidance articles ](#AdditionalResources.PrescriptiveGuidance) + [ ## Knowledge Center articles ](#AdditionalResources.KnowledgeCenter) + [ ## Blog posts, repositories, and guides ](#AdditionalResources.Guides) + [ ## Data modeling and design pattern presentations ](#AdditionalResources.DataModeling) + [ ## Training courses ](#AdditionalResources.Training) ## Tools for coding and visualization You can use the following coding and visualization tools to work with DynamoDB: + [NoSQL Workbench for Amazon DynamoDB](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/workbench.html) – A unified, visual tool that helps you design, create, query, and manage DynamoDB tables. It provides data modeling, data visualization, and query development features. + [Dynobase](https://dynobase.dev/) – A desktop tool that makes it easy to see your DynamoDB tables and work with them, create app code, and edit records with real-time validation. + [DynamoDB Toolbox](https://github.com/jeremydaly/dynamodb-toolbox) – A project from Jeremy Daly that provides helpful utilities for working with data modeling and JavaScript and Node.js. + [DynamoDB Streams Processor](https://github.com/jeremydaly/dynamodb-streams-processor) – A simple tool that you can use to work with [DynamoDB streams](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Streams.html). ## Prescriptive Guidance articles AWS Prescriptive Guidance provides time-tested strategies, guides, and patterns to help accelerate your projects. These resources were developed by AWS technology experts and the global community of AWS Partners, based on their years of experience helping customers achieve their business objectives. **Data modeling and migration** + [A hierarchical data model in DynamoDB](https://docs.aws.amazon.com/prescriptive-guidance/latest/dynamodb-hierarchical-data-model/introduction.html) + [Modeling data with DynamoDB](https://docs.aws.amazon.com/prescriptive-guidance/latest/dynamodb-data-modeling/welcome.html) + [Migrate an Oracle database to DynamoDB using AWS DMS](https://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/migrate-an-oracle-database-to-amazon-dynamodb-using-aws-dms.html) **Global tables** + [Using Amazon DynamoDB global tables](https://docs.aws.amazon.com/prescriptive-guidance/latest/dynamodb-global-tables/introduction.html) **Serverless** + [Implement the serverless saga pattern with AWS Step Functions](https://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/implement-the-serverless-saga-pattern-by-using-aws-step-functions.html) **SaaS architecture** + [Manage tenants across multiple SaaS products on a single control plane](https://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/manage-tenants-across-multiple-saas-products-on-a-single-control-plane.html) + [Tenant onboarding in SaaS architecture for the silo model using C\$1 and AWS CDK](https://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/tenant-onboarding-in-saas-architecture-for-the-silo-model-using-c-and-aws-cdk.html) **Data protection and data movement** + [Configure cross-account access to Amazon DynamoDB](https://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/configure-cross-account-access-to-amazon-dynamodb.html) + [Full table copy options for DynamoDB](https://docs.aws.amazon.com/prescriptive-guidance/latest/dynamodb-full-table-copy-options/) + [Disaster recovery strategy for databases on AWS](https://docs.aws.amazon.com/prescriptive-guidance/latest/strategy-database-disaster-recovery/) **Miscellaneous** + [Help enforce tagging in DynamoDB](https://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/help-enforce-dynamodb-tagging.html) **Prescriptive guidance video walkthroughs** + [Using Serverless Architecture to Create Data Pipelines](https://youtu.be/JiWHomdh1oI?) + [Novartis - Buying Engine: AI-powered Procurement Portal](https://youtu.be/vp8oPiHN4cA) + [Veritiv: Enable Insights to Forecast Sales Demand on AWS Data Lakes](https://youtu.be/jg85DzUZ9Ac) + [mimik: Hybrid Edge Cloud Leveraging AWS to Support Edge Microservice Mesh](https://youtu.be/-S-R7MWRpaI) + [Change Data Capture with Amazon DynamoDB](https://youtu.be/6YVjzD-70p4) For additional Prescriptive Guidance articles and videos for DynamoDB, see [Prescriptive Guidance](https://tiny.amazon.com/fiui3cog/ForinternaldemoofnewpageExternalURLwillbeneededforlive). ## Knowledge Center articles The AWS Knowledge Center articles and videos cover the most frequent questions and requests that we receive from AWS customers. The following are some current Knowledge Center articles on specific tasks that relate to DynamoDB: **Cost optimization** + [How do I optimize costs with Amazon DynamoDB?](https://repost.aws/knowledge-center/dynamodb-optimize-costs) **Throttling and latency** + [How can I troubleshoot high latency on an Amazon DynamoDB table?](https://repost.aws/knowledge-center/dynamodb-high-latency) + [Why is my DynamoDB table being throttled?](https://repost.aws/knowledge-center/dynamodb-table-throttled) + [Why is my on-demand DynamoDB table being throttled?](https://repost.aws/knowledge-center/on-demand-table-throttling-dynamodb) **Pagination** + [How do I implement pagination in DynamoDB](https://repost.aws/knowledge-center/dynamodb-implement-pagination) **Transactions** + [Why is my `TransactWriteItems` API call failing in DynamoDB](https://repost.aws/knowledge-center/dynamodb-transactwriteitems) **Troubleshooting** []() + [How do I resolve issues with DynamoDB auto scaling?](https://repost.aws/knowledge-center/dynamodb-auto-scaling) + [How do I troubleshoot HTTP 4XX errors in DynamoDB](https://repost.aws/knowledge-center/usererrors-dynamodb-table) For additional articles and videos for DynamoDB, see the [Knowledge Center articles](https://repost.aws/search/knowledge-center?globalSearch=dynamodb). ## Blog posts, repositories, and guides In addition to the [DynamoDB Developer Guide](Introduction.md), there are many useful resources for working with DynamoDB. Here are some selected blog posts, repositories, and guides for working with DynamoDB: + AWS repository of [DynamoDB code examples](https://github.com/aws-samples/aws-dynamodb-examples) in various AWS SDK languages: [Node.js](https://github.com/aws-samples/aws-dynamodb-examples/tree/master/examples/SDK/node.js), [Java](https://github.com/aws-samples/aws-dynamodb-examples/tree/master/examples/SDK/java), [Python](https://github.com/aws-samples/aws-dynamodb-examples/tree/master/examples/SDK/python), [.Net](https://github.com/aws-samples/aws-dynamodb-examples/tree/master/examples/SDK/dotnet), [Go](https://github.com/aws-samples/aws-dynamodb-examples/tree/master/examples/SDK/golang), and [Rust](https://github.com/aws-samples/aws-dynamodb-examples/tree/master/examples/SDK/rust). + [The DynamoDB Book](https://www.dynamodbbook.com/) – A comprehensive guide from [Alex DeBrie](https://twitter.com/alexbdebrie) that teaches a strategy-driven approach to data modeling with DynamoDB. + [DynamoDB guide](https://www.dynamodbguide.com/) – An open guide from [Alex DeBrie](https://twitter.com/alexbdebrie) that walks through the basic concepts and advanced features of the DynamoDB NoSQL database. + [How to switch from RDBMS to DynamoDB in 20 easy steps](https://www.jeremydaly.com/how-to-switch-from-rdbms-to-dynamodb-in-20-easy-steps/) – A list of useful steps for learning data modeling from [Jeremy Daly](https://twitter.com/jeremy_daly). + [DynamoDB JavaScript DocumentClient cheat sheet](https://github.com/dabit3/dynamodb-documentclient-cheat-sheet) – A cheat sheet to help you get started building applications with DynamoDB in a Node.js or JavaScript environment. + [ DynamoDB Core Concept Videos](https://www.youtube.com/playlist?list=PLJo-rJlep0EDNtcDeHDMqsXJcuKMcrC5F) – This playlist covers many of the core concepts of DynamoDB. ## Data modeling and design pattern presentations You can use the following resources on data modeling and design patterns to help you get the most out of DynamoDB: + [AWS re:Invent 2019: Data modeling with DynamoDB](https://www.youtube.com/watch?v=DIQVJqiSUkE) + A talk by [Alex DeBrie](https://twitter.com/alexbdebrie) that helps you started with the principles of DynamoDB data modeling. + [AWS re:Invent 2020: Data modeling with DynamoDB – Part 1](https://www.youtube.com/watch?v=fiP2e-g-r4g) + [AWS re:Invent 2020: Data modeling with DynamoDB – Part 2](https://www.youtube.com/watch?v=0uLF1tjI_BI) + [AWS re:Invent 2017: Advanced design patterns](https://www.youtube.com/watch?v=jzeKPKpucS0) + [AWS re:Invent 2018: Advanced design patterns](https://www.youtube.com/watch?v=HaEPXoXVf2k) + [AWS re:Invent 2019: Advanced design patterns](https://www.youtube.com/watch?v=6yqfmXiZTlM) + Jeremy Daly shares his [12 key takeaways](https://www.jeremydaly.com/takeaways-from-dynamodb-deep-dive-advanced-design-patterns-dat403/) from this session. + [AWS re:Invent 2020: DynamoDB advanced design patterns – Part 1](https://www.youtube.com/watch?v=MF9a1UNOAQo&index=1) + [AWS re:Invent 2020: DynamoDB advanced design patterns – Part 2](https://www.youtube.com/watch?v=_KNrRdWD25M&index=2) + [DynamoDB Office Hours on Twitch](https://amazondynamodbofficehrs.splashthat.com/) **Note** Each session covers different use cases and examples. ## Training courses There are many different training courses and educational options for learning more about DynamoDB. Here are some current examples: + [ Developing with Amazon DynamoDB](https://www.aws.training/Details/Curriculum?id=65583) – Designed by AWS to take you from beginner to expert in developing real-world applications with data modeling for Amazon DynamoDB. + [DynamoDB deep dive course](https://www.pluralsight.com/courses/aws-dynamodb-deep-dive-2019) – A course from Pluralsight. + [Amazon DynamoDB: Building NoSQL database-driven applications](https://www.edx.org/course/amazon-dynamodb-building-nosql-database-driven-app) – A course from the AWS Training and Certification team hosted on edX.