PartiQL for DynamoDB 入门 - Amazon DynamoDB
Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅中国的 Amazon Web Services 服务入门

PartiQL for DynamoDB 入门

本节介绍如何从 Amazon DynamoDB 控制台、Amazon Command Line Interface (Amazon CLI) 和 DynamoDB API 使用 PartiQL for DynamoDB。

在以下 DynamoDB 例中,DynamoDB 入门教程中定义的 DynamoDB 表是一个前提条件。

有关使用 DynamoDB 控制台、Amazon Command Line Interface 或 DynamoDB API 访问 DynamoDB 的信息,请参阅访问 DynamoDB

下载并使用 NoSQL Workbench 生成 PartiQL for DynamoDB 语句,请选择 NoSQL Workbench for DynamoDB 操作生成器右上角的 PartiQL operations(PartiQL 操作)。

Console
注意

只有新的 DynamoDB 控制台中才提供 PartiQL for DynamoDB。要使用新的 DynamoDB 控制台,请选择控制台左侧导航窗格的尝试新控制台的预览

  1. 登录 Amazon Web Services Management Console,打开 DynamoDB 控制台:https://console.aws.amazon.com/dynamodb/

  2. 在控制台左侧的导航窗格中,选择 PartiQL editor (PartiQL 编辑器)

  3. 选择 Music 表。

  4. 选择 Query table (查询表)。此操作生成不会导致完整表扫描的查询。

  5. partitionKeyValue 替换为字符串值 Acme Band。将 sortKeyValue 替换为字符串值 Happy Day

  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. 指定 ArtistSongTitle 参数值:

    1. 选择 Optional request parameters (可选请求参数)

    2. 选择 Add new parameters (添加新参数)

    3. 选择属性类型 string 和值 Acme Band

    4. 重复步骤 b 和 c,然后选择类型 string 和值 PartiQL Rocks

  4. 如果要生成代码,请选择 Generate code (生成代码)

    从显示的选项卡中选择所需的语言。现在,您便可复制此代码并在应用程序中使用它。

  5. 如果要立即执行操作,请选择 Run (执行)

Amazon 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. 使用 INSERT 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. 使用 DELETE 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()); } } }