Overview

Our redshift destination works on both serverless and provisioned clusters in your AWS account. All you need to do is to create a role in your AWS account with access to Redshift, allow us to assume it and grant it access to the database in Redshift.

Give Funnel access to Redshift

1. Create a role

We recommend that you click on the "Generate IAM Policy" button when creating a Redshift Data Share in Funnel. Then we will generate the permissions and the trust relationship where you don't have to replace any values and where it can be directly copy pasted into your AWS role in IAM.

Otherwise we explain how to write the permissions in step 1.1 and 1.2 below.

1.1 Set permissions

Create a role in your AWS account by going to IAM and pasting this into an inline policy in the Permissions section:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "GiveAccessToRedshift",
            "Effect": "Allow",
            "Action": [
                "redshift-data:ListTables",
                "redshift-data:DescribeTable",
                "redshift-data:GetStatementResult",
                "redshift-data:CancelStatement",
                "redshift-data:DescribeStatement",
                "redshift-data:BatchExecuteStatement",
                "redshift-data:ListSchemas",
                "redshift-data:ExecuteStatement",
                "redshift-data:ListStatements",
                "redshift-data:ListDatabases",
                "redshift-serverless:GetCredentials",
                "redshift:GetClusterCredentialsWithIAM"
            ],
            "Resource": "*"
        }
    ]
}

We need all redshift-data:* permissions (listed above) as well as redshift-serverless:GetCredentials if you are using Redshift serverless or redshift:GetClusterCredentialsWithIAM if you have your own provisioned Redshift cluster.

1.2 Set trust relationship

Then in the Trust relationship section paste the section below. Remember to replace {Funnel Workspace ID} with a list of all of the Funnel workspace IDs that will use this role. For example ["-aBcDeFgHiJkLmNoP1", "-aBcDeFgHiJkLmNoP2"]. The Funnel Workspace ID can be found in the URL of your Funnel workspace after /account/ and always starts with a dash.


{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "Service": "redshift.amazonaws.com"
            },
            "Action": "sts:AssumeRole"
        },
        {
            "Sid": "AllowFunnelToAssumeThisRole",
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::468215445228:role/funnelRedshiftUploadRole"
            },
            "Action": "sts:AssumeRole",
            "Condition": {
                "StringEquals": {
                    "sts:ExternalId": ["{Funnel Workspace ID}"]
                }
            }          
        }
    ]
}

Or if you are using the Funnel EU instance at app.eu.funnel.io then in the Trust relationship section paste the section below.


{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "Service": "redshift.amazonaws.com"
            },
            "Action": "sts:AssumeRole"
        },
        {
            "Sid": "AllowFunnelToAssumeThisRole",
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::924192298621:role/funnelRedshiftUploadRole"
            },
            "Action": "sts:AssumeRole",
            "Condition": {
                "StringEquals": {
                    "sts:ExternalId": ["{Funnel Workspace ID}"]
                }
            }
        }
    ]
}

2. Grant the role access to the Redshift database

In Redshift run the queries below depending on your need. Remember to replace <DATABASE_NAME>, <SCHEMA_NAME> and <AWS_ROLE_NAME> with the your own values.

If for example the AWS role that you created in step 1 is arn:aws:iam::123456789012:role/MyRedshiftRole then the <AWS_ROLE_NAME> that you input below is "MyRedshiftRole". And the user you will be granting access to is "IAMR:MyRedshiftRole".

First create a database user for the Role you created in step 1

CREATE USER "IAMR:<AWS_ROLE_NAME>" PASSWORD DISABLE;

If you get an error that the user already exists you can just skip this step.

Then run either of these queries

If you want us to create the schema(s) in the database

GRANT ALL ON DATABASE "<DATABASE_NAME>" TO "IAMR:<AWS_ROLE_NAME>"

Or if you have an existing schema run this

GRANT CREATE ON DATABASE "<DATABASE_NAME>" TO "IAMR:<AWS_ROLE_NAME>";
GRANT CREATE ON SCHEMA "<SCHEMA_NAME>" TO "IAMR:<AWS_ROLE_NAME>";
GRANT USAGE ON SCHEMA "<SCHEMA_NAME>" TO "IAMR:<AWS_ROLE_NAME>";

Create the Data Share in Funnel

Now create a new Redshift Data Share in Funnel and input the AWS Role ARN of the role that you created in step 1 into Funnel. Also fill in the AWS region that your redshift database is in as well as the database and schema name. You also have to fill in the workgroup if your Redshift instance runs the serverless version otherwise you fill in the Cluster name.

We recommend keeping the "Let Funnel create and manage a view" selected, see why in the next section.

Query the data in Redshift

With a view

If you selected "Let Funnel create and manage a view" when creating the Funnel Data Share you can now query the View ID that you selected like this:

SELECT * FROM "<DATABASE>"."<SCHEMA>"."<VIEW_ID>";

For example

SELECT * FROM "funnel_db"."monthly"."all_funnel_data_view";

Without a view

If you don't have a view you have to create one yourself or query all the tables yourself like this:

SELECT *
FROM "<DATABASE_NAME>"."<SCHEMA_NAME>"."<TABLE_NAME_1>"
UNION ALL
SELECT *
FROM "<DATABASE_NAME>"."<SCHEMA_NAME>"."<TABLE_NAME_2>"
UNION ALL
SELECT *
FROM "<DATABASE_NAME>"."<SCHEMA_NAME>"."<TABLE_NAME_3>";

For example

SELECT *
FROM "funnel_db"."monthly"."funnel_data_2024_04"
UNION ALL
SELECT *
FROM "funnel_db"."monthly"."funnel_data_2024_05"
UNION ALL
SELECT *
FROM "funnel_db"."monthly"."funnel_data_2024_06";