0

I'm using COPY to import MySQL data into my Redshift database. I've run into an issue where I have JSON data in a table and it fails to COPY, saying "Delimited value missing end quote".

So I start digging into this, and I experiment a little. I made a very basic table to test this out, called test, as so:

CREATE TABLE test (cola varchar(1000), colb varchar(1000))

I then use the COPY command to populate this table from a file called test.csv that I have in an S3 bucket. If the file looks like this, it works: 

"{  
""contactInfo"": [  
    ""givenName"",  
    ""familyName"",  
    ""fullName"",  
    ""middleNames"",  
    ""suffixes"",  
    ""prefixes"",  
    ""chats"",  
    ""websites""  
]}", "a"

If it looks like this, it fails: 

"a", "{  
""contactInfo"": [  
    ""givenName"",  
    ""familyName"",  
    ""fullName"",  
    ""middleNames"",  
    ""suffixes"",  
    ""prefixes"",  
    ""chats"",  
    ""websites""  
]}"

So, if my JSON data is in the first column, COPY ignores the line feed inside the QUOTE. If it is in the second column or later, it sees the line feed as the end of the line of data.

For the record, I am not setting QUOTE AS, I am letting it default to ", which is why I double up the " chars in the file.

Anyone have any idea why this is happening, and how I can fix it? I can't move the data to the first column all the time, I don't always know where it is, and there may be more than one column of JSON data.

Edit: For the record, I have tried this with a simple linefeed inside a string, no JSON data, and am running into the same problem.

albinoalligator
  • 1
  • 2
  • What are the details of the copy command that you're using? – mike_pdb Dec 15 '14 at 17:02
  • Literally just a copy command pointing to an S3 bucket, the only qualifier being used is csv. So, as bare bones as possible. I've worked around this problem using a non csv flat file with escapes for line feed, carriage return, and separators. I'd still prefer this method if I could get it to work, but have had no luck. Literally copy from 's3://' credentials 'aws_access_key_id= aws_secret_access_key=' csv;
     – albinoalligator Dec 17 '14 at 17:42

0 Answers0