COPY data from S3 using IAM_ROLE

rkg112

Hi, I had an IAM_ROLE setup where it has access to S3. I am testing the COPY command from my local vagrant image where Vertica is running. I want to connect to S3 via IAM_ROLE instead of setting up the AWS_AUTH and session tokens. I tried this on Redshift using the IAM_ROLE to copy files from s3 to Redshift. It worked. But I can't found proper documentation to use IAM_ROLE with COPY command. Is there any solution for this?

dbadmin=> COPY power FROM 's3://test/' ON ANY NODE IAM_ROLE='arn:aws:iam::xxxxxx:role/vertica_s3' PARQUET;
ERROR 4856: Syntax error at or near "IAM_ROLE" at character 55
LINE 1: ... power FROM 's3://test/' ON ANY NODE IAM_ROLE='...

sahil_kumar

You can follow this link-> https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/BulkLoadCOPY/SpecifyingCOPYFROMOptions.htm

SruthiA

You need to use ALTER SESSION in order to set AWSAuth and then run the COPY statement. There is no parameter named IAM_ROLE for COPY. Please find the below link which has info regarding parameters that can be passed for COPY statement

https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Statements/COPY/COPYParameters.htm

https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/BulkLoadCOPY/AWSLibrary/configuringVerticaAWSLibrary.htm

rkg112

Hi, I got what you mentioned. But, is there a way that I can specify IAM_ROLE in Copy command without explicitly setting AWSAuth and all?

SruthiA

No.. there is no way to specify the IAM_ROLE in the COPY command.