Error - Materialized view in redshift

Question

Trying to ingest streaming data from kinesis using external schema and materialized views. Getting below error while creating materialized view 
ERROR: ----------------------------------------------- error: Unable to describe stream code: 42001  Request ID: abc. curlCode: t, Timeout was reached Stream name: stream_name query: -1[child_sequence:1] location: kinesis_client.

IAM role associated with redshift has below permission 
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "ReadStream",
            "Effect": "Allow",
            "Action": [
                "kinesis:DescribeStreamSummary",
                "kinesis:GetShardIterator",
                "kinesis:GetRecords",
                "kinesis:DescribeStream"
            ],
            "Resource": "*"
        },
        {
            "Sid": "ListStream",
            "Effect": "Allow",
            "Action": [
                "kinesis:ListStreams",
                "kinesis:ListShards"
            ],
            "Resource": "*"
        }
    ]
}

MilindOke · Answer

You need to specify a particular `resource` or use `*` to allow all. I see the `*` is missing in between the quotes `"Resource": "" `, or it could be formatting issue.

Guillermo · Answer

Hello,

Probably very late to the conversation, but in case anyone encounters this issue again: The most likely cause is the fact that enhanced routing is enabled. When you enable this feature, AWS will provision a VPC endpoint in charge of the connectivity to any external data source required by Redshift.

In this specific case, when trying to load the external schema, that endpoint will try to communicate with the Kinesis public endpoint through the routing rules defined by the subnets on which the Redshift cluster was deployed. If the subnets have no proper route to reach Kinesis, it will cause the timeout error.

To fix this issue (and assuming Redshift is deployed in a private subnet and without public access enabled), perform one of these two actions:

1. Deploy a NAT Gateway on a public subnet and create a route on the route table(s) associated to the private subnets to direct public traffic to the NAT Gateway
2. Deploy a VPC interface endpoint for the service Kinesis. Take the following CLI command as reference, changing the proper values for your VPC/subnets:

```
aws ec2 create-vpc-endpoint --region  --vpc-id  --service-name com.amazonaws..kinesis-streams --vpc-endpoint-type Interface --subnet-ids   --security-group-ids  --policy-document '{\"Version\":\"2012-10-17\",\"Statement\":[{\"Action\":\"*\",\"Resource\":\"*\",\"Effect\":\"Allow\",\"Principal\":\"*\"}]}
```
References:
* https://repost.aws/knowledge-center/redshift-enhanced-vpc-routing
* https://docs.aws.amazon.com/streams/latest/dev/vpc.html

Regards

Error - Materialized view in redshift

Add your answer

Relevant content