The Denodo DynamoDB Custom Wrapper is a Virtual DataPort custom wrapper for querying Amazon DynamoDB tables.
Amazon DynamoDB is a NoSQL database service offered by AWS. It has a key-value design.
It models the data into tables, which contain items, and these items have attributes. Items are schema-less, but when a table is created it is mandatory to specify a primary key.
DynamoDB is designed for high-performance at any scale.
The Denodo DynamoDB custom wrapper allows us to create base views on DynamoDB tables and execute SQL queries on those collections.
This component uses the Amazon DynamoDB module in the AWS Java SDK for communicating with the Amazon DynamoDB Service. The wrapper is read-only. It supports authentication via Access Key ID / Secret Access Key, plus assumed IAM roles and region. It delegates filtering and projections to the amount made possible by the binary DynamoDB Java APIs.
DynamoDB offers two operations for exploring and retrieving data from a table: Scan and Query. Query finds items based on indexed values while Scan looks for the desired results by scanning every item of the table (returns all the items by default). For performance reasons, the Query operation is a better option than Scan when queries include a condition on indexed fields (which are mandatory when Query is used).
This distribution includes two wrappers:
(Note there is a third implementation called com.denodo.connect.dynamodb.DynamoDBWrapper, which is totally equivalent to DynamoDBScanWrapper and should be considered deprecated).
Denodo DynamoDB Custom Wrapper architecture
This is a brief summary of the wrapper’s current features:
In order to use the Denodo DynamoDB Custom Wrapper in VDP, first it is necessary to install its JAR file into the Denodo VDP server. For this, in the VDP administration tool, go to the File > Extensions menu option and install the jar-with-dependencies file that is included in the distribution of the Denodo DynamoDB Custom Wrapper.
At the data source level, it is necessary to fill in the parameters related with authentication in DynamoDB.
None of these fields are mandatory.
At the data source level, it is necessary to fill in the parameters related with authentication in DynamoDB.
None of these fields are mandatory.
Once the custom wrapper has been registered, click on Create a base view.
This looks like this in DynamoDBScanWrapper:
And it looks like this in DynamoDBQueryWrapper:
There are also two parameters that are mutually exclusive:
field1:type1[,field2:type2,...].
Type should be one of the constants in java.sql.Types (note these are SQL standard types). See a specific section below to learn more about the allowed syntax.
When using the Fields parameter, we will need to specify the fields we would like to import as columns in VDP, using this syntax:
field1:type1[,field2:type2,...].
Type should be one of the constants in java.sql.Types (note these are SQL standard types).
Year: integer, Title: varchar, Info:\{ directors: varchar, release_date: timestamp, genres:varchar, image_url: varchar, plot: varchar, rank: integer, running_time_secs: integer, Actors: array(varchar) \} |
See how complex structures are specified using braces ({...}), which need to be escaped, and arrays are specified using the array keyword and specifying the contents of the array between parentheses.
Also field names can be surrounded by single or double quotes (e.g. "phone number") if they contain white spaces or non-alphanumeric chars.
In the following example we import the table Movies from Dynamodb to VDP using the parameter Fields.
Example of Fields
The resulting schema translates the java.sql.Types of Fields to VQL types.
DynamoDB Base View
The user has the option not to explicitly define the schema of the base view, using the Condition Introspection parameter. This parameter has a simple condition to filter the items of a DynamoDB table. Based on the results of the query the custom wrapper builds an schema, when the same field appears several times, the type of this field will be the more generic.
The condition only can be on simple attributes, it isn't possible to filter on arrays or structs. The syntax of this field is: <operand> <operator> <value>. The allowed operators are:
=, <>, <, <=, >, >=, CONTAINS and BETWEEN. The operator has to be between blank spaces. The CONTAINS operator only supports String type.
The values of the string and date types have to be between double quotes, and in the case of date type have to be represented according to the ISO8601 standard format.
Id = 5
Name = "Bryan"
Year BETWEEN 1920 and 1930
In the following example we import the table Movies from Dynamodb to VDP using the parameter Introspection condition with year = 1934
Example using Introspection condition
DynamoDB Base View
The DynamoDBScanWrapper implementation does not pose any specific limitations on the conditions or projections of queries. As mentioned, this wrapper delegates the data retrieval operation to DynamoDB as a Scan call.
In DynamoDBQueryWrapper implementation uses a Query call in DynamoDB, and establishes a series of limitations on queries so that they adapt to the requirements of Query calls.
Specifically, a condition on the configured index’s partition key fields has to be included in the WHERE clause of the query performed on the base view, and this condition needs to use the equality operator (=). A condition on the sort key of the index can also be specified, and this sort condition can use these operators: =, < , <=, > , >= and between.
Only one partition condition and one sort condition can be specified in the query. These index and sort conditions need to be specified at the first level of the WHERE tree of conditions, and they need to be joined with AND between them and also with the root of the rest of the condition tree on other fields.
Conditions on other fields in the base view can be added to the WHERE clause without restrictions.
Example:
SELECT *
FROM table
WHERE keyPartition = 'keypartition'
AND sortPartition < 'sortpartition'
AND otherFields <> 'otherFields'
Note that keyPartition is mandatory and sortPartition and the rest of the fields participating in the query conditions are optional.
DynamoDB Base View execution