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?