Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Feb 2, 2009

Cloud Computing with Amazon Simple DB

By Don Schlichting

Introduction

Simple DB is a database web service offered by Amazon.com. This article will introduce the Simple DB service itself as well as the concepts needed to work with it.

What is Cloud Computing?

Wikipedia defines Cloud Computing as “a style of computing in which resources are provided as a service over the internet”. For me personally, Cloud Computing means developing or managing a machine or service I do not have physical responsibility for and is located somewhere in the internet. I further break down Cloud Computing into two roles of activity, either managing an entire virtual asset (virtual machine or application), or just interacting with a specific service. Amazon Simple DB is the later because we interact with the database through a service and are not responsible for any operating system or database maintenance functions.

What is Simple DB

Amazon describes Simple DB as, “a web service providing the core database functions of data indexing and querying”. The product is designed so that neither IS or the developer needs to manage, maintain, or administrate the database server or the operating system underneath.

Simple DB is one of several services collectively known as Amazon Web Services (AWS). All the AWS products are Cloud (internet) based. Other Amazon Web Service products include file storage (Simple Storage Service), message queuing (Simple Queue), and computing capacity (virtual machines called Elastic Compute Cloud).

Simple DB Pricing

Because Simple DB is an internet service, there is no equipment to purchase. Pricing is based on the amount of data stored and the amount of data transferred from the service to your consuming application. Pricing details can be found on Amazons web site at the following URL: http://aws.amazon.com/simpledb/#pricing .

Domains

The database model is non-relational. Within the database, you create Domains, these are similar to traditional database Tables. One difference though, because the model is non-relational, there is no command to JOIN (INNER JOIN or OUTER JOIN for example) one Domain to another and produce a result set. It was easiest for me to consider a Domain layout in the same way a highly non-normalized table used in reporting may be laid out. For example, in a typical relational database application for processing Sales, we would create a Customer Table, a Products Table, and a Sales Table. All would be related to each other using Keys as shown below:

All tables would be related to each other using Keys

But for the reporting side of Sales, we may create a non-normalized Table with just the subsets of information all contained in one Table.

for the reporting side of Sales, we may create a non-normalized Table with just the subsets of information all contained in one Table.

The layout of this “SalesReporting” table would produce very quick query results for reports. The same type of demoralization is required when working with Simple DB.

Each Customer Account you have (think of a Customer Account as a Database) can contain 100 Domains. Each Domain can store up to 10 Gigabyte of data.

Items, Attributes, and Values

A Simple DB Item is a row of data inside a Domain. Columns of the Domain are called Attributes.

A Simple DB Item is a row of data inside a Domain.  Columns of the Domain are called Attributes.

There can be up to 256 Attributes (Columns) per Domain (Table).

Values are the actual data stored in the Domain. The max length of a Value is 1024 bytes.

Attributes (Columns) can contain multiple Values (data). For example, imagine we have a used car lot with six cars, two different models each coming in three colors. In a regular database, we would usually create six rows of data, one row of data for each individual car as shown below:

Attributes (Columns) can contain multiple Values (data).

With Simple DB however, inserting multiple values into an Attribute is allowed and encouraged. We could store our color attribute values together for each model of car. Now only two rows (Items) of data would be required.

With Simple DB however, inserting multiple values into an Attribute is allowed and encouraged.

There are special commands used to retrieve and work with these multivalue Items.

Data Types

There is only one Data Type with Simple DB, String. All Values (data) in Simple DB are stored as UTF-8 Strings. Because all stored data is a String, care must be taken when an Attribute (column) is used in a WHERE or ORDER BY type SELECT statement. For example, Dates should be entered in the format of YYYY-MM-DD (ISO 8601). Doing this will produce proper lexicographical comparisons. Numbers also need special handling if they are to be sorted or range selected. A process called Zero Padding is used for this. To Zero Pad, add zeros to the front of each number until all are the same length. For example if you had two numbers, 12 and 6, pad the 6 to become 06. Now on a sort, the six will list before 12 as we would expect in Ascending order. Your application will have to trim the leading zeros. Next month, we’ll explore this topic in full, including working with negative numbers.

Interface

Currently there isn’t any Management Console for Simple DB. All tasks, including Creating Domains, entering data, data definition tasks, and all data manipulation tasks are done via a programming interface you create. Multiple languages are supported including Java, C#, Pearl, PHP, VBNet. The following examples will be done in Visual Studio Dot Net with c#. To begin, download the Visual Studio sample from the AWS web site.

This first example creates a new Domain (Table).

Two using statements will be needed to reference Simple DB:

using Amazon.SimpleDB;
using Amazon.SimpleDB.Model;

Next, anytime we interact with Simple DB, an Access Key and private Secret key are passed.

String accessKeyId = "myAccessKey";
String secretAccessKey = "mySecretKey";

Now a new instance of Simple DB is invoked using our keys.

AmazonSimpleDB service = new AmazonSimpleDBClient(accessKeyId, secretAccessKey);

Lastly, a new Domain (Table) is created with the name “MyStore”.

CreateDomainRequest request = new CreateDomainRequest().WithDomainName("MyStore");
            
CreateDomainSample.InvokeCreateDomain(service, request);

When the application is run from Visual Studio, a command box will confirm the Domain was created.

When the application is run from Visual Studio, a command box will confirm the Domain was created.

The “BoxUsage” can be used to figure out the actual cost that will be charged for running this statement.

Conclusion

The Amazon Simple DB is a web database service offered by Amazon. If you are a regular Oracle or SQL Server professional, a couple of Simple DB rules will need to be remembered, such as when working with Dates and Numbers and the fact that Simple DB is not relational. Next month, we’ll create sample APSX web applications that will fully explore the Simple DB syntax.

» See All Articles by Columnist Don Schlichting



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date