# PartiQL - a SQL-compatible query language for Amazon DynamoDB Amazon DynamoDB supports [PartiQL](https://partiql.org/), a SQL-compatible query language, to select, insert, update, and delete data in Amazon DynamoDB. Using PartiQL, you can easily interact with DynamoDB tables and run ad hoc queries using the AWS Management Console, NoSQL Workbench, AWS Command Line Interface, and DynamoDB APIs for PartiQL. PartiQL operations provide the same availability, latency, and performance as the other DynamoDB data plane operations. The following sections describe the DynamoDB implementation of PartiQL. **Topics** + [ ## What is PartiQL? ](#ql-reference.what-is) + [ ## PartiQL in Amazon DynamoDB ](#ql-reference.what-is) + [Getting started](ql-gettingstarted.md) + [Data types](ql-reference.data-types.md) + [Statements](ql-reference.statements.md) + [Functions](ql-functions.md) + [Operators](ql-operators.md) + [Transactions](ql-reference.multiplestatements.transactions.md) + [Batch operations](ql-reference.multiplestatements.batching.md) + [IAM policies](ql-iam.md) ## What is PartiQL? *PartiQL* provides SQL-compatible query access across multiple data stores containing structured data, semistructured data, and nested data. It is widely used within Amazon and is now available as part of many AWS services, including DynamoDB. For the PartiQL specification and a tutorial on the core query language, see the [PartiQL documentation](https://partiql.org/docs.html). **Note** Amazon DynamoDB supports a *subset* of the [PartiQL](https://partiql.org/) query language. Amazon DynamoDB does not support the [Amazon ion](http://amzn.github.io/ion-docs/) data format or Amazon Ion literals. ## PartiQL in Amazon DynamoDB To run PartiQL queries in DynamoDB, you can use: + The DynamoDB console + The NoSQL Workbench + The AWS Command Line Interface (AWS CLI) + The DynamoDB APIs For information about using these methods to access DynamoDB, see [Accessing DynamoDB](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/AccessingDynamoDB.html). # Getting started with PartiQL for DynamoDB This section describes how to use PartiQL for DynamoDB from the Amazon DynamoDB console, the AWS Command Line Interface (AWS CLI), and DynamoDB APIs. In the following examples, the DynamoDB table that is defined in the [Getting started with DynamoDB](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/GettingStartedDynamoDB.html) tutorial is a pre-requisite. For information about using the DynamoDB console, AWS Command Line Interface, or DynamoDB APIs to access DynamoDB, see [Accessing DynamoDB](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/AccessingDynamoDB.html). To [download](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/workbench.settingup.html) and use the [NoSQL workbench](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/workbench.html) to build [PartiQL for DynamoDB](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.html) statements choose **PartiQL operations** at the top right corner of the NoSQL Workbench for DynamoDB [Operation builder](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/workbench.querybuilder.operationbuilder.html). ------ #### [ Console ] ![\[PartiQL editor interface that shows the result of running the Query operation on the Music table.\]](http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/images/partiqlgettingstarted.png) 1. Sign in to the AWS Management Console and open the DynamoDB console at [https://console.aws.amazon.com/dynamodb/](https://console.aws.amazon.com/dynamodb/). 1. In the navigation pane on the left side of the console, choose **PartiQL editor**. 1. Choose the **Music** table. 1. Choose **Query table**. This action generates a query that will not result in a full table scan. 1. Replace `partitionKeyValue` with the string value `Acme Band`. Replace `sortKeyValue` with the string value `Happy Day`. 1. Choose the **Run** button. 1. You can view the results of the query by choosing the **Table view** or the **JSON view** buttons. ------ #### [ NoSQL workbench ] ![\[NoSQL workbench interface. It shows a PartiQL SELECT statement that you can run on the Music table.\]](http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/images/workbench/partiql.single.png) 1. Choose **PartiQL statement**. 1. Enter the following PartiQL [SELECT statement](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.select.html) ``` SELECT * FROM Music WHERE Artist=? and SongTitle=? ``` 1. To specify a value for the `Artist` and `SongTitle` parameters: 1. Choose **Optional request parameters**. 1. Choose **Add new parameters**. 1. Choose the attribute type **string** and value `Acme Band`. 1. Repeat steps b and c, and choose type **string** and value `PartiQL Rocks`. 1. If you want to generate code, choose **Generate code**. Select your desired language from the displayed tabs. You can now copy this code and use it in your application. 1. If you want the operation to be run immediately, choose **Run**. ------ #### [ AWS CLI ] 1. Create an item in the `Music` table using the INSERT PartiQL statement. ``` aws dynamodb execute-statement --statement "INSERT INTO Music \ VALUE \ {'Artist':'Acme Band','SongTitle':'PartiQL Rocks'}" ``` 1. Retrieve an item from the Music table using the SELECT PartiQL statement. ``` aws dynamodb execute-statement --statement "SELECT * FROM Music \ WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'" ``` 1. Update an item in the `Music` table using the UPDATE PartiQL statement. ``` aws dynamodb execute-statement --statement "UPDATE Music \ SET AwardsWon=1 \ SET AwardDetail={'Grammys':[2020, 2018]} \ WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'" ``` Add a list value for an item in the `Music` table. ``` aws dynamodb execute-statement --statement "UPDATE Music \ SET AwardDetail.Grammys =list_append(AwardDetail.Grammys,[2016]) \ WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'" ``` Remove a list value for an item in the `Music` table. ``` aws dynamodb execute-statement --statement "UPDATE Music \ REMOVE AwardDetail.Grammys[2] \ WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'" ``` Add a new map member for an item in the `Music` table. ``` aws dynamodb execute-statement --statement "UPDATE Music \ SET AwardDetail.BillBoard=[2020] \ WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'" ``` Add a new string set attribute for an item in the `Music` table. ``` aws dynamodb execute-statement --statement "UPDATE Music \ SET BandMembers =<<'member1', 'member2'>> \ WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'" ``` Update a string set attribute for an item in the `Music` table. ``` aws dynamodb execute-statement --statement "UPDATE Music \ SET BandMembers =set_add(BandMembers, <<'newmember'>>) \ WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'" ``` 1. Delete an item from the `Music` table using the DELETE PartiQL statement. ``` aws dynamodb execute-statement --statement "DELETE FROM Music \ WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'" ``` ------ #### [ Java ] ``` import java.util.ArrayList; import java.util.List; import com.amazonaws.AmazonClientException; import com.amazonaws.AmazonServiceException; import software.amazon.dynamodb.AmazonDynamoDB; import software.amazon.dynamodb.AmazonDynamoDBClientBuilder; import software.amazon.dynamodb.model.AttributeValue; import software.amazon.dynamodb.model.ConditionalCheckFailedException; import software.amazon.dynamodb.model.ExecuteStatementRequest; import software.amazon.dynamodb.model.ExecuteStatementResult; import software.amazon.dynamodb.model.InternalServerErrorException; import software.amazon.dynamodb.model.ItemCollectionSizeLimitExceededException; import software.amazon.dynamodb.model.ProvisionedThroughputExceededException; import software.amazon.dynamodb.model.RequestLimitExceededException; import software.amazon.dynamodb.model.ResourceNotFoundException; import software.amazon.dynamodb.model.TransactionConflictException; public class DynamoDBPartiQGettingStarted { public static void main(String[] args) { // Create the DynamoDB Client with the region you want AmazonDynamoDB dynamoDB = createDynamoDbClient("us-west-1"); try { // Create ExecuteStatementRequest ExecuteStatementRequest executeStatementRequest = new ExecuteStatementRequest(); List parameters= getPartiQLParameters(); //Create an item in the Music table using the INSERT PartiQL statement processResults(executeStatementRequest(dynamoDB, "INSERT INTO Music value {'Artist':?,'SongTitle':?}", parameters)); //Retrieve an item from the Music table using the SELECT PartiQL statement. processResults(executeStatementRequest(dynamoDB, "SELECT * FROM Music where Artist=? and SongTitle=?", parameters)); //Update an item in the Music table using the UPDATE PartiQL statement. processResults(executeStatementRequest(dynamoDB, "UPDATE Music SET AwardsWon=1 SET AwardDetail={'Grammys':[2020, 2018]} where Artist=? and SongTitle=?", parameters)); //Add a list value for an item in the Music table. processResults(executeStatementRequest(dynamoDB, "UPDATE Music SET AwardDetail.Grammys =list_append(AwardDetail.Grammys,[2016]) where Artist=? and SongTitle=?", parameters)); //Remove a list value for an item in the Music table. processResults(executeStatementRequest(dynamoDB, "UPDATE Music REMOVE AwardDetail.Grammys[2] where Artist=? and SongTitle=?", parameters)); //Add a new map member for an item in the Music table. processResults(executeStatementRequest(dynamoDB, "UPDATE Music set AwardDetail.BillBoard=[2020] where Artist=? and SongTitle=?", parameters)); //Add a new string set attribute for an item in the Music table. processResults(executeStatementRequest(dynamoDB, "UPDATE Music SET BandMembers =<<'member1', 'member2'>> where Artist=? and SongTitle=?", parameters)); //update a string set attribute for an item in the Music table. processResults(executeStatementRequest(dynamoDB, "UPDATE Music SET BandMembers =set_add(BandMembers, <<'newmember'>>) where Artist=? and SongTitle=?", parameters)); //Retrieve an item from the Music table using the SELECT PartiQL statement. processResults(executeStatementRequest(dynamoDB, "SELECT * FROM Music where Artist=? and SongTitle=?", parameters)); //delete an item from the Music Table processResults(executeStatementRequest(dynamoDB, "DELETE FROM Music where Artist=? and SongTitle=?", parameters)); } catch (Exception e) { handleExecuteStatementErrors(e); } } private static AmazonDynamoDB createDynamoDbClient(String region) { return AmazonDynamoDBClientBuilder.standard().withRegion(region).build(); } private static List getPartiQLParameters() { List parameters = new ArrayList(); parameters.add(new AttributeValue("Acme Band")); parameters.add(new AttributeValue("PartiQL Rocks")); return parameters; } private static ExecuteStatementResult executeStatementRequest(AmazonDynamoDB client, String statement, List parameters ) { ExecuteStatementRequest request = new ExecuteStatementRequest(); request.setStatement(statement); request.setParameters(parameters); return client.executeStatement(request); } private static void processResults(ExecuteStatementResult executeStatementResult) { System.out.println("ExecuteStatement successful: "+ executeStatementResult.toString()); } // Handles errors during ExecuteStatement execution. Use recommendations in error messages below to add error handling specific to // your application use-case. private static void handleExecuteStatementErrors(Exception exception) { try { throw exception; } catch (ConditionalCheckFailedException ccfe) { System.out.println("Condition check specified in the operation failed, review and update the condition " + "check before retrying. Error: " + ccfe.getErrorMessage()); } catch (TransactionConflictException tce) { System.out.println("Operation was rejected because there is an ongoing transaction for the item, generally " + "safe to retry with exponential back-off. Error: " + tce.getErrorMessage()); } catch (ItemCollectionSizeLimitExceededException icslee) { System.out.println("An item collection is too large, you\'re using Local Secondary Index and exceeded " + "size limit of items per partition key. Consider using Global Secondary Index instead. Error: " + icslee.getErrorMessage()); } catch (Exception e) { handleCommonErrors(e); } } private static void handleCommonErrors(Exception exception) { try { throw exception; } catch (InternalServerErrorException isee) { System.out.println("Internal Server Error, generally safe to retry with exponential back-off. Error: " + isee.getErrorMessage()); } catch (RequestLimitExceededException rlee) { System.out.println("Throughput exceeds the current throughput limit for your account, increase account level throughput before " + "retrying. Error: " + rlee.getErrorMessage()); } catch (ProvisionedThroughputExceededException ptee) { System.out.println("Request rate is too high. If you're using a custom retry strategy make sure to retry with exponential back-off. " + "Otherwise consider reducing frequency of requests or increasing provisioned capacity for your table or secondary index. Error: " + ptee.getErrorMessage()); } catch (ResourceNotFoundException rnfe) { System.out.println("One of the tables was not found, verify table exists before retrying. Error: " + rnfe.getErrorMessage()); } catch (AmazonServiceException ase) { System.out.println("An AmazonServiceException occurred, indicates that the request was correctly transmitted to the DynamoDB " + "service, but for some reason, the service was not able to process it, and returned an error response instead. Investigate and " + "configure retry strategy. Error type: " + ase.getErrorType() + ". Error message: " + ase.getErrorMessage()); } catch (AmazonClientException ace) { System.out.println("An AmazonClientException occurred, indicates that the client was unable to get a response from DynamoDB " + "service, or the client was unable to parse the response from the service. Investigate and configure retry strategy. "+ "Error: " + ace.getMessage()); } catch (Exception e) { System.out.println("An exception occurred, investigate and configure retry strategy. Error: " + e.getMessage()); } } } ``` ------ ## Using parameterized statements Instead of embedding values directly in a PartiQL statement string, you can use question mark (`?`) placeholders and supply the values separately in the `Parameters` field. Each `?` is replaced by the corresponding parameter value, in the order they are provided. Using parameterized statements is a best practice because it separates the statement structure from the data values, making statements easier to read and reuse. It also avoids the need to manually format and escape attribute values in the statement string. Parameterized statements are supported in `ExecuteStatement`, `BatchExecuteStatement`, and `ExecuteTransaction` operations. The following examples retrieve an item from the `Music` table using parameterized values for the partition key and sort key. ------ #### [ AWS CLI parameterized ] ``` aws dynamodb execute-statement \ --statement "SELECT * FROM \"Music\" WHERE Artist=? AND SongTitle=?" \ --parameters '[{"S": "Acme Band"}, {"S": "PartiQL Rocks"}]' ``` ------ #### [ Java parameterized ] ``` List parameters = new ArrayList<>(); parameters.add(new AttributeValue("Acme Band")); parameters.add(new AttributeValue("PartiQL Rocks")); ExecuteStatementRequest request = new ExecuteStatementRequest() .withStatement("SELECT * FROM Music WHERE Artist=? AND SongTitle=?") .withParameters(parameters); ExecuteStatementResult result = dynamoDB.executeStatement(request); ``` ------ #### [ Python parameterized ] ``` response = dynamodb_client.execute_statement( Statement="SELECT * FROM Music WHERE Artist=? AND SongTitle=?", Parameters=[ {'S': 'Acme Band'}, {'S': 'PartiQL Rocks'} ] ) ``` ------ **Note** The Java example in the preceding getting started section uses parameterized statements throughout. The `getPartiQLParameters()` method builds the parameter list, and each statement uses `?` placeholders instead of inline values. # PartiQL data types for DynamoDB The following table lists the data types you can use with PartiQL for DynamoDB. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.data-types.html) ## Examples The following statement demonstrates how to insert the following data types: `String`, `Number`, `Map`, `List`, `Number Set` and `String Set`. ``` INSERT INTO TypesTable value {'primarykey':'1', 'NumberType':1, 'MapType' : {'entryname1': 'value', 'entryname2': 4}, 'ListType': [1,'stringval'], 'NumberSetType':<<1,34,32,4.5>>, 'StringSetType':<<'stringval','stringval2'>> } ``` The following statement demonstrates how to insert new elements into the `Map`, `List`, `Number Set` and `String Set` types and change the value of a `Number` type. ``` UPDATE TypesTable SET NumberType=NumberType + 100 SET MapType.NewMapEntry=[2020, 'stringvalue', 2.4] SET ListType = LIST_APPEND(ListType, [4, <<'string1', 'string2'>>]) SET NumberSetType= SET_ADD(NumberSetType, <<345, 48.4>>) SET StringSetType = SET_ADD(StringSetType, <<'stringsetvalue1', 'stringsetvalue2'>>) WHERE primarykey='1' ``` The following statement demonstrates how to remove elements from the `Map`, `List`, `Number Set` and `String Set` types and change the value of a `Number` type. ``` UPDATE TypesTable SET NumberType=NumberType - 1 REMOVE ListType[1] REMOVE MapType.NewMapEntry SET NumberSetType = SET_DELETE( NumberSetType, <<345>>) SET StringSetType = SET_DELETE( StringSetType, <<'stringsetvalue1'>>) WHERE primarykey='1' ``` For more information, see [DynamoDB data types](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/HowItWorks.NamingRulesDataTypes.html#HowItWorks.DataTypes). # PartiQL statements for DynamoDB Amazon DynamoDB supports the following PartiQL statements. **Note** DynamoDB does not support all PartiQL statements. This reference provides basic syntax and usage examples of PartiQL statements that you manually run using the AWS CLI or APIs. *Data manipulation language* (DML) is the set of PartiQL statements that you use to manage data in DynamoDB tables. You use DML statements to add, modify, or delete data in a table. The following DML and query language statements are supported: + [PartiQL select statements for DynamoDB](ql-reference.select.md) + [PartiQL update statements for DynamoDB](ql-reference.update.md) + [PartiQL insert statements for DynamoDB](ql-reference.insert.md) + [PartiQL delete statements for DynamoDB](ql-reference.delete.md) [Performing transactions with PartiQL for DynamoDB](ql-reference.multiplestatements.transactions.md) and [Running batch operations with PartiQL for DynamoDB](ql-reference.multiplestatements.batching.md) are also supported by PartiQL for DynamoDB. # PartiQL select statements for DynamoDB Use the `SELECT` statement to retrieve data from a table in Amazon DynamoDB. Using the `SELECT` statement can result in a full table scan if an equality or IN condition with a partition key is not provided in the WHERE clause. A scan operation examines every item for the requested values and can use up the provisioned throughput for a large table or index in a single operation. If you want to avoid full table scan in PartiQL, you can: + Author your `SELECT` statements to not result in full table scans by making sure your [WHERE clause condition](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.select.html#ql-reference.select.parameters) is configured accordingly. + Disable full table scans using the IAM policy specified at [Example: Allow select statements and deny full table scan statements in PartiQL for DynamoDB](ql-iam.md#access-policy-ql-iam-example6), in the DynamoDB developer guide. For more information see [Best practices for querying and scanning data](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/bp-query-scan.html), in the DynamoDB developer guide. **Topics** + [ ## Syntax ](#ql-reference.select.syntax) + [ ## Parameters ](#ql-reference.select.parameters) + [ ## Examples ](#ql-reference.select.examples) ## Syntax ``` SELECT expression [, ...] FROM table[.index] [ WHERE condition ] [ [ORDER BY key [DESC|ASC] , ...] ``` ## Parameters ***expression*** (Required) A projection formed from the `*` wildcard or a projection list of one or more attribute names or document paths from the result set. An expression can consist of calls to [Use PartiQL functions with DynamoDB](ql-functions.md) or fields that are modified by [PartiQL arithmetic, comparison, and logical operators for DynamoDB](ql-operators.md). ***table*** (Required) The table name to query. ***index*** (Optional) The name of the index to query. You must add double quotation marks to the table name and index name when querying an index. ``` SELECT * FROM "TableName"."IndexName" ``` ***condition*** (Optional) The selection criteria for the query. To ensure that a `SELECT` statement does not result in a full table scan, the `WHERE` clause condition must specify a partition key. Use the equality or IN operator. For example, if you have an `Orders` table with an `OrderID` partition key and other non-key attributes, including an `Address`, the following statements would not result in a full table scan: ``` SELECT * FROM "Orders" WHERE OrderID = 100 SELECT * FROM "Orders" WHERE OrderID = 100 and Address='some address' SELECT * FROM "Orders" WHERE OrderID = 100 or OrderID = 200 SELECT * FROM "Orders" WHERE OrderID IN [100, 300, 234] ``` The following `SELECT` statements, however, will result in a full table scan: ``` SELECT * FROM "Orders" WHERE OrderID > 1 SELECT * FROM "Orders" WHERE Address='some address' SELECT * FROM "Orders" WHERE OrderID = 100 OR Address='some address' ``` ***key*** (Optional) A hash key or a sort key to use to order returned results. The default order is ascending (`ASC`) specify `DESC` if you want the results retuned in descending order. **Note** If you omit the `WHERE` clause, then all of the items in the table are retrieved. ## Examples The following query returns one item, if one exists, from the `Orders` table by specifying the partition key, `OrderID`, and using the equality operator. ``` SELECT OrderID, Total FROM "Orders" WHERE OrderID = 1 ``` The following query returns all items in the `Orders` table that have a specific partition key, `OrderID`, values using the OR operator. ``` SELECT OrderID, Total FROM "Orders" WHERE OrderID = 1 OR OrderID = 2 ``` The following query returns all items in the `Orders` table that have a specific partition key, `OrderID`, values using the IN operator. The returned results are in descending order, based on the `OrderID` key attribute value. ``` SELECT OrderID, Total FROM "Orders" WHERE OrderID IN [1, 2, 3] ORDER BY OrderID DESC ``` The following query shows a full table scan that returns all items from the `Orders` table that have a `Total` greater than 500, where `Total` is a non-key attribute. ``` SELECT OrderID, Total FROM "Orders" WHERE Total > 500 ``` The following query shows a full table scan that returns all items from the `Orders` table within a specific `Total` order range, using the IN operator and a non-key attribute `Total`. ``` SELECT OrderID, Total FROM "Orders" WHERE Total IN [500, 600] ``` The following query shows a full table scan that returns all items from the `Orders` table within a specific `Total` order range, using the BETWEEN operator and a non-key attribute `Total`. ``` SELECT OrderID, Total FROM "Orders" WHERE Total BETWEEN 500 AND 600 ``` The following query returns the first date a firestick device was used to watch by specifying the partition key `CustomerID` and sort key `MovieID` in the WHERE clause condition and using document paths in the SELECT clause. ``` SELECT Devices.FireStick.DateWatched[0] FROM WatchList WHERE CustomerID= 'C1' AND MovieID= 'M1' ``` The following query shows a full table scan that returns the list of items where a firestick device was first used after 12/24/19 using document paths in the WHERE clause condition. ``` SELECT Devices FROM WatchList WHERE Devices.FireStick.DateWatched[0] >= '12/24/19' ``` # PartiQL update statements for DynamoDB Use the `UPDATE` statement to modify the value of one or more attributes within an item in an Amazon DynamoDB table. **Note** You can only update one item at a time; you cannot issue a single DynamoDB PartiQL statement that updates multiple items. For information on updating multiple items, see [Performing transactions with PartiQL for DynamoDB](ql-reference.multiplestatements.transactions.md) or [Running batch operations with PartiQL for DynamoDB](ql-reference.multiplestatements.batching.md). **Topics** + [ ## Syntax ](#ql-reference.update.syntax) + [ ## Parameters ](#ql-reference.update.parameters) + [ ## Return value ](#ql-reference.update.return) + [ ## Examples ](#ql-reference.update.examples) ## Syntax ``` UPDATE table [SET | REMOVE] path [= data] […] WHERE condition [RETURNING returnvalues] ::= [ALL OLD | MODIFIED OLD | ALL NEW | MODIFIED NEW] * ``` ## Parameters ***table*** (Required) The table containing the data to be modified. ***path*** (Required) An attribute name or document path to be created or modified. ***data*** (Required) An attribute value or the result of an operation. The supported operations to use with SET: + LIST\$1APPEND: adds a value to a list type. + SET\$1ADD: adds a value to a number or string set. + SET\$1DELETE: removes a value from a number or string set. ***condition*** (Required) The selection criteria for the item to be modified. This condition must resolve to a single primary key value. ***returnvalues*** (Optional) Use `returnvalues` if you want to get the item attributes as they appear before or after they are updated. The valid values are: + `ALL OLD *`- Returns all of the attributes of the item, as they appeared before the update operation. + `MODIFIED OLD *`- Returns only the updated attributes, as they appeared before the update operation. + `ALL NEW *`- Returns all of the attributes of the item, as they appear after the update operation. + `MODIFIED NEW *`- Returns only the updated attributes, as they appear after the `UpdateItem` operation. ## Return value This statement does not return a value unless `returnvalues` parameter is specified. **Note** If the WHERE clause of the UPDATE statement does not evaluate to true for any item in the DynamoDB table, `ConditionalCheckFailedException` is returned. ## Examples Update an attribute value in an existing item. If the attribute does not exist, it is created. The following query updates an item in the `"Music"` table by adding an attribute of type number (`AwardsWon`) and an attribute of type map (`AwardDetail`). ``` UPDATE "Music" SET AwardsWon=1 SET AwardDetail={'Grammys':[2020, 2018]} WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks' ``` You can add `RETURNING ALL OLD *` to return the attributes as they appeared before the `Update` operation. ``` UPDATE "Music" SET AwardsWon=1 SET AwardDetail={'Grammys':[2020, 2018]} WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks' RETURNING ALL OLD * ``` This returns the following: ``` { "Items": [ { "Artist": { "S": "Acme Band" }, "SongTitle": { "S": "PartiQL Rocks" } } ] } ``` You can add `RETURNING ALL NEW *` to return the attributes as they appeared after the `Update` operation. ``` UPDATE "Music" SET AwardsWon=1 SET AwardDetail={'Grammys':[2020, 2018]} WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks' RETURNING ALL NEW * ``` This returns the following: ``` { "Items": [ { "AwardDetail": { "M": { "Grammys": { "L": [ { "N": "2020" }, { "N": "2018" } ] } } }, "AwardsWon": { "N": "1" } } ] } ``` The following query updates an item in the `"Music"` table by appending to a list `AwardDetail.Grammys`. ``` UPDATE "Music" SET AwardDetail.Grammys =list_append(AwardDetail.Grammys,[2016]) WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks' ``` The following query updates an item in the `"Music"` table by removing from a list `AwardDetail.Grammys`. ``` UPDATE "Music" REMOVE AwardDetail.Grammys[2] WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks' ``` The following query updates an item in the `"Music"` table by adding `BillBoard` to the map `AwardDetail`. ``` UPDATE "Music" SET AwardDetail.BillBoard=[2020] WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks' ``` The following query updates an item in the `"Music"` table by adding the string set attribute `BandMembers`. ``` UPDATE "Music" SET BandMembers =<<'member1', 'member2'>> WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks' ``` The following query updates an item in the `"Music"` table by adding `newbandmember` to the string set `BandMembers`. ``` UPDATE "Music" SET BandMembers =set_add(BandMembers, <<'newbandmember'>>) WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks' ``` # PartiQL delete statements for DynamoDB Use the `DELETE` statement to delete an existing item from your Amazon DynamoDB table. **Note** You can only delete one item at a time. You cannot issue a single DynamoDB PartiQL statement that deletes multiple items. For information on deleting multiple items, see [Performing transactions with PartiQL for DynamoDB](ql-reference.multiplestatements.transactions.md) or [Running batch operations with PartiQL for DynamoDB](ql-reference.multiplestatements.batching.md). **Topics** + [ ## Syntax ](#ql-reference.delete.syntax) + [ ## Parameters ](#ql-reference.delete.parameters) + [ ## Return value ](#ql-reference.delete.return) + [ ## Examples ](#ql-reference.delete.examples) ## Syntax ``` DELETE FROM table WHERE condition [RETURNING returnvalues] ::= ALL OLD * ``` ## Parameters ***table*** (Required) The DynamoDB table containing the item to be deleted. ***condition*** (Required) The selection criteria for the item to be deleted; this condition must resolve to a single primary key value. ***returnvalues*** (Optional) Use `returnvalues` if you want to get the item attributes as they appeared before they were deleted. The valid values are: + `ALL OLD *`- The content of the old item is returned. ## Return value This statement does not return a value unless `returnvalues` parameter is specified. **Note** If the DynamoDB table does not have any item with the same primary key as that of the item for which the DELETE is issued, SUCCESS is returned with 0 items deleted. If the table has an item with same primary key, but the condition in the WHERE clause of the DELETE statement evaluates to false, `ConditionalCheckFailedException` is returned. ## Examples The following query deletes an item in the `"Music"` table. ``` DELETE FROM "Music" WHERE "Artist" = 'Acme Band' AND "SongTitle" = 'PartiQL Rocks' ``` You can add the parameter `RETURNING ALL OLD *` to return the data that was deleted. ``` DELETE FROM "Music" WHERE "Artist" = 'Acme Band' AND "SongTitle" = 'PartiQL Rocks' RETURNING ALL OLD * ``` The `Delete` statement now returns the following: ``` { "Items": [ { "Artist": { "S": "Acme Band" }, "SongTitle": { "S": "PartiQL Rocks" } } ] } ``` # PartiQL insert statements for DynamoDB Use the `INSERT` statement to add an item to a table in Amazon DynamoDB. **Note** You can only insert one item at a time; you cannot issue a single DynamoDB PartiQL statement that inserts multiple items. For information on inserting multiple items, see [Performing transactions with PartiQL for DynamoDB](ql-reference.multiplestatements.transactions.md) or [Running batch operations with PartiQL for DynamoDB](ql-reference.multiplestatements.batching.md). **Topics** + [ ## Syntax ](#ql-reference.insert.syntax) + [ ## Parameters ](#ql-reference.insert.parameters) + [ ## Return value ](#ql-reference.insert.return) + [ ## Examples ](#ql-reference.insert.examples) ## Syntax Insert a single item. ``` INSERT INTO table VALUE item ``` ## Parameters ***table*** (Required) The table where you want to insert the data. The table must already exist. ***item*** (Required) A valid DynamoDB item represented as a [PartiQL tuple](https://partiql.org/docs.html). You must specify only *one* item and each attribute name in the item is case-sensitive and can be denoted with *single* quotation marks (`'...'`) in PartiQL. String values are also denoted with *single* quotation marks (`'...'`) in PartiQL. ## Return value This statement does not return any values. **Note** If the DynamoDB table already has an item with the same primary key as the primary key of the item being inserted, `DuplicateItemException` is returned. ## Examples ``` INSERT INTO "Music" value {'Artist' : 'Acme Band','SongTitle' : 'PartiQL Rocks'} ``` # Use PartiQL functions with DynamoDB PartiQL in Amazon DynamoDB supports the following built-in variants of SQL standard functions. **Note** Any SQL functions that are not included in this list are not currently supported in DynamoDB. ## Aggregate functions + [Using the SIZE function with PartiQL for amazon DynamoDB](ql-functions.size.md) ## Conditional functions + [Using the EXISTS function with PartiQL for DynamoDB](ql-functions.exists.md) + [Using the ATTRIBUTE\$1TYPE function with PartiQL for DynamoDB](ql-functions.attribute_type.md) + [Using the BEGINS\$1WITH function with PartiQL for DynamoDB](ql-functions.beginswith.md) + [Using the CONTAINS function with PartiQL for DynamoDB](ql-functions.contains.md) + [Using the MISSING function with PartiQL for DynamoDB](ql-functions.missing.md) # Using the EXISTS function with PartiQL for DynamoDB You can use EXISTS to perform the same function as `ConditionCheck` does in the [TransactWriteItems](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/transaction-apis.html#transaction-apis-txwriteitems) API. The EXISTS function can only be used in transactions. Given a value, returns `TRUE` if the value is a non-empty collection. Otherwise, returns `FALSE`. **Note** This function can only be used in transactional operations. ## Syntax ``` EXISTS ( statement ) ``` ## Arguments *statement* (Required) The SELECT statement that the function evaluates. The SELECT statement must specify a full primary key and one other condition. ## Return type `bool` ## Examples ``` EXISTS( SELECT * FROM "Music" WHERE "Artist" = 'Acme Band' AND "SongTitle" = 'PartiQL Rocks') ``` # Using the BEGINS\$1WITH function with PartiQL for DynamoDB Returns `TRUE` if the attribute specified begins with a particular substring. ## Syntax ``` begins_with(path, value ) ``` ## Arguments *path* (Required) The attribute name or document path to use. *value* (Required) The string to search for. ## Return type `bool` ## Examples ``` SELECT * FROM "Orders" WHERE "OrderID"=1 AND begins_with("Address", '7834 24th') ``` # Using the MISSING function with PartiQL for DynamoDB Returns `TRUE` if the item does not contain the attribute specified. Only equality and inequality operators can be used with this function. ## Syntax ``` attributename IS | IS NOT MISSING ``` ## Arguments *attributename* (Required) The attribute name to look for. ## Return type `bool` ## Examples ``` SELECT * FROM Music WHERE "Awards" is MISSING ``` # Using the ATTRIBUTE\$1TYPE function with PartiQL for DynamoDB Returns `TRUE` if the attribute at the specified path is of a particular data type. ## Syntax ``` attribute_type( attributename, type ) ``` ## Arguments *attributename* (Required) The attribute name to use. *type* (Required) The attribute type to check for. For a list of valid values, see DynamoDB [attribute\$1type](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Expressions.OperatorsAndFunctions.html#Expressions.OperatorsAndFunctions.Functions). ## Return type `bool` ## Examples ``` SELECT * FROM "Music" WHERE attribute_type("Artist", 'S') ``` # Using the CONTAINS function with PartiQL for DynamoDB Returns `TRUE` if the attribute specified by the path is one of the following: + A String that contains a particular substring. + A Set that contains a particular element within the set. For more information, see the DynamoDB [contains](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Expressions.OperatorsAndFunctions.html#Expressions.OperatorsAndFunctions.Functions) function. ## Syntax ``` contains( path, substring ) ``` ## Arguments *path* (Required) The attribute name or document path to use. *substring* (Required) The attribute substring or set member to check for. For more information, see the DynamoDB [contains](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Expressions.OperatorsAndFunctions.html#Expressions.OperatorsAndFunctions.Functions) function. ## Return type `bool` ## Examples ``` SELECT * FROM "Orders" WHERE "OrderID"=1 AND contains("Address", 'Kirkland') ``` # Using the SIZE function with PartiQL for amazon DynamoDB Returns a number representing an attribute's size in bytes. The following are valid data types for use with size. For more information, see the DynamoDB [size](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Expressions.OperatorsAndFunctions.html#Expressions.OperatorsAndFunctions.Functions) function. ## Syntax ``` size( path) ``` ## Arguments *path* (Required) The attribute name or document path. For supported types, see DynamoDB [size](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Expressions.OperatorsAndFunctions.html#Expressions.OperatorsAndFunctions.Functions) function. ## Return type `int` ## Examples ``` SELECT * FROM "Orders" WHERE "OrderID"=1 AND size("Image") >300 ``` # PartiQL arithmetic, comparison, and logical operators for DynamoDB PartiQL in Amazon DynamoDB supports the following [SQL standard operators](https://www.w3schools.com/sql/sql_operators.asp). **Note** Any SQL operators that are not included in this list are not currently supported in DynamoDB. ## Arithmetic operators **** | Operator | Description | | --- | --- | | \$1 | Add | | - | Subtract | ## Comparison operators **** | Operator | Description | | --- | --- | | = | Equal to | | <> | Not Equal to | | \$1= | Not Equal to | | > | Greater than | | < | Less than | | >= | Greater than or equal to | | <= | Less than or equal to | ## Logical operators **** | Operator | Description | | --- | --- | | AND | TRUE if all the conditions separated by AND are TRUE | | BETWEEN | `TRUE` if the operand is within the range of comparisons. This operator is inclusive of the lower and upper bound of the operands on which you apply it. | | IN | `TRUE` if the operand is equal to one of a list of expressions (at max 50 hash attribute values or at max 100 non-key attribute values). Results are returned in pages of up to 10 items. If the `IN` list contains more values, you must use the `NextToken` returned in the response to retrieve subsequent pages. | | IS | TRUE if the operand is a given, PartiQL data type, including NULL or MISSING | | NOT | Reverses the value of a given Boolean expression | | OR | TRUE if any of the conditions separated by OR are TRUE | For more information about using logical operators, see [Making comparisons](Expressions.OperatorsAndFunctions.md#Expressions.OperatorsAndFunctions.Comparators) and [Logical evaluations](Expressions.OperatorsAndFunctions.md#Expressions.OperatorsAndFunctions.LogicalEvaluations). # Performing transactions with PartiQL for DynamoDB This section describes how to use transactions with PartiQL for DynamoDB. PartiQL transactions are limited to 100 total statements (actions). For more information on DynamoDB transactions, see [Managing complex workflows with DynamoDB transactions](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/transactions.html). **Note** The entire transaction must consist of either read statements or write statements. You can't mix both in one transaction. The EXISTS function is an exception. You can use it to check the condition of specific attributes of the item in a similar manner to `ConditionCheck` in the [TransactWriteItems](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/transaction-apis.html#transaction-apis-txwriteitems) API operation. **Topics** + [ ## Syntax ](#ql-reference.multiplestatements.transactions.syntax) + [ ## Parameters ](#ql-reference.multiplestatements.transactions.parameters) + [ ## Return values ](#ql-reference.multiplestatements.transactions.return) + [ ## Examples ](#ql-reference.multiplestatements.transactions.examples) ## Syntax ``` [ { "Statement":" statement ", "Parameters":[ { " parametertype " : " parametervalue " }, ...] } , ... ] ``` ## Parameters ***statement*** (Required) A PartiQL for DynamoDB supported statement. The entire transaction must consist of either read statements or write statements. You can't mix both in one transaction. ***parametertype*** (Optional) A DynamoDB type, if parameters were used when specifying the PartiQL statement. ***parametervalue*** (Optional) A parameter value if parameters were used when specifying the PartiQL statement. ## Return values This statement doesn't return any values for Write operations (INSERT, UPDATE, or DELETE). However, it returns different values for Read operations (SELECT) based on the conditions specified in the WHERE clause. **Note** If any of the singleton INSERT, UPDATE, or DELETE operations return an error, the transactions are canceled with the `TransactionCanceledException` exception, and the cancellation reason code includes the errors from the individual singleton operations. ## Examples The following example runs multiple statements as a transaction. ------ #### [ AWS CLI ] 1. Save the following JSON code to a file called partiql.json. ``` [ { "Statement": "EXISTS(SELECT * FROM \"Music\" where Artist='No One You Know' and SongTitle='Call Me Today' and Awards is MISSING)" }, { "Statement": "INSERT INTO Music value {'Artist':?,'SongTitle':'?'}", "Parameters": [{\"S\": \"Acme Band\"}, {\"S\": \"Best Song\"}] }, { "Statement": "UPDATE \"Music\" SET AwardsWon=1 SET AwardDetail={'Grammys':[2020, 2018]} where Artist='Acme Band' and SongTitle='PartiQL Rocks'" } ] ``` 1. Run the following command in a command prompt. ``` aws dynamodb execute-transaction --transact-statements file://partiql.json ``` ------ #### [ Java ] ``` public class DynamoDBPartiqlTransaction { public static void main(String[] args) { // Create the DynamoDB Client with the region you want AmazonDynamoDB dynamoDB = createDynamoDbClient("us-west-2"); try { // Create ExecuteTransactionRequest ExecuteTransactionRequest executeTransactionRequest = createExecuteTransactionRequest(); ExecuteTransactionResult executeTransactionResult = dynamoDB.executeTransaction(executeTransactionRequest); System.out.println("ExecuteTransaction successful."); // Handle executeTransactionResult } catch (Exception e) { handleExecuteTransactionErrors(e); } } private static AmazonDynamoDB createDynamoDbClient(String region) { return AmazonDynamoDBClientBuilder.standard().withRegion(region).build(); } private static ExecuteTransactionRequest createExecuteTransactionRequest() { ExecuteTransactionRequest request = new ExecuteTransactionRequest(); // Create statements List statements = getPartiQLTransactionStatements(); request.setTransactStatements(statements); return request; } private static List getPartiQLTransactionStatements() { List statements = new ArrayList(); statements.add(new ParameterizedStatement() .withStatement("EXISTS(SELECT * FROM "Music" where Artist='No One You Know' and SongTitle='Call Me Today' and Awards is MISSING)")); statements.add(new ParameterizedStatement() .withStatement("INSERT INTO "Music" value {'Artist':'?','SongTitle':'?'}") .withParameters(new AttributeValue("Acme Band"),new AttributeValue("Best Song"))); statements.add(new ParameterizedStatement() .withStatement("UPDATE "Music" SET AwardsWon=1 SET AwardDetail={'Grammys':[2020, 2018]} where Artist='Acme Band' and SongTitle='PartiQL Rocks'")); return statements; } // Handles errors during ExecuteTransaction execution. Use recommendations in error messages below to add error handling specific to // your application use-case. private static void handleExecuteTransactionErrors(Exception exception) { try { throw exception; } catch (TransactionCanceledException tce) { System.out.println("Transaction Cancelled, implies a client issue, fix before retrying. Error: " + tce.getErrorMessage()); } catch (TransactionInProgressException tipe) { System.out.println("The transaction with the given request token is already in progress, consider changing " + "retry strategy for this type of error. Error: " + tipe.getErrorMessage()); } catch (IdempotentParameterMismatchException ipme) { System.out.println("Request rejected because it was retried with a different payload but with a request token that was already used, " + "change request token for this payload to be accepted. Error: " + ipme.getErrorMessage()); } catch (Exception e) { handleCommonErrors(e); } } private static void handleCommonErrors(Exception exception) { try { throw exception; } catch (InternalServerErrorException isee) { System.out.println("Internal Server Error, generally safe to retry with exponential back-off. Error: " + isee.getErrorMessage()); } catch (RequestLimitExceededException rlee) { System.out.println("Throughput exceeds the current throughput limit for your account, increase account level throughput before " + "retrying. Error: " + rlee.getErrorMessage()); } catch (ProvisionedThroughputExceededException ptee) { System.out.println("Request rate is too high. If you're using a custom retry strategy make sure to retry with exponential back-off. " + "Otherwise consider reducing frequency of requests or increasing provisioned capacity for your table or secondary index. Error: " + ptee.getErrorMessage()); } catch (ResourceNotFoundException rnfe) { System.out.println("One of the tables was not found, verify table exists before retrying. Error: " + rnfe.getErrorMessage()); } catch (AmazonServiceException ase) { System.out.println("An AmazonServiceException occurred, indicates that the request was correctly transmitted to the DynamoDB " + "service, but for some reason, the service was not able to process it, and returned an error response instead. Investigate and " + "configure retry strategy. Error type: " + ase.getErrorType() + ". Error message: " + ase.getErrorMessage()); } catch (AmazonClientException ace) { System.out.println("An AmazonClientException occurred, indicates that the client was unable to get a response from DynamoDB " + "service, or the client was unable to parse the response from the service. Investigate and configure retry strategy. "+ "Error: " + ace.getMessage()); } catch (Exception e) { System.out.println("An exception occurred, investigate and configure retry strategy. Error: " + e.getMessage()); } } } ``` ------ The following example shows the different return values when DynamoDB reads items with different conditions specified in the WHERE clause. ------ #### [ AWS CLI ] 1. Save the following JSON code to a file called partiql.json. ``` [ // Item exists and projected attribute exists { "Statement": "SELECT * FROM "Music" WHERE Artist='No One You Know' and SongTitle='Call Me Today'" }, // Item exists but projected attributes do not exist { "Statement": "SELECT non_existent_projected_attribute FROM "Music" WHERE Artist='No One You Know' and SongTitle='Call Me Today'" }, // Item does not exist { "Statement": "SELECT * FROM "Music" WHERE Artist='No One I Know' and SongTitle='Call You Today'" } ] ``` 1. following command in a command prompt. ``` aws dynamodb execute-transaction --transact-statements file://partiql.json ``` 1. The following response is returned: ``` { "Responses": [ // Item exists and projected attribute exists { "Item": { "Artist":{ "S": "No One You Know" }, "SongTitle":{ "S": "Call Me Today" } } }, // Item exists but projected attributes do not exist { "Item": {} }, // Item does not exist {} ] } ``` ------ # Running batch operations with PartiQL for DynamoDB This section describes how to use batch statements with PartiQL for DynamoDB. **Note** The entire batch must consist of either read statements or write statements; you cannot mix both in one batch. `BatchExecuteStatement` and `BatchWriteItem` can perform no more than 25 statements per batch. `BatchExecuteStatement` makes use of `BatchGetItem` which takes a list of primary keys in separate statements. **Topics** + [ ## Syntax ](#ql-reference.multiplestatements.batching.syntax) + [ ## Parameters ](#ql-reference.multiplestatements.batching.parameters) + [ ## Examples ](#ql-reference.multiplestatements.batching.examples) ## Syntax ``` [ { "Statement": "SELECT pk FROM ProblemSet WHERE pk = 'p#9StkWHYTxm7x2AqSXcrfu7' AND sk = 'info'" }, { "Statement": "SELECT pk FROM ProblemSet WHERE pk = 'p#isC2ChceGbxHgESc4szoTE' AND sk = 'info'" } ] ``` ``` [ { "Statement":" statement ", "Parameters":[ { " parametertype " : " parametervalue " }, ...] } , ... ] ``` ## Parameters ***statement*** (Required) A PartiQL for DynamoDB supported statement. + The entire batch must consist of either read statements or write statements; you cannot mix both in one batch. + `BatchExecuteStatement` and `BatchWriteItem` can perform no more than 25 statements per batch. ***parametertype*** (Optional) A DynamoDB type, if parameters were used when specifying the PartiQL statement. ***parametervalue*** (Optional) A parameter value if parameters were used when specifying the PartiQL statement. ## Examples ------ #### [ AWS CLI ] 1. Save the following json to a file called partiql.json ``` [ { "Statement": "INSERT INTO Music VALUE {'Artist':?,'SongTitle':?}", "Parameters": [{"S": "Acme Band"}, {"S": "Best Song"}] }, { "Statement": "UPDATE Music SET AwardsWon=1, AwardDetail={'Grammys':[2020, 2018]} WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'" } ] ``` 1. Run the following command in a command prompt. ``` aws dynamodb batch-execute-statement --statements file://partiql.json ``` ------ #### [ Java ] ``` public class DynamoDBPartiqlBatch { public static void main(String[] args) { // Create the DynamoDB Client with the region you want AmazonDynamoDB dynamoDB = createDynamoDbClient("us-west-2"); try { // Create BatchExecuteStatementRequest BatchExecuteStatementRequest batchExecuteStatementRequest = createBatchExecuteStatementRequest(); BatchExecuteStatementResult batchExecuteStatementResult = dynamoDB.batchExecuteStatement(batchExecuteStatementRequest); System.out.println("BatchExecuteStatement successful."); // Handle batchExecuteStatementResult } catch (Exception e) { handleBatchExecuteStatementErrors(e); } } private static AmazonDynamoDB createDynamoDbClient(String region) { return AmazonDynamoDBClientBuilder.standard().withRegion(region).build(); } private static BatchExecuteStatementRequest createBatchExecuteStatementRequest() { BatchExecuteStatementRequest request = new BatchExecuteStatementRequest(); // Create statements List statements = getPartiQLBatchStatements(); request.setStatements(statements); return request; } private static List getPartiQLBatchStatements() { List statements = new ArrayList(); statements.add(new BatchStatementRequest() .withStatement("INSERT INTO Music value {'Artist':'Acme Band','SongTitle':'PartiQL Rocks'}")); statements.add(new BatchStatementRequest() .withStatement("UPDATE Music set AwardDetail.BillBoard=[2020] where Artist='Acme Band' and SongTitle='PartiQL Rocks'")); return statements; } // Handles errors during BatchExecuteStatement execution. Use recommendations in error messages below to add error handling specific to // your application use-case. private static void handleBatchExecuteStatementErrors(Exception exception) { try { throw exception; } catch (Exception e) { // There are no API specific errors to handle for BatchExecuteStatement, common DynamoDB API errors are handled below handleCommonErrors(e); } } private static void handleCommonErrors(Exception exception) { try { throw exception; } catch (InternalServerErrorException isee) { System.out.println("Internal Server Error, generally safe to retry with exponential back-off. Error: " + isee.getErrorMessage()); } catch (RequestLimitExceededException rlee) { System.out.println("Throughput exceeds the current throughput limit for your account, increase account level throughput before " + "retrying. Error: " + rlee.getErrorMessage()); } catch (ProvisionedThroughputExceededException ptee) { System.out.println("Request rate is too high. If you're using a custom retry strategy make sure to retry with exponential back-off. " + "Otherwise consider reducing frequency of requests or increasing provisioned capacity for your table or secondary index. Error: " + ptee.getErrorMessage()); } catch (ResourceNotFoundException rnfe) { System.out.println("One of the tables was not found, verify table exists before retrying. Error: " + rnfe.getErrorMessage()); } catch (AmazonServiceException ase) { System.out.println("An AmazonServiceException occurred, indicates that the request was correctly transmitted to the DynamoDB " + "service, but for some reason, the service was not able to process it, and returned an error response instead. Investigate and " + "configure retry strategy. Error type: " + ase.getErrorType() + ". Error message: " + ase.getErrorMessage()); } catch (AmazonClientException ace) { System.out.println("An AmazonClientException occurred, indicates that the client was unable to get a response from DynamoDB " + "service, or the client was unable to parse the response from the service. Investigate and configure retry strategy. "+ "Error: " + ace.getMessage()); } catch (Exception e) { System.out.println("An exception occurred, investigate and configure retry strategy. Error: " + e.getMessage()); } } } ``` ------ # IAM security policies with PartiQL for DynamoDB The following permissions are required: + To read items using PartiQL for DynamoDB, you must have `dynamodb:PartiQLSelect` permission on the table or index. + To insert items using PartiQL for DynamoDB, you must have `dynamodb:PartiQLInsert` permission on the table or index. + To update items using PartiQL for DynamoDB, you must have `dynamodb:PartiQLUpdate` permission on the table or index. + To delete items using PartiQL for DynamoDB, you must have `dynamodb:PartiQLDelete` permission on the table or index. ## Example: Allow all PartiQL for DynamoDB statements (Select/Insert/Update/Delete) on a table The following IAM policy grants permissions to run all PartiQL for DynamoDB statements on a table. ------ #### [ JSON ] **** ``` { "Version":"2012-10-17", "Statement":[ { "Effect":"Allow", "Action":[ "dynamodb:PartiQLInsert", "dynamodb:PartiQLUpdate", "dynamodb:PartiQLDelete", "dynamodb:PartiQLSelect" ], "Resource":[ "arn:aws:dynamodb:us-west-2:123456789012:table/Music" ] } ] } ``` ------ ## Example: Allow PartiQL for DynamoDB select statements on a table The following IAM policy grants permissions to run the `select` statement on a specific table. ------ #### [ JSON ] **** ``` { "Version":"2012-10-17", "Statement":[ { "Effect":"Allow", "Action":[ "dynamodb:PartiQLSelect" ], "Resource":[ "arn:aws:dynamodb:us-west-2:123456789012:table/Music" ] } ] } ``` ------ ## Example: Allow PartiQL for DynamoDB insert statements on an index The following IAM policy grants permissions to run the `insert` statement on a specific index. ------ #### [ JSON ] **** ``` { "Version":"2012-10-17", "Statement":[ { "Effect":"Allow", "Action":[ "dynamodb:PartiQLInsert" ], "Resource":[ "arn:aws:dynamodb:us-west-2:123456789012:table/Music/index/index1" ] } ] } ``` ------ ## Example: Allow PartiQL for DynamoDB transactional statements only on a table The following IAM policy grants permissions to run only transactional statements on a specific table. ------ #### [ JSON ] **** ``` { "Version":"2012-10-17", "Statement":[ { "Effect":"Allow", "Action":[ "dynamodb:PartiQLInsert", "dynamodb:PartiQLUpdate", "dynamodb:PartiQLDelete", "dynamodb:PartiQLSelect" ], "Resource":[ "arn:aws:dynamodb:us-west-2:123456789012:table/Music" ], "Condition":{ "StringEquals":{ "dynamodb:EnclosingOperation":[ "ExecuteTransaction" ] } } } ] } ``` ------ ## Example: Allow PartiQL for DynamoDB non-transactional reads and writes and block PartiQL transactional reads and writes transactional statements on a table. The following IAM policy grants permissions to run PartiQL for DynamoDB non-transactional reads and writes while blocking PartiQL for DynamoDB transactional reads and writes. ------ #### [ JSON ] **** ``` { "Version":"2012-10-17", "Statement":[ { "Effect":"Deny", "Action":[ "dynamodb:PartiQLInsert", "dynamodb:PartiQLUpdate", "dynamodb:PartiQLDelete", "dynamodb:PartiQLSelect" ], "Resource":[ "arn:aws:dynamodb:us-west-2:123456789012:table/Music" ], "Condition":{ "StringEquals":{ "dynamodb:EnclosingOperation":[ "ExecuteTransaction" ] } } }, { "Effect":"Allow", "Action":[ "dynamodb:PartiQLInsert", "dynamodb:PartiQLUpdate", "dynamodb:PartiQLDelete", "dynamodb:PartiQLSelect" ], "Resource":[ "arn:aws:dynamodb:us-west-2:123456789012:table/Music" ] } ] } ``` ------ ## Example: Allow select statements and deny full table scan statements in PartiQL for DynamoDB The following IAM policy grants permissions to run the `select` statement on a specific table while blocking `select` statements that result in a full table scan. ------ #### [ JSON ] **** ``` { "Version":"2012-10-17", "Statement":[ { "Effect":"Deny", "Action":[ "dynamodb:PartiQLSelect" ], "Resource":[ "arn:aws:dynamodb:us-west-2:123456789012:table/WatchList" ], "Condition":{ "Bool":{ "dynamodb:FullTableScan":[ "true" ] } } }, { "Effect":"Allow", "Action":[ "dynamodb:PartiQLSelect" ], "Resource":[ "arn:aws:dynamodb:us-west-2:123456789012:table/WatchList" ] } ] } ``` ------