how to resolve syntax error using copy command from s3 to redshift db

Question

Syntax error in copy command from s3 to redshift

I can connect to the the redshift database but get syntax error at tablename of copy command. I'm using a csv file in s3 to schema.tblname in redshift

COPY ###_schema.tblName_csv
FROM 's3://####/###/Filename.csv'
CREDENTIALS 'aws_iam_role=arn:aws:iam::######'
DATEFORMAT 'auto'
IGNOREHEADER 1
DELIMITER ','
IGNOREBLANKLINES;

I'd like to be able to select * from this tbl after loading data

it says code 8001, user not authorized to assume IAM Role. I have given it the IAM provided to me and my own IAM credentials — CelestialSky
I suspect that the role is not associated with your Redshift Cluster - have a look here to see how to associate it docs.aws.amazon.com/redshift/latest/mgmt/… — botchniaque
What do you mean by and my own credentials? You should provide either a role, or credentials. — botchniaque
from above credential line in the copy command given to me arn:aws:iam::randomKey>:user/james.cole@<companyName>.com 'aws_iam_role=arn:aws:iam::<randomKey:role/<clusterName>-redshift-role'(provided) I have an IAM role but in the copy command he provided something else which might be the problem. At the start I set boto3.client to equal 'Redshift' to make the connection which I verify is true, when copying do i need another "Boto3.client" pointing to s3? — CelestialSky

botchniaque botchniaque · Accepted Answer · 2019-09-09T20:55:53

Redshift COPY command will require some way to get s3 permissions. You can

provide that using either an IAM role (role has to associated with your cluster),

copy customer from 's3://mybucket/mydata'  
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole';

provide AWS access key and secret key

copy table-name
from 's3://objectpath'
ACCESS_KEY_ID '<access-key-id>'
SECRET_ACCESS_KEY '<secret-access-key>';

provide temporary credentials from sts service

copy table-name
from 's3://objectpath'
access_key_id '<temporary-access-key-id>'
secret_access_key '<temporary-secret-access-key>
token '<temporary-token>';

Redshift Manual reference

how to resolve syntax error using copy command from s3 to redshift db

1 Answers