How to Set Up Cross-Account Amazon MSK to Redshift Streaming Over Private Network

Follow the following steps to perform cross-account streaming ingestion for Amazon Redshift:

1. Create a MSK cluster in Account-1.
2. Create an AWS Identity and Access Management (IAM) role in Account-1 to read the data stream using AWS best practices around applying least privileges permissions.
3. Create an Amazon Redshift – Customizable IAM service role in Account-2 to assume the IAM role.
4. Create an Amazon Redshift cluster in Account-2 and attach the IAM role.
5. Modify the trust relationship of the MSK IAM role in order to access the Amazon Redshift IAM role on its behalf.
6. Create an external schema using IAM role chaining.
7. Create a materialized view for high-speed ingestion of stream data.
8. Refresh the materialized view and start querying.

---

Account-1 setup

Complete the following steps in Account-1:

1. Create a MSK cluster. For instructions, refer to Step 1 in Get started using Amazon MSK .
2. Create an IAM role msk-tutorial-role granting access to create topics on the Amazon MSK cluster by following Steps in document.
3. Create a client machine and a topic in the Amazon MSK cluster as per step 3 and step 4 respectively.
4. Setup a consumer to confirm the data in topic.
5. Next, we create an IAM policy called MSKPolicy in Account-1.
6. On the IAM console, choose Policies in the navigation pane.
7. Choose Create policy.
8. Create a policy called MSKPolicy and add the following JSON to your policy (provide the AWS account ID for Account-1):

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": [
                "kafka:GetBootstrapBrokers"
            ],
            "Resource": "*"
        }
    ]
}

9. In the navigation pane, choose Roles.
10. Choose IAM msk-tutorial-role created in step 2.
11. Attach the policy MSKPolicy.

---

Account-2 setup

Complete the following steps in Account-2:

1. Sign in to the Amazon Redshift console in Account-2.
2. Create an Amazon Redshift cluster.
3. On the IAM console, choose Policies in the navigation pane.
4. Choose Create policy.
5. Create a policy RedshiftStreamPolicy and add the following JSON (provide the AWS account ID for Account-1):

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "StmtStreamRole",
            "Effect": "Allow",
            "Action": [
                "sts:AssumeRole"
            ],
            "Resource": "arn:aws:iam::<Account-1>:role/msk-tutorial-role"
        }
    ]
}

6. In the navigation pane, choose Roles.
7. Choose Create role.
8. Select AWS service and choose Redshift and Redshift customizable.
9. Create a role called RedshiftStreamRole.
10. Attach the policy RedshiftStreamPolicy to the role.

---

Set up trust relationship

To set up the trust relationship, complete the following steps:

1. Sign in to the IAM console as Account-1.
2. In the navigation pane, choose Roles.
3. Edit the IAM role msk-tutorial-role and modify the trust relationship (provide the AWS account ID for Account-2):

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::<Account-2>:role/RedshiftStreamRole",
                "Service": "ec2.amazonaws.com"
            },
            "Action": "sts:AssumeRole"
        }
    ]
}

---

Setup up connectivity:

1. Enable enhanced VPC routing on Redshift cluster in Account-2.
2. Setup VPC peering connection between the VPC of MSK and VPC of Redshift.
3. Edit Route tables of both VPCs to have route from Peering connection.
4. Update the security groups of both services to allow traffic from each other.

---

Set up streaming ingestion

To set up streaming ingestion, complete the following steps:

1. Sign in to the Amazon Redshift console as Account-2.
2. Launch the Query Editor v2 or your preferred SQL client and run the following statements to access the topic present in Account-1.
3. Create an external schema using role chaining (replace the IAM role ARNs, separated by a comma without any spaces around it):

CREATE EXTERNAL SCHEMA schema_stream
FROM MSK
IAM_ROLE 'arn:aws:iam::Account-2:role/RedshiftStreamRole,arn:aws:iam::Account-1:role/msk-tutorial-role'
AUTHENTICATION IAM 
URI 'boot-z7n.cluster.ot.kafka.us-east-1.amazonaws.com:9098,boot-2xp.cluster.ot.kafka.us-east-1.amazonaws.com:9098,boot-x6a.cluster.ot.kafka.us-east-1.amazonaws.com:9098';

4. Create a materialized view to consume data:

CREATE MATERIALIZED VIEW my_stream_vw AUTO REFRESH YES AS
SELECT *
FROM schema_stream."MSKTutorialTopic";

Kafka topic names are case sensitive and can contain both uppercase and lowercase letters. To ingest from topics with uppercase names, you can set the configuration enable_case_sensitive_identifier to true at the session or database level. To turn on auto refresh, use AUTO REFRESH YES. The default behavior is manual refresh.

5. Refresh the view, which triggers Amazon Redshift to read from the topic and load data into the materialized view:

REFRESH MATERIALIZED VIEW my_stream_vw;

6. Query data in the materialized view:

select * from my_stream_vw

Above query should give the data from MSK Topic.

---

In this article, we discussed how to set up two different AWS accounts to enable cross-account Amazon Redshift streaming ingestion. It’s simple to get started and you can perform rich analytics on streaming data, right within Amazon Redshift using existing familiar SQL.

For information about how to set up Amazon Redshift streaming ingestion from Apache Kafka sources in a single account, refer to Getting started with streaming ingestion from Apache Kafka sources.