Search code examples
jsonamazon-s3amazon-dynamodbamazon-redshift

Redshift COPY with JSON PATH Case Sensitivity

I want to copy a file from S3 to Redshift. Content is in camelCase:

{"accountId":{"S":"1acb4"}}

Redshift columns are in all lowercase. This is marshalled Dynamo JSON, so I am using a JSON PATHs file for copying:

{"jsonpaths": ["$.accountId.S"]}

I have read across various sources on the internet that using a JSON PATHs file eliminates the issue of non-matching case, but I am still getting NULLs when running my COPY statement as such:

COPY destination.table
FROM 's3://mybucket/myfile'
IAM_ROLE 'myarn'
JSON 's3://path/to/jsonpathsfile.json'

How can I get the camelCase JSON to load into Redshift by using COPY? 'ignorecase' is not allowed to be used when using a JSON PATHs file, but I need the JSON PATHs file to unmarshall the DynamoDB JSON.

Solution

  • Have you tried enabling cases sensitive identifiers?

    set enable_case_sensitive_identifier to true;

    It may not be the issue but a good place to start.