CODEWITHSUNDEEP
jsoncsvamazon-s3amazon-redshift
bruno-uy
bruno-uy

Reputation: 1855

Redshift COPY from CSV file with JSON field into SUPER column

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?

Upvotes: 1

Views: 2359

Answers (1)

bruno-uy
bruno-uy

Reputation: 1855

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;

Upvotes: 1

Related Questions