Create a Redshift connection in Airflow

DB user credentials can be used to establish a connection to an Amazon Redshift cluster. While straightforward to use, this approach lacks the strong security and user access controls provided by identity and access management (IAM). Connecting this way requires the following information:

• Cluster identifier
• Database name
• Port
• User
• Password

Complete the following steps to retrieve these values:

1. In your AWS console, select the region that contains your Redshift cluster, open the Redshift cluster dashboard, then open your cluster.

2. From the General information section, copy the Cluster identifier and Endpoint.

3. Open the Properties tab and copy the Database name and Port.

4. Create a Redshift user and grant a role so that Airflow can access Redshift through the user. Copy the username and password.

You can use IAM credentials to connect Airflow to Redshift. This approach lets you use IAM credentials and limits Airflow’s permissions. The limitation of this method is that you must include an AWS credentials file in your Airflow project.

Following information is required:

• Cluster identifier
• Database name
• Port
• Region
• IAM user
• AWS credentials file

Complete the following steps to retrieve these values:

1. In your AWS console, select the region that contains your Redshift cluster, open the Redshift cluster dashboard, then open your cluster.

2. Open the General information tab, then copy the Cluster identifier and Endpoint.

3. Open the Properties tab and copy the Database name and Port.

4. Open your IAM dashboard, go to Users and select your user. Then, go to Permissions and follow the AWS documentation to ensure that the IAM user is authorized to connect to Redshift and perform SQL operations.

5. Generate a new access key ID and secret access key.

You can use AWS’s Assume Role method to automatically generate temporary credentials to connect to Redshift. This is useful to grant temporary access to limited-privilege IAM users or roles without storing any credentials on disk. The following information is required to create the connection:

• Cluster identifier
• Database name
• Port
• Region
• IAM role ARN

Complete the following steps to retrieve these values:

1. In your AWS console, select the region that contains your Redshift cluster, open the Redshift cluster dashboard, then open your cluster.

2. Open the General information tab, then copy the Cluster identifier and Endpoint.

3. Open the Properties tab and copy the Database name and Port.

4. Open your IAM dashboard, and follow the AWS documentation to create an IAM role and attach an IAM Policy to access the required services, for example AWS Redshift.

5. Edit the trust relationship of the role created in Step 4 to add a trust policy that allows the IAM role to assume your new role.

   1
   {
   2
       "Version": "2012-10-17",
   3
       "Statement": [
   4
           {
   5
               "Effect": "Allow",
   6
               "Principal": {
   7
                   "AWS": "arn:aws:iam::<your-aws-account>:role/<your-role-name>"
   8
               },
   9
               "Action": "sts:AssumeRole"
   10
           }
   11
       ]
   12
   }

6. Copy the ARN of the role.

1. Open your Astro project and add the following line to your requirements.txt file:

   apache-airflow-providers-amazon

   This will install the Amazon provider package, which makes the Amazon Redshift connection type available in Airflow.

2. Run astro dev restart to restart your local Airflow environment and apply your changes in requirements.txt.

3. In the Airflow UI for your local Airflow environment, go to Admin > Connections. Click + to add a new connection, then select the connection type as Amazon Redshift.

4. Fill out the following connection fields using the information you retrieved from Get connection details:

   • Connection Id: Enter a name for the connection.
   • Host: Enter the cluster Endpoint.
   • Database: Enter the Database name.
   • User: Enter the DB user username.
   • Password: Enter the DB user password.
   • Port: Enter the Port.

5. Click Test. After the connection test succeeds, click Save.

   

1. Open your Astro project and add the following line to your requirements.txt file:

   apache-airflow-providers-amazon

   This will install the Amazon provider package, which makes the Amazon Redshift connection type available in Airflow.

2. Copy the aws credentials file to the include directory of your Astro project. It should have the following format:

   1
   # ~/.aws/credentials
   2
   [<your-profile-name>]
   3
   aws_access_key_id="your_aws_access_key_id"
   4
   aws_secret_access_key="your_aws_secret_access_key"

3. Run astro dev restart to restart your local Airflow environment and apply your changes in requirements.txt.

4. In the Airflow UI for your local Airflow environment, go to Admin > Connections. Click + to add a new connection, then select the connection type as Amazon Redshift.

5. Enter a name for the connection in the Connection Id field.

6. Copy the following JSON template into the Extra field, then replace the placeholder values with the information you retrieved in Get connection details.

   1
   {
   2
       "iam": true, 
   3
       "cluster_identifier": "<your-cluster-identifier>", 
   4
       "port": 5439, 
   5
       "region": "<your-region>",
   6
       "db_user": "<your-user>", 
   7
       "database": "<your-database>", 
   8
       "profile": "<your-profile-name>"
   9
   }

7. Click Test. After the connection test succeeds, click Save.

   

1. Open your Astro project and add the following line to your requirements.txt file:

   apache-airflow-providers-amazon

   This will install the Amazon provider package, which makes the Amazon Redshift connection type available in Airflow.

2. Run astro dev restart to restart your local Airflow environment and apply your changes in requirements.txt.

3. In the Airflow UI for your local Airflow environment, go to Admin > Connections. Click + to add a new connection, then select the connection type as Amazon Redshift.

4. Complete the following connection fields using the information you retrieved from Get connection details:

   • Connection Id: Enter a name for the connection.
   • Host: Enter the cluster Endpoint.
   • Database: Enter the Database name.
   • Port: Enter the Port.
   • Extra:

     1
         {
     2
         "role_arn": "<your-role-arn>",
     3
         "region_name": "<your-region>"
     4
         }

5. Click Test. After the connection test succeeds, click Save.