CODEWITHSUNDEEP
amazon-redshift
Nir
Nir

Reputation: 2649

loading gzipped json file into redshift

I'm looking to load from s3 gzipped files that looks like:

{"a": "a", "b": "a", "time": "20210210T10:10:00"}
{"a": "b", "b": "b", "time": "20210210T11:10:00"}

I created the table in redshift beforehand:

create table stTest(
a varchar(50),
b varchar(50),
time varchar(50));

This is what I run and get:

db=# COPY stTest FROM 's3://bucket/file.gz'  credentials 'aws_access_key_id=x;aws_secret_access_key=y' json 'AUTO' gzip ACCEPTINVCHARS ' ' TRUNCATECOLUMNS TRIMBLANKS;
ERROR:  S3 path "AUTO" has invalid format.
DETAIL:
  -----------------------------------------------
  error:  S3 path "AUTO" has invalid format.
  code:      8001
  context:   Parsing S3 Bucket
  query:     72165606
  location:  s3_utility.cpp:132
  process:   padbmaster [pid=4690]
  -----------------------------------------------

Would love for some help.

Upvotes: 1

Views: 527

Answers (1)

Bill Weiner
Bill Weiner

Reputation: 11082

You need to specify the json field to Redshift column mapping. This is done the FORMAT option and a jsonpaths file. See https://docs.aws.amazon.com/redshift/latest/dg/copy-parameters-data-format.html#copy-format for format of the jsonpaths file.

Upvotes: 1

Related Questions