Data load from Amazon S3 to Redshift using Copy Command - How to store Time of copy in Redshift

Question

I am using Amazon Firehose to stream online data, apply transformation using Lambda and load data to Redshift through S3. The COPY command in Firehose is:

COPY <TABLE NAME> FROM 's3 Location' CREDENTIALS 'IAM ROLE' MANIFEST json 'auto' gzip;

I am interested in knowing the exact time at which the S3 to Redshift copy is made by Firehose, so that I can store the same in Redshift and use it for future references. How do I get the time while doing copy command?

Example: If I have a Data Pipeline job, I will get sysdate and then insert it into the Redshift table. How can I accomplish something similar to this in S3 to Redshift Copy? Is there any direct option or workaround for this?

Answer 1

Since you are already using an AWS Lambda function to perform Amazon Kinesis Firehose Data Transformation, you should have the Lambda function add the date. This will then flow through to the data being loaded via the COPY command.

Answer 2

I got a reply from AWS Support team, that i should be using default column with sysdate or current_time while doing create table so that when firehose does a copy, the column is updated with current time by copy command itself.