Example of loading into RDS from S3 using SQL*Loader

Question

Does anyone have a working sample of loading data into an Amazon AWS RDS Oracle database using SQL*Loader where the source file is on S3?

In principle it's clearly possible. The Amazon RDS Data Import Guide for Oracle documents using sqlldr. But the only sample there loads data from a local EC2 instance.

I have worked with RDS and with S3 and with SQL*Loader before, but I haven't worked with all 3 together. One sample would be really useful. (Related advice would be welcome too... but a sample is all I'm really looking for.)

• mdahlman: Though Amazon documents using sqlldr (a little bit), it doesn't document installing it at all. I found the process non-trivial, so I documented installing SQL*Loader on Amazon Linux.

Answer 1

I thought of two ideas how you could (at least theoretically) access the data if you are on Linux.

• mount S3 bucket using some kind of S3 FUSE. E.g. first hit on Google https://code.google.com/p/s3fs/wiki/FuseOverAmazon
• create named pipe: mkfifo oraclepipe then configure SQL*Loader to read from the pipe probably using conventional path. And feed S3 file to the pipe: wget -O oraclepipe http://s3.com/hash.

We were using the second method on 9i to compress export online. And I do not recall problems with this approach. We ran two sessions from the shell script:

 gzip < oraclepipe > database.exp.gz &
 exp "username/passwd" FULL=y BUFFER=2048000 FILE=oraclepipe

• mdahlman: For what it's worth... in the end we needed to process the files a bit. So I simply pulled the files from S3, tweaked them, then loaded them into RDS. The retrieval is fast enough that the potential gains of FuseOverAmazon or a pipe are really quite small for us.