Digital Edition

SYS-CON.TV
Case Study: Integrating Redshift, DynamoDB and JasperSoft
JasperSoft and Redshift are touted as the new way to do data warehousing in the cloud

We recently completed a proof-of-concept (POC) that involved pulling data out of DynamoDB and into Redshift so that business users could analyze the data in an ad hoc manner with JasperSoft. JasperSoft and Redshift are touted as the new way to do data warehousing in the cloud and we were using DynamoDB as a sort of alternative to something Hadoop based.

We were amazed at how quickly and easily you could get a business-user friendly view of the data we had stored in DynamoDB using Redshift and JasperSoft. The actual human effort required to copy some initial data from DynamoDB into Redshift and then view it in JasperSoft was barely a few hours. However, there were a few unforseen technical challenges, but these challenges were not insurmountable. Ultimately we will continue with this technology combination because, as well as being easy to deploy and use, it gives us confidence that we can scale the POC into a "real" solution, and that our growing data needs will be taken care of.

Redshift's inbuilt copy from DynamoDB function makes getting data into Redshift fast but has limitations
Redshift provides an out-of-the-box copy function to copy data from DynamoDBinto Redshift without the need to set up servers or write any code other than a few simple lines of SQL. We were able to copy many of our tables straight out of DynamoDB and into Redshift and start running ad hoc queries without having to fire up servers or create an entire data translation layer of software.

We couldn't copy all of our tables, however, as DynamoDB's String Set field type is not currently supported by Redshift's copy function. After attempts at various SQL hacks and closely reading the Redshift manual we realized that we would not be able to work with these tables in the POC. In the future we will write some simple copy scripts (unless Amazon beats us to it and updates the copy command which, given their continuous product improvement, is likely).

Copying takes time
As it was a POC we were only copying across 3.5GB or 50 million rows of data but this process did take some time to complete - it took us 37 hours. Both Redshift and DynamoDB were running on the lowest performance settings and the DynamoDB instance was also servicing the needs of the live beta application we were trying to extract data from. We suspect this process could easily be made quicker by increasing the DynamoDB instance power and the power of Redshift but we did not test this.

The time it takes to copy 3.5GB of data indicated to us that a considered approach is necessary for getting data from DynamoDB into Redshift, especially considering that the live data will be much larger in volume. For example, when this goes into production we are only going to copy new records on a daily basis instead of clearing the entire Redshift database and reloading it to stay up to date.

Working in SQL with Redshift makes life easy
Once we had pulled our initial copy of data into Redshift we needed to manipulate the data to get it into a form that business users could analyze and create reports with.

The great thing about Redshift is that you are working in an environment you are familiar with. SQL. Redshift, at the time of writing, is based on PostgreSQL 8.0.2 so we were able to apply familiar string manipulation and math functions as well as create and join new tables to make the data much easier to understand for a non-technical business user.

Some SQL functions aren't yet supported by Redshift so we had to read through the documentation every now and then to find a suitable alternative. Sometimes it was just about trying to find the alternate name Redshift was using for a function we were used to using. Other times it meant creating some interesting workaround SQL. For example, Redshift doesn't support a function that can convert a Unix timestamp to a date so we had to manually convert our time stamps to dates using a mathematical formula.

Instant, non-technical user friendly data access with JasperSoft
We easily spun up a JasperSoft OnDemand instance and connected it to Redshift quite quickly. We were then creating ad hoc views and reports in a matter of minutes.

We did have some issues analyzing one of our tables straight out-of-the-box, one table had almost 3.5GB. Attempting to view reports on this table led to JasperSoft crashing. With some tweaking of the way the reports ran we were able to prevent analysis of this table from crashing JasperSoft.

About Scott Middleton
Scott Middleton is the CEO and Principal Consultant at Terem Technologies, a company that specializes in custom software development for innovative companies and high-tech ventures.

In order to post a comment you need to be registered and logged in.

Register | Sign-in

Reader Feedback: Page 1 of 1

I was wondering if Redshift can be used to load JSON Objects to run a noSQL query ?




ADS BY GOOGLE
Subscribe to the World's Most Powerful Newsletters

ADS BY GOOGLE

The explosion of new web/cloud/IoT-based applications and the data they generate are transforming ou...
CI/CD is conceptually straightforward, yet often technically intricate to implement since it require...
Containers and Kubernetes allow for code portability across on-premise VMs, bare metal, or multiple ...
Enterprises are striving to become digital businesses for differentiated innovation and customer-cen...
Digital Transformation: Preparing Cloud & IoT Security for the Age of Artificial Intelligence. As au...
DevOps is often described as a combination of technology and culture. Without both, DevOps isn't com...
The now mainstream platform changes stemming from the first Internet boom brought many changes but d...
DXWorldEXPO LLC announced today that All in Mobile, a mobile app development company from Poland, wi...
DXWorldEXPO LLC announced today that Ed Featherston has been named the "Tech Chair" of "FinTechEXPO ...
Chris Matthieu is the President & CEO of Computes, inc. He brings 30 years of experience in developm...
Bill Schmarzo, author of "Big Data: Understanding How Data Powers Big Business" and "Big Data MBA: D...
Andi Mann, Chief Technology Advocate at Splunk, is an accomplished digital business executive with e...
In this presentation, you will learn first hand what works and what doesn't while architecting and d...
The Internet of Things is clearly many things: data collection and analytics, wearables, Smart Grids...
To Really Work for Enterprises, MultiCloud Adoption Requires Far Better and Inclusive Cloud Monitori...
We are seeing a major migration of enterprises applications to the cloud. As cloud and business use ...
If your cloud deployment is on AWS with predictable workloads, Reserved Instances (RIs) can provide ...
Disruption, Innovation, Artificial Intelligence and Machine Learning, Leadership and Management hear...
We build IoT infrastructure products - when you have to integrate different devices, different syste...
Consumer-driven contracts are an essential part of a mature microservice testing portfolio enabling ...