How to send data from S3 to vertica using IAM ROLE?

0
votes

I have a requirement of moving data from aws s3 to vertica using aws roles.

I have created a table with admin user and created a policy with s3 full access , attached it to our vertica role and that in turn is attached vertica instances running on AWS.

VERTICA VERSION

Vertica Analytic Database v8.1.1-3

COMMAND:

COPY scratch.my_test SOURCE S3(url='https://s3.amazonaws.com/xxxx-xx/test.csv');

My Table:

Has only one column , name which is varchar(64)

My test.csv

name --> column

test --> this is my value

ERROR:

SQL Error [6085] [VP001]: [Vertica]VJDBC ERROR: Unexpected exception calling process() User Function in UDSource [Vertica]VJDBC ERROR: Unexpected exception calling process() User Function in UDSource com.vertica.util.ServerException: [Vertica]VJDBC ERROR: Unexpected exception calling process() User Function in UDSource

POLICY:

I also test with this policy , i got the same error.

{ "Version": "2012-10-17", "Statement": [ { "Sid": "VisualEditor0", "Effect": "Allow", "Action": "s3:", "Resource": "" }, { "Sid": "VisualEditor1", "Effect": "Allow", "Action": "s3:", "Resource": [ "arn:aws:s3:::xxx-xx", "arn:aws:s3:::xxx-xx/" ] } ] }

Someone please point where i am wrong, and also some suggestions. Thanks!

2
amazon-web-servicesamazon-s3vertica

2 Answers

2
votes

I don't know Vertica at all but the problem is likely to be that you have specified the URL of the S3 object incorrectly. You have asked Vertica to load from https://s3.amazonaws.com/xxxx-xx/test.csv which is an HTTP URL. There is no authentication in this request. It's just as if you hit the URL from a browser, and you would be denied access.

I believe that you should supply the URL as s3://xxxx-xx/test.csv.

I'm assuming that your IAM policy is correct and indicates arn:aws:s3:::xxxx-xx and arn:aws:s3:::xxxx-xx/* (note the trailing asterisk on the latter so that it applies to all objects under the xxxx-xx bucket).

0
votes

Thanks for taking time to check my issue, When i added the url you suggested , i got the following error 

SQL Error [6783] [VP001]: [Vertica][VJDBC](6783) ERROR: Filesystem does not support glob s3://xxx-xx/test.csv
  [Vertica][VJDBC](6783) ERROR: Filesystem does not support glob s3://xxx-xx/test.csv
    com.vertica.util.ServerException: [Vertica][VJDBC](6783) ERROR: Filesystem does not support glob s3://xxx-xx/test.csv

This helped me to find that, I should mount my s3 bucket into vertica

https://github.com/vertica/aws-lambda-vertica-loader#set-up-s3-bucket-mounts ( steps are here)

Discussion ( https://forum.vertica.com/discussion/205657/load-data-from-amazon-s3-bucket-to-vertica )