AWS Redshift — loading into a TIMESTAMP field

Question

I'm using an AWS Kinesis Firehose to write a file to S3, which in turn loads into Redshift. My table has a TIMESTAMP column on it:

create table TABLE_NAME
(
  ...
  COLUMN_NAME TIMESTAMP,
  ...
)

The Redshift COPY options in the Firehose give the format for the column:

DELIMITER '\t' GZIP DATEFORMAT AS 'YYYYMMDD' TIMEFORMAT AS 'YYYYMMDDHH24MISS' MAXERROR 1000

The data for COLUMN_NAME isn't always populated. In that case, I set the data for the TIMESTAMP column to an empty string. When this happens, I'm seeing Redshift load errors with error code 1206:

Invalid timestamp format or value [YYYYMMDDHH24MISS]

The interesting part is that my load is still succeeding. The records with an empty string for this TIMESTAMP column still load to my Redshift table just fine, but I keep seeing these errors logged to the stl_load_errors table.

Is there a Redshift COPY option I should be setting to ignore these errors? Is there a keyword I should set instead of a blank string in the data?

I'm 95% certain that I tried writing the string "NULL" in that position to my stream before going with the empty string approach and that the load failed in a similar fashion. I will try it again, though. — Valdogg21
Don't write the string null, just write NULL. A String is a String, NULL is not a String. Also, by having NULL you can then use "where timestamp IS NULL" in your queries. — user602525

Kien Pham Kien Pham · Accepted Answer · 2015-12-15T01:12:50

3

votes

I think you can use ACCEPTANYDATE param at your COPY command

AWS Redshift — loading into a TIMESTAMP field

1 Answers