本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。 # DynamoDB 專用 PartiQL 入門 本節說明如何從 Amazon DynamoDB 主控台、 AWS Command Line Interface (AWS CLI) 和 DynamoDB API 使用 DynamoDB 專用 PartiQL。 在下列範例中，[DynamoDB 入門](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/GettingStartedDynamoDB.html)教學課程中所定義的 DynamoDB 資料表是先決條件。 如需有關使用 DynamoDB 主控台 AWS Command Line Interface或 DynamoDB APIs 存取 DynamoDB 的資訊，請參閱[存取 DynamoDB](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/AccessingDynamoDB.html)。 若要[下載](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/workbench.settingup.html)並使用 [NoSQL Workbench](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/workbench.html) 來建置 [DynamoDB 專用 PartiQL](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.html) 陳述式，請選擇位於 NoSQL Workbench for DynamoDB [Operation Builder](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/workbench.querybuilder.operationbuilder.html) 右上角的 **PartiQL operations** (PartiQL 操作)。 ------ #### [ Console ] ![\[PartiQL 編輯器介面，顯示在 Music 資料表上執行查詢操作的結果。\]](http://docs.aws.amazon.com/zh_tw/amazondynamodb/latest/developerguide/images/partiqlgettingstarted.png) 1. 登入 AWS 管理主控台 ，並在 https：//[https://console.aws.amazon.com/dynamodb/](https://console.aws.amazon.com/dynamodb/) 開啟 DynamoDB 主控台。 1. 在主控台左側的導覽窗格中，選擇 **PartiQL editor** (PartiQL 編輯器)。 1. 選擇 **Music** (音樂) 資料表。 1. 選擇 **Query table** (查詢資料表)。此動作會產生不會導致完整資料表掃描的查詢。 1. 使用字串值 `Acme Band` 取代 `partitionKeyValue`。使用字串值 `Happy Day` 取代 `sortKeyValue`。 1. 選擇 **Run** (執行) 按鈕。 1. 您可以選擇 **Table view** (資料表檢視) 或 **JSON view** (JSON 檢視) 按鈕來檢視查詢的結果。 ------ #### [ NoSQL workbench ] ![\[NoSQL Workbench 介面。它會顯示 PartiQL SELECT 陳述式，您可以在 Music 資料表上執行。\]](http://docs.aws.amazon.com/zh_tw/amazondynamodb/latest/developerguide/images/workbench/partiql.single.png) 1. 選擇 **PartiQL statement** (PartiQL 陳述式)。 1. 輸入下列 PartiQL [SELECT 陳述式](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.select.html) ``` SELECT * FROM Music WHERE Artist=? and SongTitle=? ``` 1. 若要指定 `Artist` 和 `SongTitle` 參數的值： 1. 選擇 **Optional request parameters** (選用的請求參數)。 1. 選擇 **Add new parameters** (新增新參數)。 1. 選擇屬性類型 **string** 和數值 `Acme Band`。 1. 重複步驟 b 和 c，然後選擇類型 **string** 和數值 `PartiQL Rocks`。 1. 若要產生程式碼，請選擇 **Generate code** (產生程式碼)。 從顯示的標籤中選取所需的語言。您現在可以複製此程式碼，並使用在您的應用程式中。 1. 若希望立即執行此操作，請選擇 **Run** (執行)。 ------ #### [ AWS CLI ] 1. 使用 INSERT PartiQL 陳述式在 `Music` 資料表中建立項目。 ``` aws dynamodb execute-statement --statement "INSERT INTO Music \ VALUE \ {'Artist':'Acme Band','SongTitle':'PartiQL Rocks'}" ``` 1. 使用 SELECT PartiQL 陳述式從 Music 資料表中檢索項目。 ``` aws dynamodb execute-statement --statement "SELECT * FROM Music \ WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'" ``` 1. 使用 UPDATE PartiQL 陳述式在 `Music` 資料表中更新項目。 ``` aws dynamodb execute-statement --statement "UPDATE Music \ SET AwardsWon=1 \ SET AwardDetail={'Grammys':[2020, 2018]} \ WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'" ``` 在 `Music` 資料表中新增項目的清單值。 ``` aws dynamodb execute-statement --statement "UPDATE Music \ SET AwardDetail.Grammys =list_append(AwardDetail.Grammys,[2016]) \ WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'" ``` 在 `Music` 資料表中移除項目的清單值。 ``` aws dynamodb execute-statement --statement "UPDATE Music \ REMOVE AwardDetail.Grammys[2] \ WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'" ``` 在 `Music` 資料表中新增項目的新映射成員。 ``` aws dynamodb execute-statement --statement "UPDATE Music \ SET AwardDetail.BillBoard=[2020] \ WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'" ``` 在 `Music` 資料表中新增項目的新字串集屬性。 ``` aws dynamodb execute-statement --statement "UPDATE Music \ SET BandMembers =<<'member1', 'member2'>> \ WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'" ``` 在 `Music` 資料表中更新項目的字串集屬性。 ``` aws dynamodb execute-statement --statement "UPDATE Music \ SET BandMembers =set_add(BandMembers, <<'newmember'>>) \ WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'" ``` 1. 使用刪除 PartiQL 陳述式從 `Music` 資料表中刪除項目。 ``` 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()); } } } ``` ------ ## 使用參數化陳述式 您可以使用問號 (`?`) 預留位置，並在 `Parameters` 欄位中分別提供值，而不是直接在 PartiQL 陳述式字串中內嵌值。每個 `?` 都會依提供的順序，以對應的參數值取代。 使用參數化陳述式是最佳實務，因為它會將陳述式結構與資料值分開，讓陳述式更容易閱讀和重複使用。它也不需要在陳述式字串中手動格式化和逸出屬性值。 `ExecuteStatement`、 `BatchExecuteStatement`和 `ExecuteTransaction`操作支援參數化陳述式。 下列範例使用分割區索引鍵和排序索引鍵的參數化值，從`Music`資料表中擷取項目。 ------ #### [ 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'} ] ) ``` ------ **注意** 上一節中的 Java 範例使用參數化陳述式。`getPartiQLParameters()` 方法會建置參數清單，而每個陳述式會使用`?`預留位置而非內嵌值。