12.10. Amazon SimpleDB Translator
12.10. Amazon SimpleDB Translator Amazon SimpleDB is a web service for running queries on structured data in real time. This service works in close conjunction with Amazon Simple Storage Service (Amazon S3) and Amazon Elastic Compute Cloud (Amazon EC2), collectively providing the ability to store, process and query data sets in the cloud. These services are designed to make web-scale computing easier and more cost-effective for developers. This translator provides you with a way to connect to Amazon SimpleDB and it also provides relational functionality to add records directly from a user or from other sources that are integrated with Teiid. It does so via SQL. It also gives you the ability to read, update and delete existing records from the SimpleDB store. Amazon SimpleDB is a hosted key/value store where a single key can contain multiple attribute name/value pairs where "value" can also be a multi-value. When you import the metadata from SimpleDB into Teiid, the constructs are aligned like this: Table 12.4. Registry Properties Simple DB Name SQL (Teiid) Domain Table Item Name Column (ItemName) Primary Key attribute - single value Column - String Datatype attribute - multi value Column - String Array Datatype Since all attributes are considered, by default, to be string data types, columns are defined with string data type. However, during modeling of the schema in Designer, you can use various other data types supported through Teiid to define a data type of column, if you wish to expose one. Important If you did modify the data type be something other than a string, do not use these changed columns in comparison queries, as SimpleDB does only lexicographical matching. To avoid using them, set the "SearchType" on the changed column to "UnSearchable" This is an example Dynamic VDB that shows how you define the SimpleDB translator: Note The translator does not provide a connection to the SimpleDB. For that purpose, Teiid has a JCA adapter that provides a connection to SimpleDB using Amazon SDK Java libraries. To define such connector, see Amazon SimpleDB Data Sources or see an example in the jboss-as/docs/teiid/datasources/simpledb file. If you are using Designer Tooling, to create a VDB, you can create or use a Teiid Designer Model project. Use the "Teiid Connection - Source Model" importer, create a SimpleDB Data Source using the Data Source Creation wizard and use simpledb as the translator in the importer. The table is created in a source model by this importer, providing that the data is already defined on Amazon SimpleDB. Create a VDB and deploy into Teiid Server and use either jdbc, odbc or odata to query it. The Amazon SimpleDB Translator currently has no import or execution properties. The Amazon SimpleDB Translator supports SELECT statements with a restrictive set of capabilities including: comparison predicates, IN predicates, LIMIT and ORDER BY. Insert, update, delete are also supported. Attributes with multiple values will defined as string array type. So this column is treated SQL Array type. This table shows the SimpleDB way of querying compared with the Teiid way of querying: Table 12.5. Registry Properties SimpleDB Query Teiid Query select * from mydomain where Rating = '4 stars' or Rating = '****' select * from mydomain where Rating = ('4 stars','****') select * from mydomain where Keyword = 'Book' and Keyword = 'Hardcover' select * from mydomain where intersection(Keyword,'Book','Hardcover') select * from mydomain where every(Rating) = '****' select * from mydomain where every(Rating) = '****' If you want to Insert/Update/Delete you can write prepare statements or you can compose SQL statements like this: Warning The Direct Query Support feature is turned off by default because of the security risk this exposes to execute any command against the source. To enable this feature, override the execution property called SupportsDirectQueryProcedure to true. Note By default the name of the procedure that executes the queries directly is called native. Override the execution property DirectQueryProcedureName to change it to another name. The SimpleDB translator provides a procedure to execute any ad-hoc simpledb query directly against the source without Teiid parsing or resolving. Since the metadata of this procedure's results are not known to Teiid, they are returned as an object array. ARRAYTABLE can be used construct tabular output for consumption by client applications. Direct query supported for "select" based calls. The Teiid-specific Amazon SimpleDB Resource Adapter should be used with this translator
<vdb name="myvdb" version="1"> <model name="simpledb"> <source name="node" translator-name="simpledb" connection-jndi-name="java:/simpledbDS"/> </model> <vdb> INSERT INTO mydomain (ItemName, title, author, year, pages, keyword, rating) values ('0385333498', 'The Sirens of Titan', 'Kurt Vonnegut', ('1959'), ('Book', Paperback'), ('*****','5 stars','Excellent')) SELECT X.* FROM simpledb_source.native('SELECT firstname, lastname FROM users') n, ARRAYTABLE(n.tuple COLUMNS firstname string, lastname string) AS X