Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.)

share|improve this question
    
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. –  mdahlman Aug 8 '13 at 16:03

1 Answer 1

up vote 2 down vote accepted

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
share|improve this answer
    
I like this! "FuseOverAmazon" seems very interesting, I had not thought of this part. I already had that pipe idea in mind, so it's great to see your confirmation of this method. –  mdahlman Jul 25 '13 at 19:17
    
Post your experiments results after you try. It can be quite useful with today's trends. –  Mindaugas Riauba Jul 26 '13 at 10:27
    
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. –  mdahlman Dec 4 '13 at 23:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.