Redshift - Daily exports from AB Tasty to Redshift
Last updated
Was this helpful?
Last updated
Was this helpful?
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
Create an account
Go to AWS Redshift using the search bar)
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.
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.
Go back to your cluster and click on action and Modify publicly accessible setting.
Click on “Turn on Publicly accessible” and save changes
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).
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:
Replace username by the name of your user:
Select the first line and execute it
Select the second one and execute it
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.
Go back to the bucket/ Permissions tab/ Edit Bucket Policy
Copy / Paste the following script:
Replace NAME BUCKET by the real name of the created bucket.
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.
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.
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.
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:
Once you're on AWS Redshift, click on “create Cluster” (top right on the screen). More information .
To set up your daily export, please refer to the guide: .
Refer to the article to create your payload.