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

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
  • 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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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