Data stores compatible with Amazon EMR

There are a number of different file systems that can be used

1. Hadoop Distributed File System (HDFS) : EC2 local/ephemeral disk is where HDFS  resides.  The obvious disadvantage is that it’s ephemeral storage which is reclaimed when the cluster ends. It can be used for caching the results produced by intermediate job-flow steps during a large EMR job.
2. Local (ephemeral) EC2 disk :  Each EMR node comes with local disk.  This disk works well for temporary storage of data that is continually changing, such as buffers, caches, scratch data, and other temporary content.
3. S3 native : Used for input (data set to be reduced) and output/results.
4. S3 block : Stay away from as not as performant as the other options.
5. HBase : HBase is an open source, non-relational, distributed database that runs on top of HDFS.  HBase works with Hadoop/EMR, sharing its file system and serving as a direct input and output to EMR jobs. HBase also integrates with Apache Hive, enabling SQL-like queries over HBase tables, joins with Hive-based tables, and support for Java Database Connectivity (JDBC).

More information here:
http://docs.aws.amazon.com/ElasticMapReduce/latest/DeveloperGuide/emr-plan-file-systems.html

Process to load Oracle data from on premise to RDS

RDS does not allow access to the native OS.  However, using Oracle-managed files you can create a local directory that Oracle RDS can access.  Here are the steps:

Local DB configuration

Local data pump directory

Here I run the export from my local DB

Now I go and update the Perl script with the Dump file name. UTL_FILE package takes care of copying file. It took 3 minutes for 250MB file

Login to RDS database

Ran the import

And here we are – Schema DEEP is present in RDS database TESTDB now.

The blog post adds more detail to this blog post: http://cloudconclave.blogspot.com/2013/04/importing-on-premise-oracle-database.html

Here is the Perl code:

#!/usr/bin/perl -w

use strict;

use DBI;

# RDS instance info

my $RDS_PORT=4080;

my $RDS_HOST="myrdshost.xxx.us-east-1-devo.rds-dev.amazonaws.com";

my $RDS_LOGIN="orauser/orapwd";

my $RDS_SID="myoradb";

my $SQL_INTEGER         =  4;

my $SQL_VARCHAR         = 12;

my $SQL_LONGRAW         = 24;

# Oracle destination directory and file name (these could become parameters)

my $dirname = "DATA_PUMP_DIR";

my $fname   = "test.bin";

my $data  = "test";

my $chunk = 8192;

my $sql_open   = "BEGIN perl_global.fh  := utl_file.fopen(:dirname, :fname, 'wb', :chunk); END;";

my $sql_write  = "BEGIN utl_file.put_raw(perl_global.fh, :data, true); END;";

my $sql_close  = "BEGIN utl_file.fclose(perl_global.fh); END;";

my $sql_global = "create or replace package perl_global as fh utl_file.file_type; end;";

my $conn = DBI->connect('dbi:Oracle:host='.$RDS_HOST.';sid='.$RDS_SID.';port='.$RDS_PORT,$RDS_LOGIN, '')

|| die ( $DBI::errstr . "\n") ;

# create a package just to have a global variable

my $updated=$conn->do($sql_global);

# open the file for writing

my $stmt = $conn->prepare ($sql_open);

$stmt->bind_param_inout(":dirname", \$dirname, $SQL_VARCHAR);

$stmt->bind_param_inout(":fname", \$fname, $SQL_VARCHAR);

$stmt->bind_param_inout(":chunk", \$chunk, $SQL_INTEGER);

$stmt->execute() || die ( $DBI::errstr . "\n");

open (INF, $fname) ||  die "\nCan't open $fname for reading: $!\n";

binmode(INF);

$stmt = $conn->prepare ($sql_write);

my %attrib = ('ora_type',$SQL_LONGRAW);

my $val=1;

while ($val > 0) {

  $val = read (INF, $data, $chunk);

  $stmt->bind_param(":data", $data , \%attrib);

  $stmt->execute() || die ( $DBI::errstr . "\n") ;

};

die "Problem copying: $!\n" if $!;

close INF || die "Can't close $fname: $!\n";

$stmt = $conn->prepare ($sql_close);

$stmt->execute() || die ( $DBI::errstr . "\n") ;