Redshift COPY from CSV file with JSON field into SUPER column

Question

I'm trying to use the COPY command from S3 to Redshift with a CSV file that looks like this:

uuid,new_json_column
a3299e1e-f45a-11e9-99cc-12b09cbafc98,"{""this"": ""is a json"", ""file"": 0}"
c9a31bb9d15fd5d9abe38f28fb2c824e,"{""this"": ""is a json"", ""file"": 0}"

In the Redshift side I have a table structure like the following:

CREATE TABLE IF NOT EXISTS testing_table (
    uuid VARCHAR(150) NOT NULL,
    json_field SUPER
)

My COPY command looks like this:

COPY testing_table
FROM 's3://some_s3_bucket/folder1/'
with credentials
'aws_access_key_id=***;aws_secret_access_key=***'
DELIMITER ',' 
EMPTYASNULL
BLANKSASNULL
TRUNCATECOLUMNS
REMOVEQUOTES
TRIMBLANKS
GZIP
DATEFORMAT 'auto'
TIMEFORMAT 'auto'
ACCEPTINVCHARS;

I cannot load the CSV file with the error:

Load into table 'testing_table' failed.  Check 'stl_load_errors' system table for details.

The row in the stl_load_errors table is this:

|userid|slice|tbl|starttime|session|query|filename|line_number|colname|type|col_length|position|raw_line|raw_field_value|err_code|err_reason|
|------|-----|---|---------|-------|-----|--------|-----------|-------|----|----------|--------|--------|---------------|--------|----------|
|100|3|805168|2021-06-15 16:00:56|28051|3897481|s3://some_s3_bucket/folder1/input_csv_file.csv.gz|1|json_field|super     |1048470   |5|uuid,new_json_column|new_json_column|1224|Invalid sequence for null constant|

Any idea of how I need to format the CSV column or the options I must change in the COPY command?

bruno-uy · Accepted Answer · 2021-06-16 02:26:39Z

2

I finally realized what was happening. The first problem was that I wasn't using the IGNOREHEADER 1 option and that caused the COPY to fail due to wrong values.

This is the final working COPY command:

COPY testing_table
FROM 's3://some_s3_bucket/folder1/'
with credentials
'aws_access_key_id=***;aws_secret_access_key=***'
CSV
IGNOREHEADER 1
DELIMITER ',' 
EMPTYASNULL
BLANKSASNULL
TRUNCATECOLUMNS
TRIMBLANKS
GZIP
DATEFORMAT 'auto'
TIMEFORMAT 'auto'
ACCEPTINVCHARS;

answered Jun 16, 2021 at 2:26

bruno-uy

1,92514 silver badges20 bronze badges

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

2 Comments

expert Over a year ago

Just to let other people. No matter what I tried I couldn't make Redshift COPY load JSON as object for SUPER column if I load it using avro files.

bruno-uy Over a year ago

Oh, didn't try with Avro :/ I had no problems with CSV.

Collectives™ on Stack Overflow

Redshift COPY from CSV file with JSON field into SUPER column

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