Amazon Redshift

📘

This article is for data engineers and DevOps

It walks through giving your Redshift cluster read access to the S3 bucket that holds your data export, and creating the user the ETL service connects as.

Complete the Amazon S3 export first; the ETL service for Redshift loads from that bucket.

1. Policy: Redshift reads the export bucket

This policy lets your Redshift cluster read one S3 bucket.

1. Log in to your AWS account as an administrator.

2. Open the IAM Policies page and click Create policy.

3. Open the JSON tab and paste the policy below, replacing YourAttributionS3Bucket with the name of your export bucket in both resource lines:

   JSON

   {
     "Version": "2012-10-17",
     "Statement": [
         {
             "Sid": "VisualEditor0",
             "Effect": "Allow",
             "Action": [
                 "s3:Get*",
                 "s3:List*"
             ],
             "Resource": [
                 "arn:aws:s3:::YourAttributionS3Bucket/*",
                 "arn:aws:s3:::YourAttributionS3Bucket"
             ]
         }
     ]
   }

4. Click Next: Tags, then Next: Review.

5. Name the policy AttributionS3BucketReadOnly and click Create policy.

2. Policy: AWS Glue

The loader registers the export files as external tables through AWS Glue. On the same Policies page, create a second policy named AttributionGlue with this JSON:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "ATBGluePolicy",
      "Effect": "Allow",
      "Action": [
        "glue:CreateTable",
        "glue:CreateDatabase",
        "glue:DeleteTable",
        "glue:GetTable"
      ],
      "Resource": "*"
    }
  ]
}

3. Role: attach both policies to Redshift

1. Open the IAM Roles page and click Create role.
2. Leave AWS Service selected, choose Redshift under Other services, then Redshift - Customizable, and click Next.
3. On Add permissions, select AttributionS3BucketReadOnly and AttributionGlue, and click Next.
4. Name the role AttributionS3DataLoader and click Create role.
5. Open your Redshift cluster, go to the Properties tab, click Associate IAM role, select AttributionS3DataLoader and click Associate IAM roles.

4. User and database for the loader

Open the Redshift query editor and run:

CREATE USER attribution WITH PASSWORD 'YOUR_PASSWORD';
CREATE DATABASE attribution WITH OWNER attribution;

Choose a strong password; you will enter the same password in the Attribution settings in the next step. If you prefer not to put a plain password in the query, Redshift accepts an MD5 hash, generated like this:

SELECT 'md5' || md5('PLAIN_PASSWORD' || 'attribution');

See Redshift's documentation for the role setup in full.

🚧

No static IP address to allow

Attribution has no fixed IP address. Each run connects from a different, one-off host, so the cluster must be reachable from the internet and its security group cannot be narrowed to an Attribution address. Access is protected by the attribution user's password and the IAM role above.

5. Set up the ETL in Attribution

Open Settings → Data Export, choose Amazon Redshift and enter:

1. The name of the S3 bucket your export lands in.
2. The Redshift server address as host:port.
3. Your AWS account ID, the twelve-digit number in the role's ARN.
4. The password of the attribution user.

Save, and the first load runs after the next daily export. The console links above open the us-east-1 region; switch to your own if the cluster is elsewhere.