Query and Scan of DynamoDB

Summary of Video

Using Amazon Web Services and DynamoDB functions to query and scan data in a DynamoDB table and return the results.

Prerequisites

Video Link

Support Materials

All Materials Copyright 2012 Dr. Ron Eaglin
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using Amazon;
using Amazon.DynamoDB;
using Amazon.DynamoDB.DocumentModel;
using Amazon.DynamoDB.DataModel;
using Amazon.SecurityToken;
using Amazon.Runtime;
using Amazon.DynamoDB.Model;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
// Add using statements to access AWS SDK for .NET services.
// Both the Service and its Model namespace need to be added
// in order to gain access to a service. For example, to access
// the EC2 service, add:
// using Amazon.EC2;
// using Amazon.EC2.Model;
namespace AWS_App1
{
class Program
{
private static AmazonDynamoDBClient client;
private const int MAXROWS = 100;
public static void Main(string[] args)
{
try
{
AmazonDynamoDBConfig config = new AmazonDynamoDBConfig();
config.ServiceURL = “http://dynamodb.us-west-2.amazonaws.com”;

client = new AmazonDynamoDBClient(config);

//UploadData();
//TransferData(“rawData”, “SERIALNO”, “HousingData”, “id”);
//QueryOnPrimaryKey(“HousingData”, “2006000000609”);
ScanOnAttributeValue(“HousingData”, “BDS”, “4”);
// Upload data (using the .NET SDK helper API to upload data)
//Console.WriteLine(“Data uploaded… To continue, press Enter”);

}
catch (AmazonDynamoDBException e) { Console.WriteLine(“DynamoDB Message:” + e.Message); }
catch (AmazonServiceException e) { Console.WriteLine(“Service Exception:” + e.Message); }
catch (Exception e) { Console.WriteLine(“General Exception:” + e.Message); }
Console.ReadLine();
}
private static void TransferData(string SourceTable, string SourcePK, string DestinationTable, string DestinationPK)
{
// These open the local SQL Server
Console.WriteLine(“Reading Source Data”);
SqlConnection conn = new SqlConnection(@”Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=FloridaHousingDB;Data Source=A30609\EAGLIN”);
SqlDataAdapter adapter = new SqlDataAdapter(“SELECT TOP 100 * FROM ” + SourceTable, conn);
// Puts the Data into a DataSet
DataSet ds = new DataSet();
adapter.Fill(ds);
// Get Table from SQL Server DataSet
DataTable dt = ds.Tables[0];
Console.WriteLine(“Opening Desitnation table at AWS”);
// Get handle to DynamoDB table
Table destTable = Table.LoadTable(client, DestinationTable);
int r = 0; // Row Number
// Loop for each row in the table
foreach (DataRow dr in dt.Rows)
{
Console.WriteLine(“Starting Insert Row: ” + r.ToString());
var doc = new Document();
doc[DestinationPK] = Convert.ToString(dr[SourcePK]);
int c = 0; // Column Number
// Loop for each column in the row
foreach (DataColumn dc in dt.Columns)
{
// check not null and not Primary Key
if ((dt.Rows[r][c] != null) && (dc.ColumnName != SourcePK))
{
Console.WriteLine(dc.ColumnName + ” ” + Convert.ToString(dt.Rows[r][c]));
doc[dc.ColumnName] = Convert.ToString(dt.Rows[r][c]);
}
c++; // increment column
}
Console.WriteLine(“Completed Insert Row: ” + r.ToString());
destTable.PutItem(doc);
r++; // increment row

}
}
private static void QueryOnPrimaryKey(string tableName, string pkValue)
{
// requires using Amazon.DynamoDB.Model; in header
GetItemRequest request = new GetItemRequest{
TableName = tableName,
Key = new Key {HashKeyElement = new AttributeValue { S = pkValue }}
};
GetItemResponse response = client.GetItem(request);
PrintItem(response.GetItemResult.Item);
}
private static void ScanOnAttributeValue(string tableName, string attributeName, string attributeValue)
{
ScanRequest request = new ScanRequest
{
TableName = tableName,
AttributesToGet = new List { “id” },
ScanFilter = new Dictionary<string, Condition>()
{
{attributeName, new Condition {
ComparisonOperator = “EQ”,
AttributeValueList = new List()
{
new AttributeValue {S = attributeValue }
}
}
}
}
};
ScanResponse response = client.Scan(request);
foreach (Dictionary<string, AttributeValue> item in response.ScanResult.Items)
{
PrintItem(item);
}
}
private static void QueryOnPrimaryKeyWithHashKey(string tableName, string pkValue)
{
// This methods requires a HashKey
QueryRequest request = new QueryRequest
{
TableName = tableName,
HashKeyValue = new AttributeValue { S = pkValue }
};
QueryResponse response = client.Query(request);
QueryResult result = response.QueryResult;
foreach (Dictionary<string, AttributeValue> item in response.QueryResult.Items)
{
PrintItem(item) ;
}
}

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

private static void UploadData()
{
Table sampleTable = Table.LoadTable(client, “SampleData”);
var d1 = new Document();
d1[“id”] = “1”;
d1[“Field1”] = “A field”;
d1[“Field2”] = “Another Field”;
sampleTable.PutItem(d1);
var d2 = new Document();
d2[“id”] = “2”;
d2[“Field1”] = “A field 2”;
d2[“Field2”] = “Another Field 2”;
sampleTable.PutItem(d2);
}
}
}