Send data from Amazon Redshift to your entire stack.
9 minute read
Amazon Redshift is one of the fastest cloud data warehouse services. It lets you handle large analytical workloads with best-in-class performance, speed, and efficiency.
RudderStack supports Amazon Redshift as a data source from which you can ingest data and route it to your desired downstream destinations.
Grant permissions
Before you set up Redshift as a source, you must grant certain permissions on your Redshift warehouse for RudderStack to access data from it.
Run the queries listed in the following sections in the exact order to grant the required permissions:
Step 1: Create new user in Redshift
Create a new user rudder with a password <strong_unique_password>.
The password set in the above command must meet the following conditions:
It should be 8-64 characters in length.
It must contain at least one upper case, one lower case, and one number.
It can contain any ASCII characters with the ASCII codes 33-126, with the exception of ' (single quotation mark), " (double quotation mark), \, /, and @.
Under Sources, click Reverse ETL and select Redshift.
Configure warehouse credentials
You can choose to proceed with your existing warehouse credentials if you have configured them in the RudderStack dashboard previously. Otherwise, click Add new credentials to add new credentials for your warehouse.
Host: Enter the host name of your Redshift service.
Port: Enter the port number associated with the Redshift database instance.
Database: Enter the database name of your Redshift instance from where RudderStack ingests the data.
Authentication Type: Select the authentication mechanism from the dropdown. RudderStack provides the below authentication mechanisms:
IAM (recommended): Lets you use the RudderStack IAM role for authentication. For more information on creating a RudderStack IAM role for Redshift, see this guide.
Username Password Authentication: Lets you use the username and password for authenticating to Redshift.
You will see the following settings depending on the authentication type you select:
Cluster identifier: Enter your AWS cluster ID.
Cluster region: Enter your AWS cluster region.
Password: Enter the password for the user specified in the User field above.
Then, continue with the setup by specifying the below setting:
SSL Mode: Select the SSL mode as disable or require depending on how RudderStack should connect to your Redshift instance.
Under Select your source type, choose Table and specify the below fields:
Schema: Select the warehouse schema from the dropdown.
Table: Choose the required table from which RudderStack syncs the data.
Primary key: Select the column from the above table that uniquely identifies your records in the warehouse.
RudderStack uses the primary key column for diffing in case of incremental syncs. You can generate it by:
Generating your table with a primary key, OR
Creating a table view
You can use a composite key in cases where one column cannot be considered as a primary key. For example, you can a declare a composite key of user_id and timestamp by creating a view on your warehouse table.
Under Select your source type, choose Model and click Continue.
To configure a model as source:
Enter an optional description and specify the custom SQL query in Query section.
Click Run Query to fetch the data preview.
Select the Primary key to use a column that uniquely identifies your warehouse records.
You can set a primary key only after you run the SQL query successfully using the Run Query option.
RudderStack uses the primary key column for diffing in case of incremental syncs. You can generate it by:
Generating your table with a primary key, OR
Creating a table view
You can use a composite key in cases where one column cannot be considered as a primary key. For example, you can a declare a composite key of user_id and timestamp in SQL query of the model.
Under Select your source type, choose Audience and follow these steps:
Configure your audience source by specifying the below fields:
Schema: Select the warehouse schema from the dropdown.
Table: Choose the required table from which RudderStack syncs the data.
Primary key: Select the column from the above table that uniquely identify your records in the warehouse.
RudderStack uses the primary key column for diffing in case of incremental syncs. You can generate it by:
Generating your table with a primary key, OR
Creating a table view
You can use a composite key in cases where one column cannot be considered as a primary key. For example, you can a declare a composite key of user_id and timestamp by creating a view on your warehouse table.
You cannot delete a source that is connected to any destination.
FAQ
What are the SSL mode options when setting up the Redshift source in RudderStack?
When setting up a Redshift source, RudderStack provides the following two SSL options:
disable: SSL mode is disabled when you select this option. Use it in cases where security is not an issue and you don’t want any encryption overhead.
require: When you select this option, your data is encrypted and sent to RudderStack. Use it in cases where security is important and you can deal with the resulting encryption overhead.
What do the three validations under Verifying Credentials imply?
When setting up a Reverse ETL source, you will see the following three validations under the Verifying Credentials option once you proceed after entering the warehouse credentials:
These options are explained below:
Verifying Connection: This option indicates that RudderStack is trying to connect to the warehouse with the provided warehouse credentials.
If this option gives an error, it means that one or more fields specified in the warehouse credentials are incorrect. Verify your credentials in this case.
Able to List Schema: This option checks if RudderStack is able to fetch all schema details by using the provided credentials.
Able to Access RudderStack Schema: This option implies that RudderStack is able to access the _rudderstack schema you have created by running all commands in the User Permissions section.
If this option gives an error, verify if you have successfully created the _rudderstack schema and given RudderStack the required permissions to access it.
What is the difference between the Table, Model, and Audience options when creating a Reverse ETL source?
When creating a new Reverse ETL source, you are presented with the following options from which RudderStack syncs the data:
Source type
Description
Table
RudderStack uses an existing warehouse table as a data source.
This site uses cookies to improve your experience while you navigate through the website. Out of
these
cookies, the cookies that are categorized as necessary are stored on your browser as they are as
essential
for the working of basic functionalities of the website. We also use third-party cookies that
help
us
analyze and understand how you use this website. These cookies will be stored in your browser
only
with
your
consent. You also have the option to opt-out of these cookies. But opting out of some of these
cookies
may
have an effect on your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This
category only includes cookies that ensures basic functionalities and security
features of the website. These cookies do not store any personal information.
This site uses cookies to improve your experience. If you want to
learn more about cookies and why we use them, visit our cookie
policy. We'll assume you're ok with this, but you can opt-out if you wish Cookie Settings.