Conceitos básicos de PartiQL para DynamoDB - Amazon DynamoDB

Conceitos básicos de PartiQL para DynamoDB

Esta seção descreve como usar PartiQL para DynamoDB via console do Amazon DynamoDB, AWS Command Line Interface (AWS CLI) e APIs do DynamoDB.

Nos exemplos a seguir, a tabela do DynamoDB definida no tutorial Conceitos básicos do DynamoDB é um pré-requisito.

Para obter informações sobre como usar o console do DynamoDB, a AWS Command Line Interface ou as APIs do DynamoDB para acessar o DynamoDB, consulte Acesso ao DynamoDB.

Para baixar e usar o NoSQL Workbench para criar instruções em PartiQL para DynamoDB, escolha PartiQL Operations (Operações PartiQL) no canto superior direito do Criador de operações do NoSQL Workbench para DynamoDB.

Console
Interface do Editor PartiQL que mostra o resultado da execução da operação de consulta na tabela Music.
  1. Faça login no AWS Management Console e abra o console do DynamoDB em https://console.aws.amazon.com/dynamodb/.

  2. No painel de navegação, no lado esquerdo do console, selecione PartiQL editor (Editor de PartiQL).

  3. Escolha a tabela Music.

  4. Escolha Query table (Consultar tabela). Essa ação gera uma consulta que não resultará em uma varredura de tabela completa.

  5. Substitua partitionKeyValue pelo valor da string Acme Band. Substitua sortKeyValue pelo valor da string Happy Day.

  6. Selecione o botão Run (Executar).

  7. Você pode visualizar os resultados da consulta escolhendo a opção Table view (Visualização da tabela ou JSON view (Visualização de JSON).

NoSQL workbench
Interface do NoSQL Workbench. Ela mostra uma declaração PartiQL SELECT que você pode executar na tabela Music.
  1. Escolha PartiQL statement (Instrução PartiQL).

  2. Insira a seguinte instrução SELECT PartiQL

    SELECT * FROM Music WHERE Artist=? and SongTitle=?
  3. Para especificar um valor para os parâmetros Artist e SongTitle:

    1. Escolha Optional request parameters (Parâmetros de solicitação opcionais).

    2. Escolha Add new parameters (Adicionar novos parâmetros).

    3. Escolha o tipo de atributo string e o valor Acme Band.

    4. Repita as etapas b e c e escolha o tipo string e o valor PartiQL Rocks.

  4. Se desejar gerar código, escolha Generate code (Gerar código).

    Selecione a linguagem desejada nas guias exibidas. Agora você pode copiar esse código e usá-lo na sua aplicação.

  5. Se desejar que a operação seja executada imediatamente, escolha Run (Executar).

AWS CLI
  1. Crie um item na tabela Music usando a instrução PartiQL INSERT.

    aws dynamodb execute-statement --statement "INSERT INTO Music \ VALUE \ {'Artist':'Acme Band','SongTitle':'PartiQL Rocks'}"
  2. Recupere um item da tabela Music usando a instrução PartiQL SELECT.

    aws dynamodb execute-statement --statement "SELECT * FROM Music \ WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'"
  3. Atualize um item na tabela Music usando a instrução PartiQL UPDATE.

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

    Adicione um valor de lista para um item na tabela Music.

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

    Remova um valor de lista para um item na tabela Music.

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

    Adicione um novo membro de mapa para um item na tabela Music.

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

    Adicione um novo atributo de conjunto de strings para um item na tabela Music.

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

    Atualize um atributo de conjunto de strings para um item na tabela Music.

    aws dynamodb execute-statement --statement "UPDATE Music \ SET BandMembers =set_add(BandMembers, <<'newmember'>>) \ WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'"
  4. Exclua um item da tabela Music usando a instrução PartiQL DELETE.

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