查询表和索引:.NET - Amazon DynamoDB
Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅 中国的 Amazon Web Services 服务入门 (PDF)

本文属于机器翻译版本。若本译文内容与英语原文存在差异,则一律以英文原文为准。

查询表和索引:.NET

Query 支持在 Amazon DynamoDB 中查询表或二级索引。您必须提供分区键值和相等条件。如果表或索引具有排序键,则可通过提供排序键值和条件来优化结果。

以下是使用低级 Amazon SDK for .NET API 查询表的步骤。

  1. 创建 AmazonDynamoDBClient 类的实例。

  2. 创建 QueryRequest 类的实例,并提供查询操作参数。

  3. 执行 Query 方法并提供您在之前步骤中创建的 QueryRequest 对象。

    响应包括可以提供由查询返回的所有项目的 QueryResult 数据元。

以下 C# 代码示例演示了上述任务。该代码假定您有用于存储论坛话题回复的 Reply 表。有关更多信息,请参阅为 DynamoDB 中的代码示例创建表和加载数据

Reply Id, ReplyDateTime, ... )

每个论坛话题都有一个唯一 ID,并且可以有 0 个或更多个回复。因此,主键由 Id(分区键)和 ReplyDateTime(排序键)这两者组成。

以下查询检索特定话题主题的所有回复。执行此查询需要使用表名称和 Subject 值。

AmazonDynamoDBClient client = new AmazonDynamoDBClient(); var request = new QueryRequest { TableName = "Reply", KeyConditionExpression = "Id = :v_Id", ExpressionAttributeValues = new Dictionary<string, AttributeValue> { {":v_Id", new AttributeValue { S = "Amazon DynamoDB#DynamoDB Thread 1" }}} }; var response = client.Query(request); foreach (Dictionary<string, AttributeValue> item in response.Items) { // Process the result. PrintItem(item); }

指定可选参数

Query 方法支持多个可选参数。例如,您可以选择指定一个条件,缩小先前查询的结果范围,以返回过去两周内的回复。这一条件称为排序键条件,因为 DynamoDB 会根据主键的排序键来评估您指定的查询条件。您可以指定其他可选参数以便只检索查询结果项目中特定的属性列表。有关更多信息,请参阅查询

以下 C# 代码示例检索过去 15 天内发布的论坛话题回复。该示例指定了以下可选参数:

  • KeyConditionExpression,只检索过去 15 天内的回复。

  • ProjectionExpression 参数,指定要为查询结果中的项目检索的属性列表。

  • ConsistentRead 参数,执行强一致性读取。

DateTime twoWeeksAgoDate = DateTime.UtcNow - TimeSpan.FromDays(15); string twoWeeksAgoString = twoWeeksAgoDate.ToString(AWSSDKUtils.ISO8601DateFormat); var request = new QueryRequest { TableName = "Reply", KeyConditionExpression = "Id = :v_Id and ReplyDateTime > :v_twoWeeksAgo", ExpressionAttributeValues = new Dictionary<string, AttributeValue> { {":v_Id", new AttributeValue { S = "Amazon DynamoDB#DynamoDB Thread 2" }}, {":v_twoWeeksAgo", new AttributeValue { S = twoWeeksAgoString }} }, ProjectionExpression = "Subject, ReplyDateTime, PostedBy", ConsistentRead = true }; var response = client.Query(request); foreach (Dictionary<string, AttributeValue> item in response.Items) { // Process the result. PrintItem(item); }

您还可以选择通过添加可选的 Limit 参数来限制页面大小 (每页的项目数量)。每次执行 Query 方法时,您都会获得一页结果,其中包含指定数量的项目。要抓取下一页结果,您可以提供上一页中最后一个项目的主键值再次执行 Query 方法,这样该方法就会返回下一组项目。您可以通过设置 ExclusiveStartKey 属性在请求中提供此信息。最初,此属性可以为空。要检索后面的页面,您必须将此属性值更新为上一页中最后一个项目的主键。

以下 C# 示例查询 Reply 表。它在请求中指定了 LimitExclusiveStartKey 可选参数。do/while 循环将不断执行一次扫描一页的操作,直到 LastEvaluatedKey 返回空值。

Dictionary<string,AttributeValue> lastKeyEvaluated = null; do { var request = new QueryRequest { TableName = "Reply", KeyConditionExpression = "Id = :v_Id", ExpressionAttributeValues = new Dictionary<string, AttributeValue> { {":v_Id", new AttributeValue { S = "Amazon DynamoDB#DynamoDB Thread 2" }} }, // Optional parameters. Limit = 1, ExclusiveStartKey = lastKeyEvaluated }; var response = client.Query(request); // Process the query result. foreach (Dictionary<string, AttributeValue> item in response.Items) { PrintItem(item); } lastKeyEvaluated = response.LastEvaluatedKey; } while (lastKeyEvaluated != null && lastKeyEvaluated.Count != 0);

示例-使用 Amazon SDK for .NET

下面的表存储关于论坛集合的信息。有关更多信息,请参阅为 DynamoDB 中的代码示例创建表和加载数据

Forum ( Name, ... ) Thread ( ForumName, Subject, Message, LastPostedBy, LastPostDateTime, ...) Reply ( Id, ReplyDateTime, Message, PostedBy, ...)

在此示例中,运行各种查询,查找论坛“DynamoDB”中话题“DynamoDB Thread 1”的回复。

  • 查找话题的回复。

  • 查找话题的回复。指定 Limit 查询参数来设置页面大小。

    此函数说明了如何使用分页处理多页结果。DynamoDB 对页面大小有限制,如果结果超出这一页面大小,将只能获得第一页结果。此编码模式可确保您的代码处理查询结果中的所有页面。

  • 查找过去 15 天内的回复。

  • 查找特定日期范围内的回复。

    前面两个查询显示了如何指定排序键条件来缩小查询结果范围以及如何使用其他可选查询参数。

有关测试以下示例的 step-by-step 说明,请参阅.NET 代码示例

using System; using System.Collections.Generic; using Amazon.DynamoDBv2; using Amazon.DynamoDBv2.Model; using Amazon.Runtime; using Amazon.Util; namespace com.amazonaws.codesamples { class LowLevelQuery { private static AmazonDynamoDBClient client = new AmazonDynamoDBClient(); static void Main(string[] args) { try { // Query a specific forum and thread. string forumName = "Amazon DynamoDB"; string threadSubject = "DynamoDB Thread 1"; FindRepliesForAThread(forumName, threadSubject); FindRepliesForAThreadSpecifyOptionalLimit(forumName, threadSubject); FindRepliesInLast15DaysWithConfig(forumName, threadSubject); FindRepliesPostedWithinTimePeriod(forumName, threadSubject); Console.WriteLine("Example complete. To continue, press Enter"); Console.ReadLine(); } catch (AmazonDynamoDBException e) { Console.WriteLine(e.Message); Console.ReadLine(); } catch (AmazonServiceException e) { Console.WriteLine(e.Message); Console.ReadLine(); } catch (Exception e) { Console.WriteLine(e.Message); Console.ReadLine(); } } private static void FindRepliesPostedWithinTimePeriod(string forumName, string threadSubject) { Console.WriteLine("*** Executing FindRepliesPostedWithinTimePeriod() ***"); string replyId = forumName + "#" + threadSubject; // You must provide date value based on your test data. DateTime startDate = DateTime.UtcNow - TimeSpan.FromDays(21); string start = startDate.ToString(AWSSDKUtils.ISO8601DateFormat); // You provide date value based on your test data. DateTime endDate = DateTime.UtcNow - TimeSpan.FromDays(5); string end = endDate.ToString(AWSSDKUtils.ISO8601DateFormat); var request = new QueryRequest { TableName = "Reply", ReturnConsumedCapacity = "TOTAL", KeyConditionExpression = "Id = :v_replyId and ReplyDateTime between :v_start and :v_end", ExpressionAttributeValues = new Dictionary<string, AttributeValue> { {":v_replyId", new AttributeValue { S = replyId }}, {":v_start", new AttributeValue { S = start }}, {":v_end", new AttributeValue { S = end }} } }; var response = client.Query(request); Console.WriteLine("\nNo. of reads used (by query in FindRepliesPostedWithinTimePeriod) {0}", response.ConsumedCapacity.CapacityUnits); foreach (Dictionary<string, AttributeValue> item in response.Items) { PrintItem(item); } Console.WriteLine("To continue, press Enter"); Console.ReadLine(); } private static void FindRepliesInLast15DaysWithConfig(string forumName, string threadSubject) { Console.WriteLine("*** Executing FindRepliesInLast15DaysWithConfig() ***"); string replyId = forumName + "#" + threadSubject; DateTime twoWeeksAgoDate = DateTime.UtcNow - TimeSpan.FromDays(15); string twoWeeksAgoString = twoWeeksAgoDate.ToString(AWSSDKUtils.ISO8601DateFormat); var request = new QueryRequest { TableName = "Reply", ReturnConsumedCapacity = "TOTAL", KeyConditionExpression = "Id = :v_replyId and ReplyDateTime > :v_interval", ExpressionAttributeValues = new Dictionary<string, AttributeValue> { {":v_replyId", new AttributeValue { S = replyId }}, {":v_interval", new AttributeValue { S = twoWeeksAgoString }} }, // Optional parameter. ProjectionExpression = "Id, ReplyDateTime, PostedBy", // Optional parameter. ConsistentRead = true }; var response = client.Query(request); Console.WriteLine("No. of reads used (by query in FindRepliesInLast15DaysWithConfig) {0}", response.ConsumedCapacity.CapacityUnits); foreach (Dictionary<string, AttributeValue> item in response.Items) { PrintItem(item); } Console.WriteLine("To continue, press Enter"); Console.ReadLine(); } private static void FindRepliesForAThreadSpecifyOptionalLimit(string forumName, string threadSubject) { Console.WriteLine("*** Executing FindRepliesForAThreadSpecifyOptionalLimit() ***"); string replyId = forumName + "#" + threadSubject; Dictionary<string, AttributeValue> lastKeyEvaluated = null; do { var request = new QueryRequest { TableName = "Reply", ReturnConsumedCapacity = "TOTAL", KeyConditionExpression = "Id = :v_replyId", ExpressionAttributeValues = new Dictionary<string, AttributeValue> { {":v_replyId", new AttributeValue { S = replyId }} }, Limit = 2, // The Reply table has only a few sample items. So the page size is smaller. ExclusiveStartKey = lastKeyEvaluated }; var response = client.Query(request); Console.WriteLine("No. of reads used (by query in FindRepliesForAThreadSpecifyLimit) {0}\n", response.ConsumedCapacity.CapacityUnits); foreach (Dictionary<string, AttributeValue> item in response.Items) { PrintItem(item); } lastKeyEvaluated = response.LastEvaluatedKey; } while (lastKeyEvaluated != null && lastKeyEvaluated.Count != 0); Console.WriteLine("To continue, press Enter"); Console.ReadLine(); } private static void FindRepliesForAThread(string forumName, string threadSubject) { Console.WriteLine("*** Executing FindRepliesForAThread() ***"); string replyId = forumName + "#" + threadSubject; var request = new QueryRequest { TableName = "Reply", ReturnConsumedCapacity = "TOTAL", KeyConditionExpression = "Id = :v_replyId", ExpressionAttributeValues = new Dictionary<string, AttributeValue> { {":v_replyId", new AttributeValue { S = replyId }} } }; var response = client.Query(request); Console.WriteLine("No. of reads used (by query in FindRepliesForAThread) {0}\n", response.ConsumedCapacity.CapacityUnits); foreach (Dictionary<string, AttributeValue> item in response.Items) { PrintItem(item); } Console.WriteLine("To continue, press Enter"); Console.ReadLine(); } private static void PrintItem( Dictionary<string, AttributeValue> attributeList) { foreach (KeyValuePair<string, AttributeValue> kvp in attributeList) { string attributeName = kvp.Key; AttributeValue value = kvp.Value; Console.WriteLine( attributeName + " " + (value.S == null ? "" : "S=[" + value.S + "]") + (value.N == null ? "" : "N=[" + value.N + "]") + (value.SS == null ? "" : "SS=[" + string.Join(",", value.SS.ToArray()) + "]") + (value.NS == null ? "" : "NS=[" + string.Join(",", value.NS.ToArray()) + "]") ); } Console.WriteLine("************************************************"); } } }