3

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

Improve this question
1
  • 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
    Commented Aug 8, 2013 at 16:03

1 Answer 1

Reset to default
3

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
Improve this answer
1
  • 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
    Commented Dec 4, 2013 at 23:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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