public Query ( |
||
request | Container for the necessary parameters to execute the Query service method. | |
return |
private static void QueryByHashKey(string userId, AmazonDynamoDBClient client, DynamoDBContext context) { var selectQuery = string.Format("SELECT * FROM GameScores WHERE UserId = \"{0}\"", userId); Console.WriteLine("(AmazonDynamoDBClient) Running basic hash key query :\n\t\t{0}", selectQuery); var response = client.Query(selectQuery); Debug.Assert(response.Items.Count == 5); Debug.Assert(response.Items.TrueForAll(i => i["UserId"].S == userId)); Console.WriteLine("(DynamoDBContext) Running basic hash key query :\n\t\t{0}", selectQuery); var gameScores = context.ExecQuery<GameScore>(selectQuery).ToArray(); Debug.Assert(gameScores.Count() == 5); Debug.Assert(gameScores.All(gs => gs.UserId == userId)); }
private static void QueryWithRangeKey(string userId, AmazonDynamoDBClient client, DynamoDBContext context) { var selectQuery = string.Format("SELECT * FROM GameScores WHERE UserId = \"{0}\" AND GameTitle BEGINS WITH \"A\"", userId); Console.WriteLine("(AmazonDynamoDBClient) Running query with range key :\n\t\t{0}", selectQuery); var response = client.Query(selectQuery); Debug.Assert(response.Items.Count == 2); Debug.Assert(response.Items.TrueForAll(i => i["UserId"].S == userId)); Debug.Assert(response.Items.TrueForAll(i => i["GameTitle"].S.StartsWith("A"))); Console.WriteLine("(DynamoDBContext) Running query with range key :\n\t\t{0}", selectQuery); var gameScores = context.ExecQuery<GameScore>(selectQuery).ToArray(); Debug.Assert(gameScores.Count() == 2); Debug.Assert(gameScores.All(gs => gs.UserId == userId)); Debug.Assert(gameScores.All(gs => gs.GameTitle.StartsWith("A"))); }
public Dictionary<string, object> GetPinWithPinID(string owner, double pinDate) { Dictionary<string, object> retval = new Dictionary<string, object>(); QueryResponse response; var config = new AmazonDynamoDBConfig(); config.ServiceURL = System.Configuration.ConfigurationManager.AppSettings["ServiceURL"]; client = new AmazonDynamoDBClient(config); try { QueryRequest request = new QueryRequest() { TableName = "Pin", KeyConditions = new Dictionary<string, Condition>() { { "Owner", new Condition() { ComparisonOperator = ComparisonOperator.EQ, AttributeValueList = new List<AttributeValue> { new AttributeValue { S = owner } } } }, { "PinDate", new Condition() { ComparisonOperator = ComparisonOperator.EQ, AttributeValueList = new List<AttributeValue> { new AttributeValue { N = pinDate.ToString() } } } } } }; response = client.Query(request); retval.Add("Title", response.Items[0]["Title"].S); retval.Add("Owner", response.Items[0]["Owner"].S); retval.Add("OwnerName", response.Items[0]["UserName"].S); retval.Add("OwnerHeadshot", response.Items[0]["HeadshotURL"].S); retval.Add("Latitude", response.Items[0]["Latitude"].S); retval.Add("Longitude", response.Items[0]["Longitude"].S); retval.Add("PinDate", Convert.ToDouble(response.Items[0]["PinDate"].N)); retval.Add("Images", response.Items[0]["Images"].SS); } catch (AmazonDynamoDBException e) { Console.WriteLine(e.Message); } catch (AmazonServiceException e) { Console.WriteLine(e.Message); } catch (Exception e) { Console.WriteLine(e.Message); } return retval; }
public List<Dictionary<string, object>> GetPinWithUserID(string userID, double since, int takeCnt) { List<Dictionary<string, object>> retval = new List<Dictionary<string, object>>(); Dictionary<string, object> tmpObject = null; QueryResponse response = null; var config = new AmazonDynamoDBConfig(); config.ServiceURL = System.Configuration.ConfigurationManager.AppSettings["ServiceURL"]; client = new AmazonDynamoDBClient(config); try { QueryRequest query = new QueryRequest() { TableName = "Pin", KeyConditions = new Dictionary<string, Condition>() { { "Owner", new Condition() { ComparisonOperator = ComparisonOperator.EQ, AttributeValueList = new List<AttributeValue> { new AttributeValue { S = userID } } } }, { "PinDate", new Condition() { ComparisonOperator = ComparisonOperator.LT, AttributeValueList = new List<AttributeValue> { new AttributeValue { N = since.ToString() } } } } }, Limit = takeCnt, ScanIndexForward = false }; response = client.Query(query); foreach (var item in response.Items) { tmpObject = new Dictionary<string, object>(); tmpObject.Add("Title", item["Title"].S); tmpObject.Add("Owner", item["Owner"].S); tmpObject.Add("OwnerName", item["UserName"].S); tmpObject.Add("OwnerHeadshot", item["HeadshotURL"].S); tmpObject.Add("Latitude", item["Latitude"].S); tmpObject.Add("Longitude", item["Longitude"].S); tmpObject.Add("PinDate", Convert.ToDouble(item["PinDate"].N)); tmpObject.Add("Images", item["Images"].SS); retval.Add(tmpObject); } } catch (AmazonDynamoDBException e) { Console.WriteLine(e.Message); } catch (AmazonServiceException e) { Console.WriteLine(e.Message); } catch (Exception e) { Console.WriteLine(e.Message); } return retval; }
public override int GetErrors(int pageIndex, int pageSize, IList errorEntryList) { CheckTableExists(); if (pageIndex < 0) throw new ArgumentOutOfRangeException("pageIndex", pageIndex, null); if (pageSize < 0) throw new ArgumentOutOfRangeException("pageSize", pageSize, null); int max = pageSize * (pageIndex + 1); // http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/LowLevelDotNetQuerying.html AmazonDynamoDBClient client = new AmazonDynamoDBClient(); Dictionary<string, AttributeValue> lastKeyEvaluated = null; List<EL.ErrorLogEntry> list = new List<EL.ErrorLogEntry>(max); // there is a max of 1MB of data returned per read operation, so you have to do repeated reads until you reach the end do { Amazon.DynamoDBv2.Model.QueryRequest request = new Amazon.DynamoDBv2.Model.QueryRequest(s_TableName); request.KeyConditionExpression = "Application = :v_Application"; request.ExpressionAttributeValues = new Dictionary<string, AttributeValue> { { ":v_Application", new AttributeValue(this.ApplicationName) } }; request.IndexName = "Application-TimeUtc-index"; request.ScanIndexForward = false; request.Limit = max; request.Select = Select.ALL_PROJECTED_ATTRIBUTES; if (lastKeyEvaluated != null) request.ExclusiveStartKey = lastKeyEvaluated; QueryResponse response = client.Query(request); foreach (Dictionary<string, AttributeValue> item in response.Items) { string errorXml = item["AllXml"].S; string errorId = item["ErrorId"].S; EL.Error error = EL.ErrorXml.DecodeString(errorXml); list.Add(new EL.ErrorLogEntry(this, errorId, error)); } lastKeyEvaluated = response.LastEvaluatedKey; } while (lastKeyEvaluated != null && lastKeyEvaluated.Count > 0); int numToSkip = (pageIndex - 1) * pageSize; list = list.Skip(numToSkip).ToList(); list.ForEach(err => errorEntryList.Add(err)); return errorEntryList.Count; }
private static void QueryWithOrderAndLimit(string userId, AmazonDynamoDBClient client, DynamoDBContext context) { var selectQuery = string.Format("SELECT * FROM GameScores WHERE UserId = \"{0}\" ORDER ASC LIMIT 3", userId); Console.WriteLine("(AmazonDynamoDBClient) Running query with order and limit :\n\t\t{0}", selectQuery); var response = client.Query(selectQuery); Debug.Assert(response.Items.Count == 3); Debug.Assert(response.Items.TrueForAll(i => i["UserId"].S == userId)); Debug.Assert(response.Items.TrueForAll(i => i["GameTitle"].S.CompareTo(MeteorBlasters) < 0)); Console.WriteLine("(DynamoDBContext) Running query with order and limit :\n\t\t{0}", selectQuery); var gameScores = context.ExecQuery<GameScore>(selectQuery).ToArray(); Debug.Assert(gameScores.Count() == 3); Debug.Assert(gameScores.All(gs => gs.UserId == userId)); Debug.Assert(gameScores.All(gs => gs.GameTitle.CompareTo(MeteorBlasters) < 0)); }
private static void QueryWithGlobalSecondaryIndexProjectedAttributes(AmazonDynamoDBClient client, DynamoDBContext context) { var selectQuery = string.Format("SELECT * FROM GameScores WHERE GameTitle = \"{0}\" AND TopScore >= 1000 WITH(Index(GameTitleIndex, false), NoConsistentRead)", StarshipX); Console.WriteLine("(AmazonDynamoDBClient) Running query with global secondary index (projected attributes) :\n\t\t{0}", selectQuery); var response = client.Query(selectQuery); Debug.Assert(response.Items.Count > 0); Debug.Assert(response.Items.TrueForAll(i => !string.IsNullOrWhiteSpace(i["UserId"].S))); Debug.Assert(response.Items.TrueForAll(i => i["GameTitle"].S == StarshipX)); Debug.Assert(response.Items.TrueForAll(i => int.Parse(i["TopScore"].N) >= 1000)); Debug.Assert(response.Items.TrueForAll(i => i.Count == 3 && !i.ContainsKey("TopScoreDateTime"))); Console.WriteLine("(DynamoDBContext) Running query with global secondary index (projected attributes) :\n\t\t{0}", selectQuery); var gameScores = context.ExecQuery<GameScore>(selectQuery).ToArray(); Debug.Assert(gameScores.Any()); Debug.Assert(gameScores.All(gs => !string.IsNullOrWhiteSpace(gs.UserId))); Debug.Assert(gameScores.All(gs => gs.GameTitle == StarshipX)); Debug.Assert(gameScores.All(gs => gs.TopScore >= 1000)); Debug.Assert(gameScores.All(gs => gs.TopScoreDateTime == default(DateTime))); }
private static void QueryWithNoReturnedConsumedCapacity(string userId, AmazonDynamoDBClient client) { var selectQuery = string.Format("SELECT * FROM GameScores WHERE UserId = \"{0}\" WITH (NoReturnedCapacity)", userId); Console.WriteLine("(AmazonDynamoDBClient) Running query with no returned consumed capacity :\n\t\t{0}", selectQuery); var response = client.Query(selectQuery); Debug.Assert(response.Items.Count == 5); Debug.Assert(response.Items.TrueForAll(i => i["UserId"].S == userId)); Debug.Assert(response.ConsumedCapacity == null); }
private static void QueryWithLocalSecondaryIndexProjectedAttributes(string userId, AmazonDynamoDBClient client, DynamoDBContext context) { var selectQuery = string.Format("SELECT * FROM GameScores WHERE UserId = \"{0}\" AND TopScore >= 1000 WITH(Index(TopScoreIndex, false))", userId); Console.WriteLine("(AmazonDynamoDBClient) Running query with local secondary index (projected attributes) :\n\t\t{0}", selectQuery); var response = client.Query(selectQuery); Debug.Assert(response.Items.Count > 0); Debug.Assert(response.Items.TrueForAll(i => i["UserId"].S == userId)); Debug.Assert(response.Items.TrueForAll(i => int.Parse(i["TopScore"].N) >= 1000)); Debug.Assert(response.Items.TrueForAll(i => i.Count == 3 && !i.ContainsKey("TopScoreDateTime"))); Console.WriteLine("(DynamoDBContext) Running query with local secondary index (projected attributes) :\n\t\t{0}", selectQuery); var gameScores = context.ExecQuery<GameScore>(selectQuery).ToArray(); Debug.Assert(gameScores.Any()); Debug.Assert(gameScores.All(gs => gs.UserId == userId)); Debug.Assert(gameScores.All(gs => gs.TopScore >= 1000)); Debug.Assert(gameScores.All(gs => gs.TopScoreDateTime == default(DateTime))); }
private static void SelectSpecificAttributes(string userId, AmazonDynamoDBClient client, DynamoDBContext context) { var selectQuery = string.Format("SELECT UserId, GameTitle, Wins FROM GameScores WHERE UserId = \"{0}\"", userId); Console.WriteLine("(AmazonDynamoDBClient) Running query with specific attributes :\n\t\t{0}", selectQuery); var response = client.Query(selectQuery); Debug.Assert(response.Items.Count == 5); Debug.Assert(response.Items.TrueForAll(i => i["UserId"].S == userId)); Debug.Assert(response.Items.TrueForAll(i => i.Count() == 3)); Console.WriteLine("(DynamoDBContext) Running query with specific attributes :\n\t\t{0}", selectQuery); var gameScores = context.ExecQuery<GameScore>(selectQuery).ToArray(); Debug.Assert(gameScores.Count() == 5); Debug.Assert(gameScores.All(gs => gs.UserId == userId)); Debug.Assert(gameScores.All(gs => !string.IsNullOrWhiteSpace(gs.GameTitle) && gs.Wins > 0 && gs.Losses == 0 && gs.TopScore == 0 && gs.TopScoreDateTime == default(DateTime))); }
private static void ThrottlingWithQueryPageSize(string userId, AmazonDynamoDBClient client, DynamoDBContext context) { var selectQuery = string.Format("SELECT * FROM GameScores WHERE UserId = \"{0}\" LIMIT 3 WITH (PageSize(1))", userId); Console.WriteLine("(AmazonDynamoDBClient) Running query with PageSize :\n\t\t{0}", selectQuery); var response = client.Query(selectQuery); Debug.Assert(response.Items.Count == 3); Debug.Assert(response.Items.TrueForAll(i => i["UserId"].S == userId)); Console.WriteLine("(DynamoDBContext) Running query with PageSize :\n\t\t{0}", selectQuery); var gameScores = context.ExecQuery<GameScore>(selectQuery).ToArray(); Debug.Assert(gameScores.Count() == 3); Debug.Assert(gameScores.All(gs => gs.UserId == userId)); }
public void SearchSamples() { RemoveTables(); CreateLSITable(); TableUtils.WaitUntilTableActive("SampleTable", TestClient); { // Create items to put into first table Dictionary<string, AttributeValue> item1 = new Dictionary<string, AttributeValue>(); item1["Author"] = new AttributeValue { S = "Mark Twain" }; item1["Title"] = new AttributeValue { S = "A Connecticut Yankee in King Arthur's Court" }; item1["Pages"] = new AttributeValue { N = "575" }; Dictionary<string, AttributeValue> item2 = new Dictionary<string, AttributeValue>(); item2["Author"] = new AttributeValue { S = "Booker Taliaferro Washington" }; item2["Title"] = new AttributeValue { S = "My Larger Education" }; item2["Pages"] = new AttributeValue { N = "313" }; item2["Year"] = new AttributeValue { N = "1911" }; // Construct write-request for first table List<WriteRequest> sampleTableItems = new List<WriteRequest>(); sampleTableItems.Add(new WriteRequest { PutRequest = new PutRequest { Item = item1 } }); sampleTableItems.Add(new WriteRequest { PutRequest = new PutRequest { Item = item2 } }); AmazonDynamoDBClient client = new AmazonDynamoDBClient(); client.BatchWriteItem(new BatchWriteItemRequest { RequestItems = new Dictionary<string, List<WriteRequest>> { { "SampleTable", sampleTableItems } } }); PutSample(); } { #region Query Sample // Create a client AmazonDynamoDBClient client = new AmazonDynamoDBClient(); // Define item hash-key to be string value "Mark Twain" AttributeValue hashKey = new AttributeValue { S = "Mark Twain" }; // Define query condition to search for range-keys that begin with the string "The Adventures" Condition condition = new Condition { ComparisonOperator = "BEGINS_WITH", AttributeValueList = new List<AttributeValue> { new AttributeValue { S = "The Adventures" } } }; // Create the key conditions from hashKey and condition Dictionary<string, Condition> keyConditions = new Dictionary<string, Condition> { // Hash key condition. ComparisonOperator must be "EQ". { "Author", new Condition { ComparisonOperator = "EQ", AttributeValueList = new List<AttributeValue> { hashKey } } }, // Range key condition { "Title", condition } }; // Define marker variable Dictionary<string, AttributeValue> startKey = null; do { // Create Query request QueryRequest request = new QueryRequest { TableName = "SampleTable", ExclusiveStartKey = startKey, KeyConditions = keyConditions }; // Issue request var result = client.Query(request); // View all returned items List<Dictionary<string, AttributeValue>> items = result.Items; foreach (Dictionary<string, AttributeValue> item in items) { Console.WriteLine("Item:"); foreach (var keyValuePair in item) { Console.WriteLine("{0} : S={1}, N={2}, SS=[{3}], NS=[{4}]", keyValuePair.Key, keyValuePair.Value.S, keyValuePair.Value.N, string.Join(", ", keyValuePair.Value.SS ?? new List<string>()), string.Join(", ", keyValuePair.Value.NS ?? new List<string>())); } } // Set marker variable startKey = result.LastEvaluatedKey; } while (startKey != null && startKey.Count > 0); #endregion } { #region Query Local Secondary Index Sample // Create a client AmazonDynamoDBClient client = new AmazonDynamoDBClient(); // Define item hash-key to be string value "Mark Twain" AttributeValue hashKey = new AttributeValue { S = "Mark Twain" }; // Define query condition to search for range-keys ("Year", in "YearsIndex") that are less than 1900 Condition condition = new Condition { ComparisonOperator = "LT", AttributeValueList = new List<AttributeValue> { new AttributeValue { N = "1900" } } }; // Create the key conditions from hashKey and condition Dictionary<string, Condition> keyConditions = new Dictionary<string, Condition> { // Hash key condition. ComparisonOperator must be "EQ". { "Author", new Condition { ComparisonOperator = "EQ", AttributeValueList = new List<AttributeValue> { hashKey } } }, // Range key condition { "Year", // Reference the correct range key when using indexes condition } }; // Define marker variable Dictionary<string, AttributeValue> startKey = null; do { // Create Query request QueryRequest request = new QueryRequest { TableName = "SampleTable", ExclusiveStartKey = startKey, KeyConditions = keyConditions, IndexName = "YearsIndex" // Specify the index to query against }; // Issue request var result = client.Query(request); // View all returned items List<Dictionary<string, AttributeValue>> items = result.Items; foreach (Dictionary<string, AttributeValue> item in items) { Console.WriteLine("Item:"); foreach (var keyValuePair in item) { Console.WriteLine("{0} : S={1}, N={2}, SS=[{3}], NS=[{4}]", keyValuePair.Key, keyValuePair.Value.S, keyValuePair.Value.N, string.Join(", ", keyValuePair.Value.SS ?? new List<string>()), string.Join(", ", keyValuePair.Value.NS ?? new List<string>())); } } // Set marker variable startKey = result.LastEvaluatedKey; } while (startKey != null && startKey.Count > 0); #endregion } { #region Scan Sample // Create a client AmazonDynamoDBClient client = new AmazonDynamoDBClient(); // Define scan conditions Dictionary<string, Condition> conditions = new Dictionary<string, Condition>(); // Title attribute should contain the string "Adventures" Condition titleCondition = new Condition(); titleCondition.ComparisonOperator = ComparisonOperator.CONTAINS; titleCondition.AttributeValueList.Add(new AttributeValue { S = "Adventures" }); conditions["Title"] = titleCondition; // Pages attributes must be greater-than the numeric value "200" Condition pagesCondition = new Condition(); pagesCondition.ComparisonOperator = ComparisonOperator.GT;; pagesCondition.AttributeValueList.Add(new AttributeValue { N = "200" }); conditions["Pages"] = pagesCondition; // Define marker variable Dictionary<string, AttributeValue> startKey = null; do { // Create Scan request ScanRequest request = new ScanRequest { TableName = "SampleTable", ExclusiveStartKey = startKey, ScanFilter = conditions }; // Issue request ScanResult result = client.Scan(request); // View all returned items List<Dictionary<string, AttributeValue>> items = result.Items; foreach (Dictionary<string, AttributeValue> item in items) { Console.WriteLine("Item:"); foreach (var keyValuePair in item) { Console.WriteLine("{0} : S={1}, N={2}, SS=[{3}], NS=[{4}]", keyValuePair.Key, keyValuePair.Value.S, keyValuePair.Value.N, string.Join(", ", keyValuePair.Value.SS ?? new List<string>()), string.Join(", ", keyValuePair.Value.NS ?? new List<string>())); } } // Set marker variable startKey = result.LastEvaluatedKey; } while (startKey != null && startKey.Count > 0); #endregion } { // Create lots of items to put into first table var table = Amazon.DynamoDBv2.DocumentModel.Table.LoadTable(TestClient, "SampleTable"); var batchWrite = table.CreateBatchWrite(); for (int i = 0; i < 100; i++) { var document = new Amazon.DynamoDBv2.DocumentModel.Document(); document["Author"] = "FakeAuthor" + i; document["Title"] = "Book" + i; document["Pages"] = (180 + i); document["Year"] = 1900 + i; batchWrite.AddDocumentToPut(document); } batchWrite.Execute(); } { #region Parallel Scan Sample // Create a client AmazonDynamoDBClient client = new AmazonDynamoDBClient(); // Define scan conditions Dictionary<string, Condition> conditions = new Dictionary<string, Condition>(); // Pages attributes must be greater-than the numeric value "200" Condition pagesCondition = new Condition(); pagesCondition.ComparisonOperator = ComparisonOperator.GT; pagesCondition.AttributeValueList.Add(new AttributeValue { N = "200" }); conditions["Pages"] = pagesCondition; // Setup 10 simultaneous threads, each thread calling Scan operation // with its own segment value. int totalSegments = 10; Parallel.For(0, totalSegments, segment => { // Define marker variable Dictionary<string, AttributeValue> startKey = null; do { // Create Scan request ScanRequest request = new ScanRequest { TableName = "SampleTable", ExclusiveStartKey = startKey, ScanFilter = conditions, // Total segments to split the table into TotalSegments = totalSegments, // Current segment to scan Segment = segment }; // Issue request var result = client.Scan(request); // Write returned items to file string path = string.Format("ParallelScan-{0}-of-{1}.txt", totalSegments, segment); List<Dictionary<string, AttributeValue>> items = result.Items; using (Stream stream = File.OpenWrite(path)) using (StreamWriter writer = new StreamWriter(stream)) { foreach (Dictionary<string, AttributeValue> item in items) { writer.WriteLine("Item:"); foreach (var keyValuePair in item) { writer.WriteLine("{0} : S={1}, N={2}, SS=[{3}], NS=[{4}]", keyValuePair.Key, keyValuePair.Value.S, keyValuePair.Value.N, string.Join(", ", keyValuePair.Value.SS ?? new List<string>()), string.Join(", ", keyValuePair.Value.NS ?? new List<string>())); } } } // Set marker variable startKey = result.LastEvaluatedKey; } while (startKey != null && startKey.Count > 0); }); #endregion } }
public virtual bool IsImageInDynamo(AmazonDynamoDBClient dynamoDbClient, string tableName, string key) { try { var queryRequest = new QueryRequest { TableName = tableName, KeyConditions = new Dictionary<string, Condition> { { "Key", new Condition { ComparisonOperator = "EQ", AttributeValueList = new List<AttributeValue> { new AttributeValue {S = key} } } } }, ConsistentRead = true, }; QueryResponse queryResponse = dynamoDbClient.Query(queryRequest); return queryResponse.Count > 0; } catch (Exception ex) { _Default.LogMessageToPage("IsImageInDynamo Error: {0}", ex.Message); return false; } }
public virtual QueryResponse LookupByHashKey(AmazonDynamoDBClient ddbClient, string tableName, string company) { // Build request var queryRequest = new QueryRequest { TableName = tableName, KeyConditions = new Dictionary<string, Condition> { { "Company", new Condition { ComparisonOperator = "EQ", AttributeValueList = new List<AttributeValue> { new AttributeValue {S = company} } } } }, ConsistentRead = true, }; // Submit request and return the response return ddbClient.Query(queryRequest); }