Accessing NoSQL Tables
The adapter implements Automatic Schema Discovery that is highly configurable. The following sections outline the adapter's defaults and link to ways to further customize.
Flattening Nested JSON
By default, the adapter projects columns over the properties of objects. Arrays are returned as JSON strings, by default. You can use the following properties to access array elements, including objects nested in arrays.
- FlattenArrays: Set this property to the number of array elements that you want to return as column values. You can also use this property with FlattenObjects to extract the properties of objects nested in arrays.
FlattenObjects: By default, this is true; that is, the properties of objects and nested objects are returned as columns. When you set FlattenArrays, objects nested in the specified array elements are also flattened and returned as columns.
Other mechanisms for accessing nested objects are detailed in NoSQL Database.
Inferring the Data Type
You can use the following properties to configure automatic data type detection, which is enabled by default.
- TypeDetectionScheme: You can use this property to enable or disable automatic type detection based on the value specified in RowScanDepth.
- RowScanDepth: This property determines the number of rows that will be scanned to determine column data types.
Fine Tuning Data Access
You can use the following properties to gain greater control over Amazon DynamoDB API features and the strategies the adapter uses to surface them:
GenerateSchemaFiles: This property enables you to persist table metadata in static schema files that are easy to customize, to change column data types, for example. You can set this property to "OnStart" to generate schema files for all tables in your database at connection. Or, you can generate schemas as you execute SELECT queries to tables. The resulting schemas are based on the connection properties you use to configure Automatic Schema Discovery.
- NumberColumnMode: This property enables you to specify an SQL data type for the Amazon DynamoDB "number" type, such as double, decimal, or string.
InsertMode: Inserts in Amazon DynamoDB have several key differences from SQL inserts. An insert in Amazon DynamoDB is an upsert; that is, a row that already exists is replaced by the incoming row. Also, the primary key must be specified.
By default, the adapter will perform an upsert based on the primary key. You can use this connection property to throw an error instead of overwriting existing records.
To use the resulting schema files, set the Location property to the folder containing the schemas.
UseSimpleNames: Amazon DynamoDB supports attribute names with special characters that many database-oriented tools do not support.
In addition, Amazon DynamoDB table names can include dots and dashes -- the adapter interprets dots within table names as hierarchy separators that enable you to drill down to nested fields, similar to XPath.
You can use this property to replace any nonalphanumeric character with an underscore.
- SeparatorCharacter: You can use this property to more easily access nested fields when Querying Documents and Lists; specify the hierarchy separator with this property. By default, this character is the '.' (dot) character.
Fine Tuning Performance
You can use the following connection properties to avoid "maximum throughput exceeded" errors.
Using Paging Effectively
You can use the Pagesize property to optimize use of your provisioned throughput, based on the size of your items and Amazon DynamoDB's 1MB page size. Set this property to the number of items to return.
Generally, a smaller page size reduces spikes in throughput that cause throttling. A smaller page size also inserts pauses between requests. This interval evens out the distribution of requests and allows more requests to be successful by avoiding throttling.
You can also take advantage of secondary indexes by specifying them in the WHERE clause.
You can specify partition keys and sort keys in the WHERE clause to improve the performance of query operations. Based on these inputs, the adapter automatically detects the secondary index for a query operation.
See Querying The Data for more information on scan operations vs. query operations.
Setting a Retry Interval
You can set the following properties to retry queries instead of throwing a temporary error such as "maximum throughput exceeded":
- RetryWaitTime: The minimum number of milliseconds the adapter will wait to retry a request.
- MaximumRequestRetries: The maximum number of times to retry a request.
Connecting Through a Firewall or Proxy
To connect through the Windows system proxy, set only the Amazon DynamoDB authentication properties and, if necessary, AWSRegion and Domain. To connect to other proxies, set ProxyAutoDetect to false and in addition set the following.
To authenticate to an HTTP proxy, set ProxyAuthScheme, ProxyUser, and ProxyPassword, in addition to ProxyServer and ProxyPort.
To connect to other proxies, set FirewallType, FirewallServer, and FirewallPort. To tunnel the connection, set FirewallType to TUNNEL. To authenticate to a SOCKS proxy, set FirewallType to SOCKS5. Additionally, specify FirewallUser and FirewallPassword.
Troubleshooting the Connection
To show adapter activity from query execution to HTTP calls, use Logfile and Verbosity. The examples of common connection errors below show how to use these properties to get more context. Contact the support team for help tracing the source of an error or circumventing a performance issue.
- Authentication errors: Typically, recording a Logfile at Verbosity 4 is necessary to get full details on an authentication error.
- Queries time out: A server that takes too long to respond will exceed the adapter's client-side timeout. Often, setting the Timeout property to a higher value will avoid a connection error. Another option is to disable the timeout by setting the property to 0. Setting Verbosity to 2 will show where the time is being spent.