Kinesis Firehose load csv data into Redshift

Question

I am using Kinesis firehose to process data into redshift and i am trying both Json and Csv formats.

The Json format works fine for me and data is getting loaded into redshift table.

COPY COMMAND USED FOR JSON :

COPY products_json FROM 's3://foldername/' CREDENTIALS 'aws_iam_role=arn:aws:iam:::role/' MANIFEST json 'auto';

CLI Command to put record :

aws firehose put-record --delivery-stream-name csvtoredshiftstreamingjson --record='Data="{\"productid\":1,\"productname\":\"phone\",\"productprice\":\"2.30\"}"'

However when i use CSV, data is present in S3 but not loaded in redshift.

COPY COMMAND USED FOR CSV :

COPY products_csv FROM 's3://foldername/' CREDENTIALS 'aws_iam_role=arn:aws:iam:::role/' MANIFEST csv;

CLI Command to put CSV Record :

aws firehose put-record --delivery-stream-name csvtoredshiftstream --record='Data="1,Phone,2.30"'

The records are getting processed to S3 but not loaded to redshift. The STL_LOAD_ERRORS table also does not have any records.

Please do let me know if I am missing something.

Thanks.

Answer 1

I was able to overcome this issue by providing firehose access to redshift by unblocking the following Ips in Redshift

52.70.63.192/27 for US East (N. Virginia) 52.89.255.224/27 for US West (Oregon) 52.19.239.192/27 for EU (Ireland)

These Ip addresses are used by firehose to communicate with Redshift.

Thanks & Regards, Srivignesh KN