Redshift copy JSON data with jsonpaths fails with not in correct json format error

Question

I am trying to load data into Redshift using a Firehose delivery stream.

I am using a jsonpaths file uploaded to S3 at the following location.

s3://my_bucket/jsonpaths.json

This file contains the following jsonpaths config

{
    "jsonpaths": [
        "$['col_1']",
        "$['col_2']",
        "$['col_3']",
        "$['col_4']"
    ]
}

To me this config looks ok, but the Firehose Redshift logs keep showing the following error.

"The provided jsonpaths file is not in a supported JSON format."

A similar error is seen even if I run the following copy command directly on the Redshift cluster.

reshift_db=# COPY my_schema.my_table 
FROM 's3://my_bucket/data.json' 
FORMAT JSON 's3://my_bucket/jsonpaths.json'
CREDENTIALS 'aws_iam_role=<role_arn>'
;
ERROR:  Manifest file is not in correct json format
DETAIL:  
  -----------------------------------------------
  error:  Manifest file is not in correct json format
  code:      8001
  context:   Manifest file location = s3://my_bucket/jsonpaths.json
  query:     yyyyy
  location:  s3_utility.cpp:338
  process:   padbmaster [pid=xxxxx]
  -----------------------------------------------

Can someone help with what is going wrong here?

Dhwani Katagade · Accepted Answer · 2020-11-25 18:06:49Z

1

The problem in my case was a BOM (Byte Order Mark) at the beginning of the jsonpaths file. Some editors can save a file with BOM, and this does not show as characters when seen in the editor. And apparently Redshift does not like BOM at the beginning of the jsonpaths file.

For those of you who want to check if this is the case for your jsonpaths file, you can open the file in a hex editor. For the S3 file this can be done as follows.

# aws s3 cp s3://my_bucket/jsonpaths.json - | hexdump -C

To remove the BOM from the file you can do the following.

# aws s3 cp s3://my_bucket/jsonpaths.json - | dos2unix | aws s3 cp - s3://my_bucket/jsonpaths.json

Almost after 2 days of trying, and after having raised an AWS Support ticket, and having posted this question, it struct me that I should check the file in a hex editor.

answered Nov 25, 2020 at 18:06

Dhwani Katagade

1,35217 silver badges31 bronze badges

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

2 Comments

user433342 Jan 23 at 19:48

Yeah I’m running into this now: excel save as csv utf-8 adds a bom, and if cell A1 has a comma you get bom”a,b” which is invalid csv, so it affects copy format csv too. Specifying encoding doesn’t help

Dhwani Katagade Jan 24 at 5:48

@user433342 - Please upvote the question and answer if it helped you. :-)

Collectives™ on Stack Overflow

Redshift copy JSON data with jsonpaths fails with not in correct json format error

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related