Redshift federated query with Aurora(MySQL)
Tags: redshift, query, federated, aws
Published: 2024-04-05 15:58:17

---
title: Redshift federated query with Aurora(MySQL)
published: true
date: 2021-10-15 13:38:56 UTC
tags: redshift,query,federated,aws
canonical_url: 
---

Redshift federated query lets you query and analyse data across multiple operational databases directly. In this blog I have explained how to setup Redshift to query across multiple Aurora instances cross-account.

### Architecture

![](https://cdn-images-1.medium.com/max/1024/1*-chv-ZLBfeJkN-_88bl-xw.png)

### AWS Resources Setup

I have explained how to setup AWS resources in the below steps.

#### Prerequisites

> VPC Peering should be enabled between the accounts.

> Amazon Redshift cluster should have a cluster maintenance version that supports federated queries.

> Amazon Redshift cluster should have enhanced VPC routing enabled.

> Grant select access to innodb\_index\_stats & innodb\_table\_stats for the aurora user in the aurora cluster.

#### Steps

- In Account A create a Customer managed KMS key and add a grant to the Account X’s root user(can also be the Redshift role) with _Decrypt_ & _DescribeKey_ operations.
- In Account A create a secret in the SecretsManager and store the Aurora instance credentials.
- Allow access to the secret for Account X’s root user(can also be the Redshift role) by adding the below resource policy to the secret.

```json
{
   "Version":"2012-10-17",
   "Statement":[
      {
         "Sid":"",
         "Effect":"Allow",
         "Principal":{
            "AWS":"arn:aws:iam::${account-X-id}:root"
         },
         "Action":"secretsmanager:GetSecretValue",
         "Resource":"*"
      }
   ]
}
```

- In the Aurora cluster security group add inbound rules for Redshift.
- Add a policy to the Redshift role in Account X to allow access to Account A’s KMS key and SecretManger like below.

```json
{
   "Version":"2012-10-17",
   "Statement":[
      {
         "Sid":"",
         "Effect":"Allow",
         "Action":"kms:Decrypt",
         "Resource":[
            "arn:aws:kms:${aws-region}:${account-A-id}:key/*******"
         ]
      },
      {
         "Sid":"",
         "Effect":"Allow",
         "Action":[
            "secretsmanager:ListSecrets",
            "secretsmanager:GetRandomPassword"
         ],
         "Resource":"*"
      },
      {
         "Sid":"",
         "Effect":"Allow",
         "Action":[
            "secretsmanager:ListSecretVersionIds",
            "secretsmanager:GetSecretValue",
            "secretsmanager:GetResourcePolicy",
            "secretsmanager:DescribeSecret"
         ],
         "Resource":[
            "arn:aws:secretsmanager:${aws-region}:${account-A-id}:secret:aurora/*******"
         ]
      }
   ]
}
```

- Allow all outbound traffic in the Redshift cluster security group.

### Adding Aurora database as external schema in Redshift

Once the AWS resources are setup then you can add the Aurora databases as external schemas in Redshift and start querying.

#### External schema

```sql
CREATE EXTERNAL SCHEMA redshift\_schema\_name  
FROM MYSQL  
DATABASE ‘aurora\_schema\_name’  
URI ‘${aurora\_cluster\_endpoint}’   
IAM\_ROLE ‘${redshift\_role\_arn}’  
SECRET\_ARN ‘${aurora\_secret\_arn}’;  
```