Issue using COPY command with AWS Redshift

Question

I am trying to copy a CSV file from my PC, which has SSH running, to AWS Redshift. I have copied the Redshfit public key to authorized_keys for the appropriate user.

copy contact (id,name)
from 's3://mybucket/m1.mfs'
credentials 'aws_access_key_id=xxx;aws_secret_access_key=yyy''
region 'us-west-2'
csv ssh

The manifest file, which has been uploaded to S3, is as follows:

{ 
"entries": [ 
{"endpoint":"73.29.89.30", 
"command": "/bin/cat /home/user23/rfr/src/redshift/Contact2.csv",
"mandatory":true, 
"username": “user23"} 
] 
}

The error I get is:

Error: ERROR: Manifest file is not in correct json format**

From what I can see, the file is in the correct format. I think the issue may be something else, but how do I debug this? The endpoint is valid, the command is valid, and the username is valid.

Answer 1

Perhaps the JSON you have is not quite correct. When I paste your JSON into jsonlint.com I get the following:

Parse error on line 7: 
...        "username": “user23"} ] }
-----------------------^
Expecting 'STRING', 'NUMBER', 'NULL', 'TRUE', 'FALSE', '{', '['

The AWS Redshift documentation on COPY SSH parameters has the following

The quote characters must be simple quotation marks (0x22), not slanted or "smart" quotation marks.

So double check you quote marks around the username you've provided, and if they are how you've provided them above, change it from to “user23" >> "user23" (note the first quote mark is different).

• The first quote mark in your code is unicode : U+201C and not U+0022