How to work with Amazon DynamoDB Data in Apache Spark using SQL

Apache Spark is a fast and general engine for large-scale data processing. When paired with the CData JDBC Driver for Amazon DynamoDB, Spark can work with live Amazon DynamoDB data. This article describes how to connect to and query Amazon DynamoDB data from a Spark shell.

The CData JDBC Driver offers unmatched performance for interacting with live Amazon DynamoDB data due to optimized data processing built into the driver. When you issue complex SQL queries to Amazon DynamoDB, the driver pushes supported SQL operations, like filters and aggregations, directly to Amazon DynamoDB and utilizes the embedded SQL engine to process unsupported operations (often SQL functions and JOIN operations) client-side. With built-in dynamic metadata querying, you can work with and analyze Amazon DynamoDB data using native data types.

Install the CData JDBC Driver for Amazon DynamoDB

Download the CData JDBC Driver for Amazon DynamoDB installer, unzip the package, and run the JAR file to install the driver.

Start a Spark Shell and Connect to Amazon DynamoDB Data

1. Open a terminal and start the Spark shell with the CData JDBC Driver for Amazon DynamoDB JAR file as the jars parameter: $ spark-shell --jars /CData/CData JDBC Driver for Amazon DynamoDB/lib/cdata.jdbc.amazondynamodb.jar
2. With the shell running, you can connect to Amazon DynamoDB with a JDBC URL and use the SQL Context load() function to read a table.

   The connection to Amazon DynamoDB is made using your AccessKey, SecretKey, and optionally your Domain and Region. Your AccessKey and SecretKey can be obtained on the security credentials page for your Amazon Web Services account. Your Region will be displayed in the upper left-hand corner when you are logged into DynamoDB.

   Built-in Connection String Designer

   For assistance in constructing the JDBC URL, use the connection string designer built into the Amazon DynamoDB JDBC Driver. Either double-click the JAR file or execute the jar file from the command-line.

   java -jar cdata.jdbc.amazondynamodb.jar

   Fill in the connection properties and copy the connection string to the clipboard.

   Configure the connection to Amazon DynamoDB, using the connection string generated above.

   scala> val amazondynamodb_df = spark.sqlContext.read.format("jdbc").option("url", "jdbc:amazondynamodb:Access Key=xxx;Secret Key=xxx;Domain=amazonaws.com;Region=OREGON;").option("dbtable","Lead").option("driver","cdata.jdbc.amazondynamodb.AmazonDynamoDBDriver").load()
3. Once you connect and the data is loaded you will see the table schema displayed.

4. Register the Amazon DynamoDB data as a temporary table:

   scala> amazondynamodb_df.registerTable("lead")

5. Perform custom SQL queries against the Data using commands like the one below:

   scala> amazondynamodb_df.sqlContext.sql("SELECT Industry, Revenue FROM Lead WHERE FirstName = Bob").collect.foreach(println)

   You will see the results displayed in the console, similar to the following:

Using the CData JDBC Driver for Amazon DynamoDB in Apache Spark, you are able to perform fast and complex analytics on Amazon DynamoDB data, combining the power and utility of Spark with your data. Download a free, 30 day trial of any of the 200+ CData JDBC Drivers and get started today.