AWS Kinesis Firehose not inserting data in Redshift

Question:

I try to have a Kinesis Firehose pushing data in a Redshift table.

The firehose stream is working and putting data in S3.

But nothing arrive in the destination table in Redshift.

  • In the metrics DeliveryToRedshift Success is 0 (DeliveryToRedshift Records is empty)
  • The load logs (redshift web console) and STL_LOAD_ERRORS table are empty.
  • I checked that Firehose is able to connect to Redshift (I see the connections in STL_CONNECTION_LOG)

How can I troubleshoot this ?

Answer:

In the end, I made it work by deleting and re-creating the Firehose stream :-/
Probably the repeated edits via the web console made the thing unstable.

But here are troubleshooting guidelines :

  • A good start point is this procedure : http://docs.aws.amazon.com/firehose/latest/dev/troubleshooting.html
  • Check that data is arriving in S3
  • At this point, the “DeliveryToS3 Success” metric in Firehose monitoring should be non-zero
  • The Redshift cluster must be publicly accessible (see cluster web console)
  • The security group of the cluster must allow inbound traffic from Firehose IP addresses : Firehose currently uses one CIDR block for each available AWS Region:
    • 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)
  • Double check the redshift user/password you gave to Firehose
  • At this point, you should be able to see the connection attempts in Redshift logs :
  • Check that the Redshift user used by Firehose has enough privileges on the target table :
  • Then you can check if the COPY command is run :
  • Then check load errors, or server errors :
  • If you have format problems in your data, or in the COPY options, or a mismatch between your data and the target columns, you should at least see the COPY attempts, and some load errors.
  • If you’re still stuck, with nothing appearing in those log tables, try deleting and recreating the whole firehose stream, as there may be some bugs related to the web console. (This step worked for me)

Leave a Reply