Configuring Connections - AWS DynamoDB With Simba Driver

Versions: 8.0+

AWS DynamoDB is a prominent NOSQL database that is designed for automatic scaling to handle large, growing sets of data while offering excellent 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. Simba offers a JDBC driver for DynamoDB, which QuerySurge can utilize to execute data tests. This article walks you through the steps of configuring the Simba JDBC driver for DynamoDB and adding a QuerySurge Connection to DynamoDB in QuerySurge using this driver.

Note: RTTS, the vendor of QuerySurge, does not have distribution rights for this driver, and has no relationship with the driver vendor. If you download a driver, your license for the driver is with the driver vendor and not with RTTS. Support for this driver is not provided by RTTS.

Download and deploy the Simba JDBC DynamoDB Driver

1. Download the driver from this link.

2. After downloading the driver, extract the files and deploy the driver. 

   1. Stop the QuerySurge Agent service. (Instructions to do this can be found here.)
   2. Copy the DynamoDBJDBC<version number>.jar file (found in the libs/ sub-folder of the driver 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.

   Note: If you received a licence from Simba through email, you must copy the license file to the Agent jdbc/ directory as well.

Build the Connection URL

Use the following template to construct your connection URL:

jdbc:dynamodb:[Property1]=[Value];[Property2]=[Value];...

Detailed instructions and examples for building a connection URL for this driver can be found here. 

Driver properties can be found here.

 Note: The LocalMetadataFile and MetadataDatabaseTable properties can be omitted at this point in your setup. These properties pertain to a schema file, which can be created in the following step.

Important Driver Re-Normalization Behavior

Because DynamoDB is a schema-less database, it does not adhere to many of the rules of traditional relational databases. The Simba DynamoDB JDBC Driver, therefore, has built-in mechanisms for handling DynamoDB data types that do not have clear corresponding SQL data types; namely, Lists, Sets, and Maps.

• Maps - Detailed information and examples of how DynamoDB Map data types are handled can be found here.
• Lists and Sets - Detailed information and examples of how DynamoDb List and Set data types are handled can be found here.
• Other Data Types - Detailed information on how other DynamoDB data types are converted to SQL data types can be found here.

Generate a Schema File

A driver schema file can be used by the the Simba 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.

The Simba DynamoDB JDBC Driver gives you the option of storing your schema file locally in a JSON file, or in a DynamoDB table.

Follow instructions here to open the included Schema Editor Tool and create a schema definition.

 Note:  It is not required to define a schema. However, if you don't, the Simba DynamoDB JDBC Driver will default to data types and column sizes that may not be ideal for your needs. Additionally, when the driver connects to a database without a specified schema definition, it automatically generates a temporary schema definition. However, temporary schema definitions do not persist after the connection is closed, and the driver may generate different schema definitions during subsequent connections to the same database.

 Note: After creating your schema definition, you can now add the LocalMetadataFile property (if you chose to store your schema definition in a local JSON file), or the MetadataDatabaseTable property (if you chose to store your schema definition in a DynamoDB table).

Add the Connection

1. Navigate to the Connection Wizard by clicking the Administration menu the and choosing Add Connection in the submenu list.
2. In the Wizard, enter a name for your Connection.
3. In the Data Source dropdown menu, choose All Other JDBC Connections (Connection Extensibility).
    

4. For the Driver Class, enter the following, and click Next.

   com.simba.dynamodb.jdbc.Driver

5. Enter JDBC URL in the Wizard and click Next.  When you've entered your information, the Connection Wizard will look similar to this: 

   
   

     Note: A Username and Password are not necessary in this case, as the Connection URL contains keys.
   
   Authentication for DynamoDB with the Simba DynamoDB JDBC Driver is discussed here.
   Different connection URL mechanisms for authentication are discussed here.

6. (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.

7. (Optional) If you entered a Test Query, you can use the Test Connection button to test whether your Connection is set up properly:

   
   
8. Click Save to save your Connection.

Once you have successfully set up the Connection, you will be able to write SQL queries against your DynamoDB tables.