Configuring Connections - AWS DynamoDB with the CData JDBC Driver – Customer Support

AWS DynamoDB is a prominent NOSQL database that is designed to automatically scale (to handle large, growing sets of data) while offering high performance. DynamoDB is commonly a component in Data Warehouse and Big Data ETL/LT data flows, and teams regularly use QuerySurge to test these flows. CData offers a JDBC driver for DynamoDB, which QuerySurge can utilize to execute data tests. This article walks you through the steps of configuring the CData JDBC driver for DynamoDB and adding a QuerySurge Connection to DynamoDB in QuerySurge using this driver.

Note: RTTS, the vendor of QuerySurge, partners with CData to make a broad range of JDBC drivers available to QuerySurge users. For information about our partnership, click here. See all of CData's JDBC offerings here. For questions related to ordering, contact us here.

Download and deploy the CData JDBC DynamoDB Driver

Download the driver from CData here.
Install the driver using setup.jar, included in the download directory. Instructions on how to run setup.jar can be found in the README which is in the download directory.
Deploy the driver to the QuerySurge Agent.
1. Stop the QuerySurge Agent service. (Instructions to do this can be found here.)
2. Copy the cdata.jdbc.amazondynamodb.jar file along with the cdata.jdbc.amazondynamodb.lic license file (found in the lib/ sub-folder of the driver installation directory) into the Agent jdbc/ directory. (Instructions to do so can be found here for Windows, and here for Linux.)
3. Start the QuerySurge Agent service.

Important JDBC Driver Configuration options

Because DynamoDB is a schema-less database, it doesn't adhere to many of the rules of traditional relational databases. There are certain driver-based configuration options that are worth considering in order to ensure that your data is handled best for use with QuerySurge. These configurations are determined by connection URL properties, which can be built using the driver's included Connection URL Builder Application, or by manually modifying the JDBC URL.

Settings that are especially important to consider for QuerySurge are:

Flatten Objects
The default behavior is to treat each entry in a DynamoDB Map Data Type as a new column in the results set. You can change this with the following Connection URL property:
```
Flatten Objects="True|False";
```
Generate Schema Files
A driver schema file can be used by the the CData DynamoDB JDBC driver to describe how your non-relational data should be treated as tables, rows, and columns, by mapping your DynamoDB data structures into common SQL tables and data types.

With the CData DynamoDB JDBC driver, you have a few options as to how to go about schema generation.
1. Dynamically retrieve DynamoDB Data (No Schema)
  This option is the simplest. If you choose this route, the driver will read whatever it finds in your DynamoDB tables, following no schema or type detection rules. All Columns will be returned as string type (by default, 2000 characters maximum) .
  
  In order to configure your driver like this, set the following properties as so, and append them to your connection URL
```
Generate Schema Files="Never";Location="";AutoCache="False";
```
2. Automatically Generate a Schema File
  With this option, you can either have a schema file generated for every table in your database at the time the connection is made, or you can have schema generated for just those tables queried only at the time a query executes. To use the first option (a schema file generated for every table on connection), include the following property in your connection URL
```
Generate Schema Files="OnStart";
```
  For the second option (a schema for just those tables queried only at the time the query executes), set this property to "OnUse", like so:
```
Generate Schema Files="OnUse";
```
  For both options, it is also necessary to include the following property:
```
Location="Path/to/Schema File Directory"
```
  Note: The path specified in this property is a relative path, starting from: \QuerySurge Install Dir\agent\
  
  Note: If you manually edit the schema, and you want that change to be reflected in the result set in QuerySurge, you will have to restart the QuerySurge Agent service.
  
  Note: If you would like to have a new schema file generated, you will have to delete the existing one.
3. Manually Generate a Schema File
  This option allows you to manually create your own custom schema file. CData provides a sample Schema file here. To see how to create one yourself, we recommend that you use the Generate Schema Files connection URL property to have one generated that you can edit.

Build the Connection URL

The CData DynamoDB JDBC driver contains a connection URL builder tool. You can use the built-in tool, or you can manually construct your own connection URL. Both options are described below.

To Use the Connection URL Builder Tool

Open the connection URL builder by manually running the .jar file using the following command:

For Windows:

java -jar 'C:\Program Files\CData\CData JDBC Driver for Amazon DynamoDB 2019\lib\cdata.jdbc.amazondynamodb.jar'

For Linux:

java -jar cdata.jdbc.amazondynamodb.jar

In the tool, choose from the list of connection URL property options and enter the relevant values. The Connection URL Builder will automatically add the properties and their values to the URL.
(Optional) Click Test Connection.
Copy the Connection URL and paste it into your text editor, to use when you set up the Connection in QuerySurge.

To Build a Connection URL Manually

Use the following template to construct your connection URL (Options for Other Properties can be found here):

jdbc:amazondynamodb:Access Key=xxx;Secret Key=xxx;Other Properties=xxx

Add a DynamoDB Connection to QuerySurge

Navigate to the Connection Wizard by clicking the Administration menu the and choosing Add Connection in the submenu list.
In the Wizard, enter a name for your Connection.
In the Data Source dropdown menu, choose All Other JDBC Connections (Connection Extensibility).
For the Driver Class, enter the following, and click Next.
```
cdata.jdbc.amazondynamodb.AmazonDynamoDBDriver
```
Enter JDBC URL in the Wizard and click Next. When you've entered your information, the Connection Wizard will look similar to this:

Note: Your Username and Password are not necessary using the setup described here. Different Authentication mechanisms for DynamoDB are discussed here.
(Optional) Enter a Test Query. A test query should be a standard query that returns a small amount of information - one row/one column is enough. Make sure that your login has permission to query the table that you use. Click Next.
(Optional) If you entered a Test Query, you can use the Test Connection button to test whether your Connection is set up properly:
Click Save.

Once you have successfully set up the Connection, you will be able to write SQL queries against your DynamoDB tables. The CData DynamoDB JDBC driver documentation discusses supported SQL syntax and functions.

Articles in this section