Amazon SimpleDB - Now With Select

There's now a new and somewhat easier way to write SimpleDB queries.

In addition to SimpleDB's existing query language, you can now use select statements which look very similar to standard SQL (Structured Query Language). We made some small changes and additions to the language in order to accomodate SimpleDB's unique multi-valued attribute model.

Here are some valid select statements:

select * from mydomain where city = 'Seattle'
select * from mydomain where city = 'Seattle' or city = 'Portland'
select * from mydomain where author not like 'Henry%'

Things get even more interesting once multi-valued atttibutes are used. This query returns the items where the only attribute value for keyword is 'Book':

select * from mydomain where every(keyword) = 'Book'

The following query returns items where the only value for keyword is 'Book' or Paperback':

select * from mydomain where every(keyword) in ('Book', 'Paperback')

And the following query returns all the items which have the values 'Book' and 'Hardcover' in keyword:

select * from mydomain where keyword = 'Book' intersection keyword = 'Hardcover'

You can also sort the results on any of the attributes that was used in the expression:

select * from mydomain where Year = '2007' intersection Author is not null order by Author desc

The new SimpleDB Select function accepts queries in this new syntax. The existing Query and QueryWithAttributes functions are still usable, of course. There's full information in the new version of the Developer Guide.

--Jeff;

Comments

[William Blanchard]

There was rumors and anticipation of another company starting a SQL cloud... then POW! Amazon is on the cutting edge of what developers need and this addition of Select API is yet another championship ring for AWS.

MySQL just bothers me in terms of needing to handle big spikes of use of say 1000 simultaneous transactions even with a cluster going. SQS has been solid and now SDB will become an important offering for enterprise users...

Perhaps there is some interest to create a real-time MySQL to SELECT API SDB bridge library- we will look at doing it in 2009 :)

[Paul Prescod]

William, I hate to burst your bubble but SQL-like syntax or not, Amazon SimpleDb is very different than a relational database. This change is a very minor part of bridging that yawning gap between their data models. I wish it were true that SimpleDb was just "MySQL without the scale problems" but it is not. There are deep reasons why it is really hard to arbitrarily scale relational databases and therefore why Amazon decided NOT to make SimpleDb a relational database.

[William Blanchard]

Hi Paul, it wont be long before we have INSERT, UPDATE and DELETE.

It only takes a hint to get me excited.

[Paul Prescod]

William: I'm not talking about three more method. I'm talking about the WHOLE RELATIONAL DATA MODEL. Primary keys, foreign keys, joins, transactions, constraints, triggers, sub-selects, etc. etc.

Compare the documentation for MySQL to the documentation for SimpleDB. There is almost no intersection. They are not the same kind of thing at all.

[Sdbexplorer]

SDB Explorer supports run SELECT query. Query results will be displayed in GRID. You can perform UPDATE/DELETE/SORT operation on query results.

http://goo.gl/kDgQr

[BC]

I am using SimpleDB right now for a project. And while I like the database, I absolutely hate the query language. Why SQL?? Why? Why? Why?

It's called NoSql for a reason!

It makes no sense. SQL is a great language for adhoc queries. It's great for business people that want to access the database, but don't want to learn a full blown programming language.

However it is god-awful for use as a programming API. Guess what Amazon, the vast majority of people accessing SimpleDB are programmers. I don't want to embed SQL strings in my code. It is impossible to maintain. It's ugly, it's stupid.

So here I am concatenating strings in a goddam where clause.

I mean I couldn't be more pissed off that Amazon did this to try and attract the legions of dipshit SQL developers.

Sorry for the rant, but fuck.

[xiaowei]

How and where to choose domain of the SDB,or it is defaultful???

[Jeff Barr]

@xiaowei - You specify the domain as part of the select:

"select * from mydomain where city = 'Seattle'"