|
SSIS Amazon DynamoDB Source Adapter (Bulk Extract,Read,Copy NoSQL Data)
|
 
|
|
Amazon DynamoDB Source can be used to extract large amount of data from Amazon DynamoDB. This component supports SQL like query language to query DynamoDB data without worrying about complex API. Amazon DynamoDB is a cloud based NoSQL data storage service from Amazon AWS
|
AccessKey: AKIAIOSFODNN7EXAMPLE SecretKey: wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEYClick here to read more how to get your AccessKey and Secret Key.
| Property Name | Description | ||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| LoggingMode | LoggingMode determines how much information is logged during Package Execution. Set Logging mode to Debugging for maximum log.
|
||||||||||||||||||||
| PrefixTimestamp | When you enable this property it will prefix timestamp before Log messages. | ||||||||||||||||||||
| TableName | Table name from where you want to read data | ||||||||||||||||||||
| BatchSize | Indicates how many records are process in a single request. Setting this value to 0 will extract all records in single request. | ||||||||||||||||||||
| AccessMode | Indicates Access Mode of data (e.g. Table or Sql Query)
|
||||||||||||||||||||
| SqlCommand | Specifies SQL query to access data. This setting is ignored when access mode is set to Table. | ||||||||||||||||||||
| OutputMode | Specifies how you want to output columns. If you select RAW JSON Document mode then only single document will output for each record in DT_NTEXT datatype
|
||||||||||||||||||||
| IndentOutput | Indent JSON output so its easy to read. | ||||||||||||||||||||
| DateFormatString | Specifies how custom date formatted strings are parsed when reading JSON. | ||||||||||||||||||||
| DateParseHandling | Specifies how date formatted strings, e.g. Date(1198908717056) and 2012-03-21T05:40Z, are parsed when reading JSON.
|
||||||||||||||||||||
| EnableJsonPath | This option allows you to extract array content and sub documents of array. | ||||||||||||||||||||
| Filter | Enter expression here to filter data.(Example: $.Users[*].UserName ) This will fetch User names from users records | ||||||||||||||||||||
| IncludeParentColumns | Use this option to include parent properties (Non array) in the output along with Filtered Rows | ||||||||||||||||||||
| IncludeParentColumnsWhenChildMissing | By default child and parent information is not included in the output if children not found for specified expression. For example if you want to extract all orders from all customers nodes then you can type $.Customers[*].Orders[*]. This will fetch all orders from all customers. By default customers records without orders wont be included in the output. If you want to include those customers where orders not found then check this option (Output null information for order attributes). This behavior is similar to LEFT OUTER JOIN in SQL (Left side is parent, right side is child). This option is ***resource intensive*** so only check if you really care about this behavior. | ||||||||||||||||||||
| IncludeParentColumnsWithArrayType | Set this option to true if you want to output parent columns which are array. By default any parent column which is an array is not included in output. See also FilterForParentColumnsWithArrayType property if you set this property | ||||||||||||||||||||
| FilterForParentColumnsWithArrayType | Filter expression to extract value form parent | ||||||||||||||||||||
| ParentColumnPrefix | Prefix for parent column name. This option is only valid if you have set IncludeParentColumns=True | ||||||||||||||||||||
| ThrowErrorIfPropertyMissing | Throw error if property name specified in filter expression is missing. By default it will ignore any missing property errors. | ||||||||||||||||||||
| MaxLevelsToScan | This property how many nested levels should be scanned to fetch various properties. 0=Scan all child levels. | ||||||||||||||||||||
| ExcludedProperties | List comma separated property names from JSON document which you want to exclude from output. Specify parent property name to exclude all child nodes. | ||||||||||||||||||||
| LevelSeparator | Property level separator used in generated property name (separator for outer properties - Above selected filter node). Use this if default separator is producing duplicate property name which is conflicting with existing name. | ||||||||||||||||||||
| EnableArrayFlattening | Enables deep array flattening for selected filtered hierarchy. When you turn on this property it will flatten each property of each array item and expose as column (e.g. If you have Filter set as $.customers[*] and for each customer you have an array of Addresses then you may see output columns like Addresses.1.City, Addresses.1.State, Addresses.2.City, Addresses.2.State .... Addresses.N.City, Addresses.N.State). You can control how many array items you want to flatten by setting MaxArrayItemsToFlatten property. | ||||||||||||||||||||
| MaxArrayItemsToFlatten | Maximum number of array items to flatten. inner array flattening. Adjust this property to control how many columns being generated. This option is ignored if you set EnableArrayFlattening=false | ||||||||||||||||||||
| MetaDataScanMode | Metadata scan mode controls how data type and length is determined. By default few records scanned to determine datatype/length. Changing ScanMode affects length/datatype accuracy.
|
||||||||||||||||||||
| MetaDataCustomLength | Length for all string column. This option is only valid for MetaDataScanMode=Custom | ||||||||||||||||||||
| MetaDataTreatStringAsAscii | When this option is true, it detects all string values as DT_STR (Ascii) rather than DT_WSTR (Unicode) |
SELECT TOP 2 * FROM Customer WHERE (CustomerID='ALFKI' OR CustomerID='BOLID')
SELECT Age, CustomerID FROM Customer WHERE CustomerID IN ('ALFKI','BOLID','C3')
<Select Statement> ::= SELECT [{TOP Clause}] {Column List} {From Clause} [{Where Clause}] [{With Clause}]
<TOP Clause> ::= TOP IntegerLiteral
<Column List> ::= * | IDENTIFIER [ [,IDENTIFIER] [,IDENTIFIER] ... ]
<From Clause> ::= FROM IDENTIFIER
<Where Clause> ::= WHERE <Expression List>
<With Clause> ::= WITH ( [SCAN|QUERY] [,INDEX=your_index_name] [,LIMIT=some_integer] [,SCAN_INDEX_FORWARD=true|false] [,CONSISTENT_READ] )
! =============================================================================
! Expressions
! =============================================================================
! Mixing AND, OR not allowed. Just one type of Logical Operator can be used across multiple conditions in your filter
<Expression List> ::=
| <Expression> [[AND <Expression>] [AND <Expression>] ...]
| <Expression> [[OR <Expression>] [OR <Expression>] ...]
<Expression> ::= IDENTIFIER = <Value>
<Expression> ::= IDENTIFIER != <Value>
<Expression> ::= IDENTIFIER <> <Value>
<Expression> ::= IDENTIFIER > <Value>
<Expression> ::= IDENTIFIER >= <Value>
<Expression> ::= IDENTIFIER < <Value>
<Expression> ::= IDENTIFIER <= <Value>
<Expression> ::= IDENTIFIER IN (<Value> [[, <Value>] [, <Value>] ...])
<Expression> ::= IDENTIFIER BETWEEN <Value> AND <Value>
<Expression> ::= IDENTIFIER LIKE <Value>
<Expression> ::= IDENTIFIER NOT LIKE <Value>
<Expression> ::= IDENTIFIER CONTAINS <Value>
<Expression> ::= IDENTIFIER IS NULL
<Expression> ::= IDENTIFIER IS NOT NULL
<Value> ::= IntegerLiteral | StringLiteral
| Option | Description |
|---|---|
| SCAN |
A
A single |
| QUERY (This is default) |
A
In a
The following function is also supported:
|
| LIMIT | The maximum number of items to evaluate (not necessarily the number of matching items). If DynamoDB processes the number of items up to the limit while processing the results, it stops the operation and returns the matching values up to that point, and a key in LastEvaluatedKey to apply in a subsequent operation, so that you can pick up where you left off. Also, if the processed data set size exceeds 1 MB before DynamoDB reaches this limit, it stops the operation and returns the matching values up to the limit, and a key in LastEvaluatedKey to apply in a subsequent operation to continue the operation. For more information, see Query and Scan in the Amazon DynamoDB Developer Guide. |
| INDEX | The name of an index to query. This index can be any local secondary index or global secondary index on the table. Note that if you use the IndexName parameter, you must also provide TableName. |
| SCAN_INDEX_FORWARD [=true|false] | Applicable when QUERY Mode is used. Determines the read consistency model: If set to true, then the operation uses strongly consistent reads; otherwise, the operation uses eventually consistent reads.Strongly consistent reads are not supported on global secondary indexes. If you query a global secondary index with ConsistentRead set to true, you will receive an error message. |
| CONSISTENT_READ | Applicable when QUERY Mode is used. Determines the read consistency model: If set to true, then the operation uses strongly consistent reads; otherwise, the operation uses eventually consistent reads.Strongly consistent reads are not supported on global secondary indexes. If you query a global secondary index with ConsistentRead set to true, you will receive an error message. |
select * from Orders where OrderDate > '2015-12-31T23:59:59.000Z' WITH(SCAN)Using variable in Date/Time query
select * from Orders where OrderDate > '{{User::vMyDate,yyyy-MM-ddTHH:mm:ssZ}}' WITH(SCAN)
Click here to see various ISO date formats
select * from mytable where SomeColumn > 55 WITH(SCAN)
select * from mytable where SomeColumn > 55 WITH(SCAN, LIMIT=1000)
select * from mytable where MyHashKeyColumn = 'XYZ' And MyRangeKey > '1223' WITH(QUERY)
select * from mytable where MyHashKeyColumn = 'XYZ' And MyRangeKey > '1223' and Phone LIKE '111-%' WITH(QUERY, INDEX=[index-phone-number])
Use brackets around identifier if it has space or dash or dot.
select * from mytable where MyHashKeyColumn = 'XYZ' And MyRangeKey > '1223' and Phone LIKE '111-%' WITH(QUERY, INDEX=[index-phone-number])
-- Selecting all columns
select * from CustomerTest
-- Selecting multiple columns
select CustomerID,CompanyName from CustomerTest
-- Selecting multiple columns, where some attribute name has space in it
select CustomerID,[Contact Title] from CustomerTest
-- Find records where City attribute is missing (Missing is different than actual NULL value {City: null, Id:1} -vs- {Id:1} )
select * from CustomerTest Where City IS NULL
-- Find records where City attribute is NULL (i.e. attribute is found but value is NULL e.g. {City: null, Id:1} )
select * from CustomerTest Where City = NULL
-- Using Where clause with simple condition, select all columns
select * from CustomerTest Where CustomerID='ALC3R'
-- Limiting records returned from query using TOP clause (Similar as LIMIT found in some RDBMS query engine)
select top 7 * from CustomerTest
-- Using OR
select * from CustomerTest Where CustomerID = 'ALFKI' OR CustomerID = 'BOLID'
-- Using AND
select
* from CustomerTest Where CustomerID = 'ALFKI' AND Age >
-- Using comparison operators
select * from CustomerTest Where CustomerID <> 'ALFKI'
select * from CustomerTest Where CustomerID != 'ALFKI'
select * from CustomerTest Where Age > 5
select * from CustomerTest Where Age >= 5
select * from CustomerTest Where Age < 5
select * from CustomerTest Wherepa Age papa=pa 5 AND CustomerID = 'C5'
|
How to Read / Write Amazon DynamoDB in SSISIntroduction In this article we will look at how to Read / Write Amazon DynamoDB in SSIS. ZappySys developed many AWS related components but in this article we will look at 3 Tasks/Components for DynamoDB Integration Scenarios (Read, Write, Update, Bulk Insert, Create / Drop Table etc.). We will discuss how to use SSIS DynamoDB Source Connector […] |
