By using AWS re:Post, you agree to the Terms of Use

COPY from S3 to Redshift with manifest fails

0

Hi,

I need to load data from Aurora (MySQL) to Redshift and using S3 is one of the available options. I can extract data from Aurora (MySQL) to S3 using:

SELECT *
FROM data_table
INTO OUTFILE S3 's3-XXX://bucket_name/aurora_files/data_table'
    FORMAT CSV HEADER
    FIELDS TERMINATED BY ';'
    LINES TERMINATED BY '\n'
    OVERWRITE ON;

and load the same data to Redshift using:

copy data_table
from 's3://bucket_name/aurora_files/data_table.part_00000'
access_key_id 'XXX'
secret_access_key 'XXX'
csv
delimiter ';'
ignoreheader 1
timeformat 'YYYY-MM-DD HH:MI:SS'
region 'XXX';

If I try to extract data with Manifest and load that from Manifest, I get the following error:

[2022-03-14 18:08:52] [XX000] ERROR: S3 path "s3-XXX://bucket_name/aurora_files/data_table.part_00000" has invalid format.
[2022-03-14 18:08:52] Detail:
[2022-03-14 18:08:52] -----------------------------------------------
[2022-03-14 18:08:52] error:  S3 path "s3-XXX://bucket_name/aurora_files/data_table.part_00000" has invalid format.
[2022-03-14 18:08:52] code:      8001
[2022-03-14 18:08:52] context:   Parsing S3 Bucket
[2022-03-14 18:08:52] query:     312924
[2022-03-14 18:08:52] location:  s3_utility.cpp:133
[2022-03-14 18:08:52] process:   padbm@ster [pid=13049]
[2022-03-14 18:08:52] -----------------------------------------------

Following commands are used to create S3 file and load that to Redshift with manifest:

SELECT *
FROM data_table
INTO OUTFILE S3 's3-XXX://bucket_name/aurora_files/data_table'
    FORMAT CSV HEADER
    FIELDS TERMINATED BY ';'
    LINES TERMINATED BY '\n'
    MANIFEST ON
    OVERWRITE ON;
copy data_table
from 's3://bucket_name/aurora_files/data_table.manifest'
access_key_id 'XXX'
secret_access_key 'XXX'
csv
delimiter ';'
ignoreheader 1
timeformat 'YYYY-MM-DD HH:MI:SS'
region 'XXX'
manifest;

What could be the issue?

1 Answer
0

Is the file 's3://bucket_name/aurora_files/data_table.part_00000' manifest file? You should point out manifest file or only prefix such as 's3://bucket_name/aurora_files/data_table'

https://docs.aws.amazon.com/redshift/latest/dg/t_loading-tables-from-s3.html

answered 6 months ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions