DynamoDB 專用 PartiQL 入門

本節說明如何從 Amazon DynamoDB 主控台、 AWS Command Line Interface (AWS CLI) 和 DynamoDB API 使用 DynamoDB 專用 PartiQL。

在下列範例中，DynamoDB 入門教學課程中所定義的 DynamoDB 資料表是先決條件。

如需使用 DynamoDB 主控台或 DynamoDB API 存取 DynamoDB 的相關資訊 AWS Command Line Interface，請參閱存取 DynamoDB。

若要下載並使用 NoSQL Workbench 來建置 DynamoDB 專用 PartiQL 陳述式，請選擇位於 NoSQL Workbench for DynamoDB Operation Builder 右上角的 PartiQL operations (PartiQL 操作)。

Console

1. 登入 AWS Management Console 並開啟 DynamoDB 支援主控台，網址為 https://console.aws.amazon.com/dynamodb/。

2. 在主控台左側的導覽窗格中，選擇 PartiQL editor (PartiQL 編輯器)。

3. 選擇 Music (音樂) 資料表。

4. 選擇 Query table (查詢資料表)。此動作會產生不會導致完整資料表掃描的查詢。

5. 使用字串值 Acme Band 取代 partitionKeyValue。使用字串值 Happy Day 取代 sortKeyValue。

6. 選擇 Run (執行) 按鈕。

7. 您可以選擇 Table view (資料表檢視) 或 JSON view (JSON 檢視) 按鈕來檢視查詢的結果。

NoSQL workbench

1. 選擇 PartiQL statement (PartiQL 陳述式)。

2. 輸入下列 PartiQL SELECT 陳述式

   SELECT *                                         
   FROM Music  
   WHERE Artist=? and SongTitle=?

3. 若要指定 Artist 和 SongTitle 參數的值：

   1. 選擇 Optional request parameters (選用的請求參數)。

   2. 選擇 Add new parameters (新增新參數)。

   3. 選擇屬性類型 string 和數值 Acme Band。

   4. 重複步驟 b 和 c，然後選擇類型 string 和數值 PartiQL Rocks。

4. 若要產生程式碼，請選擇 Generate code (產生程式碼)。

   從顯示的標籤中選取所需的語言。您現在可以複製此程式碼，並使用在您的應用程式中。

5. 若希望立即執行此操作，請選擇 Run (執行)。

AWS CLI

1. 使用 INSERT PartiQL 陳述式在 Music 資料表中建立項目。

   aws dynamodb execute-statement --statement "INSERT INTO Music  \
   					    VALUE  \
   					    {'Artist':'Acme Band','SongTitle':'PartiQL Rocks'}"

2. 使用 SELECT PartiQL 陳述式從 Music 資料表中檢索項目。

   aws dynamodb execute-statement --statement "SELECT * FROM Music   \
                                               WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'"

3. 使用 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'"

4. 使用刪除 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 com.amazonaws.services.dynamodbv2.AmazonDynamoDB;
import com.amazonaws.services.dynamodbv2.AmazonDynamoDBClientBuilder;
import com.amazonaws.services.dynamodbv2.model.AttributeValue;
import com.amazonaws.services.dynamodbv2.model.ConditionalCheckFailedException;
import com.amazonaws.services.dynamodbv2.model.ExecuteStatementRequest;
import com.amazonaws.services.dynamodbv2.model.ExecuteStatementResult;
import com.amazonaws.services.dynamodbv2.model.InternalServerErrorException;
import com.amazonaws.services.dynamodbv2.model.ItemCollectionSizeLimitExceededException;
import com.amazonaws.services.dynamodbv2.model.ProvisionedThroughputExceededException;
import com.amazonaws.services.dynamodbv2.model.RequestLimitExceededException;
import com.amazonaws.services.dynamodbv2.model.ResourceNotFoundException;
import com.amazonaws.services.dynamodbv2.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<AttributeValue> 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<AttributeValue> getPartiQLParameters() {
        List<AttributeValue> parameters = new ArrayList<AttributeValue>();
        parameters.add(new AttributeValue("Acme Band"));
        parameters.add(new AttributeValue("PartiQL Rocks"));
        return parameters;
    }

    private static ExecuteStatementResult executeStatementRequest(AmazonDynamoDB client, String statement, List<AttributeValue> 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());
        }
    }

}