AWS Kinesis Firehose 未在 Redshift 中插入数据 [英] AWS Kinesis Firehose not inserting data in Redshift

问题描述

我尝试使用 Kinesis Firehose 在 Redshift 表中推送数据.

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

firehose 流正在工作并将数据放入 S3.

The firehose stream is working and putting data in S3.

但在 Redshift 中没有任何内容到达目标表.

But nothing arrive in the destination table in Redshift.

• 在指标中，DeliveryToRedshift Success 为 0(DeliveryToRedshift Records 为空)
• 加载日志(红移 Web 控制台)和 STL_LOAD_ERRORS 表为空.
• 我检查了 Firehose 是否能够连接到 Redshift(我在 STL_CONNECTION_LOG 中看到了连接)

我该如何解决这个问题?

How can I troubleshoot this ?

推荐答案

最后，我通过删除并重新创建 Firehose 流使其工作:-/可能是通过 Web 控制台的重复编辑使事情变得不稳定.

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.

但这里有故障排除指南:

• 这个过程是一个很好的起点:http://docs.aws.amazon.com/firehose/latest/dev/troubleshooting.html
• 检查数据是否到达 S3
  • 必须有一个 IAM 角色用于 Firehose 交付，并且 Firehose 服务与此角色之间存在信任关系
  • 此 IAM 角色必须具有 S3 访问策略
  • 在此处查看政策 json:http://docs.aws.amazon.com/firehose/latest/dev/controlling-access.html#using-iam-s3
  • 52.70.63.192/27 适用于美国东部(弗吉尼亚北部)
  • 美国西部(俄勒冈)52.89.255.224/27
  • 52.19.239.192/27 适用于欧盟(爱尔兰)

  此时，您应该能够在 Redshift 日志中看到连接尝试:

  At this point, you should be able to see the connection attempts in Redshift logs :

  select * from stl_connection_log where remotehost like '52%' order by recordtime desc;  
  

• 检查 Firehose 使用的 Redshift 用户是否对目标表具有足够的权限:

• Check that the Redshift user used by Firehose has enough privileges on the target table :

  select tablename, 
     HAS_TABLE_PRIVILEGE(tablename, 'select') as select,
     HAS_TABLE_PRIVILEGE(tablename, 'insert') as insert,
     HAS_TABLE_PRIVILEGE(tablename, 'update') as update,
     HAS_TABLE_PRIVILEGE(tablename, 'delete') as delete, 
     HAS_TABLE_PRIVILEGE(tablename, 'references') as references 
  from pg_tables where schemaname='public' order by tablename;
  

• 然后您可以检查是否运行了 COPY 命令:

• Then you can check if the COPY command is run :

  select * from stl_query order by endtime desc limit 10;
  

• 然后检查加载错误，或服务器错误:

• Then check load errors, or server errors :

  select * from stl_load_errors  order by starttime desc;
  select * from stl_error where userid!=0 order by recordtime desc;
  

• 如果您的数据或 COPY 选项存在格式问题，或者您的数据与目标列不匹配，您至少应该看到 COPY 尝试和一些加载错误.

• 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.

  如果您仍然卡住，那些日志表中没有任何内容，请尝试删除并重新创建整个流水管流，因为可能存在一些与 Web 控制台相关的错误.(这一步对我有用)

  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)

  这篇关于AWS Kinesis Firehose 未在 Redshift 中插入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！