JSON Data loading into Redshift Table

Question

I am trying to load JSON Data into Redshift Table. Below is the sample code, Table structure and JSON Data.

I have gone through many post in this site and AWS. However, my issue is not yet resolved.

JSON data is below, that I copied the below data in test.json and uploaded in S3...

{backslash: "a",newline: "ab",tab: "dd"}

Table structure is as below

create table escapes (backslash varchar(25), newline varchar(35), tab 
varchar(35));

Copy command is as below

copy escapes  from 's3://dev/test.json'
credentials 'aws_access_key_id=******;aws_secret_access_key=$$$$$'
format as JSON 'auto';

However it throws the below error

Amazon Invalid operation: Load into table 'escapes' failed. Check 'stl_load_errors' system table for details.; 1 statement failed.

In the 'stl_load_errors' table , the error reason is as below "Invalid value."

If its not typo, I think its data format issue. Given JSON is not valid. { backslash: "a", newline: "ab", tab: "dd" } ideally it should be { "backslash": "a","newline": "ab", "tab": "dd" } — Red Boy
– Red Boy, Commented Nov 2, 2018 at 13:24
I have already tired with the data as { "backslash": "a", "newline": "ab", "tab": "dd" }. It didn't work. That's the reason I edited the JSON data as mentioned in the question. — BigData-Guru
– BigData-Guru, Commented Nov 2, 2018 at 13:31
@cloudguru I tried its working. share your actual data in test.json — iammehrabalam
– iammehrabalam, Commented Nov 4, 2018 at 12:35
@ iammehrabalam, Is there any configuration needs to be defined in the Redshift Table. My Table is defined as mentioned in the Original Question. Since the Data loading didn't work with Actual Data , I created a Simple JSON file to test with the above sample data and trying with copy command... — BigData-Guru
– BigData-Guru, Commented Nov 4, 2018 at 14:21
Sorry Friends.. It was my JSON file which has issue. I am using windows Notepad++.. There was some special character which I could not visualize. — BigData-Guru
– BigData-Guru, Commented Nov 4, 2018 at 15:58

Red Boy · Accepted Answer · 2018-11-02 13:28:50Z

1

Seems like issue is with your JSON data. Ideally it should be-

{
 "backslash": "a",
 "newline": "ab",
 "tab": "dd"
}

I hope this should resolve your issue, but if not, update your question and I could reattempt the answer.

answered Nov 2, 2018 at 13:28

Red Boy

5,7893 gold badges34 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

JSON Data loading into Redshift Table

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related