AWS COPY From S3 Command Fails As A Result of Truncation

First of all I would like to mention that I tried searching for this issue in existing SO questions but I couldn't find the scenario I came across. Hence asking a new question.

So I am trying to import data from S3 to Redshift.

The data in S3 is JSON data seperated by new line character i.e. \n (exported using UNLOAD command from other redshift cluster)

The copy command is -

copy redhist_table_name
from 's3://bucket/path/to/s3/json/file.json'
iam_role 'iam_role_info'
region 'region';
json 'auto';

The STL_LOAD_ERRORS shows error as - delimiter not found but when I looked closely, I found that the copy command is copying only first 1024 characters from the json row which results in above error

I looked for all the options that copy command offers to see if there's way to increase this limit but I found none.

Any ideas where is this limit coming from? Or is this not the root cause of this issue?

CodePudding user response：

So I expect this is not the root cause. Stl_load_errors only stores 1024 characters in the "raw_line" column. There very well may be a limit to how long a line can be but I know that it is much longer than 1024 characters.

Which line in the file is COPY failing on? First or somewhere later in the file?

If a line deep in the file there may be something off about it. UNLOAD to COPY should work correctly but I can see that there may be some corner cases (like " in string values). If it a specific line then posting that line (sanitized if need be) would be helpful.