Working with Amazon Simple DB

Introduction

This article explores the concepts and methods necessary to create,
manipulate, and work with Amazon Simple DB.

Simple DB is an internet (cloud) based web service provided by Amazon.
Working with Simple DB requires an understanding of some concepts not usually present
when dealing with traditional databases.

For starters, there is no management console, or graphical interface
for interacting with Simple DB. All interaction with Simple DB is done though
code. Several languages are supported, including Java, C#, Perl, PHP, and VB.

In addition, Simple DB is not relational. There is no concept of a
JOIN on multiple tables. As a result, tables (called Domains) are created like
non-normalized reporting tables. Very flat tables holding columns of support
data.

In addition, Simple DB offers only one data type: string. All values
saved in the database are handled as strings. This means special consideration
must be used when values that are numeric or date time are going to be used in
WHERE or ORDER BY situations.

Getting Started

The first step to using Simple DB is to create an account. This can
be done from the AWS (Amazon Web Services) home page at http://aws.amazon.com/ . Your account will
yield two pieces of information, an Access Key and a Secret Key. The Access
Key is your account; the Secret Key is the password to work with the account.
With Simple DB, an Account is more than just a login name, it is actually your
Database name. When objects are created, such as tables, they are created
under your account. Each Account can contain 100 Domains (Tables). If you
need more tables, create a second account for an additional one hundred. Keep
in mind you may not need as many tables as usual, because we will not be
creating highly normalized structures.

Next, we’ll modify example code from Amazon on how to work with Simple
DB. For this article, the c# Visual Studio project was downloaded from the
Simple DB Resources web at http://developer.amazonwebservices.com/connect/kbcategory.jspa?categoryID=141
. Within the Visual Studio project, there is a file called Amazon Simple DB
Samples. This page contains a good overview of the various methods needed to
work with simple DB.

Create Domain

In this first example, we’ll pass our Account to Simple DB and Create
a Domain (Table).


String accessKeyId = “myAccount”;
String secretAccessKey = “password”;

AmazonSimpleDB service = new AmazonSimpleDBClient(accessKeyId, secretAccessKey);

CreateDomainRequest request = new CreateDomainRequest().WithDomainName(“myFirstTable”);

CreateDomainSample.InvokeCreateDomain(service, request);

The first three lines of code will pass our account creditials into
Simple DB. The last two lines specifiy a new table to be created called
“myFirstTable” and tie it to our account login. The executed results are shown
below.

CreateDomainResponse

Had there been an error, it would have been noted here. Instead, we
have two pieces of information, a Request ID and Box Usage. An ID is generated
for each action we take. The Box Useage can be used to figure out the cost of
each request we make. Current pricing can be obtained from http://aws.amazon.com/simpledb/#pricing
. At the time of this writing, Box Usage (machine resources used) was $0.14
per hour. So in our above example, we take Box Usage (.0055590278) x 0.14 =
$0.00078 for this action.

List Domain

To confirm our Domain (Table) has been created, we can list our
Domains by calling InvokeListDomains as shown below. Our newly created
myFirstTable appears in the Domain Name list.


String accessKeyId = “myAccount”;
String secretAccessKey = “password”;

AmazonSimpleDB service = new AmazonSimpleDBClient(accessKeyId, secretAccessKey);

ListDomainsRequest request = new ListDomainsRequest();

ListDomainsSample.InvokeListDomains(service, request);

ListDomainResponse

Like the first example, the first three lines create a security string
to pass in. The last two lines tie our List Request to the our Access Key.

Insert Columns and Values

Next, we’ll add columns and values to the newly created Domain.


String accessKeyId = “myAccount”;
String secretAccessKey = “password”;

AmazonSimpleDB service = new AmazonSimpleDBClient(accessKeyId, secretAccessKey);

List<ReplaceableAttribute> myList = new List<ReplaceableAttribute>(3);
myList.Add(new ReplaceableAttribute().WithName(“CustFirstName”).WithValue(“Bob”));
myList.Add(new ReplaceableAttribute().WithName(“CustLastName”).WithValue(“Smith”));
myList.Add(new ReplaceableAttribute().WithName(“LastSaleDate”).WithValue(“2009-06-01”));

PutAttributesRequest request = new PutAttributesRequest().WithDomainName(“myFirstTable”).WithItemName(“Item001”);

request.Attribute = myList;
PutAttributesSample.InvokePutAttributes(service, request);

PutAttributesResponse

The response returned without an error. The first thee lines of the
statement are the same Access Key values used previously. Next, an array of
Attribute Names and values are created. Note the LastSaleDate format. The
value is entered in an ISO format rather than a US format like ‘06/01/2009’. Using
the ISO format will ensure that ORDER BY and WHERE operations produce the
expected results. This is because all values stored in Simple DB, including
dates, are treated as strings. The WithItemName line assigns a label to the
row.

List the Data Rows

The List Data Rows command simply returns a line for row of data in
the Domain. The WithItemName value from the previous command is returned as
the label for the row.


String accessKeyId = “myAccount”;
String secretAccessKey = “password”;

AmazonSimpleDB service = new AmazonSimpleDBClient(accessKeyId, secretAccessKey);

QueryRequest request = new QueryRequest().WithDomainName(“myFirstTable”);
QuerySample.InvokeQuery(service, request);

QueryResponse

List the Data Row with Attributes

In this next example, we’ll return the row along with its values.


String accessKeyId = “myAccount”;
String secretAccessKey = “password”;

AmazonSimpleDB service = new AmazonSimpleDBClient(accessKeyId, secretAccessKey);

GetAttributesRequest request = new GetAttributesRequest().WithDomainName(“myFirstTable”).WithItemName(“Item001”);
GetAttributesSample.InvokeGetAttributes(service, request);

GetAttributesResponse

SELECT API

Originally, the query language used for Simple DB was not similar to
ANSI SQL. So to work with Simple DB new syntaxes needed to be learned. However,
a soon to be release API called Select will correct this. Using the Select API,
traditional query statements such as “SELECT * FROM customers WHERE firstname =
Bob”, will be supported.

Documentation

There are several documents on the Amazon Simple DB web site that will
assist in programming Simple DB. First is the getting started guide located at
http://docs.amazonwebservices.com/AmazonSimpleDB/latest/GettingStartedGuide/
. The guide contains an introduction to the web service and examples of
creating domains, entering data, and selecting rows from Simple DB. Also on
the web site is the Developers Guide located at http://docs.amazonwebservices.com/AmazonSimpleDB/latest/DeveloperGuide/
. The Developers Guide provides API, SOAP, and REST explanations. In
addition, there are reviews of basic concepts and terms. A Code and Samples
library page located at http://developer.amazonwebservices.com/connect/kbcategory.jspa?categoryID=189
contains an interesting assortment of Simple DB applications such as “Simple DB
Wrapper for iPhone” and a “Microsoft Excel Plug-in”.

Working with Numerical Data

Included in the Developers Guide is an important section explaining
how to work with numerical data. Because there is only one data type in
Simple DB, string, this means any numbers entered are sorted and evaluated like
a string. For example the number values 1, 3 5, 12, 14, if used in an ORDER BY
statement, would be returned as 1, 12, 14, 3, 5. To have them behave like
numbers as expected, we must zero pad them, or use offsets if there are any
negative numbers. Both of these concepts are detailed in the Developers Guide.

Conclusion

Simple DB is a cloud based database service offered by Amazon. The
structure is non-relational and designed for fast query responses. All data values
entered into Simple DB are treated as strings. Working with Simple DB is
accomplished though programming.

»


See All Articles by Columnist
Don Schlichting

Don Schlichting
Don Schlichting
Don Schlichting is a Microsoft MSCE, MCDBA and an Oracle Certified Associate. Working at Professional Control Corporation as the IT Director,.

Latest Articles