Nozioni di base su PartiQL per DynamoDB

In questa sezione viene descritto come utilizzare PartiQL per DynamoDB dalla console Amazon DynamoDB, da AWS Command Line Interface (AWS CLI) e dalle API DynamoDB.

Nei seguenti esempi, la tabella DynamoDB definita nel tutorial Nozioni di base su DynamoDB è un prerequisito.

Per informazioni sull'utilizzo della console DynamoDB o delle API DynamoDB per accedere a DynamoDB AWS Command Line Interface, vedere Accesso a DynamoDB.

Per scaricare e utilizzare NoSQL Workbench per creare istruzioni PartiQL per DynamoDB, seleziona Operazioni PartiQL nell'angolo in alto a destra dell'Operation builder di NoSQL Workbench per DynamoDB.

Console

1. Accedi AWS Management Console e apri la console DynamoDB all'indirizzo https://console.aws.amazon.com/dynamodb/.

2. Nel pannello di navigazione sul lato sinistro della console seleziona Editor PartiQL.

3. Seleziona la tabella Music.

4. Scegli Esegui query sulla tabella. Questa azione genera una query che non determinerà una scansione completa della tabella.

5. Sostituisci partitionKeyValue con il valore stringa Acme Band. Sostituisci sortKeyValue con il valore stringa Happy Day.

6. Scegli il pulsante Esegui.

7. È possibile visualizzare i risultati della query scegliendo i pulsanti Vista tabella o Vista JSON.

NoSQL workbench

1. Scegli Istruzione PartiQL.

2. Specifica la seguente istruzione SELECT (SELEZIONA) PartiQL.

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

3. Per specificare un valore per i parametri Artist e SongTitle:

   1. Scegli Parametri di richiesta facoltativi.

   2. Scegli Aggiungi nuovi parametri.

   3. Scegli il tipo di attributo string e il valore Acme Band.

   4. Ripeti i passaggi b e c, quindi scegli il tipo string e il valore PartiQL Rocks.

4. Per generare codice, selezionare Generate code (Genera codice).

   Selezionare la lingua desiderata dalle schede visualizzate. È possibile copiare questo codice e utilizzarlo nell'applicazione.

5. Per eseguire l'operazione immediatamente, seleziona Esegui.

AWS CLI

1. Crea un elemento nella tabella Music utilizzando l'istruzione INSERT (INSERISCI) PartiQL.

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

2. Recupera un elemento dalla tabella Music utilizzando l'istruzione SELECT (SELEZIONA) PartiQL.

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

3. Aggiorna un elemento nella tabella Music utilizzando l'istruzione UPDATE (AGGIORNA) PartiQL.

   aws dynamodb execute-statement --statement "UPDATE Music  \
                                               SET AwardsWon=1  \
                                               SET AwardDetail={'Grammys':[2020, 2018]}  \
                                               WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'"

   Aggiungi un valore di elenco per un elemento nella tabella Music.

   aws dynamodb execute-statement --statement "UPDATE Music  \
                                               SET AwardDetail.Grammys =list_append(AwardDetail.Grammys,[2016])  \
                                               WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'"

   Rimuovi un valore di elenco per un elemento nella tabella Music.

   aws dynamodb execute-statement --statement "UPDATE Music  \
                                               REMOVE AwardDetail.Grammys[2]  \
                                               WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'"

   Aggiungi un nuovo membro della mappa per un elemento nella tabella Music.

   aws dynamodb execute-statement --statement "UPDATE Music  \
                                               SET AwardDetail.BillBoard=[2020]  \
                                               WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'"

   Aggiungi un nuovo attributo del set di stringhe per un elemento nella tabella Music.

   aws dynamodb execute-statement --statement "UPDATE Music  \
                                               SET BandMembers =<<'member1', 'member2'>>  \
                                               WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'"

   Aggiungi un attributo del set di stringhe per un elemento nella tabella Music.

   aws dynamodb execute-statement --statement "UPDATE Music  \
                                               SET BandMembers =set_add(BandMembers, <<'newmember'>>)  \
                                               WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'"

4. Elimina un elemento dalla tabella Music utilizzando l'istruzione DELETE (ELIMINA) PartiQL.

   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());
        }
    }

}