BOOYAH: Amazon Redshift Challenge loaded in 8 min #OOW14

[Sumit Sarkar]

This challenge was set forth on Sept-12 to the public (read article) to perform a 1M record load LIVE at an Oracle OpenWorld session on Oct-2 using Oracle Data Integrator titled, Oracle Data Integration: A Crucial Ingredient for Cloud Integration [CON7926]. The operation takes ~6 hours with an open source Postgres JDBC driver v8.4 and there was no proof this was even possible to do.

Amazon Redshift Challenge Parameters

• Load 1 Million Records using JDBC into Amazon Redshift from Oracle Data Integrator 12c
• Direct JDBC connection without staging data in S3 bucket or DynamoDB
• Finish within the 45 minute session at Oracle OpenWorld
• To keep it real, work tables will be created in Redshift including light transformations to show ODI’s ELT capabilities (used an UPPER expression on a character column)
• Source data is in an Oracle 11g Database using sample SUPPLIER table from Amazon

How’d it go? Done in 8 minutes and some change (down from ~6 hours)


Here’s the play by play which came down to the wire, and we ended up doing a little SaaS Cloud integration with Salesforce as well with the unexpected extra time

Hacking away with @brodymessmer at Duke’s cafe at #oow14 for the Amazon Redshift challenge in < 48 hours #oow14 pic.twitter.com/mFg2lSuIde

— Sumit Sarkar (@SAsInSumit) September 30, 2014
​

(Probably should have skipped the beers in hindsight)

All eyes on Moscone South as the @awscloud redshift challenge from ODI begins at 9:30am in rm 270 #oow14 pic.twitter.com/zzmBRxbXaK — Sumit Sarkar (@SAsInSumit) October 2, 2014
​

Yikes! Redshift challenge by @SAsInSumit at #OOW14 to load 1M rows via ODI/JDBC WITHOUT S3 buckets http://t.co/SsIZpBNQ6c

— Russell Rothstein (@RussRothsteinIT) September 29, 2014
​

It’s getting interesting. @SAsInSumit kicking off 1 mn record load LIVE. #oow14 #odi12c #cloud — Madhu Nair (@nmadhu2k3) October 2, 2014
​

And it worked over the #OOW14 wifi! @SAsInSumit #ODI12c #DataDirect — Michael Rainey (@mRainey) October 2, 2014
​

Live demo for extracting data from #Salesforce using #ODI12c and DataDirect @SAsInSumit @JulienTestut pic.twitter.com/ku8QTvptEv — Irem Radzik (@Irem_Radzik) October 2, 2014


Great to see this formalised – ODI12c loading and extracting Amazon Redshift and http://t.co/zdG8NMNC5b data #oow14 pic.twitter.com/CFvgYcKFAF

— Mark Rittman (@markrittman) October 2, 2014
​

Sent a tweet with the results to the Amazon Redshift product team following the action from Seattle just after the session.

@SAsInSumit @rahulpathak @awscloud @ProgressSW awesome — David Allan (@i_m_dave) October 2, 2014
​

And all your chatter made it to Larry Carvalho from IDC:

@robustcloud Great meeting you and you meant DataDirect did it under 10 min

Cant wait to try the chai cart on market st next week #PRGS14

— Sumit Sarkar (@SAsInSumit) October 8, 2014
​

503 seconds to be exact on Moscone Center Conference wifi


ODI Studio 12c showing session results for 1,000,000 row insert (total of 2,000,000 processed with ELT)


ODI Studio 12c showing mapping with ELT expression staging the 1M rows




Key Takeaways

• Oracle Data Integrator (ODI) is a flexible data integration platform that works well across heterogenous environments and efficiently loads data using the JDBC API which enables our drivers, including the DataDirect Redshift JDBC, to take performance to the next level.
• I was never worried since I was backed by the finest data connectivity R&D group in the universe and they do stuff like this every day.
• If we pulled this off, @JulienTestut, agreed to buy everyone in attendance a $5 cup of Philz Coffee. Please tweet at him to coordinate your winnings
  
• If you missed it, please check back for our follow-up webcast hosted by Oracle later this month.

@rahulpathak @SAsInSumit @awscloud @ProgressSW it wasn’t recorded but we are planning to do a webcast later this month — Julien Testut (@JulienTestut) October 2, 2014
​

Take the challenge?


Download the DataDirect Amazon Redshift ODBC/JDBC driver and tell us you want to take the challenge so we can deliver you the latest patch since it may still be in QA. If you’re not running Oracle Data Integrator, then give it a try against your data integration platform of choice: SSIS, IBM DataStage, Informatica PowerCenter, Ab Initio, SAP Data Service, Pentaho Data Integrator, Talend, Qlikview Expressor, SAS ETL, Pervasive Data Integrator, etc.

Continue reading...