How to load gzipped json data from a copy

Question

COPY tmp_data FROM 's3://mybucket/copy/batch_insert_data_1417072335118.json'  credentials 'aws_access_key_id=XXXXXXXXXXXXXXX;aws_secret_access_key=YyYYYyyYYYYYyyYYYYyyYYYYY' json gzip ACCEPTINVCHARS ' ' TRUNCATECOLUMNS TRIMBLANKS;

Above COPY command works without gzip. But Now i want to use the gzip to speed up the process. Now im uploading a gzipped json file into S3 bucket. But the above copy command does not work? any idea how to load a gzipped json file from COPY in Redshift?

Answer 1

You are missing json_option (http://docs.aws.amazon.com/redshift/latest/dg/r_COPY.html). Try setting it 'auto'. See corrected example below:

COPY tmp_data FROM 's3://mybucket/copy/batch_insert_data_1417072335118.json'  credentials 'aws_access_key_id=XXXXXXXXXXXXXXX;aws_secret_access_key=YyYYYyyYYYYYyyYYYYyyYYYYY' json 'AUTO' gzip ACCEPTINVCHARS ' ' TRUNCATECOLUMNS TRIMBLANKS;