# 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"
}
```