Working with Amazon Simple DB
March 6, 2009
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.
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, well 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.
In this first example, well 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.
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.
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);
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, well 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);
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);
List the Data Row with Attributes
In this next example, well 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);
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.
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.
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.