Copy error Amazon Redshift loading from S3

Very new to AWS and RedShift, so please go slow.
I'm trying to figure out how to load data from a csv file I uploaded to S3 into a Redshift cluster. I managed to create the required table already, but when I try to run the Copy command to load the data from S3 into the table, I get the following error;

Invalid operation: S3ServiceException:The bucket you are attempting to access must be addressed using the specified endpoint. Please send all future requests to this endpoint.
Some Googling will tell you that this means that the S3 bucket and the Redshift cluster are not in the same region. However, both the cluster and the bucket reside in region ap-southeast-1 (Singapore). Specifying the REGION command with this region code doesn't help and the same error returns.

Here is the full command used (access and secret key removed):

copy dim_products
from 's3://s3-ap-southeast-1.amazonaws.com/dwh-dimensions/dim_products.csv' 
CREDENTIALS 'aws_access_key_id=MyAccessKey;aws_secret_access_key=MySecretKey' delimiter ';' 
timeformat 'YYYY-MM-DD HH:MI:SS'
	REGION 'ap-southeast-1';

Open in new window


Can someone please tell me what I'm doing wrong? Happy to provide more info if required, just not sure which details will help in this case.
Thanks.
LVL 13
Koen Van WielinkBusiness Intelligence SpecialistAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Zephyr ICTCloud ArchitectCommented:
Did you try by renaming your csv file to something like "dim_products-csv.txt" in other words change the extension to txt instead of csv ...
Koen Van WielinkBusiness Intelligence SpecialistAuthor Commented:
Hi Spravtek,

Just tried, no luck. I'm attaching screenshots of the file and cluster properties. Perhaps there's something you can find in there?

AWS File properties
AWS Cluster properties
Zephyr ICTCloud ArchitectCommented:
Very weird indeed ... All regions seem ok, syntax of the copy command seems ok to ...

The only thing I can add is when using the csv file, try adding "csv" at the end of the command, something like:

copy dim_products from 's3://s3-ap-southeast-1.amazonaws.com/dwh-dimensions/dim_products.csv' 
CREDENTIALS 'aws_access_key_id=MyAccessKey;aws_secret_access_key=MySecretKey' delimiter ';'  
timeformat 'YYYY-MM-DD HH:MI:SS' 
csv 
REGION 'ap-southeast-1';

Open in new window


I'll do some more digging but so far it all seems to check out.
Need More Insight Into What’s Killing Your Network

Flow data analysis from SolarWinds NetFlow Traffic Analyzer (NTA), along with Network Performance Monitor (NPM), can give you deeper visibility into your network’s traffic.

Koen Van WielinkBusiness Intelligence SpecialistAuthor Commented:
Nope, tried that and still gives the same result.
Will try to delete the bucket and add it again. Maybe I missed something.
Koen Van WielinkBusiness Intelligence SpecialistAuthor Commented:
Ok, recreated both the bucket and the cluster. Still the same error. Running out of ideas...
Zephyr ICTCloud ArchitectCommented:
Could it be an IAM issue? As in restriction in place for the region in question?
Koen Van WielinkBusiness Intelligence SpecialistAuthor Commented:
Got the answer:

change s3://s3-ap-southeast-1.amazonaws.com/dwh-dimensions/dim-products/dim_products.csv

to

s3://dwh-dimensions/dim-products/dim_products.csv

Now getting load errors, but at least it's connecting.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Zephyr ICTCloud ArchitectCommented:
Ah yes, the link should be correct of course :-)
Great ...
Koen Van WielinkBusiness Intelligence SpecialistAuthor Commented:
I thought it was :D. What do I know...
Thanks for the help anyway, appreciate it. Most likely will be back with some more questions in the very near future...
Zephyr ICTCloud ArchitectCommented:
No problem, thanks for sharing the solution.
Koen Van WielinkBusiness Intelligence SpecialistAuthor Commented:
Fixed the problem.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
AWS

From novice to tech pro — start learning today.