Because Amazon DynamoDB is a NoSQL data source, queries need to be handled a bit differently than standard relational databases.

Value-Sensitive Queries

The lack of a required data type for a given column means that you could store different types of data in a single column. For instance, one row could have a String called EmailAddresses and another could have a StringSet also called EmailAddresses. For these and other kinds of cases, the component largely determines what data type to use based on the values in the query.

For instance, say you have an Items table where the PartNumber could store either a String or a Number. To get back a part with the PartNumber of the number value 12345, you would issue the following query:

SELECT Name, Location, Quantity, PartNumber FROM Items WHERE PartNumber = 12345

Alternatively, the PartNumber could have been stored as the string "12345". To get back a part with the PartNumber of the literal string 12345, issue the following query:

SELECT Name, Location, Quantity, PartNumber FROM Items WHERE PartNumber = '12345'

If the data type of the specified value is not ambiguous, it is always used before the autodetected data type. In both of these cases if a parameter was used instead of of a hardcoded value, then the data type of the parameter would be used to determine what type to submit to Amazon DynamoDB.

Detected Column Data Type

If a value is not obvious based purely on the detected data type, the component compares it to the autodetected column. For instance, if you want to insert a column called Coordinates into the Location table, your INSERT would look like:

INSERT INTO Locations (Address, Coordinates) VALUES ('123 Fake Street', '[40.7127, 74.0059]')

Based on the input value alone, the detected data type is a string. However, because a Coordinates column was previously autodetected, the component inserts a NumberSet and not a simple String.

If a Coordinates column was not autodetected when scanning the Locations table, the data type of the inserted value is used.

In this case, we could still resolve that the INSERT is a NumberSet, but it will cost a bit more overhead to do this.

Count

Amazon DynamoDB supports 2 different methods of of using the COUNT aggregate function. To simply return the number of Items in you table, issue the following query:

SELECT COUNT(*) FROM MyTable

The CData SSIS Components for Amazon DynamoDB will read the ItemCount from the DescribeTable Action. This avoids using too many read units to scan the full table. However, DynamoDB updates this value approximately every six hours and recent changes might not be reflected in this value.

Issuing the below example queries will instead scan the full table for count:

SELECT COUNT(*) FROM MyTable WHERE MyInt > 10
SELECT COUNT(MyInt) FROM MyTable