getting error when trying to ingest data from firehose to redshift

user_10305346 0
Viewed 47

getting an error 1206 and 1205 when injesting data from fireshose to redshift using a copy command

Below is the raw data on firehose

{
    "Name": "yoyo"
    "a_timestamp": "2021-05-11T15:02:02.426729Z",
    "a_date": "2021-05-11T00:00:00Z"
}

below is the copy command

COPY pqr_table FROM 's3://xyz/<manifest>' CREDENTIALS 'aws_iam_role=arn:aws:iam::<aws-account-id>:role/<role-name>' MANIFEST json 's3://xyz/abc.json' DATEFORMAT 'YYYY-MM-DD' ;

below is the DDL command

create table events (
Name varchar(8), 
a_timestamp timestamp,
a_date date)


It would be great if anyone can please help me with this
amazon-kinesis-firehose amazon-redshift amazon-web-services sql
1 Answers

Those are errors for bad timestamp and date formats. You need to have "timeformat" specified with that string as it is not Redshift's default format. I'd first try 'auto' for both of these and see if Redshift can work things out.

dateformat as 'auto'
timeformat as 'auto'

Also, having time specified in your date may create some confusion and may need you to manually specify the format or ingest as timestamp and then cast to date. I'd fist see if 'auto' does the trick.

user_133506520
Related