How to ignore errors but not skip rows in redshift copy command

1
votes

I have a nested json as my source file in S3 and I am trying to copy this file into redshift. My issues with this are as follows,

  1. I use MAXERROR - I need to skip certain errors because the source file is missing certain fields in some cases and has them in other
  2. I use a JSONPATH file - to pick the fields that I need to copy to redshift
  3. All the columns in the table are varchar

Obviously, since I am using maxerror the copy command executes successfully but the table has 0 records. Here is my copy command

COPY public.table(col1,col2,col3,col4,col5,col6)
from 's3://bucket/filename'
credentials 'redshift'
format as JSON 'jsonpathfile.json'
timeformat 'YYYY-MM-DDTHH:MI:SS'
EMPTYASNULL ACCEPTANYDATE ACCEPTINVCHARS TRUNCATECOLUMNS maxerror 100 ;

If I check into stl_load_errors it keeps saying

Invalid JSONPath format: Member is not an object.

Does this mean the copy command is not able to find even one object that fits the jsonpath file? Which is definitely not true. I inferred the schema of the input file to design the jsonpath file.

1
amazon-s3amazon-redshift

1 Answers

0
votes

Here is an example from COPY Examples - Amazon Redshift:

copy category
from 's3://mybucket/category_object_paths.json'
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' 
json 's3://mybucket/category_jsonpath.json';

The path to the jsonpath file is specified fully, whereas your example just refers to the filename.

Try specifying the full path starting with s3:// and see whether that helps.