Redshift - Daily exports from AB Tasty to Redshift
This feature is currently available in Early Adoption. Please contact your CSM to enroll into the Early Adopter program.
Amazon Redshift is a fully managed, petabyte-scale data warehouse service in the cloud. Amazon Redshift Serverless lets you access and analyze data without all the configurations of a provisioned data warehouse
The connector
Step 1: Create an AWS account
Create an AWS account
Go to AWS Redshift using the search bar)

Step 2: Create a cluster
Once you're on AWS Redshift, click on “create Cluster” (top right on the screen). More information here.

Input an identifier of your choice
Choose your own configuration (AWS can help you if you chose “Help me choose”).
Inside Data Warehouse configuration, you need to create an admin username
Enter a username
Enter a password (we recommend adding it manually)
Click on “create cluster”

Once your cluster is created, it should be displayed on AWS Redshift Cluster.

Step 3: Check VPC security group
Click on the new cluster / Properties tab and click on the instance on VPC security group


Click on the instance of Security group ID.
Inside Inbound rules, check that at least one rule has the follow configuration:
IP version at IPv4
Type: All traffic
Source: 0.0.0.0/0
If it is not the case, you should edit one route with this specific configuration.
Step 4: Edit publicly accessible setting
Go back to your cluster and click on action and Modify publicly accessible setting.

Click on “Turn on Publicly accessible” and save changes

Step 5: Configure the console Query editor v2
Connect to the Data Warehouse
Stay inside your cluster and click on query data => Query in query editor V2.

A new window will open and a new modal will be displayed for the first connection.

Choose Data Warehouse user name and password
By default, the name of your Data Warehouse is dev. Don’t edit this input
User name: enter here the username you have already registered at step 1 (admin user of your cluster). Enter the user name and the good password
Click on connection: the connection is now established.
Create a new user
Since the connection is done, you need to create another user (which is not the admin of the cluster).
Go to the console, and copy/paste this script into app2 integration hub (the user name and password you created).
CREATE USER username PASSWORD 'Userpassword';
Replace username and Userpassword by the username and UserPassword of your choice
Username has to be in lowercase. You must have at least one digital number for your password.
And run the script
Give rights to the new user
Give to the new user all the rights it needs to write some data (provided by app2). Copy and past this script inside the console:
GRANT CREATE ON DATABASE dev TO username;
GRANT usage, create on schema public TO username;
GRANT select on svv_table_info to username;
Replace username by the name of your user:
Select the first line and execute it
Select the second one and execute it

Step 6: Create a S3 bucket
Global Configuration
Go to AWS S3 (Enter S3 or Bucket inside the search bar). Click on create Bucket

Create a bucket by using the following configuration:
Bucket name: Name of your choice
AWS Region: Choose the appropriate region
ACLS disabled
Unchecked Block all public access
Bucket versioning: Disabled
Encryption type: Server-side encryption with Amazon S3 managed keys
Bucket key: Enabled
Click on create Bucket
Now that the bucket is created, click on it and go to the Permissions tab → Edit Block public access
Uncheck Block all public access.
Step 7: Configuration of bucket Policy
Go back to the bucket/ Permissions tab/ Edit Bucket Policy
Copy / Paste the following script:
{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "ListObjectsInBucket",
"Effect": "Allow",
"Principal": "*",
"Action": "s3:ListBucket",
"Resource": "arn:aws:s3:::NAME BUCKET"
},
{
"Sid": "AllObjectActions",
"Effect": "Allow",
"Principal": "*",
"Action": "s3:*Object",
"Resource": "arn:aws:s3:::NAME BUCKET/*"
}
]
}
Replace NAME BUCKET by the real name of the created bucket.
Step 8: Create an IAM User
Create a new user and user group
We need to add a new IAM user who will have the rights on the S3 Bucket
Search for IAM via the search bar:

Click on User => Create user
Enter a user name and click on next

On the second step, you have to create a new user group before finalizing the creation of the user

User group name : Enter the name of the user group you want.
Permissions policies : add to this new group :
As soon as the new user group is created, add it to the new user

Click on next and finalize the creation of the new user.
Step 9: Create an access key
As soon as the user is created, click on it: Security credentials tab / Create access key

Choose Third-party service option
Add a description
Click on create access key
A user key and a user secret are now created.
We recommend saving them as you will need them later.
Step 10: Set up the connector
Go to the integration page > Data Warehouse > Redshift > Create connector
Enter a name for the connector
Host: Go on AWS redshift, and on this screen, copy the endpoint URL
Paste it in the Host field, deleting all characters after the domain name.
Ex: endpoint URL:: julien-nied-cluster.cgtw9e5st7qf.eu-west-3.redshift.amazonaws.com:5439/dev
Enter only:: julien-nied-cluster.cgtw9e5st7qf.eu-west-3.redshift.amazonaws.com
Port: By default, it is 5439 (you can find it on the endpoint URL. If it is not the same, copy and paste it)
Data Warehouse: By default, it’s “dev” (you can find it on the endpoint URL. If it is not the same, copy and paste it)
Schema and JDBC parameters URL: Leave empty/
Authorization method: Choose Username And password
Username: enter the username you created above (create a new user inside the console)
Password: enter the password you created above (create a new user inside the console)
Loading Method: Choose S3 staging
S3 Bucket name: The name of the bucket you created

S3 Bucket path: Need no values.
S3 Bucket region: Choose the region where the bucket is created (ex: eu-west-1)
AWS access key Id: Paste the key ID you have created when you have created a new IAM User
AWS access secret key: Paste the secret key you have created when you have created a new IAM User
Click “Test connection”. If everything works well, you can validate by clicking on “Next step”.
Your connector is now set up, and you can proceed to set up your Export.
You will get an error message, if one of the fields contains an error.


The export
To set up your daily export, please refer to the guide: Data Warehouse integrations: General information.
Step 1: Generate your payload
Refer to the Data Warehouse article to create your payload.
Step 2: Set up the export

Export name: the name of your export, give an explicit name to easily retrieve it in AB Tasty
Name of the table: the name of the table we will create in Redshift
Data exporter query: paste the payload of your data explorer query
Click save and create.
The RedShift integration is now complete, and you will soon see the data flowing into your dedicated Data Warehouse (It can take up to 2–3 hours, depending on the size of your report).
The export is activated upon creation, and new data will be appended to the current one, daily. The following screenshot shows that the export is activated on creation:
Last updated
Was this helpful?