DataDirect Redshift Drivers: Million Row Challenge

Can DataDirect Improve Data Performance?

The challenge? Load one million rows of data into Amazon Redshift, a process that usually takes six hours, in less than one. All using my usual tools and no help from an Amazon S3 bucket. That was the challenge I accepted last October. I can’t say that it wasn’t a little bit daunting.

The reason for this challenge is simple: Until now, the only way to load data into Redshift was to use Amazon S3 buckets. Loading data into Redshift is an isolated, time-consuming and frustrating process, outside of usual workflows. This inefficiency just won’t cut it in today’s performance-driven world. I wanted to prove that this process could be much faster than people realize and easily integrated into your daily workflow.

Bulk-Loading Data at Lightning Speed

So, how did we do? Pretty well, I’d say. Using Progress® DataDirect® drivers, we were able to cut the time to load one million rows of data from six hours down to only eight minutes.

Your Step-by-Step Instructions

It really is as simple as downloading our Progress® DataDirect® Amazon Redshift ODBC driver or JDBC driver. After some minor reconfiguration on Redshift, you are ready to bulk upload data at lightning speeds. In my demo, I used Oracle Data Integrator, but the drivers I used are compatible with many more tools including:

Build a basic workflow to load data from your CSV file into Amazon Redshift using the DataDirect driver.

Run workflow.

The Results Are In

The following images are sample results using Microsoft SQL Server Integration Services 2012 (SSIS) and finishing in less than 10 minutes compared to six hours with the open source Postgres ODBC driver.

Sumit Sarkar is a Chief Data Evangelist at Progress, with over 10 years experience working in the data connectivity field. The world's leading consultant on open data standards connectivity with cloud data, Sumit's interests include performance tuning of the data access layer for which he has developed a patent pending technology for its analysis; business intelligence and data warehousing for SaaS platforms; and data connectivity for aPaaS environments, with a focus on standards such as ODBC, JDBC, ADO.NET and ODATA. He is an IBM Certified Consultant for IBM Cognos Business Intelligence and TDWI member. He has presented sessions on data connectivity at various conferences including Dreamforce, Oracle OpenWorld, Strata Hadoop, MongoDB World and SAP Analytics and Business Objects Conference, among many others.

Progress, Telerik, and certain product names used herein are trademarks or registered trademarks of Progress Software Corporation and/or one of its subsidiaries or affiliates in the U.S. and/or other countries. See Trademarks for appropriate markings.